前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试738】在Oracle中,将RAC备份集恢复到单实例数据库的步骤有哪些?

【DB笔试面试738】在Oracle中,将RAC备份集恢复到单实例数据库的步骤有哪些?

作者头像
AiDBA宝典
发布2020-02-24 10:13:20
1.1K0
发布2020-02-24 10:13:20
举报
文章被收录于专栏:小麦苗的DB宝专栏

题目部分

在Oracle中,将RAC备份集恢复到单实例数据库的步骤有哪些?

答案部分

将RAC备份集恢复到单实例数据库的过程基本上就是先将备份集恢复为RAC数据库,然后再将数据库转换为单实例的数据库。

数据库的备份可以使用如下的脚本:

代码语言:javascript
复制
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中

代码语言:javascript
复制
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中

代码语言:javascript
复制
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 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档