Oracle Interview Questions Page 2

Page 1Page 2Page 3Page 4Page 5Page 6Page 7Page 8
1.When should you consider denormalization
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.

2.How can you determine if an Oracle instance is up from the operating system level
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.
3.Users from the PC clients are getting messages indicating :
ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)

What could the problem be

The instance name is probably incorrect in their connection string.

4.Users from the PC clients are getting the following error stack:

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

The Oracle instance is shutdown that they are trying to access, restart the instance.

5.How can you determine if the SQLNET process is running for SQLNET V1? How about V2

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".

6.What file will give you Oracle instance status information? Where is it located

The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.

7.Users aren?t being allowed on the system. The following message is received:

ORA-00257 archiver is stuck. Connect internal only, until freed

What is the problem

The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.

8.Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs

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.

9.You attempt to add a datafile and get:

ORA-01118: cannot add anymore datafiles: limit of 40 exceeded

What is the problem and how can you fix it

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.

10.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



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.

11.Your users get the following error:

ORA-00055 maximum number of DML locks exceeded

What is the problem and how do you fix it

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.

12.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

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.