暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

一次Oracle升级后的ORA-04045故障处理(OGG重建DDL功能重建)

原创 virvle 2022-11-08
2484

摘要

11.2.0.1升级到11.2.0.4后,进行验证,建表报错: ORA-04045 & ORA-00604 & ORA-06508
最后通过OGG重建DDL功能解决

报错详情如下

SQL> create table t(a int); create table t(a int) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of GGS.DDLREPLICATION ORA-06508: PL/SQL: could not find program unit being called: "GGS.DDLREPLICATION" ORA-06512: at line 1181 ORA-04067: not executed, package body "GGS.DDLREPLICATION" does not exist ORA-06508: PL/SQL: could not find program unit being called: "GGS.DDLREPLICATION" ORA-06508: PL/SQL: could not find program unit being called: "GGS.DDLREPLICATION" ORA-06512: at line 1042 ORA-04045: errors during recompilation/revalidation of GGS.DDLREPLICATION ORA-04067: not executed, package body "GGS.DDLREPLICATION" does not exist ORA-06508: PL/SQL: could not find program unit being called: "GGS.DDLREPLICATION"

引起原因

数据库升级,未先关掉ogg的ddl功能导致(11.2.0.1升级到11.2.0.4)

解决办法

PS:若ogg进程还在运行,先关掉(使用stop xx 命令)

GGSCI (dbtest) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED EXTRACT ABENDED DUMP 00:00:03 132:08:27 EXTRACT ABENDED EXT1 00:00:00 132:08:20 REPLICAT STOPPED REP 71:33:43 47:42:56 GGSCI (dbtest) 2>

从ogg的主目录进入sqlplus, 使用ddl_disable.sql禁用DDL触发器

[oracle@dbtest ggs]$ pwd /mnt/oggs/ggs [oracle@dbtest ggs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 8 16:14:16 2020 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 Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @ddl_disable.sql Trigger altered.

使用ddl_remove.sql移除DDL触发器,DDL历史和标记表,这个脚本会生成一个ddl_remove_spool.txt文件,记录了脚本的输出,还有一个ddl_remove_set.txt文件,记录了当前的用户环境设置,以防debug之用。

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.

使用marker_remove.sql 移除对oracle GoldenGet marker系统的支持

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:ggs PL/SQL procedure successfully completed. Sequence dropped. Table dropped. Script complete.

使用marker_setup.sql 安装marker

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:ggs Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete.

给OGG权限(重建不需要执行)

GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;

安装DDL触发器

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:ggs 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. Using GGS 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 GGS 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 ------------------------------------------------------------------------------------------------------------------------ /mnt/vdb1/oracle/diag/rdbms/nip/nip/trace/ggs_ddl_trace.log Analyzing installation status... VERSION OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316 STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete.

安装角色

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:ggs Wrote file role_setup_set.txt PL/SQL procedure successfully completed. 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.

启动DDL触发器

SQL> @ddl_enable.sql Trigger altered.

验证测试(新建表->插入数据->查询->清空表->删除表)

SQL> create table t(a int); Table created. SQL> insert into t value(1); insert into t value(1) * ERROR at line 1: ORA-00928: missing SELECT keyword SQL> insert into t values (1); 1 row created. SQL> commit; Commit complete. SQL> select * from t; A ---------- 1 1 row selected. SQL> truncate table t; Table truncated. SQL> select * from t; no rows selected SQL> drop table t; Table dropped. SQL> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist SQL>
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论