一个备份集同时恢出dataguard的主库&备库

最新推荐文章于 2021-03-05 18:42:53 发布

转载最新推荐文章于 2021-03-05 18:42:53 发布 · 125 阅读

本文详细记录了一次使用RMAN进行数据库备份与恢复的过程。包括数据库文件、控制文件及归档日志的恢复步骤，并针对主备库不同步的问题提出了有效的解决办法。

----------源库rman备份----------------------
RMAN> backup as compressed backupset database format '/mnt/rmanm/wholem_%d_%U';
生成：wholem_MSCDB_02ovel9i_1_1/wholem_MSCDB_03ovelef_1_1/wholem_MSCDB_04ovetlg_1_1
RMAN> backup current controlfile format '/mnt/rmanm/ctl_%d_%U';
生成：ctl_MSCDB_05ovev9f_1_1
backup archivelog all format '/mnt/rmanm/arch_%d_%U';
生成：arch_MSCDB_07ovevel_1_
backup current controlfile format '/mnt/rmanm/ctl_%d_%U';
生成：ctl_MSCDB_08ovevfk_1_1 


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6       1.48M      DISK        00:00:00     31-JAN-14      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20140131T135949
        Piece Name: /mnt/rmanm/arch_MSCDB_07ovevel_1_1


  List of Archived Logs in backup set 6
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    31227   194125679  31-JAN-14 194126673  31-JAN-14
  1    31228   194126673  31-JAN-14 194127437  31-JAN-14
  1    31229   194127437  31-JAN-14 194127886  31-JAN-14
  1    31230   194127886  31-JAN-14 194128361  31-JAN-14
  1    31231   194128361  31-JAN-14 194128820  31-JAN-14
  1    31232   194128820  31-JAN-14 194129424  31-JAN-14
  1    31233   194129424  31-JAN-14 194129980  31-JAN-14
  1    31234   194129980  31-JAN-14 194130514  31-JAN-14
  1    31235   194130514  31-JAN-14 194131079  31-JAN-14
  1    31236   194131079  31-JAN-14 194131303  31-JAN-14
  1    31237   194131303  31-JAN-14 194131324  31-JAN-14


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    13.61M     DISK        00:00:02     31-JAN-14      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140131T140020
        Piece Name: /mnt/rmanm/ctl_MSCDB_08ovevfk_1_1
  Control File Included: Ckp SCN: 194131352    Ckp time: 31-JAN-14
  
-----------------主库--------------------
1.恢控制文件
sql>startup nomount;
RMAN>restore controlfile from '/mnt/rmanm/ctl_MSCDB_05ovev9f_1_1';
2.恢数据文件
RMAN>sql 'alter database mount';


run{
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
restore database; 
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
switch datafile all;
recover database;
}
找不到归档31227#，


3.恢归档
sql>shutdown immediate;
cp control01.ctl control01.ctl.bak                               
cp control02.ctl control02.ctl.bak                               
cp control03.ctl control03.ctl.bak   
sql>startup nomount;
RMAN>restore controlfile from '/mnt/rmanm/ctl_MSCDB_08ovevfk_1_1 ';
RMAN>restore archivelog from sequence = 31227 until sequence = 31235;  ------------脑子晕了 明明是31237阿。为啥写31235呢。悲剧
sql>shutdown immediate;
cp control01.ctl    control01.ctlnew
cp control01.ctlbak control01.ctl
cp control02.ctl    control02.ctlnew
cp control02.ctlbak control02.ctl
cp control03.ctl    control03.ctlnew
cp control03.ctlbak control03.ctl


4.recover启库
sql>recover database using backup controlfile until cancel;
auto
select name from v$tempfile;
select member from v$logfile;
alter database rename file '/oradata/MSCDB/temp01.dbf'     to '+DATADG/MSCDB/data/temp01.dbf';  
alter database rename file '/oradata/MSCDB/redo01.log' to '+DATADG/MSCDB/redo_a/redo01.log';
alter database rename file '/oradata/MSCDB/redo02.log' to '+DATADG/MSCDB/redo_a/redo02.log';
alter database rename file '/oradata/MSCDB/redo03.log' to '+DATADG/MSCDB/redo_a/redo03.log';
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('+DATADG/MSCDB/redo_a/standby_redo4.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('+DATADG/MSCDB/redo_a/standby_redo5.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('+DATADG/MSCDB/redo_a/standby_redo6.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('+DATADG/MSCDB/redo_a/standby_redo7.log') SIZE 200M;
alter database open resetlogs;  


------------------备库-------------------
1，2和主库一样
1.恢控制文件
sql>startup nomount;
RMAN>restore controlfile from '/mnt/rmanm/ctl_MSCDB_05ovev9f_1_1';


2.恢数据文件
RMAN>sql 'alter database mount';
run{
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
restore database; 
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
switch datafile all;
recover database;
}
--------------------------分析-------------------------------------------------
备库网络相当好，速度是主库的20倍，导致 备库的数据文件比主库更早恢复好。
主库完工的时候，备库后面的归档都自动传了。非常诡异已经应用到31237了（这是为什么呢？！）。两边不一致了，
备库在分岔路口不知道要应用哪个归档了。
   31234    31235    31236
__________________________________31237备库
             ·\__________________ 
               1   2  3   4        主库
               


——————————————————————————备库正确操作------------------------------------------
1.恢控制文件
sql>startup nomount;
RMAN>restore standby controlfile from '/mnt/rmanm/ctl_MSCDB_05ovev9f_1_1';


2.恢数据文件
RMAN>sql 'alter database mount';
run{
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
restore database; 
set NEWNAME for datafile  1 to '+DATADG/MSCDB/data/1-system01.dbf';
set NEWNAME for datafile  2 to '+DATADG/MSCDB/data/2-sysaux01.dbf';
set NEWNAME for datafile  3 to '+DATADG/MSCDB/data/3-undotbs01.dbf';
。。。
set NEWNAME for datafile 54 to '+DATADG/MSCDB/data/54-CBMS_DATA0103.ora';
switch datafile all;
--recover database;  这句去掉，防止发生诡异问题
}
recover报错找不到归档31227#


3.追resetlogs之前的归档
sql>shutdown immediate;
sql>startup nomount;
RMAN>restore controlfile from '/mnt/rmanm/ctl_MSCDB_08ovevfk_1_1 ';
RMAN>restore archivelog from sequence = 31227 until sequence = 31235; 
应用缺失的归档进行恢复：
sql>recover automatic standby database;
auto追至31235，寻找31236。。不能让它找到哦，要和做了resetlog的主库一样再从1开始。那么就用主库的控制文件。


4. 追resetlogs之后的归档
sql>shutdown immediate;
sql>startup nomount;
把主库的控制文件备份出来
RMAN>backup current controlfile;
在备库上恢复
RMAN>restore standby controlfile from '/oracle/app/oracle/product/11.2.0/dbs/07otonhb_1_1';
再做sql>recover automatic standby database;  果然开始从1开始了，大功快告成了！
把主库归档备份出来
RMAN>backup archivelog from sequence = 1 until sequence = 104;
在备库上恢复
RMAN>catalog start with '/oracle/app/oracle/product/11.2.0/dbs'; 
RMAN>restore archivelog from sequence = 1 until sequence = 104; 
再做sql>recover automatic standby database;  刷的就追完了，和主库一样了。


5.打开实时应用
sql>alter database recover managed standby database using current logfile disconnect from session;
在主库切几个归档alter system switch logfile;
select thread#,sequence#,applied from v$archived_log order by 2;
看到都及时应用了。done！

过程有点坎坷。但证明一个备份集可以恢出主备两个库来的，即使主库做了resetlog。

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

转载于:http://blog.itpub.net/7590112/viewspace-1070201/