oracle goldengate报错解决之OGG-00446、OGG-00529、OGG-00014

3242423

OGG测试：无法启动ext进程，报错OGG-00446

报错日志【截取重要部分】
2016-10-28 16:51:40  ERROR   OGG-00446  Missing filename opening checkpoint file.
2016-10-28 16:51:40  ERROR   OGG-01668  PROCESS ABENDING.

日志意思很明显：在打开checkpoint file 时缺少文件名

查看参数：
oracle@a-test30 dirprm]$ more ext1.prm
UserId ogg, Password ogg
ExtTrail ./dirdat/eo
GetTruncates
TranLogOptions ExcludeUser ogg
--DDL Include All
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOptions AddTranData Retryop Retrydelay 10 Maxretries 10
Table TMP.test1;
Table TMP.test2;
Table TMP.test3
[oracle@a-test30 dirprm]$ more pump1.prm
Extract pump1
PassThru
RmtHost 192.168.10.61, MgrPort 7809
RmtTrail ./dirdat/go

Table TMP.test1;
Table TMP.test2;
Table TMP.test3;

发现ext1.prm没有文件头名
加上Extract ext1
GGSCI (a-test30 as ogg@qatest30) 27> view params ext1
Extract ext1
UserId ogg, Password ogg
ExtTrail ./dirdat/eo
GetTruncates
TranLogOptions ExcludeUser ogg
--DDL Include All
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOptions AddTranData Retryop Retrydelay 10 Maxretries 10

Table TMP.test1;

Table TMP.test2;

Table TMP.test3;

再去启动又继续报错OGG-00529
016-10-28 17:02:15  ERROR   OGG-00529  DDL Replication is enabled but table goldengate.GGS_DDL_HIST is not found. Please check DDL installation in the database.
2016-10-28 17:02:15  ERROR   OGG-01668  PROCESS ABENDING.

分析：
查阅各种资料分析可能原因
根据日志可以看出DDL复制操作已经打开，但没有找到安装复制DDL执行脚本产生的表GGS.GGS_DDL_HIST导致的故障，会不会是因为安装复制DDL是使用用户ogg，执行脚本后会在该用户产生跟踪goldengate运行的表，所以要实现支持DDL操作，在参数文件中登录数据库必须使用GGDLL和对应的密码登录。
【针对rac多节点：USERID ogg@qatest30,PASSWORD ogg】

实际原因：
原因是配置源端mgr参数文件时用的是用户名和密码是ogg,且配置支持DDL复制时输入的用户名也是ogg。但目标端mgr参数文件中配置的用户名密码是goldengate,源端目标端不匹配，找不到验证信息。


解决方法：

卸载ogg，并使支持DDL功能失效
运行脚本即可

注意：一定要在ogg软件安装目录下登陆数据库，运行脚本，否则是打不开文件的~~
SQL>  @ddl_disable.sql
SP2-0310: 无法打开文件 "ddl_disable.sql"

[oracle@a-test30 softogg1]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 11:35:04 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @ddl_disable.sql
触发器已更改
SQL> @ddl_remove.sql
DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SQL>
SQL> @marker_remove.sql
Marker removal script.
WARNING: this script removes all marker objects and data.
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
Enter Oracle GoldenGate schema name:ogg
PL/SQL 过程已成功完成。
序列已删除。
表已删除。
Script complete.

重新安装：
[oracle@a-test30 softogg1]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 14:44:53 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
WARNING: Tablespace OGG does not have AUTOEXTEND enabled.
declare
*
第 1 行出现错误:
ORA-20783:
ORA-20783:
Oracle GoldenGate DDL Replication setup:
*** Please move GOLDENGATE to its own tablespace
ORA-06512: 在 line 34
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
报错说需要将goldengate迁移到自己的表空间上
改就是了：
[oracle@a-test30 softogg1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 15:57:12 2016
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> create tablespace goldengate datafile '/oradata01/qatest30/goldengate_data_01.dbf' size 30g autoextend off;
表空间已创建。
SQL> alter user ogg default tablespace goldengate;
用户已更改。
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
WARNING: Tablespace OGG does not have AUTOEXTEND enabled.
Using GOLDENGATE as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
CLEAR_TRACE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
CREATE_TRACE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
TRACE_PUT_LINE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
INITIAL_SETUP STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX  PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
SYS.DDLCTXINFO  PACKAGE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
SYS.DDLCTXINFO  PACKAGE BODY STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/qatest30/qatest30/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:goldengate
已写入 file role_setup_set.txt
PL/SQL 过程已成功完成。
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE to goldengate;
授权成功。
SQL> @ddl_enable.sql
触发器已更改

可以了，再去启动，报错OGG-00014
2016-11-01 17:26:46  ERROR   OGG-00014  Unrecognized parameter: chema. Parameter could be misspelled or unsupported.
2016-11-01 17:26:46  ERROR   OGG-01668  PROCESS ABENDING.
2016-11-01 17:26:46  WARNING OGG-00543  Unexpected threading library failure. Error code 16 (Device or resource busy).
报错说不能识别参数，chema，怀疑是./globals参数有问题

GGSCI (a-test30 as goldengate@qatest30) 10> view Params ./GLOBALS
chema goldengate
CheckpointTable goldengate.checkpoint
UnlockedTrailFiles
果然写错了，chema前面少东西，真是粗心。。。
重新编辑：
GGSCI (a-test30 as goldengate@qatest30) 12> view params ./GLOBALS
GGSchema goldengate
CheckpointTable goldengate.checkpoint
UnlockedTrailFiles
编辑完后需要删除checkpoint table再重新添加

GGSCI (a-test30) 5> DbLogin UserId goldengate, Password goldengate
Successfully logged into database.
GGSCI (a-test30 as goldengate@qatest30) 6> Add CheckpointTable
No checkpoint table specified. Using GLOBALS specification (goldengate.checkpoint)...
ERROR: Failed creating checkpoint table goldengate.checkpoint.
OCI Error ORA-00955: 鍚嶇О宸茬敱鐜版湁瀵硅薄浣跨敤 (status = 955), SQL <CREATE TABLE goldengate.checkpoint (  group_name VARCHAR2(8) NOT NULL,  group_key NUMBER(19) NOT NULL,  seqno NUMBER(10),  rba NUMBER(19) NOT NULL,  audit_ts VARCHAR2(29),  create_ts DATE NOT NULL,  last_update_ts DATE NOT NULL,  current_dir VARCHAR2(255) NOT NULL,  log_bsn VARCHAR2(128),  log_csn VARCHAR2(128),  log_xid VARCHAR2(128),  log_cmplt_csn VARCHAR2(128),  log_cmplt_xids VARCHAR2(2000),  version NUMBER(3),  PRIMARY KEY (group_name, group_key)) PCTFREE 60>.
GGSCI (a-test30 as goldengate@qatest30) 7> delete CheckpointTable
No checkpoint table specified. Using GLOBALS specification (goldengate.checkpoint)...
This checkpoint table may be required for other installations.  Are you sure you want to delete this checkpoint table? yes
Successfully deleted checkpoint table goldengate.checkpoint.
GGSCI (a-test30 as goldengate@qatest30) 8> exit
[oracle@a-test30 softogg1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (a-test30) 1>
GGSCI (a-test30) 1> DbLogin UserId goldengate, Password goldengate
Successfully logged into database.
GGSCI (a-test30 as goldengate@qatest30) 2> Add CheckpointTable
No checkpoint table specified. Using GLOBALS specification (goldengate.checkpoint)...
Successfully created checkpoint table goldengate.checkpoint.

添加完成后一定要退出重新进来
启动进程，终于可以了

GGSCI (a-test30 as goldengate@qatest30) 10> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:42:42      00:00:09   
EXTRACT     RUNNING     PUMP1       00:00:00      00:42:34   
总结：复制粘贴之后一定要再确认一遍没有问题，出现报错可能都是因为一些基本参数没有配置正确。