深入浅出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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1056915/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1056915/