深入淺出redo和undo記載03

如何高效快速插入資料，nologging+append+parallel 停掉索引和約束！下面來看看nologging和append模式下產生的redo測試！

SQL> archive log list

資料庫日誌模式 非存檔模式

自動存檔 禁用

存檔終點 USE_DB_RECOVERY_FILE_DEST

最早的聯機日誌序列 1

當前日誌序列 3

SQL> create or replace view redo_size as select name,value from v$mystat a,v$statname b where

2 a.STATISTIC#=b.STATISTIC# and b.NAME='redo size';

SQL> create public synonym redo_size for redo_size;

Synonym created

SQL> conn xiaoyu/xiaoyu

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as xiaoyu

SQL> select * from redo_size;

NAME VALUE

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

redo size 0

SQL> insert into test10 select * from dba_objects;

50040 rows inserted

SQL> select * from redo_size;

NAME VALUE

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

redo size 5665748

SQL> insert /*+append*/ into test10 select * from dba_objects;

50040 rows inserted

SQL> select * from redo_size;

NAME VALUE

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

redo size 5698228

SQL> alter table test10 nologging;

Table altered

SQL> insert into test10 select * from dba_objects;

50040 rows inserted

SQL> select * from redo_size;

NAME VALUE

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

redo size 11314308

SQL> insert /*+append*/ into test10 select * from dba_objects;

50040 rows inserted

SQL> select * from redo_size;

NAME VALUE

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

redo size 11337948

在noarchivelog模式下

nologging的表：append模式插入是不寫入redo的，非append模式還是會寫redo

logging的表：append模式插入依然不寫入redo，非appedn模式還是會寫redo

只簡單列舉archivelog模式的redo產生情況了，可以參照上面的步驟測試下。

Archivelog模式下

除了nologging下的append模式插入不寫入redo，其餘都寫入redo

nologging操作會導致資料庫不記錄日誌，如果資料庫崩潰這部分資料無法恢復的。這裡參照eygle的例子進行測試：

再簡單看下隱含引數_disable_logging引數，預設是false開啟redo記錄，設定為true時redo記錄功能將關閉，redo將不記載資料庫的變更的記錄。

SQL> col ksppinm for a20

SQL> col ksppdesc for a20

SQL> col ksppstvl for a20

SQL> select ksppinm,ksppdesc,ksppstvl from x$ksppi a,x$ksppcv b

2 where a.indx=b.indx and a.ksppinm like '_disable_logging';

KSPPINM KSPPDESC KSPPSTVL

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

_disable_logging Disable logging TRUE

SQL> create table xiaoyu.test21 as select * from dba_users;

Table created

SQL> shutdown abort;

Oracle 例項已關閉

SQL> startup;

已連線到目標資料庫 (未啟動)

Oracle 例項已啟動

資料庫已裝載

資料庫已開啟

SQL> select count(*) from xiaoyu.test21;

select count(*) from xiaoyu.test21

ORA-00942: 表或檢視不存在

可以看出意外關閉資料庫沒有進行checkpoint，test21表建立所需的block都在sga的buffer cache中並沒有寫入disk，關閉資料庫時釋放了sga的資訊，再次啟動資料庫時，由於redo中沒有記載，無法透過redo記錄前滾而實現資料恢復。

SQL>alter system switch logfile

這裡swtich log是為了使當前的redo的資訊更加清晰，更容易檢視

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 INACTIVE

3 INACTIVE

Alter system dump logfile 'D:oracleproduct10.2.0oradataxiaoyuredo03.log'

DUMP OF REDO FROM FILE 'D:oracleproduct10.2.0oradataxiaoyuredo03.log'

Opcodes *.*

RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

Times: creation thru eternity

FILE HEADER:

Compatibility Vsn = 169869568=0xa200100

Db ID=628569583=0x257735ef, Db Name='XIAOYU'

Activation ID=629697909=0x25886d75

Control Seq=2490=0x9ba, File size=102400=0x19000

File Number=3, Blksiz=512, File Type=2 LOG

descrip:"Thread 0001, Seq# 0000000004, SCN 0x00000018bee4-0xffffffffffff"

thread: 1 nab: 0xffffffff seq: 0x00000004 hws: 0x1 eot: 1 dis: 0

resetlogs count: 0x2deb61b9 scn: 0x0000.0017d75a (1562458)

resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

prev resetlogs count: 0x2de1bf19 scn: 0x0000.0014873e (1345342)

prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000

Low scn: 0x0000.0018bee4 (1621732) 12/21/2011 10:18:40

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

Enabled scn: 0x0000.0017d75a (1562458) 12/20/2011 16:11:37

Thread closed scn: 0x0000.0018bee4 (1621732) 12/21/2011 10:18:40

Disk cksum: 0xf2f7 Calc cksum: 0xf2f7

Terminal recovery stop scn: 0x0000.00000000

Terminal recovery 01/01/1988 00:00:00

Most recent redo scn: 0x0000.00000000

Largest LWN: 0 blocks

End-of-redo stream : No

Unprotected mode

Miscellaneous flags: 0x0

Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

END OF REDO DUMP

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 0Kb in 0.44s => 0.00 Mb/sec

Total physical reads: 4096Kb

----------------------------------------------可以看出redo中並沒有記錄任何關於test21的資訊。

[@more@]