• [20171110]_allow_read_only_corruption参数.txt


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

  • 相关阅读:
    【AtCoder】ARC067 F
    【AtCoder】ARC095 E
    【BZOJ】4559: [JLoi2016]成绩比较 计数DP+排列组合+拉格朗日插值
    【CodeForces】961 F. k-substrings 字符串哈希+二分
    【CodeForces】961 G. Partitions 斯特林数
    【BZOJ】2310: ParkII 插头DP
    【BZOJ】2331: [SCOI2011]地板 插头DP
    webpack从0开始---(二)
    webpack从0开始---(一)
    前端基础知识(不应需要思考的知识点)三
  • 原文地址:https://www.cnblogs.com/lfree/p/7813250.html
Copyright © 2020-2023  润新知