When should you consider denormalization

Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.

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.

Users from the PC clients are getting messages indicating?

ORA-06114: (Cnct err, can’t get err txt. See Servr Msgs & Codes Manual)

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

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

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.

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.

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.

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.

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.

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.