缺少log_archive_config导致归档路径被禁用

10g的DATA GUARD的一个主要特点就是引入了log_archive_config参数，如果缺少这个参数，可能会导致归档路径被禁用。

看别人建立DATA GUARD时碰到了这个问题，当时觉得比较有意思，于是特意重现一下。

当前是一个已经配置好的DATA GUARD，为了模拟错误，先将这个参数设置为空：

SQL> alter system set log_archive_config = '';

System altered.

SQL> alter system switch logfile;

System altered.

SQL> show parameter archive_dest_2

NAME                                 TYPE        VALUEITPUB个人空间
x*O})q8T
------------------------------------ ----------- ------------------------------ITPUB个人空间3U m;Dd~w
log_archive_dest_2                   string      SERVICE=standby LGWR SYNC VALI
mo1iIl\q9f0                                                 D_FOR=(ONLINE_LOGFILES,PRIMARY
d-q4M5S%J
u0                                                 _ROLE) DB_UNIQUE_NAME=standby
nI%V:K7o3q&|0SQL> alter system set log_archive_dest_2 = 'SERVICE=standby ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

SQL> alter system switch logfile;

System altered.

日志切换后，alert文件中并没有任何的错误，包含重新设置log_archive_dest_2参数后再次执行切换日志，alert文件中仍然看不到预期的错误。

导致问题无法重现的原因可能是由于系统中已经设置过log_archive_config参数了，虽然现在置为空，但是这个参数的生效可能会一直保留，于是尝试重启数据库：

SQL> shutdown immediate
7d;H
U"vjE9^/r2Zrv0Database closed.
h
PL0ai#r7|CQ%Y0Database dismounted.
,QG4BmN!I0ORACLE instance shut down.
#H6vSc)b0SQL> startup
fX-Glv7_C0ORACLE instance started.

Total System Global Area 2147483648 bytesITPUB个人空间X"fe?o$d+vf
Fixed Size                  2074112 bytesITPUB个人空间q0HsBwZ+V*Wf
Variable Size             486541824 bytesITPUB个人空间HF!PwHp/\?
Database Buffers         1644167168 bytes
s5gA7_@^#h0Redo Buffers               14700544 bytes
&\s`w.]Mo9l0Database mounted.
+fJ8f+E*Qex0Database opened.ITPUB个人空间9H+P&LI0Z
SQL> alter system switch logfile;

System altered.

SQL> alter system set log_archive_dest_2 = 'SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

SQL> alter system switch logfile;

System altered.

这是从alert文件中已经可以看到预期的错误了：

Thu Dec 23 21:42:48 2010
y6zf&~EK0Completed: ALTER DATABASE OPEN
.Lj!xId
xq-b0Thu Dec 23 21:42:51 2010ITPUB个人空间V}7I8vHA5F
Thread 1 advanced to log sequence 11
-RiP k |W.mfyb;L`0  Current log# 2 seq# 11 mem# 0: /data/oradata/primary/redo02.logITPUB个人空间Vy)I'\Gb!V.l
Thu Dec 23 21:42:51 2010
9Ii7zz:x![R0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:ITPUB个人空间&J)U]{KZ?z
ORA-16057: DGID from server not in Data Guard configurationITPUB个人空间v wp H$R{
Thu Dec 23 21:42:51 2010ITPUB个人空间%sN9\z3F-UM
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
([*~-QTL?GF0FAL[server, ARC1]: FAL archive failed, see trace file.ITPUB个人空间JM)Nic.Co(aV
Thu Dec 23 21:42:51 2010
x;?%G!^y0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
,vBP \?t H9c#S0ORA-16055: FAL request rejected
h~^6?B[0ARCH: FAL archive failed. Archiver continuing
Jyjy;RZ2Q7i@0Thu Dec 23 21:42:51 2010
b+F;VXw$C|-TkO0ORACLE Instance primary - Archival Error. Archiver continuing.
2FH4J j+mqz0Thu Dec 23 21:43:46 2010ITPUB个人空间 SgE"w:S)wr2v&K
Shutting down archive processesITPUB个人空间:@yt __
Thu Dec 23 21:43:51 2010
k,V`qN!I0ARCH shutting downITPUB个人空间F4O-]A:uN.{;p
ARC2: Archival stopped
/zl5C{5un,Ag0Thu Dec 23 21:45:10 2010
G`V`6q8x0ALTER SYSTEM SET log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' SCOPE=BOTH;ITPUB个人空间2_Wu,@D:J
LNS1 started with pid=18, OS id=18425
.F)zFF3oe Dd0Thu Dec 23 21:45:29 2010ITPUB个人空间 HL?k*v/w(J}(R
Thread 1 advanced to log sequence 12ITPUB个人空间a~K%\)KJy
  Current log# 3 seq# 12 mem# 0: /data/oradata/primary/redo03.log
o(k0b"S7x'oT0Thu Dec 23 21:45:29 2010ITPUB个人空间'{&q'\,l7ey
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:ITPUB个人空间`zH/Q9W
ORA-16057: DGID from server not in Data Guard configurationITPUB个人空间P.Bj,yC8z
Thu Dec 23 21:45:29 2010ITPUB个人空间 P` QJB Coc
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
)SjmJTu,SW4L&W*\0FAL[server, ARC1]: FAL archive failed, see trace file.
|-M T`r5M0Thu Dec 23 21:45:29 2010
1Et(Jj s&i7T0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
0w@2|+_0r.S,m}Nz0ORA-16055: FAL request rejectedITPUB个人空间.W5LpX qbb6m*]
ARCH: FAL archive failed. Archiver continuing
'mC"s#Z T5xm%Yk:v u0Thu Dec 23 21:45:29 2010
?xH n#dJP5b0ORACLE Instance primary - Archival Error. Archiver continuing.ITPUB个人空间qz#kt:o)\q
Thu Dec 23 21:50:51 2010
_
Y'|#Mi'N1BOd&z/_ Ru0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
h WK9z9@Wpb1b0ORA-16057: DGID from server not in Data Guard configuration
R-D [ H&O*K"].L_0Thu Dec 23 21:50:51 2010ITPUB个人空间$[S&j-l7n v'@
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
"D.g6q*Z'B+h8U0qI e0Thu Dec 23 21:55:51 2010
bd q[:b wP0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
Q(^*wgt0ORA-16057: DGID from server not in Data Guard configurationITPUB个人空间 ZE{?*Y Bk[
Thu Dec 23 21:55:51 2010
(UX'@ Vn1V$x0PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
Xc,CEl"[4{c0Thu Dec 23 22:00:51 2010
An2GD\#F%i~0Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
W]
jz7p#X+w8w+JT7\0ORA-16057: DGID from server not in Data Guard configurationITPUB个人空间:Q.xyZB^z[$X:@9}
Thu Dec 23 22:00:51 2010ITPUB个人空间,w1^X2s.a^VT_
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.

错误信息很明确ORA-16057，说明当前的SERVICE归档设置的主机没有包括在DATA GUARD配置中。

下面将log_archive_config参数添加回来：

SQL> alter system set log_archive_config = 'DG_CONFIG=(primary,standby)';

System altered.

SQL> alter system switch logfile;

System altered.

检查alert文件：

Thu Dec 23 22:05:29 2010ITPUB个人空间3E{l4sqSg@
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(primary,standby)' SCOPE=BOTH;ITPUB个人空间*w6M#qlt~I.FY^
Thu Dec 23 22:05:40 2010
hrlX c
?H+]0Thread 1 advanced to log sequence 13ITPUB个人空间-u9P&pwkEB d#H+G
  Current log# 1 seq# 13 mem# 0: /data/oradata/primary/redo01.log
:H(|6IfG*Y7A4g0Thu Dec 23 22:05:40 2010ITPUB个人空间)xBG!i$V|
ARC0: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: destination Data Guard configuration error

这时，第二个预期的错误也出现了对于LOG_ARCHIVE_DEST_2参数设置的路径被禁止掉。

SQL> show parameter log_archive_dest_%2

NAME                                 TYPE        VALUE
"Tv1J.L7NH1De"G0------------------------------------ ----------- ------------------------------
I0MM4h+F6cg:b&[0log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VALITPUB个人空间-r0a_f3L;s
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
d!eFJkb+U0                                                 Y_ROLE) DB_UNIQUE_NAME=standbyITPUB个人空间D+UI'k ]b
log_archive_dest_state_2             string      enable

仅从参数上看，log_archive_dest_state_2的值仍然是enable，但是这个归档路径已经不会完成归档操作了：

SQL> select name, sequence#ITPUB个人空间+|$p!InnYS3XQ
  2  from v$archived_log
,h?af5[ ?C0  3  order by 2, 1;

NAME                                                          SEQUENCE#ITPUB个人空间v+{t1G-T7C;~7I
------------------------------------------------------------ ----------ITPUB个人空间QH3iUXKy\;v
                                                                      3ITPUB个人空间i*W4hCe*I
/data/oradata/primary/archivelog/1_4_737020478.dbf                    4
'IUo*@3_4pVU}"F0standby                                                               4
3tyzy$wd0                                                                      4ITPUB个人空间Q'o4k(y*jO
/data/oradata/primary/archivelog/1_5_737020478.dbf                    5
wYbnQEu0standby                                                               5
^ ~8Y]A`0/data/oradata/primary/archivelog/1_6_737020478.dbf                    6
!N~hc#iO!{0standby                                                               6
C$z S y)F1t6p!s1[0/data/oradata/primary/archivelog/1_7_737020478.dbf                    7ITPUB个人空间(EhpXY3J| v:mJ
standby                                                               7ITPUB个人空间 \0V6s1f?:SJ
/data/oradata/primary/archivelog/1_8_737020478.dbf                    8
^ANl yq^N'kk0standby                                                               8ITPUB个人空间1D#}tQ+s7~
/data/oradata/primary/archivelog/1_9_737020478.dbf                    9
SO#Z[py8{&k0standby                                                               9ITPUB个人空间/e0@ _(T ]#y
/data/oradata/primary/archivelog/1_10_737020478.dbf                  10ITPUB个人空间bra4Kp1NIq5C
/data/oradata/primary/archivelog/1_11_737020478.dbf                  11
)YY2A/fX*O-`0/data/oradata/primary/archivelog/1_12_737020478.dbf                  12ITPUB个人空间&{8AR+ps"rB
/data/oradata/primary/archivelog/1_13_737020478.dbf                  13

18 rows selected.

最近4个归档都没有想到远端，检查V$ARCHIVE_DEST视图：

SQL> select dest_name, status, errorITPUB个人空间? gj:^:gV`
  2  from v$archive_dest
(P6Mt$l'YW Y(E0  3  where dest_id = 2;

DEST_NAME            STATUS    ERRORITPUB个人空间'zy loItt eaG
-------------------- --------- ------------------------------------------------------------
*w_,~W!s;q*Nu(H!Rm0LOG_ARCHIVE_DEST_2   DISABLED  ORA-16057: DGID from server not in Data Guard configuration

可以看到，路径2对应的状态是DISABLED，通过设置log_archive_dest_state_2为ENABLE，可以解决这个问题：

SQL> alter system set log_archive_dest_state_2 = enable;

System altered.

SQL> alter system switch logfile;

System altered.

检查视图状态：

SQL> select name, sequence#
7v$?"gU3hB]CxU.``0  2  from v$archived_log
8g*j7C `s{3?;DQ%_`0  3  order by 2, 1;

NAME                                                          SEQUENCE#ITPUB个人空间k{)j?*`;qj8F
------------------------------------------------------------ ----------ITPUB个人空间@#vx}%t O
                                                                      3ITPUB个人空间)L/n^{#q0n}-r7z
/data/oradata/primary/archivelog/1_4_737020478.dbf                    4ITPUB个人空间6T)^H'Mo
@&gl!~T
standby                                                               4
6H&yrGob0N1hC0                                                                      4
!N&ka6K8t0/data/oradata/primary/archivelog/1_5_737020478.dbf                    5
s(o{xHgt'R0standby                                                               5
GZ,HZbF:X+C-\0/data/oradata/primary/archivelog/1_6_737020478.dbf                    6ITPUB个人空间b3iO/i/e,c
standby                                                               6ITPUB个人空间v q@lb+S$VS6~*B0W
/data/oradata/primary/archivelog/1_7_737020478.dbf                    7ITPUB个人空间;`8f&p#mT&Jr
standby                                                               7ITPUB个人空间Vel V"J
/data/oradata/primary/archivelog/1_8_737020478.dbf                    8
.un*`0^n s0standby                                                               8
[p\G2w5rt;Y&XBj0/data/oradata/primary/archivelog/1_9_737020478.dbf                    9
%S|q-S2}'D^0standby                                                               9ITPUB个人空间^4i[;yE!{:nS
/data/oradata/primary/archivelog/1_10_737020478.dbf                  10ITPUB个人空间 Ql!M9j$\/n
STANDBY                                                              10ITPUB个人空间.p N[R B%\
/data/oradata/primary/archivelog/1_11_737020478.dbf                  11ITPUB个人空间&p:hmox
STANDBY                                                              11
.B}0l3D _:a;D0/data/oradata/primary/archivelog/1_12_737020478.dbf                  12
(p5e9_kH:S:~6VoC0STANDBY                                                              12
M mX']5Za0/data/oradata/primary/archivelog/1_13_737020478.dbf                  13
Hp{j ^5g0STANDBY                                                              13
{!Y vV LN{0/data/oradata/primary/archivelog/1_14_737020478.dbf                  14
J0I5AX8sX0standby                                                              14

24 rows selected.

SQL> select dest_name, status, failure_count, errorITPUB个人空间V;nWV%q.t1z/U+U
  2  from v$archive_dest    
Z LCpu-D,p*T0  3  where dest_id = 2;

DEST_NAME            STATUS    ERROR
F"R?nP0-------------------- --------- ------------------------------------------------------------
lO(\_9Le/MA0LOG_ARCHIVE_DEST_2   VALID

有的时候并不要过分相信Oracle返回的状态，比如这个例子中路径已经被禁止，但是查询参数log_archive_dest_state_2的值却是ENABLE。而解决这个问题的办法，又恰好就是将log_archive_dest_state_2参数的值改为ENABLE。