测试环境:Oracle 10gR2 RAC + Redhat 5.4 +ASM + RAW
源库:
db_name=orclSID=orcc1/orcl2
IP: 192.168.6.223/224
目标库:
db_name=orcl SID=orcl
IP:192.168.6.229
操作过程和RMAN的异机复制有点类似。区别在参数文件的处理上。
RMAN异机复制数据库
http://blog.csdn.net/xujinyang/article/details/6837409
一.目标库的准备
1.安装redhat 5.4操作系统
2.安装Oracle软件,这里只安装软件,不创建实例。
参考:linux平台oracle数据库安装
http://blog.csdn.net/xujinyang/article/details/6830215
3.建立相关目录
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/admin/orcl/cdump
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/admin/orcl/bdump
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/admin/orcl/udump
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/admin/orcl/cdump
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/
[oracle@singledb ~]$ mkdir -p /u01/app/oracle/oradata/orcl/
二.源库操作
1.创建pfile文件
SQL> create pfile from spfile;
File created.
2.将创建的pfile文件传到目标数据库的$ORACLE_HOME/dbs/目录下
[oracle@racdb1 ~]$cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@racdb1 dbs]$ ls
ab_+ASM1.dathc_orcl1.datinitdw.orainitorcl1.oraorapworcl1
hc_+ASM1.datinit+ASM1.orainit.oraorapw+ASM1
[oracle@racdb1 dbs]$scp initorcl1.ora 192.168.6.229:/u01/app/oracle/product/10.2.0/db_1/dbs/
The authenticity of host '192.168.6.229 (192.168.6.229)' can't be established.
RSA key fingerprint is 2a:5a:fb:63:ca:64:75:68:86:f8:43:b3:0b:80:0e:7c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.6.229' (RSA) to the list of known hosts.
oracle@192.168.6.229's password:
initorcl1.ora100% 14681.4KB/s00:00
确认一下:
[oracle@singledb dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@singledb dbs]$ ls
initdw.orainit.orainitorcl1.ora
3.监听配置
在源库的的tnsnames.ora文件里添加如下内容:
targetorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.229)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
4.备份源库,并将备份文件传到目标库的相同位置。备份目录必须相同。
先创建备份目录
[root@racdb1 ~]# mkdir -p /u02/backup
[root@racdb1 ~]# chown -R oracle:oinstall /u02
[root@racdb1 ~]# chmod 777 /u02
用RMAN全备数据库:
[oracle@racdb1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Dec 5 18:00:02 2010
Copyright (c) 1982, 2005, Oracle.All rights reserved.
connected to target database: ORCL (DBID=1264379992)
RMAN> RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FORMAT '/u02/backup/orcl_%U_%T' skip inaccessible filesperset 5DATABASE TAG orcl_hot_db_bk;
sql 'alter system archive log current';
BACKUP FORMAT '/u02/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
backup current controlfile tag='bak_ctlfile' format='/u02/backup/ctl_file_%U_%T';
backup spfile tag='spfile' format='/u02/backup/ORCL_spfile_%U_%T';
release channel c2;
release channel c1;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=124 instance=orcl1 devtype=DISK
allocated channel: c2
channel c2: sid=146 instance=orcl1 devtype=DISK
Starting backup at 05-DEC-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/orcl/datafile/system.256.736598559
input datafile fno=00004 name=+DATA/orcl/datafile/users.259.736598641
channel c1: starting piece 1 at 05-DEC-10
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003 name=+DATA/orcl/datafile/sysaux.257.736598563
input datafile fno=00002 name=+DATA/orcl/datafile/undotbs1.258.736598599
input datafile fno=00005 name=+DATA/orcl/datafile/undotbs2.264.736599805
channel c2: starting piece 1 at 05-DEC-10
channel c2: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/orcl_04luqhqp_1_1_20101205 tag=ORCL_HOT_DB_BK comment=NONE
channel c2: backup set complete, elapsed time: 00:08:21
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current control file in backupset
channel c2: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/orcl_03luqhqp_1_1_20101205 tag=ORCL_HOT_DB_BK comment=NONE
channel c1: backup set complete, elapsed time: 00:09:37
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 05-DEC-10
channel c2: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/orcl_05luqiaq_1_1_20101205 tag=ORCL_HOT_DB_BK comment=NONE
channel c2: backup set complete, elapsed time: 00:01:09
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/orcl_06luqicu_1_1_20101205 tag=ORCL_HOT_DB_BK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-DEC-10
sql statement: alter system archive log current
Starting backup at 05-DEC-10
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=4 stamp=736718437
input archive log thread=1 sequence=4 recid=6 stamp=736752948
input archive log thread=1 sequence=5 recid=7 stamp=736772474
input archive log thread=1 sequence=6 recid=9 stamp=736893879
input archive log thread=1 sequence=7 recid=12 stamp=736944911
channel c1: starting piece 1 at 05-DEC-10
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=3 stamp=736718159
channel c2: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/arch_07luqif5_1_1_20101205 tag=TAG20101205T181350 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:59
channel c1: deleting archive log(s)
archive log filename=+FRA/archivelog/orcl/1_3_736599263.dbf recid=4 stamp=736718437
archive log filename=+FRA/archivelog/orcl/1_4_736599263.dbf recid=6 stamp=736752948
archive log filename=+FRA/archivelog/orcl/1_5_736599263.dbf recid=7 stamp=736772474
archive log filename=+FRA/archivelog/orcl/1_6_736599263.dbf recid=9 stamp=736893879
archive log filename=+FRA/archivelog/orcl/1_7_736599263.dbf recid=12 stamp=736944911
channel c2: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/arch_08luqif6_1_1_20101205 tag=TAG20101205T181350 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:01
channel c2: deleting archive log(s)
archive log filename=+FRA/archivelog/orcl/1_2_736599263.dbf recid=3 stamp=736718159
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=2 sequence=3 recid=5 stamp=736752401
input archive log thread=2 sequence=4 recid=8 stamp=736772568
input archive log thread=2 sequence=5 recid=10 stamp=736893888
input archive log thread=2 sequence=6 recid=11 stamp=736944342
input archive log thread=2 sequence=7 recid=14 stamp=736968019
channel c1: starting piece 1 at 05-DEC-10
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=13 stamp=736968002
input archive log thread=1 sequence=9 recid=15 stamp=736971214
input archive log thread=1 sequence=10 recid=18 stamp=736971229
input archive log thread=2 sequence=1 recid=1 stamp=736717965
input archive log thread=2 sequence=2 recid=2 stamp=736718031
channel c2: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/arch_09luqih3_1_1_20101205 tag=TAG20101205T181350 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:30
channel c1: deleting archive log(s)
archive log filename=+FRA/archivelog/orcl/2_3_736599263.dbf recid=5 stamp=736752401
archive log filename=+FRA/archivelog/orcl/2_4_736599263.dbf recid=8 stamp=736772568
archive log filename=+FRA/archivelog/orcl/2_5_736599263.dbf recid=10 stamp=736893888
archive log filename=+FRA/archivelog/orcl/2_6_736599263.dbf recid=11 stamp=736944342
archive log filename=+FRA/archivelog/orcl/2_7_736599263.dbf recid=14 stamp=736968019
channel c2: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/arch_0aluqih5_1_1_20101205 tag=TAG20101205T181350 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:31
channel c2: deleting archive log(s)
archive log filename=+FRA/archivelog/orcl/1_8_736599263.dbf recid=13 stamp=736968002
archive log filename=+FRA/archivelog/orcl/1_9_736599263.dbf recid=15 stamp=736971214
archive log filename=+FRA/archivelog/orcl/1_10_736599263.dbf recid=18 stamp=736971229
archive log filename=+FRA/archivelog/orcl/2_1_736599263.dbf recid=1 stamp=736717965
archive log filename=+FRA/archivelog/orcl/2_2_736599263.dbf recid=2 stamp=736718031
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=2 sequence=8 recid=16 stamp=736971215
input archive log thread=2 sequence=9 recid=17 stamp=736971223
channel c1: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/arch_0bluqii4_1_1_20101205 tag=TAG20101205T181350 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: deleting archive log(s)
archive log filename=+FRA/archivelog/orcl/2_8_736599263.dbf recid=16 stamp=736971215
archive log filename=+FRA/archivelog/orcl/2_9_736599263.dbf recid=17 stamp=736971223
Finished backup at 05-DEC-10
Starting backup at 05-DEC-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
channel c1: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/ctl_file_0cluqil1_1_1_20101205 tag=BAK_CTLFILE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:37
Finished backup at 05-DEC-10
Starting backup at 05-DEC-10
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 05-DEC-10
channel c1: finished piece 1 at 05-DEC-10
piece handle=/u02/backup/ORCL_spfile_0dluqimo_1_1_20101205 tag=SPFILE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
Finished backup at 05-DEC-10
released channel: c2
released channel: c1
这里要注意,我是把归档文件放在了ASM里,所以可以这样备份,如果不是这么放的,可能需要其他配置,具体参考:
RAC之RMAN备份
http://blog.csdn.net/xujinyang/article/details/6837226
Linux平台下RMAN全备和增量备份shell脚本
http://blog.csdn.net/xujinyang/article/details/6838002
三.目标库操作
1.创建目标库ORCL的口令文件
[oracle@singledb dbs]$ cd $ORACLE_HOME/bin
[oracle@singledb bin]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=admin
[oracle@singledb bin]$ cd $ORACLE_HOME/dbs
[oracle@singledb dbs]$ ls
initdw.orainit.orainitorcl1.oraorapworcl
windows下oracle默认的位置是$ORACLE_HOME/database目录,文件名格式是pwdSID.ora。
linux下oracle默认的位置是$ORACLE_HOME/dbs目录,文件名格式是orapwSID。
创建完后,数据库需要重启动,新的口令文件才能生效。
关于口令文件创建,详细内容参考blog:
Oracle OS认证口令文件密码丢失处理
http://blog.csdn.net/xujinyang/article/details/6830312
2.创建备份目录
[root@singledb Server]# mkdir -p /u02/backup
[root@singledb Server]# chown -R oracle:oinstall /u02
[root@singledb Server]# chmod 777 /u02
3.配置监听
3.1创建listener.ora文件中,并添加如下内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/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)
)
)
3.2创建tnsnames.ora文件,并添加如下内容
sourceorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.223)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
建议使用net manager工具从界面来修改,这样不容易出错:
Oracle数据库监听配置
http://blog.csdn.net/xujinyang/article/details/6830009
Oracle Listener动态注册与静态注册
http://blog.csdn.net/xujinyang/article/details/6829560
4.修改参数文件
之前已经将参数文件传了过来。我们先对参数文件重命名。
[oracle@singledb dbs]$mv initorcl1.ora initorcl.ora --改成orcl
[oracle@singledb dbs]$cat initorcl.ora
orcl2.__db_cache_size=117440512
orcl1.__db_cache_size=54525952
orcl1.__java_pool_size=4194304
orcl2.__java_pool_size=4194304
orcl1.__large_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=92274688
orcl1.__shared_pool_size=155189248
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.1.0'
*.control_files='+DATA/orcl/controlfile/current.260.736599257','+FRA/orcl/controlfile/current.256.736599259'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=3040870400
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
orcl1.instance_number=1
orcl2.instance_number=2
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+FRA/archivelog/orcl/'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=73400320
*.processes=150
*.remote_listener='LISTENERS_ORCL'
*.remote_login_passwordfile='exclusive'
*.sga_target=221249536
orcl2.thread=2
orcl1.thread=1
*.undo_management='AUTO'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
[oracle@singledb dbs]$
红色部分就是我们需要删除或者修改的。
修改之后的pfile文件如下:
[oracle@singledb dbs]$ cat initorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.cluster_database=false
*.compatible='10.2.0.1.0'
*.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_create_file_dest='/u01/app/oracle/oradata/orcl/'
*.db_domain=''
*.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
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/archivelog/'
*.log_archive_format='%t_%s_%r.arch'
*.open_cursors=300
*.pga_aggregate_target=150229440
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=359785472
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.log_file_name_convert=('+DATA/orcl/onlinelog','/u01/app/oracle/oradata/orcl')
*.log_file_name_convert=('+FRA/orcl/onlinelog','/u01/app/oracle/oradata/orcl')
*.db_file_name_convert=('+DATA/orcl/datafile','/u01/app/oracle/oradata/orcl')
*.db_file_name_convert=('+DATA/orcl/tempfile','/u01/app/oracle/oradata/orcl')
注意最后两行,因为RAC的目录结构和我们的单实例不一样,所以我们这里进行转换一下。这里面要根据自己的情况来决定。比如我将redo放到了FRA了。所以在这里也需要进行转换。
这里可以根据RMAN duplicate的命令进行判断:
RMAN> duplicate target database to orcl;
..
RMAN-05001: auxiliary filename +FRA/orcl/onlinelog/group_2.258.736599295 conflicts with a file used by the target database
这是我测试时遇到的问题。把FRA也做一下转换就搞定了。
如果这里不写这个参数的话,那么在RMAN恢复的时候就需要用命令转换。如:
SET NEWNAME FOR DATAFILE 1 to '/u01/app/oracle/oradata/orcl/sys01.dbf';
查看文件位置:
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ---------------------------------------------------------------------
1 +DATA/orcl/datafile/system.256.736598559
2 +DATA/orcl/datafile/undotbs1.258.736598599
3 +DATA/orcl/datafile/sysaux.257.736598563
4 +DATA/orcl/datafile/users.259.736598641
5 +DATA/orcl/datafile/undotbs2.264.736599805
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ---------------------------------------------------------------------
1 +DATA/orcl/tempfile/temp.263.73659950
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/orcl/onlinelog/group_2.262.736599285
+FRA/orcl/onlinelog/group_2.258.736599295
+DATA/orcl/onlinelog/group_1.261.736599263
+FRA/orcl/onlinelog/group_1.257.736599275
+DATA/orcl/onlinelog/group_3.265.736600119
+FRA/orcl/onlinelog/group_3.259.736600137
+DATA/orcl/onlinelog/group_4.266.736600151
+FRA/orcl/onlinelog/group_4.260.736600165
8 rows selected.
5.用SCP将源库的备份copy到目标库
[oracle@singledb admin]$scp 192.168.6.223:/u02/backup/* /u02/backup
oracle@192.168.6.223's password:
arch_07luqif5_1_1_20101205100%56MB 909.4KB/s01:03
arch_08luqif6_1_1_20101205100%50MB 711.1KB/s01:12
arch_09luqih3_1_1_20101205100%43MB1.3MB/s00:34
arch_0aluqih5_1_1_20101205100%22MB 933.7KB/s00:24
arch_0bluqii4_1_1_20101205100% 1341KB 670.3KB/s00:02
ctl_file_0cluqil1_1_1_20101205100%15MB 998.4KB/s00:15
orcl_03luqhqp_1_1_20101205100%354MB1.3MB/s04:42
orcl_04luqhqp_1_1_20101205100%210MB2.3MB/s01:31
orcl_05luqiaq_1_1_20101205100%15MB2.9MB/s00:05
orcl_06luqicu_1_1_20101205100%96KB96.0KB/s00:01
ORCL_spfile_0dluqimo_1_1_20101205100%96KB96.0KB/s00:00
[oracle@singledb admin]$
6.将目标库启动到nomount状态,并进行duplicate
6.1启动目标库到nomount状态
[oracle@singledb dbs]$ export ORACLE_SID=orcl
[oracle@singledb dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 7 18:47:36 2010
Copyright (c) 1982, 2005, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area360710144 bytes
Fixed Size1219424 bytes
Variable Size109053088 bytes
Database Buffers247463936 bytes
Redo Buffers2973696 bytes
SQL>
6.2进行duplicate
[oracle@singledb dbs]$ tnsping sourceorcl
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 07-DEC-2010 18:49:35
Copyright (c) 1997, 2005, Oracle.All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.223)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) (INSTANCE_NAME = orcl1)))
OK (200 msec)
[oracle@singledb dbs]$ rman target sys/h2oiswater@sourceorcl auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Dec 7 18:54:37 2010
Copyright (c) 1982, 2005, Oracle.All rights reserved.
connected to target database: ORCL (DBID=1264379992)
connected to auxiliary database: ORCL (not mounted)
RMAN>duplicate target database to orcl;
--这里要注意,因为我是将RAC的归档文件放在ASM上的,所以可以这么写。如果归档不是这么放的,就需要手工配置通过。如
run{
allocate channel d1 type disk connect sys/oracle@rac1;
allocate channel d2 type disk connect sys/oracle@rac2;
allocate auxiliary channel d3 type disk;
duplicate target database to orcl;
}
RMAN> duplicate target database to orcl;
Starting Duplicate Db at 07-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set until scn861023;
set newname for datafile1 to
"/u01/app/oracle/oradata/orcl/system.256.736598559"; --在pfile里设置的参数
set newname for datafile2 to
"/u01/app/oracle/oradata/orcl/undotbs1.258.736598599";
set newname for datafile3 to
"/u01/app/oracle/oradata/orcl/sysaux.257.736598563";
set newname for datafile4 to
"/u01/app/oracle/oradata/orcl/users.259.736598641";
set newname for datafile5 to
"/u01/app/oracle/oradata/orcl/undotbs2.264.736599805";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-DEC-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to/u01/app/oracle/oradata/orcl/undotbs1.258.736598599
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux.257.736598563
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/undotbs2.264.736599805
--先将数据从备份集中restore到/u01/app/oracle/oradata/orcl目录
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_0fluu1ac_1_1_20101207
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/orcl_0fluu1ac_1_1_20101207 tag=ORCL_HOT_DB_BK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system.256.736598559
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users.259.736598641
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_0eluu1aa_1_1_20101207
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/orcl_0eluu1aa_1_1_20101207 tag=ORCL_HOT_DB_BK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 07-DEC-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL"
--创建控制文件
RESETLOGS ARCHIVELOG
MAXLOGFILES192
MAXLOGMEMBERS3
MAXDATAFILES1024
MAXINSTANCES32
MAXLOGHISTORY292
LOGFILE
GROUP1 ( '/u01/app/oracle/oradata/orcl/group_1.261.736599263', '/u01/app/oracle/oradata/orcl/group_1.257.736599275' ) SIZE 50 MREUSE,
GROUP2 ( '/u01/app/oracle/oradata/orcl/group_2.262.736599285', '/u01/app/oracle/oradata/orcl/group_2.258.736599295' ) SIZE 50 MREUSE
DATAFILE
'/u01/app/oracle/oradata/orcl/system.256.736598559'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=737150574 filename=/u01/app/oracle/oradata/orcl/undotbs1.258.736598599
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=737150574 filename=/u01/app/oracle/oradata/orcl/sysaux.257.736598563
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=737150574 filename=/u01/app/oracle/oradata/orcl/users.259.736598641
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=737150574 filename=/u01/app/oracle/oradata/orcl/undotbs2.264.736599805
contents of Memory Script:
{
set until scn861023;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 07-DEC-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
-- restore归档文件
archive log thread=2 sequence=11
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_0hluu1q6_1_1_20101207
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/arch_0hluu1q6_1_1_20101207 tag=TAG20101207T015407
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_0iluu1q7_1_1_20101207
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/arch_0iluu1q7_1_1_20101207 tag=TAG20101207T015407
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=/u01/archivelog/1_12_736599263.arch thread=1 sequence=12
archive log filename=/u01/archivelog/2_11_736599263.arch thread=2 sequence=11
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/2_11_736599263.arch recid=1 stamp=737150585
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=2 sequence=12
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_0jluu1qu_1_1_20101207
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/arch_0jluu1qu_1_1_20101207 tag=TAG20101207T015407
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/archivelog/2_12_736599263.arch thread=2 sequence=12
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/1_12_736599263.arch recid=3 stamp=737150587
archive log filename=/u01/archivelog/1_13_736599263.arch thread=1 sequence=13
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/1_13_736599263.arch recid=2 stamp=737150587
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/2_12_736599263.arch recid=4 stamp=737150593
media recovery complete, elapsed time: 00:00:06
Finished recover at 07-DEC-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
--如果在这里卡住,就检查一下有没有session窗口是开的,有关闭即可
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area360710144 bytes
Fixed Size1219424 bytes
Variable Size109053088 bytes
Database Buffers247463936 bytes
Redo Buffers2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES192
MAXLOGMEMBERS3
MAXDATAFILES1024
MAXINSTANCES32
MAXLOGHISTORY292
LOGFILE
GROUP1 ( '/u01/app/oracle/oradata/orcl/group_1.261.736599263', '/u01/app/oracle/oradata/orcl/group_1.257.736599275' ) SIZE 50 MREUSE,
GROUP2 ( '/u01/app/oracle/oradata/orcl/group_2.262.736599285', '/u01/app/oracle/oradata/orcl/group_2.258.736599295' ) SIZE 50 MREUSE
DATAFILE
'/u01/app/oracle/oradata/orcl/system.256.736598559'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile1 to
"/u01/app/oracle/oradata/orcl/temp.263.736599505";
switch clone tempfile all;
catalog clone datafilecopy"/u01/app/oracle/oradata/orcl/undotbs1.258.736598599";
catalog clone datafilecopy"/u01/app/oracle/oradata/orcl/sysaux.257.736598563";
catalog clone datafilecopy"/u01/app/oracle/oradata/orcl/users.259.736598641";
catalog clone datafilecopy"/u01/app/oracle/oradata/orcl/undotbs2.264.736599805";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp.263.736599505 in control file
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs1.258.736598599 recid=1 stamp=737150639
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/sysaux.257.736598563 recid=2 stamp=737150640
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/users.259.736598641 recid=3 stamp=737150641
cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/orcl/undotbs2.264.736599805 recid=4 stamp=737150642
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=737150639 filename=/u01/app/oracle/oradata/orcl/undotbs1.258.736598599
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=737150640 filename=/u01/app/oracle/oradata/orcl/sysaux.257.736598563
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=737150641 filename=/u01/app/oracle/oradata/orcl/users.259.736598641
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=737150642 filename=/u01/app/oracle/oradata/orcl/undotbs2.264.736599805
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 07-DEC-10
RMAN>
至此,RMAN的duplicate已经完成。迁移基本完成。
四.目标库的其他扫尾操作
4.1清除多余的undo文件
查看UNDO信息:
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
因为我们使用的是UNDOTBS1,在pfile里设置的,所以把UNDOTBS2删除掉。
SQL>show parameter undo_tablespace;
NAMETYPEVALUE
------------------------------------ ----------- ------------------------------
undo_tablespacestringUNDOTBS1
SQL>drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
SQL>select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
SQL>
4.2清除未使用线程的redo日志组
默认情况下,RAC环境下,每个实例都有2个redo。在单实例下,就没有必要了。我们删除点线程2的redo信息。
SQL> select thread#,status,enabled from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPENPUBLIC
2 CLOSED PRIVATE
SQL>select group#,thread#,archived,status from v$log;
GROUP#THREAD# ARC STATUS
---------- ---------- --- ----------------
11 YES UNUSED
21 NOCURRENT
32 NOCURRENT
42 YES UNUSED
SQL> alter database disable thread 2;
Database altered.
SQL>alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,thread#,archived,status from v$log;
GROUP#THREAD# ARC STATUS
---------- ---------- --- ----------------
11 YES INACTIVE
21 NOCURRENT
现在就剩2个了。一般的单实例是3个online redo。我们在添加一组。
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03.log') size 10m;
Database altered.
SQL> select group#,thread#,archived,status from v$log;
GROUP#THREAD# ARC STATUS
---------- ---------- --- ----------------
11 YES INACTIVE
21 NOCURRENT
31 YES UNUSED
Redo的相关信息,也可以参考:
Redo Log和Checkpoint not complete
http://blog.csdn.net/xujinyang/article/details/6832719
如何搭建一个数据库服务器平台
http://blog.csdn.net/xujinyang/article/details/6822424
4.3重建临时表空间,并删除原来的数据文件
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ---------------------------------------------------------------------
1 /u01/app/oracle/oradata/orcl/temp.263.736599505
Temp重建参考:
Oracle Temp临时表空间
http://blog.csdn.net/xujinyang/article/details/6830318
4.4OEM配置
手工用命令创建一下,也就几个命令。参考:
http://blog.csdn.net/xujinyang/article/details/6948034
小结:
步骤和RMAN的异机复制基本差不多了。就多加了几个参数。也是折腾到现在,都2点多了。这几天白天比较忙,没有时间搞。月底公司服务器要搬迁,从绍兴的IDC机房搬到萧山的IDC机房。有20台左右的服务器,还有盘柜。搬迁之后还要对数据库做一次迁移和升级。够折腾的,还有十来天时间,抓紧时间测试。折腾死了。
------------------------------------------------------------------------------