Tips_25 Diagnosing Oracle Database Issues

Tips_25 Diagnosing Oracle Database Issues

■■The Alert Log
･location：BACKGOUND_DUMP_DEST
･name： alert_.log
■The informational messages in the alert log will include these items：
･
①All startup and shutdown commands, with the time and mode of the operation.The startup times will include the timings of the transitions from NOMOUNT to MOUNT to OPEN, which are of vital importance for assessing whether the mean-time-to-recover figure matches standards agreed with the users.
②creating, dropping, and renaming datafiles and redo logs. Resize operations on datafiles and taking datafiles on and offline are also recorded.
③Tablespace operations, such as DROP and CREATE. Also putting tablespaces into and out of hot backup mode for user-managed backups.
④All log switches and archives, including the names of the affected files.
⑤The nondefault initialization parameters used to start the instance. Also any subsequent ALTER SYSTEM commands that change parameters.
･
Thus the alert log contains a continuous history of operations affecting the structure of the database and the instance. It does not include any standard SQL statements, such as DML or DDL commands. It will, however, include some warnings and errors.
The more common of these follow:
⑥Checkpoint incomplete Make no mistake.checkpoints always complete.
But if your database is performing log switches so frequently that Oracle wants to start a checkpoint before the previous checkpoint has completed, then this error will be signaled. It is indicative of logfiles being far too small and will result in the database hanging until the checkpoint does finish.
⑦Unable to open file This will occur at the transition from NOMOUNT to MOUNT if there is a problem with any copy of the controlfile, or while transitioning from MOUNT to OPEN if there is a problem with an online logfile member or a datafile.
⑧Corrupt block Not a nice message! If a block of a datafile is found to be damaged, its address (file number and the block number within the file) is written out.
⑨Problems with archiving These include an archive log destination being full or unavailable.
⑩Deadlocks Such events occur when badly written software sets

■■Background Trace Files
trace files always mean an error.often a critical one。
The only control the DBA has over trace files is where to put them, and a limit on their maximum size.
･BACKGROUND_DUMP_DEST
･MAX_DUMP_FILE_SIZE  defaults to UNLIMITED
Note that this maximum file size does not apply to the alert log; that will continue to grow until you trim or delete it.

■■Server-Generated Alerts

 

********

■Editing Thresholds

eg:

1. Connect to your database with SQL*Plus as user SYSTEM, and create a tablespace that is 1MB big.
SQL> create tablespace small datafile 'small.dbf' size 1m;
2.Set the warning and critical alerts for this tablespace to 50 percent and 75 percent respectively.
execute dbms_server_alert.set_threshold(
metrics_id=>dbms_server_alert.tablespace_pct_full, warning_operator=>dbms_server_alert.operator_ge, warning_value=>'50', critical_operator=>dbms_server_alert.operator_ge, critical_value=>'75',
observation_period=>1,
consecutive_occurrences=>2,
instance_name=>null, object_type=>dbms_server_alert.object_type_tablespace, object_name=>'SMALL'
);
• dbms_server_alert.tablespace_pct_full Nominates which metric to monitor
• dbms_server_alert.operator_ge A comparison operator, “greater than or equal to”
• dbms_server_alert.object_type_tablespace The type of object being monitored
■User Trace Files
Location:USER_DUMP_DEST
・Instance-Level SQL Tracing
SQL> alter system set sql_trace=true;
・Session-Level SQL Tracing
①SQL> alter session set sql_trace=true;
②SQL> select sid,serial# from v$session where username='JOHN';
SQL> execute dbms_monitor.session_trace_enable(session_id=>162,serial_num=>14);

＊DBMS_MONITOR package
tracing levels:
• Session level
• Module level
• Client ID level
• Service level
• Action