ITPub博客

首页 > 数据库 > Oracle > 工作中XX容灾端数据库恢复

工作中XX容灾端数据库恢复

Oracle 作者:410192979 时间:2016-03-22 21:06:08 0 删除 编辑
1、使用之前的spfile将数据库启动到nomount状态,生成pfile,修改pfile里面的相关的路径参数及数据库默认undo表空间
export ORACLE_SID=orcl                                                           
rman target /
sql> startup nomount;
2、用拷过来的rman备份集,找到控制文件的备份片,用此备份进行controlfile的恢复
rman >restore controlfile from '/oradata/dbbackup/c-575767957-20150609-02'  ;  
sql> alter database mount;
3、controlfile恢复完之后,将数据库启动到mount状态                                         
SQL>alter database mount ;
4、注册备份片 
SQL> catalog  start with '/oradata/dbbackup'  ;
      catalog backuppiece '/oradata/dbbackup/yktdb_7533_1_881948373';
      catalog backuppiece '/oradata/dbbackup/yktdb_7534_1_881948373';
      catalog backuppiece '/oradata/dbbackup/yktdb_7535_1_881948374';
      catalog backuppiece '/oradata/dbbackup/yktdb_7536_1_881949039';
      catalog backuppiece '/oradata/dbbackup/yktdb_7537_1_881949374';
      catalog backuppiece '/oradata/dbbackup/yktdb_7538_1_881949499';
5、注册归档备份片
   SQL>   
      alter database register logfile '/oradata/dbbackup/arc_bkq92vjt_1_1_7540.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_blq92vjt_1_1_7541.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_bmq92vjt_1_1_7542.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_bnq92vuh_1_1_7543.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_boq92vv2_1_1_7544.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_bpq9301p_1_1_7545.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_bqq93096_1_1_7546.bak'        ;
      alter database register logfile '/oradata/dbbackup/arc_brq930c7_1_1_7547.bak'        ;
      alter database register logfile '/oradata/dbbackup/two_arc_buq932a6_1_1_7550.bak'    ;
7、恢复数据库
nohup rman target / cmdfile=/oradata/dbbackup/restore_yktdb.sql log=restore_yktdb.log &

restore_yktdb.sql的脚本如下:
run
{
set newname for datafile    6 to '/oradata/orcl/ykt1507.262.816867597'      ;
set newname for datafile    7 to '/oradata/orcl/ykt1504.266.816867625'      ;
set newname for datafile    4 to '/oradata/orcl/users.259.816199655'        ;
set newname for datafile    3 to '/oradata/orcl/sysaux.257.816199655'       ;
set newname for datafile    2 to '/oradata/orcl/undotbs1.258.816199655'     ;
set newname for datafile    1 to '/oradata/orcl/system.256.816199655'       ;
set newname for datafile    5 to '/oradata/orcl/undotbs2.264.816199717'     ;
set newname for datafile    8 to '/oradata/orcl/ykt1522.265.816867699'      ;
set newname for datafile    9 to '/oradata/orcl/ykt1506.261.816867711'      ;
set newname for datafile   29 to '/oradata/orcl/sfhctbs.259.874177457'      ;
set newname for datafile   10 to '/oradata/orcl/ykt1501.273.816867723'      ;
set newname for datafile   11 to '/oradata/orcl/ykt1525.274.816867733'      ;
set newname for datafile   12 to '/oradata/orcl/ykt1529.275.816867743'      ;
set newname for datafile   13 to '/oradata/orcl/ykt1505.276.816867749'      ;
set newname for datafile   14 to '/oradata/orcl/ykt1502.277.816867777'      ;
set newname for datafile   15 to '/oradata/orcl/ykt1530.278.816867789'      ;
set newname for datafile   16 to '/oradata/orcl/ykt1531.279.816867795'      ;
set newname for datafile   17 to '/oradata/orcl/ykt1503.280.816867799'      ;
set newname for datafile   18 to '/oradata/orcl/patrol.281.816867807'       ;
set newname for datafile   19 to '/oradata/orcl/ykt1509.282.816867853'      ;
set newname for datafile   20 to '/oradata/orcl/ykt.283.816868367'          ;
set newname for datafile   21 to '/oradata/orcl/ykt1508.284.816868801'      ;
set newname for datafile   22 to '/oradata/orcl/ykt1509.285.817469351'      ;
set newname for datafile   23 to '/oradata/orcl/ykt1508.286.817469369'      ;
set newname for datafile   24 to '/oradata/orcl/ykt1507.287.817469391'      ;
set newname for datafile   25 to '/oradata/orcl/ykt1504.288.817469403'      ;
set newname for datafile   26 to '/oradata/orcl/ykt1506.289.817469419'      ;
set newname for datafile   27 to '/oradata/orcl/ykt1522.290.817469429'      ;
set newname for datafile   28 to '/oradata/orcl/ykt1501.291.817469467'      ;
set newname for datafile   31 to '/oradata/orcl/bipdb.294.849871023'        ;
set newname for datafile   33 to '/oradata/orcl/cibdb.257.864755533'        ;
set newname for datafile   34 to '/oradata/orcl/accredittbs.256.864497207'  ;
set newname for datafile   35 to '/oradata/orcl/sfhctbs.260.874177499'      ;
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
switch datafile all;
release channel c1 ;
release channel c2 ;
}

8、修改redo名称  

SQL>
alter database rename file '+DATA1/yktdb/onlinelog/redo9a' to '/oradata/orcl/redo9a';
alter database rename file '+FLASH/yktdb/onlinelog/redo9b' to '/oradata/orcl/redo9b';
alter database rename file '+DATA1/yktdb/onlinelog/redo7a' to '/oradata/orcl/redo7a';
alter database rename file '+FLASH/yktdb/onlinelog/redo7b' to '/oradata/orcl/redo7b';
alter database rename file '+DATA1/yktdb/onlinelog/redo8a' to '/oradata/orcl/redo8a';
alter database rename file '+FLASH/yktdb/onlinelog/redo8b' to '/oradata/orcl/redo8b';
alter database rename file '+DATA1/yktdb/onlinelog/redo1a' to '/oradata/orcl/redo1a';
alter database rename file '+FLASH/yktdb/onlinelog/redo1b' to '/oradata/orcl/redo1b';
alter database rename file '+DATA1/yktdb/onlinelog/redo2a' to '/oradata/orcl/redo2a';
alter database rename file '+FLASH/yktdb/onlinelog/redo2b' to '/oradata/orcl/redo2b';
alter database rename file '+DATA1/yktdb/onlinelog/redo3a' to '/oradata/orcl/redo3a';
alter database rename file '+FLASH/yktdb/onlinelog/redo3b' to '/oradata/orcl/redo3b';

9、recover数据库
nohup rman target / cmdfile=/oradata/dbbackup/recover_yktdb.sql log=recover_yktdb.log &

recover_yktdb.sql脚本如下:
RMAN>
run
{set until scn=3344180531;
recover database;
alter database open resetlogs;
}
10、创建系统默认临时表空间
create temporary tablespace temptbs tempfile '/oradata/orcl/temptbs.dbf' size 20G;
alter database default temporary tablespace temptbs;
11、删除多余的undo表空间
drop tablespace UNDOTBS2 including contents and datafiles ;  

注意:spfile中的undo_tablespace参数必须设置为现有的undo表空间


可能遇到的命令:
*._allow_resetlogs_corruption=true
备注:当数据库无法resetlogs打开时,在pfile中修改隐含参数_allow_resetlogs_corruption为true,再强制resetlogs打开数据库
*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$','_SYSSMU21$','_SYSSMU22$','_SYSSMU23$','_SYSSMU24$','_SYSSMU25$','_SYSSMU26$','_SYSSMU27$','_SYSSMU28$','_SYSSMU29$','_SYSSMU30$','_SYSSMU31$','_SYSSMU32$','_SYSSMU33$','_SYSSMU34$','_SYSSMU35$','_SYSSMU36$','_SYSSMU37$','_SYSSMU38$','_SYSSMU39$','_SYSSMU40$','_SYSSMU41$','_SYSSMU42$','_SYSSMU43$','_SYSSMU44$','_SYSSMU45$','_SYSSMU46$','_SYSSMU47$','_SYSSMU48$','_SYSSMU49$','_SYSSMU50$','_SYSSMU51$','_SYSSMU52$','_SYSSMU53$','_SYSSMU54$','_SYSSMU55$','_SYSSMU56$','_SYSSMU57$','_SYSSMU58$','_SYSSMU59$'  
备注:当undo表空间无法restore时,在pfile中添加参数_corrupted_rollback_segments,意为跳过undo指定的段进行restore

可能使用到的命令:

备份增量的归档日志:
backup archivelog all not backed up 1 times format '/odscdc/test/orabak/two_test_%s_%p_%t';
恢复单个数据文件
restore datafile n;
删除多余节点的redo
alter database disable thread 2;
alter database drop logfile group 3;
删除redo group
alter database drop logfile group 3;
添加redo member
alter database add logfile member '+FLASH/' to group 1   ;
删除redo member
alter database drop logfile member  '+FLASH/citybank/onlinelog/group_12.3132.882192177'; 
检查备份片:
crosscheck backup;
delete expired backup;
校验备份片是否可用:
restore datafile 19 validate check logical;
设置event参数,最大限度的恢复数据:
alter system set event='19548 trace name context forever','19549 trace name context forever' scope=spfile;

可能使用到的查询语句:

select to_char(scn_to_timestamp(19617145102), 'yyyy-mm-dd hh24:mi:ss') scn_time, timestamp_to_scn(scn_to_timestamp(19617145102)) scn from dual;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select timestamp_to_scn(sysdate) from dual;

select timestamp_to_scn(to_date('2015-06-10 04:38:26','yyyy-mm-dd hh24:mi:ss')) as scn  from dual;

select current_scn from v$database;

select name,sequence#,first_time,FIRST_CHANGE#,next_time,next_change# from v$archived_log ;

select file#,checkpoint_change# from v$datafile;

select file#,checkpoint_change# from v$datafile_header;

select name,sequence#,first_time,FIRST_CHANGE#,next_time,next_change# from v$archived_log where status ='A' order by first_change#  ;



附:

基于时间的不完全恢复:

RMAN> run { 
  set until time='2011-10-18 05:08:04'; 
  restore database; 
  recover database; 
  alter database open resetlogs; } 
 
基于scn号的不完全恢复:

RMAN> run { 
  set until scn=475351; 
  restore database; 
  recover database; 
  alter database open resetlogs; } 

基于日志序号的不完全恢复:

RMAN> run { 
  set until sequence=9; 
  restore database; 
  recover database; 
  alter database open resetlogs; } 








    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26366371/viewspace-2062052/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-08

  • 博文量
    153
  • 访问量
    111048