本文简单研究oracle隐含参数_disable_logging与_allow_resetlogs_corruption两个参数。
仅作学习测试用，实际情况需慎重，做好数据库的备份，以防丢失数据。
1 启动数据库，获取相关信息：
SYS@orcl > startup
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileorcl
.ora
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SYS@orcl > select count(*) from m;
COUNT(*)
----------
44
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
可以看到，当前数据库使用SPFILE参数文件，_disable_logging参数为FALSE，测试表m有44条记录，当前日志组为第3组。
2修改隐含参数_disable_logging为TRUE，并重启数据库使之生效：
SYS@orcl > alter system set "_disable_logging"=true scope=spfile;
System altered.
SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl > startup;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
44
SYS@orcl > insert into m select * from m;
44 rows created.
SYS@orcl > commit;
Commit complete.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
插入44条记录，这44条记录写入当前组第3组日志文件中的。
SYS@orcl > alter system switch logfile;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO ACTIVE 1098823 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
88
SYS@orcl > insert into m select * from m;
88 rows created.
SYS@orcl > commit;
Commit complete.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO ACTIVE 1098823 17-FEB-11
SYS@orcl > alter system checkpoint;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO INACTIVE 1098823 17-FEB-11
SYS@orcl >
向m表插入记录（此时共有176条记录），做完日志切换，并做检查点，看到第三组状态有ACTIVE变为INACTIVE，说明检查点完成。
3 强制启动数据库FORCE：
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO CURRENT 1121523 17-FEB-11
3 1 1 52428800 1 NO INACTIVE 1098823 17-FEB-11
看到数据库一切正常，且M表中的176条记录完好。
4 继续插入测试数据，又插入176条记录，这176条记录对应的日志是在第2组的。
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl > alter system switch logfile;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
352
SYS@orcl > insert into m select * from m;
352 rows created.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'
SYS@orcl > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
不做检查点，FORCE强制启动数据库，发现报错！！！第2个日志组需要恢复。为什么呢，该怎么恢复呢？
5 修改另外一个隐含参数_allow_resetlogs_corruption为TRUE，并做恢复：
SYS@orcl > show parameter _allow
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SYS@orcl > alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'
SYS@orcl > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@orcl > recover database using backup controlfile until cancel;
ORA-00279: change 1121524 generated at 02/17/2011 11:45:37 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/
archivelog/2011_02_17/o1_mf_1_3_%u_.arc
ORA-00280: change 1121524 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u01/app/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oradata/orcl/system01.dbf'
SYS@orcl > alter database open resetlogs;
Database altered.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl >
6 结果，数据库不完全恢复成功，但是丢失数据了。那么又丢失了哪些数据呢？
结论：①_disable_logging参数是禁止数据库产生日志的。可以看到，在日志组为ACTIVE状态下（检查点未完成），startup force时，该日志组需要恢复。也就是为什么做过检查点（alter system checkpoint）后，startup force时，数据还在，而未做检查点时，数据丢失；
②_allow_resetlogs_corruption参数允许在特定情况下将数据库强制打开，不做校验；
③本测试环境：数据库非归档模式，未做任何备份；
SYS@orcl > !uname -a Linux rhel10g 2.6.18-164.el5xen #1 SMP Tue Aug 18 16:06:30 EDT 2009 i686 athlon i386 GNU/Linux SYS@orcl > select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SYS@orcl >