RAC迁移至单实例利用RMANDUPLICATE-Tim
测试环境: Oracle 11.2.0.3.0 RAC + Redhat 5.5 +ASM
源库:
db_name=prod SID=prod1/prod2
IP: 192.168.6.60/62
一. 目标库的准备
1. 安装redhat 5.5 操作系统
2. 安装Oracle 软件, 这里只安装软件,不创建实例。
(如果内存不够就不用再起一个虚拟机了,直接在第二节点操作也行)
二. 源库操作
1. 创建pfile文件
SQL> create pfile from spfile;
File created.
2. 将创建的pfile文件传到目标数据库的$ORACLE_HOME/dbs/ 目录下
(这里我们目标库为single,PFILE要改名为initsingle.ora)
3. 备份源库,并将备份文件传到目标库的相同位置。 备份目录必须相同。
先创建备份目录
[oracle@rh1 ~]$ mkdir -p /home/oracle/backup
用RMAN 全备数据库:
[oracle@rh1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 -
Production on Wed Jul 30 10:06:35 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=265283811)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod1.f'; # default
RMAN> RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FORMAT '/home/oracle/backup/prod_%U_%T' DATABASE;
sql 'alter system archive log current';
BACKUP FORMAT '/home/oracle/backup/arch_%U_%T' ARCHIVELOG ALL;
release channel c2;
release channel c1;
}
三. 目标库操作
1. 创建目标库single 的口令文件
[oracle@rh2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@rh2 dbs]$ orapwd file=orapurpdb password=oracle
2.创建备份目录
[oracle@rh2 ~]$ mkdir -p /home/oracle/backup
3.1 如果是另起一台虚拟机,需要配置监听,这里我们直接在rh2操作,就不用配置监听了
3.2 配置TNS,添加如下内容
sourceprod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.60)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
(INSTANCE_NAME = prod1)
)
)
4. 修改参数文件
源参数文件:
prod1.__db_cache_size=218103808
prod2.__db_cache_size=218103808
prod1.__java_pool_size=4194304
prod2.__java_pool_size=4194304
prod1.__large_pool_size=4194304
prod2.__large_pool_size=4194304
prod1.__pga_aggregate_target=142606336
prod2.__pga_aggregate_target=142606336
prod1.__sga_target=423624704
prod2.__sga_target=423624704
prod1.__shared_io_pool_size=0
prod2.__shared_io_pool_size=0
prod1.__shared_pool_size=188743680
prod2.__shared_pool_size=188743680
prod1.__streams_pool_size=0
prod2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+ASM_DATA/prod/controlfile/current.256.853445481','+ASM_RCY/prod/controlfile/current.256.8
53445485'
*.db_block_size=8192
*.db_create_file_dest='+ASM_DATA'
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest='+ASM_RCY'
*.db_recovery_file_dest_size=6005194752
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
prod1.instance_number=1
prod2.instance_number=2
*.log_archive_dest_1='LOCATION=+ASM_RCY'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=140509184
*.processes=150
*.remote_listener='scan-ip:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=421527552
prod2.thread=2
prod1.thread=1
prod2.undo_tablespace='UNDOTBS2'
prod1.undo_tablespace='UNDOTBS1'
查看源库文件位置:
SQL> select file#,name from v$datafile;
FILE# NAME
1 +ASM_DATA/prod/datafile/system.259.853445501
2 +ASM_DATA/prod/datafile/sysaux.260.853445535
3 +ASM_DATA/prod/datafile/undotbs1.261.853445563
4 +ASM_DATA/prod/datafile/undotbs2.263.853445599
5 +ASM_DATA/prod/datafile/users.264.853445607
----------
--------------------------------------------------
SQL> select file#,name from v$tempfile;
FILE# NAME
1 +ASM_DATA/prod/tempfile/temp.262.853445571
----------
--------------------------------------------------
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+ASM_DATA/prod/onlinelog/group_1.257.853445487
+ASM_RCY/prod/onlinelog/group_1.257.853445491
+ASM_DATA/prod/onlinelog/group_2.258.853445493
+ASM_RCY/prod/onlinelog/group_2.258.853445497
+ASM_DATA/prod/onlinelog/group_3.265.853449629
+ASM_RCY/prod/onlinelog/group_3.259.853449633
+ASM_DATA/prod/onlinelog/group_4.266.853449637
+ASM_RCY/prod/onlinelog/group_4.260.853449641
改后目标参数文件:
[oracle@rh2 dbs]$ vi initsingle.ora
*.audit_file_dest='/u01/app/oracle/admin/single/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='/soft/single/controlfile/control01.ctl','/soft/single/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/soft/single/oradata/'
*.db_domain=''
*.db_name='single'
*.db_recovery_file_dest='/soft/single/flash_recover_area/'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=/soft/single/arch/'
*.log_archive_format='%t_%s_%r.dbf
'*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.log_file_name_convert=('+ASM_DATA/prod/onlinelog','/soft/single/oradata')
*.log_file_name_convert=('+ASM_RCY/prod/onlinelog','/soft/single/oradata')
*.db_file_name_convert=('+ASM_DATA/prod/datafile','/soft/single/oradata')
*.db_file_name_convert=('+ASM_DATA/prod/tempfile','/soft/single/oradata')
因为RAC的目录结构和我们的单实例不一样,所以我们这里进行转换一下。 这里面要根据自己的情况来决定。
5. 用SCP将源库的备份copy到目标库
6. 将目标库启动到nomount 状态,并进行duplicate
6.1 启动目标库到nomount状态
[oracle@rh2 dbs]$export ORACLE_SID=single
[oracle@rh2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 30 10:21:42 2014Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomountORACLE instance started.
Total System Global Area 242208768 bytesFixed Size 2227176 bytesVariable Size 184550424 bytesDatabase Buffers 50331648 bytesRedo Buffers 5099520 bytes
6.2 进行duplicate
[oracle@rh2 dbs]$ rman target sys/oracle@sourceprod auxiliary /
Recovery Manager: Release 11.2.0.3.0 -Production on Wed Jul 30 10:23:29 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: PROD (DBID=265283811)connected to auxiliary database: SINGLE (not mounted)
RMAN> duplicate target database to single;
一条命令,剩下的活RMAN全干了
四. 目标库的其他扫尾操作
4.1 清除多余的undo文件查看UNDO 信息:
SQL> select name from v$tablespace where name like 'UNDO%';NAME
NAME -------------------------------------------------------------------------
UNDOTBS1
UNDOTBS2
因为我们使用的是UNDOTBS1,在pfile里设置的,所以把UNDOTBS2删除掉。
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
-----------------------------------------------------------------------------undo_tablespace string UNDOTBS1
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 OPEN PUBLIC 2 CLOSED PRIVATE
SQL> select group#,thread#,archived,status from v$log;
GROUP# THREAD# ARC STATUS
---------------------------------------
1 1 YES UNUSED
2 1 NO CURRENT
3 2 NO CURRENT
4 2 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
---------------------------------------
1 1 YES INACTIVE
2 1 NO CURRENT
现在就剩2个了。 一般的单实例是3个online redo。 我们再添加一组。
SQL> alter database add logfile group 3 ('/soft/single/oradata/redo03.log') size 50m;
Database altered.
SQL> select group#,thread#,archived,status from v$log; GROUP# THREAD# ARC STATUS
---------------------------------------
1 1 YES INACTIVE
2 1 NO CURRENT
3 1 YES UNUSED
4.3 重建临时表空间,并删除原来的数据文件SQL> select file#,name from v$tempfile; FILE# NAME
-------------------------------------------------------------------------------
1 /soft/single/oradata/temp01.dbf