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
总结:复制粘贴之后一定要再确认一遍没有问题,出现报错可能都是因为一些基本参数没有配置正确。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。