Oracle恢復實驗(四)
環境:Oracle10g、Red Hat 4,Oracle執行在歸檔模式。
場景:所有控制檔案和資料檔案丟失。
具體步驟:
1、主機斷電,導致資料檔案和所有控制檔案全部丟失
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.ctl
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.dbf
SQL> !ls /home/oracle/oracle/product/oradata/orcl
redo01.log redo02.log redo03.log
SQL> shutdown abort
ORACLE instance shut down.
2、嘗試開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
提示找不到控制檔案
3、拷貝資料檔案和控制檔案
SQL> !cp /disk2/oracle/coldbak/*.ctl /home/oracle/oracle/product/oradata/orcl/
SQL> !cp /disk2/oracle/coldbak/*.dbf /home/oracle/oracle/product/oradata/orcl/
4、嘗試開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1:
'/home/oracle/oracle/product/oradata/orcl/redo02.log'
提示控制檔案比較舊,記錄的日誌序號比redo日誌的小。
5、獲取重建控制檔案的指令碼
SQL> alter database backup controlfile to trace;
Database altered.
從udump目錄中找到指令碼。
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/oracle/product/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/home/oracle/oracle/product/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/home/oracle/oracle/product/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/oracle/product/oradata/orcl/system01.dbf',
'/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf',
'/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf',
'/home/oracle/oracle/product/oradata/orcl/users01.dbf'
CHARACTER SET AL32UTF8
;
6、重啟資料庫到nomount狀態、執行重建控制檔案指令碼
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
7、嘗試開啟資料庫
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oracle/product/oradata/orcl/system01.dbf'
8、介質恢復
SQL> recover database;
Media recovery complete.
9、開啟資料庫
SQL> alter database open;
Database altered.
SQL> conn scott/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
RECOVER_TEST TABLE
RECOVER_TEST2 TABLE
6 rows selected.
SQL> select count(*) from recover_test2;
COUNT(*)
----------
28672
場景:所有控制檔案和資料檔案丟失。
具體步驟:
1、主機斷電,導致資料檔案和所有控制檔案全部丟失
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.ctl
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.dbf
SQL> !ls /home/oracle/oracle/product/oradata/orcl
redo01.log redo02.log redo03.log
SQL> shutdown abort
ORACLE instance shut down.
2、嘗試開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
提示找不到控制檔案
3、拷貝資料檔案和控制檔案
SQL> !cp /disk2/oracle/coldbak/*.ctl /home/oracle/oracle/product/oradata/orcl/
SQL> !cp /disk2/oracle/coldbak/*.dbf /home/oracle/oracle/product/oradata/orcl/
4、嘗試開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1:
'/home/oracle/oracle/product/oradata/orcl/redo02.log'
提示控制檔案比較舊,記錄的日誌序號比redo日誌的小。
5、獲取重建控制檔案的指令碼
SQL> alter database backup controlfile to trace;
Database altered.
從udump目錄中找到指令碼。
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/oracle/product/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/home/oracle/oracle/product/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/home/oracle/oracle/product/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/oracle/product/oradata/orcl/system01.dbf',
'/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf',
'/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf',
'/home/oracle/oracle/product/oradata/orcl/users01.dbf'
CHARACTER SET AL32UTF8
;
6、重啟資料庫到nomount狀態、執行重建控制檔案指令碼
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 130023424 bytes
Fixed Size 1218100 bytes
Variable Size 62917068 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
7、嘗試開啟資料庫
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oracle/product/oradata/orcl/system01.dbf'
8、介質恢復
SQL> recover database;
Media recovery complete.
9、開啟資料庫
SQL> alter database open;
Database altered.
SQL> conn scott/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
RECOVER_TEST TABLE
RECOVER_TEST2 TABLE
6 rows selected.
SQL> select count(*) from recover_test2;
COUNT(*)
----------
28672
相關文章
- Oracle恢復實驗(一)Oracle
- Oracle恢復實驗(二)Oracle
- Oracle恢復實驗(三)Oracle
- oracle實驗記錄 (恢復-完全恢復)Oracle
- oracle實驗記錄 (恢復-rman恢復)Oracle
- Oracle資料庫特種恢復技術(四)--實驗篇Oracle資料庫
- oracle實驗記錄 (恢復-不完全恢復)Oracle
- 【Oracle 恢復表空間】 實驗Oracle
- oracle實驗記錄 (恢復-redo)Oracle
- oracle media recovery介質恢復實驗-Oracle
- oracle實驗記錄(恢復-checkpoint cnt)Oracle
- oracle實驗記錄 (可恢復session)OracleSession
- oracle實驗記錄 (恢復-rman catalog)Oracle
- oracle 資料庫全庫恢復實驗Oracle資料庫
- Oracle常規恢復的實驗測試Oracle
- oracle實驗記錄 (恢復-rman保留策略)Oracle
- oracle實驗記錄 (恢復-使用resetlogs open前備份恢復)Oracle
- oracle實驗記錄 (恢復-rman基於控制檔案的恢復)Oracle
- oracle實驗記錄 (恢復-恢復未備份的資料檔案)Oracle
- oracle實驗記錄 (恢復-rman維護(1))Oracle
- oracle實驗記錄 (恢復-rman維護(2))Oracle
- oracle實驗記錄 (恢復-rman增量備份)Oracle
- oracle實驗記錄 (恢復-表空間基於時間點恢復(rman))Oracle
- oracle實驗記錄 (恢復-rman reset incatnation(1))Oracle
- oracle實驗記錄 (恢復-關於熱備份)Oracle
- oracle實驗記錄 (恢復read only tablespace(1))Oracle
- oracle實驗記錄 (恢復read only tablespace(2))Oracle
- oracle實驗記錄 (恢復-rman操作delete(all) input )Oracledelete
- oracle 增量備份恢復驗證Oracle
- oracle實驗記錄 (恢復-表空間基於時間點恢復(手動))Oracle
- Oracle備份恢復實驗(原廠培訓資料)Oracle
- oracle實驗記錄 (恢復-關於控制檔案(1))Oracle
- oracle實驗記錄 (恢復-關於控制檔案(2))Oracle
- oracle實驗記錄 (恢復-關於控制檔案(3))Oracle
- oracle實驗記錄 (恢復-關於控制檔案(4))Oracle
- oracle實驗記錄 (恢復-rman操作(設定&備份))Oracle
- oracle實驗記錄 (oracle 10G dataguard(9)rman恢復與dg)Oracle
- Oracle備份與恢復系列 (一)實驗環境介紹Oracle