使用logminer回答一个网友的问题
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 使用logminer回答一个网友的问题
|
1 |
一个网友问了一个问题，其实非常的简单，如下： |
|
1 2 3 4 5 6 7 8 9 |
半步疯癫 17:11:02 大师 我想问下 正常关闭库的时候，当前的redo会归档吗？ Roger 11:15:03 可能会可能不会 半步疯癫 12:18:01 可能会可能不会 大师 为什么这么说呢 什么时候会 什么时候不会呢 我问的是 正常关闭数据库的情况下 |
|
1 2 |
为什么我说可能会可能不会呢？其实我们知道在什么情况下oracle会将 redo 数据写入archive文件即可。 下面通过实验来说明： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
SQL> SELECT name, supplemental_log_data_min FROM v$database; NAME SUPPLEME --------- -------- ROGER NO SQL> show parameter utl NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ create_stored_outlines string utl_file_dir string /home/ora10g/logs SQL> alter database add supplemental log data; Database altered. SQL> select name, supplemental_log_data_min FROM v$database; NAME SUPPLEME --------- -------- ROGER YES SQL> @ ?/rdbms/admin/dbmslm.sql Package created. Grant succeeded. SQL> @ ?/rdbms/admin/dbmslmd.sql Package created. SQL> exec dbms_logmnr_d.build('logminer_dict.dat','/home/ora10g/logs'); PL/SQL procedure successfully completed. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE /home/ora10g/oradata/roger/redo01.log NO 3 ONLINE /home/ora10g/oradata/roger/redo03.log NO 2 ONLINE /home/ora10g/oradata/roger/redo02.log NO SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 4 52428800 1 YES INACTIVE 695360 23-OCT-11 2 1 5 52428800 1 YES INACTIVE 695362 23-OCT-11 3 1 6 52428800 1 NO CURRENT 695364 23-OCT-11 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /home/ora10g/archivelog Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 SQL> !ls -ltr /home/ora10g/archivelog total 296 -rw-r----- 1 ora10g oinstall 277504 Oct 23 01:19 1_3_765013258.dbf -rw-r----- 1 ora10g oinstall 1024 Oct 23 01:19 1_4_765013258.dbf -rw-r----- 1 ora10g oinstall 2048 Oct 23 01:19 1_5_765013258.dbf |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
++++++ 另外开一个窗口 ++++++ SQL> conn roger/roger Connected. SQL> select count(*) from ht1; COUNT(*) ---------- 1022976 SQL> delete from ht1 where rownum <10; 9 rows deleted. SQL> commit; Commit complete. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
++++++ 正常shutdown immediate ++++++ SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORA-00000: normal, successful completion SQL> conn /as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 71304424 bytes Database Buffers 92274688 bytes Redo Buffers 2920448 bytes Database mounted. Database opened. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 4 52428800 1 YES INACTIVE 695360 23-OCT-11 2 1 5 52428800 1 YES INACTIVE 695362 23-OCT-11 3 1 6 52428800 1 NO CURRENT 695364 23-OCT-11 SQL> exec dbms_logmnr.add_logfile('/home/ora10g/oradata/roger/redo03.log' ); PL/SQL procedure successfully completed. SQL> !ls -ltr /home/ora10g/archivelog total 296 -rw-r----- 1 ora10g oinstall 277504 Oct 23 01:19 1_3_765013258.dbf -rw-r----- 1 ora10g oinstall 1024 Oct 23 01:19 1_4_765013258.dbf -rw-r----- 1 ora10g oinstall 2048 Oct 23 01:19 1_5_765013258.dbf SQL> exec dbms_logmnr.add_logfile('/home/ora10g/archivelog/1_3_765013258.dbf' ); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.start_logmnr(0,0,'','','/home/ora10g/logs/logminer_dict.dat',0); PL/SQL procedure successfully completed. SQL> col table_name for a10 SQL> col sql_redo for a70 SQL> set long 99999999 SQL> select log_id,table_name,SQL_REDO 2 from v$logmnr_contents 3 where table_name='HT1'; LOG_ID TABLE_NAME SQL_REDO ---------- ---------- ---------------------------------------------------------------------- 6 HT1 delete from "ROGER"."HT1" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I COL$' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '20' and "DATA_OB JECT_ID" = '2' and "OBJECT_TYPE" = 'TABLE' and "CREATED" = TO_DATE('15 -APR-10', 'DD-MON-RR') and "LAST_DDL_TIME" = TO_DATE('15-APR-10', 'DD- MON-RR') and "TIMESTAMP" = '2010-04-15:13:14:44' and "STATUS" = 'VALID ' and "TEMPORARY" = 'N' and "GENERATED" = 'N' and "SECONDARY" = 'N' an d ROWID = 'AAAMpRAAFAAAAAUAAA'; 6 HT1 delete from "ROGER"."HT1" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'I _USER1' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '44' and "DATA_ OBJECT_ID" = '44' and "OBJECT_TYPE" = 'INDEX' and "CREATED" = TO_DATE( ... ... ... ... 省略部分内容 OBJECT_ID" = '51' and "OBJECT_TYPE" = 'INDEX' and "CREATED" = TO_DATE( '15-APR-10', 'DD-MON-RR') and "LAST_DDL_TIME" = TO_DATE('15-APR-10', ' DD-MON-RR') and "TIMESTAMP" = '2010-04-15:13:14:44' and "STATUS" = 'VA LID' and "TEMPORARY" = 'N' and "GENERATED" = 'N' and "SECONDARY" = 'N' and ROWID = 'AAAMpRAAFAAAAAUAAI'; 9 rows selected. |
|
1 2 3 4 5 6 7 |
我们可以看到，数据仍在在current redo log中，并未归档。 其实这个问题本身是非常简单的，我们只有明白数据库何时归档，归档有哪些条件就行了。 1. redo log写满以后，switch 到另外一个 2. 手工触发alter system switch logfile等命令 下面来验证下第一个。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE /home/ora10g/oradata/roger/redo01.log NO 3 ONLINE /home/ora10g/oradata/roger/redo03.log NO 2 ONLINE /home/ora10g/oradata/roger/redo02.log NO SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 4 52428800 1 YES INACTIVE 695360 23-OCT-11 2 1 5 52428800 1 YES INACTIVE 695362 23-OCT-11 3 1 6 52428800 1 NO CURRENT 695364 23-OCT-11 SQL> conn roger/roger Connected. SQL> select bytes/1024/1024 2 from sys.dba_segments 3 where segment_name='HT1'; BYTES/1024/1024 --------------- 168 Elapsed: 00:00:00.10 SQL> delete from ht1 where rownum <900000; 899999 rows deleted. Elapsed: 00:01:11.43 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 16 52428800 1 NO CURRENT 711575 23-OCT-11 2 1 14 52428800 1 YES ACTIVE 708373 23-OCT-11 3 1 15 52428800 1 YES ACTIVE 710102 23-OCT-11 Elapsed: 00:00:00.01 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> !ls -ltr /home/ora10g/archivelog total 501216 -rw-r----- 1 ora10g oinstall 277504 Oct 23 01:19 1_3_765013258.dbf -rw-r----- 1 ora10g oinstall 1024 Oct 23 01:19 1_4_765013258.dbf -rw-r----- 1 ora10g oinstall 2048 Oct 23 01:19 1_5_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:58 1_6_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:58 1_7_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:58 1_8_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:58 1_9_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:58 1_10_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:59 1_11_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:59 1_12_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:59 1_13_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:59 1_14_765013258.dbf -rw-r----- 1 ora10g oinstall 51229184 Oct 23 01:59 1_15_765013258.dbf |
|
1 2 3 4 5 6 7 8 9 10 11 |
可以看到，从log id 6~15 都已经归档。 所以针对该网友的问题，从上面的实验就可以进行很好的回答了，我猜测他可能 存在一个误区，以为是shutdown immediate会触发一个完全检查点，然后完全 检查点会将数据写入到归档文件中。 这里需要说明一下的是，完全检查点其实就是把cache buffer中的脏数据写入到 datafile中，另外会去更新controlfile和datafile header，当然更新的仅仅是 SCN。 另外，关于dbms_logminer，还有几个过程，大家可以了解下。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> desc dbms_logmnr PROCEDURE ADD_LOGFILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME VARCHAR2 IN OPTIONS BINARY_INTEGER IN DEFAULT FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO NUMBER IN DEFAULT COLUMN_NAME VARCHAR2 IN DEFAULT PROCEDURE END_LOGMNR FUNCTION MINE_VALUE RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO NUMBER IN DEFAULT COLUMN_NAME VARCHAR2 IN DEFAULT PROCEDURE REMOVE_LOGFILE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME VARCHAR2 IN PROCEDURE START_LOGMNR Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- STARTSCN NUMBER IN DEFAULT ENDSCN NUMBER IN DEFAULT STARTTIME DATE IN DEFAULT ENDTIME DATE IN DEFAULT DICTFILENAME VARCHAR2 IN DEFAULT OPTIONS BINARY_INTEGER IN DEFAULT |
|
1 2 |
当然，logminer其实在很多情况下还是非常有用的，我们可以根据条件去进行分析， 比如根据时间段，根据scn等等，这不在本文的讨论范围。 |
One Response to “使用logminer回答一个网友的问题”
太感谢大师啦，疑惑解开了 ，以后我要多学习 多动手做实验
再次感谢大师
Leave a Reply
You must be logged in to post a comment.