RAC迁移到单节点

从RAC迁移到单节点可以用备份导出导入的方法，也可以用RMAN，下面实验下RMAN的做法

实验数据库RAC无闪回区，10.2.0版本，迁移到rac2所在主机的orcl实例

1.备份数据库，备份归档

run{
sql 'alter system archive log current';
backup database format '+backup/full_%T_%s_%p'
include current controlfile;
}

run{
allocate channel t1 type disk  connect 'sys/fishcat@rac1';
allocate channel t2 type disk  connect 'sys/fishcat@rac2';
BACKUP
FORMAT '+backup/arch_%T_%s_%p'
SKIP INACCESSIBLE
ARCHIVELOG ALL DELETE INPUT;
release channel t1;
release channel t2;
}

2.复制dump目录

[oracle@node2 admin]$ cp -r rac orcl

3.修改pfile文件
在源数据库上
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

内容如下：
[oracle@node1 tmp]$ cat pfile.ora.bak
rac2.__db_cache_size=83886080
rac1.__db_cache_size=71303168
rac2.__java_pool_size=4194304
rac1.__java_pool_size=4194304
rac2.__large_pool_size=4194304
rac1.__large_pool_size=4194304
rac2.__shared_pool_size=71303168
rac1.__shared_pool_size=83886080
rac2.__streams_pool_size=0
rac1.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/rac/adump'
*.background_dump_dest='/home/oracle/admin/rac/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.1.0'
*.control_files='+G1/rac/controlfile/current.256.804551605'
*.core_dump_dest='/home/oracle/admin/rac/cdump'
*.db_block_size=8192
*.db_create_file_dest='+G1'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='rac'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac1.instance_number=1
rac2.instance_number=2
*.job_queue_processes=10
rac2.log_archive_dest_1='location=/archive/rac2'
rac1.log_archive_dest_1='location=/archive/rac1'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_listener='LISTENERS_RAC'
*.remote_login_passwordfile='exclusive'
*.sga_target=167772160
rac2.thread=2
rac1.thread=1
*.undo_management='AUTO'
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/oracle/admin/rac/udump'

修改后如下：
*.audit_file_dest='/home/oracle/admin/orcl/adump'
*.background_dump_dest='/home/oracle/admin/orcl/bdump'
*.cluster_database=false
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/oradata/orcl/control01.ctl',/home/oracle/oradata/orcl/control02.ctl',/home/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/home/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/oradata/orcl'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.job_queue_processes=10
*.log_archive_dest_1='location=/archive2'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/orcl/udump'
*.log_file_name_convert=('+G1/rac/onlinelog','/home/oracle/oradata/orcl')
*.db_file_name_convert=('+G1/rac/datafile','/home/oracle/oradata/orcl')
*.db_file_name_convert=('+G1/rac/tempfile','/home/oracle/oradata/orcl')

------------------------------------------------------------
*.log_file_name_convert=('+G1/rac/onlinelog','/home/oracle/oradata/orcl')
*.db_file_name_convert=('+G1/rac/datafile','/home/oracle/oradata/orcl')
*.db_file_name_convert=('+G1/rac/tempfile','/home/oracle/oradata/orcl')

上面这三行是转换logfile，dbfile，tempfile路径，如果一样则不必修改

4.制作RMAN脚本
根据语句
select 'set newname for datafile '||file_id||' to "/home/oracle/oradata/orcl/'||substr(file_name,18)||'";' from DBA_data_files;
select 'set newname for tempfile '||file_id||' to "/home/oracle/oradata/orcl/'||substr(file_name,18)||'";' from dba_temp_files;
select 'group '||a.group#||' (''/home/oracle/oradata/orcl/'||substr(member,19)||''')size '||bytes||' reuse,'
from v$logfile a,v$log b
where a.GROUP#=b.GROUP#;
可以得出
--------------更改数据文件路径的语句
set newname for datafile 1 to "/home/oracle/oradata/orcl/system.259.804551621";
set newname for datafile 2 to "/home/oracle/oradata/orcl/undotbs1.260.804551649";
set newname for datafile 3 to "/home/oracle/oradata/orcl/sysaux.261.804551661";
set newname for datafile 4 to "/home/oracle/oradata/orcl/undotbs2.263.804551689";
set newname for datafile 5 to "/home/oracle/oradata/orcl/users.264.804551699";

----------------更改临时文件路径的语句
set newname for tempfile 1 to "/home/oracle/oradata/orcl/temp.262.804551671";

----------------更改控制文件的语句

group 1 ('/home/oracle/oradata/orcl/group_1.257.804551611')size 52428800 reuse,
group 2 ('/home/oracle/oradata/orcl/group_2.258.804551615')size 52428800 reuse,
group 3 ('/home/oracle/oradata/orcl/group_3.265.804553343')size 52428800 reuse,
group 4 ('/home/oracle/oradata/orcl/group_4.266.804553347')size 52428800 reuse,

根据以上语句得到如下的Rman脚本

run{
set newname for datafile 1 to "/home/oracle/oradata/orcl/system.259.804551621";
set newname for datafile 2 to "/home/oracle/oradata/orcl/undotbs1.260.804551649";
set newname for datafile 3 to "/home/oracle/oradata/orcl/sysaux.261.804551661";
set newname for datafile 4 to "/home/oracle/oradata/orcl/undotbs2.263.804551689";
set newname for datafile 5 to "/home/oracle/oradata/orcl/users.264.804551699";
set newname for tempfile 1 to "/home/oracle/oradata/orcl/temp.262.804551671";
duplicate target database to orcl nofilenamecheck logfile
group 1 ('/home/oracle/oradata/orcl/group_1.257.804551611')size 52428800 reuse,
group 2 ('/home/oracle/oradata/orcl/group_2.258.804551615')size 52428800 reuse,
group 3 ('/home/oracle/oradata/orcl/group_3.265.804553343')size 52428800 reuse,
group 4 ('/home/oracle/oradata/orcl/group_4.266.804553347')size 52428800 reuse;
}

5.切换到orcl环境下export ORACLE_SID=orcl
根据pfile文件启动到nomount状态，然后exit
SQL> startup nomount pfile='$ORACLE_HOME/dbs/pfile.ora'
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
SQL> exit

6.执行RMAN脚本

[oracle@node2 orcl]$ rman target 'sys/fishcat@rac1' auxiliary /

RMAN> run{
2> set newname for datafile 1 to "/home/oracle/oradata/orcl/system.259.804551621";
3> set newname for datafile 2 to "/home/oracle/oradata/orcl/undotbs1.260.804551649";
4> set newname for datafile 3 to "/home/oracle/oradata/orcl/sysaux.261.804551661";
5> set newname for datafile 4 to "/home/oracle/oradata/orcl/undotbs2.263.804551689";
6> set newname for datafile 5 to "/home/oracle/oradata/orcl/users.264.804551699";
7> set newname for tempfile 1 to "/home/oracle/oradata/orcl/temp.262.804551671";
8> duplicate target database to orcl nofilenamecheck logfile
9> group 1 ('/home/oracle/oradata/orcl/group_1.257.804551611')size 52428800 reuse,
10> group 2 ('/home/oracle/oradata/orcl/group_2.258.804551615')size 52428800 reuse,
11> group 3 ('/home/oracle/oradata/orcl/group_3.265.804553343')size 52428800 reuse,
12> group 4 ('/home/oracle/oradata/orcl/group_4.266.804553347')size 52428800 reuse;
13> }

--------------脚本执行结果如下；
executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 13-JAN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=153 devtype=DISK

contents of Memory Script:
{
   set until scn  318371;
   set newname for datafile  1 to
 "/home/oracle/oradata/orcl/system.259.804551621";
   set newname for datafile  2 to
 "/home/oracle/oradata/orcl/undotbs1.260.804551649";
   set newname for datafile  3 to
 "/home/oracle/oradata/orcl/sysaux.261.804551661";
   set newname for datafile  4 to
 "/home/oracle/oradata/orcl/undotbs2.263.804551689";
   set newname for datafile  5 to
 "/home/oracle/oradata/orcl/users.264.804551699";
   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 13-JAN-13
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 00001 to /home/oracle/oradata/orcl/system.259.804551621
restoring datafile 00002 to /home/oracle/oradata/orcl/undotbs1.260.804551649
restoring datafile 00003 to /home/oracle/oradata/orcl/sysaux.261.804551661
restoring datafile 00004 to /home/oracle/oradata/orcl/undotbs2.263.804551689
restoring datafile 00005 to /home/oracle/oradata/orcl/users.264.804551699
channel ORA_AUX_DISK_1: reading from backup piece +BACKUP/full_20130113_13_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+BACKUP/full_20130113_13_1 tag=TAG20130113T161131
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:27
Finished restore at 13-JAN-13
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/home/oracle/oradata/orcl/group_1.257.804551611' ) SIZE 52428800    REUSE,
  GROUP  2 ( '/home/oracle/oradata/orcl/group_2.258.804551615' ) SIZE 52428800    REUSE,
  GROUP  3 ( '/home/oracle/oradata/orcl/group_3.265.804553343' ) SIZE 52428800    REUSE,
  GROUP  4 ( '/home/oracle/oradata/orcl/group_4.266.804553347' ) SIZE 52428800    REUSE
 DATAFILE
  '/home/oracle/oradata/orcl/system.259.804551621'
 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=804617372 filename=/home/oracle/oradata/orcl/undotbs1.260.804551649
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=804617372 filename=/home/oracle/oradata/orcl/sysaux.261.804551661
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=804617372 filename=/home/oracle/oradata/orcl/undotbs2.263.804551689
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=804617373 filename=/home/oracle/oradata/orcl/users.264.804551699

contents of Memory Script:
{
   set until scn  318371;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 13-JAN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=153 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
archive log thread=2 sequence=17
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=2 sequence=18
channel ORA_AUX_DISK_1: reading from backup piece +BACKUP/arch_20130113_18_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+BACKUP/arch_20130113_18_1 tag=TAG20130113T161419
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
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=35
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=36
channel ORA_AUX_DISK_1: reading from backup piece +BACKUP/arch_20130113_19_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+BACKUP/arch_20130113_19_1 tag=TAG20130113T161419
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/archive2/1_35_804551593.dbf thread=1 sequence=35
archive log filename=/archive2/2_17_804551593.dbf thread=2 sequence=17
channel clone_default: deleting archive log(s)
archive log filename=/archive2/1_35_804551593.dbf recid=3 stamp=804617377
archive log filename=/archive2/1_36_804551593.dbf thread=1 sequence=36
channel clone_default: deleting archive log(s)
archive log filename=/archive2/2_17_804551593.dbf recid=2 stamp=804617376
archive log filename=/archive2/2_18_804551593.dbf thread=2 sequence=18
channel clone_default: deleting archive log(s)
archive log filename=/archive2/1_36_804551593.dbf recid=4 stamp=804617377
channel clone_default: deleting archive log(s)
archive log filename=/archive2/2_18_804551593.dbf recid=1 stamp=804617376
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JAN-13

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1218316 bytes
Variable Size                 62916852 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/home/oracle/oradata/orcl/group_1.257.804551611' ) SIZE 52428800    REUSE,
  GROUP  2 ( '/home/oracle/oradata/orcl/group_2.258.804551615' ) SIZE 52428800    REUSE,
  GROUP  3 ( '/home/oracle/oradata/orcl/group_3.265.804553343' ) SIZE 52428800    REUSE,
  GROUP  4 ( '/home/oracle/oradata/orcl/group_4.266.804553347' ) SIZE 52428800    REUSE
 DATAFILE
  '/home/oracle/oradata/orcl/system.259.804551621'
 CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/home/oracle/oradata/orcl/temp.262.804551671";
   switch clone tempfile all;
   catalog clone datafilecopy  "/home/oracle/oradata/orcl/undotbs1.260.804551649";
   catalog clone datafilecopy  "/home/oracle/oradata/orcl/sysaux.261.804551661";
   catalog clone datafilecopy  "/home/oracle/oradata/orcl/undotbs2.263.804551689";
   catalog clone datafilecopy  "/home/oracle/oradata/orcl/users.264.804551699";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oradata/orcl/temp.262.804551671 in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oradata/orcl/undotbs1.260.804551649 recid=1 stamp=804617403

cataloged datafile copy
datafile copy filename=/home/oracle/oradata/orcl/sysaux.261.804551661 recid=2 stamp=804617403

cataloged datafile copy
datafile copy filename=/home/oracle/oradata/orcl/undotbs2.263.804551689 recid=3 stamp=804617403

cataloged datafile copy
datafile copy filename=/home/oracle/oradata/orcl/users.264.804551699 recid=4 stamp=804617404

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=804617403 filename=/home/oracle/oradata/orcl/undotbs1.260.804551649
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=804617403 filename=/home/oracle/oradata/orcl/sysaux.261.804551661
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=804617403 filename=/home/oracle/oradata/orcl/undotbs2.263.804551689
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=804617404 filename=/home/oracle/oradata/orcl/users.264.804551699

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 13-JAN-13