I,OracleTracking File
There are three types of Oracle tracking files:
One is the background alarm log file, which recordsDatabaseActivities of background processes during startup, shutdown, and running, such as creating tablespaces, creating rollback segments, some alter commands, log switching, and error messages. When the database fails, you should first check the file, but the information in the file is not necessarily related to any error status. The background alarm log file is saved in the directory specified by the background_dump_dest parameter. The file format is sidalrt. log.
Another type is the background trace files created by background processes such as dbwr, lgwr, and SMON. The background trace file is generated based on the running status of the background process. The background trace file is also saved in the directory specified by the background_dump_dest parameter. The file format is siddbwr. TRC and sidsmon. TRC.
Another type is the user process connected to Oracle (ServerProcesses. These files are generated only when errors occur during user sessions. In addition, you can execute an oracle tracking event (see the following section) to generate this type of file. The User tracking file is saved in the directory specified by the user_dump_dest parameter. The file format is oraxxxxx. TRC, Xxxxx is the process number (or thread number) of the file to be created ).
2. Oracle tracking events
Oracle provides a class of commands, dump information contained in various Oracle internal structures to the tracking file so that users can solve various faults based on the file content. There are two ways to set the tracking event. One is to set the event in the init. ora file, so that after the database is opened, all sessions will be affected. The format is as follows:
event = "eventnumber trace name eventname [forever,] [level levelnumber]: ...... "
with the symbol, you can set multiple events consecutively or use the event continuously to set multiple events.
another method is to use the alter session set events command during the session process, which only affects the current session. The setting format is as follows:
alter session set events '[eventnumber | immediate] Trace name eventname [forever] [, level levelnumber]:... '
with the symbol, you can set multiple events consecutively or use alter session set events consecutively to set multiple events. Format description: eventnumber refers to the event number that triggers dump. The event number can be an oracle error number (the specified event is tracked when an error occurs) or an internal oralce event number, the internal event number is between 10000 and 10999, and cannot be used with the immediate keyword.
The immediate keyword indicates that the specified structure is immediately dumped to the trace file after the command is issued. This keyword is only used in the alter session Statement and cannot be used with the eventnumber or forever keyword.
Trace name is a keyword.
Eventname refers to the event name (see the following section), that is, the actual structure name for dump. If eventname is context, internal event numbers are used for tracking.
The forever keyword indicates that the event remains valid during the instance or session period and cannot be used with immediate.
Level is the event-level keyword. But the error stack (errorstack) does not have a level.
Levelnumber indicates the event level number. Generally, from 1 to 10, 1 indicates only the dump structure header information, and 10 indicates all the information of the dump structure. 1. buffers event: DB Buffer Structure in the dump SGA Buffer
Alter session set events 'immediate trace name buffers level 1'; -- indicates the dump buffer header. 2. blockdump event: dump data file, index file, and rollback segment file structure
Alter session set events 'immediate trace name blockdump level 100'; -- indicates that the dump block address is 66666.
After Oracle 8, the command has been changed:
Alter system dump datafile 11 Block 9; -- indicates that the dump data file number is the 9th data blocks in column 11. 3. controlf event: Dump control file structure
Alter session set events 'immediate trace name controlf level 10'; -- all contents of the dump control file. 4. Locks event: the lock information of the dump LCK Process
Alter session set events 'immediate trace name locks level 5 '; 5. redohdr event: Dump redo log header information
Alter session set events 'immediate trace name redohdr level 1'; -- indicates the control file entry in the dump redo log header.
Alter session set events 'immediate trace name redohdr Level 2'; -- indicates the general file header of the dump redo log.
Alter session set events 'immediate trace name redohdr level 10'; -- indicates the full file header of the dump redo log. Note: dump of redo logs can use the following statement:
Alter system dump logfile 'logfilename '; 6. loghist event: log history items in the dump Control File
Alter session set events 'immediate trace name loghist level 1'; -- indicates that only the earliest and latest log history items are dumped.
When levelnumber is greater than or equal to 2, it indicates the levelnumber of 2 to the power of log history.
Alter session set events 'immediate trace name loghist level 4'; -- indicates 16 log history items in dump. 7. file_hdrs event: Dump header information of all data files
Alter session set events 'immediate trace name file_hdrs level 1'; -- indicates the control file entry in the dump header of all data files.
Alter session set events 'immediate trace name file_hdrs Level 2'; -- indicates the general file header of all data files in dump.
Alter session set events 'immediate trace name file_hdrs level 10'; -- indicates the full file header of all data files in dump. 8. errorstack event: Dump error stack information. Generally, when an oracle error occurs, the foreground process will get an error message, but in some cases the error message is not obtained. You can use this method to get an oracle error.
Alter session set events '1970 trace name errorstack forever '; -- indicates dump error stack and process stack when a 604 error occurs. 9. systemstate event: dump all system statuses and process statuses
Alter session set events 'immediate trace name systemstate level 10'; -- indicates all system and process statuses of dump. 10. coalesec event: Dump specifies the free interval in the tablespace
When levelnumber is expressed in hexadecimal notation, two high bytes represent the number of free intervals, and two low bytes represent the tablespace number. For example, 0x00050000 represents the five free intervals in the dump system tablespace, to convert to decimal format is 327680, that is:
Alter session set events 'immediate trace name coalesec level 100 '; 11. processsate event: Dump Process status
Alter session set events 'immediate trace name processsate level 10 '; 12. library_cache event: Dump library Cache Information
Alter session set events 'immediate trace name library_cache level 10 '; 13. heapdump event: Information in dump PGA, SGA, and UGA
Alter session set events 'immediate trace name heapdump level 1 '; 14. row_cache event: Information in the dump Data Dictionary Buffer
Alter session set events 'immediate trace name row_cache level 1 ';
3. Internal event no. 1. 10013: used to monitor transactions
Restore 2. 10015: dump the Undo segment Header
Event = "10015 trace name context forever" 3. 10029: used to provide login information during the session 4. 10030: used to provide logout information during the session 5. 10032: Dump sorting statistics 6. 10033: dump the statistics of sort Growth 7. 10045: Tracking freelist management operations 8. 10046: Tracking
SQLStatement
Alter session set events '2017 trace name context forever, level 4'; -- Trace SQL statements and display Bound variables
Alter session set events '2014 trace name context forever, level 8'; -- Trace SQL statements and display wait events 9. 10053: Dump optimization policy 10. 10059: Create and clear errors in the simulated redo log 11. 10061: prevents the SMON process from clearing the temporary segment at startup 12. 10079: dumping SQL * Net statistics 13. 10081: dump high-water mark changes 14. 10104: Dump hash connection statistics 15. 10128: Dump partition rest Information 16. 10200: Dump consistent read information 17. 10201: dumping undo applications in consistent read 18. 10209: Allow simulating errors in the control file 19. 10210: trigger the data block check event
Event = "10210 trace name context forever, level 10" 20. 10211: Index check event triggered 21. 10213: simulate a crash after the write control file 22. 10214: Simulate write errors in the control file
Levelnumber from 1 to 9 indicates that an incorrect block number is generated. If it is greater than or equal to 10, an error occurs for each control file. 23. 10215: simulate a read error in the control file 24. 10220: Dump undo header changes 25. 10221; dump undo changes 26. 10224: Separation and deletion of dump Indexes 27. 10225: dump is dictionary-based
ManagementInterval change 28. 10229: Simulate I/O errors on data files 29. 10231: Set to ignore damaged data blocks during full table Scan
Alter session set events '10231 trace name context off'; -- disable data block check during the session
Event = "10231 trace name context forever, level 10" -- check the data blocks read by any process into SGA. 30. 10232: dumps data blocks that are set to soft-corruption (when dbms_repair package is set or db_block_checking is set to true) to the trace file. 31. 10235: used for memory heap check
Alter session set events '10235 trace name context forever, level 1 '; 32. 10241: dumping Remote SQL Execution 33. 10246: Tracking pmon Processes 34. 10248: trace the dispatch Process 35. 10249: Tracking MTS Processes 36. 10252: simulate a data file header error 37. 10253: Simulate an error in writing a redo log file 38. 10262: Memory leakage is allowed during connection
Alter session set events '2014 trace name context forever, level 100'; -- 10262 bytes of Memory leakage is allowed 39. 10270: Dump shared cursor 40. 10285: the head of the simulated control file is damaged. 41. 10286: simulated control file opening Error 42. 10287: Simulated archiving Error 43. 10357: debug the direct path Mechanism 44. 10500: trace the SMON Process 45. 10608: Tracking the creation of Bitmap indexes 46. 10704: Tracking enqueues 47. 10706: tracking global enqueues 48. 10708: Tracking
RAC Buffer cache 49. 10710: Tracking access to Bitmap indexes 50. 10711: Tracking bitmap index merge operations 51. 10712: Tracking bitmap index or operations 52. 10713: Tracking Bitmap indexes and operations 53. 10714: trace bitmap index minus operations 54. 10715: Tracking bitmap index conversion to rowid 55. 10716: Tracking the compression and decompression of Bitmap indexes 56. 10719: Tracking the modification of Bitmap indexes 57. 10731: Tracking cursor Declaration 58. 10928: trace PL/SQL Execution 59. 10938: dumping PL/SQL Execution statistics syntax may vary depending on different versions, but most of them are still available.
