這一篇文章中，將講述透過一個rman的完整全庫備份來作資料庫的遷移。
（使用的備份在上一篇博文中：http://blog.itpub.net/31392094/viewspace-2128466/）

---裝置：

主機：oracle (HOST = 192.168.2.4)作為A庫：target database: ORA11GR2 (DBID=238796283)
主機：enmo (HOST = 192.168.2.6)作為B庫：target database: ORA11GR2 (DBID=238796283)

---在B庫建立對應的目錄存放備份檔案並複製A庫所有的備份檔案：

[oracle@enmo app]$ cd backup

[oracle@enmo backup]$ pwd

/u01/app/backup

[oracle@enmo backup]$ scp 192.168.2.4:/u01/app/backup/db_0ari1305_1_1.rmn .

oracle@192.168.2.4's password:

db_0ari1305_1_1.rmn 100% 1225MB 7.4MB/s 02:46

[oracle@enmo backup]$ scp 192.168.2.4:/u01/app/backup/db_0bri135o_1_1.rmn .

oracle@192.168.2.4's password:

db_0bri135o_1_1.rmn 100% 9568KB 9.3MB/s 00:00

[oracle@enmo backup]$ ls

db_0ari1305_1_1.rmn db_0bri135o_1_1.rmn db_2grhjt7j_1_1.rmn system01.dbf

[oracle@enmo ~]$

[oracle@enmo ~]$ mkdir -p /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/

[oracle@enmo ~]$ cd /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/

[oracle@enmo 2016_10_10]$ ls

[oracle@enmo 2016_10_10]$ scp 192.168.2.4:/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924880064_czphl11d_.bkp .

oracle@192.168.2.4's password:

o1_mf_s_924880064_czphl11d_.bkp 100% 9600KB 9.4MB/s 00:01

[oracle@enmo 2016_10_10]$ ls

o1_mf_s_924880064_czphl11d_.bkp

---設定B庫空庫的例項名：

[oracle@enmo ~]$ echo $ORACLE_SID

PROD

[oracle@enmo ~]$ export ORACLE_SID=OCMU

[oracle@enmo ~]$ echo $ORACLE_SID

OCMU

---使用rman進入一個空例項並制定dbid開啟到nomount狀態：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 10 16:47:16 2016

connected to target database (not started)

RMAN> set dbid 238796283

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/11.2.0/dbhome_1/dbs/initOCMU.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 281019272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5517312 bytes

---從A庫複製遷移過來的引數檔案與控制檔案的自動備份檔案恢復spfile引數檔案：

RMAN> restore spfile to pfile '?/dbs/initORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924880064_czphl11d_.bkp';

Starting restore at 10-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924880064_czphl11d_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 10-OCT-16

--檢視：

[oracle@enmo ~]$ ls $ORACLE_HOME/dbs/initORA11GR2*

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initORA11GR2.ora

---建立審計檔案與資料檔案等所需目錄：

[oracle@enmo ~]$ mkdir -p /u01/app/oracle/admin/OCMU/{a,b,c,u}dump

[oracle@enmo ~]$ ls /u01/app/oracle/admin/OCMU

adump bdump cdump udump

[oracle@enmo ~]$ mkdir -p /u01/app/oracle/oradata/OCMU/

[oracle@enmo ~]$ ls /u01/app/oracle/oradata/

OCMU PROD

[oracle@enmo ~]$ cd $ORACLE_HOME/dbs

[oracle@enmo dbs]$ ls

hc_OCMU.dat hc_PROD.dat init.ora initORA11GR2.ora init.ora.bck initPROD.ora lkPROD orapwPROD spfilePROD.ora

[oracle@enmo dbs]$ mv initORA11GR2.ora initOCMU.ora

[oracle@enmo dbs]$ ls

hc_OCMU.dat hc_PROD.dat initOCMU.ora init.ora init.ora.bck initPROD.ora lkPROD orapwPROD spfilePROD.ora

---透過pfile檔案修改引數檔案：

[oracle@enmo dbs]$ vi initOCMU.ora

ORA11GR2.__db_cache_size=327155712

ORA11GR2.__java_pool_size=4194304

ORA11GR2.__large_pool_size=8388608

ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ORA11GR2.__pga_aggregate_target=289406976

ORA11GR2.__sga_target=545259520

ORA11GR2.__shared_io_pool_size=0

ORA11GR2.__shared_pool_size=197132288

ORA11GR2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/OCMU/adump'

*.audit_trail='DB','EXTENDED'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/OCMU/control01.ctl','/u01/app/oracle/fast_recovery_area/control02.ctl',
'/u01/app/oracle/oradata/OCMU/control03.ctl','/u01/app/FRA/control04.ctl'

......

#以下部分基本不用修改。

---從pfile引數檔案生成spfile引數檔案：

[oracle@enmo dbs]$ export ORACLE_SID=OCMU

[oracle@enmo dbs]$ echo $ORACLE_SID

OCMU

[oracle@enmo dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 10 17:16:02 2016

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

生成spfile：

SQL> create spfile from pfile;

File created.

---再次關閉並開啟到nomount狀態：

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileOCMU.ora

SQL>

---從自動備份檔案中還原控制檔案：

[oracle@enmo ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 10 17:49:51 2016

connected to target database: ORA11GR2 (not mounted)

RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_10/o1_mf_s_924880064_czphl11d_.bkp';

Starting restore at 10-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/oradata/OCMU/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/control02.ctl

output file name=/u01/app/oracle/oradata/OCMU/control03.ctl

output file name=/u01/app/FRA/control04.ctl

Finished restore at 10-OCT-16

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

#控制檔案恢復完成。

---從遷移過來的備份資料檔案中還原資料檔案：

RMAN> run{

2> SET NEWNAME FOR DATAFILE 1 TO '/u01/app/oracle/oradata/OCMU/system01.dbf';

3> SET NEWNAME FOR DATAFILE 2 TO '/u01/app/oracle/oradata/OCMU/undotbs01.dbf';

4> SET NEWNAME FOR DATAFILE 3 TO '/u01/app/oracle/oradata/OCMU/sysaux.dbf';

5> SET NEWNAME FOR DATAFILE 4 TO '/u01/app/oracle/oradata/OCMU/users01.dbf';

6> SET NEWNAME FOR DATAFILE 5 TO '/u01/app/oracle/oradata/OCMU/example01.dbf';

7> SET NEWNAME FOR DATAFILE 6 TO '/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf';

8> SET NEWNAME FOR DATAFILE 7 TO '/u01/app/oracle/oradata/OCMU/test01.dbf';

9> SET NEWNAME FOR DATAFILE 8 TO '/u01/app/oracle/oradata/OCMU/MYTEST02.dbf';

10> SET NEWNAME FOR DATAFILE 9 TO '/u01/app/oracle/oradata/OCMU/ts_audit01.dbf';

11> RESTORE DATABASE;

12> SWITCH DATAFILE ALL;

13> RECOVER DATABASE;

14> }
rman中執行語句塊。

... ...

using channel ORA_DISK_1

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 00001 to /u01/app/oracle/oradata/OCMU/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/OCMU/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/OCMU/sysaux.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/OCMU/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/OCMU/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/OCMU/test01.dbf

channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/OCMU/MYTEST02.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/OCMU/ts_audit01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/backup/db_0ari1305_1_1.rmn

channel ORA_DISK_1: piece handle=/u01/app/backup/db_0ari1305_1_1.rmn tag=TAG20161010T150437

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:39

Finished restore at 10-OCT-16

... ...

datafile 1 switched to datafile copy

input datafile copy RECID=12 STAMP=924901602 file name=/u01/app/oracle/oradata/OCMU/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=13 STAMP=924901602 file name=/u01/app/oracle/oradata/OCMU/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=14 STAMP=924901602 file name=/u01/app/oracle/oradata/OCMU/sysaux.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=15 STAMP=924901602 file name=/u01/app/oracle/oradata/OCMU/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=16 STAMP=924901603 file name=/u01/app/oracle/oradata/OCMU/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=17 STAMP=924901603 file name=/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=18 STAMP=924901603 file name=/u01/app/oracle/oradata/OCMU/test01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=19 STAMP=924901604 file name=/u01/app/oracle/oradata/OCMU/MYTEST02.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=20 STAMP=924901604 file name=/u01/app/oracle/oradata/OCMU/ts_audit01.dbf

Starting recover at 10-OCT-16

using channel ORA_DISK_1

datafile 6 not processed because file is read-only

starting media recovery

---恢復資料檔案後嘗試開啟資料庫：

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00344: unable to re-create online log

'/u01/app/oracle/oradata/ORA11GR2/redo06.log'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

---最後恢復資料檔案6後以resetlogs方式開啟資料庫：

SQL> alter database open resetlogs;

Database altered.

#資料庫已經開啟。

---補充臨時表空間與臨時資料檔案：

SQL> select file_name from dba_temp_files;

select file_name from dba_temp_files

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf'

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL>

SQL> alter database rename file '/u01/app/oracle/oradata/ORA11GR2/temp01.dbf' to '/u01/app/oracle/oradata/OCMU/temp01.dbf';

Database altered.

SQL> select file_name from dba_temp_files;

FILE_NAME

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

/u01/app/oracle/oradata/OCMU/temp01.dbf

/u01/app/oracle/oradata/mytemp.dbf

--整個資料庫遷移大概就是以上過程，可能還存在不足，但相信能提供足夠的的思路了。

透過rman全庫備份遷移資料庫

相關文章