ORACLE alarm log file, oracle alarm
Alarm log Introduction
The alarm log file is a special trace file ). The alert log File is generally named alert _ <SID>. log, where SID is the name of the ORACLE database instance. Database alarm logs record message and error information in chronological order.
Alert Log location
In ORACLE 10 Gb, The BACKGROUND_DUMP_DEST parameter determines the location of the alarm log, but the file name of the alarm log cannot be modified. The alarm log name is: alert _ <SID>. log, where <SID> is the name of the instance. The BACKGROUND_DUMP_DEST parameter is dynamic.
SQL> show parameter background_dump_dest;
NAME TYPE VALUE
--------------------- ----------- ------------------------------
background_dump_dest string /u01/app/oracle/admin/GSP/bdump
SQL>
Alarm logs and all background trace files are written to the directory specified by the BACKGROUND_DUMP_DEST parameter.
In ORACLE 11g and ORACLE 12c, the location of the alarm log file has changed. This is mainly because of the introduction of ADR (Automatic Diagnostic Repository: a directory that stores database Diagnostic logs and trace files). You can view the directory location of ADR through v $ diag_info System View. As shown below (ORACLE 12c)
SQL> select * from v$diag_info;
INST_ID NAME VALUE CON_ID
------- -------------------- -------------------------------------------------- -------
1 Diag Enabled TRUE 0
1 ADR Base /u01/app/oracle 0
1 ADR Home /u01/app/oracle/diag/rdbms/ignite/epps 0
1 Diag Trace /u01/app/oracle/diag/rdbms/ignite/epps/trace 0
1 Diag Alert /u01/app/oracle/diag/rdbms/ignite/epps/alert 0
1 Diag Incident /u01/app/oracle/diag/rdbms/ignite/epps/incident 0
1 Diag Cdump /u01/app/oracle/diag/rdbms/ignite/epps/cdump 0
1 Health Monitor /u01/app/oracle/diag/rdbms/ignite/epps/hm 0
1 Default Trace File /u01/app/oracle/diag/rdbms/ignite/epps/trace/epps_ 0
ora_13810.trc
1 Active Problem Count 0 0
1 Active Incident Coun 0 0
t
11 rows selected.
As shown above, the directory corresponding to Diag Trace is the directory where the Alert Log File in text format is located, and the Directory corresponding to Diag Alert is the warning log in XML format (corresponding to log_x.xml)
[oracle@gettestlnx01 trace]$ pwd
/u01/app/oracle/diag/rdbms/ignite/epps/trace
[oracle@gettestlnx01 trace]$ ls alert_epps.log
alert_epps.log
[oracle@gettestlnx01 trace]$ cd ../alert/
[oracle@gettestlnx01 alert]$ pwd
/u01/app/oracle/diag/rdbms/ignite/epps/alert
[oracle@gettestlnx01 alert]$ ls
log_1.xml log_2.xml log_3.xml log_4.xml log_5.xml log_6.xml log_7.xml log_8.xml log_9.xml log.xml
Alarm log Content:
So the alarm log is critical and important. What information does the alarm log contain? The alarm log contains the following information. For example, some ORA errors play an extremely important role in monitoring databases.
1: All internal error (ORA-600) information, block damage error (ORA-1578) information, and deadlock error (ORA-60) information and so on.
2: management operations, such as CREATE, ALTER, and DROP statements, as well as information about database startup, shutdown, and log archiving.
2.1 All operations involving physical structures: for example, the alter database Command for creating, deleting, renaming, and online redo log files, it also involves allocating the data file size and online and offline operations on the data file.
2.2 tablespace operations, such as the DROP and CREATE commands. In addition, the operations include placing and removing tablespaces in the hot backup mode for user-managed backup.
3: messages and error messages about functions related to the shared server or scheduling process.
4: errors during Automatic refresh of materialized views.
5. Dynamic Parameter Modification information.
Alarm log monitoring:
Since alarm logs are so important that we cannot manually view the alarm log files at any time, we must monitor the alarm logs. What are the solutions for monitoring alarm logs? Below is a summary
Solution 1: a solution provided by Master Tom (only applicable to ORACLE 10 Gb) reads the alarm log file information into the global temporary table, then we can customize some SQL statements to query the alarm log information.
create global temporary table alert_log
( line int primary key,
text varchar2(4000)
)
on commit preserve rows
/
create or replace procedure load_alert
as
l_background_dump_dest v$parameter.value%type;
l_filename varchar2(255);
l_bfile bfile;
l_last number;
l_current number;
l_start number := dbms_utility.get_time;
begin
select a.value, 'alert_' || b.instance || '.log'
into l_background_dump_dest, l_filename
from v$parameter a, v$thread b
where a.name = 'background_dump_dest';
execute immediate
'create or replace directory x$alert_log$x as
''' || l_background_dump_dest || '''';
dbms_output.put_line( l_background_dump_dest );
dbms_output.put_line( l_filename );
delete from alert_log;
l_bfile := bfilename( 'X$ALERT_LOG$X', l_filename );
dbms_lob.fileopen( l_bfile );
l_last := 1;
for l_line in 1 .. 50000
loop
dbms_application_info.set_client_info( l_line || ', ' ||
to_char(round((dbms_utility.get_time-l_start)/100, 2 ) )
|| ', '||
to_char((dbms_utility.get_time-l_start)/l_line)
);
l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
exit when (nvl(l_current,0) = 0);
insert into alert_log
( line, text )
values
( l_line,
utl_raw.cast_to_varchar2(
dbms_lob.substr( l_bfile, l_current-l_last+1,
l_last ) )
);
l_last := l_current+1;
end loop;
dbms_lob.fileclose(l_bfile);
end;
/
However, if the database goes down, these error messages cannot be obtained. Compared with solution 3 (from operating system monitoring alarm logs), some specific scenarios are not applicable. In addition, when the log file is large and the monitoring alarm log information is frequent, unnecessary IO operations are generated.
Solution 2: use an external table to view the alarm log file. It is quite convenient. Then, you can use custom SQL statements to query error information.
SQL> create or replace directory bdump as '/u01/app/oracle/admin/GSP/bdump';
Directory created.
SQL> create table alert_logs
2 (
3 text varchar2(2000)
4 )
5 organization external
6 (
7 type oracle_loader
8 default directory bdump
9 access parameters
10 (
11 records delimited by newline
12 fields
13 reject rows with all null fields
14 )
15 location
16 (
17 'alert_GSP.log'
18 )
19 )
20 reject limit unlimited;
Table created.
SQL> select * from alert_logs;
TEXT
--------------------------------------------------------------------------------
Thu Aug 7 14:50:28 2014
Thread 1 advanced to log sequence 14
Current log# 1 seq# 14 mem# 0: /u01/app/oracle/oradata/GSP/redo01.log
SQL>
Solution 3: I archive my previous blog-monitoring ORACLE Database alarm logs, which describes how to archive and monitor alarm logs. These scripts are indeed very effective for me to monitor the operation of the database.
Alarm log Archiving
If the alarm log is not archived in time, the alarm log file will become very large after a long time. Viewing and reading the alarm log will cause additional IO overhead. Therefore, you should archive the Alert Log files on a daily basis and keep them for a period of time (for example, 90 days). The files will be deleted after the specified time.
Can alarm logs be deleted? A former colleague said that all the trace files under the background_dump_dest directory can be deleted in addition to alarm logs. If you delete the alarm log file, unexpected errors may occur. I am skeptical, delete the alarm log on the test server. If no effect is found after verification, the system will generate the alarm log file again (the alarm log file will not be generated immediately at the time, instead, a new alarm log file is generated when a process writes a record to the alarm log ).
References:
Https://asktom.oracle.com/pls/asktom/f? P = 100: 11: 0: P11_QUESTION_ID: 1352202934074
Role and storage location of oracle warning Log File ALERTLOG
Record System logs, such as log switching records and system events such as modifying system parameters.
The location is in the path specified by the background_dump_dest parameter, which is generally % ORACLE_BASE % \ admin \ % ORACLE_SID % \ bdump
How to back up the oracle alarm log and clean it up
AlertSID of oracle. the log file is stored in the bdump directory of the oracle database. If the file does not exist, the oracle database automatically creates a new file and writes the major changes to the file.
In this case, backup is simple. You can manually back up the data, cp to a specific folder, or mv is alertSID. log. you can also use the cron job to perform automatic and scheduled backup on the server, so as to avoid the slow opening of the alert Log File.