♣
题目部分
在Oracle中,将RAC备份集恢复到单实例数据库的步骤有哪些?
♣
答案部分
将RAC备份集恢复到单实例数据库的过程基本上就是先将备份集恢复为RAC数据库,然后再将数据库转换为单实例的数据库。
数据库的备份可以使用如下的脚本:
1run 2{ 3 allocate channel c1 type disk; 4 allocate channel c2 type disk; 5 backup database format '/home/oracle/rman_back/full_%n_%T_%t_%s_%p.bak'; 6 sql 'alter system archive log current'; 7 backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input; 8 backup current controlfile format '/home/oracle/rman_back/ctl_%d_%T_%s_%p.bak'; 9 release channel c1; 10 release channel c2; 11} 12
将RAC备份集恢复到单实例数据库可以分为恢复为ASM存储的单实例和FS存储的单实例,其处理过程分别不同。
1、rac恢复到ASM中
1ORACLE_SID=lhrdbasm 2startup nomount; 3set dbid 2136828548 4restore spfile to '/tmp/aabb.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak'; 5 6strings /tmp/aabb.ora 7 8*.audit_file_dest='/u01/app/oracle/admin/lhrdbasm/adump' 9*.audit_trail='db' 10*.compatible='11.2.0.0.0' 11*.control_files='+FRA','+FRA' 12*.db_block_size=8192 13*.db_create_file_dest='+FRA' 14*.db_domain='' 15*.db_name='lhrrac1' 16*.db_recovery_file_dest='+FRA' 17*.db_recovery_file_dest_size=20558159872 18*.diagnostic_dest='/u01/app/oracle' 19*.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrdbasmXDB)' 20*.memory_target=630194176 21*.open_cursors=300 22*.processes=150 23*.remote_login_passwordfile='exclusive' 24 25 26mkdir -p /u01/app/oracle/admin/lhrdbasm/adump 27create spfile from pfile='/tmp/b.txt'; 28startup nomount force; 29restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak'; 30alter database mount; 31 32set line 9999 pagesize 9999 33col FILE_NAME format a60 34select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile 35union all 36select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile 37union all 38select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile 39union all 40select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile; 41 42 43set pagesize 200 linesize 200 44select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a 45union all 46select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a 47union all 48SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a; 49 50 51set pagesize 200 linesize 200 52select 'set newname for datafile ' || a.FILE# || ' to "+FRA";' from v$datafile a 53union all 54select 'set newname for tempfile ' || a.FILE# || ' to "+FRA";' from v$tempfile a 55union all 56SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''+FRA'''' ";' FROM v$logfile a; 57 58 59run { 60 ALLOCATE CHANNEL c1 DEVICE TYPE DISK; 61 set newname for datafile 1 to "+FRA"; 62 set newname for datafile 2 to "+FRA"; 63 set newname for datafile 3 to "+FRA"; 64 set newname for datafile 4 to "+FRA"; 65 set newname for datafile 5 to "+FRA"; 66 set newname for datafile 6 to "+FRA"; 67 set newname for tempfile 1 to "+FRA"; 68 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''+FRA'' "; 69 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''+FRA'' "; 70 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''+FRA'' "; 71 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''+FRA'' "; 72 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''+FRA'' "; 73 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''+FRA'' "; 74 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''+FRA'' "; 75 SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''+FRA'' "; 76 restore database; 77 SWITCH DATAFILE ALL; 78 SWITCH TEMPFILE ALL; 79 release channel c1; 80 } 81 82 83list backupset of archivelog all; 84RUN 85{ 86set until sequence 10 thread 1; 87set until sequence 7 thread 2; 88recover database; 89} 90 91alter database open resetlogs; 92 93 94col instance format a20 95select thread#,instance,status,enabled from v$thread; 96alter database disable thread 2 ; 97alter database drop logfile group 3 ; 98alter database drop logfile group 4 ; 99 100 101drop tablespace undotbs2 including contents and datafiles; 102
2、RAC恢复到FS中
1ORACLE_SID=lhrfs 2startup nomount; 3set dbid 2136828548 4restore spfile to '/tmp/aabbcc.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak'; 5 6*.audit_file_dest='/u01/app/oracle/admin/lhrfs/adump' 7*.audit_trail='db' 8*.compatible='11.2.0.0.0' 9*.control_files='/u01/app/oracle/oradata/lhrfs/control01.dbf','/u01/app/oracle/oradata/lhrfs/control02.dbf' 10*.db_block_size=8192 11*.db_create_file_dest='/u01/app/oracle/oradata/lhrfs' 12*.db_domain='' 13*.db_name='lhrrac1' 14*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' 15*.db_recovery_file_dest_size=4558159872 16*.diagnostic_dest='/u01/app/oracle' 17*.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrfsXDB)' 18*.memory_target=630194176 19*.open_cursors=300 20*.processes=150 21*.remote_login_passwordfile='exclusive' 22 23 24 25create spfile from pfile='/tmp/b.txt'; 26startup nomount force; 27restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak'; 28alter database mount; 29 30set line 9999 pagesize 9999 31col FILE_NAME format a60 32select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile 33union all 34select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile 35union all 36select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile 37union all 38select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile; 39 40 41set pagesize 200 linesize 200 42select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a 43union all 44select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a 45union all 46SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a; 47 48 49set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf"; 50set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf"; 51set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf"; 52set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf"; 53set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf"; 54set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf"; 55set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf"; 56SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' "; 57SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' "; 58SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' "; 59SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' "; 60SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' "; 61SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' "; 62SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' "; 63SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' "; 64 65 66 67run { 68 ALLOCATE CHANNEL c1 DEVICE TYPE DISK; 69set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf"; 70set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf"; 71set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf"; 72set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf"; 73set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf"; 74set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf"; 75set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf"; 76SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' "; 77SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' "; 78SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' "; 79SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' "; 80SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' "; 81SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' "; 82SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' "; 83SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' "; 84 restore database; 85 SWITCH DATAFILE ALL; 86 SWITCH TEMPFILE ALL; 87 release channel c1; 88 } 89 90 91list backupset of archivelog all; 92RUN 93{ 94set until sequence 10 thread 1; 95set until sequence 7 thread 2; 96recover database; 97} 98 99alter database open resetlogs; 100 101 102col instance format a20 103select thread#,instance,status,enabled from v$thread; 104alter database disable thread 2 ; 105alter database drop logfile group 3 ; 106alter database drop logfile group 4 ; 107 108 109drop tablespace undotbs2 including contents and datafiles; 110
& 说明:
有关RAC备份集恢复到单实例数据库的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-1682255/。
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me:小麦苗
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
● QQ:646634621 QQ群:618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处,还望各位朋友批评指正,共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。