【DB筆試面試738】在Oracle中，將RAC備份集恢復到單實例數據庫的步驟有哪些？
- 2020 年 2 月 24 日
- 筆記
題目部分
在Oracle中，將RAC備份集恢復到單實例數據庫的步驟有哪些？
♣
答案部分
將RAC備份集恢復到單實例數據庫的過程基本上就是先將備份集恢復為RAC數據庫，然後再將數據庫轉換為單實例的數據庫。
數據庫的備份可以使用如下的腳本：
run { allocate channel c1 type disk; allocate channel c2 type disk; backup database format '/home/oracle/rman_back/full_%n_%T_%t_%s_%p.bak'; sql 'alter system archive log current'; backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input; backup current controlfile format '/home/oracle/rman_back/ctl_%d_%T_%s_%p.bak'; release channel c1; release channel c2; }
將RAC備份集恢復到單實例數據庫可以分為恢復為ASM存儲的單實例和FS存儲的單實例，其處理過程分別不同。
1、rac恢復到ASM中
ORACLE_SID=lhrdbasm startup nomount; set dbid 2136828548 restore spfile to '/tmp/aabb.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak'; strings /tmp/aabb.ora *.audit_file_dest='/u01/app/oracle/admin/lhrdbasm/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+FRA','+FRA' *.db_block_size=8192 *.db_create_file_dest='+FRA' *.db_domain='' *.db_name='lhrrac1' *.db_recovery_file_dest='+FRA' *.db_recovery_file_dest_size=20558159872 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrdbasmXDB)' *.memory_target=630194176 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' mkdir -p /u01/app/oracle/admin/lhrdbasm/adump create spfile from pfile='/tmp/b.txt'; startup nomount force; restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak'; alter database mount; set line 9999 pagesize 9999 col FILE_NAME format a60 select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile union all select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile union all select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile union all select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile; set pagesize 200 linesize 200 select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a union all select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a union all SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a; set pagesize 200 linesize 200 select 'set newname for datafile ' || a.FILE# || ' to "+FRA";' from v$datafile a union all select 'set newname for tempfile ' || a.FILE# || ' to "+FRA";' from v$tempfile a union all SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''+FRA'''' ";' FROM v$logfile a; run { ALLOCATE CHANNEL c1 DEVICE TYPE DISK; set newname for datafile 1 to "+FRA"; set newname for datafile 2 to "+FRA"; set newname for datafile 3 to "+FRA"; set newname for datafile 4 to "+FRA"; set newname for datafile 5 to "+FRA"; set newname for datafile 6 to "+FRA"; set newname for tempfile 1 to "+FRA"; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''+FRA'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''+FRA'' "; restore database; SWITCH DATAFILE ALL; SWITCH TEMPFILE ALL; release channel c1; } list backupset of archivelog all; RUN { set until sequence 10 thread 1; set until sequence 7 thread 2; recover database; } alter database open resetlogs; col instance format a20 select thread#,instance,status,enabled from v$thread; alter database disable thread 2 ; alter database drop logfile group 3 ; alter database drop logfile group 4 ; drop tablespace undotbs2 including contents and datafiles;
2、RAC恢復到FS中
ORACLE_SID=lhrfs startup nomount; set dbid 2136828548 restore spfile to '/tmp/aabbcc.ora' from '/home/oracle/rman_back/full_LHRRAC1x_20180711_981219654_4_1.bak'; *.audit_file_dest='/u01/app/oracle/admin/lhrfs/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/lhrfs/control01.dbf','/u01/app/oracle/oradata/lhrfs/control02.dbf' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata/lhrfs' *.db_domain='' *.db_name='lhrrac1' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4558159872 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=lhrfsXDB)' *.memory_target=630194176 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' create spfile from pfile='/tmp/b.txt'; startup nomount force; restore controlfile from '/home/oracle/rman_back/ctl_LHRRAC1_20180711_7_1.bak'; alter database mount; set line 9999 pagesize 9999 col FILE_NAME format a60 select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile union all select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile union all select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile union all select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile; set pagesize 200 linesize 200 select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";' from v$datafile a union all select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";' from v$tempfile a union all SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' || a.MEMBER || ''''' ";' FROM v$logfile a; set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf"; set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf"; set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf"; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' "; run { ALLOCATE CHANNEL c1 DEVICE TYPE DISK; set newname for datafile 1 to "/u01/app/oracle/oradata/lhrfs/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/lhrfs/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/lhrfs/undotbs101.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/lhrfs/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/lhrfs/example01.dbf"; set newname for datafile 6 to "/u01/app/oracle/oradata/lhrfs/undotbs201.dbf"; set newname for tempfile 1 to "/u01/app/oracle/oradata/lhrfs/temp01.dbf"; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.264.976375887'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_2.265.976375889'' to ''/u01/app/oracle/oradata/lhrfs/G2_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.262.976375881'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_1.263.976375883'' to ''/u01/app/oracle/oradata/lhrfs/G1_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.269.976376503'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_3.270.976376505'' to ''/u01/app/oracle/oradata/lhrfs/G3_redo02.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.271.976376507'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''+DATA/lhrrac1/onlinelog/group_4.272.976376509'' to ''/u01/app/oracle/oradata/lhrfs/G4_redo02.log'' "; restore database; SWITCH DATAFILE ALL; SWITCH TEMPFILE ALL; release channel c1; } list backupset of archivelog all; RUN { set until sequence 10 thread 1; set until sequence 7 thread 2; recover database; } alter database open resetlogs; col instance format a20 select thread#,instance,status,enabled from v$thread; alter database disable thread 2 ; alter database drop logfile group 3 ; alter database drop logfile group 4 ; drop tablespace undotbs2 including contents and datafiles;
& 說明：
有關RAC備份集恢復到單實例數據庫的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-1682255/。
本文選自《Oracle程序員面試筆試寶典》，作者：小麥苗
