当前位置: 首页 > Oracle, oracle 11g > 正文

dataguard丢失归档案例(ARCHIVE GAP)

在本人虚拟机上有一套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环境实现自动同步。

本文固定链接: http://www.dbdream.com.cn/2013/12/dataguard%e4%b8%a2%e5%a4%b1%e5%bd%92%e6%a1%a3%e6%a1%88%e4%be%8barchive-gap/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2013年12月05日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: dataguard丢失归档案例(ARCHIVE GAP) | 信春哥,系统稳,闭眼上线不回滚!
关键字: , , , , ,

dataguard丢失归档案例(ARCHIVE GAP):等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter