[20171110]_allow_read_only_corruption参数.txt

--//昨天在修改查询隐含参数脚本时发现一个参数_allow_read_only_corruption,感觉应该可以在异常关闭的情况下以read only打开.

--//自己测试看看.

1.环境:

SYS@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ &r/hide _allow_read_only_corruption

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- ------------

_allow_read_only_corruption allow read-only open even if database is corrupt TRUE          FALSE         FALSE

2.测试前准备:

SYS@book> create pfile='/tmp/init@.ora' from spfile ;

File created.

--//修改 /tmp/initbook.ora文件,加入如下内容:

*._allow_read_only_corruption=true

--//做一个异常关闭数据库.

SYS@book> shutdown abort ;

ORACLE instance shut down.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

SYS@book> show parameter allow

NAME                        TYPE    VALUE

--------------------------- ------- ------

_allow_read_only_corruption boolean TRUE

--//说明只要配置了参数在参数文件中,隐含参数实际上也可以使用show parameter.但是像前面带2个下划线参数,show parameter还是无

--//法查询.比如:

SYS@book> show parameter __java_pool_size

SYS@book> @ &r/hide __java_pool_size

NAME              DESCRIPTION                       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

----------------- --------------------------------- ------------- ------------- ------------

__java_pool_size  Actual size in bytes of java pool FALSE         4194304       4194304

SYS@book> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16005: database requires recovery

--//可以发现不行.也许需要_allow_resetlogs_corruption参数配合.

3.继续测试:

--//修改 /tmp/initbook.ora文件,加入如下内容:

*._allow_resetlogs_corruption=true

SYS@book> @ &r/logfile

GROUP# STATUS     TYPE       MEMBER                           IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------

     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO       1       1       728    52428800       512       1 NO  CURRENT      13277659048 2017-11-09 16:18:09 2.814750E+14

     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO       2       1       726    52428800       512       1 YES INACTIVE     13277630208 2017-11-09 09:54:47  13277632611 2017-11-09 10:18:14

     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO       3       1       727    52428800       512       1 YES INACTIVE     13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09

     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO

     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO

     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO

     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO

7 rows selected.

$ mv /mnt/ramdisk/book/redo01.log /mnt/ramdisk/book/redo01.log_xxx

--//这样避免找到redo文件.或者假象redo01.log文件损坏了.

SYS@book> shutdown abort ;

ORACLE instance shut down.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- ------------

_allow_read_only_corruption allow read-only open even if database is corrupt FALSE         TRUE          TRUE

_allow_resetlogs_corruption allow resetlogs even if it will cause corruption FALSE         TRUE          TRUE

SYS@book> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16005: database requires recovery

$ oerr ora 16005

16005, 00000, "database requires recovery"

// *Cause:  The database requires recovery, and therefore cannot be opened for

//          read-only access by this instance.

// *Action: Perform the necessary recovery and reopen for read-only access.

//

--//视乎与_allow_resetlogs_corruption无关,取消*._allow_resetlogs_corruption=true设置,重来..

SYS@book> shutdown abort ;

ORACLE instance shut down.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%

old  10:  and lower(a.ksppinm) like lower('%&1%')

new  10:  and lower(a.ksppinm) like lower('%allow_r%corrupt%%')

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- -------------

_allow_read_only_corruption allow read-only open even if database is corrupt FALSE         TRUE          TRUE

_allow_resetlogs_corruption allow resetlogs even if it will cause corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;

ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_728_896605872.dbf

ORA-00280: change 13277663682 for thread 1 is in sequence #728

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_728_896605872.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/archivelog/book/1_728_896605872.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 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: '/mnt/ramdisk/book/system01.dbf'

SYS@book> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16005: database requires recovery

--//依旧不行.失望!!

4.看看正常关闭数据库丢失某个redo的情况呢?

$ mv /mnt/ramdisk/book/redo01.log_xxx /mnt/ramdisk/book/redo01.log

--//先恢复到正常状态.

SYS@book> shutdown abort ;

ORACLE instance shut down.

SYS@book> startup mount

ORACLE instance started.

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- ------------

_allow_read_only_corruption allow read-only open even if database is corrupt FALSE         TRUE          TRUE

_allow_resetlogs_corruption allow resetlogs even if it will cause corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;

ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_728_896605872.dbf

ORA-00280: change 13277663682 for thread 1 is in sequence #728

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/mnt/ramdisk/book/redo01.log

Log applied.

Media recovery complete.

SYS@book> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@book> alter database open NORESETLOGS;

Database altered.

--//这种情况下是可以NORESETLOGS打开的,因为redo文件设置回来了.

SYS@book> select open_mode from v$database ;

OPEN_MODE

-----------

READ WRITE

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

Database mounted.

SYS@book> @ &r/logfile

GROUP# STATUS TYPE       MEMBER                           IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

------ ------ ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------

     1        ONLINE     /mnt/ramdisk/book/redo01.log     NO       1       1       728    52428800       512       1 YES INACTIVE     13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02

     2        ONLINE     /mnt/ramdisk/book/redo02.log     NO       2       1       729    52428800       512       1 NO  CURRENT      13277684336 2017-11-10 09:54:02 2.814750E+14

     3        ONLINE     /mnt/ramdisk/book/redo03.log     NO       3       1       727    52428800       512       1 YES INACTIVE     13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09

     4        STANDBY    /mnt/ramdisk/book/redostb01.log  NO

     5        STANDBY    /mnt/ramdisk/book/redostb02.log  NO

     6        STANDBY    /mnt/ramdisk/book/redostb03.log  NO

     7        STANDBY    /mnt/ramdisk/book/redostb04.log  NO

7 rows selected.

--//当前是/mnt/ramdisk/book/redo02.log.

$ mv /mnt/ramdisk/book/redo02.log /mnt/ramdisk/book/redo02.log_xxx

SYS@book> alter database open read only ;

Database altered.

SYS@book> @ &r/hide allow_r%corrupt%

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- ------------

_allow_read_only_corruption allow read-only open even if database is corrupt FALSE         TRUE          TRUE

_allow_resetlogs_corruption allow resetlogs even if it will cause corruption TRUE          FALSE         FALSE

--//^_^,这个参数意义不大,要在正常关闭的情况下,redo文件损坏的情况下,可以使用它打开数据库.

5.看看使用正常参数启动情况如何?

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup

ORACLE instance started.

Total System Global Area    634732544 bytes

Fixed Size                    2255792 bytes

Variable Size               197133392 bytes

Database Buffers            427819008 bytes

Redo Buffers                  7524352 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 18301

Session ID: 274 Serial number: 3

--//因为/mnt/ramdisk/book/redo02.log文件无法找到,启动失败.修改回来:

$ mv /mnt/ramdisk/book/redo02.log_xxx /mnt/ramdisk/book/redo02.log

SYS@book> startup

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

Database opened.

SYS@book> @ &r/hide allow_r%corrupt%

old  10:  and lower(a.ksppinm) like lower('%&1%')

new  10:  and lower(a.ksppinm) like lower('%allow_r%corrupt%%')

NAME                        DESCRIPTION                                      DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

--------------------------- ------------------------------------------------ ------------- ------------- ------------

_allow_read_only_corruption allow read-only open even if database is corrupt TRUE          FALSE         FALSE

_allow_resetlogs_corruption allow resetlogs even if it will cause corruption TRUE          FALSE         FALSE

总结:

1._allow_read_only_corruption参数意义不大,要在正常关闭的情况下,某个redo文件不存在或者损坏的情况下临时open read only打开.

2.一旦数据库文件头与控制文件当前scn不一致,需要恢复是无法使用它,read only打开的,怪不得很少见人提到这个参数.

--//补充测试redo损坏不是current的情况.

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

SYS@book> @ &r/logfile

GROUP# STATUS     TYPE       MEMBER                          IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME

------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------

     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO       1       1       728    52428800       512       1 YES INACTIVE     13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02

     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO       2       1       729    52428800       512       1 NO  CURRENT      13277684336 2017-11-10 09:54:02 2.814750E+14

     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO       3       1       727    52428800       512       1 YES INACTIVE     13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09

     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO

     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO

     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO

     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO

7 rows selected.

$ mv /mnt/ramdisk/book/redo03.log /mnt/ramdisk/book/redo03.log_xxx

SYS@book> alter database open ;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 18497

Session ID: 274 Serial number: 3

--//无法打开数据库.

SYS@book> startup mount pfile='/tmp/init@.ora'

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

SYS@book> alter database open read only ;

Database altered.

--//还原现场:

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

$ mv /mnt/ramdisk/book/redo03.log_xxx /mnt/ramdisk/book/redo03.log

SYS@book> startup

ORACLE instance started.

Total System Global Area  634732544 bytes

Fixed Size                  2255792 bytes

Variable Size             197133392 bytes

Database Buffers          427819008 bytes

Redo Buffers                7524352 bytes

Database mounted.

Database opened.