rman duplicate实现数据库的迁移

目的：迁移单实例数据库到oracle rac asm环境下。即迁移原系统hrdbprim数据库到oracle rac ASM环境下hrdb数据库，实现数据库的迁移，转为后的实例为hrdb3。

具体过程如下

1，配置tnsnames.ora文件，实现两个数据库的互相访问

1，配置tnsnames.ora文件，实现两个数据库的互相访问
vi tnsnames.orahrdb_source =   (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.×.×)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = hrdbprim)    )  )hrdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.×.×)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = hrdb)    )  )  

2，设置备库pfile文件，常见备库的初始化pfile文件，根据参数建立相应的目录。

asm使用asmcmd命令进行创建。

vi /oracle/app/oracle/admin/hrdb/inithrdb.ora*.audit_file_dest='/oracle/app/oracle/admin/hrdbb/adump'*.audit_trail='db'*.cluster_database=false*.compatible='11.2.0.0.0'*.control_files='+DATA/hrdb/controlfile/control01.ctl','+FRA/hrdb/controlfile/control02.ctl'*.db_block_size=8192*.db_create_file_dest='+DATA'*.db_domain=''*.db_name='hrdb'*.db_recovery_file_dest='+FRA'*.db_recovery_file_dest_size=107374182400*.diagnostic_dest='/oracle/app/oracle'hrdbb3.instance_number=3hrdbb2.instance_number=2hrdbb1.instance_number=1*.log_archive_format='%t_%s_%r.dbf'*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=1000*.pga_aggregate_target=1610612736*.processes=500*.remote_listener='dtydb-scan2:1521'*.remote_login_passwordfile='exclusive'*.resource_manager_plan='default_plan'*.result_cache_mode='MANUAL'*.sga_target=4399824896hrdb3.thread=3hrdb2.thread=2hrdb1.thread=1hrdb3.undo_tablespace='UNDOTBS1'hrdb1.undo_tablespace='UNDOTBS3'hrdb2.undo_tablespace='UNDOTBS2'*.standby_file_management='auto'

注意：cluster_database必须是false，undo_tablespace要和生产保持一致

3，启动备库的监听，新建pfile,spfile文件

startup pfile = '/oracle/app/oracle/admin/hrdb/inithrdb.ora' nomount;
create spfile from pfile='/oracle/app/oracle/admin/hrdb/inithrdb.ora';

startup nomount 启动到mount状态

4，登录到备份数据库，开始rman备份

alter system switch log file;多运行几次rman target / run {  allocate channel c1 type disk; allocate channel c2 type disk; backup database format '/data/backup/hrdbprim-%U' ; backup archivelog all delete all input format '/data/backup/ARC_%U'; } backup database format '/data/backup/hrdbprim-%U';backup current controlfile format '/data/backup/%d_control_%s_%c_%p_%T.ctl';backup archivelog all format '/data/backup/%d_arch_%s_%c_%p_%T.bak';  backup current controlfile format '/data/backup/%d_control_%s_%c_%p_%T.ctl'; 

5，传输备份文件到备库 ，注意最好在同一个目录，要不还需要地址转换，包括备份的控制文件，备份的数据文件和密码文件等

6，备份主机运行如下命令，开始备份

rman target sys/oracle@hrdb_source auxiliary sys/oracle RUN{  # The DUPLICATE command uses an automatic sbt channel.  # Because the target datafiles are spread across multiple directories,   # run SET NEWNAME rather than DB_FILE_NAME_CONVERT  SET NEWNAME FOR DATAFILE 1 TO '+DATA/hrdb/datafile/system01.dbf';   SET NEWNAME FOR DATAFILE 2 TO '+DATA/hrdb/datafile/sysaux01.dbf';   SET NEWNAME FOR DATAFILE 3 TO '+DATA/hrdb/datafile/undotbs03.dbf';   SET NEWNAME FOR DATAFILE 4 TO '+DATA/hrdb/datafile/users01.dbf';   # Do not set a newname for datafile 7, because it is in the tools tablespace,  # and you are excluding tools from the duplicate database.  #SET NEWNAME FOR DATAFILE 8 TO '/oradata7/users01.dbf';   DUPLICATE TARGET DATABASE TO hrdb      LOGFILE    GROUP 7 ('+DATA/tyolap/onlinelog/redo31a') SIZE 50m REUSE,     GROUP 8 ('+DATA/tyolap/onlinelog/redo32a') SIZE 50m REUSE;}duplicate target database to hrdb nofilenamecheck;run { allocate channel c1 device type disk; restore controlfile from '/oracle/app/oracle/product/11.2.0/db_1/dbs/c-4034584542-20120802-01'; alter database mount; }

7，运行结果如下，显示rman恢复成功

[oracle@dtydb5 ~]$ rman target sys/oracle@hrdb_source auxiliary sys/oracle恢复管理器: Release 11.2.0.2.0 - Production on 星期五 8月 3 15:02:31 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.已连接到目标数据库: HRDBPRIM (DBID=4034584542)已连接到辅助数据库: HRDB (未装载)RMAN> RUN2> {3>   # The DUPLICATE command uses an automatic sbt channel.4>   # Because the target datafiles are spread across multiple directories, 5>   # run SET NEWNAME rather than DB_FILE_NAME_CONVERT6>   SET NEWNAME FOR DATAFILE 1 TO '+DATA/hrdb/datafile/system01.dbf'; 7>   SET NEWNAME FOR DATAFILE 2 TO '+DATA/hrdb/datafile/sysaux01.dbf'; 8>   SET NEWNAME FOR DATAFILE 3 TO '+DATA/hrdb/datafile/undotbs03.dbf'; 9>   SET NEWNAME FOR DATAFILE 4 TO '+DATA/hrdb/datafile/users01.dbf'; 10>   # Do not set a newname for datafile 7, because it is in the tools tablespace,11>   # and you are excluding tools from the duplicate database.12>   #SET NEWNAME FOR DATAFILE 8 TO '/oradata7/users01.dbf'; 13>   DUPLICATE TARGET DATABASE TO hrdb  14>     LOGFILE15>     GROUP 7 ('+DATA/tyolap/onlinelog/redo31a') SIZE 50m REUSE, 16>     GROUP 8 ('+DATA/tyolap/onlinelog/redo32a') SIZE 50m REUSE;17> }正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME启动 Duplicate Db 于 2012-08-03 15:02:44使用目标数据库控制文件替代恢复目录分配的通道: ORA_AUX_DISK_1通道 ORA_AUX_DISK_1: SID=26 设备类型=DISK内存脚本的内容:{   sql clone "alter system set  db_name =  ''HRDBPRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile";   sql clone "alter system set  db_unique_name =  ''HRDB'' comment= ''Modified by RMAN duplicate'' scope=spfile";   shutdown clone immediate;   startup clone force nomount   restore clone primary controlfile;   alter clone database mount;}正在执行内存脚本sql 语句: alter system set  db_name =  ''HRDBPRIM'' comment= ''Modified by RMAN duplicate'' scope=spfilesql 语句: alter system set  db_unique_name =  ''HRDB'' comment= ''Modified by RMAN duplicate'' scope=spfileOracle 实例已关闭Oracle 实例已启动系统全局区域总计    4392697856 字节Fixed Size                     2233616 字节Variable Size                838863600 字节Database Buffers            3539992576 字节Redo Buffers                  11608064 字节启动 restore 于 2012-08-03 15:03:08分配的通道: ORA_AUX_DISK_1通道 ORA_AUX_DISK_1: SID=1 设备类型=DISK通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集通道 ORA_AUX_DISK_1: 正在还原控制文件通道 ORA_AUX_DISK_1: 正在读取备份片段 /oracle/app/oracle/product/11.2.0/db_1/dbs/c-4034584542-20120803-07通道 ORA_AUX_DISK_1: ORA-19870: 还原备份片段 /oracle/app/oracle/product/11.2.0/db_1/dbs/c-4034584542-20120803-07 时出错ORA-19505: 无法识别文件"/oracle/app/oracle/product/11.2.0/db_1/dbs/c-4034584542-20120803-07"ORA-27037: 无法获得文件状态Linux-x86_64 Error: 2: No such file or directoryAdditional information: 3故障转移到上一个备份通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集通道 ORA_AUX_DISK_1: 正在还原控制文件通道 ORA_AUX_DISK_1: 正在读取备份片段 /data/backup/HRDBPRIM_control_48_1_1_20120803.ctl通道 ORA_AUX_DISK_1: 段句柄 = /data/backup/HRDBPRIM_control_48_1_1_20120803.ctl 标记 = TAG20120803T142924通道 ORA_AUX_DISK_1: 已还原备份片段 1通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:03输出文件名=+DATA/hrdb/controlfile/control01.ctl输出文件名=+FRA/hrdb/controlfile/control02.ctl完成 restore 于 2012-08-03 15:03:12数据库已装载内存脚本的内容:{   set until scn  1076103;   set newname for datafile  1 to  "+DATA/hrdb/datafile/system01.dbf";   set newname for datafile  2 to  "+DATA/hrdb/datafile/sysaux01.dbf";   set newname for datafile  3 to  "+DATA/hrdb/datafile/undotbs03.dbf";   set newname for datafile  4 to  "+DATA/hrdb/datafile/users01.dbf";   restore   clone database   ;}正在执行内存脚本正在执行命令: SET until clause正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME正在执行命令: SET NEWNAME启动 restore 于 2012-08-03 15:03:17使用通道 ORA_AUX_DISK_1通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集通道 ORA_AUX_DISK_1: 正在指定从备份集还原的数据文件通道 ORA_AUX_DISK_1: 将数据文件 00001 还原到 +DATA/hrdb/datafile/system01.dbf通道 ORA_AUX_DISK_1: 将数据文件 00002 还原到 +DATA/hrdb/datafile/sysaux01.dbf通道 ORA_AUX_DISK_1: 将数据文件 00003 还原到 +DATA/hrdb/datafile/undotbs03.dbf通道 ORA_AUX_DISK_1: 将数据文件 00004 还原到 +DATA/hrdb/datafile/users01.dbf通道 ORA_AUX_DISK_1: 正在读取备份片段 /data/backup/hrdbprim-1anhnl0f_1_1通道 ORA_AUX_DISK_1: 段句柄 = /data/backup/hrdbprim-1anhnl0f_1_1 标记 = TAG20120803T142830通道 ORA_AUX_DISK_1: 已还原备份片段 1通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:16完成 restore 于 2012-08-03 15:03:33内存脚本的内容:{   switch clone datafile all;}正在执行内存脚本数据文件 1 已转换成数据文件副本输入数据文件副本 RECID=5 STAMP=790355013 文件名=+DATA/hrdb/datafile/system01.dbf数据文件 2 已转换成数据文件副本输入数据文件副本 RECID=6 STAMP=790355013 文件名=+DATA/hrdb/datafile/sysaux01.dbf数据文件 3 已转换成数据文件副本输入数据文件副本 RECID=7 STAMP=790355013 文件名=+DATA/hrdb/datafile/undotbs03.dbf数据文件 4 已转换成数据文件副本输入数据文件副本 RECID=8 STAMP=790355013 文件名=+DATA/hrdb/datafile/users01.dbf内存脚本的内容:{   set until scn  1076103;   recover   clone database    delete archivelog   ;}正在执行内存脚本正在执行命令: SET until clause启动 recover 于 2012-08-03 15:03:33使用通道 ORA_AUX_DISK_1正在开始介质的恢复通道 ORA_AUX_DISK_1: 正在开始将归档日志还原到默认目标通道 ORA_AUX_DISK_1: 正在还原归档日志归档日志线程=1 序列=18通道 ORA_AUX_DISK_1: 正在读取备份片段 /data/backup/HRDBPRIM_arch_46_1_1_20120803.bak通道 ORA_AUX_DISK_1: 段句柄 = /data/backup/HRDBPRIM_arch_46_1_1_20120803.bak 标记 = TAG20120803T142917通道 ORA_AUX_DISK_1: 已还原备份片段 1通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01归档日志文件名=+FRA/hrdb/archivelog/2012_08_03/thread_1_seq_18.490.790355017 线程=1 序列=18通道 clone_default: 正在删除归档日志归档日志文件名=+FRA/hrdb/archivelog/2012_08_03/thread_1_seq_18.490.790355017 RECID=15 STAMP=790355016介质恢复完成, 用时: 00:00:01完成 recover 于 2012-08-03 15:03:38Oracle 实例已启动系统全局区域总计    4392697856 字节Fixed Size                     2233616 字节Variable Size                838863600 字节Database Buffers            3539992576 字节Redo Buffers                  11608064 字节内存脚本的内容:{   sql clone "alter system set  db_name =  ''HRDB'' comment= ''Reset to original value by RMAN'' scope=spfile";   sql clone "alter system reset  db_unique_name scope=spfile";   shutdown clone immediate;   startup clone nomount;}正在执行内存脚本sql 语句: alter system set  db_name =  ''HRDB'' comment= ''Reset to original value by RMAN'' scope=spfilesql 语句: alter system reset  db_unique_name scope=spfileOracle 实例已关闭已连接到辅助数据库 (未启动)Oracle 实例已启动系统全局区域总计    4392697856 字节Fixed Size                     2233616 字节Variable Size                838863600 字节Database Buffers            3539992576 字节Redo Buffers                  11608064 字节sql 语句: CREATE CONTROLFILE REUSE SET DATABASE "HRDB" RESETLOGS ARCHIVELOG   MAXLOGFILES     16  MAXLOGMEMBERS      3  MAXDATAFILES      100  MAXINSTANCES     8  MAXLOGHISTORY      292 LOGFILE  GROUP  7 ( '+DATA/tyolap/onlinelog/redo31a' ) SIZE 50 M  REUSE,  GROUP  8 ( '+DATA/tyolap/onlinelog/redo32a' ) SIZE 50 M  REUSE DATAFILE  '+DATA/hrdb/datafile/system01.dbf' CHARACTER SET ZHS16GBK内存脚本的内容:{   set newname for clone tempfile  1 to new;   switch clone tempfile all;   catalog clone datafilecopy  "+DATA/hrdb/datafile/sysaux01.dbf",  "+DATA/hrdb/datafile/undotbs03.dbf",  "+DATA/hrdb/datafile/users01.dbf";   switch clone datafile all;}正在执行内存脚本正在执行命令: SET NEWNAME临时文件 1 在控制文件中已重命名为 +DATA已将数据文件副本列入目录数据文件副本文件名=+DATA/hrdb/datafile/sysaux01.dbf RECID=1 STAMP=790355060已将数据文件副本列入目录数据文件副本文件名=+DATA/hrdb/datafile/undotbs03.dbf RECID=2 STAMP=790355060已将数据文件副本列入目录数据文件副本文件名=+DATA/hrdb/datafile/users01.dbf RECID=3 STAMP=790355060数据文件 2 已转换成数据文件副本输入数据文件副本 RECID=1 STAMP=790355060 文件名=+DATA/hrdb/datafile/sysaux01.dbf数据文件 3 已转换成数据文件副本输入数据文件副本 RECID=2 STAMP=790355060 文件名=+DATA/hrdb/datafile/undotbs03.dbf数据文件 4 已转换成数据文件副本输入数据文件副本 RECID=3 STAMP=790355060 文件名=+DATA/hrdb/datafile/users01.dbf内存脚本的内容:{   Alter clone database open resetlogs;}正在执行内存脚本数据库已打开完成 Duplicate Db 于 2012-08-03 15:04:26