Oracle RMAN 恢複資料庫到不同主機

Oracle RMAN 恢複資料庫到不同主機

一、RMAN 備份的內容

RMAN做資料庫全備時包含了 資料檔案、歸檔日誌、控制檔案和參數檔案和備份日誌，如下：

arch_20160223_08qukp2t_1_1  arch_20160223_0bqukp92_1_1  ctl_c-3234695588-20160223-01  rmanbak-20160223-0852.log  scp_20160223_09qukp2u_1_1  scp_20160223_0aqukp2u_1_1

二、測試環境

OS：CentOS release 6.4 (Final)

Database：Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

原平台與當前平台環境一致，但是oracle資料庫目錄結構不一致。

三、開始恢複

1、  確認原資料庫的DBID（通過RMAN的備份日誌，或者通過RMAN備份的控制檔案名稱來識別），同時確認一下原資料庫的執行個體名；

2、 將RMAN備份的內容拷貝到目標資料庫上；

3、 設定好環境變數：

[oracle@dg1 ~]$ export NLS_DATA_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@dg1 ~]$ export ORACLE_SID=scp
[oracle@dg1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 6 22:18:52 2016

4、裝載資料庫並進行恢複

    A、裝載資料庫

RMAN> set dbid=3234695588;

executing command: SET DBID

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/dbs/initscp.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

B、先恢複spfile檔案（或者是pfile檔案，可以修改各項參數），因為在spfile 檔案中包含了控制檔案的位置

RMAN> restore spfile to pfile '$ORACLE_HOME/dbs/initscp.ora' from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

OR：

RMAN> restore spfile to '$ORACLE_HOME/dbs/spfilescp.ora' from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

C、根據pfile檔案中的相關參數去修改本地系統目錄，或者修改這個pfile檔案來匹配本地系統中的路徑和目錄，我們選擇後者

需要修改：oracle_base、*.audit_file_dest、*.control_files、*.db_recovery_file_dest、*.db_recovery_file_dest_size、*.diagnostic_dest、*.log_archive_dest_1、

memory_target、undo_tablespace等。並在當前系統中建立好相關的目錄和許可權。

[oracle@dg1 dbs]$ vim initscp.ora

scp.__data_transfer_cache_size=0
scp.__db_cache_size=822083584
scp.__java_pool_size=16777216
scp.__large_pool_size=33554432
scp.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
scp.__pga_aggregate_target=654311424
scp.__sga_target=1241513984
scp.__shared_io_pool_size=50331648
scp.__shared_pool_size=301989888
scp.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/scp/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/scp/control01.ctl','/u01/app/oracle/fast_recovery_area/scp/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='scp'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=scpXDB)'
*.log_archive_dest_1='LOCATION=/OracleArch'
*.memory_target=1800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

D、從修改後的pfile檔案啟動資料庫，進行控制檔案的恢複

RMAN> shutdown abort;
RMAN> startup nomount pfile='$ORACLE_HOME/dbs/initscp.ora';
RMAN> restore controlfile from '/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01';

Starting restore at 06-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/OracleData/scp/control01.ctl
output file name=/home/OracleArch/fast_recovery_area/control02.ctl
Finished restore at 06-MAY-16

--------------------------------------推薦閱讀 --------------------------------------

RMAN備份時遭遇ORA-19571 

RMAN 配置歸檔日誌刪除策略

Oracle基礎教程之通過RMAN複製資料庫

RMAN備份策略制定參考內容

RMAN備份學習筆記

OracleDatabase Backup加密 RMAN加密

RMAN備份時遇到ORA-19588 

--------------------------------------分割線 --------------------------------------

E、控制檔案恢複後就可以mount資料庫了

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

F、至此，控制檔案已經恢複，資料庫已經mount，所有的RMAN配置參數均已設定，您應該驗證路徑以確保它們適用於該主機。

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SCP are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/RmanBackupSet/20160223/ctl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0.2/dbs/snapcf_scp.f'; # default

G、為了能讓RMAN找到恢複檔案的位置，我們有兩種途徑可以實現：一是修改RMAN配置以符合當前備份檔案所在位置，其次是將RMAN備份檔案拷貝到設定檔中設定的位置  （需要參考RMAN的備份日誌）。在第一個方法中，為了讓RMAN知道備份檔案位置 /home/OracleBack/rmanbak ，我們使用catalog命令:

RMAN> catalog start with '/home/OracleBack/rmanbak';

Starting implicit crosscheck backup at 06-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 06-MAY-16

Starting implicit crosscheck copy at 06-MAY-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-16

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/OracleBack/rmanbak

List of Files Unknown to the Database
=====================================
File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1
File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1
File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01
File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1
File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1
File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01
File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1

H、對備份組做交叉檢查，否則還原資料庫時可能會報錯

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orabackup/RmanBackupSet/20160223/arch_20160223_08qukp2t_1_1 RECID=8 STAMP=904553565
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1 RECID=16 STAMP=911172456
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orabackup/RmanBackupSet/20160223/scp_20160223_0aqukp2u_1_1 RECID=9 STAMP=904553567
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1 RECID=14 STAMP=911172456
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orabackup/RmanBackupSet/20160223/scp_20160223_09qukp2u_1_1 RECID=10 STAMP=904553567
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1 RECID=12 STAMP=911172456
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/orabackup/RmanBackupSet/20160223/arch_20160223_0bqukp92_1_1 RECID=11 STAMP=904553762
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1 RECID=13 STAMP=911172456
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01 RECID=15 STAMP=911172456
Crosschecked 9 objects

I、通過控制檔案獲得資料表空間及資料檔案列表

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SCP

List of Permanent Datafiles
===========================
File Size(MB) Tablespace          RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM              ***    /u01/app/oracle/oradata/scp/system01.dbf
2    0        ZYTK_AC              ***    /u01/app/oracle/oradata/scp/zytk_ac01.dbf
3    0        SYSAUX              ***    /u01/app/oracle/oradata/scp/sysaux01.dbf
4    0        UNDOTBS1            ***    /u01/app/oracle/oradata/scp/undotbs01.dbf
5    0        EXAMPLE              ***    /u01/app/oracle/oradata/scp/example01.dbf
6    0        USERS                ***    /u01/app/oracle/oradata/scp/users01.dbf
7    0        ZYTK_AC              ***    /u01/app/oracle/oradata/scp/zytk_ac02.dbf
8    0        ZYTK_ID              ***    /u01/app/oracle/oradata/scp/zytk_id01.dbf
9    0        ZYTK_ID              ***    /u01/app/oracle/oradata/scp/zytk_id02.dbf
10  0        ZYTK_OP              ***    /u01/app/oracle/oradata/scp/zytk_op01.dbf
11  0        ZYTK_OP              ***    /u01/app/oracle/oradata/scp/zytk_op02.dbf
12  0        ZYTK_TEST01          ***    /u01/app/oracle/oradata/scp/zytk_test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace          Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60      TEMP                32767      /u01/app/oracle/oradata/scp/temp01.dbf

注意：restore 的時候不會對temp 資料表空間進行restore。所以等restore 之後，我們需要手工建立temp資料表空間。

J、為了將資料檔案恢複到不同於原來的目錄結構中，必須編寫指令碼，重新命名資料檔案的位置，最後進行全庫的恢複。

  （如果資料檔案路徑和原來的相同則直接還原資料庫）

run{
set newname for datafile 1 to '/home/OracleData/scp/system01.dbf';
set newname for datafile 2 to '/home/OracleData/scp/zytk_ac01.dbf';
set newname for datafile 3 to '/home/OracleData/scp/sysaux01.dbf';
set newname for datafile 4 to '/home/OracleData/scp/undotbs01.dbf';
set newname for datafile 5 to '/home/OracleData/scp/example01.dbf';
set newname for datafile 6 to '/home/OracleData/scp/users01.dbf';
set newname for datafile 7 to '/home/OracleData/scp/zytk_ac02.dbf';
set newname for datafile 8 to '/home/OracleData/scp/zytk_id01.dbf';
set newname for datafile 9 to '/home/OracleData/scp/zytk_id02.dbf';
set newname for datafile 10 to '/home/OracleData/scp/zytk_op01.dbf';
set newname for datafile 11 to '/home/OracleData/scp/zytk_op02.dbf';
set newname for datafile 12 to '/home/OracleData/scp/zytk_test01.dbf';
restore database;
switch datafile all; 
}

對switch datafile all命令的說明：

--對於nocatalog 模式下，rman備份的資訊是儲存在控制檔案裡的，包括資料檔案的路徑資訊。 這裡的  switch datafile all  的作用，就是更新控制檔案裡的資訊。如果不更新控制檔案的話，則進行recover操作時還在原來的位置找檔案。

K、還原資料庫以後，進行 recover 操作

RMAN> recover database;

Starting recover at 06-MAY-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=148
channel ORA_DISK_1: reading from backup piece /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
channel ORA_DISK_1: piece handle=/home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1 tag=ZYTK_DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/OracleArch/ArchiveLog/1_148_899483175.dbf thread=1 sequence=148
unable to find archived log
archived log thread=1 sequence=149
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/06/2016 23:53:58
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 149 and starting SCN of 3507749

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁