参数 _disable_logging 和 _allow_resetlogs_corruption 使用
今天主要给大家介绍一下oracle的两个隐含参数_disable_logging与_allow_resetlogs_corruption。
通过下面的试验，给大家介绍这个两个参数的作用。以下的试验仅作学习测试用，实际情况需慎重，在做试验前要做好数据库的备份，以防丢失数据。
注意：请勿在生产环境上模拟该试验。
1.先看试验环境：
pxboracle@live.com
2014.07.29 20:02
share you knowledge with the world.
通过下面的试验，给大家介绍这个两个参数的作用。以下的试验仅作学习测试用，实际情况需慎重，在做试验前要做好数据库的备份，以防丢失数据。
注意：请勿在生产环境上模拟该试验。
1.先看试验环境：
SYS@orcl > show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileorcl
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileorcl
.ora
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SYS@orcl > select count(*) from t;
COUNT(*)
----------
10
----------
10
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT
向t表插入10记录（此时共有20条记录），做完日志切换，并做检查点，看到第三组状态有ACTIVE变为INACTIVE，说明检查点完成。
3.强制启动数据库FORCE：
因此我们使用到下面的参数_allow_resetlogs_corruption。
5.使用参数_allow_resetlogs_corruption修复错误。
我们可以看到，当前数据库使用SPFILE参数文件，_disable_logging参数为FALSE，
其中测试表t有10条记录，当前日志组为第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.
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
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
Database mounted.
Database opened.
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
------------------------------------ ----------- ------------------------------
_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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
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 t;
COUNT(*)
----------
10
----------
10
SYS@orcl > insert into t select * from t;
10 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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT
我们插入了10条记录，这10条记录写入当前组第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 t;
COUNT(*)
---------- 20
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 >
|
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
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted. Database opened.
Database mounted. Database opened.
SYS@orcl > select count(*) from t;
COUNT(*)
----------
20
----------
20
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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
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
我们可以看到数据库一切正常，t表中的20条记录完好。
4.我们测试不发生检查点的情况，继续插入测试数据，插入20条记录，这20条记录对应的日志是在第2组的。
在该情况下我们没有做检查点，FORCE强制启动数据库，发现报错！！！第2个日志组需要恢复。为什么呢，该怎么恢复呢？我们可以看到数据库一切正常，t表中的20条记录完好。
4.我们测试不发生检查点的情况，继续插入测试数据，插入20条记录，这20条记录对应的日志是在第2组的。
SYS@orcl > insert into t select * from t;
20 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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
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 t;
COUNT(*)
----------
40
----------
40
SYS@orcl > insert into t select * from t;
40 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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
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
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 07/29/2014 13: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
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
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
因此我们使用到下面的参数_allow_resetlogs_corruption。
5.使用参数_allow_resetlogs_corruption修复错误。
SYS@orcl > show parameter _allow
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SYS@orcl >altersystemset"_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SYS@orcl > startup force;
ORACLE instance started.
Total SystemGlobalArea 507510784 bytes
FixedSize 1220240 bytes
VariableSize 167772528 bytes
DatabaseBuffers 331350016 bytes
Redo Buffers 7168000 bytes
Databasemounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 07/29/2014 13: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
*
ERRORatline 1:
ORA-01139: RESETLOGSoptiononlyvalidafteran incompletedatabaserecovery
SYS@orcl > recover database using backup controlfile until cancel;
ORA-00279: change 1121524 generatedat07/29/201413:45:37 neededforthread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/
archivelog/2014_07_29/o1_mf_1_3_%u_.arc
ORA-00280: change 1121524forthread 1isinsequence#3
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannotopenarchived log'/u01/app/flash_recovery_area/ORCL/archivelog/2014_07_29/o1_mf_1_3_%u_.arc'
ORA-27037: unabletoobtain file status
Linux Error: 2:Nosuch fileordirectory
Additional information: 3
ORA-01547: warning: RECOVER succeeded butOPENRESETLOGS would get error below
ORA-01194: file 1 needs more recoverytobe 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 t;
COUNT(*)
----------
20
SYS@orcl >
结论：数据库不完全恢复成功，会丢失数据。那么又丢失了哪些数据呢？
1）_disable_logging参数是禁止数据库产生日志的。可以看到，在日志组为ACTIVE状态下（检查点未完成），startup force时，该日志组需要恢复。也就是为什么做过检查点（alter system checkpoint）后，startup force时，数据还在，而未做检查点时，数据丢失；
2）_allow_resetlogs_corruption参数允许在特定情况下将数据库强制打开，不做校验；
3）本测试环境：数据库非归档模式，未做任何备份。
结论：数据库不完全恢复成功，会丢失数据。那么又丢失了哪些数据呢？
1）_disable_logging参数是禁止数据库产生日志的。可以看到，在日志组为ACTIVE状态下（检查点未完成），startup force时，该日志组需要恢复。也就是为什么做过检查点（alter system checkpoint）后，startup force时，数据还在，而未做检查点时，数据丢失；
2）_allow_resetlogs_corruption参数允许在特定情况下将数据库强制打开，不做校验；
3）本测试环境：数据库非归档模式，未做任何备份。
pxboracle@live.com
2014.07.29 20:02
share you knowledge with the world.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-1241234/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 数据库
下一篇：
11G RAC ADG 配置
请登录后发表评论
登录
全部评论
最新文章
- 第84期 查询RAC之间私网的流量信息，监控 RAC 私网流量变化
- 第83期 MySQL JSON Duality View + HeatWave Vector Search for RAG
- 第82期 Oracle 19c RAC集群：如何在线迁移/替换 OCR 磁盘组
- 第81期 批量抓取数据库中耗时最高的SQL自动生成【Active HTML格式的SQL Monitor报告】
- 第80期 追踪 SQL 性能随时间变化、执行计划是否漂移、资源消耗变化
- 第79期 Oracle Buffer Busy Waits 等待事件 P3 参数详解
- 第78期 Oracle Buffer Busy Waits等待事件深入解读
- 第77期 ogg实现REPLICAT进程FILTER数据过滤
- 第76期 明明Oracle表LCADDRESS列定义VARCHAR2(80)ORA-12899报错值太大
- 第75期 ORA-27104: system-defined limits for shared memory was misconfigured
