Teach you how to use Oracle to easily view alarm log errors

After checking the data on the Internet for several days, I tried to solve this problem by comprehensively clearing the contents of alarm logs and building external tables.

1: Back up and clear the contents of the alarm log

Back up the alarm log every day, and then clear it.

1: Back up the alarm log

Under the $ORACLE_HOME/SID/bdump/ directory,
Back up the file alert_ORACLE_ your instance name. LOG by date, such as: alert_ORACLE_orcl_201408111639_bak. LOG.

2: Clear log content

Open the alarm log file and use true > The file name can erase the contents inside

Specific examples:
If I make this setting for the first time, I will back up the existing alarm log first, such as:

--Backup file name: Alarm log information before 16:24 today

[oracle@rac2 bdump]$ cp alert_orcl2.log alert_orcl2_before_201408111624_bak.log

-Empty the alarm log

[oracle@rac2 bdump]$ true > alert_orcl2.log

Now the alarm log is empty.
Switch the archive log to check whether new contents appear in the alarm log:

SQL> alter system switch logfile;
System altered.

Now alert_orcl2.log records the information from 16:24 on August 11, 2014 to the next time you backup and intercept the alarm log.

2: Error in building external table to view alarm log

If 1 user has been established and assigned corresponding permissions, it is OK to directly establish directory objects and establish external tables.

1. Create a directory object

SQL> conn test / 123
Connected.
SQL> create directory bdump as '/oracle/u01/app/oracle/admin/db2/bdump';
Directory created.

2. Create external tables

SQL> create table alert_log(
text varchar2(400)
)organization external
(type oracle_loader
default directory bdump
access parameters
(records delimited by newline
)location('alert_db2.log')
);

3. Test first to see if you can find the contents of alert_db2.log

SQL> select * from alert_log where rownum < 10;
TEXT
--------------------------------------------------------------------------------
Thu Jun 11 00:51:46 2009
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/a
de/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
9 rows selected.

Successful test
Then we test the alarm message 'ORA-%'

SQL> select * from alert_log where text like 'ORA-%';
TEXT
--------------------------------------------------------------------------------
ORA-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2
.dbf'
ORA-27037: unable to obtain file status
ORA-205 signalled during: ALTER DATABASE  MOUNT...
ORA-00301: error in adding log file '/home/oracle/oracle/oradata/testdb/redo01.l
og' - file cannot be created
ORA-27040: file create error
ORA-1501 signalled during: CREATE DATABASE db2
ORA-00200: control file could not be created
TEXT
--------------------------------------------------------------------------------
ORA-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2
.dbf'
ORA-27038: created file already exists
ORA-1501 signalled during: CREATE DATABASE db2
ORA-00200: control file could not be created
ORA-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2
.dbf'
ORA-27038: created file already exists
ORA-1501 signalled during: CREATE DATABASE db2

If your alarm log file only contains today's information (which can be achieved by backing up and clearing the alarm log contents), you can find out what errors occurred on that day through an external table.