single instance to 3 node rac
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: single instance to 3 node rac
最近做了很多套系统的迁移，大小500G~3T不等，方法差不多，有单机、dataguard、rac。
本来是打算写一篇非常详细的总结文档，但是涉及到不少客户信息，加上自己有点懒，所以
这里就来一篇超级简洁版。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
###### Single instance to rac（3 node） 1. create pfile 此时不用添加rac相关的参数，直接从原库create一份即可。 create pfile='/tmp/pfile.ora' from spfile; 恢复到asm需要修改pfile中control_files参数。 2. Restore controlfile; rman target / startup nomount pfile='/tmp/pfile.ora'; set dbid=2820248277; restore controlfile from '/backup/backup_control.bak'; |
3. Restore datafiles
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
---restore_database.sh rman target / <<EOF RUN { ALLOCATE CHANNEL ch00 TYPE disk; ALLOCATE CHANNEL ch01 TYPE disk; ALLOCATE CHANNEL ch02 TYPE disk; ALLOCATE CHANNEL ch03 TYPE disk; set newname for datafile 1 to '+data/ucp/datafile/SYSTEM.dbf'; set newname for datafile 2 to '+data/ucp/datafile/UNDOTBS1.dbf'; set newname for datafile 3 to '+data/ucp/datafile/SYSAUX.dbf'; set newname for datafile 4 to '+data/ucp/datafile/UCP_DATA_01.dbf'; set newname for datafile 5 to '+data/ucp/datafile/UCP_INDEX_01.dbf'; set newname for datafile 6 to '+data/ucp/datafile/UNDOTBS2.dbf'; set newname for datafile 7 to '+data/ucp/datafile/WEB_DATA_01.dbf'; set newname for datafile 8 to '+data/ucp/datafile/WEB_INDEX_01.dbf'; set newname for datafile 9 to '+data/ucp/datafile/UCP_DATA_02.dbf'; set newname for datafile 10 to '+data/ucp/datafile/UCP_DATA_03.dbf'; set newname for datafile 11 to '+data/ucp/datafile/UCP_DATA_04.dbf'; set newname for datafile 12 to '+data/ucp/datafile/UCP_DATA_05.dbf'; set newname for datafile 13 to '+data/ucp/datafile/UCP_DATA_06.dbf'; set newname for datafile 14 to '+data/ucp/datafile/UCP_DATA_07.dbf'; set newname for datafile 15 to '+data/ucp/datafile/UCP_DATA_08.dbf'; set newname for datafile 16 to '+data/ucp/datafile/UCP_INDEX_02.dbf'; set newname for datafile 17 to '+data/ucp/datafile/UCP_INDEX_03.dbf'; set newname for datafile 18 to '+data/ucp/datafile/UCP_INDEX_04.dbf'; set newname for datafile 19 to '+data/ucp/datafile/UCP_INDEX_05.dbf'; set newname for datafile 20 to '+data/ucp/datafile/UCP_INDEX_06.dbf'; set newname for datafile 21 to '+data/ucp/datafile/WEB_DATA_02.dbf'; set newname for datafile 22 to '+data/ucp/datafile/WEB_DATA_03.dbf'; set newname for datafile 23 to '+data/ucp/datafile/WEB_DATA_04.dbf'; set newname for datafile 24 to '+data/ucp/datafile/WEB_DATA_05.dbf'; set newname for datafile 25 to '+data/ucp/datafile/WEB_DATA_06.dbf'; set newname for datafile 26 to '+data/ucp/datafile/WEB_DATA_07.dbf'; set newname for datafile 27 to '+data/ucp/datafile/WEB_DATA_08.dbf'; set newname for datafile 28 to '+data/ucp/datafile/WEB_INDEX_02.dbf'; set newname for datafile 29 to '+data/ucp/datafile/WEB_INDEX_03.dbf'; set newname for datafile 30 to '+data/ucp/datafile/WEB_INDEX_04.dbf'; set newname for datafile 31 to '+data/ucp/datafile/WEB_INDEX_05.dbf'; set newname for datafile 32 to '+data/ucp/datafile/WEB_INDEX_06.dbf'; set newname for datafile 33 to '+data/ucp/datafile/UNDOTBS1_02.dbf'; set newname for datafile 34 to '+data/ucp/datafile/UNDOTBS2_02.dbf'; set newname for datafile 35 to '+data/ucp/datafile/UNDOTBS1_03.dbf'; set newname for datafile 36 to '+data/ucp/datafile/UNDOTBS2_03.dbf'; set newname for datafile 37 to '+data/ucp/datafile/UNDOTBS1_04.dbf'; set newname for datafile 38 to '+data/ucp/datafile/UNDOTBS2_04.dbf'; set newname for datafile 39 to '+data/ucp/datafile/PERFSTAT.dbf'; set newname for datafile 40 to '+data/ucp/datafile/UCP_DATA_09.dbf'; set newname for datafile 41 to '+data/ucp/datafile/UCP_DATA_10.dbf'; set newname for datafile 42 to '+data/ucp/datafile/UCP_INDEX_07.dbf'; set newname for datafile 43 to '+data/ucp/datafile/UCP_INDEX_08.dbf'; set newname for datafile 44 to '+data/ucp/datafile/UCP_DATA_11.dbf'; set newname for datafile 45 to '+data/ucp/datafile/UCP_DATA_12.dbf'; set newname for datafile 46 to '+data/ucp/datafile/UCP_DATA_13.dbf'; set newname for datafile 47 to '+data/ucp/datafile/UCP_INDEX_09.dbf'; set newname for datafile 48 to '+data/ucp/datafile/UCP_INDEX_10.dbf'; set newname for datafile 49 to '+data/ucp/datafile/UCP_INDEX_11.dbf'; set newname for datafile 50 to '+data/ucp/datafile/GGS_TBS_01.dbf'; set newname for datafile 51 to '+data/ucp/datafile/UCP_DATA_14.dbf'; set newname for datafile 52 to '+data/ucp/datafile/UCP_DATA_15.dbf'; set newname for datafile 53 to '+data/ucp/datafile/UCP_DATA_16.dbf'; set newname for datafile 54 to '+data/ucp/datafile/UCP_DATA_17.dbf'; set newname for datafile 55 to '+data/ucp/datafile/UNDOTBS1_05.dbf'; set newname for datafile 56 to '+data/ucp/datafile/UNDOTBS2_05.dbf'; set newname for datafile 57 to '+data/ucp/datafile/UNDOTBS1_06.dbf'; set newname for datafile 58 to '+data/ucp/datafile/UNDOTBS2_06.dbf'; set newname for datafile 59 to '+data/ucp/datafile/UCP_DATA_18.dbf'; set newname for datafile 60 to '+data/ucp/datafile/UCP_DATA_19.dbf'; set newname for datafile 61 to '+data/ucp/datafile/UCP_INDEX_12.dbf'; set newname for datafile 62 to '+data/ucp/datafile/UCP_INDEX_13.dbf'; set newname for datafile 63 to '+data/ucp/datafile/WEB_DATA_09.dbf'; set newname for datafile 64 to '+data/ucp/datafile/WEB_DATA_10.dbf'; set newname for datafile 65 to '+data/ucp/datafile/WEB_INDEX_07.dbf'; set newname for datafile 66 to '+data/ucp/datafile/WEB_INDEX_08.dbf'; set newname for tempfile 1 to '+data/ucp/tempfile/temp01.dbf'; set newname for tempfile 2 to '+data/ucp/tempfile/temp02.dbf'; restore database from tag='DBFULL'; switch datafile all; SWITCH TEMPFILE ALL; RELEASE CHANNEL ch00; RELEASE CHANNEL ch01; RELEASE CHANNEL ch02; RELEASE CHANNEL ch03; } EOF |
4. 拷贝原主库中archivelog到该主机(此时原生产机还未停止)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
11gR2中 如果你archivelog存放在FRA磁盘组中，可以直接使用asmcmd命令进行cp拷贝，如下： cp +fra/ucp/2012_10_08/thread_1_seq_323.291.796093125 /arch/thread_1_seq_323.291.796093125 cp +fra/ucp/2012_10_08/thread_1_seq_324.293.796094275 /arch/thread_1_seq_324.293.796094275 cp +fra/ucp/2012_10_08/thread_1_seq_325.294.796096147 /arch/thread_1_seq_325.294.796096147 cp +fra/ucp/2012_10_08/thread_1_seq_326.296.796100409 /arch/thread_1_seq_326.296.796100409 cp +fra/ucp/2012_10_08/thread_1_seq_327.297.796103579 /arch/thread_1_seq_327.297.796103579 cp +fra/ucp/2012_10_08/thread_1_seq_328.298.796106599 /arch/thread_1_seq_328.298.796106599 cp +fra/ucp/2012_10_08/thread_1_seq_329.299.796107497 /arch/thread_1_seq_329.299.796107497 cp +fra/ucp/2012_10_08/thread_1_seq_330.300.796108397 /arch/thread_1_seq_330.300.796108397 cp +fra/ucp/2012_10_08/thread_1_seq_331.301.796109067 /arch/thread_1_seq_331.301.796109067 5. Register archivelog alter database register physical logfile '/arch/thread_1_seq_322.290.796091183'; alter database register physical logfile '/arch/thread_1_seq_323.291.796093125'; alter database register physical logfile '/arch/thread_1_seq_325.294.796096147'; alter database register physical logfile '/arch/thread_1_seq_326.296.796100409'; alter database register physical logfile '/arch/thread_1_seq_327.297.796103579'; alter database register physical logfile '/arch/thread_1_seq_328.298.796106599'; alter database register physical logfile '/arch/thread_1_seq_329.299.796107497'; alter database register physical logfile '/arch/thread_1_seq_330.300.796108397'; alter database register physical logfile '/arch/thread_1_seq_331.301.796109067'; 当然你也可以使用更简单如下命令来注册archivelog： rman target / catalog start with '/arch'; 6. Recover database rman target / recover database; |
7. 第一次recover完成以后，由于我们业务系统还在运行，可能后面又会产生一些archivelog。
在该步骤停止业务系统，同时停库,停监听。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
SQLPLUS > alter system checkpoint; SQLPLUS > alter system switch logfile; SQLPLUS > alter system switch logfile; SQLPLUS > shutdown immediate lsnrctl stop 8. 追加archivelog，进行recover。 拷贝上面第7步骤原生产库产生的archivelog，到恢复主机上，进行注册。 这样我们再进行recover，最后open数据库，这样可以最大程度的减少停机时间，速度快的话 保持在10~30分钟是没有任何问题的。 9. rename redo logfiles alter database rename file '/dev/raw/raw7' to '+DATA/ucp/onlinelog/redo1_1'; alter database rename file '/dev/raw/raw12' to '+DATA/ucp/onlinelog/redo1_2'; alter database rename file '/dev/raw/raw8' to '+DATA/ucp/onlinelog/redo2_1'; alter database rename file '/dev/raw/raw13' to '+DATA/ucp/onlinelog/redo2_2'; alter database rename file '/dev/raw/raw9' to '+DATA/ucp/onlinelog/redo3_1'; alter database rename file '/dev/raw/raw14' to '+DATA/ucp/onlinelog/redo3_2'; alter database rename file '/dev/raw/raw10' to '+DATA/ucp/onlinelog/redo4_1'; alter database rename file '/dev/raw/raw15' to '+DATA/ucp/onlinelog/redo4_2'; alter database rename file '/dev/raw/raw11' to '+DATA/ucp/onlinelog/redo5_1'; alter database rename file '/dev/raw/raw16' to '+DATA/ucp/onlinelog/redo5_2'; alter database rename file '/dev/raw/raw17' to '+DATA/ucp/onlinelog/redo6_1'; alter database rename file '/dev/raw/raw22' to '+DATA/ucp/onlinelog/redo6_2' 10. Check checkpoint_change#是否一致 select max(checkpoint_change#) from v$datafile_header; select min(checkpoint_change#) from v$datafile_header; 11. open resetlogs打开数据库 SQLPLUS > alter database open resetlogs; 说明： 1) 使用backup controlfile进行恢复，正常情况下必须以open resetlogs方式打开，即使你把原库的redo拷贝过来进行恢复也一样; 2) 如果想noresetlogs open数据库，唯一的方式就是重建controlfile，以noresetlogs方式重建即可。 3) 上面的open resetlogs操作会重建redo，tempfile，所以你观察alert log中出现一些相关的错误，可以忽略掉。 |
12. Create rac redo logfile
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
alter database add logfile thread 2 group 6 size 512m; alter database add logfile thread 2 group 7 size 512m; alter database add logfile thread 2 group 8 size 512m; alter database add logfile thread 2 group 9 size 512m; alter database add logfile thread 2 group 10 size 512m; alter database add logfile thread 3 group 11 size 512m; alter database add logfile thread 3 group 12 size 512m; alter database add logfile thread 3 group 13 size 512m; alter database add logfile thread 3 group 14 size 512m; alter database add logfile thread 3 group 15 size 512m; 13. Create undo tablespace create undo tablespace undotbs2 datafile '+DATA' size 20480m autoextend off; create undo tablespace undotbs3 datafile '+DATA' size 20480m autoextend off; 14. create spfile SQLPLUS > create spfile from pfile='/tmp/pfile.ora'; SQLPLUS > shutdown immediate SQLPLUS > startup mount 15. Modify cluster parameters alter system set cluster_database=true scope=spfile; alter system set cluster_database_instances = 3 scope = spfile; alter system set instance_number=1 scope=spfile sid='UCP1'; alter system set instance_number=2 scope=spfile sid='UCP2'; alter system set instance_number=3 scope=spfile sid='UCP3'; 16. Restart instance export ORACLE_SID=UCP3 sqlplus "/as sysdba" shutdown immediate; startup |
17. 根据该rac实例spfile，创建pfile，用于启动其他2个rac 节点
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
sqlplus "/as sysdba" create pfile='/tmp/pfile.ora' from spfile; 18. 修改/tmp/pfile.ora中rac相关参数，以及创建相关的目录 19. 启动剩余的2个rac 实例 20. 修改undo_tablespace alter system set undo_tablespace=undotbs1 scope=both sid='UCP1'; alter system set undo_tablespace=undotbs2 scope=both sid='UCP2'; alter system set undo_tablespace=undotbs3 scope=both sid='UCP3'; 21. 注册数据库实例到crs中 srvctl add database -d UCP -o $ORACLE_HOME srvctl add instance -d UCP -i UCP1 -n node1 srvctl add instance -d UCP -i UCP2 -n node2 srvctl add instance -d UCP -i UCP3 -n node3 22. 配置listener.ora、tnsnames.ora 23. 启动listener,测试业务. |
说明：本来还是从10gR2 rac(raw)开始恢复，然后到10g单实例asm(该节点已经安装好11gR2 grid和db,但是
我tar了10g的 $ORACLE_HOME，使用10g创建了asm diskgroup),然后直接升级到11gR2,最后再从该11gR2
单节点恢复到另外一个3节点的11gR2 rac中。
由于第一次操作完成后，即升级完成之后，最后主机重启出现故障，无法启动，故作罢。最后可以花了
将近10个小时的时间直接从10g中exp导入到11gR2中，后面我接着从11gR2 恢复到3节点rac。
中间省略了很多的内容，不过大体上思路都差不多，没有什么复杂的东西，故不做过多描述。
2 Responses to “single instance to 3 node rac”
膜拜！
大牛就是牛！
Leave a Reply
You must be logged in to post a comment.