linux下僅僅有rman備份組的異機不同檔案夾恢複

標籤：

昨天在客戶那裡做了一次rman異機的恢複，把生產庫弄一份給測試庫用，總庫大概80G，總共花費了2個小時，當時客戶的環境是windows 11.2.0.3，今天早晨在linux下又一次測試了一下，記錄下來供大家參考

環境:

全部操作都是按主機名稱區分

源庫:

主機名稱  bre1  執行個體名為bre1  資料檔案檔案夾在/u01/app/oradata下

目標庫:

主機名稱 bre2   資料檔案準備放在/bre1/oradata下

廢話不多說，以下是實際操作步驟

1.在源庫建立備份組，而且複製到目標庫

拷貝的檔案夾可用和原來的備份的時候的檔案夾一致，也能夠不一致

備份system資料表空間rman會自己主動備份參數檔案和控制檔案

RMAN> backup database format '/home/oracle/%U.bak';Starting backup at 21-AUG-2014 10:03:35using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/bre1/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/bre1/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/bre1/undotbs01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/bre1/test.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/bre1/users01.dbfchannel ORA_DISK_1: starting piece 1 at 21-AUG-2014 10:03:36channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:51piece handle=/home/oracle/03pggb7o_1_1.bak tag=TAG20140821T100336 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 21-AUG-2014 10:03:52channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:53piece handle=/home/oracle/04pggb87_1_1.bak tag=TAG20140821T100336 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 21-AUG-2014 10:03:53[[email protected] ~]$ scp bre1:/home/oracle/03pggb7o_1_1.bak .The authenticity of host 'bre1 (192.168.56.45)' can't be established.RSA key fingerprint is 73:56:4c:3a:01:3f:50:c8:d8:3a:5d:d5:21:00:6a:fe.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added 'bre1,192.168.56.45' (RSA) to the list of known hosts.[email protected]'s password: 03pggb7o_1_1.bak                                                                                     100%  336MB  37.3MB/s   00:09    [[email protected] ~]$ scp bre1:/home/oracle/04pggb87_1_1.bak .[email protected]'s password: 04pggb87_1_1.bak                                                                                     100% 9600KB   9.4MB/s   00:00  

2.還原spfile，使用nomount開啟資料庫

編輯一個最簡單的pfile，僅僅有db_name就可以：

[[email protected] dbs]$ vi initbre1.ora[[email protected] dbs]$ cat initbre1.ora db_name=bre1

然後就能夠nomount開啟資料庫了：

SQL> startup nomount;ORACLE instance started.Total System Global Area  229683200 bytesFixed Size    2251936 bytesVariable Size  171967328 bytesDatabase Buffers   50331648 bytesRedo Buffers    5132288 bytesSQL> 

再使用rman來恢複spfile：

RMAN> restore spfile from '/home/oracle/backup/04pggb87_1_1.bak';Starting restore at 21-AUG-2014 9:39:31using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=171 device type=DISKchannel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/04pggb87_1_1.bakchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP completeFinished restore at 21-AUG-2014 9:39:32RMAN> 

使用spfile來打理資料庫試試

[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 10:08:01 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomount;ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora'SQL> startup nomount;ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora'SQL> startup nomount;ORA-09925: Unable to create audit trail fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 9925

有非常多相關檔案夾沒有建立，能夠使用strings spfilebre1.ora來查看一下spfile裡面的內容，建好相關的檔案夾，而且賦予許可權。。這些都搞好後nomount開啟資料庫不再報錯。

3.恢複控制檔案

登陸到rman，控制檔案還原的時候將還原到參數檔案指定的位置，所以參數檔案指定的控制檔案檔案夾必須存在，否則報錯，比如以下一開始我沒有建立就報錯，後來建立完畢就不報錯了，假設你想還原到其它地方，能夠改動參數檔案指定到對應地址。

RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';Starting restore at 21-AUG-2014 10:26:14using channel ORA_DISK_1channel ORA_DISK_1: restoring control fileRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 08/21/2014 10:26:15ORA-19870: error while restoring backup piece /home/oracle/04pggb87_1_1.bakORA-19504: failed to create file "/u01/app/oracle/oradata/bre1/control01.ctl"ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 1RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';Starting restore at 21-AUG-2014 10:26:50using channel ORA_DISK_1channel ORA_DISK_1: restoring control fileRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 08/21/2014 10:26:51ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/bre1/control02.ctl"ORA-27040: file create error, unable to create fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 1ORA-19600: input file is control file  (/u01/app/oracle/oradata/bre1/control01.ctl)ORA-19601: output file is control file  (/u01/app/oracle/fast_recovery_area/bre1/control02.ctl)RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';Starting restore at 21-AUG-2014 10:27:24using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u01/app/oracle/oradata/bre1/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/bre1/control02.ctlFinished restore at 21-AUG-2014 10:27:25

4.mount資料庫,注冊備份組

重新啟動資料庫，直接就能夠啟動到mount

SQL> startup mount;ORACLE instance started.Total System Global Area  835104768 bytesFixed Size    2257840 bytesVariable Size  503319632 bytesDatabase Buffers  322961408 bytesRedo Buffers    6565888 bytesDatabase mounted.SQL> 

假設此時備份組所在檔案檔案夾改變，須要手工catalog注冊備份組，這裡我們將拷過來的備份檔案拷貝到/home/oracle/backup下，示範一下手工catalog備份組

[[email protected] ~]$ ls03pggb7o_1_1.bak  04pggb87_1_1.bak  pfile.ora  test[[email protected] ~]$ mkdir backup[[email protected] ~]$ mv *.bak backup[[email protected] ~]$ ls backup03pggb7o_1_1.bak  04pggb87_1_1.bakRMAN> catalog backuppiece '/home/oracle/backup/03pggb7o_1_1.bak';Starting implicit crosscheck backup at 21-AUG-2014 10:32:10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKCrosschecked 1 objectsFinished implicit crosscheck backup at 21-AUG-2014 10:32:10Starting implicit crosscheck copy at 21-AUG-2014 10:32:10using channel ORA_DISK_1Finished implicit crosscheck copy at 21-AUG-2014 10:32:11searching for all files in the recovery areacataloging files...no files catalogedcataloged backup piecebackup piece handle=/home/oracle/backup/03pggb7o_1_1.bak RECID=2 STAMP=856175531RMAN>  catalog backuppiece '/home/oracle/backup/04pggb87_1_1.bak';cataloged backup piecebackup piece handle=/home/oracle/backup/04pggb87_1_1.bak RECID=3 STAMP=856175550

5.使用set newname將資料檔案還原到不同檔案夾

原來的資料檔案檔案夾在/u01/admin/oradata以下,我們將資料檔案恢複到/bre1/oradata以下，%b的意思是僅僅擷取檔案名稱，沒有檔案夾資訊

[[email protected] ~]# mkdir -p /bre1/oradata[[email protected] ~]# chown -R oracle:oinstall /bre1[[email protected] ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 21 10:36:10 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: BRE1 (DBID=2522432392, not open)run{allocate channel ch1 device type disk;set newname for database to '/bre1/oradata/%b';restore database;release channel ch1;6> }using target database control file instead of recovery catalogallocated channel: ch1channel ch1: SID=396 device type=DISKexecuting command: SET NEWNAMEStarting restore at 21-AUG-2014 10:38:03channel ch1: starting datafile backup set restorechannel ch1: specifying datafile(s) to restore from backup setchannel ch1: restoring datafile 00001 to /bre1/oradata/system01.dbfchannel ch1: restoring datafile 00002 to /bre1/oradata/sysaux01.dbfchannel ch1: restoring datafile 00003 to /bre1/oradata/undotbs01.dbfchannel ch1: restoring datafile 00004 to /bre1/oradata/users01.dbfchannel ch1: restoring datafile 00005 to /bre1/oradata/test.dbfchannel ch1: reading from backup piece /home/oracle/backup/03pggb7o_1_1.bakchannel ch1: piece handle=/home/oracle/backup/03pggb7o_1_1.bak tag=TAG20140821T100336channel ch1: restored backup piece 1channel ch1: restore complete, elapsed time: 00:00:45Finished restore at 21-AUG-2014 10:38:48released channel: ch1資料檔案都在了：[[email protected] ~]# ls -l /bre1/oradatatotal 1564468-rw-r----- 1 oracle oinstall 629153792 Aug 21 10:38 sysaux01.dbf-rw-r----- 1 oracle oinstall 734011392 Aug 21 10:38 system01.dbf-rw-r----- 1 oracle oinstall  20979712 Aug 21 10:38 test.dbf-rw-r----- 1 oracle oinstall 209723392 Aug 21 10:38 undotbs01.dbf-rw-r----- 1 oracle oinstall   6561792 Aug 21 10:38 users01.dbf

資料上說能夠使用switch datafile all來直接改動控制檔案裡的檔案檔案夾，可是我試了一下貌似不行，還是須要在手工rename，以下是我手工改動控制檔案裡的資料檔案檔案夾，先看一下當前的資料檔案檔案夾

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/bre1/system01.dbf/u01/app/oracle/oradata/bre1/sysaux01.dbf/u01/app/oracle/oradata/bre1/undotbs01.dbf/u01/app/oracle/oradata/bre1/users01.dbf/u01/app/oracle/oradata/bre1/test.dbfSQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/bre1/temp01.dbfSQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/bre1/redo01.log/u01/app/oracle/oradata/bre1/redo02.log/u01/app/oracle/oradata/bre1/redo03.log

我自己寫了一個拼接sql來將資料檔案檔案夾做轉換：

select ‘alter database rename file ‘‘‘||name||‘‘‘ to ‘‘/bre1/oradata‘||substr(name,length(name)-INSTR(reverse(name),‘/‘)+1,INSTR(reverse(name),‘/‘))||‘‘‘;‘ from v$datafile
union all
select ‘alter database rename file ‘‘‘||name||‘‘‘ to ‘‘/bre1/oradata‘||substr(name,length(name)-INSTR(reverse(name),‘/‘)+1,INSTR(reverse(name),‘/‘))||‘‘‘;‘ from v$tempfile
union all
select ‘alter database rename file ‘‘‘||member||‘‘‘ to ‘‘/bre1/oradata‘||substr(member,length(member)-INSTR(reverse(member),‘/‘)+1,INSTR(reverse(member),‘/‘))||‘‘‘;‘ from v$logfile
;

select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafileunion allselect 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfileunion allselect 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile  6  ;'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/BRE1/ORADATA'||SUBSTR(NAME,LENGTH(NAME)-INSTR(REVERSE(NAME),'/')+1,INSTR(REVERSE(NAME),'/'))||''';'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------alter database rename file '/bre1/oradata/system01.dbf' to '/bre1/oradata/system01.dbf';alter database rename file '/bre1/oradata/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf';alter database rename file '/bre1/oradata/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf';alter database rename file '/bre1/oradata/users01.dbf' to '/bre1/oradata/users01.dbf';alter database rename file '/bre1/oradata/test.dbf' to '/bre1/oradata/test.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/temp01.dbf' to '/bre1/oradata/temp01.dbf';alter database rename file '/bre1/oradata/redo01.log' to '/bre1/oradata/redo01.log';alter database rename file '/bre1/oradata/redo02.log' to '/bre1/oradata/redo02.log';alter database rename file '/bre1/oradata/redo03.log' to '/bre1/oradata/redo03.log';alter database rename file '/u01/app/oracle/oradata/bre1/system01.dbf' to '/bre1/oradata/system01.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/users01.dbf' to '/bre1/oradata/users01.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/test.dbf' to '/bre1/oradata/test.dbf';alter database rename file '/u01/app/oracle/oradata/bre1/redo01.log' to '/bre1/oradata/redo01.log';alter database rename file '/u01/app/oracle/oradata/bre1/redo02.log' to '/bre1/oradata/redo02.log';Database altered.SQL> Database altered.SQL> Database altered.SQL> Database altered.SQL> Database altered.SQL> Database altered.SQL> Database altered.SQL> alter database rename file '/u01/app/oracle/oradata/bre1/redo03.log' to '/bre1/oradata/redo03.log';Database altered.

rename之後控制檔案裡的資料檔案檔案夾都已經改到了/bre1/oradata下，那麼就能夠進行恢複了。
6.恢複資料庫

我這裡是測試就沒有恢複什麼歸檔日誌，假設是正式生產庫，還須要恢複歸檔日誌，可能還須要使用到CATALOG ARCHIVELOG 來注冊歸檔的備份組和在RMAN中使用
RUN
{ 
  SET ARCHIVELOG DESTINATION TO ‘/home/oracle‘;
  RESTORE ARCHIVELOG all;       
}    
來還原出歸檔日誌。
或者還能夠在controlfile中改動歸檔檔案夾來恢複。恢複出歸檔日誌之後，就能夠進行資料庫恢複。

[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 11:16:06 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> recover database using backup controlfile until cancel;ORA-00279: change 830841 generated at 08/21/2014 10:03:36 needed for thread 1ORA-00289: suggestion : /arch1_42_851018056.dbfORA-00280: change 830841 for thread 1 is in sequence #42Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.SQL> alter database open resetlogs;Database altered.

open 過程中可能會遇到ora-00392錯誤，
解決的方法：
使用SQL> alter database clear logfile group 2;
到此恢複成功！

總結：
基本的痛點是須要對rman比較熟悉，須要使用語句改動控制檔案裡的檔案位置，否則會報錯找不到相關檔案。
假裝置份集的檔案夾改變了，須要手工catalog注冊備份組到控制檔案，而且rename file。

linux下僅僅有rman備份組的異機不同檔案夾恢複