一个网友问了一个问题，其实非常的简单，如下：
半步疯癫 17:11:02
大师 我想问下 正常关闭库的时候，当前的redo会归档吗？
Roger 11:15:03
可能会可能不会
半步疯癫 12:18:01
可能会可能不会 大师 为什么这么说呢 什么时候会 什么时候不会呢
我问的是 正常关闭数据库的情况下
为什么我说可能会可能不会呢？其实我们知道在什么情况下oracle会将
redo 数据写入archive文件即可。 下面通过实验来说明：
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
++++++ 另外开一个窗口 ++++++
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.
++++++ 正常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.
我们可以看到，数据仍在在current redo log中，并未归档。
其实这个问题本身是非常简单的，我们只有明白数据库何时归档，归档有哪些条件就行了。
1. redo log写满以后，switch 到另外一个
2. 手工触发alter system switch logfile等命令
下面来验证下第一个。
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
可以看到，从log id 6~15 都已经归档。
所以针对该网友的问题，从上面的实验就可以进行很好的回答了，我猜测他可能
存在一个误区，以为是shutdown immediate会触发一个完全检查点，然后完全
检查点会将数据写入到归档文件中。
这里需要说明一下的是，完全检查点其实就是把cache buffer中的脏数据写入到
datafile中，另外会去更新controlfile和datafile header，当然更新的仅仅是
SCN。
另外，关于dbms_logminer，还有几个过程，大家可以了解下。
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
当然，logminer其实在很多情况下还是非常有用的，我们可以根据条件去进行分析，
比如根据时间段，根据scn等等，这不在本文的讨论范围。
发表回复