Oracle数据库迁移之二：Duplicate

数据库的迁移方式千变万化，各有千秋。今天为大家呈现的是第二种方法，利用RMAN中的Duplicate来完成数据库的迁移工作。

1.创建目标数据库的辅助实例
为了使用迁移后的数据库，我们首先创建一个辅助实例。

1）创建参数文件
在本文中，我们将原数据库的参数文件和口令文件拷贝过来。
[oracle@ENMOEDU ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@ENMOEDU dbs]$ ls
hc_ENMOEDU.dat  initENMOEDU.ora  init.ora  lkENMOEDU  orapwENMOED spfileENMOEDU.ora
[oracle@ENMOEDU dbs]$ scp orapwENMOEDU spfileENMOEDU.ora oracle@192.168.80.27:/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.
oracle@192.168.80.27's password: 
orapwENMOEDU                                                              100% 1536     1.5KB/s   00:00    
spfileENMOEDU.ora                                                         100% 2560     2.5KB/s   00:00    

在备库中查询传输过来的文件。
[oracle@FRANK dbs]$ ls
hc_FRANK.dat  init.ora  lkFRANK  orapwENMOEDU  orapwFRANK  spfileENMOEDU.ora  spfileFRANK.or

至此，口令文件和参数文件传输成功。

2）修改参数文件
[oracle@FRANK dbs]$ mv orapwENMOEDU orapwDBFRANK
[oracle@FRANK dbs]$ mv spfileENMOEDU.ora spfileDBFRANK.ora
[oracle@FRANK dbs]$ ls
hc_FRANK.dat  init.ora  lkFRANK  orapwDBFRANK  orapwFRANK  spfileDBFRANK.ora  spfileFRANK.ora

创建pfile
[oracle@FRANK dbs]$ export ORACLE_SID=DBFRANK
[oracle@FRANK dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:31:40 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create pfile from spfile;
File created.
[oracle@FRANK dbs]$ vi initDBFRANK.ora

将pfile中的ENMOEDU全部替换为DBFRANK，然后重新创建spfile.
[oracle@FRANK dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:39:45 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile; 
File created.

3）创建所需要的目录
[oracle@FRANK dbs]$ cd /u01/app/oracle/admin/
[oracle@FRANK admin]$ mkdir -p DBFRANK/adump
[oracle@FRANK DBFRANK]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@FRANK fast_recovery_area]$ mkdir DBFRANK
[oracle@FRANK fast_recovery_area]$ cd /u01/app/oracle/oradata/
[oracle@FRANK oradata]$ mkdir DBFRANK

4)启动辅助实例
[oracle@FRANK oradata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:46:21 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
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文件
[oracle@ENMOEDU dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@ENMOEDU 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)
    )
  )

然后尝试连接：
[oracle@ENMOEDU admin]$ sqlplus sys/oracle@DBFRANK as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 00:53:37 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

为了解决这个错误，我们需要配置一下目标数据库上的静态监听。
[oracle@FRANK oradata]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@FRANK 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

重启监听：
[oracle@FRANK admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 08-APR-2014 01:01:10
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=FRANK)(PORT=1521)))
The command completed successfully

再次在原数据库上连接目标数据库：
[oracle@ENMOEDU admin]$ sqlplus sys/oracle@DBFRANK as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 01:04:37 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
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

SYS@DBFRANK > 

连接成功，至此，目标数据库的辅助实例配置完成。

2.原数据库向备库传输备份文件
[oracle@ENMOEDU fast_recovery_area]$ scp -r ENMOEDU/ oracle@192.168.80.27:/u01/app/oracle/fast_recovery_area/
oracle@192.168.80.27'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连接备库
[oracle@ENMOEDU fast_recovery_area]$ rman target / auxiliary sys/oracle@DBFRANK

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 8 01:22:43 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
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

为了解决上述错误，我们修改备库的参数文件上两个参数即可。

[oracle@FRANK admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 8 01:31:12 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
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

主库重新连接备库：
[oracle@ENMOEDU fast_recovery_area]$ rman target / auxiliary sys/oracle@DBFRANK

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Apr 8 01:37:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
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/