摘要

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>