当前位置： 首页 > news >正文

20171110_allow_read_only_corruption参数

news 来源：原创 2024/5/21 0:58:06

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

相关文章：

  • 手机震动效果--ios
  • AD DS最佳实践分析程序（BPA）应用实例---扫描并归档结果
  • httpd.conf文件详解（转）
  • java环境JDK安装及配置
  • WCF 入门
  • 学会提问 (豆瓣)
  • 路由协议OSPF
  • Linux服务器装机安全快速进阶指南
  • jetty client 与apache http client的实现、分析
  • suse linux 10上MQ7.0安装测试手记
  • Mac下开机启动rc.common不生效的问题
  • ubuntu安装过程小记
  • GitHub 上开源的区块链项目 90% 死亡了
  • 防止僵尸进程的三种方法
  • cisco之VLAN的配置
  • CentOS7简单部署NFS
  • Django 博客开发教程 16 - 统计文章阅读量
  • iOS动画编程-View动画[ 1 ] 基础View动画
  • Promise初体验
  • SSH 免密登录
  • vue和cordova项目整合打包，并实现vue调用android的相机的demo
  • 前端技术周刊 2018-12-10：前端自动化测试
  • 前端学习笔记之原型——一张图说明`prototype`和`__proto__`的区别
  • 入口文件开始，分析Vue源码实现
  • 通过npm或yarn自动生成vue组件
  • 学习笔记：对象，原型和继承（1）
  • 正则与JS中的正则
  • Prometheus VS InfluxDB
  • 数据库巡检项
  • ​​​​​​​Installing ROS on the Raspberry Pi
  • ​用户画像从0到100的构建思路
  • $emit传递多个参数_PPC和MIPS指令集下二进制代码中函数参数个数的识别方法
  • (01)ORB-SLAM2源码无死角解析-(56) 闭环线程→计算Sim3:理论推导(1)求解s,t
  • （2）MFC+openGL单文档框架glFrame
  • （2）nginx 安装、启停
  • （20）目标检测算法之YOLOv5计算预选框、详解anchor计算
  • （八）光盘的挂载与解挂、挂载CentOS镜像、rpm安装软件详细学习笔记
  • （附源码）spring boot校园健康监测管理系统 毕业设计 151047
  • (解决办法)ASP.NET导出Excel，打开时提示“您尝试打开文件'XXX.xls'的格式与文件扩展名指定文件不一致
  • （转）我也是一只ＩＴ小小鸟
  • ***微信公众号支付+微信H5支付+微信扫码支付+小程序支付+APP微信支付解决方案总结...
  • .bat批处理（九）：替换带有等号=的字符串的子串
  • .bat批处理（十）：从路径字符串中截取盘符、文件名、后缀名等信息
  • .class文件转换.java_从一个class文件深入理解Java字节码结构
  • .cn根服务器被攻击之后
  • .NET 3.0 Framework已经被添加到WindowUpdate
  • .NET 编写一个可以异步等待循环中任何一个部分的 Awaiter
  • .NET 读取 JSON格式的数据
  • .net 逐行读取大文本文件_如何使用 Java 灵活读取 Excel 内容 ？
  • .Net8 Blazor 尝鲜
  • /var/spool/postfix/maildrop 下有大量文件
  • ：中兴通讯为何成功
  • ?php echo ?,?php echo Hello world!;?
  • @GlobalLock注解作用与原理解析
  • @Not - Empty-Null-Blank