作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


能否不生成Redo:

正常的数据库必须生成Redo,这是数据库的机制,否则数据库在遇到故障或Crash时则无法恢复。但是Oracle为了增强某些特殊操作的性能,对于一些SQL语句,Oracle允许使用NOLOGGING子句,NOLOGGING可以使得日志生成大幅降低,但是必要日志(比如:对于字典表的修改)仍然会被记录

可以使用NOLOGGING的环境非常有限,在以下操作中,可以增加NOLOGGING子句:

·创建索引或重建索引时;
·通过/*+ APPEND */提示,使用直接路径(Direct Path)批量INSERT操作或SQL*Loader直接路径加载数据;
·CTAS方式创建数据表时;
·大对象(LOB)的操作;
·一些ALTER TABLE操作,如MOVE、SPLIT等。

NOLOGGING和表模式(LOGGING/NOLOGGING),插入模式(APPEND/NO APPEND)及数据库运行模式(归档/非归档)都有关系,具体可以归纳如下表所示:

数据库模式表模式插入模式REDO生成
ARCHIVED LOGLOGGINGAPPEND有REDO
  NO APPEND有REDO
 NOLOGGINGAPPEND 无REDO
  NO APPEND有REDO
NOARCHIVED LOGLOGGINGAPPEND无REDO
  NO APPEND有REDO
 NOLOGGINGAPPEND无REDO
  NO APPEND有REDO

由于大多数生产数据库运行在归档模式下,所以下面以归档模式为例,简要介绍一下NOLOGGING对于REDO生成的影响。

⑴ 首先创建一个视图用于方便REDO的查询:

tq@TQGZS> create or replace view redo_size
  2  as
  3  select value
  4  from v$mystat,v$statname
  5  where v$mystat.statistic# = v$statname.statistic#
  6  and v$statname.name = 'redo size';
View created.

⑵ 通过如下步骤将数据库启动在归档模式:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

⑶ 测试对于常规表,Redo的生成,可以看到在归档模式下,APPEND操作对于常规表是无效的

tq@TQGZS> create table test as select * from dba_objects where 1=0;
Table created.
tq@TQGZS> select owner,table_name,logging from dba_tables where table_name='TEST' and owner='TQ';
OWNER                TABLE_NAME           LOG
-------------------- -------------------- ---
TQ                   TEST                 YES
tq@TQGZS> insert into test select * from dba_objects;
51119 rows created.
tq@TQGZS> select * from redo_size;
     VALUE
----------
   6740444
tq@TQGZS> insert /*+ append */ into test select * from dba_objects;
51119 rows created.
tq@TQGZS> select * from redo_size;
     VALUE
----------
  15573512
tq@TQGZS> select (15573512-6740444) redo_append,(6740444-0) redo from dual;
REDO_APPEND       REDO
----------- ----------
    8833068    6740444
tq@TQGZS> drop table test purge;
Table dropped.

⑷ 再来看对于NOLOGGING表的APPEND操作:

tq@TQGZS> select owner,table_name,logging
  2  from dba_tables where owner='TQ' and table_name='TEST';
OWNER                          TABLE_NAME           LOG
------------------------------ -------------------- ---
TQ                             TEST                 NO
tq@TQGZS> select * from redo_size;
     VALUE
----------
     20988
tq@TQGZS> insert into test select * from dba_objects;
51119 rows created.
tq@TQGZS> select * from redo_size;
     VALUE
----------
   5802796
tq@TQGZS> insert /*+ append */ into test select * from dba_objects;
51119 rows created.
tq@TQGZS> select * from redo_size;
     VALUE
----------
   5822748
tq@TQGZS> select (5822748-5802796) redo_append,(5802796-20988) redo from dual;
REDO_APPEND       REDO
----------- ----------
      19952    5781808
tq@TQGZS> drop table test purge;
Table dropped.

但是需要注意的是,由于NOLOGGING操作会导致对于数据的操作不记录日志,如果数据库崩溃,这部分数据是无法恢复的,所以通常的建议是,在进行了NOLOGGING操作之后,需要对数据库进行备份,以避免数据因数据库失效而丢失。

下面通过一个简短的测试,来看一下NOLOGGING对于数据恢复的影响。本测试环境如下:

sys@TQGZS> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

⑴ 首先对表空间TQ进行热备份:

sys@TQGZS> alter tablespace tq begin backup;
Tablespace altered.
sys@TQGZS> ! cp /u01/oracle/oradata/tqgzs/tq.dbf /u01/oracle/databak/tq.dbf.bak
sys@TQGZS> alter tablespace tq end backup;
Tablespace altered.

⑵ 在TQ表空间创建NOLOGGING测试表并APPEND追加测试数据:

sys@TQGZS> conn tq/tq
Connected.
tq@TQGZS> create table test nologging as select * from dba_objects where 0=1;
Table created.
tq@TQGZS> insert /*+ append */ into test select * from dba_objects;
51119 rows created.
tq@TQGZS> commit;
Commit complete.

⑶ 移除TQ表空间的数据文件,模拟故障:

tq@TQGZS> conn / as sysdba
Connected.
sys@TQGZS> alter tablespace tq offline;
Tablespace altered.
sys@TQGZS> ! mv /u01/oracle/oradata/tqgzs/tq.dbf /u01/oracle/databak/tq.dbf.del

⑷ 恢复先前热备份文件并进行恢复:

sys@TQGZS> ! cp /u01/oracle/databak/tq.dbf.bak /u01/oracle/oradata/tqgzs/tq.dbf
sys@TQGZS> alter tablespace tq online;
alter tablespace tq online
*
ERROR at line 1:
ORA-01113: file 15 needs media recovery
ORA-01110: data file 15: '/u01/oracle/oradata/tqgzs/tq.dbf'
sys@TQGZS> recover tablespace tq;
Media recovery complete.
sys@TQGZS> alter tablespace tq online;       
Tablespace altered.

⑸ 查询该表,发现数据库出现错误:

sys@TQGZS> select count(*) from tq.test;
select count(*) from tq.test
                        *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 15, block # 12)
ORA-01110: data file 15: '/u01/oracle/oradata/tqgzs/tq.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

由此可见NOLOGGING对于数据库的影响。而如果在NOLOGGING之后立即对数据库进行过备份,那么这些数据已经写出到数据文件上,自然就是可以恢复的,那么这个恢复过程可能类似:

sys@TQGZS> alter tablespace tq offline;
Tablespace altered.
sys@TQGZS> ! cp /u01/oracle/databak/tq.dbf.del /u01/oracle/oradata/tqgzs/tq.dbf
sys@TQGZS> recover tablespace tq;
Media recovery complete.
sys@TQGZS> alter tablespace tq online;
Tablespace altered.
sys@TQGZS> select count(*) from tq.test;
  COUNT(*)
----------
     51119

当使用DataGuard作为数据库的备份或容灾高可用性手段时,通常日志就变得不可缺少。在Oracle 9iR2中,可以将数据库置于强制日志模式(FORCE LOGGING MODE)。

在强制日志模式下,所有操作都将记录日志:

sys@TQGZS> select force_logging from v$database;
FOR
---
NO
sys@TQGZS> alter database force logging;  
Database altered.
sys@TQGZS> select force_logging from v$database;
FOR
---
YES

- The End -