[Oracle Notes] 4. Diagnostic files on the Oracle server

Use diagnostic files to monitor routine 1. Diagnostic files diagnostic files are a way to obtain information about database activities, which is used to solve database problems.

Use diagnostic files to monitor routine 1. Diagnostic files diagnostic files are a way to obtain information about database activities, which is used to solve database problems.

Use diagnostic file monitoring routines
I. Diagnostic files
Diagnostic files are a way to obtain information about database activities. They are used to solve database problems and contain information about important events in the database, these files can better manage the database on a daily basis.
There are several main types
1. alertSID. log. File: records routine database operations
2. background trace file: records SMON, PMON,

Important information generated when DBWn and other background processes fail
3. user trace file: records important information generated when a serious user error occurs or when a user enforces a trace file.
Ii. alert. log alarm log File
1. alertSID. log: Each Oracle routine has an Alert log File. If the file has not been created, it will be created during the Startup Process of the routine. The alarm log will continue to grow as the database continues to run. When you diagnose routine operations or errors, you should first view the alarm log file. The Alert Log File also contains pointers to the trace file for more detailed information.
2. The alarm log file records the information.
Time when the database is started or shut down
List of all non-default initialization parameters
Startup of background processes
The log serial number LGWR in which the thread used by the routine is writing information to it
Information about log Switching
Tablespace creation and revocation segments
Issued alert statement
Information about error messages and partition errors such as ORA-600
(TIPS: The alarm log reads the parameter file information when the database is started.

Record the parameters used to start the database at that time. When the parameter file is lost, you can read the correct parameter information used in the previous time from the alarm log file to create a pfile file, to start the database .)
3. Each entry recorded in the Alert Log File carries a timestamp associated with it
4. Only DBA has the permission to manage it
5. The storage location is defined by background_dump_dest. You can run the showparameter background_dump_dest command to view the location of the alarm log file in the operating system.
Iii. Background tracking files
Background trace files are used to record background processes (such as SMON, PMON, DBWn, and other

Background Program) the background trace file is created only when an error occurs that requires the tracking file to be written. After you create a background trace file, the file contains the header information of the database server and the operating system.
The naming rule is sid_processname_PID.trc.
The storage location is also defined by background_dump_dest. You can run the showparameter background_dump_dest command to view the operating system of the background trace file.

.
Iv. User tracking files
1. User tracking files can be generated by user processes and server processes, which mainly contain statistics of SQL statements executed by users and user error information.
2. Create
3. The storage location is defined by user_dump_dest. You can use show parameter

Run the user_dump_dest command to view the location of the User tracking file in the operating system.
4. Its size is defined by max_dump_file_size.
5. The naming rule is sid_ora_PID.trc.
6. commands for enabling or disabling user trace files
Session level: only the user trace file of the current user session is enabled.
Alert session set SQL _trac = true
Routine level: the user tracking file of the entire routine is started. If it is a production database with a large load, it is not recommended to enable the User tracking file at the routine level, because the User tracking file records all user operations, if there are many operations, the database performance will be affected, and after a long time, the User tracking file will become very large, then it occupies a large disk capacity.
SQL _trace = true