OGG: OGG-00446 Oracle GoldenGate Capture for Oracle, extdata.prm: No valid log files for current redo
如题所示的错误，发生在为Oracle RAC配置OGG的时候，启动源端（RAC）的抽取进程。
该错误的详细反馈如下：
|
1 2 3 4 5 |
[oracle@rac2 ogg_1]$ cat ggserr.log | grep --color "OGG-00446" 2016-03-30 11:14:05 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, extdata.prm: No valid log files for current redo sequence 8, thread 1, error retrieving redo file name for sequence 8, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2016-03-30 11:10:12.000000. 2016-03-30 11:27:49 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, extdata.prm: No valid log files for current redo sequence 8, thread 1, error retrieving redo file name for sequence 8, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2016-03-30 11:10:12.000000. 2016-03-30 11:31:23 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, extdata.prm: No valid log files for current redo sequence 8, thread 1, error retrieving redo file name for sequence 8, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2016-03-30 11:30:22.000000. [oracle@rac2 ogg_1]$ |
造成该错误的原因是因为RAC的共享存储采用了ASM（自动存储管理），而OGG的抽取进程无法连接到ASM，故而无法抓取到redo log。
关于该问题，官方给出了说法：
Extract Fails Due to an ASM Connection Configuration Issue (文档 ID 1061093.1)
根据官方文档的说法，同样的错误还有其他的表现形式：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
GOAL： To show how to recover from an extract failure when your Archive or Redo files are stored under ASM and you see one of the following messages ERROR 118 No Valid Log File For Current Redo Sequence Xxxx, Thread Y ERROR 500 No valid log files for current redo sequence X, thread Y, error retrieving redo file name for sequence X, archived = 0, use_alternate = 0 Not able to establish initial position for begin time YYYY-MM-DD HH:MI:SS ERROR OGG-00446 error 2 (No such file or directory) opening redo log <log file name>.dbf for sequence #### Not able to establish initial position for begin time YYYY-MM-DD HH:MI:SS ERROR OGG-00446 No valid log files for current redo sequence 35194, thread 1, error retrieving redo file name for sequence 35194, archived = 0, use_alternate = 0 Not able to establish initial position for begin time 2014-03-25 15:55:47.000000. |
官方给出的解决方案是这样的：
|
1 2 3 4 5 6 7 8 9 10 11 |
Solution： If you are running Oracle ASM, the problem may be that the ASM connection is either not defined or is incorrectly defined or TRANSLOGOPTINS DBLOGREADER needs to be added. If your archive files are ONLY under ASM and extract receives an error 500, extract may have run successfully until the process needed to read from the ARCHIVES instead of the REDO. Once it needs to read from archive, the extract will fail. Please add the following line, or correct it in your Extract parameter file, if you are On Oracle 11.2.0.2 or better, or 10.2.0.5 or better and using OGG 11.x: TRANLOGOPTIONS DBLOGREADER If the above version of Oracle or OGG doesn't apply to you specifying a user that can connect to the ASM instance and restart your Extract: TRANLOGOPTIONS ASMUSER 【user】@【ASM_instance_name】, ASMPASSWORD 【password】 |
具体解决方法如下所示：
1. 关于抽取进程的配置：
在我的环境中，源端的抽取进程是：extdata
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[oracle@rac2 ogg_1]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (rac2) 1> view param extdata extract extdata dynamicresolution userid oggadmin,password oracle TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle exttrail ./dirdat/et table scott.dept; GGSCI (rac2) 2> exit [oracle@rac2 ogg_1]$ |
注意看其中：TRANLOGOPTIONS，的设定。
2. TNSName的设定
设置TNSNAME的目的是为了在抽取进程中让ASMUSER的“sys@+ASM”可以连接到正确的ASM实例。
不同的节点的TNSNAMES的配置中的”HOST”是不一样的。
具体如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[oracle@rac2 ogg_1]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. SARA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sara) ) ) +ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) ) ) [oracle@rac2 ogg_1]$ |
配置TNSNAME需要对照监听器的状态，上面的“SERVICE_NAME”，在监听器的当前状态中，需要真实存在。
我当前的监听器的状态：
|
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 |
[oracle@rac2 ogg_1]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2016 14:40:51 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 30-MAR-2016 08:40:44 Uptime 0 days 6 hr. 0 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/rac2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.232.152)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.232.252)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "sara" has 1 instance(s). Instance "sara2", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 ogg_1]$ |
最后测试一下：
|
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 |
[oracle@rac2 ogg_1]$ tnsping +ASM TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-MAR-2016 14:41:58 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM))) OK (0 msec) [oracle@rac2 ogg_1]$ [oracle@rac2 ogg_1]$ sqlplus sys/oracle@+ASM as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 30 14:42:05 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ +ASM2 STARTED SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options [oracle@rac2 ogg_1]$ |
最后，保证了上面这些点的正确，那么，如题所示的错误（OGG-00446）就可以避免。
——————————————————————
Done。
