作者简介： 徐明辉，Oracle维护工程师，有5年Oracle维护经验，曾经维护过电信，教育，银行等行业，获得Oracle 10g ocm认证，擅长Oracle数据库故障诊断及问题处理。
当Dataguard由于网络等原因，不能将归档传输到备库，主库归档又快满的时候，我们迫不得已将归档删除，当故障原因解除后，备库丢失了部分的主库日志，造成不能进行同步，由于数据库体量较大，如果重建的方式来创建备库，时间较长，所以本篇讨论的是在不重建备库的情况下，如何来恢复数据库的备库。
模拟主库丢失归档日志
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> ALTER system SET log_archive_dest_state_2 ='defer'; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /oracle/app/oracle/archive Oldest online log sequence 61 Next log sequence to archive 63 Current 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. |
删除归档文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@db ~]$ cd /oracle/app/oracle/archive/ [oracle@db archive]$ ls -l -rw-r-----. 1 oracle oinstall 2048 Aug 30 18:47 1_61_976759400.dbf -rw-r-----. 1 oracle oinstall 4608 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 oinstall 2560 Aug 30 18:50 1_64_976759400.dbf -rw-r-----. 1 oracle oinstall 4608 Aug 30 18:51 1_65_976759400.dbf -rw-r-----. 1 oracle oinstall 1024 Aug 30 18:51 1_66_976759400.dbf [oracle@db archive]$ rm -rf * [oracle@db archive]$ ls -l total 0 SQL>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 initialization
parameter is defined to a value that’s sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
——————————————————————————————————————————————
主库的归档：
|
1 2 3 4 5 |
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 |
备库接收和应用的归档：
|
1 2 3 4 5 6 7 8 9 10 11 |
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号
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log WHERE SEQUENCE# >60 order by 1; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ------------- ------------ 61 1474796 1474801 62 1474801 1474807 63 1474807 1475156 64 1475156 1475161 65 1475161 1475167 66 1475167 1475170 67 1475170 1475342 |
增量备份
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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:04 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/oracle/app/oracle/oradata/test/system01.dbf input datafile file number=00002 name=/oracle/app/oracle/oradata/test/sysaux01.dbf input datafile file number=00005 name=/oracle/app/oracle/oradata/test/example01.dbf input datafile file number=00003 name=/oracle/app/oracle/oradata/test/undotbs01.dbf input datafile file number=00006 name=/oracle/app/oracle/oradata/test/test11.dbf input datafile file number=00004 name=/oracle/app/oracle/oradata/test/users01.dbf channel ORA_DISK_1: starting piece 1 at 2018/08/30 19:17:04 channel ORA_DISK_1: finished piece 1 at 2018/08/30 19:17:11 piece handle=/home/oracle/incr_TEST_20180830_3.bak tag=TAG20180830T191704 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 2018/08/30 19:17:12 channel ORA_DISK_1: finished piece 1 at 2018/08/30 19:17:13 piece handle=/home/oracle/incr_TEST_20180830_4.bak tag=TAG20180830T191704 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2018/08/30 19:17:13 |
将备份拷贝到备库
|
1 2 3 4 |
[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的恢复
取消应用日志
|
1 2 |
SQL> alter database recover managed standby database cancel ; Database altered. |
恢复增量备份
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
[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 catalog cataloged backup piece backup 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.20M DISK 00:00:00 2018/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 Time Name ---- -- ---- ---------- ------------------- ---- 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:55 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oracle/app/oracle/oradata/test/system01.dbf destination for restore of datafile 00002: /oracle/app/oracle/oradata/test/sysaux01.dbf destination for restore of datafile 00003: /oracle/app/oracle/oradata/test/undotbs01.dbf destination for restore of datafile 00004: /oracle/app/oracle/oradata/test/users01.dbf destination for restore of datafile 00005: /oracle/app/oracle/oradata/test/example01.dbf destination for restore of datafile 00006: /oracle/app/oracle/oradata/test/test11.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/incr_TEST_20180830_3.bak channel ORA_DISK_1: piece handle=/home/oracle/incr_TEST_20180830_3.bak tag=TAG20180830T191704 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 2018/08/30 19:25:57 |
查看alert日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Thu Aug 30 19:25:56 2018 Incremental restore complete of datafile 4 /oracle/app/oracle/oradata/test/users01.dbf checkpoint is 1477134 last deallocation scn is 3 Incremental restore complete of datafile 6 /oracle/app/oracle/oradata/test/test11.dbf checkpoint is 1477134 Incremental restore complete of datafile 3 /oracle/app/oracle/oradata/test/undotbs01.dbf checkpoint is 1477134 last deallocation scn is 3 Incremental restore complete of datafile 5 /oracle/app/oracle/oradata/test/example01.dbf checkpoint is 1477134 last deallocation scn is 942056 Incremental restore complete of datafile 2 /oracle/app/oracle/oradata/test/sysaux01.dbf checkpoint is 1477134 last deallocation scn is 925426 Incremental restore complete of datafile 1 /oracle/app/oracle/oradata/test/system01.dbf checkpoint is 1477134 last deallocation scn is 923796 |
此次恢复说明，scn已经恢复到1477134 大于归档67的1475342，丢失的日志已经恢复。
从主库获取备库的控制文件，并且在备库上恢复。
主库执行：
|
1 2 |
SQL> ALTER DATABASE CREATE standby controlfile AS'/home/oracle/standby.ctl'; Database altered. |
备库恢复:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[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:53 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/oracle/app/oracle/oradata/test/control01.ctl Finished restore at 2018/08/30 19:49:55 |
启动备库日志接收并检查备份是否恢复
启动备库日志接收
|
1 2 |
SQL> alter database recover managed standby database disconnect from session; Database altered. |
Alert 日志如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Thu Aug 30 19:30:39 2018 RFS[1]: Assigned to RFS process 4933 RFS[1]: Selected log 4 for thread 1 sequence 68 dbid -2020061464 branch 976759400 Thu Aug 30 19:30:39 2018 Archived Log entry 56 added for thread 1 sequence 68 ID 0x881c23b8 dest 1: Thu Aug 30 19:30:39 2018 RFS[2]: Assigned to RFS process 4935 RFS[2]: Selected log 4 for thread 1 sequence 69 dbid -2020061464 branch 976759400 Thu Aug 30 19:30:39 2018 Archived Log entry 57 added for thread 1 sequence 69 ID 0x881c23b8 dest 1: Thu Aug 30 19:30:39 2018 Primary database is in MAXIMUM PERFORMANCE mode RFS[3]: Assigned to RFS process 4937 RFS[3]: Selected log 4 for thread 1 sequence 70 dbid -2020061464 branch 976759400 RFS[3]: Selected log 5 for thread 1 sequence 71 dbid -2020061464 branch 976759400 Thu Aug 30 19:30:41 2018 Archived Log entry 58 added for thread 1 sequence 70 ID 0x881c23b8 dest 1: |
已经成功接收归档日志。
检查主备库日志是否一致
主库：
|
1 2 3 4 5 |
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 |
备库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 72 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 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一体机用户组 » Dataguard 主库归档丢失后，如何不重建备库来恢复备库
