9. Give two UNIX kernel parameters that effect an Oracle install?
SHMMAX & SHMMNI
10. Briefly, how do you install Oracle software on UNIX.?
Basically, set up disks, kernel parameters, and run orainst..
ORACLE TROUBLESHOOTING INTERVIEW QUESTIONS
1. How can you determine if an Oracle instance is up from the operating system level?
Level: Low
Expected answer: There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is
acceptable. For example, on UNIX a ps -ef|grep dbwr will show what instances are up.
2. Users from the PC clients are getting messages indicating : Level: Low
ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual) What could the problem be?
Expected answer: The instance name is probably incorrect in their connection string.
3. Users from the PC clients are getting the following error stack:
Level: Low
ERROR: ORA-01034: ORACLE not available
ORA-07318: smsget: open error when opening sgadef.dbf file.
HP-UX Error: 2: No such file or directory What is the probable cause?
Expected answer: The Oracle instance is shutdown that they are trying to access, restart the instance.
4. How can you determine if the SQLNET process is running for SQLNET V1? How about V2?
Level: Low
Expected answer: For SQLNET V1 check for the existence of the orasrv process. You can use the command "tcpctl status" to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command "lsnrctl status".
5. What file will give you Oracle instance status information? Where is it located?
Level: Low
Expected answer: The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.
6. Users aren?t being allowed on the system. The following message is received:
Level: Intermediate
ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem?
Expected answer: The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
7. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?
Level: Intermediate
Expected answer: There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.
8. You attempt to add a datafile and get:
Level: Intermediate
ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it?
Expected answer: When the database was created the db_files parameter in the
initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
9. You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem?
Level: High
Expected answer: Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
10. Your users get the following error:
Level: Intermediate
ORA-00055 maximum number of DML locks exceeded
What is the problem and how do you fix it?
Expected answer: The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error.
Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
11. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do?
Level: High
Expected answer: As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following:
CONNECT INTERNAL STARTUP MOUNT
(Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE ....
OFFLINE;)
RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS;
(bring read-only tablespaces back online) Shutdown and backup the system, then restart
If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well.
If no backup of the control file is available then the following will be required:
CONNECT INTERNAL STARTUP NOMOUNT
CREATE CONTROL FILE ...;
However, they will need to know all of the datafiles, logfiles, and settings for
MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command
1. Why is a UNION ALL faster than a UNION?
The union operation, you will recall, brings two sets of data together. It will *NOT*
however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.
2. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?
You can script the process to include it in a set of install scripts you deliver with a product.
You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
You can log the output and review it for errors.
You learn more about the process of database creation, such as what options are available and why.
3. What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?
Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through 10's of thousands of attempts quickly, this can be a very affective way to break into a database. A good password therefore should not be a dictionary word, it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to ten characters, including upper and lowercase, special characters, and even alternate characters if possible.
Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.
In the security arena, passwords can be made better, and it is a fairly solvable problem.
However, what about in the real-world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake.
4. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events.
Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.
5. How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
6. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode.
Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.
7. What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?
A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.
A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.
A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link.