摘要
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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。