您好,欢迎光临! 请 |

Oracle _disable_logging and _allow_resetlogs_corruption

作者: | 分类:Oracle | Tag: | 评论:0 
字号:T|T

本文简单研究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 >
顶一下
(0)
100%
踩一下
(0)
100%

原创内容,转载时请保留原文信息:
文章标题:Oracle _disable_logging and _allow_resetlogs_corruption
↑上一篇↑:
↓下一篇↓:

发表评论

插入图片

NOTICE1:请申请gravatar头像,没有头像的评论可能不会被回复|头像相关帮助!