[20170213]現有控制檔案恢復到以前時間點
[20170213]現有控制檔案恢復到以前時間點.txt
--如果使用現有控制檔案是否可以恢復以前的時間點?假設這個時間點之後還增加了資料檔案,這樣新的控制檔案裡面有記錄.
--恢復後是什麼情況呢? 透過真實的測試來說明問題.
--一般我做全備份完成後會在指令碼最後加入備份當前控制檔案的命令,如果我需要恢復,我會選擇這個控制檔案來恢復.
--而上面的情況呢?
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
2.測試前準備:
create table t1 (id number,d date);
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> set numw 12
SCOTT@book> select * from t1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28
--//使用rman做全備.
RMAN> backup database format '/u01/backup/full_20170213_%U';
RMAN> backup archivelog all not backed up 1 times tag='archivelog 20170213' format '/u01/backup/archive_20170213_%U';
insert into t1 select current_scn,sysdate from v$database ;
commit ;
--//建立新的資料檔案以及表空間.
CREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> create table tt tablespace tea as select * from emp ;
Table created.
insert into t1 select current_scn,sysdate from v$database ;
commit ;
SCOTT@book> select * from t1 order by 1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28 -->備份前
13276257730 2017-02-13 15:09:29 -->備份後
13276258059 2017-02-13 15:10:25 -->備份後並且增加1個資料檔案.
13276258118 2017-02-13 15:11:05 -->往增加的表空間建立表tt.
3.關閉資料庫僅僅保留控制檔案.
--//假設現在要恢復到scn=13276257730.首先做一個冷備份略.
--//刪除資料檔案以及日誌檔案:
$ cd /mnt/ramdisk/book
$ rm -f *.dbf
$ rm -f *.log
$ ls -l
total 20952
-rw-r----- 1 oracle oinstall 10698752 2017-02-13 15:13:05 control01.ctl
-rw-r----- 1 oracle oinstall 10698752 2017-02-13 15:13:05 control02.ctl
--//僅僅保留控制檔案.
4.開始恢復:
--//恢復到scn=13276257730
$ cat 13276257730.rman
run {
set until scn 13276257730;
restore database;
recover database;
}
RMAN> startup mount ;
Oracle instance started
database mounted
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
RMAN> run {
2> set until scn 13276257730;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 2017-02-13 15:21:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=12 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /mnt/ramdisk/book/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/full_20170213_f0rsfurm_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /mnt/ramdisk/book/system01.dbf
channel ORA_DISK_2: restoring datafile 00005 to /mnt/ramdisk/book/example01.dbf
channel ORA_DISK_2: reading from backup piece /u01/backup/full_20170213_eursfurm_1_1
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /mnt/ramdisk/book/sysaux01.dbf
channel ORA_DISK_3: reading from backup piece /u01/backup/full_20170213_evrsfurm_1_1
channel ORA_DISK_1: piece handle=/u01/backup/full_20170213_f0rsfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_3: piece handle=/u01/backup/full_20170213_evrsfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/u01/backup/full_20170213_eursfurm_1_1 tag=TAG20170213T150838
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
Finished restore at 2017-02-13 15:21:15
Starting recover at 2017-02-13 15:21:15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 469 is already on disk as file /u01/app/oracle/archivelog/book/1_469_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_469_896605872.dbf thread=1 sequence=469
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2017 15:21:17
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/archivelog/book/1_469_896605872.dbf'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> **end-of-file**
--//昏,忘記切換了.
SYS@book> recover database until change 13276257730;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/mnt/ramdisk/book/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//昏也不行.測試疏忽了.先從冷備份恢復過來看看.
$ cp /u01/backup/20170213/redo01.log /mnt/ramdisk/book/
RMAN> @ 13276257730.rman
RMAN> run {
2> set until scn 13276257730;
3> #restore database;
4> recover database;
5> }
executing command: SET until clause
Starting recover at 2017-02-13 15:31:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=12 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2017-02-13 15:31:18
RMAN> **end-of-file**
RMAN> report schema ;
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 930 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 350 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 570 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 0 TEA *** /mnt/ramdisk/book/tea01.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
--//因為控制檔案有記錄,這裡顯示size=0.
SYS@book> alter system set log_archive_dest_state_2=defer ;
System altered.
SYS@book> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery
--無法只讀開啟.
SYS@book> alter database open resetlogs;
Database altered.
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 930 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 350 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 570 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
--//可以發現open resetlog後正常.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS NAME
----- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
1 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13276257734 2017-02-13 15:35:01 0 13276257730 13276257731 ONLINE /mnt/ramdisk/book/users01.dbf
5 13276257734 2017-02-13 15:35:01 0 2017-01-16 22:00:05 13276257730 13276257731 ONLINE /mnt/ramdisk/book/example01.dbf
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ------------------------------
1 13276257734 2017-02-13 15:35:01 7 13276257731 ONLINE 631 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13276257734 2017-02-13 15:35:01 1834 13276257731 ONLINE 620 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13276257734 2017-02-13 15:35:01 923328 13276257731 ONLINE 541 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13276257734 2017-02-13 15:35:01 16143 13276257731 ONLINE 625 YES /mnt/ramdisk/book/users01.dbf USERS
5 13276257734 2017-02-13 15:35:01 952916 13276257731 ONLINE 537 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
SYS@book> select * from scott.t1;
ID D
------------ -------------------
13276257537 2017-02-13 15:07:28
--^_^,應該在增加scn才能看到第2條記錄.
5.還原:
SYS@book> shutdown abort ;
ORACLE instance shut down.
$ cp /u01/backup/20170213/* /mnt/ramdisk/book/ */
SYS@book> alter system set log_archive_dest_state_2=enable ;
System altered.
--從測試看是沒有什麼問題的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2133458/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman恢復到指定時間點
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 與控制檔案有關的恢復
- 恢復測試：擁有當時的全部歸檔，控制檔案，恢復丟失的資料檔案。
- db2恢復到指定時間點DB2
- 控制檔案恢復—從trace檔案中恢復
- 與控制檔案有關的恢復(二)
- 與控制檔案有關的恢復1
- 與控制檔案有關的恢復2
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 控制檔案恢復—從快照中恢復
- 恢復到特定點（時間點、scn、日誌序列號），rman不完全恢復
- rman恢復--丟失控制檔案的恢復
- cp方式恢復控制檔案
- 控制檔案恢復測試
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 使用rman恢復控制檔案
- 同時丟失控制檔案與資料檔案的恢復
- rman給歸檔指定備份位置；庫先備份盤同時到帶庫；更改控制檔案自備路徑；時間點的恢復
- RMAN恢復表空間,資料檔案,歸檔檔案,控制檔案等介紹
- 控制檔案丟失恢復(二)
- 恢復丟失的控制檔案
- 控制檔案的恢復方法（一）
- 控制檔案的恢復方法（二）
- 控制檔案的恢復方法（三）
- 控制檔案的恢復方法（四）
- 控制檔案全部丟失恢復
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 使用舊的控制檔案備份來恢復控制檔案
- rman恢復資料檔案 恢復表空間
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- MySQL使用bin-log異庫恢復到指定時間點MySql