参数 _disable_logging 和 _allow_resetlogs_corruption 使用

最新推荐文章于 2020-08-31 17:13:04 发布

cuchou5321

最新推荐文章于 2020-08-31 17:13:04 发布

阅读量182

点赞数

今天主要给大家介绍一下oracle的两个隐含参数_disable_logging与_allow_resetlogs_corruption。
通过下面的试验，给大家介绍这个两个参数的作用。以下的试验仅作学习测试用，实际情况需慎重，在做试验前要做好数据库的备份，以防丢失数据。
注意：请勿在生产环境上模拟该试验。
1.先看试验环境：

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 t;

COUNT(*)
----------
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

2 1 0 52428800 1 YES UNUSED 0

3 1 1 52428800 1 NO CURRENT

我们可以看到，当前数据库使用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.

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;

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

SYS@orcl > insert into t select * from t;

10 rows created.

SYS@orcl > commit;

Commit complete.

SYS@orcl > select * from V$log;

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 >

向t表插入10记录（此时共有20条记录），做完日志切换，并做检查点，看到第三组状态有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 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 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组的。

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

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

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

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 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

3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11

2 1 3 52428800 1 NO ACTIVE

在该情况下我们没有做检查点，FORCE强制启动数据库，发现报错！！！第2个日志组需要恢复。为什么呢，该怎么恢复呢？
因此我们使用到下面的参数_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(*)

----------

SYS@orcl >

结论：数据库不完全恢复成功，会丢失数据。那么又丢失了哪些数据呢？
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博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1241234/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1241234/

cuchou5321

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
参数 _disable_logging 和 _allow_resetlogs_corruption 使用

今天主要给大家介绍一下oracle的两个隐含参数_disable_logging与_allow_resetlogs_corruption。通过下面的试验，给大家介绍这个两个参数的作用。以下的试验仅作学习测试用，实际情况需慎重...
复制链接

扫一扫