View Oracle Database diagnostic files (logs)

Oracle Database Diagnostic File (log) view Diagnostic File (Diagnostic File) 1: Role of the Diagnostic File Diagnostic files: contains information about Major Events encountered in the background. It is used for parsing issues and is used for daily log file management. 2: diagnostic file logs are classified into two categories: 1: alterSID. log ----- background trace files (Background process trace file) 2: trace files --- ----- user trace file (user trace file) 1: Name the background trace files file: naming method: SID_processname_PID.trc. trc 2: Name the user trace files file SID_ora_PID.trc: SID_ora _ process number. trc 3: For alertSID. log Description: This file is for record: 1: record some operation commands 2: record the results of major events 3: and daily operation information 4: used to diagnose database errors each entry has a time stamp (timestamp) and it is associated with this file must be The location for managing this file is: BACKGROUND_DUMP_DEST. You can use show parameter dump to view the location of this file. This file also contains the database startup information equivalent to pfile or spfile. Log on as an administrator: 2. perform the following operations: Log On With sysdba and run: [SQL] SQL> show parameter dump NAME TYPE VALUE =----------- unzip background_core_dump string partial background_dump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \ trace core_dump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \ cdump max_dump_file_size string unlimited limit string none user_d Ump_dest string d: \ app \ topwqp \ diag \ rdbms \ orcl \ trace can view the path information of these files. Based on the explicit information, I found the location of my file: the directory structure is as follows: The following describes how to record the information to these log files. Some switches are required. If not, record only a little bit of information: two methods allow the user to tracing 1: session level: Use the following command: alter sesssion set SQL _TRACE = TRUE; the second is to execute the following stored procedure: dbms_system.SET_ SQL _TRACE_IN_SESSION the second method is at the instance level: Set the initialization parameter: SQL _TRACE = TRUE. Generally, the session level is used. If the instance level is set, the log file is too large. You can use the information in the alterSID. log File to create a pfile or spfile file to start the database. Modify SQL _trace to true at the session level to view the corresponding information in the corresponding file of user_dump_dest. [SQL] SQL> conn/as sysdba is connected. SQL> alter session set SQL _trace = true; the session has been changed. After execution: view the information of the orcl_ora_7188.trc file PS: if you do not know which file is used, delete all the files in this directory. Then, run the SQL command to view the generated file: view the file information as follows: very detailed execution information: for example, the following information is generated in the select * from dual file: [plain] *** 22:58:20. 776 ================================ parsing in cursor #1 len = 18 dep = 0 uid = 0 oct = 3 lid = 0 tim = 9184375464 hv = 942515969 ad = '232363f8' sqlid = 'a5s9fhw2v9s1 'select * from dual end of stmt parse #1: c = 0, e = 32, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 918437545 8 EXEC #1: c = 0, e = 50, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 9184376205 FETCH #1: c = 0, e = 109, p = 0, cr = 3, cu = 0, mis = 0, r = 1, dep = 0, og = 1, tim = 9184376423 STAT #1 id = 1 cnt = 1 pid = 0 pos = 1 obj = 115 op = 'table access full dual (cr = 3 pr = 0 pw = 0 time = 0 us cost = 2 size = 2 card = 1) 'fetch #1: c = 0, e = 2, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 0, tim = 9184376893 is a detailed explanation of the execution of this SQL statement. The following part of the execution information is displayed: [plain] *** 22:58:20. 776 ============== ========== Parsing in cursor #1 len = 18 dep = 0 uid = 0 oct = 3 lid = 0 tim = 9184375464 hv = 942515969 ad = '232363f8 'sqlid = 'a5ks9fhw2v9s1 'select * from dual end of stmt parse #1: c = 0, e = 32, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 9184375458 EXEC #1: c = 0, e = 50, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 9184376205 FETCH #1: c = 0, e = 109, p = 0, cr = 3, cu = 0, mis = 0, r = 1, dep = 0, og = 1, tim = 9184376423 STAT #1 id = 1 cnt = 1 pid = 0 pos = 1 obj = 115 Op = 'table access full dual (cr = 3 pr = 0 pw = 0 time = 0 us cost = 2 size = 2 card = 1) 'FETCH #1: c = 0, e = 2, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 0, tim = 9184376893 *** 23:15:15. 474 ================================ parsing in cursor #1 len = 289 dep = 0 uid = 0 oct = 3 lid = 0 tim = 10199053291 hv = 2462394820 ad = '23366e0' sqlid = '7cfz5wy9caaf4 'select name NAME_COL_PLUS_SHOW_PARAM, DECODE (TYPE, 1, 'boolean', 2, 'string', 3, 'integer', 4 ,' File ', 5, 'number', 6, 'Big integer', 'unknown ') TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM from v $ parameter where upper (NAME) like upper (: NMBIND_SHOW_OBJ) order by NAME_COL_PLUS_SHOW_PARAM, rownum end of stmt parse #1: c = 0, e = 438, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 1, tim = 10199053285 ================================ parsing in cursor #2 len = 210 dep = 1 uid = 0 oct = 3 lid = 0 tim = 10199056088 hv = 864012087 ad = '000000' sqli D = 'rule' select/* + rule */bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp #, sample_size, minimum, maximum, distcnt, lowval, hival, density, col #, spare1, spare2, avgcln from hist_head $ where obj # =: 1 and intcol # =: 2 end of stmt parse #2: c = 0, e = 568, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 3, tim = 10199056084 EXEC #2: c = 0, e = 1024, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 1, og = 3, tim = 10199057412 FETCH #2: c = 0, e = 30, p = 0, cr = 2, cu = 0, mis = 0, r = 0, dep = 1, og = 3, tim = 10199057533 STAT #2 id = 1 cnt = 0 pid = 0 pos = 1 obj = 411 op = 'table access by index rowid HIST_HEAD $ (cr = 2 pr = 0 pw = 0 time = 0 us) 'stat #2 id = 2 cnt = 0 pid = 1 pos = 1 obj = 413 op = 'index range scan I _HH_OBJ # _ INTCOL # (cr = 2 pr = 0 pw = 0 time = 0 us) '================================ parsing in cursor #2 len = 210 dep = 1 uid = 0 oct = 3 lid = 0 tim = 10199057848 hv = 864012087 ad = '000000' sqlid = '96g93hntr Zjtr 'select/* + rule */bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp #, sample_size, minimum, maximum, distcnt, lowval, hival, density, col #, spare1, spare2, avgcln from hist_head $ where obj # =: 1 and intcol # =: 2 end of stmt exec #2: c = 0, e = 25, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 1, og = 3, tim = 10199057844 FETCH #2: c = 0, e = 13, p = 0, cr = 2, cu = 0, mis = 0, r = 0, dep = 1, og = 3, tim = 10199058128 EXEC #1: c = 0, e = 7034, p = 0, cr = 4, cu = 0, m Is = 1, r = 0, dep = 0, og = 1, tim = 10199060756 FETCH #1: c = 15600, e = 13882, p = 0, cr = 0, cu = 0, mis = 0, r = 1, dep = 0, og = 1, tim = 10199075783 FETCH #1: c = 0, e = 21, p = 0, cr = 0, cu = 0, mis = 0, r = 5, dep = 0, og = 1, tim = 10199076326 STAT #1 id = 1 cnt = 6 pid = 0 pos = 1 obj = 0 op = 'sort order by (cr = 0 pr = 0 pw = 0 time = 0 us cost = 2 size = 2115 card = 1) 'stat #1 id = 2 cnt = 6 pid = 1 pos = 1 obj = 0 op = 'count (cr = 0 pr = 0 pw = 0 time = 8 us) 'stat #1 id = 3 cnt = 6 pid = 2 pos = 1 obj = 0 Op = 'hash JOIN (cr = 0 pr = 0 pw = 0 time = 6 us cost = 1 size = 2115 card = 1) 'stat #1 id = 4 cnt = 35 pid = 3 pos = 1 obj = 0 op = 'fixed table full x $ KSPPI (cr = 0 pr = 0 pw = 0 time = 70 us cost = 0 size = 81 card = 1) 'stat #1 id = 5 cnt = 1915 pid = 3 pos = 2 obj = 0 op = 'fixed table full x $ KSPPCV (cr = 0 pr = 0 pw = 0 time = 19 us cost = 0 size = 203400 card = 100) 'The content in alter_SID.log is as follows: Today's: note that this file contains the Oracle startup parameter information: You can use this information to configure the spfile or pfile file to try to start data with this configuration file. [Plain] Thu Jun 13 22:13:43 2013 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_1 parameter default value as D: \ app \ topwqp \ product \ 11.1.0 \ db_1 \ RDBMS Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on. IMODE = br ilat = 18 LICENSE_MAX_US ERS = 0 SYS auditing is disabled Starting up oracle rdbms Version: 11.1.0.6.0. using parameter settings in server-side spfile D: \ APP \ TOPWQP \ PRODUCT \ 11.1.0 \ DB_1 \ DATABASE \ SPFILEORCL. ORA System parameters with non-default values: processes = 150 memory_target = 412 M control_files = "D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL01.CTL" control_files = "D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL02.CTL "control_files =" D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL03.CTL "db_block_size = 8192 compatible =" 11.1.0.0.0 "db_recovery_file_dest =" D: \ app \ topwqp \ flash_recovery_area "placement = 2G fast_start_mttr_target = 0 undo_tablespace =" UNDOTBS1 "placement =" EXCLUSIVE "db_domain =" "dispatchers =" (PROTOCOL = TCP) (SERVICE = orclXDB) "audit_file_dest =" D: \ APP \ TOPWQP \ ADMIN \ ORCL \ ADUMP "audit_trail = "DB" db_name = "orcl" open_cursors = 300 diagnostic_dest = "D: \ APP \ TOPWQP" Thu Jun 13 22:13:46 2013 PMON started with pid = 2, OS id = 1888 Thu Jun 13 22:13:46 2013 VKTM started with pid = 3, OS id = 4296 at elevated priority Thu Jun 13 22:13:46 2013 DIAG started with pid = 4, OS id = 6804 VKTM running at (20) MS precision Thu Jun 13 22:13:46 2013 DBRM started with pid = 5, OS id = 7360 Thu Jun 13 22:13:46 2013 PSP0 started with pid = 6, OS id = 648 Thu Jun 13 22:13:46 2013 DSKM started with pid = 7, OS id = 7460 Thu Jun 13 22:13:46 2013 DIA0 started with pid = 8, OS id = 1592 Thu Jun 13 22:13:46 2013 MMAN started with pid = 7, OS id = 4512 Thu Jun 13 22:13:46 2013 DBW0 started with pid = 9, OS id = 6920 Thu Jun 13 22:13:46 2013 LGWR started with pid = 10, OS id = 3712 Thu Jun 13 22:13:46 2013 CKPT started with pid = 11, OS id = 6 512 Thu Jun 13 22:13:46 2013 SMON started with pid = 12, OS id = 7992 Thu Jun 13 22:13:46 2013 RECO started with pid = 13, OS id = 7056 Thu Jun 13 22:13:46 2013 MMON started with pid = 14, OS id = 7844 starting up 1 dispatcher (s) for network address '(ADDRESS = (PARTIAL = YES) (PROTOCOL = TCP ))'... thu Jun 13 22:13:46 2013 MMNL started with pid = 15, OS id = 6284 starting up 1 shared server (s )... ORACLE_BASE from envir Onment = D: \ app \ topwqp Thu Jun 13 22:13:46 2013 alter database mount exclusive Setting recovery target incarnation to 2 Successful mount of redo thread 1, with mount id 1345846942 Database mounted in Exclusive Mode Lost write protection disabled Completed: alter database mount exclusive alter database open Beginning crash recovery of 1 threads parallel recovery started with 3 processes Started red O scan Thu Jun 13 22:13:56 2013 Completed redo scan 4095 redo blocks read, 510 data blocks need recovery Started redo application at Thread 1: logseq 434, block 67636 Recovery of Online Redo Log: thread 1 Group 2 Seq 434 Reading mem 0 Mem #0: D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO02.LOG Completed redo application Completed crash recovery at Thread 1: logseq 434, block 71731, scn 5101264 510 data blocks r Ead, 510 data blocks written, 4095 redo blocks read Thu Jun 13 22:14:00 2013 Thread 1 advanced to log sequence 435 Thread 1 opened at log sequence 435 Current log #3 seq #435 mem #0: D: \ APP \ TOPWQP \ ORADATA \ ORCL \ REDO03.LOG Successful open of redo thread 1 MTTR advisory is disabled because failure is not set Thu Jun 13 22:14:01 2013 SMON: enabling cache recovery onlined Un Do Tablespace 2. verifying file header compatibility for 11g tablespace encryption .. verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK Opening with internal Resource Manager plan Starting background process FBDA Starting background process SMCO Thu Jun 13 22:14:06 2013 FBDA started with pid = 22, OS id = 4524 Thu Ju N 13 22:14:06 2013 SMCO started with pid = 23, OS id = 6344 Thu Jun 13 22:14:07 2013 replication_dependency_tracking turned off (no async multimaster replication found) starting background process QMNC Thu Jun 13 22:14:09 2013 QMNC started with pid = 25, OS id = 7168 Thu Jun 13 22:14:20 2013 db_recovery_file_dest_size of 2048 MB is 0.00% used. this is a user-specified limit on the amount of space that wil L be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. thu Jun 13 22:14:30 2013 Completed: alter database open Thu Jun 13 22:18:50 2013 Starting background process CJQ0 Thu Jun 13 22:18:50 2013 CJQ0 started with pid = 28, OS id = 5912 Setting Resource Manager plan SCHEDULER [0x2C0C]: DEFAULT_MAINTENANCE_PLA N via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Thu Jun 13 22:19:07 2013 Thu Jun 13 22:19:07 2013 Logminer Bld: Lockdown Complete. DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 5102149 Thu Jun 13 22:19:11 2013 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_ SQL _TUNING_TASK" Thu Jun 13 22:20:29 2013 End automatic SQL Tuning Advisor r The startup parameters of un for special tuning task "SYS_AUTO_ SQL _TUNING_TASK" oracle are as follows: System parameters with non-default values: processes = 150 memory_target = 412 M control_files = "D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL01.CTL "control_files =" D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL02.CTL "control_files =" D: \ APP \ TOPWQP \ ORADATA \ ORCL \ CONTROL03.CTL "db_block_size = 8192 compatible =" 11.1.0.0.0 "db_recovery_file_dest =" D: \ App \ topwqp \ flash_recovery_area "placement = 2G fast_start_mttr_target = 0 undo_tablespace =" UNDOTBS1 "placement =" EXCLUSIVE "db_domain =" "dispatchers =" (PROTOCOL = TCP) (SERVICE = orclXDB) "audit_file_dest =" D: \ APP \ TOPWQP \ ADMIN \ ORCL \ ADUMP "audit_trail =" DB "db_name =" orcl "open_cursors = 300 diagnostic_dest =" D: \ APP \ TOPWQP "can be used for pfile or spfile content.