How to view the ORACLE alarm log？

1. Understand the oracle external tables
External table definition: The structure is stored in the data dictionary, and the table data is stored in the table in the OS file
Role: Query OS file data in the database, you can also load OS file data into the database
Difference from other tables: DML operations cannot be performed on external tables, nor can indexes be built on external tables, only select operations can be performed
Create a simple external table 1. Create a file on OS
Since the external table is primarily looking at files on OS, start by building a file on OS
mkdir -p /oracle/ext
vi/oracle/ext/ext dat
10, 30
40,50,60
70,80,90

2. Grant user permissions and establish directory objects

So let's start with a new user

create user identified by "123" default tablespace quota unlimited on test

The user authorization

SQL > grant create any directory to test;

Create directory objects

SQL > conn test / 123
Connected.
SQL > create directory ext as '/oracle/ext';
Directory created.

3. Create external tables
SQL > create table exttable(
id number, name varchar2 (10), i number
) organization external
(type oracle_loader
default directory ext
access parameters
(records delimited by newline
fields terminated by ', '
) location (' ext. dat)
);

4. Test

SQL > select * from exttable;
ID NAMEI
---------- ---------- ----------
10, 2030
40, 5060
70, 8090,

The test is successful and you can see that the data of OS file can be queried in the database

2. Use external tables to view the oracle alarm log

Since in the above experiment, you have created a user and given the appropriate permissions, and you also have the OS file (the alarm file alert_SID.log), you can just create the directory object and create the external table here.

1. Create directory objects

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. The test

First, see if you can find es193EN_ES194en2.log

SQL > select * from alert_log where rownum < 10;
TEXT
--------------------------------------------------------------------------------
Thu Jun 11 00:51:46 2009
Starting ORACLE instance (normal)
Cannot determine dependent libraries for /proc/self/exe
Unable find dynamic library libocr10
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 directories are /lib and /usr/lib
Unable find dynamic library libocrb10
Unable to selected libocrutl10
9 rows selected.

Test success

Then we test check 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 ES3333en status
ORA-205 signalled during: ALTER DATABASEMOUNT...
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 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 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 signalled during: ES4444en DATABASE db2

The test was successful.

So we can use the external table to view the alarm information of ORACLE easily.