能否不生成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 LOG | LOGGING | APPEND | 有REDO |
NO APPEND | 有REDO | ||
NOLOGGING | APPEND | 无REDO | |
NO APPEND | 有REDO | ||
NOARCHIVED LOG | LOGGING | APPEND | 无REDO |
NO APPEND | 有REDO | ||
NOLOGGING | APPEND | 无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 -