Teach you how to easily view alert log errors with Oracle _oracle

Check the information on the internet for a few days, try to comprehensively clear the alarm log content and the way to build the external table to solve this problem.

One: Back up and clear the alert log contents

The daily alarm log is backed up and then cleared.

1: Back up the alert log

In the $oracle_home/sid/bdump/directory,
Backup by date Alert_oracle_ your instance name. Log this file, such as: Alert_oracle_orcl_201408111639_bak. LOG.

2: Clear Log contents

Open the alert log file and use True > filename to clear out the contents

Specific examples:
If, for the first time, I do this, I'll back up the existing alarm log, such as:

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

[ORACLE@RAC2 bdump]$ CP Alert_orcl2.log Alert_orcl2_before_201408111624_bak.log

-Empty alert Log

[Oracle@rac2 bdump]$ true > Alert_orcl2.log

The alarm log is now empty.
Switch the archive log to check for new content in the alert log:

sql> alter system switch logfile;
System altered.

Now Alert_orcl2.log record is August 11, 2014 after 16:24 to your next to the alarm log backup, intercept the information before.

Second: building an external table to view alert log errors

If you have established a user and assigned the appropriate permissions, it is OK to create the directory object directly and create an external table.

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 an external table

Sql> CREATE TABLE Alert_log (
text varchar2 ()
) 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 <;
TEXT
--------------------------------------------------------------------------------
Thu June 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.

Test successful
Then we test the alert information ' 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 is created Ora-27040:f Ile Create error ORA-1501 signalled during:create DATABASE DB2 Ora-00200:control file could not is 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 DA Tabase DB2 Ora-00200:control file could not is created Ora-00202:control file: '/oracle/u01/app/oracle/product/10.2.0/db 2/dbs/cntrldb2. dbf ' ora-27038:created file already exists ORA-1501 SignalleD during:create DATABASE DB2
 

 --If your alert log file contains only today's information (which can be implemented by backing up and clearing the alert log content), you can find out from the external table which errors occurred that day.