很久之前就想做一个实际的rac数据库迁移到单实例上，最近由于接触rac的dg相对较多，就动手实际操作一下关于rac数据库迁移到单实例。
1 首先备份所有的数据文件、参数文件、控制文件
RMAN> backup database include current controlfile format '/home/oracle/backup_%U'
2> plus archivelog delete all input format '/home/oracle/arch_%U';
2 获取rac的pfile文件
SQL> create pfile='/home/oracle/initxiaoyu.ora' from spfile;
File created.
修改部分集群参数，如下
*.db_domain=''
*.db_name='xiaoyu'
*.db_recovery_file_dest='/db/oracle/product/flash_recovery_area/xiaoyu'
*.db_recovery_file_dest_size=21474836480
*.db_unique_name='xiaoyu'
*.control_files='/db/oracle/product/oradata/xiaoyu_single/control01.ctl',''/db/oracle/product/oradata/xiaoyu_single/control02.ctl'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=121634816
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=364904448
*.undo_management='AUTO'
*.user_dump_dest='/db/oracle/product/10.2.0/db_1/admin/xiaoyu/udump'
*.undo_tablespace='undotbs1'
*.background_dump_dest='/db/oracle/product/10.2.0/db_1/admin/xiaoyu/bdump'
利用spfile参数文件启动数据库到nomount状态
SQL> startup nomount;
ORACLE instance started.
从备份集恢复controlfile文件，总是出现了ORA-07202: sltln: invalid parameter to sltln这个错误，
小鱼纠结半天终于发现是上面的control_files参数拼写错误引起的，多了一个单引号，平时指定路径时要特别注意了，认真 细心！
RMAN> restore controlfile from '/home/oracle/backup_0go339vu_1_1';
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2013 10:11:16
ORA-19870: error reading backup piece /home/oracle/backup_0go339vu_1_1
ORA-07202: sltln: invalid parameter to sltln.
恢复controlfile并启动数据库到mount
RMAN> restore controlfile from '/home/oracle/backup_0go339vu_1_1';
RMAN> alter database mount;
恢复数据文件
Catalog备份信息到controlfile然后restore datafile all并修改controlfile中datafile的路径
RMAN> run{
2> set newname for datafile 1 to '/db/oracle/product/oradata/xiaoyu_single/system01.dbf';
3> set newname for datafile 2 to '/db/oracle/product/oradata/xiaoyu_single/undotbs1.dbf';
4> set newname for datafile 3 to '/db/oracle/product/oradata/xiaoyu_single/sysaux01.dbf';
5> set newname for datafile 4 to '/db/oracle/product/oradata/xiaoyu_single/undotbs2.dbf';
6> set newname for datafile 5 to '/db/oracle/product/oradata/xiaoyu_single/users01.dbf';
7> set newname for datafile 6 to '/db/oracle/product/oradata/xiaoyu_single/xiaoyu01.dbf';
8> restore database;
9> switch datafile all;
10> }
查看下logfile的位置并进行修改。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/xiaoyu/onlinelog/group_1.271.808287787
+DATA/xiaoyu/onlinelog/group_1.270.808287807
+DATA/xiaoyu/onlinelog/group_2.273.808287819
+DATA/xiaoyu/onlinelog/group_2.272.808287829
+DATA/xiaoyu/onlinelog/group_3.281.808287837
+DATA/xiaoyu/onlinelog/group_3.280.808287843
+DATA/xiaoyu/onlinelog/group_4.283.808287847
+DATA/xiaoyu/onlinelog/group_4.282.808287853
SQL> alter database rename file '+DATA/xiaoyu/onlinelog/group_1.271.808287787' to '/db/oracle/product/oradata/xiaoyu_single/redo01.log';
Database altered
。。。。，
.
SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------
/db/oracle/product/oradata/xiaoyu_single/redo01.log
/db/oracle/product/oradata/xiaoyu_single/redo01_2.log
/db/oracle/product/oradata/xiaoyu_single/redo02.log
/db/oracle/product/oradata/xiaoyu_single/redo02_2log
/db/oracle/product/oradata/xiaoyu_single/redo03.log
/db/oracle/product/oradata/xiaoyu_single/redo03_2.log
/db/oracle/product/oradata/xiaoyu_single/redo04.log
/db/oracle/product/oradata/xiaoyu_single/redo04_2.log
确定不完全恢复的终点。
查看新的单实例上的最后的归档日志分别是thread 1 seq 6和thread 2 seq 5归档日志。
由于生产环境中我们没有关库，此时其实主库rac还是会产生日志的，而此时关掉必要的应用，保证不再产生新的生产数据，
而后两个实例强制做两个switchover logfile并归档当前日志，这样是为了保证迁移到单实例上避免数据丢失，因为rac不完全恢复并不能用到最后一组归档日志。
RMAN> list backup of archivelog all
……
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16 325.50K DISK 00:00:00 28-FEB-13
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20130228T081046
Piece Name: /home/oracle/arch_0ho33a07_1_1
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1017152 28-FEB-13 1017690 28-FEB-13
2 5 1017155 28-FEB-13 1017687 28-FEB-13
Rac的下查看新产生的归档日志有以下六组。
136 1 7 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_1_seq_7.326.808570223
140 1 8 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_1_seq_8.318.808570251
141 1 9 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_1_seq_9.316.808570255
137 2 6 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_2_seq_6.324.808570235
138 2 7 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_2_seq_7.323.808570241
139 2 8 A 28-FEB-13 +DATA/xiaoyu/archivelog/2013_02_28/thread_2_seq_8.321.808570249
由于该归档日志在asm上不能直接scp过来，可以先用rman备份然后scp备份集
RMAN> backup archivelog from scn 1017690 format '/home/oracle/new_arch'
2> ;
Starting backup at 28-FEB-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 instance=xiaoyu1 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=136 stamp=808570224
input archive log thread=1 sequence=8 recid=140 stamp=808570251
input archive log thread=1 sequence=9 recid=141 stamp=808570254
input archive log thread=1 sequence=10 recid=142 stamp=808570849
input archive log thread=2 sequence=6 recid=137 stamp=808570236
input archive log thread=2 sequence=7 recid=138 stamp=808570241
input archive log thread=2 sequence=8 recid=139 stamp=808570248
input archive log thread=2 sequence=9 recid=143 stamp=808570852
channel ORA_DISK_1: starting piece 1 at 28-FEB-13
channel ORA_DISK_1: finished piece 1 at 28-FEB-13
piece handle=/home/oracle/new_arch tag=TAG20130228T110100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 28-FEB-13
注册全部的归档日志备份集到单实例的controlfile中
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
16 325.50K DISK 00:00:00 28-FEB-13
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20130228T081046
Piece Name: /home/oracle/arch_0ho33a07_1_1
List of Archived Logs in backup set 16
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 1017152 28-FEB-13 1017690 28-FEB-13
2 5 1017155 28-FEB-13 1017687 28-FEB-13
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18 19.32M DISK 00:00:00 28-FEB-13
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20130228T110100
Piece Name: /home/oracle/new_arch
List of Archived Logs in backup set 18
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 1017690 28-FEB-13 1033377 28-FEB-13
1 8 1033377 28-FEB-13 1033450 28-FEB-13
1 9 1033450 28-FEB-13 1033454 28-FEB-13
1 10 1033454 28-FEB-13 1034768 28-FEB-13
2 6 1017687 28-FEB-13 1033418 28-FEB-13
2 7 1033418 28-FEB-13 1033434 28-FEB-13
2 8 1033434 28-FEB-13 1033448 28-FEB-13
2 9 1033448 28-FEB-13 1034771 28-FEB-13
确定恢复终点1034768，虽然归档日志传递给我们的最大的scn是thread 2 seq 9，不过由于实例间的scn是不连续的，所以我们只能利用到倒数第二个归档日志。
RMAN> run{
2> set until scn 1034768;
3> recover database;
4> }
。。。
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-FEB-13
此时恢复到最后的thread 1 seq10日志的最大scn1034768是可以的。
如果我们恢复到 thread 2 seq 9的最大scn1034771 则此时oracle会提示还需要更多的日志也就是还没产生的归档日志thread 1 seq 11。
RMAN> run{
2> set until scn 1034771;
3> recover database;
4> }
executing command: SET until clause
Starting recover at 28-FEB-13
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/28/2013 11:08:52
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11 lowscn 1034768
此时resetlogs打开数据库
RMAN> alter database open resetlogs;
database opened
数据库现在已经可以提供服务了，而后我们需要做一些基本的善后工作，当然不处理也不影响数据库的使用。
禁用掉thread 2
SQL> alter database disable thread 2;
Database altered.
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------------
1 OPEN
2 CLOSED
删除不适用的undo tablespace
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
删除没有使用的logfile group
SQL> select thread#,group# from v$log;
THREAD# GROUP#
---------- ----------
1 1
1 2
2 3
2 4
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance xiaoyu2 (thread 2) needs to be archived
ORA-00312: online log 3 thread 2:
'/db/oracle/product/oradata/xiaoyu_single/redo03.log'
ORA-00312: online log 3 thread 2:
'/db/oracle/product/oradata/xiaoyu_single/redo03_2.log'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
删除原来的tempfile，增加新的tempfile。
SQL> alter tablespace temp add tempfile '/db/oracle/product/oradata/xiaoyu_single/temp01.dbf' size 50m;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '+DATA/xiaoyu/tempfile/temp.277.805001779'；
Tablespace altered.
此时这个迁移已经完成了