Use external tables to manage Oracle alarm logs

-- ===================================================== ==========

-- Use an external table to manage Oracle alarm logs (ALAERT _ $ SID. LOG)

-- ===================================================== ==========

A part of Oracle alarm logs that DBAs often need to pay attention to when maintaining the database. However, alarm logs are stored as text files and accumulated in chronological order.

For a long time, the alarm log is too large, and it is difficult to maintain and find relevant information. Using External tables to manage alarm logs will greatly simplify the maintenance workload and make it easier to obtain the required

Information.

For more information about external tables, see Oracle External tables.

I. Alarm log Content

Types of messages and errors (Types of messages and errors)

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

ORA-1578 block corruption error (ORA-1578 block corruption uption errors that require recovery)

ORA-12012 (job queue error (ORA-12012 job queue errors)

STARTUP & SHUTDOWN, and RECOVER statement execution messages)

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

Commands that affect TABLESPACES, data files, and rollback segments (Statements that effect TABLESPACES, DATAFILES, and rollback segments)

Continuous commands are suspended (When a resumable statement is suincluded)

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

Archive Process startup information (When new Archiver Process (ARCn) is started)

Dispatcher information)

The occurrence of someone changing a dynamic parameter)

2. Create an External table

1. view the background Log Path

Sys @ ORCL> show parameter % B % _ dump_dest -- this can be omitted and will be directly implemented using the script cre_ext_tb. SQL later.

NAME TYPE VALUE

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

Background_dump_dest string/u01/oracle/admin/orcl/bdump

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

Sys @ ORCL> create user usr1 identified by usr1 -- create account usr1

2 temporary tablespace temp

3 default tablespace users

4 quota unlimited on users;

Sys @ ORCL> grant connect, resource to usr1; -- grant connect and resource roles to the account usr1

Sys @ ORCL> grant create any directory to usr1; -- grant the account usr1 the permission to create a directory

Sys @ ORCL> conn usr1/usr1 -- use usr1 to connect to the database

3. The following scripts are used to track and manage alarm logs.

Script description

Cre_ext_tb. SQL

An alert_log table is created to store important information about alarm logs. An external table alert_log_disk allows you to view alarm logs directly on local data.

Library.

Update_alert_log. SQL

It is used to filter important information from an external table and update the latest information not stored in the alert_log table to the alert_log table.

4. Use the following script to create the alert_log table and alert_log_disk External table

Usr1 @ ORCL> get/u01/bk/scripts/cre_ext_tb. SQL -- view the table creation code

1 define alert_length = "500"

2 drop table alert_log;

3 create table alert_log (-- create table alert_log to store important information of alarm logs

4 alert_date date,

5 alert_text varchar2 (& alert_length)

6)

7 storage (initial 512 k next 512 K pctincrease 0 );

8 create index alert_log_idx on alert_log (alert_date) -- create an index for the table alert_log

9 storage (initial 512 k next 512 K pctincrease 0 );

10 column db new_value _ DB noprint;

11 column bdump new_value _ bdump noprint;

12 select instance_name db from v $ instance; -- Obtain the instance name and alarm Log Path

13 select value bdump from v $ parameter

14 where name = 'background _ dump_dest ';

15 drop directory BDUMP;

16 create directory BDUMP as '& _ bdump ';

17 drop table alert_log_disk;

18 create table alert_log_disk (text varchar2 (& alert_length) -- create an external table

19 organization external (

20 type oracle_loader

21 default directory BDUMP

22 access parameters (

23 records delimited by newline nologfile nobadfile

24 fields terminated by "&" ltrim

25)

26 location ('alert _ & _ DB .. log ')

27)

28 * reject limit unlimited;

Usr1 @ ORCL> start/u01/bk/scripts/cre_ext_tb. SQL -- run the table creation code.

5. Fill the alert_log table with the following script

Usr1 @ ORCL> get/u01/bk/scripts/update_alert_log. SQL -- script update_alert_log. SQL is used to fill important information of External tables with alert_log

1 set serveroutput on

2 declare

3 isdate number: = 0;

4 start_updating number: = 0;

5 rows_inserted number: = 0;

6 alert_date date;

7 max_date date;

8 alert_text alert_log_disk.text % type;

9 begin

10/* find a starting date */

11 select max (alert_date) into max_date from alert_log;

12 if (max_date is null) then

13 max_date: = to_date ('01-jan-1980 ', 'dd-mon-yyyy ');

14 end if;

15 for r in (

16 select substr (text, 1,180) text from alert_log_disk -- use the for loop to filter information from the alarm log

17 where text not like '% offlining %'

18 and text not like 'arc _: %'

19 and text not like '% LOG_ARCHIVE_DEST_1 %'

20 and text not like '% Thread 1 advanced to log sequence %'

21 and text not like '% Current log # % seq # % mem # %'

22 and text not like '% Undo Segment % lined %'

23 and text not like '% alter tablespace % back %'

24 and text not like '% Log actively being archived by another process %'

25 and text not like '% alter database backup controlfile to trace %'

26 and text not like '% Created Undo Segment %'

27 and text not like '% started with pid %'

28 and text not like '% ORA-12012 %'

29 and text not like '% ORA-06512 %'

30 and text not like '% ORA-000060: %'

31 and text not like '% coalesce %'

32 and text not like '% Beginning log switch checkpoint up to RBA %'

33 and text not like '% Completed checkpoint up to RBA %'

34 and text not like '% specifies an obsolete parameter %'

35 and text not like '% begin backup %'

36 and text not like '% end backup %'

37)

38 loop

39 isdate: = 0;

40 alert_text: = null;

41 select count (*) into isdate -- sets the flag to determine whether the modified row is time data.

42 from dual

43 where substr (r. text, 21) in ('20160301', '20160301', '20160301', '20160301 ')

44 and r. text not like '% cycle_run_year % ';

45 if (isdate = 1) then -- format the time data

46 select to_date (substr (r. text, 5), 'mon dd hh24: mi: ss rrrr ')

47 into alert_date

48 from dual;

49 if (alert_date> max_date) then -- set the flag to determine whether update is required.

50 start_updating: = 1;

51 end if;

52 else

53 alert_text: = r. text;

54 end if;

55 if (alert_text is not null) and (start_updating = 1) then -- start_updating flag and alert_text are true, insert record

56 insert into alert_log values (alert_date, substr (alert_text, 1,180 ));

57 rows_inserted: = rows_inserted + 1;

58 commit;

59 end if;

60 end loop;

61 sys. dbms_output.put_line ('inserting after date' | to_char (max_date, 'Mm/DD/RR HH24: MI: ss '));

62 sys. dbms_output.put_line ('rows Inserted: '| rows_inserted );

63 commit;

64 * end;

65

Usr1 @ ORCL> start/u01/bk/scripts/update_alert_log. SQL

Inserting after date 01/01/80 00:00:00

Rows Inserted: 632

PL/SQL procedure successfully completed.

Based on the above method, you can regularly update the alarm log to the local database, and then clear the alarm log file.

3. View alarm log Content

1. Modify the display format of the session date

Usr1 @ ORCL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

2. View alarm log information

Usr1 @ ORCL> select * from alert_log where rownum <5;

ALERT_DATE ALERT_TEXT

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

21:36:11 SYS auditing is disabled

21:36:11 ksdpec: called for event 13740 prior to event group initialization

21:36:11 Starting up oracle rdbms Version: 10.2.0.1.0.

21:36:11 System parameters with non-default values:

3. view the latest 5 alarm logs

Usr1 @ ORCL> select * from alert_log where rownum <5 order by alert_date desc;

ALERT_DATE ALERT_TEXT

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

21:36:11 SYS auditing is disabled

21:36:11 ksdpec: called for event 13740 prior to event group initialization

21:36:11 Starting up oracle rdbms Version: 10.2.0.1.0.

21:36:11 System parameters with non-default values:

4. view the ORA error message in the alarm log

Usr1 @ ORCL> select * from alert_log where alert_text like 'ora-% ';

ALERT_DATE ALERT_TEXT

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

21:36:13 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'

21:36:13 ORA-27037: unable to obtain file status

21:36:13 ORA-205 signalled during: alter database mount...

21:36:23 ORA-1507 signalled during: alter database close normal...

21:36:27 ORA-00202: control file: '/u01/oracle/oradata/orcl/control03.ctl'

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html