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

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 oggExtTrail ./dirdat/eoGetTruncatesTranLogOptions ExcludeUser ogg --DDL Include AllDDL &INCLUDE MAPPED OBJTYPE 'table' &INCLUDE MAPPED OBJTYPE 'index' &EXCLUDE OPTYPE COMMENTDDLOptions AddTranData Retryop Retrydelay 10 Maxretries 10Table TMP.test1;Table TMP.test2;Table TMP.test3[oracle@a-test30 dirprm]$ more pump1.prm Extract pump1PassThruRmtHost 192.168.10.61, MgrPort 7809RmtTrail ./dirdat/goTable TMP.test1;Table TMP.test2;Table TMP.test3;

发现ext1.prm没有文件头名

加上Extract ext1

GGSCI (a-test30 as ogg@qatest30) 27> view params ext1Extract ext1UserId ogg, Password oggExtTrail ./dirdat/eoGetTruncatesTranLogOptions ExcludeUser ogg --DDL Include AllDDL &INCLUDE MAPPED OBJTYPE 'table' &INCLUDE MAPPED OBJTYPE 'index' &EXCLUDE OPTYPE COMMENTDDLOptions AddTranData Retryop Retrydelay 10 Maxretries 10Table 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.sqlSP2-0310: 无法打开文件 "ddl_disable.sql"[oracle@a-test30 softogg1]$ !sqlsqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 11:35:04 2016Copyright (c) 1982, 2009, Oracle.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @ddl_disable.sql触发器已更改SQL> @ddl_remove.sqlDDL 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:oggWorking, please wait ...Spooling to file ddl_remove_spool.txtScript complete.SQL> SQL> @marker_remove.sqlMarker 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:oggPL/SQL 过程已成功完成。序列已删除。表已删除。Script complete.

重新安装：

[oracle@a-test30 softogg1]$ !sqlsqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 14:44:53 2016Copyright (c) 1982, 2009, Oracle.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @marker_setup.sqlMarker setup scriptYou 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:goldengateMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup scriptVerifying 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:goldengateWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking 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 tablespaceORA-06512: 在 line 34从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options 断开报错说需要将goldengate迁移到自己的表空间上改就是了：[oracle@a-test30 softogg1]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 1 15:57:12 2016Copyright (c) 1982, 2009, Oracle.  All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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.sqlOracle GoldenGate DDL Replication setup scriptVerifying 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:goldengateWorking, please wait ...Spooling to file ddl_setup_spool.txtChecking 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 GOLDENGATECLEAR_TRACE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsCREATE_TRACE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsTRACE_PUT_LINE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsINITIAL_SETUP STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLREPLICATION PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL IGNORE TABLE-----------------------------------OKDDL IGNORE LOG TABLE-----------------------------------OKDDLAUX  PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDLAUX PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsSYS.DDLCTXINFO  PACKAGE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsSYS.DDLCTXINFO  PACKAGE BODY STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL HISTORY TABLE-----------------------------------OKDDL HISTORY TABLE(1)-----------------------------------OKDDL DUMP TABLES-----------------------------------OKDDL DUMP COLUMNS-----------------------------------OKDDL DUMP LOG GROUPS-----------------------------------OKDDL DUMP PARTITIONS-----------------------------------OKDDL DUMP PRIMARY KEYS-----------------------------------OKDDL SEQUENCE-----------------------------------OKGGS_TEMP_COLS-----------------------------------OKGGS_TEMP_UK-----------------------------------OKDDL TRIGGER CODE STATUS:Line/pos             Error-------------------- -----------------------------------------------------------------No errors            No errorsDDL TRIGGER INSTALL STATUS-----------------------------------OKDDL TRIGGER RUNNING STATUS----------------------------------------------------------------------ENABLEDSTAYMETADATA IN TRIGGER----------------------------------------------------------------------OFFDDL TRIGGER SQL TRACING----------------------------------------------------------------------0DDL TRIGGER TRACE LEVEL----------------------------------------------------------------------0LOCATION OF DDL TRACE FILE------------------------------------------------------------------------------------------------------------------------/oracle/diag/rdbms/qatest30/qatest30/trace/ggs_ddl_trace.logAnalyzing installation status...VERSION OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1STATUS OF DDL REPLICATION------------------------------------------------------------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software componentsScript complete.SQL> SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo 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.txtPL/SQL 过程已成功完成。Role setup script completeGrant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:GRANT GGS_GGSUSER_ROLE TO where  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 ./GLOBALSchema goldengateCheckpointTable goldengate.checkpointUnlockedTrailFiles

果然写错了，chema前面少东西，真是粗心。。。

重新编辑：

GGSCI (a-test30 as goldengate@qatest30) 12> view params ./GLOBALSGGSchema goldengateCheckpointTable goldengate.checkpointUnlockedTrailFiles

编辑完后需要删除checkpoint table再重新添加

GGSCI (a-test30) 5> DbLogin UserId goldengate, Password goldengateSuccessfully logged into database.GGSCI (a-test30 as goldengate@qatest30) 6> Add CheckpointTableNo checkpoint table specified. Using GLOBALS specification (goldengate.checkpoint)...ERROR: Failed creating checkpoint table goldengate.checkpoint.OCI Error ORA-00955: 鍚嶇О宸茬敱鐜版湁瀵硅薄浣跨敤 (status = 955), SQL .GGSCI (a-test30 as goldengate@qatest30) 7> delete CheckpointTableNo 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? yesSuccessfully deleted checkpoint table goldengate.checkpoint.GGSCI (a-test30 as goldengate@qatest30) 8> exit[oracle@a-test30 softogg1]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBOLinux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25Operating 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 goldengateSuccessfully logged into database.GGSCI (a-test30 as goldengate@qatest30) 2> Add CheckpointTableNo checkpoint table specified. Using GLOBALS specification (goldengate.checkpoint)...Successfully created checkpoint table goldengate.checkpoint.

添加完成后一定要退出重新进来

启动进程，终于可以了

GGSCI (a-test30 as goldengate@qatest30) 10> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     EXT1        00:42:42      00:00:09    EXTRACT     RUNNING     PUMP1       00:00:00      00:42:34

总结：复制粘贴之后一定要再确认一遍没有问题，出现报错可能都是因为一些基本参数没有配置正确。