oracle重建控制檔案遺失資料檔案導致悲劇

資料庫最初故障
Thu Sep 25 09:27:26 2014
MMON started with pid=15, OS id=1968
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = F:＼oracle
Thu Sep 25 09:27:26 2014
ALTER DATABASE   MOUNT
Thu Sep 25 09:27:26 2014
MMNL started with pid=16, OS id=5976
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_4624.trc:
ORA-00202: ????: ''F:＼ORACLE＼ORADATA＼ORCL＼CONTROL01.CTL
ORA-27070: ????/????
OSD-04006: ReadFile() 失敗, 無法讀取檔案
O/S-Error: (OS 23) 資料錯誤(迴圈冗餘檢查)。
Thu Sep 25 09:28:31 2014
ORA-204 signalled during: ALTER DATABASE   MOUNT...
因為硬體或者系統層面問題，導致控制檔案無法正常訪問

重建控制檔案
Fri Sep 26 12:28:44 2014
Successful mount of redo thread 1, with mount id 1387065723
Completed: CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 2
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'F:＼oracle＼oradata＼orcl＼REDO01.LOG'  SIZE 50M,  --redo log ????
  GROUP 2 'F:＼oracle＼oradata＼orcl＼REDO02.LOG'  SIZE 50M,  --redo log ????
  GROUP 3 'F:＼oracle＼oradata＼orcl＼REDO03.LOG'  SIZE 50M  --redo log ????
-- STANDBY LOGFILE
DATAFILE
  'F:＼oracle＼oradata＼orcl＼SYSAUX01.DBF',  --sysaux???????
  'F:＼oracle＼oradata＼orcl＼SYSTEM01.DBF',
  'F:＼oracle＼oradata＼orcl＼USERS01.DBF', --user????????
  'F:＼oracle＼oradata＼orcl＼UNDOTBS01.DBF' --undo???????
CHARACTER SET ZHS16GBK
Fri Sep 26 12:29:55 2014
alter database open resetlogs
ORA-1194 signalled during: alter database open resetlogs...
埋下了雷，建立控制檔案中未全部列舉出來所有資料檔案

進行不完全恢複,嘗試resetlogs庫發現redo異常
Fri Sep 26 14:13:24 2014
ALTER DATABASE   MOUNT
Fri Sep 26 14:13:24 2014
MMNL started with pid=16, OS id=9024
Successful mount of redo thread 1, with mount id 1387037444
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Sep 26 14:14:08 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Fri Sep 26 14:15:16 2014
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc:
ORA-00333: 重做日誌讀取塊 2049 計數 6143 出錯
ORA-00312: 聯機日誌 1 線程 1: 'F:＼ORACLE＼ORADATA＼ORCL＼REDO01.LOG'
ORA-27070: 非同步讀取/寫入失敗
OSD-04016: 非同步 I/O 請求排隊時出錯。
O/S-Error: (OS 23) 資料錯誤(迴圈冗餘檢查)。
Fri Sep 26 14:16:24 2014
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc:
ORA-00333: 重做日誌讀取塊 1 計數 8191 出錯
ORA-00312: 聯機日誌 1 線程 1: 'F:＼ORACLE＼ORADATA＼ORCL＼REDO01.LOG'
ORA-27070: 非同步讀取/寫入失敗
OSD-04006: ReadFile() 失敗, 無法讀取檔案
O/S-Error: (OS 23) 資料錯誤(迴圈冗餘檢查)。
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc:
ORA-00333: 重做日誌讀取塊 1 計數 8191 出錯
ARCH: All Archive destinations made inactive due to error 333
使用隱含參數嘗試拉庫,報ORA-600[2662]
Fri Sep 26 14:16:45 2014
SMON: enabling cache recovery
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc  (incident=57761):
ORA-00600: 內部錯誤碼, 參數: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Incident details in: f:＼oracle＼diag＼rdbms＼orcl＼orcl＼incident＼incdir_57761＼orcl_ora_3720_i57761.trc
Fri Sep 26 14:16:45 2014
ARC3 started with pid=23, OS id=9692
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc:
ORA-00704: 引導程式進程失敗
ORA-00704: 引導程式進程失敗
ORA-00600: 內部錯誤碼, 參數: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3720.trc:
ORA-00704: 引導程式進程失敗
ORA-00704: 引導程式進程失敗
ORA-00600: 內部錯誤碼, 參數: [2662], [0], [38221304], [0], [38352371], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3720): terminating the instance due to error 704
Instance terminated by USER, pid = 3720
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3720) as a result of ORA-1092
資料庫在未使用所有資料檔案的情況下,進行了resetlogs操作,悲劇的本質已經註定,我的失誤是沒有評估好現狀,還繼續在錯誤的道路上越走越遠.

我開始接手該庫現況
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Sep 26 14:18:55 2014
alter database open
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_8968.trc:
ORA-01113: 檔案 1 需要介質恢複
ORA-01110: 資料檔案 1: 'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...
Fri Sep 26 14:19:31 2014
alter database open
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_8968.trc:
ORA-01113: 檔案 1 需要介質恢複
ORA-01110: 資料檔案 1: 'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF'
ORA-1113 signalled during: alter database open ...
Fri Sep 26 14:22:26 2014
ALTER DATABASE RECOVER  database 
Media Recovery Start
 started logmerger process
Fri Sep 26 14:22:26 2014
Media Recovery failed with error 16433
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...
Fri Sep 26 14:24:25 2014
ALTER DATABASE RECOVER  datafile 'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF'
Media Recovery Start
Media Recovery failed with error 16433
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF'  ...
Fri Sep 26 14:28:47 2014
alter database open read write
Errors in file f:＼oracle＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_8968.trc:
ORA-01113: 檔案 1 需要介質恢複
ORA-01110: 資料檔案 1: 'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF'
ORA-1113 signalled during: alter database open read write...
Fri Sep 26 14:31:48 2014
ALTER DATABASE RECOVER  datafile 'F:＼oracle＼oradata＼orcl＼SYSTEM01.DBF'
Media Recovery Start
Media Recovery failed with error 16433
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'F:＼oracle＼oradata＼orcl＼SYSTEM01.DBF'  ...
提示ORA-01110: 資料檔案 1需要恢複,嘗試recover操作

嘗試recover操作
串連到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
 
 
SQL> alter database backup controlfile to trace as 'd:＼ctl.txt';
alter database backup controlfile to trace as 'd:＼ctl.txt'
*
第 1 行出現錯誤:
ORA-16433: 必須以讀/寫入模式開啟資料庫。
 
 
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
重建控制檔案
SQL> shutdown immediate;
ORA-01109: 資料庫未開啟
 
 
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL> STARTUP NOMOUNT
ORACLE 常式已經啟動。
 
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE orcl NORESETLOGS FORCE LOGGING ARCHIVELOG
 
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2921
  7  LOGFILE
  8    GROUP 1 'F:＼ORACLE＼ORADATA＼ORCL＼REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'F:＼ORACLE＼ORADATA＼ORCL＼REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'F:＼ORACLE＼ORADATA＼ORCL＼REDO03.LOG'  SIZE 50M
 11  DATAFILE
 12    'F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF',
 13    'F:＼ORACLE＼ORADATA＼ORCL＼SYSAUX01.DBF',
 14    'F:＼ORACLE＼ORADATA＼ORCL＼UNDOTBS01.DBF',
 15    'F:＼ORACLE＼ORADATA＼ORCL＼USERS01.DBF'
 16  CHARACTER SET ZHS16GBK
 17  ;
 
控制檔案已建立。
這一步嚴重發錯,在恢複前未認真看alert日誌,太依賴v$datafile查詢出來結果,導致重建控制檔案遺失資料檔案,埋下大雷。根據前面alert日誌報錯ORA-600 2662,決定一併處理該問題,然後進行恢複
SQL> shutdown immediate;
ORA-01109: ??????
 
 
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL> startup pfile='d:＼pfile.txt'  mount;
ORACLE 常式已經啟動。
 
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
資料庫裝載完畢。
SQL> recover database;
完成介質恢複。
SQL> alter database open;
alter database open
*
第 1 行出現錯誤:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [
資料庫報ORA-600 4194,直接修改undo_management=manual,然後嘗試啟動資料庫
SQL> conn / as sysdba
已串連到空閑常式。
SQL> startup pfile='d:＼pfile.txt'
ORACLE 常式已經啟動。
 
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
 
F:＼ORACLE＼ORADATA＼ORCL＼SYSTEM01.DBF
F:＼ORACLE＼ORADATA＼ORCL＼SYSAUX01.DBF
F:＼ORACLE＼ORADATA＼ORCL＼UNDOTBS01.DBF
F:＼ORACLE＼ORADATA＼ORCL＼USERS01.DBF
F:＼ORACLE＼PRODUCT＼11.2.0＼DBHOME_1＼DATABASE＼MISSING00005
F:＼ORACLE＼PRODUCT＼11.2.0＼DBHOME_1＼DATABASE＼MISSING00006
 
已選擇6行。
 
SQL> alter database rename file 'F:＼ORACLE＼PRODUCT＼11.2.0＼DBHOME_1＼DATABASE＼MISSING00005'
2   to 'F:＼oracle＼oradata＼SOURCE_DATA1.DBF';
 
資料庫已更改。
 
SQL> alter database rename file 'F:＼ORACLE＼PRODUCT＼11.2.0＼DBHOME_1＼DATABASE＼MISSING00006'
2   to 'F:＼oracle＼oradata＼SOURCE_idx1.DBF';
 
資料庫已更改。
 
SQL> shutdown immediate;
資料庫已經關閉。
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL> startup mount pfile='d:＼pfile.txt'
ORACLE 常式已經啟動。
 
Total System Global Area  970895360 bytes
Fixed Size                  1375452 bytes
Variable Size             603980580 bytes
Database Buffers          360710144 bytes
Redo Buffers                4829184 bytes
資料庫裝載完畢。
SQL> alter datafile 5 online;
alter datafile 5 online
      *
第 1 行出現錯誤:
ORA-00940: 無效的 ALTER 命令
 
 
SQL> alter database datafile 5 online;
 
資料庫已更改。
 
SQL> alter database datafile 6 online;
 
資料庫已更改。
 
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 5 belongs to an orphan incarnation
ORA-01110: data file 5: 'F:＼ORACLE＼ORADATA＼SOURCE_DATA1.DBF'
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出現錯誤:
ORA-01139: RESETLOGS 選項僅在不完全資料庫恢複後有效
 
 
SQL> alter database datafile 6 offline;
 
資料庫已更改。
 
SQL> alter database datafile 5 offline;
 
資料庫已更改。
 
SQL> recover database until cancel;
完成介質恢複。
SQL> alter database datafile 6 online;
 
資料庫已更改。
 
SQL> alter database datafile 5 online;
 
資料庫已更改。
 
SQL> alter database open resetlogs;
 
資料庫已更改。
還好結合一些隱含參數僥倖恢複成功,差點到了要使用bbed的程度

這次的恢複告訴我:Oracle資料庫恢複千萬比大意,需要認真分析alert日誌和諮詢客戶做了那些操作,不然可能導致萬劫不複之禁地

聯絡：手機(13429648788) QQ(107644445)
連結：http://www.xifenfei.com/5515.html