oracle media recovery介質恢復實驗-
oracle介質恢復的概念
http://space.itpub.net/9240380/viewspace-757008
如何控制檔案檢查點scn和資料檔案頭及資料檔案檢查點scn不致,則需要介質恢復
測試思路：
1,開啟歸檔
2,備份21號檔案
3,檢視備份後的各個scn
4,關庫
5,用備份21資料檔案替換當前的21資料檔案
6,啟動庫到mount狀態
7,檢視各個scn
8,檢視alert是否發生介質恢復（我們知道介質恢復要人工操作）
9,手工進行介質恢復
10,檢視open後的各個scn
/************開始測試****************/
1,開啟歸檔
SQL> startup mount
ORA-32004: obsolete or deprecated parameter
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
SQL> select log_mode from v$database;
LOG_MODE
------------------------
NOARCHIVELOG
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
/*********配置歸檔目錄,不然即使開啟歸檔無地可歸檔***********/
SQL> alter system set log_archive_dest='d:\archive';
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 825 52428800 512 1 YES INACTIVE 10592541 2013/3/25 1 10620531 2013/3/26 9
4 1 826 209715200 512 1 YES ACTIVE 10620531 2013/3/26 9 10628796 2013/3/26 1
5 1 827 209715200 512 1 NO CURRENT 10628796 2013/3/26 1 281474976710
6 1 820 209715200 512 1 YES INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 YES INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 YES INACTIVE 10561202 2013/3/25 1 10588922 2013/3/25 1
9 1 824 209715200 512 1 YES INACTIVE 10591778 2013/3/25 1 10592541 2013/3/25 1
10 1 823 20971520 512 1 YES INACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
/********active日誌已歸檔************/
SQL> select * from v$archived_log;
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
1 811078878 D:\ARCHIVE\ARC0000000826_0803485505.0001 1 1 826 1 2012/12/31 14: 803485505 10620531 2013/3/26 9 10628796 2013/3/26 1 27049 512 ARCH ARCH NO YES NO NO A 2013/3/26 11:41 NO NO NO 0 1 1331289025 NO NO NO NO
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10628796
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10628796
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10628796
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10628796
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10628796
6 C:\TBS_1.DBF 10628796
7 C:\TBS_1_OTHER.DBF 10628796
8 C:\TBS_2.DBF 10628796
9 C:\TBS_2_OTHER.DBF 10628796
10 C:\TBS_HANG1.DBF 10628796
11 D:\TBS_AUTO.DBF 10628796
12 C:\TBS_NON_STANDARD.DBF 10628796
13 C:\1.DBF 10628796
14 C:\2.DBF 10628796
15 C:\3.DBF 10628796
16 C:\5.DBF 10628796
17 C:\9.DBF 10628796
18 C:\22.DBF 10628796
19 C:\19.DBF 10628796
20 C:\222.DBF 10628796
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10628796
21 rows selected
2,備份21號檔案
/**********手工備份後對資料檔案進行dml*************/
SQL> create table tbs_16(a int) tablespace tbs_16k;
Table created
SQL> insert into tbs_16 values(1);
1 row inserted
SQL> commit;
Commit complete
/****強制手工檢查點*************/
SQL> alter system checkpoint;
System altered
3,檢視備份後的各個scn
SQL> select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629687
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629687
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629687
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629687
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629687
6 C:\TBS_1.DBF 10629687
7 C:\TBS_1_OTHER.DBF 10629687
8 C:\TBS_2.DBF 10629687
9 C:\TBS_2_OTHER.DBF 10629687
10 C:\TBS_HANG1.DBF 10629687
11 D:\TBS_AUTO.DBF 10629687
12 C:\TBS_NON_STANDARD.DBF 10629687
13 C:\1.DBF 10629687
14 C:\2.DBF 10629687
15 C:\3.DBF 10629687
16 C:\5.DBF 10629687
17 C:\9.DBF 10629687
18 C:\22.DBF 10629687
19 C:\19.DBF 10629687
20 C:\222.DBF 10629687
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629687
21 rows selected
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629687
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629687
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629687
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629687
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629687
6 C:\TBS_1.DBF 10629687
7 C:\TBS_1_OTHER.DBF 10629687
8 C:\TBS_2.DBF 10629687
9 C:\TBS_2_OTHER.DBF 10629687
10 C:\TBS_HANG1.DBF 10629687
11 D:\TBS_AUTO.DBF 10629687
12 C:\TBS_NON_STANDARD.DBF 10629687
13 C:\1.DBF 10629687
14 C:\2.DBF 10629687
15 C:\3.DBF 10629687
16 C:\5.DBF 10629687
17 C:\9.DBF 10629687
18 C:\22.DBF 10629687
19 C:\19.DBF 10629687
20 C:\222.DBF 10629687
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629687
21 rows selected
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629687
/*********檢查點後的日誌檔案及歸檔日誌資訊**********/
SQL> select * from v$archived_log;
RECID STAMP NAME DEST_ID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
1 811078878 D:\ARCHIVE\ARC0000000826_0803485505.0001 1 1 826 1 2012/12/31 14: 803485505 10620531 2013/3/26 9 10628796 2013/3/26 1 27049 512 ARCH ARCH NO YES NO NO A 2013/3/26 11:41 NO NO NO 0 1 1331289025 NO NO NO NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 825 52428800 512 1 YES INACTIVE 10592541 2013/3/25 1 10620531 2013/3/26 9
4 1 826 209715200 512 1 YES INACTIVE 10620531 2013/3/26 9 10628796 2013/3/26 1
5 1 827 209715200 512 1 NO CURRENT 10628796 2013/3/26 1 281474976710
6 1 820 209715200 512 1 YES INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 YES INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 YES INACTIVE 10561202 2013/3/25 1 10588922 2013/3/25 1
9 1 824 209715200 512 1 YES INACTIVE 10591778 2013/3/25 1 10592541 2013/3/25 1
10 1 823 20971520 512 1 YES INACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
4,關庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
5,用備份21資料檔案替換當前的21資料檔案
6,啟動庫到mount狀態
SQL> startup mount
ORA-32004: obsolete or deprecated paramete
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
7,檢視各個scn,發現資料檔案頭的檢查點scn與其它不一樣
select file#,name,checkpoint_change# from v$datafile_header
FILE# NAME CHECKPOINT_CHANGE#
----- -------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629758
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629758
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629758
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629758
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629758
6 C:\TBS_1.DBF 10629758
7 C:\TBS_1_OTHER.DBF 10629758
8 C:\TBS_2.DBF 10629758
9 C:\TBS_2_OTHER.DBF 10629758
10 C:\TBS_HANG1.DBF 10629758
11 D:\TBS_AUTO.DBF 10629758
FILE# NAME CHECKPOINT_CHANGE#
----- -------------------------------------------------- ------------------
12 C:\TBS_NON_STANDARD.DBF 10629758
13 C:\1.DBF 10629758
14 C:\2.DBF 10629758
15 C:\3.DBF 10629758
16 C:\5.DBF 10629758
17 C:\9.DBF 10629758
18 C:\22.DBF 10629758
19 C:\19.DBF 10629758
20 C:\222.DBF 10629758
21 C:\TBS16K_1.DBF 10628796 --明顯看到這個scn小於其它的
21 rows selected.
/**你發現即使用了備份的資料檔案,21資料檔案的檢查點scn與其它是一致,所以v$datafile與v$datafile_header是不一樣的檢查點****/
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629758
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629758
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629758
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629758
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629758
6 C:\TBS_1.DBF 10629758
7 C:\TBS_1_OTHER.DBF 10629758
8 C:\TBS_2.DBF 10629758
9 C:\TBS_2_OTHER.DBF 10629758
10 C:\TBS_HANG1.DBF 10629758
11 D:\TBS_AUTO.DBF 10629758
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------
12 C:\TBS_NON_STANDARD.DBF 10629758
13 C:\1.DBF 10629758
14 C:\2.DBF 10629758
15 C:\3.DBF 10629758
16 C:\5.DBF 10629758
17 C:\9.DBF 10629758
18 C:\22.DBF 10629758
19 C:\19.DBF 10629758
20 C:\222.DBF 10629758
21 C:\TBS16K_1.DBF 10629758
21 rows selected.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629758
8,檢視alert是否發生介質恢復（我們知道介質恢復要人工操作）
/*******提示要介質恢復******/
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: 'C:\TBS16K_1.DBF'
9,手工進行介質恢復
/****** 手工介質恢復*********/
SQL> recover datafile 21;
Media recovery complete.
SQL>
/******檢視alert看介質恢復的內容*****/
ALTER DATABASE RECOVER datafile 21
Media Recovery Start --介質恢復開始
Serial Media Recovery started
WARNING! Recovering data file 21 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command. ---提示很明確因為我是線上用os 備份的21資料檔案
Recovery of Online Redo Log: Thread 1 Group 5 Seq 827 Reading mem 0 --介質恢復應用了827號日誌,827號日誌是介質恢復之前current的日誌
Mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO05.LOG
Completed: ALTER DATABASE RECOVER datafile 21 --介質恢復結束
10,檢視open後的各個scn,發現介質恢復後各個scn又達到一致，資料庫又迴歸到了正常狀態
SQL> alter database open;
Database altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10629761
SQL> select file#,name,checkpoint_change# from v$datafile;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629761
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629761
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629761
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629761
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629761
6 C:\TBS_1.DBF 10629761
7 C:\TBS_1_OTHER.DBF 10629761
8 C:\TBS_2.DBF 10629761
9 C:\TBS_2_OTHER.DBF 10629761
10 C:\TBS_HANG1.DBF 10629761
11 D:\TBS_AUTO.DBF 10629761
12 C:\TBS_NON_STANDARD.DBF 10629761
13 C:\1.DBF 10629761
14 C:\2.DBF 10629761
15 C:\3.DBF 10629761
16 C:\5.DBF 10629761
17 C:\9.DBF 10629761
18 C:\22.DBF 10629761
19 C:\19.DBF 10629761
20 C:\222.DBF 10629761
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629761
21 rows selected
SQL> select file#,name,checkpoint_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
1 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSTEM01.DBF 10629761
2 D:\ORACLE11G_64BIT\ORADATA\ORCL\SYSAUX01.DBF 10629761
3 D:\ORACLE11G_64BIT\ORADATA\ORCL\UNDOTBS01.DBF 10629761
4 D:\ORACLE11G_64BIT\ORADATA\ORCL\USERS01.DBF 10629761
5 D:\ORACLE11G_64BIT\ORADATA\ORCL\SELF_LEARN.DBF 10629761
6 C:\TBS_1.DBF 10629761
7 C:\TBS_1_OTHER.DBF 10629761
8 C:\TBS_2.DBF 10629761
9 C:\TBS_2_OTHER.DBF 10629761
10 C:\TBS_HANG1.DBF 10629761
11 D:\TBS_AUTO.DBF 10629761
12 C:\TBS_NON_STANDARD.DBF 10629761
13 C:\1.DBF 10629761
14 C:\2.DBF 10629761
15 C:\3.DBF 10629761
16 C:\5.DBF 10629761
17 C:\9.DBF 10629761
18 C:\22.DBF 10629761
19 C:\19.DBF 10629761
20 C:\222.DBF 10629761
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10629761
21 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-757153/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle media recovery介質恢復小記Oracle
- ASM管理環境----資料檔案丟失介質恢復（MEDIA RECOVERY）ASM
- Oracle介質恢復（二）Oracle
- Oracle介質恢復（三）Oracle
- Oracle介質恢復（一）Oracle
- Oracle例項恢復和介質恢復Oracle
- Backup And Recovery User's Guide-介質恢復問題解決-執行試驗恢復GUIIDE
- oracle database 例項恢復和介質恢復OracleDatabase
- Backup And Recovery User's Guide-恢復的取消(並行介質恢復)GUIIDE並行
- Backup And Recovery User's Guide-介質恢復問題解決-調查介質恢復問題：階段1GUIIDE
- Oracle恢復實驗（一）Oracle
- Oracle恢復實驗（二）Oracle
- Oracle恢復實驗（三）Oracle
- Oracle恢復實驗（四）Oracle
- oracle實驗記錄 （恢復-完全恢復）Oracle
- oracle實驗記錄 （恢復-rman恢復）Oracle
- Backup And Recovery User's Guide-介質恢復問題解決GUIIDE
- Backup And Recovery User's Guide-RMAN資料修復概念-RMAN介質恢復GUIIDE
- oracle實驗記錄 （恢復-不完全恢復）Oracle
- SCN、Checkpoint、例項恢復介質恢復理解
- 塊介質恢復（BLOCKRECOVER命令）BloC
- Disk Drill Media Recovery for Mac(支援多種格式的資料恢復工具)Mac資料恢復
- 【Oracle 恢復表空間】 實驗Oracle
- oracle實驗記錄 （恢復-redo）Oracle
- Oracle備份與恢復系列 （一）實驗環境介紹Oracle
- Backup And Recovery User's Guide-使用者管理的介質恢復概覽GUIIDE
- oracle實驗記錄（恢復-checkpoint cnt）Oracle
- oracle實驗記錄 （可恢復session）OracleSession
- 基於RMAN實現壞塊介質恢復(blockrecover)BloC
- oracle scn與備份恢復backup recovery(一)Oracle
- oracle instance recovery例項恢復小記Oracle
- Backup And Recovery User's Guide-備份和恢復介紹-Oracle備份和恢復解決方案GUIIDEOracle
- oracle實驗記錄 （恢復-rman catalog）Oracle
- oracle 資料庫全庫恢復實驗Oracle資料庫
- Oracle常規恢復的實驗測試Oracle
- oracle實驗記錄 （恢復-rman保留策略）Oracle
- oracle實驗記錄 （恢復-使用resetlogs open前備份恢復）Oracle
- oracle實驗記錄 （恢復-rman基於控制檔案的恢復）Oracle