dataguard丢失归档案例(ARCHIVE GAP)
Dec052013
在本人虚拟机上有一套11.2.0.3.0版本的DG环境，用于做一些相关的测试，在平时需要用到11.2.0.3.0版本的数据库时，都是将主库打开应用，备库的虚拟机一直都是挂起状态，前几天用到DG环境，在将备库起来后发现少了很多的归档没有传输过来，正常在备库可以访问后，主库会自动向备库传出备库需要的归档，这次竟然没有传，尝试让备库应用日志，也不会传输这些归档。
Fri Nov 29 16:26:12 2013 ALTER DATABASE recover managed standby database using current logfile disconnect Attempt to start background Managed Standby Recovery process (stream) Fri Nov 29 16:26:12 2013 MRP0 started with pid=22, OS id=24980 MRP0: Background Managed Standby Recovery process started (stream) Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 /u01/app/oracle/oradata/stream/redo01.log Clearing online log 1 of thread 1 sequence number 139 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u01/app/oracle/oradata/stream/redo02.log Clearing online log 2 of thread 1 sequence number 137 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /u01/app/oracle/oradata/stream/redo03.log Clearing online log 3 of thread 1 sequence number 138 Clearing online redo logfile 3 complete Media Recovery Waiting for thread 1 sequence 110 Fetching gap sequence in thread 1, gap sequence 110-135 Completed: ALTER DATABASE recover managed standby database using current logfile disconnect
告警日志提示缺少110-135号归档日志，这些日志在主库上是存在的。
[oracle@secdb1 arch]$ ls 1_100_808409555.dbf 1_105_808409555.dbf 1_110_808409555.dbf 1_115_808409555.dbf 1_120_808409555.dbf 1_125_808409555.dbf 1_130_808409555.dbf 1_135_808409555.dbf 1_101_808409555.dbf 1_106_808409555.dbf 1_111_808409555.dbf 1_116_808409555.dbf 1_121_808409555.dbf 1_126_808409555.dbf 1_131_808409555.dbf 1_136_808409555.dbf 1_102_808409555.dbf 1_107_808409555.dbf 1_112_808409555.dbf 1_117_808409555.dbf 1_122_808409555.dbf 1_127_808409555.dbf 1_132_808409555.dbf 1_137_808409555.dbf 1_103_808409555.dbf 1_108_808409555.dbf 1_113_808409555.dbf 1_118_808409555.dbf 1_123_808409555.dbf 1_128_808409555.dbf 1_133_808409555.dbf 1_138_808409555.dbf 1_104_808409555.dbf 1_109_808409555.dbf 1_114_808409555.dbf 1_119_808409555.dbf 1_124_808409555.dbf 1_129_808409555.dbf 1_134_808409555.dbf
将这些日志传输到备库归档目录下。
[oracle@secdb2 arch]$ scp 1_1* 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/ oracle@192.168.249.128's password: 1_100_808409555.dbf 100% 89KB 89.0KB/s 00:00 1_101_808409555.dbf 100% 246KB 245.5KB/s 00:00 1_102_808409555.dbf 100% 9787KB 9.6MB/s 00:01 1_103_808409555.dbf 100% 939KB 939.0KB/s 00:00 1_104_808409555.dbf 100% 5490KB 5.4MB/s 00:00 1_105_808409555.dbf 100% 13KB 12.5KB/s 00:00 1_106_808409555.dbf 100% 39MB 9.6MB/s 00:04 1_107_808409555.dbf 100% 28MB 14.1MB/s 00:02 1_108_808409555.dbf 100% 82KB 82.0KB/s 00:00 1_109_808409555.dbf 100% 2729KB 2.7MB/s 00:01 1_110_808409555.dbf 100% 1536 1.5KB/s 00:00 1_111_808409555.dbf 100% 6873KB 6.7MB/s 00:00 1_112_808409555.dbf 100% 1509KB 1.5MB/s 00:00 1_113_808409555.dbf 100% 42MB 13.9MB/s 00:03 1_114_808409555.dbf 100% 24MB 24.3MB/s 00:01 1_115_808409555.dbf 100% 1536 1.5KB/s 00:00 1_116_808409555.dbf 100% 6894KB 6.7MB/s 00:00 1_117_808409555.dbf 100% 1413KB 1.4MB/s 00:00 1_118_808409555.dbf 100% 46MB 23.0MB/s 00:02 1_119_808409555.dbf 100% 40MB 13.4MB/s 00:03 1_120_808409555.dbf 100% 43MB 21.5MB/s 00:02 1_121_808409555.dbf 100% 48MB 15.9MB/s 00:03 1_122_808409555.dbf 100% 44MB 14.8MB/s 00:03 1_123_808409555.dbf 100% 44MB 21.9MB/s 00:02 1_124_808409555.dbf 100% 48MB 15.9MB/s 00:03 1_125_808409555.dbf 100% 48MB 23.9MB/s 00:02 1_126_808409555.dbf 100% 48MB 23.9MB/s 00:02 1_127_808409555.dbf 100% 48MB 11.9MB/s 00:04 1_128_808409555.dbf 100% 38MB 19.1MB/s 00:02 1_129_808409555.dbf 100% 38MB 12.8MB/s 00:03 1_130_808409555.dbf 100% 6119KB 6.0MB/s 00:00 1_131_808409555.dbf 100% 2048 2.0KB/s 00:00 1_132_808409555.dbf 100% 6100KB 6.0MB/s 00:00 1_133_808409555.dbf 100% 26KB 26.0KB/s 00:00 1_134_808409555.dbf 100% 382KB 382.0KB/s 00:00 1_135_808409555.dbf 100% 31KB 30.5KB/s 00:00 1_136_808409555.dbf 100% 6304KB 6.2MB/s 00:00 1_137_808409555.dbf 100% 7046KB 6.9MB/s 00:00
将这些110-135号日志注册到备库。
SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_110_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_111_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_112_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_113_808409555.dbf'; Database altered. ... ... SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_134_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_135_808409555.dbf'; Database altered.
监控告警日志会发现，在注册成功会，备库应用了这些归档。
Fri Nov 29 16:30:03 2013 Archived Log entry 20 added for thread 1 sequence 146 ID 0x5dd7c213 dest 1: Fri Nov 29 16:30:18 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_110_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_110_808409555.dbf' Fri Nov 29 16:30:24 2013 Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_110_808409555.dbf Media Recovery Waiting for thread 1 sequence 111 Fetching gap sequence in thread 1, gap sequence 111-135 Fri Nov 29 16:30:36 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_111_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_111_808409555.dbf' Fri Nov 29 16:30:44 2013 Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_111_808409555.dbf Media Recovery Waiting for thread 1 sequence 112 Fetching gap sequence in thread 1, gap sequence 112-135 ... ... Fri Nov 29 16:35:09 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_135_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_135_808409555.dbf' Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_135_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_136_808409555.dbf Fri Nov 29 16:35:10 2013 Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_137_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_138_808409555.dbf Recovery of Online Redo Log: Thread 1 Group 10 Seq 139 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stream/redo10.log
并且在应用完135号归档后，自动应用136-138号归档，主库切换几次日志，看看备库是否自动应用这些日志。
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 146 Next log sequence to archive 148 Current log sequence 148
看看备库是否应用这些刚切换的归档。
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 139
Next log sequence to archive 0
Current log sequence 147
SQL> select * from V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 138 145
139号-145号日志没有传过来。
1_136_808409555.dbf 1_137_808409555.dbf 1_138_808409555.dbf 1_146_808409555.dbf 1_147_808409555.dbf
将139-145号归档传到备库。
[oracle@secdb2 arch]$ scp 1_139_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_139_808409555.dbf 100% 136KB 135.5KB/s 00:00 [oracle@secdb2 arch]$ scp 1_140_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_140_808409555.dbf 100% 1536 1.5KB/s 00:00 [oracle@secdb2 arch]$ scp 1_141_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_141_808409555.dbf 100% 2048 2.0KB/s 00:00 [oracle@secdb2 arch]$ scp 1_142_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_142_808409555.dbf 100% 1024 1.0KB/s 00:00 [oracle@secdb2 arch]$ scp 1_143_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_143_808409555.dbf 100% 1024 1.0KB/s 00:00 [oracle@secdb2 arch]$ scp 1_144_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_144_808409555.dbf 100% 1536 1.5KB/s 00:00 [oracle@secdb2 arch]$ scp 1_145_808409555.dbf 192.168.249.128:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch oracle@192.168.249.128's password: 1_145_808409555.dbf 100% 1024 1.0KB/s 00:00
将139-145号归档注册给备库。
SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_139_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_140_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_141_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_142_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_143_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_144_808409555.dbf'; Database altered. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_145_808409555.dbf'; Database altered.
注册成功后，备库并没有自动应用这些归档。
Fri Nov 29 16:45:45 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_139_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_139_808409555.dbf' ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_140_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_140_808409555.dbf' Fri Nov 29 16:46:03 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_141_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_141_808409555.dbf' ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_142_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_142_808409555.dbf' Fri Nov 29 16:46:21 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_143_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_143_808409555.dbf' ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_144_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_144_808409555.dbf' Fri Nov 29 16:46:36 2013 ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_145_808409555.dbf' There are 1 logfiles specified. ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_145_808409555.dbf'
重启备库应用日志进程。
Fri Nov 29 16:48:31 2013 ALTER DATABASE recover managed standby database using current logfile disconnect ORA-1153 signalled during: ALTER DATABASE recover managed standby database using current logfile disconnect... Fri Nov 29 16:49:43 2013 Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_139_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_140_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_141_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_142_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_143_808409555.dbf Fri Nov 29 16:49:43 2013 FAL[server, ARC3]: Error 16009 creating remote archivelog file 'secdb2' FAL[server, ARC3]: FAL archive failed, see trace file. ARCH: FAL archive failed. Archiver continuing ORACLE Instance stream - Archival Error. Archiver continuing. Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_144_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_145_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_146_808409555.dbf Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_147_808409555.dbf Media Recovery Waiting for thread 1 sequence 148 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 148 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stream/redo11.log
由于应用日志进程已经启动，所以启动是报错，但是激活了应用日志进程，应用了这些日志。主库再切换日志，备库可正常应用，故障解决。
主库：
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 146 Next log sequence to archive 148 Current log sequence 148 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL>
备库：
Fri Nov 29 16:50:54 2013 Archived Log entry 55 added for thread 1 sequence 148 ID 0x5dd7c213 dest 1: Fri Nov 29 16:50:54 2013 Media Recovery Waiting for thread 1 sequence 149 Fri Nov 29 16:50:54 2013 RFS[2]: Selected log 10 for thread 1 sequence 149 dbid 1574437395 branch 808409555 Recovery of Online Redo Log: Thread 1 Group 10 Seq 149 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stream/redo10.log Fri Nov 29 16:51:15 2013 RFS[2]: Selected log 11 for thread 1 sequence 150 dbid 1574437395 branch 808409555 Fri Nov 29 16:51:15 2013 Archived Log entry 56 added for thread 1 sequence 149 ID 0x5dd7c213 dest 1: Fri Nov 29 16:51:15 2013 Media Recovery Waiting for thread 1 sequence 150 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 150 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stream/redo11.log
主库再切换日志，归档可以自动传输到备库，不会在产生GAP现象，备库接受到主库传过来的归档后，也可自动应用。至此DG环境实现自动同步。
dataguard丢失归档案例(ARCHIVE GAP)：等您坐沙发呢！
发表评论
