Oracle資料庫遷移之二：Duplicate

資料庫的遷移方式千變萬化，各有千秋。今天為大家呈現的是第二種方法，利用RMAN中的Duplicate來完成資料庫的遷移工作。

1.建立目标資料庫的輔助執行個體

為了使用遷移後的資料庫，我們首先建立一個輔助執行個體。

1）建立參數檔案

在本文中，我們将原資料庫的參數檔案和密碼檔案拷貝過來。

[[email protected] ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[[email protected] dbs]$ ls

hc_ENMOEDU.dat initENMOEDU.ora init.ora lkENMOEDU orapwENMOED spfileENMOEDU.ora

[[email protected] dbs]$ scp orapwENMOEDU spfileENMOEDU.ora [email protected]:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

The authenticity of host '192.168.80.27 (192.168.80.27)' can't be established.

RSA key fingerprint is db:39:d8:4c:81:41:aa:a3:e5:be:bc:60:f5:b6:43:56.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.80.27' (RSA) to the list of known hosts.

[email protected]'s password:

orapwENMOEDU 100% 1536 1.5KB/s 00:00

spfileENMOEDU.ora 100% 2560 2.5KB/s 00:00

在備庫中查詢傳輸過來的檔案。

[[email protected] dbs]$ ls

hc_FRANK.dat init.ora lkFRANK orapwENMOEDU orapwFRANK spfileENMOEDU.ora spfileFRANK.or

至此，密碼檔案和參數檔案傳輸成功。

2）修改參數檔案

[[email protected] dbs]$ mv orapwENMOEDU orapwDBFRANK

[[email protected] dbs]$ mv spfileENMOEDU.ora spfileDBFRANK.ora

[[email protected] dbs]$ ls

hc_FRANK.dat init.ora lkFRANK orapwDBFRANK orapwFRANK spfileDBFRANK.ora spfileFRANK.ora

建立pfile

[[email protected] dbs]$ export ORACLE_SID=DBFRANK

[[email protected] dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:31:40 2014

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

[[email protected] dbs]$ vi initDBFRANK.ora

将pfile中的ENMOEDU全部替換為DBFRANK，然後重新建立spfile.

[[email protected] dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:39:45 2014

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

3）建立所需要的目錄

[[email protected] dbs]$ cd /u01/app/oracle/admin/

[[email protected] admin]$ mkdir -p DBFRANK/adump

[[email protected] DBFRANK]$ cd /u01/app/oracle/fast_recovery_area/

[[email protected] fast_recovery_area]$ mkdir DBFRANK

[[email protected] fast_recovery_area]$ cd /u01/app/oracle/oradata/

[[email protected] oradata]$ mkdir DBFRANK

4)啟動輔助執行個體

[[email protected] oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:46:21 2014

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 335546524 bytes

Database Buffers 79691776 bytes

Redo Buffers 6086656 bytes

輔助執行個體在目标資料庫上啟動成功。

5）在原資料庫上連接配接目标資料庫的輔助執行個體

首先要配置目标資料庫上的tnsname.ora檔案

[[email protected] dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[[email protected] admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ENMOEDU =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.10)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ENMOEDU)

)

DBFRANK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.27)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = DBFRANK)

)

然後嘗試連接配接：

[[email protected] admin]$ sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:53:37 2014

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

為了解決這個錯誤，我們需要配置一下目标資料庫上的靜态監聽。

[[email protected] oradata]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/

[[email protected] admin]$ vi listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = FRANK)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=DBFRANK)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=DBFRANK))

)

ADR_BASE_LISTENER = /u01/app/oracle

重新開機監聽：

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 08-APR-2014 01:01:10

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=FRANK)(PORT=1521)))

The command completed successfully

再次在原資料庫上連接配接目标資料庫：

[[email protected] admin]$ sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 01:04:37 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[email protected] >

連接配接成功，至此，目标資料庫的輔助執行個體配置完成。

2.原資料庫向備庫傳輸備份檔案

[[email protected] fast_recovery_area]$ scp -r ENMOEDU/ [email protected]:/u01/app/oracle/fast_recovery_area/

[email protected]'s password:

o1_mf_1_96_9n5q8wb2_.arc 100% 1024 1.0KB/s 00:00

o1_mf_1_94_9n5q8qy2_.arc 100% 1024 1.0KB/s 00:00

o1_mf_1_93_9n5q8p23_.arc 100% 303KB 302.5KB/s 00:00

o1_mf_1_98_9n5q9hwr_.arc 100% 6144 6.0KB/s 00:00

o1_mf_1_97_9n5q8x7d_.arc 100% 1024 1.0KB/s 00:00

o1_mf_1_99_9n5qb2sr_.arc 100% 1536 1.5KB/s 00:00

o1_mf_1_95_9n5q8tsf_.arc 100% 2048 2.0KB/s 00:00

o1_mf_annnn_TAG20140408T011528_9n5q9jfl_.bkp 100% 313KB 312.5KB/s 00:00

o1_mf_nnndf_TAG20140408T011529_9n5q9koc_.bkp 100% 1279MB 51.2MB/s 00:25

o1_mf_annnn_TAG20140408T011546_9n5qb2yf_.bkp 100% 3072 3.0KB/s 00:00

o1_mf_ncnnf_TAG20140408T011037_9n5q0gn0_.bkp 100% 9568KB 9.3MB/s 00:01

o1_mf_ncnnf_TAG20140408T011529_9n5qb1rs_.bkp 100% 9568KB 9.3MB/s 00:00

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

control02.ctl 100% 9520KB 9.3MB/s 00:00

3.建立備庫

1）用在主庫上用rman連接配接備庫

[[email protected] fast_recovery_area]$ rman target / auxiliary sys/[email protected]

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 8 01:22:43 2014

connected to target database: ENMOEDU (DBID=87396644)

connected to auxiliary database: DBFRANK (not mounted)

連接配接成功。

2)使用duplicate指令來建立備庫

RMAN> duplicate target database to DBFRANK;

Starting Duplicate Db at 08-APR-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=134 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 04/08/2014 01:25:35

RMAN-05501: aborting duplication of target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/test02.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/test01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/example01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/users01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf conflicts with a file used by the target database

RMAN-05001: auxiliary file name /u01/app/oracle/oradata/ENMOEDU/system01.dbf conflicts with a file used by the target database

為了解決上述錯誤，我們修改備庫的參數檔案上兩個參數即可。

[[email protected] admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 01:31:12 2014

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 335546524 bytes

Database Buffers 79691776 bytes

Redo Buffers 6086656 bytes

SQL> show parameter convert;

NAME TYPE VALUE

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

db_file_name_convert string

log_file_name_convert string

SQL> alter system set db_file_name_convert='ENMOEDU','DBFRANK' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='ENMOEDU','DBFRANK' scope=spfile;

System altered.

SQL> startup force nomount

ORACLE instance started.

Total System Global Area 422670336 bytes

Fixed Size 1345380 bytes

Variable Size 335546524 bytes

Database Buffers 79691776 bytes

Redo Buffers 6086656 bytes

主庫重新連接配接備庫：

[[email protected] fast_recovery_area]$ rman target / auxiliary sys/[email protected]

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 8 01:37:47 2014

connected to target database: ENMOEDU (DBID=87396644)

connected to auxiliary database: DBFRANK (not mounted)

重複上次的duplicate指令：

RMAN> duplicate target database to DBFRANK;

Starting Duplicate Db at 08-APR-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 08-APR-14

至此，建立備庫的工作完成。

Frank

2014.04.07

--To be continued--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29436907/viewspace-1137531/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/29436907/viewspace-1137531/

Oracle資料庫遷移之二：Duplicate

繼續閱讀

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

ACS基本配置-權限等級管理

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

nginx 安裝錯誤資訊解決

【MySQL資料庫】資料庫索引事務1.索引2.事務

傳說FreeBSD等比Linux更穩定，更“健壯”

無人機--飛控科普

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述