你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 11g的Redo Log和Archive Log的分析方法

[日期:2016-08-21] 来源:Ask Oracle   作者:Ask Oracle [字体: ]

Oracle 11g起,无需设置UTL_FILE_DIR就可以使用LOGMNR对本地数据库的日志进行分析,以下是使用LOGMNR的DICT_FROM_ONLINE_CATALOG分析REDO和归档日志的步骤

分析REDO日志的实验

  • 创建测试表,并做DML操作。
SQL> create table t_test(id number,name varchar2(15));
Table created.
  
SQL> insert into t_test values(1,'stream');
1 row created.
  
SQL> insert into t_test values(2,'dbdream');
1 row created.
  
SQL> commit;
Commit complete.
  
SQL> update t_test set name='streamsong' where id=1;
1 row updated.
  
SQL> commit;
Commit complete.
  
SQL> delete from t_test;
2 rows deleted.
  
SQL> commit;
Commit complete.
  • 查看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
  • 添加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.
  • 添加其他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.
  • 开始对添加的REDO进行分析
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
  
PL/SQL procedure successfully completed.
  • 查看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断开连接后需重新执行,否则会报以下错误提示。
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

  • 结束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_DESTUSE_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 852M

如果数据据库开启闪回恢复区,闪回恢复区就是默认的归档地址,我个人建议使用这个空间存放归档日志,因为从Oracle 11g开始当该空间的使用率达到80%的时候,系统会自动删除已经备份过的归档文件,避免被撑爆。闪回恢复区的大小受db_recovery_file_dest_size大小的限制,是一个动态参数,可以随时在线修改。

本实验步骤如下:

  • 切换日志,使REDO日志归档。
SQL> alter system switch logfile;
System altered.
  
SQL> /
System altered.
  
SQL> /
System altered.
  • 按照归档日志的时间,找到存放需要分析信息的归档日志。
[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
  • 将归档日志添加到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.
  • 开始分析。
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
  
PL/SQL procedure successfully completed.
  • 查看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.
  • 结束LOGMNR操作。
SQL> exec dbms_logmnr.end_logmnr;
  
PL/SQL procedure successfully completed.

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2016-08/134449.htm

linux
相关资讯       redo log  Archive log 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款