RMAN 异机 复制数据库

1、RMAN 异机 复制数据库 2014年4月24日15:48 RMAN 异机 复制数据库 RMAN 异机复制和同机复制操作步骤是一样的，不同的是我们需要把备份的文件copy到辅助库上去，而且位置要和目标库上一致，因为备份集的位置是写在控制文件里的。 实验平台： oraclelinux+ oracle 10g目标库信息：ORACLE_BASE: /u01/app/oracleORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1ORACLE_SID：orclIP: 00辅助库信息：ORACLE_BASE: /u01/app/

2、oracleORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1ORACLE_SID：orclIP: 我们以实例名相同的为例，实例名不同也一样，在pfile里修改对应参数就可以了。步骤如下：（1）构建辅助数据库目录结构（2）创建pfile，并复制到辅助数据库，修改相关内容（3）创建辅助实例口令文件（4）RMAN 备份源库（5）添加配置监听（6）将源库备份文件拷贝到辅助库相同位置（7）用pfile文件，将辅助数据库启动到nomount 状态（8）执行RMAN duplicate命令复制数据库（9）创建spfile文件（10）在磁带

3、上恢复备份（11）使用DBNEWID程序生成新的DBID一构建辅助数据库目录结构1.1 Oracle data 目录oraclelocalhost oracle$ mkdir oradataoraclelocalhost oracle$ lsoradata oraInventory productoraclelocalhost oracle$ cd oradata/oraclelocalhost oracle$ mkdir orcl1.2 其他目录oraclelocalhost oracle$ pwd/u01/app/oracleoraclelocalhost oracle$ mkdir ad

4、minoraclelocalhost oracle$ lsadmin oradata oraInventory productoraclelocalhost oracle$ cd adminoraclelocalhost admin$ lsoraclelocalhost admin$ mkdir orcloraclelocalhost admin$ lsorcloraclelocalhost admin$ cd orcloraclelocalhost orcl$ mkdir adumporaclelocalhost orcl$ mkdir bdumporaclelocalhost orcl$

5、mkdir cdumporaclelocalhost orcl$ mkdir dpdumporaclelocalhost orcl$ mkdir pfileoraclelocalhost orcl$ mkdir udumporaclelocalhost orcl$ lsadump bdump cdump dpdump pfile udump二 创建pfile，复制到辅助数据库，并修改相关参数2.1 在源库上创建pfile 文件SQL> conn sys/adminorcl as sysdba;Connected.SQL> create pfile from spfile;File

6、created.2.2 用scp 命令将pfile 文件传到辅助库。要放在$ORACLE_HOME/dbs目录下，不然ORACLE 不识别。oracledb1 orcl$ cd /u01/app/oracle/product/10.2.0/db_1/dbsoracledb1 dbs$ lshc_DAVE.dat initdw.ora lkDAVE orapworcl spfileorcl.orahc_orcl.dat init.ora lkORCL snapcf_orcl.finitDAVE.ora initorcl.ora orapwDAVE spfileDAVE.oraoracledb1

7、dbs$ scp initorcl.ora 1:/$ORACLE_HOME/dbsThe authenticity of host '1 (1)' can't be established.RSA key fingerprint is 1a:20:7a:05:bd:e0:ac:04:21:02:b1:72:01:69:40:d6.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added '

8、;1' (RSA) to the list of known hosts.oracle1's password:initorcl.ora 100% 1108 1.1KB/s 00:002.3 在辅助库上确认文件是否传送过来oraclelocalhost orcl$ cd $ORACLE_HOME/dbsoraclelocalhost dbs$ lsinitdw.ora init.ora initorcl.ora2.4 在辅助库上修改pfile参数oraclelocalhost dbs$ more initorcl.oraorcl._d

9、b_cache_size=167772160orcl._java_pool_size=4194304orcl._large_pool_size=4194304orcl._shared_pool_size=62914560orcl._streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'*.compatible='.0'*.contr

10、ol_file_record_keep_time=14*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'*.db_block_size=8192*.db_domain='

11、9;*.db_file_multiblock_read_count=16*.db_name='orcl'*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.job_queue_processes=10*.log_archive_dest_1='location=/u01/archivelog&#

12、39;*.open_cursors=300*.pga_aggregate_target=81788928*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=246415360*.undo_management='AUTO'*.undo_tablespace='UNDOTBS1'*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'说明，在这里我没有做修改，因为我异机复制实例名相同，存储位置也相同。 如果

13、说你复制的不同的话，就需要修改相关的实例名。 如果存储位置不同的话，还需要添加以下参数，对数据文件位置进行转换。db_file_name_convert = ('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/AUX')三创建辅助实例ORCL 的口令文件oraclelocalhost dbs$ cd $ORACLE_HOME/binoraclelocalhost bin$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=adminoraclelocalh

14、ost bin$ cd $ORACLE_HOME/dbsoraclelocalhost dbs$ lsinitdw.ora init.ora initorcl.ora orapworclwindows下oracle默认的位置是$ORACLE_HOME/database目录，文件名格式是pwdSID.ora。 linux下oracle默认的位置是$ORACLE_HOME/dbs目录，文件名格式是orapwSID。 创建完后，数据库需要重启动，新的口令文件才能生效。 四RMAN 备份源库（orcl）mkdir p /u01/backup oracledb1 backup$ rman target

15、sys/adminorclRecovery Manager: Release .0 - Production on Sun Jul 18 18:26:16 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=1248423599)RMAN>RUN allocate channel c1 type disk;allocate channel c2 type disk;BACKUP FORMAT '/u01/backup/o

16、rcl_%U_%T' skip inaccessible filesperset 5 DATABASE TAG orcl_hot_db_bk; sql 'alter system archive log current'BACKUP FORMAT '/u01/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT; backup current controlfile tag='bak_ctlfile' format='/u01/

17、backup/ctl_file_%U_%T'backup spfile tag='spfile' format='/u01/backup/ORCL_spfile_%U_%T'release channel c2;release channel c1; 五添加，配置监听5.1 在辅助库的Listener.ora 文件中，添加如下内容用图形netmgr操作SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /u01/app/oracle/product/

18、10.2.0/db_1)(PROGRAM = extproc)(SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)(SID_NAME = orcl)启动侦听 lsnrctl start Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service.  5.2 修改辅助库的tnsnam

19、es.ora 文件，添加如下内容TARGETORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 00)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NA

20、ME = orcl) ) )可以远程连接主库服务名 5.3 修改源库的tnsnames.ora 文件，添加如下内容：AUXORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 00)(POR

21、T = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )建议使用net manager 工具从界面来修改，这样不容易出错 六 将源库的备份拷贝到辅助数据库的相同位置 在辅助数据上建立一个文件/u01/backup 这里有两种方法，一种直接在辅助库上建相同的目录结构，在用SCP 命令把这些备份文件copy过去，但是这样会占用大量的网络资源。 还有一种就是建好目录结构后用NFS直接Mount上去。 6.1 在辅助库上建相同的备份目录结构因为我们在源库上备份是备份到/u01/backup目录中的，所以我们在辅

22、助库上也建这个目录rootlocalhost /# mkdir -p /u01/backup rootlocalhost /# chmod 777 /u01/backup6.2 将源库的备份挂载到辅助库上6.2.1 在源库上将/u01/backup 目录添加到共享rootdb1 # more /etc/exports/u01/backup (rw,async,anonuid=65534,anongid=65534)rootdb1 # service nfs start6.2.2 在辅助库上挂载源库的目录，注意，mount命令必须以root用户执行rootlocalhost

23、 /# mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 00:/u01/backup /u01/backup - 注意： 在挂载NFS的时候，一定要加上-t -o 参数，不然在RMAN 复制的时候会报错：-ORA-27054: NFS file system where the file is created or resides is not mounted with correct optionsrootlocalhost /# cd /u

24、01/backup/rootlocalhost backup# lsarch_3klj4676_1_1_20100718 orcl_3hlj45vf_1_1_20100718arch_3llj4676_1_1_20100718 orcl_3ilj465c_1_1_20100718ctl_file_3mlj467h_1_1_20100718 orcl_3jlj4661_1_1_20100718orcl_3glj45vf_1_1_20100718 ORCL_spfile_3nlj467k_1_1_20100718七 启动辅助库至nomount 状态，注意使用pfile参数启动oraclelocal

25、host dbs$ sqlplus /nologSQL*Plus: Release .0 - Production on Tue Jul 20 03:53:53 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn / as sysdbaConnected to an idle instance.SQL> startup nomount pfile=?/dbs/initorcl.oraORACLE instance started.Total System Global Area 24

26、7463936 bytesFixed Size 1218772 bytesVariable Size 79693612 bytesDatabase Buffers 163577856 bytesRedo Buffers 2973696 bytesSQL> exit - 启动后就退出session，因为在duplicata的时候不能有session连接Disconnected from Oracle Database 10g Enterprise Edition Release .0 - ProductionWith the Partitioning, OLAP and D

27、ata Mining options八 RMAN 连接到目标实例和辅助实例，运行duplicate 命令复制数据库oracledb1 $ rman target sys/oracleorcl auxiliary sys/oracleauxorcl;Recovery Manager: Release .0 - Production on Sun Jul 18 20:41:19 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=1248

28、423599)connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database to orcl nofilenamechecklogfile'/u01/app/oracle/oradata/orcl/redo01.dbf' size 50m, '/u01/app/oracle/oradata/orcl/redo02.dbf' size 50m, '/u01/app/oracle/oradata/orcl/redo03.dbf' size 50

29、m;注： 这个地方有2个地方需要说明：（1） Nofilenamecheck 参数： 如果在复制时，位置不同时，我们会用参数db_file_name_convert 对文件位置进行转换。 但是在这个复制示例中我们用的是相同的位置。 所以这里必须加上nofilenamecheck参数。 该参数通知复制操作不必在执行还原操作前确认文件名是不同的。如果没有指定nofilenamecheck参数，rman会给出如下错误：RMAN-05001: auxiliary filename /u01/app/oracle/oradata/orcl/example01.dbf conflicts with a f

30、ile used by the target databaseRMAN-05001: auxiliary filename /u01/app/oracle/oradata/orcl/users01.dbf conflicts with a file used by the target database（2） logfile 参数：在完成复制进程并执行最有一条open resetlogs命令时，RMAN 必须为辅助数据库构建全新的日志文件。 如上面命令所示，我们可以在duplicate 命令中指定logfile参数，并指定使用的日志文件的大小，数目和位置。 还可以在pfile里指定log_fi

31、le_name_convert 参数来进行转换。 这种方法相对而言要简单一点。 但因为我们在RMAN 同机复制数据库的例子中用的就是这种方法，所以就换种方法来测试。 Starting Duplicate Db at 18-JUL-10using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=155 devtype=DISKcontents of Memory Script:set until scn 7335

32、45;set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf"set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf"set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf"set newname for datafile 4 to "/u01/app/or

33、acle/oradata/orcl/users01.dbf"set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/example01.dbf"restorecheck readonlyclone database ;executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEW

34、NAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 18-JUL-10using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backupset restore - 开始restore 数据文件channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00002 to /u

35、01/app/oracle/oradata/orcl/undotbs01.dbfrestoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbfrestoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_3plj4ejl_1_1_20100718channel ORA_AUX_DISK_1: restored

36、backup piece 1piece handle=/u02/backup/orcl_3plj4ejl_1_1_20100718 tag=ORCL_HOT_DB_BKchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:48channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 0

37、0001 to /u01/app/oracle/oradata/orcl/system01.dbfrestoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_3olj4ejk_1_1_20100718channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/backup/orcl_3olj4ejk_1_1_20100

38、718 tag=ORCL_HOT_DB_BKchannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:14Finished restore at 18-JUL-10sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG- 创建源库的控制文件，然后根据这个文件进行相关的恢复MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHIS

39、TORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.dbf' SIZE 50 M ,GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.dbf' SIZE 50 M ,GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.dbf' SIZE 50 MDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf'CHARACTER SET WE8ISO8

40、859P1contents of Memory Script:switch clone datafile all;executing Memory Scriptreleased channel: ORA_AUX_DISK_1datafile 2 switched to datafile copyinput datafile copy recid=1 stamp=724831148 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy r

41、ecid=2 stamp=724831148 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=3 stamp=724831148 filename=/u01/app/oracle/oradata/orcl/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=724831148 filename=/u01/app/

42、oracle/oradata/orcl/example01.dbfcontents of Memory Script:set until scn 733545;recoverclone databasedelete archivelog ;executing Memory Scriptexecuting command: SET until clauseStarting recover at 18-JUL-10allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=155 devtype=DISKstarting media r

43、ecoverychannel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=46channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_3tlj4er7_1_1_20100718channel ORA_AUX_DISK_1: restored backup piece 1piece

44、handle=/u02/backup/arch_3tlj4er7_1_1_20100718 tag=TAG20100718T205809channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=45channel ORA_AUX_

45、DISK_1: reading from backup piece /u02/backup/arch_3slj4er7_1_1_20100718channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/backup/arch_3slj4er7_1_1_20100718 tag=TAG20100718T205809channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02archive log filename=/u01/archivelog/1_45_7

46、20642866.dbf thread=1 sequence=45channel clone_default: deleting archive log(s)archive log filename=/u01/archivelog/1_45_720642866.dbf recid=2 stamp=724831155archive log filename=/u01/archivelog/1_46_720642866.dbf thread=1 sequence=46channel clone_default: deleting archive log(s)archive log filename

47、=/u01/archivelog/1_46_720642866.dbf recid=1 stamp=724831153media recovery complete, elapsed time: 00:00:02Finished recover at 18-JUL-10contents of Memory Script:shutdown clone;startup clone nomount ;- 如果此时，辅助实例上有session连在上面，将一直等到session退出才能执行，如果等了很长时间还没有执行，不妨检查一下，有没有session连在数据库上。executing Memory Sc

48、riptdatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 247463936 bytesFixed Size 1218772 bytesVariable Size 79693612 bytesDatabase Buffers 163577856 bytesRedo Buffers 2973696 bytessql statement: CREATE CONTROLFILE

49、REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG- 创建新的控制文件MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.dbf' SIZE 50 M ,GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.dbf' SIZE 50 M ,GROUP 3 '

50、/u01/app/oracle/oradata/orcl/redo03.dbf' SIZE 50 MDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf'CHARACTER SET WE8ISO8859P1contents of Memory Script:set newname for tempfile 1 to"/u01/app/oracle/oradata/orcl/temp01.dbf"switch clone tempfile all;catalog clone datafilecopy &q

51、uot;/u01/app/oracle/oradata/orcl/undotbs01.dbf"catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/sysaux01.dbf"catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/users01.dbf"catalog clone datafilecopy "/u01/app/oracle/oradata/orcl/example01.dbf"switch c

52、lone datafile all;executing Memory Scriptexecuting command: SET NEWNAMErenamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control filecataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs01.dbf recid=1 stamp=724831185cataloged datafile copy datafile

53、copy filename=/u01/app/oracle/oradata/orcl/sysaux01.dbf recid=2 stamp=724831186cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/orcl/users01.dbf recid=3 stamp=724831186cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf recid=4 stamp=724831

54、186datafile 2 switched to datafile copy input datafile copy recid=1 stamp=724831185 filename=/u01/app/oracle/oradata/orcl/undotbs01.dbfdatafile 3 switched to datafile copy input datafile copy recid=2 stamp=724831186 filename=/u01/app/oracle/oradata/orcl/sysaux01.dbfdatafile 4 switched to datafile co

55、pyinput datafile copy recid=3 stamp=724831186 filename=/u01/app/oracle/oradata/orcl/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=724831186 filename=/u01/app/oracle/oradata/orcl/example01.dbfcontents of Memory Script:Alter clone database open resetlogs;executing Me

56、mory Scriptexecuting Memory Scriptdatabase openedFinished Duplicate Db at 18-JUL-10九 创建spfile，并验证数据库通过上面8步的操作，复制操作已经完成。 我们现在创建一个spfile。SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'File created.SQL> select name from v$database;NAME-ORCLSQL> sele

57、ct tablespace_name,file_name from dba_data_files;TABLESPACE_NAME FILE_NAME- -SYSTEM /u01/app/oracle/oradata/orcl/system01.dbfUNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbfSYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbfUSERS /u01/app/oracle/oradata/orcl/users01.dbfEXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf十 在远程服务器上复制磁带备份如果在远程服务器上复制磁带备份，必须配置辅助通道，以反映介质管理器的需求。 除了指定介质管理器和用于常规通道的类或池外，还必须指定目标客户端名称，如下所示：RMAN> configure auxiliary channel 1 device type sb