How to use an external table to manage Oracle alert logs

The DBA maintains a portion of the database that is often a concern when Oracle alert logs are in use. However, the alarm log is stored in the form of a text file, accumulating in chronological order of time, and

Long, it is bound to cause the alarm log too large, difficult to maintain and find relevant information. Using the external table method to manage the alarm log will greatly simplify the maintenance effort, and more direct access to the required

Information.

For the use of external tables, refer to: Oracle external tables

The contents of alarm log

Type of message and error (Types of messages and errors)

ORA-600 Internal error (ORA-600 internal errors that need immediate support from Oracle ' s customer support) '

ORA-1578 Block corruption error (ORA-1578 blocks corruption errors that require recovery)

ORA-12012 (Job Queue error (ORA-12012 job \ Errors)

Instance start shutdown, recovery, etc. (Startup & SHUTDOWN, and RECOVER statement execution messages)

Specific DDL commands (Certain CREATE, ALTER, & DROP statements)

Commands that affect the tablespace, data files, and rollback segments (statements that effect tablespaces, datafiles, and ROLLBACK segments)

A sustainable command is suspended (when a resumable statement is suspended)

LGWR cannot be written to the log file (when log writer (LGWR) cannot write to a member of a group)

Archive process startup information (when the new archiver process (ARCn) is started)

Information about the scheduling process (Dispatcher information)

Modification information for dynamic parameters (the occurrence of someone changing a dynamic parameter)

ii. Establishment of external tables

1. View the background log path

Sys@orcl> Show Parameter%b%_dump_dest--this can be omitted, in the following directly with the script Cre_ext_tb.sql implementation

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Background_dump_dest String/u01/oracle/admin/orcl/bdump

2. Create a user and give specific permissions, and create a database directory

Sys@orcl> create user USR1 identified by USR1--Creating an account USR1

2 Temporary Tablespace Temp

3 Default tablespace users

4 quota unlimited on users;

Sys@orcl> Grant Connect,resource to USR1; --Grant Connect,resource role to account USR1

Sys@orcl> Grant Create any directory to USR1; --Grant Account USR1 permission to create a directory

Sys@orcl> Conn USR1/USR1--Connect the database using the USR1

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/