主库归档丢失后赌青皮 ，如何不重建备库来恢复备库 Dataguard-Oracle一体机用户组

作者简介
徐明辉，Oracle维护工程师，有5年Oracle维护经验，曾经维护过电信，教育龙翔九洲，银行等行业，获得Oracle 10g ocm认证，擅长Oracle数据库故障诊断及问题处理。
当Dataguard由于网络等原因ca1415，不能将归档传输到备库，主库归档又快满的时候，我们迫不得已将归档删除，当故障原因解除后赵勃楠，备库丢失了部分的主库日志，造成不能进行同步，由于数据库体量较大逆命者曹丕 ，如果重建的方式来创建备库，时间较长，所以本篇讨论的是在不重建备库的情况下水杨柳 ，如何来恢复数据库的备库。
模拟主库丢失归档日志
SQL> ALTER system SET log_archive_dest_state_2 ='defer';System altered. SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /oracle/app/oracle/archiveOldest online log sequence61Next log sequence to archive 63Current log sequence 63 SQL> create table test as select * from dba_objects;Table created.SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.
删除归档文件
[oracle@db ~]$ cd /oracle/app/oracle/archive/[oracle@db archive]$ ls -l-rw-r-----. 1 oracle oinstall2048 Aug 30 18:47 1_61_976759400.dbf-rw-r-----. 1 oracle oinstall4608 Aug 30 18:47 1_62_976759400.dbf-rw-r-----. 1 oracle oinstall 10807296 Aug 30 18:50 1_63_976759400.dbf-rw-r-----. 1 oracle oinstall2560 Aug 30 18:50 1_64_976759400.dbf-rw-r-----. 1 oracle oinstall4608 Aug 30 18:51 1_65_976759400.dbf-rw-r-----. 1 oracle oinstall1024 Aug 30 18:51 1_66_976759400.dbf[oracle@db archive]$ rm -rf *[oracle@db archive]$ ls -ltotal 0SQL>ALTER system SET log_archive_dest_state_2 ='enable';System altered.
备库目前的情况
———————————————————————————
Thu Aug 30 18:53:49 2018
RFS[5]:Assigned to RFS process 4159
RFS[5]:Selected log 4 for thread 1 sequence 67 dbid -2020061464 branch 976759400
Thu Aug 30 18:53:49 2018
Archived Log entry 55 added for thread 1 sequence 67 ID 0x881c23b8 dest 1:
Thu Aug 30 18:53:50 2018
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]:Assigned to RFS process 4161
RFS[6]:Selected log 4 for thread 1 sequence 68 dbid -2020061464 branch 976759400
Thu Aug 30 18:53:53 2018
Fetching gap sequence in thread 1, gap sequence 61-66
Thu Aug 30 18:55:44 2018
FAL[client]:Failed to request gap sequence
GAP – thread 1 sequence 61-66
DBID 2274905832 branch 976759400
FAL[client]:All defined FAL servers have been attempted.
———————————————————————————
Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that’s sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.
———————————————————————————
主库的归档：
SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; THREAD# Last Primary Seq Generated---------- -------------------------- 1 67
备库接收和应用的归档：
SQL> select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; THREAD# Last Standby Seq Received---------- ------------------------- 1 60 SQL> select thread#疯狂的作家 , max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied='YES' group by thread# order by 1; THREAD# Last Standby Seq Applied---------- ------------------------ 1 60
用rman进行增量scn的备份
查询未归档61的SCN号
SQL> select SEQUENCE#,FIRST_CHANGE#吴亚贤,NEXT_CHANGE# FROM v$archived_log WHERe SEQUENCE# >60 order by 1;SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------61 1474796147480162 1474801147480763 1474807147515664 1475156147516165 1475161147516766 1475167147517067 14751701475342
增量备份
RMAN> backup device type disk incremental from scn 1474796 database format '/home/oracle/incr_%d_%T_%s.bak'; Starting backup at 2018/08/30 19:17:04using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/oracle/app/oracle/oradata/test/system01.dbfinput datafile file number=00002 name=/oracle/app/oracle/oradata/test/sysaux01.dbfinput datafile file number=00005 name=/oracle/app/oracle/oradata/test/example01.dbfinput datafile file number=00003 name=/oracle/app/oracle/oradata/test/undotbs01.dbfinput datafile file number=00006 name=/oracle/app/oracle/oradata/test/test11.dbfinput datafile file number=00004 name=/oracle/app/oracle/oradata/test/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2018/08/30 19:17:04channel ORA_DISK_1: finished piece 1 at 2018/08/30 19:17:11piece handle=/home/oracle/incr_TEST_20180830_3.bak tag=TAG20180830T191704 comment=NONEchannel ORA_DISK_1: backup set complete我为钱狂 , elapsed time: 00:00:07channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 2018/08/30 19:17:12channel ORA_DISK_1: finished piece 1 at 2018/08/30 19:17:13piece handle=/home/oracle/incr_TEST_20180830_4.bak tag=TAG20180830T191704 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 2018/08/30 19:17:13
将备份拷贝到备库
[oracle@db ~]$ scp incr_TEST_20180830_* oracle@192.168.220.133:/home/oracle/oracle@192.168.220.133's password: incr_TEST_20180830_3.bak 100% 14MB 14.2MB/s 00:00 incr_TEST_20180830_4.bak 100% 9664KB 9.4MB/s 00:01
用rman进行增量scn的恢复
取消应用日志
SQL> alter database recover managed standby database cancel ;Database altered.
恢复增量备份
[oracle@db ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on 星期四 8月 30 19:22:11 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (DBID=2274905832) RMAN>catalog backuppiece '/home/oracle/incr_TEST_20180830_3.bak'; using target database control file instead of recovery catalogcataloged backup piecebackup piece handle=/home/oracle/incr_TEST_20180830_3.bak RECID=1 STAMP=985548193 RMAN> list backup of database;List of Backup Sets=================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1 Incr 14.20MDISK 00:00:002018/08/30 19:17:04 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20180830T191704 Piece Name: /home/oracle/incr_TEST_20180830_3.bak List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp TimeName ---- -- ---- ---------- ------------------- ---- 1 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/system01.dbf 2 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/sysaux01.dbf 3 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/undotbs01.dbf 4 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/users01.dbf 5 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/example01.dbf 6 Incr 1477134 2018/08/30 19:17:04 /oracle/app/oracle/oradata/test/test11.dbf RMAN> recover DATABASE noredo; Starting recover at 2018/08/30 19:25:55using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=25 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /oracle/app/oracle/oradata/test/system01.dbfdestination for restore of datafile 00002: /oracle/app/oracle/oradata/test/sysaux01.dbfdestination for restore of datafile 00003: /oracle/app/oracle/oradata/test/undotbs01.dbfdestination for restore of datafile 00004: /oracle/app/oracle/oradata/test/users01.dbfdestination for restore of datafile 00005: /oracle/app/oracle/oradata/test/example01.dbfdestination for restore of datafile 00006: /oracle/app/oracle/oradata/test/test11.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/incr_TEST_20180830_3.bakchannel ORA_DISK_1: piece handle=/home/oracle/incr_TEST_20180830_3.bak tag=TAG20180830T191704channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 2018/08/30 19:25:57
查看alert日志：
Thu Aug 30 19:25:56 2018Incremental restore complete of datafile 4 /oracle/app/oracle/oradata/test/users01.dbf checkpoint is 1477134 last deallocation scn is 3Incremental restore complete of datafile 6 /oracle/app/oracle/oradata/test/test11.dbf checkpoint is 1477134Incremental restore complete of datafile 3 /oracle/app/oracle/oradata/test/undotbs01.dbf checkpoint is 1477134 last deallocation scn is 3Incremental restore complete of datafile 5 /oracle/app/oracle/oradata/test/example01.dbf checkpoint is 1477134 last deallocation scn is 942056Incremental restore complete of datafile 2 /oracle/app/oracle/oradata/test/sysaux01.dbf checkpoint is 1477134 last deallocation scn is 925426Incremental restore complete of datafile 1 /oracle/app/oracle/oradata/test/system01.dbf checkpoint is 1477134 last deallocation scn is 923796
此次恢复说明霹雳九皇座 ，scn已经恢复到1477134 大于归档67的1475342，丢失的日志已经恢复。
从主库获取备库的控制文件宝嘉康蒂 ，并且在备库上恢复。蔡紫芬
主库执行：
SQL> ALTER DATABASE CREATE standby controlfile AS'/home/oracle/standby.ctl';Database altered.
备库恢复:
[oracle@db archive]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on 星期四 8月 30 19:49:16 2018 Copyright (c) 1982, 2011一言九鼎造句 , Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (not mounted) RMAN> restore controlfile from '/home/oracle/standby.ctl'; Starting restore at 2018/08/30 19:49:53using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: copied control file copyoutput file name=/oracle/app/oracle/oradata/test/control01.ctlFinished restore at 2018/08/30 19:49:55
启动备库日志接收并检查备份是否恢复
启动备库日志接收
SQL> alter database recover managed standby database disconnect from session;Database altered.
Alert 日志如下：
Thu Aug 30 19:30:39 2018RFS[1]: Assigned to RFS process 4933RFS[1]: Selected log 4 for thread 1 sequence 68 dbid -2020061464 branch 976759400Thu Aug 30 19:30:39 2018Archived Log entry 56 added for thread 1 sequence 68 ID 0x881c23b8 dest 1:Thu Aug 30 19:30:39 2018RFS[2]: Assigned to RFS process 4935RFS[2]: Selected log 4 for thread 1 sequence 69 dbid -2020061464 branch 976759400Thu Aug 30 19:30:39 2018Archived Log entry 57 added for thread 1 sequence 69 ID 0x881c23b8 dest 1:Thu Aug 30 19:30:39 2018Primary database is in MAXIMUM PERFORMANCE modeRFS[3]: Assigned to RFS process 4937RFS[3]: Selected log 4 for thread 1 sequence 70 dbid -2020061464 branch 976759400RFS[3]: Selected log 5 for thread 1 sequence 71 dbid -2020061464 branch 976759400Thu Aug 30 19:30:41 2018Archived Log entry 58 added for thread 1 sequence 70 ID 0x881c23b8 dest 1:
已经成功接收归档日志九鼎神皇 。
检查主备库日志是否一致
主库：
SQL> select thread#全职领主 , max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; THREAD# Last Primary Seq Generated---------- -------------------------- 1 72
备库：
SQL> select thread#宋雨霏 , max(sequence#) "Last Standby Seq Received" from v$archived_log val河南经贸职业学院教务处 闪灵侠 , v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; THREAD# Last Standby Seq Received---------- ------------------------- 1 72SQL> select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied='YES' group by thread# order by 1; THREAD# Last Standby Seq Applied---------- ------------------------ 1 72 SQL> alter database recover managed standby database cancel ; Database altered. SQL> alter database open; Database altered. SQL> select count(*) from scott.test; COUNT(*)----------86959
同步成功！
原创文章，版权归本文作者所有，如需转载请注明出处
喜欢本文请长按下方的二维码订阅Oracle一体机用户组