實驗環境

作業系統 Redhat5.4 x86
資料庫版本 oracle 11gR2 (11.2.0.1.0)
資料庫為未開啟歸檔模式

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release .. Production on Sun May  :: 

Copyright (c) , , Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     
Current log sequence           

SQL> select LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;

LOG_MODE     OPEN_MODE        FOR
------------ -------------------- ---
NOARCHIVELOG READ WRITE       NO

實驗模拟

1.redo日志組中部分member檔案丢失或損壞

redo日志組部分member檔案丢失，無論目前日志組是inactive， active（非current，current）都不會造成資料丢失，恢複方法也相同，是以不做特别區分。

詳細參見：http://blog.csdn.net/zhuke0203/article/details/71311993#t2

案例模拟

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.
SQL> select group#,sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  INACTIVE
                     NO  INACTIVE
                     NO  CURRENT

SQL> !rm /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.

--資料庫可以正常啟動，alert日志會有相關報錯資訊：
Sun May  :: 
db_recovery_file_dest_size of  MB is % used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.trc:
ORA-: log  of thread  is more recent than control file
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_m000_8957.trc:
ORA-: open failed for members of log group  of thread 
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information: 
Checker run found  new persistent data failures

案例恢複

由于損壞的隻是日志組中的部分member，是以可以将損壞的member file檔案删除以後重新添加進來：

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';
alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
*
ERROR at line :
ORA-: log  is the current log for thread  - cannot drop members
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log'
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log'


SQL> alter system switch logfile;

System altered.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  ACTIVE
                     NO  CURRENT
                     NO  INACTIVE

SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log';

Database altered.

SQL> alter database add logfile member '/u01/app/oracle/oradata/PROD1/disk1/redo01a.log' reuse to group ;

Database altered.

SQL> !ls -la /u01/app/oracle/oradata/PROD1/disk1/redo01a.log
-rw-r-----  oracle oinstall  May   : /u01/app/oracle/oradata/PROD1/disk1/redo01a.log

這種情況資料庫可以正常啟動，不會造成資料丢失。

2.非active redo日志組中所有member檔案都丢失或損壞

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  ACTIVE
                     NO  CURRENT
                     NO  INACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
ORA-: end-of-file on communication channel
Process ID: 
Session ID:  Serial number:

案例恢複

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;    

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  INACTIVE
                     NO  INACTIVE
                     NO  CURRENT

SQL> alter database clear logfile group ;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

這種情況資料庫無法正常啟動，恢複不會造成資料丢失。

3.active redo日志組中所有member檔案都丢失或損壞

ACTIVE redo日志組無論是否為current狀态，恢複方法相同。

案例模拟

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  CURRENT
                     NO  INACTIVE
                     NO  ACTIVE

SQL> !rm /u01/app/oracle/oradata/PROD1/disk3/redo03a.log /u01/app/oracle/oradata/PROD1/disk4/redo03b.log

SQL> startup force;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
ORA-: open failed for members of log group  of thread 
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information:

案例恢複

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> select * from v$logfile;  

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
      INVALID ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, sequence#, members, archived, status from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
                     NO  CURRENT
                     NO  ACTIVE
                     NO  INACTIVE

--嘗試clear日志組：
SQL> alter database clear logfile group ;
alter database clear logfile group 
*
ERROR at line :
ORA-: log  needed for crash recovery of instance PROD1 (thread )
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk3/redo03a.log'
ORA-: online log  thread : '/u01/app/oracle/oradata/PROD1/disk4/redo03b.log'

--嘗試recover database：
SQL> recover database using backup controlfile;
ORA-: change  generated at // :: needed for thread 
ORA-: suggestion : /u01/app/oracle/flash_recovery_area/PROD1/archivelog/_05_07/o1_mf_1_24_%u_.arc
ORA-: change  for thread  is in sequence #24


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

ORA-: cannot open archived log '/u01/app/oracle/flash_recovery_area/PROD1/archivelog/_05_07/o1_mf_1_24_%u_.arc'
ORA-: unable to obtain file status
Linux Error: : No such file or directory
Additional information: 

--嘗試resetlogs:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line :
ORA-: file  needs media recovery
ORA-: data file : '/u01/app/oracle/oradata/PROD1/disk1/system01.dbf'

--嘗試設定隐藏參數，讓資料庫跳過一緻性檢查：
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

--将隐藏參數重置回預設值：
SQL> alter system reset "_allow_resetlogs_corruption";

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area   bytes
Fixed Size           bytes
Variable Size          bytes
Database Buffers       bytes
Redo Buffers             bytes
Database mounted.
Database opened.
SQL> 
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> set line 
SQL> col member for a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                           IS_
---------- ------- ------- ------------------------------------------------------------ ---
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo01b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk1/redo01a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk2/redo02a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo02b.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk3/redo03a.log      NO
          ONLINE  /u01/app/oracle/oradata/PROD1/disk4/redo03b.log      NO

 rows selected.

SQL> select group#, members, sequence#, archived, status from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
                      NO  CURRENT
                      YES UNUSED
                      YES UNUSED

--注：這種方法慎用，隻有當其它方法都嘗試了無效，作為最後一種嘗試的時候使用，設定隐藏參數有可能造成意想不到的後果。

該情況下，資料庫無法正常啟動，會造成部分資料丢失。

ORACE 非歸檔模式redo檔案恢複實驗環境實驗模拟

實驗環境

實驗模拟

1.redo日志組中部分member檔案丢失或損壞

案例模拟

案例恢複

2.非active redo日志組中所有member檔案都丢失或損壞

案例模拟

案例恢複

3.active redo日志組中所有member檔案都丢失或損壞

案例模拟

案例恢複

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List