oracle rlw,使用LOGMNR工具分析Oracle Redo Log和Archive Log教程

自Oracle

11g起，无需设置UTL_FILE_DIR就可以使用LOGMNR对本地数据库的日志进行分析，以下是使用LOGMNR的DICT_FROM_ONLINE_CATALOG分析REDO和归档日志的步骤，供大家参考学习：​

1.查看REDO日志的路径。

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/stream/STREAM/redo03.log

/u01/app/oracle/oradata/stream/STREAM/redo02.log

/u01/app/oracle/oradata/stream/STREAM/redo01.log

2.添加REDO日志

第一个添加的日志需指定NEW，如果确定要查询的信息在指定的REDO日志内，可以只添加那个REDO日志，而不需要再添加其他

SQL> exec

dbms_logmnr.add_logfile(logfilename=>'

/u01/app/oracle/oradata/stream/STREAM/redo01.log',

options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

3.添加其他REDO日志

不是第一个添加的日志需指定ADDFILE。

SQL> exec

dbms_logmnr.add_logfile(logfilename=>'

/u01/app/oracle/oradata/stream/STREAM/redo02.log',

options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec

dbms_logmnr.add_logfile(logfilename=>'

/u01/app/oracle/oradata/stream/STREAM/redo03.log',

options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

4.开始对添加的REDO进行分析

SQL> exec

dbms_logmnr.start_logmnr(options=>

dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

5.查看LOGMNR分析后得到的信息

LOGMNR分析后的数据会存放在v$logmnr_contents视图中，通过查询v$logmnr_contents视图就可以查询到REDO日志的信息。

SQL> select timestamp,sql_redo,sql_undo from

v$logmnr_contents where username='SCOTT'and

table_name='T_TEST';

TIMESTAMP SQL_REDO

---------

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

21-MAR-12 create table t_test(id number,name varchar2(15));

21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values

('1','stream');

21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values

('2','dbdream');

21-MAR-12 update "SCOTT"."T_TEST" set "NAME" = 'streamsong'

where "NAME" =

'stream' and ROWID = 'AAAR7fAAEAAAADXAAA';

21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '1' and

"NAME" =

'streamsong' and ROWID = 'AAAR7fAAEAAAADXAAA';

21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '2' and

"NAME" =

'dbdream' and ROWID = 'AAAR7fAAEAAAADXAAB';

SQL_REDO就是执行的SQL语句，SQL_UNDO是回滚操作的SQL语句，也就是执行SQL_UNDO的相关SQL，就可以回滚对应的操作。

注：LOGMNR是SESSION级的，以上实验第3步到第6步需在同一个SESSION中进行，SESSION断开连接后需重新执行，否则会报以下错误提示。

SQL> select count(*) from v$logmnr_contents;

select count(*) from v$logmnr_contents

*

ERROR at line 1:

ORA-01306: dbms_logmnr.start_logmnr() must be invoked before

selecting from

v$logmnr_contents

7.结束LOGMNR操作

由于LOGMNR是会话级的，可以用直接退出或关闭当前的终端的方式来结束LOGMNR的操作，当然，正确的结束LOGMNR操作需使用下面的命令。

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.​

二、分析归档日志的实验

分析归档日志的操作和分析REDO的操作基本一样，最重要的就是准确的找到需要查找的信息在哪些归档日志内。既然是利用LOGMNR分析归档日志，数据库一定是在归档模式，要不哪来的归档日志，可以通过如下命令查看数据库是否启用归档模式。

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 15

Next log sequence to archive 17

Current log sequence 17

可以看到当前数据库已经开启归档模式，归档地址是USE_DB_RECOVERY_FILE_DEST，USE_DB_RECOVERY_FILE_DEST的具体位置可以通过下面的命令查看。

SQL> show parameter db_recove

NAME TYPE VALUE

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

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

db_recovery_file_dest string

/u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 3852M

如果数据据库开启闪回恢复区，闪回恢复区就是默认的归档地址，也是RMAN备份文件的默认存放地址，如果数据库开启闪回特性，这也是存放闪回日志的默认地址。强烈建议手动修改归档日志的存放地址，闪回恢复区的大小受db_recovery_file_dest_size大小的限制，已经遇到好多客户的数据库由于没有指定归档日志的路径，闪回恢复区满导致REDO日志无法归档，导致数据库挂起的情况。如何更改归档日志的路径，请参照《Oracle系统归档模式管理》

本实验步骤如下：

1.切换日志，使REDO日志归档。

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

2.按照归档日志的时间，找到存放需要分析信息的归档日志。

[oracle@stream 2012_03_21]$ pwd

/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21

[oracle@stream 2012_03_21]$ ll

total 32196

-rw-r----- 1 oracle oinstall 26598912 Mar 21 10:55

o1_mf_1_12_7pljs9lb_.arc

-rw-r----- 1 oracle oinstall 6206976 Mar 21 14:05

o1_mf_1_13_7plvx3bc_.arc

-rw-r----- 1 oracle oinstall 100864 Mar 21 14:07

o1_mf_1_14_7plw0bgo_.arc

-rw-r----- 1 oracle oinstall 1024 Mar 21 14:07

o1_mf_1_15_7plw0d0q_.arc

-rw-r----- 1 oracle oinstall 2048 Mar 21 14:07

o1_mf_1_16_7plw0gc1_.arc

3.将归档日志添加到LOGMNR。

SQL> exec

dbms_logmnr.add_logfile(logfilename=>

'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/

2012_03_21/o1_mf_1_13_7plvx3bc_.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec

dbms_logmnr.add_logfile(logfilename=>

'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/

2012_03_21/o1_mf_1_14_7plw0bgo_.arc',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

4.开始分析。

SQL> exec

dbms_logmnr.start_logmnr(options=>

dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

5.查看LOGMNR分析后的数据。

SQL> select timestamp,sql_redo from

v$logmnr_contents where username='SCOTT' and

table_name='T_TEST';

TIMESTAMP SQL_REDO

---------

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

21-MAR-12 create table t_test(id number,name varchar2(15));

21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values

('1','stream');

21-MAR-12 insert into "SCOTT"."T_TEST"("ID","NAME") values

('2','dbdream');

21-MAR-12 update "SCOTT"."T_TEST" set "NAME" = 'streamsong'

where "NAME" =

'stream' and ROWID = 'AAAR7fAAEAAAADXAAA';

21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '1' and

"NAME" =

'streamsong' and ROWID = 'AAAR7fAAEAAAADXAAA';

21-MAR-12 delete from "SCOTT"."T_TEST" where "ID" = '2' and

"NAME" =

'dbdream' and ROWID = 'AAAR7fAAEAAAADXAAB';

6 rows selected.

6.结束LOGMNR操作。

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

我要分享该文章 0这篇文章很赞

本文链接：http://www.askoracle.org/oracle/ORA-errors/902.html

Ask Oracle社区