ORACLE alarm log file, oracle alarm

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.