RMAN> list backup of spfile;
從一大推list信息找出最近的備份信息
/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01
RMAN> list backup of controlfile;
找出裡面的控制文件
/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01
RMAN> list backup of database;
1.4 查看歸檔日志信息:
RMAN> list backup of archivelog all;
BS Key Type LV Size Device TypeElapsed Time Completion Time
------- ---- -- ---------- ----------------------- ---------------
4110 Full 18.36M DISK 00:00:01 20-AUG-15
BP Key: 4110 Status: AVAILABLE Compressed: NO Tag: TAG20150820T032017
Piece Name:/pddata2/oracle/backup/data/ctl_auto/c-3391761643-20150820-01
Control File Included: Ckp SCN: 11412370967 Ckp time: 20-AUG-15
將參數文件控制文件copy到測試環境到測試環境/data/impdp/
cd /pddata2/oracle/backup/data/ctl_auto/
scp c-3391761643-20150820-01c-3391761643-20150820-01 192.168.180.60:/data/impdp/
2、開始恢復參數文件控制文件:
SQL> select dbid from v$database;
注意:在rman下即使沒有參數文件,默認也會啟動一個DUMMY實例,以便能夠恢復參數文件。
set dbid 3391761643
startup到open狀態,先查看spfile文件位置:
SQL> show parameter spfile;
/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora
shutdown後,再startup 到 nomount狀態
去生產環境查找
RMAN> show all;
......
CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f'; # default
找到之後copy到測試環境:
scp/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_powerdes.f192.168.180.60:/data/impdp/
開始進行恢復
restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from '/data/impdp/snapcf_powerdes.f';
startup nomount再恢復
恢復報錯如下:
RMAN> restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from '/data/impdp/c-3391761643-20150820-01';
RMAN> restore spfile to'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepowerdes.ora' from '/data/impdp/c-3391761643-20150820-01';
Starting restore at 20-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile fromAUTOBACKUP /data/impdp/c-3391761643-20150820-01
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of restore command at08/20/2015 18:25:14
ORA-32011: cannot restore SPFILE tolocation already being used by the instance
RMAN>
所以從pd線上重新拉一個參數文件出來,copy到測試環境
SQL> create pfile='/oracle/pfile01.ora'from spfile;
File created.
SQL>
然後根據復制來的參數文件再在測試庫上建立spfile
create spfile frompfile='/data/pfile01.ora';
SQL> create spfile frompfile='/data/impdp/pfile01.ora';
File created.
SQL>
然後將測試庫啟動到nomount
SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported onthis system
SQL>
看到報內存錯誤了,然後修改/etc/fstab文件設置好內存標示配置
vi/etc/fstab
tmpfs /dev/shm tmpfs defaults,size=11G 0 0
執行生效
mount -t tmpfs shmfs -o size=11g /dev/shm
SQL> startup mount;
ORA-01078: failure in processing systemparameters
LRM-00109: could not open parameter file'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpowerdes.ora'
SQL>
restore
restore controlfile to'/data/oracle/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';
RMAN> restore controlfile to'/data/oracle/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01'
restore controlfile to'/home/oradata/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';
2> ;
之後需要啟動到mount才能進行restore和recover操作,但是啟動失敗
RMAN> alter database mount
2> ;
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of alter db command at08/21/2015 11:12:13
ORA-00205: error in identifying controlfile, check alert log for more info
RMAN>
看到是因為控制文件不識別,去看下alert日志信息,
[root@testoracle1 /]# tail -f/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/alert_powerdes.log
Checker run found 1 new persistent datafailures
Fri Aug 21 11:13:51 2015
alter database mount
Fri Aug 21 11:13:51 2015
ORA-00210: cannot open the specifiedcontrol file
ORA-00202: control file:'/home/oradata/powerdes/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file ordirectory
Additional information: 3
ORA-205 signalled during: alter databasemount...
從alert日志可以看出控制文件已經變成了生產環境的路徑了,原因是spfile是從生產環境copy出來的,生產環境的spfile裡面記錄的控制文件路徑和測試環境不同,所以這裡為了快速恢復,要重新指定和生產環境一樣路徑的,重新生成新路徑的控制文件。
控制文件
RMAN> restore controlfile to'/home/oradata/powerdes/control01.ctl' from'/data/impdp/c-3391761643-20150820-01';
Starting restore at 21-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
Finished restore at 21-AUG-15
OK,看到控制文件restore成功了,接下來直接將數據庫狀態改成mount
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN>
然後就可以開始restore整個庫
要將db狀態改成mount才行
catalog start with'/data/impdp/2015-08-20/';
RMAN> alter database mount
2> ;
using target database control file insteadof recovery catalog
database mounted
RMAN> catalog start with'/data/impdp/2015-08-20/';
Starting implicit crosscheck backup at20-AUG-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 devicetype=DISK
Crosschecked 98 objects
Finished implicit crosscheck backup at20-AUG-15
Starting implicit crosscheck copy at20-AUG-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at20-AUG-15
searching for all files in the recoveryarea
cataloging files...
no files cataloged
searching for all files that match thepattern /data/impdp/2015-08-20/
List of Files Unknown to the Database
=====================================
File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak
File Name:/data/impdp/2015-08-20/rman_backup.log
File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4418.bak
File Name:/data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak
Do you really want to catalog the abovefiles (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak
File Name:/data/impdp/2015-08-20/arch_POWERDES_20150820_4418.bak
File Name:/data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak
List of Files Which Where Not Cataloged
=======================================
File Name:/data/impdp/2015-08-20/rman_backup.log
RMAN-07517: Reason: The file header is corrupted
RMAN>
因為前面恢復了整個spfile已經controlfile,所以接下來恢復所有庫的話,就不用帶參數,直接恢復restore database就可以 ;
RMAN> restore database;
RMAN> restore database;
Starting restore at 20-AUG-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile00001 to /home/oradata/powerdes/system01.dbf
channel ORA_DISK_1: restoring datafile00002 to /home/oradata/powerdes/sysaux01.dbf
channel ORA_DISK_1: restoring datafile00003 to /home/oradata/powerdes/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00004 to /home/oradata/powerdes/users01.dbf
channel ORA_DISK_1: restoring datafile00005 to /home/oradata/powerdes/powerdesk01.dbf
channel ORA_DISK_1: restoring datafile00006 to /home/oradata/powerdes/plas01.dbf
channel ORA_DISK_1: restoring datafile00007 to /home/oradata/powerdes/pl01.dbf
channel ORA_DISK_1: restoring datafile00008 to /home/oradata/powerdes/help01.dbf
channel ORA_DISK_1: restoring datafile00009 to /home/oradata/powerdes/adobelc01.dbf
channel ORA_DISK_1: restoring datafile00010 to /home/oradata/powerdes/sms01.dbf
channel ORA_DISK_1: restoring datafile00011 to /home/oradata/powerdes/plcrm01.dbf
channel ORA_DISK_1: restoring datafile00012 to /home/oradata/powerdes/powerdesk02.dbf
channel ORA_DISK_1: reading from backuppiece /data/impdp/2015-08-20/full_POWERDES_20150820_4419.bak
channel ORA_DISK_1: piecehandle=/data/impdp/2015-08-20/full_POWERDES_20150820_4419.baktag=TAG20150820T030008
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:17:35
Finished restore at 20-AUG-15
RMAN>
RMAN> recover database;
Starting recover at 20-AUG-15
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived logrestore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=36277
channel ORA_DISK_1: reading from backuppiece /data/impdp/2015-08-20/arch_POWERDES_20150820_4420.bak
channel ORA_DISK_1: piecehandle=/data/impdp/2015-08-20/arch_POWERDES_20150820_4420.baktag=TAG20150820T032015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
archived log filename=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2015_08_20/o1_mf_1_36277_bxcjyzbg_.arcthread=1 sequence=36277
channel default: deleting archivedlog(s)
archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2015_08_20/o1_mf_1_36277_bxcjyzbg_.arcRECID=71395 STAMP=888264671
unable to find archived log
archived log thread=1 sequence=36278
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at08/20/2015 20:11:13
RMAN-06054: media recovery requestingunknown archived log for thread 1 with sequence 36278 and starting SCN of11412370952
RMAN>
後台alet日志報錯信息為:
Fri Aug 21 11:47:07 2015
alter database recover datafile list clear
Completed: alter database recover datafilelist clear
alter database recover datafile list
1 ,2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12
Completed: alter database recover datafilelist
1 ,2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 4slaves
ORA-279 signalled during: alter databaserecover if needed
start until cancel using backup controlfile
...
alter database recover logfile'/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf'
Media Recovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf
ORA-279 signalled during: alter databaserecover logfile'/oracle/app/oracle/flash_recovery_area/archivelog1_36277_821708334.dbf'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
Fri Aug 21 11:47:09 2015
Checker run found 1 new persistent datafailures
可見,出先此錯誤的原因是恢復需要的歸檔日志記錄在控制文件或恢復目錄中找不到。解決方法分兩種情況:
1.如果相關的日志存在且可用的話,就將此日志記錄添加到控制文件或恢復目錄中。
2.如果相關的日志已經被刪除了或不可用了,那麼就按照錯誤的提示scn將數據庫恢復到此scn,這裡是11412370952。也就是說此時數據庫只能進行不完全恢復了,在打開數據庫時得使用resetlogs打開。
recover database until scn 11412370952;
RMAN> recover database until scn11412370952;
Starting recover at 20-AUG-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time:00:00:00
Finished recover at 20-AUG-15
RMAN>
然後打開數據庫
RMAN> alter database openresetlogs;
database opened
RMAN>
然後打開報錯
RMAN> alter database open resetlogs;
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of alter db command at08/20/2015 20:31:07;
ORA-03113: end-of-file on communicationchannel
Process ID: 30584
Session ID: 192 Serial number: 19
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of alter db command at08/20/2015 20:31:07
ORA-03113: end-of-file on communicationchannel
Process ID: 30584
Session ID: 192 Serial number: 19
[oracle@testoracle1 dbs]$
重新進去打開
RMAN> alter database open resetlogs;
using target database control file insteadof recovery catalog
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of alter db command at08/20/2015 20:35:44
RMAN-06403: could not obtain a fullyauthorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does notexist
Linux-x86_64 Error: 2: No such file ordirectory
RMAN>
嘗試去sqlplus模式下啟動
SQL> startup
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size 2212936 bytes
Variable Size 2751466424 bytes
Database Buffers 2214592512 bytes
Redo Buffers 42414080 bytes
Database mounted.
ORA-03113: end-of-file on communicationchannel
Process ID: 10504
Session ID: 191 Serial number: 3
SQL>
這裡打不開的原因是,參數文件恢復失效後,跳過參數文件恢復這一步驟,然後進行控制文件恢復後restore庫recover庫引發的問題。解決方案:就是重新恢復參數文件後再進行下述步驟就完全OK。