分类目录归档:Middleware

Goldengate DDL Recreate Solution

在安装有Goldengate并开启DDL捕捉进程的Oracle数据库升级过程中,其实施过程应如下:
1.先升级目标端数据库
2.再升级源端数据库
无论是在目标端还是源端升级数据库之前,都应将开启的Goldengate DDL捕捉进程先disable掉,然后再按照正确的步骤进行数据库的升级,升级完成后再开启Goldengate DDL捕捉进程,否则会在如下两个地方出现报错:
1.软件版本升级安装正常,在执行升级数据库脚本catupgrd.sql时报如下类似错误:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04045: errors during recompilation/revalidation of GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATIO

2. 执行升级数据库脚本catupgrd.sql时未报错,但在执行重编译无效对象脚本utlrp.sql时报如下错:

DECLARE
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"

如果你的运气特别好,在上述两处都没有出现类似报错(其实这基本是不可能的,因为即便你在执行升级数据库脚本catupgrd.sql没有报错,也肯定会在执行重编译无效对象脚本utlrp.sql时报错,前面提到的两处都未报错,更多的可能性是在执行重编译无效对象脚本utlrp.sql报错只是被你忽略掉而已,又或是粗心没看见重编译无效对象时抛出的这个错误,认为数据库升级完成),但当你登录数据库后执行DDL操作时,你同样而且肯定还是会遇见上述类似报错:

SQL> create table objects as select * from all_objects;

create table objects as select * from all_objects;
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"

当你在执行DDL操作时,你会遇到上述报错,第一反应就是GOLDENGATE.DDLREPLICATION包失效,只需重编译一下这个包即可,于是你可能尝试如下的办法,很不幸依然报上述类似错误:

SQL> select owner,object_name,object_type,status from dba_objects where object_name='DDLREPLICATION';

OWNER                          OBJECT_NAME          OBJECT_TYPE         STATUS
------------------------------ -------------------- ------------------- -------
GOLDENGATE                     DDLREPLICATION       PACKAGE             VALID
GOLDENGATE                     DDLREPLICATION       PACKAGE BODY        INVALID

SQL> ALTER PACKAGE GOLDENGATE.DDLREPLICATION COMPILE BODY;

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"
ORA-06512: at line 870
ORA-04045: errors during recompilation/revalidation of
GOLDENGATE.DDLREPLICATION
ORA-04064: not executed, invalidated
ORA-04064: not executed, invalidated package body "GOLDENGATE.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GOLDENGATE.DDLREPLICATION"

这时解决的办法很简单:将目标端及源端的Goldengate禁用掉其DDL功能 ,然后再执行重新编译无效对象脚本utlrp.sql,编译成功后在开启目标端及源端的Goldengate DDL功能,登陆数据库执行DDL操作成功,具体过程操作过程如下(最好先目标端执行完再去源端操作):
1)切换到Goldengate安装目录下,并且以SYS用户登陆数据库执行ddl_disable.sql脚本

# su - ogg
$ sqlplus / as sysdba
SQL> @ddl_disable.sql

2)切换到oracle用户下,以sys用户登录数据执行重编译无效对象utlrp.sql脚本

# su - oracle
SQL> @?/rdbms/admin/utlrp.sql

3)编译无效对象成功之后,再切换到Goldengate安装目录下,并且以SYS用户登陆数据库执行ddl_enable.sql脚本:

# su - ogg
$ sqlplus / as sysdba
SQL> @ddl_enable.sql

到此登录数据库后做DDL操作,报错不在出现,问题解决!

SQL> create table objects as select * from all_objects;

Table created.

以上解决思路很简单,但却存在很大缺陷,设想如果这是一个7×24小时业务系统,并且必须保证源端与目标端数据库的复制链路不能中断(其实这样的业务系统肯定是存在的,而且还很常见,比如国家电网下属各个省电力公司的某些数据库就是通过Goldengate复制到异地做灾备(出现链路中断会被考核扣分),当出现上述问题:由于在数据库升级时未禁用开启Goldengate DDL功能而导致升级完成后登录数据库无法执行任何DDL操作),那么以上解决方法肯定不行,因为这样的操作必定会导致其复制链路中断,那么有没有好的办法解决这个问题呢?方法肯定是有的,但是不保证100%成功,至少在前不久我就遇到了这样的事情,按照如下操作还有Oracle Support给的方法,链路还是中断了6分钟,结果你懂得!
这里的处理方案有两个版本,方案1来源于认识的一位经验丰富的OGG工程师,方案2来源于MOS上Oracle Support 的建议。
方案一:
1.1卸载DDL配置
依次在OGG的根目录执行,切换到Goldengate安装目录下,并且按照顺序以SYS用户依次执行以下脚本:

# su - ogg
$ sqlplus / as sysdba
SQL> @/goldengate/ddl_disable.sql
SQL> @/goldengate/ddl_remove.sql
SQL> @/goldengate/marker_remove.sql

注:在执行上述脚本的时候,会提示输入安装时所指定的schema名称:goldengate
1.2停止数据库的所有session
OGG的DDL对象安装时不能有运行的sessoin存在,请DBA执行清理所有数据库中的session,建议有条件先执行停止业务,并关闭oracle的Listener!
1.3重新建立OGG的DDL对象

# su - ogg
$ sqlplus / as sysdba
SQL> @/goldengate/marker_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @ddl_setup.sql 【如果存在业务session锁表,这一步会执行不成功】
Enter GoldenGate schema name:goldengate
SQL> @role_setup.sql
Grant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TOwhereis the user assigned to the GoldenGate processes.

注意这里的提示:它需要你手工将这个GGS_GGSUSER_ROLE指定给你的extract所使用的数据库用户(即参数文件里面通过userid指定的用户),可以到SQL*Plus下执行类似的SQL:
GRANT GGS_GGSUSER_ROLE TO goldengate;
这里的goldengate是extract使用的用户,如果你有多个extract,使用不同的数据库用户,则需要重述以上过程全部赋予GGS_GGSUSER_ROLE权限。
1.4重建失败处理
如果重建DDL失败,则数据库执行DDL操作正常,但这些操作将不在被Goldengate捕获,但不影响Goldengate的DDL数据同步。但如果有存在DDL操作,会因为没有同步,导致灾备端中断,需人工进行DDL修改,最好的方式是做一次全新初始化。
方案二:
附两张截图,就当是丰富一下站点!

文章的最后我附加一篇MOS上的原文供大家参考,其原文内容如下: 
[Do I Need To Disable The GoldenGate DDL Trigger Before An Oracle DB Upgrade or PSU patching? (ID 971222.1)]
Issue:

If you are performing any Oracle database upgrades, for example CPU/PSU patch upgrade, and you are currently capturing Oracle DDL operations with GoldenGate, disable the GoldenGate DDL trigger before the upgrade and then enable it after the upgrade. Use the ddl_disable and ddl_enable scripts in the GoldenGate installation directory for this purpose.
Solution Description:
Patches and upgrades can modify Oracle’s internal tables and views. This can cause stored procedures that call them to be invalidated. All dependent objects are invalidated as well.
The ALTER COMPILE is DDL, and because parts of the DDL package are invalidated, it cannot execute. Because Oracle patches and upgrades are generally NOT replicated by GoldenGate (replication is stopped, then upgrades or patches are done on both source and target), there should be no gaps. Also, because upgrades and patches generally operate on Oracle-reserved objects (which GoldenGate filters out), there should not be any patch-related DDL operations that are replicated, even if for some reason replication is not stopped during the patch.
To apply the Oracle patch:
Before applying a database patch to an environment that has an active GoldenGate DDL trigger, follow this procedure:
1. Log into SQL*Plus as sysdba and execute the ddl_disable script.
2. Apply the Oracle patch.
3. If you are still on the same major Oracle version (for example, you went from 9.2.0.6 to 9.2.0.8), enable the trigger again by executing the ddl_enable script as sysdba. If you are upgrading to a newer major version (for example, from 10.1.x to 10.2.x), download and install a new GoldenGate version before re-enabling the DDL trigger.
If you don’t follow the procedure of disabling the DDL trigger before upgrade or patching, after the Database version upgrade or CPU/PSU patch complete, you will run into various issues with GoldenGate DDL replication package, typical error messages looks like below:
OCI error (4045-ORA-04045: errors during recompilation/revalidation of PUBLIC.ALL_TAB_COLUMNS
ORA-04064: not executed, invalidated  ORA-04064: not executed, invalidated package body “GGS_DDL.DDLREPLICATION” ORA-06508: PL/SQL: could not find program unit being called: “GGS_DDL.DDLREPLICATION” ORA-06512: at line 837 ORA-04064: not executed, invalidated package body “GGS_DDL.DDLREPLICATION” ORA-06508: PL/SQL: could not find program unit being called: “GGS_DDL.DDLREPLICATION” ORA-06508: PL/SQL: could not find program unit being called: “GGS_DDL.DDLREPLICATION” ORA-06512: at line 837 ORA-04045: errors during recompilation/revalidation of GGS_DDL.DDLREPLICATION ORA-04064: not executed, invalidated  ORA-04064: not executed, invalidated package body “GGS_DDL.DDLREPLICATION” ORA-06508: PL/SQL: could not find program unit being called: “GGS_DDL.DDLREPLICATION” ORA-06512: at line 837 ORA-04064: not executed, invalidated package body “GGS_DDL.DDLREPLICATION” ORA-06508: PL/SQL: could not find program unit being called: “GGS_DDL.DDLREPLI) initializing query to obtain NUMBER precision (table: ‘xxxx.yy’, column: ‘Cc’), try to fix this issue in order to avoid possible fatal error.
If that happens, you have to reinstall the DDL replication package.  Please see detail on how to install DDL replication package in the installation guide.