[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 與控制檔案有關的恢復
- 【轉載】TortoiseSVN怎麼恢復到以前版本-恢復到以前版本的方法
- 與控制檔案有關的恢復(二)
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- PostgreSQL 時間點恢復SQL
- win10恢復到上一個時間節點的檔案還在嗎Win10
- PostgreSQL啟動恢復期間，恢復到的時間線的確定SQL
- 如何將 Mac 恢復還原到以前的日期Mac
- mongodb 基於oplog的時間點恢復MongoDB
- RAC控制檔案恢復（三種不同情況）
- [20210225]控制檔案序列號滿的恢復.txt
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- 同名檔案替換怎麼恢復，恢復同名檔案
- DM8 基於時間點的恢復
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- rman恢復控制檔案的一個小錯誤
- PostgreSQL12中實現增量備份與任意時間點恢復SQL
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- word怎麼恢復儲存前的檔案，word檔案恢復
- 檔案替換後怎麼恢復，恢復被覆蓋的檔案
- 檔案中勒索恢復
- 剪下後的檔案可以恢復嗎？恢復剪下檔案怎麼辦？
- word自動儲存的檔案怎麼恢復，word檔案恢復
- C盤重灌還能恢復以前檔案嘛，重灌C盤資料備份清單
- mongodb使用備份後的oplog做時間點恢復MongoDB
- 7.5 使用binary log 做基於時間點的恢復
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復資料庫
- eclipse 恢復誤刪檔案Eclipse
- 通過事務日誌恢復SqlServer資料庫到一個特定的時間點SQLServer資料庫
- 看詳細到秒的檔案時間
- qq檔案失效怎麼恢復 qq已失效的檔案能不能恢復
- 360粉碎檔案可以恢復嗎，如何恢復360強力刪除的檔案
- 使用NineData構建任意時間點（PITR）資料恢復能力資料恢復
- win10恢復到上一個時間節點的方法Win10
- 磁碟顯示沒有初始化恢復檔案方法
- mongodb異機做時間點恢復(基於時間範圍查詢匯出oplog)MongoDB
- Git恢復刪除的檔案Git
- mysql 透過idb 恢復檔案MySql