【DB笔试面试738】在Oracle中，将RAC备份集恢复到单实例数据库的步骤有哪些？
【DB笔试面试738】在Oracle中，将RAC备份集恢复到单实例数据库的步骤有哪些？
AiDBA宝典
发布于 2020-02-24 10:13:20
发布于 2020-02-24 10:13:20
文章被收录于专栏：小麦苗的DB宝专栏
题目部分
在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程序员面试笔试宝典》，作者：小麦苗
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-01-22，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
腾讯云开发者
