[20171110]_allow_read_only_corruption參數.txt

来源:http://www.cnblogs.com/lfree/archive/2017/11/10/7813250.html
-Advertisement-
Play Games

[20171110]_allow_read_only_corruption參數.txt--//昨天在修改查詢隱含參數腳本時發現一個參數_allow_read_only_corruption,感覺應該可以在異常關閉的情況下以read only打開.--//自己測試看看.1.環境:SYS@book> @ ...


[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/[email protected]' 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/[email protected]'
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/[email protected]'
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/[email protected]'
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/[email protected]'
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/[email protected]'
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/[email protected]'
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.


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • iOS-版本控制工具SmartSVN的使用【Mac SmartSVN】
    http://m.blog.csdn.net/potato512/article/details/51487643 ...
  • csv格式的數據存儲到mysql
    python寫的，有點冗餘,先碼出來~~~~ 這是data_stored.py的代碼 1 # -*- coding:utf-8 -*- 2 # 存數據到mysql (只存了時間數字） 3 import pymysql 4 import csv 5 import datetime 6 import s ...
  • 如何利用sqoop將hive數據導入導出數據到mysql
    運行環境 centos 5.6 hadoop hive sqoop是讓hadoop技術支持的clouder公司開發的一個在關係資料庫和hdfs,hive之間數據導入導出的一個工具。 上海尚學堂hadoop大數據培訓組原創，陸續有hadoop大數據技術相關文章奉上，請多關註！ 在使用過程中可能遇到的問 ...
  • MySQL多表
    一、外鍵 1.外鍵：鏈接兩張表的欄位，通過主表的主鍵和從表的外鍵來描述主外鍵關係，呈現的是一對多的關係。例如：商品類別（一）對商品（多），主表：商品類別表，從表：商品表。 2.外鍵的特點：從表外鍵的值是對主表主鍵的引用、從表外鍵類型必須與主表主鍵類型一致 3.聲明外鍵約束 語法：alter tabl ...
  • Oracle SYS用戶無法設置session級別的read only
    官方文檔參考：SYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For exam ...
  • 獲取v$latch數據源實驗
    實驗環境：Oracle Rac 11.2.0.3 首先獲取v$latch的定義：通過PL/SQL或者get ddl等常規途徑只能獲取到v_$latch相關的視圖信息。需要通過特殊方法獲取v$latch的ddl可以通過set autotrace traceonly開啟狀態下執行select * fro ...
  • python編寫的簡單的mysql巡檢腳本
    準備工作：1 安裝python 3.5，本次使用源碼安裝。2 安裝psutil模塊，使用python3.5自帶的easy_install包直接運行cd /opt/python3/bin./easy_install-3.5 psuitl安裝3 安裝mysql_connector模塊，同樣使用easy_ ...
  • Oracle RAC和SCAN同時對外提供服務的配製方法
    1, tnsnames.ora on two nodes:RACTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racscan.test.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DED ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類