How to use Oracle to conveniently view alarm log errors and oracle logs

How to use Oracle to conveniently view alarm log errors and oracle logs

I checked the information on the Internet for a few days and tried to clear the alarm log Content and create an external table to solve this problem.

I. Back up and clear alarm logs

Back up the daily alarm logs and clear them.

1: Back up alarm logs

Under the $ ORACLE_HOME/SID/bdump/directory,
Back up the file alert_ORACLE _ your instance name. LOG by date, for example, alert_ORACLE_orcl_201408111639_bak.LOG.

2: Clear log Content

Open the alarm log file and clear the content in the file with the "true"> file name.

Example:
For example, if I do this for the first time, I will back up the existing alarm logs first, for example:

-- Backup file name: Alarm log information generated before today

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

-Clear alarm logs

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

The alarm log is empty.
Switch to archive logs and check whether any new content appears in the alarm logs:

SQL> alter system switch logfile;System altered.

Now, alert_orcl2.log records the information generated before on January 1, August 11, 2014 and then backs up the alarm log for the next time.

2. An error occurred while creating an external table to view the alarm log.

If you have created a user and assigned the corresponding permissions, you can directly create a directory object 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(400))organization external(type oracle_loaderdefault directory bdumpaccess parameters(records delimited by newline)location('alert_db2.log'));

3. Test whether alert_db2.log can be found first.

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

Test successful
Then we test the alarm 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 statusORA-205 signalled during: ALTER DATABASE  MOUNT...ORA-00301: error in adding log file '/home/oracle/oracle/oradata/testdb/redo01.log' - file cannot be createdORA-27040: file create errorORA-1501 signalled during: CREATE DATABASE db2ORA-00200: control file could not be createdTEXT--------------------------------------------------------------------------------ORA-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2.dbf'ORA-27038: created file already existsORA-1501 signalled during: CREATE DATABASE db2ORA-00200: control file could not be createdORA-00202: control file: '/oracle/u01/app/oracle/product/10.2.0/db2/dbs/cntrldb2.dbf'ORA-27038: created file already existsORA-1501 signalled during: CREATE DATABASE db2

-- If your alarm log file only contains today's information (you can back up and clear the alarm log Content), you can use an external table to find out which errors occurred on the current day.

How to name an oracle alarm log

Alert_orcl.log
 
How does one view oracle warning logs in linux?

Sqlplus/as sysdba
Show parameter background

Find the directory where the alarm log is located

Then cd to the directory and vi alert *. log

You can view
Askmaclean