rman模拟故障恢複實驗

補充1：restore database和recover database的差別 

restore 隻是用備份來還原,recover是用archivelog或者online log 

舉例說明： 

假設我時間點a,做了個備份,時間點b資料庫挂了 

restore database ;// 這個操作利用時間點a做的備份來還原,傳回到時間點a 

recover database ;//這個操作利用archivelog and online log做recover,從時間點a,推進到時間點b 

restore 是轉儲 也是還原被損壞檔案(rman經常用) 

recover 是恢複 通過redo log & archive log恢複 

補充2：rman模拟故障恢複過程（所有spfile、controlfile、datafile均丢失）-前提是資料庫故障前有rman備份 

1）rman> show all; 

configure controlfile autobackup on;   ---控制檔案備份的同時，會自動備份參數檔案 

sql> select dbid from v$database;  --1669126943 

2）先給資料庫做個備份： 

rman> backup database format '/orabak/whole_%d_%u'; 

starting backup at 18-dec-15 

using channel ora_disk_1 

channel ora_disk_1: starting full datafile backup set 

channel ora_disk_1: specifying datafile(s) in backup set 

input datafile file number=00002 name=/u01/oradata/tinadb/sysaux01.dbf 

input datafile file number=00001 name=/u01/oradata/tinadb/system01.dbf 

input datafile file number=00005 name=/u01/oradata/tinadb/ts_tina01.dbf 

input datafile file number=00003 name=/u01/oradata/tinadb/undotbs01.dbf 

input datafile file number=00004 name=/u01/oradata/tinadb/users01.dbf 

channel ora_disk_1: starting piece 1 at 18-dec-15 

channel ora_disk_1: finished piece 1 at 18-dec-15 

piece handle=/orabak/whole_tinadb_0vqp4nrf_1_1 tag=tag20151218t143214 comment=none   ---注意這行，可以看到備份集的具體名稱和tag标簽 

channel ora_disk_1: backup set complete, elapsed time: 00:00:36 

finished backup at 18-dec-15 

starting control file and spfile autobackup at 18-dec-15 

piece handle=/u01/oracle/tinadb/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp comment=none 

--注意這行，我們可以看到控制檔案和spfile都自動備份了，因為我配置了configure controlfile autobackup on; 

finished control file and spfile autobackup at 18-dec-15 

[root@oratest orabak]# cd /orabak 

[root@oratest orabak]# ll 

-rw-r-----. 1 oracle oinstall 1238605824 dec 18 14:32 whole_tinadb_0vqp4nrf_1_1   

[root@oratest orabak]# cd /u01/oracle/tinadb/autobackup/2015_12_18/ 

[root@oratest 2015_12_18]# ll 

-rw-r-----. 1 oracle oinstall 10158080 dec 18 14:32 o1_mf_s_898785171_c77b4mv5_.bkp 

3）模拟spfile，controlfile，datafile全部都丢失 

sql> shutdown immediate; 

删除檔案： 

[oracle@oratest dbs]$ cd /u01/oracle/dbs/ 

[oracle@oratest dbs]$ rm -f pfiletinadb.ora  spfiletinadb.ora   

[oracle@oratest dbs]$ cd /u01/oradata/tinadb/ 

[oracle@oratest dbs]$ rm -f *.dbf  redo*.log  control01.ctl 

[root@oratest test]# cd /u01/fast_recovery_area/tinadb/ 

[root@oratest tinadb]# rm -f control02.ctl    

4)以oracle預設的參數檔案init.ora啟動後，恢複spfile 

rman> startup force nomount; 

startup failed: ora-01078: failure in processing system parameters 

lrm-00109: could not open parameter file '/u01/oracle/dbs/inittinadb.ora' 

starting oracle instance without parameter file for retrieval of spfile   

oracle instance started 

total system global area     158662656 bytes 

fixed size                     2226456 bytes 

variable size                 92276456 bytes 

database buffers              58720256 bytes 

redo buffers                   5439488 bytes   

rman> set dbid=1669126943   --一定要設定dbid才行 

executing command: set dbid 

rman> restore spfile from autobackup; 

starting restore at 18-dec-15 

channel ora_disk_1: looking for autobackup on day: 20151218 

channel ora_disk_1: looking for autobackup on day: 20151217 

channel ora_disk_1: looking for autobackup on day: 20151216 

channel ora_disk_1: looking for autobackup on day: 20151215 

channel ora_disk_1: looking for autobackup on day: 20151214 

channel ora_disk_1: looking for autobackup on day: 20151213 

channel ora_disk_1: looking for autobackup on day: 20151212 

channel ora_disk_1: no autobackup in 7 days found 

rman-00571: =========================================================== 

rman-00569: =============== error message stack follows =============== 

rman-03002: failure of restore command at 12/18/2015 15:20:27 

rman-06172: no autobackup found or specified handle is not a valid copy or piece 

由于使用的預設參數檔案啟動，如果更改過autobackup的位置或格式（allocate或format）， 

恢複時就會找不到路徑，可以從警告日志找到autobackup的位置，用日志号最新一個恢複參數檔案   

rman> restore spfile from '/u01/oracle/tinadb/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp'; 

---剛剛備份時生成的那個controlfile和spfile的備份 

channel ora_disk_1: restoring spfile from autobackup /u01/oracle/tinadb/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp 

channel ora_disk_1: spfile restore from autobackup complete 

finished restore at 18-dec-15 

5）以新生成的spfile啟動庫并恢複控制檔案 

rman> shutdown immediate;         

oracle instance shut down 

rman> set dbid=1669126943 

rman> startup nomount;          

connected to target database (not started) 

total system global area    2087780352 bytes 

fixed size                     2229944 bytes 

variable size                520096072 bytes 

database buffers            1560281088 bytes 

redo buffers                   5173248 bytes 

rman> restore controlfile from autobackup; 

allocated channel: ora_disk_1 

channel ora_disk_1: sid=171 device type=disk 

recovery area destination: /u01/oracle/ 

database name (or database unique name) used for search: tinadb 

channel ora_disk_1: autobackup /u01/oracle/tinadb/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp found in the recovery area 

channel ora_disk_1: restoring control file from autobackup /u01/oracle/tinadb/autobackup/2015_12_18/o1_mf_s_898785171_c77b4mv5_.bkp 

channel ora_disk_1: control file restore from autobackup complete 

output file name=/u01/oradata/tinadb/control01.ctl 

output file name=/u01/fast_recovery_area/tinadb/control02.ctl    --還是原來的那兩個目錄 

6）恢複庫 

rman> alter database mount; 

database mounted 

released channel: ora_disk_1 

rman> restore database;   --利用之前的全備恢複到備份的時刻狀态 

skipping datafile 1; already restored to file /u01/oradata/tinadb/system01.dbf 

skipping datafile 2; already restored to file /u01/oradata/tinadb/sysaux01.dbf 

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 /u01/oradata/tinadb/undotbs01.dbf 

channel ora_disk_1: restoring datafile 00004 to /u01/oradata/tinadb/users01.dbf 

channel ora_disk_1: restoring datafile 00005 to /u01/oradata/tinadb/ts_tina01.dbf 

channel ora_disk_1: reading from backup piece /orabak/whole_tinadb_0vqp4nrf_1_1 

channel ora_disk_1: piece handle=/orabak/whole_tinadb_0vqp4nrf_1_1 tag=tag20151218t143214 

channel ora_disk_1: restored backup piece 1 

channel ora_disk_1: restore complete, elapsed time: 00:00:15 

rman> recover database;  ---利用歸檔和線上日志回複資料庫到最新狀态 

starting recover at 18-dec-15 

starting media recovery 

archived log for thread 1 with sequence 109 is already on disk as file /u01/oracle/tinadb/archivelog/1_109_898687982.dbf 

archived log for thread 1 with sequence 110 is already on disk as file /u01/oracle/tinadb/archivelog/1_110_898687982.dbf 

archived log file name=/u01/oracle/tinadb/archivelog/1_109_898687982.dbf thread=1 sequence=109 

archived log file name=/u01/oracle/tinadb/archivelog/1_110_898687982.dbf thread=1 sequence=110 

unable to find archived log 

archived log thread=1 sequence=111 

rman-03002: failure of recover command at 12/18/2015 15:39:45 

rman-06054: media recovery requesting unknown archived log for thread 1 with sequence 111 and starting scn of 1889531 

可見，出現此錯誤的原因是恢複需要的日志記錄在控制檔案或恢複目錄中找不到。解決方法分兩種情況： 

1.如果相關的日志存在且可用的話，就将此日志記錄添加到控制檔案或恢複目錄中。 

2.如果相關的日志已經被删除了或不可用了，那麼就按照錯誤的提示scn将資料庫恢複到此scn，本案例是2292709。 

也就是說此時資料庫隻能進行不完全恢複了，在打開資料庫時得使用resetlogs打開。 

rman> recover database until scn 1889531; 

media recovery complete, elapsed time: 00:00:00 

finished recover at 18-dec-15 

rman> alter database open resetlogs; 

database opened 

整個過程恢複完成！！！ 

補充3：rman恢複資料庫到某一個指定時刻---基于歸檔日志 

1）目前正常環境 

sql> select * from tina.salgrade; 

     grade losal    hisal 

---------- ---------- ---------- 

1   700     1200 

2 1201     1400 

3 1401     2000 

4 2001     3000 

5 3001     9999 

rman> crosscheck archivelog all; 

validation succeeded for archived log 

archived log file name=/u01/oracle/tinadb/archivelog/1_5_898789368.dbf recid=101 stamp=898791906 

archived log file name=/u01/oracle/tinadb/archivelog/1_6_898789368.dbf recid=102 stamp=898791928 

archived log file name=/u01/oracle/tinadb/archivelog/1_7_898789368.dbf recid=103 stamp=898792550 

crosschecked 3 objects 

sql> select current_timestamp from dual; 

current_timestamp 

-------------------------------------------- 

18-dec-15 16.35.09.058343 pm +08:00 

sql> alter system archive log current; 

system altered. 

2）誤操作 

sql> drop table tina.salgrade;   ---16:35之後進行的操作 

table dropped. 

select * from tina.salgrade                * 

error at line 1: 

ora-00942: table or view does not exist 

3）利用歸檔日志，回退到操作之前 

啟動庫到mount狀态 

database closed. 

database dismounted. 

oracle instance shut down. 

sql> startup mount; 

oracle instance started. 

total system global area 2087780352 bytes 

fixed size     2229944 bytes 

variable size   520096072 bytes 

database buffers 1560281088 bytes 

redo buffers     5173248 bytes 

database mounted. 

回退： 

rman> run{ 

set until time "to_date('2015-12-18 16:35','yyyy-mm-dd hh24:mi')"; 

restore database; 

recover database; 

}2> 3> 4> 5> 

executing command: set until clause 

channel ora_disk_1: sid=10 device type=disk 

channel ora_disk_1: restoring datafile 00002 to /u01/oradata/tinadb/sysaux01.dbf 

channel ora_disk_1: reading from backup piece /orabak/tinadb/db_0_tinadb_13qp4tpm_1_1 

channel ora_disk_1: piece handle=/orabak/tinadb/db_0_tinadb_13qp4tpm_1_1 tag=tag20151218t161342 

channel ora_disk_1: restore complete, elapsed time: 00:00:55 

channel ora_disk_1: restoring datafile 00001 to /u01/oradata/tinadb/system01.dbf 

channel ora_disk_1: reading from backup piece /orabak/tinadb/db_0_tinadb_14qp4tpm_1_1 

channel ora_disk_1: piece handle=/orabak/tinadb/db_0_tinadb_14qp4tpm_1_1 tag=tag20151218t161342  --先去讀取最近一次的全備 

channel ora_disk_1: restore complete, elapsed time: 00:01:05 

archived log for thread 1 with sequence 5 is already on disk as file /u01/oracle/tinadb/archivelog/1_5_898789368.dbf 

archived log for thread 1 with sequence 6 is already on disk as file /u01/oracle/tinadb/archivelog/1_6_898789368.dbf 

archived log for thread 1 with sequence 7 is already on disk as file /u01/oracle/tinadb/archivelog/1_7_898789368.dbf 

channel ora_disk_1: starting archived log restore to default destination  ---開始恢複歸檔日志。 

channel ora_disk_1: restoring archived log 

archived log thread=1 sequence=3 

archived log thread=1 sequence=4 

channel ora_disk_1: reading from backup piece /orabak/arch19qp4ts7_41_1 

channel ora_disk_1: piece handle=/orabak/arch19qp4ts7_41_1 tag=tag20151218t161501 

channel ora_disk_1: restore complete, elapsed time: 00:00:01 

archived log file name=/u01/oracle/tinadb/archivelog/1_3_898789368.dbf thread=1 sequence=3 

archived log file name=/u01/oracle/tinadb/archivelog/1_4_898789368.dbf thread=1 sequence=4 

archived log file name=/u01/oracle/tinadb/archivelog/1_5_898789368.dbf thread=1 sequence=5 

media recovery complete, elapsed time: 00:00:01 

sql> alter database open resetlogs; 

database altered. 

sql> select * from tina.salgrade;   --資料果然回來了。 

sql> alter system switch logfile; 

完成！