作者归档：Lynn

关于Lynn

Oracle DBA is my Occupation dream, Focus on Oracle, Love the life! I hope I can make a difference in this industry.

Goldengate DDL Recreate Solution

4条回复

在安装有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.

New Site New Begining

4条回复

自开始学习Oracle，一路走来，算不上艰辛，但也不轻松。之所以这样说，和我关系好的朋友都能猜到，为了进入这个行业，可以说自己投入了比较大的精力和money，但结果还是没有达到我的预期，不过这都没关系，人生在世，不如意之事十之八九，倘若事事如意，又岂能谓之为生活！

早在Oracle入门时，自己会偶尔看点有关Oracle的书籍，然后去别人的技术博客上逛逛，看到有些知识及见解觉得很不错，就随便记录下来，久而久之，零零散散的这些知识越来越多，但是自己却懒于梳理，最终被遗忘在某一个角落！

后来觉得自己可以把这些零散的知识和学习过程记录在博客上，因此便开始在新浪安家，记录的也都只是关于Oracle的，不涉及任何其他的东西，大概持续了半年时间，开始觉得这些运行商提供的站点服务太粗糙，毕竟是免费的，所以各种广告，而且所提供的博客风格及和版式都不尽人意，最后也就荒废掉了！

在厌倦了新浪无休止的广告和长到让人看见就恶心的域名串，至此终于有尝试搭建自己站点的想法，原因其实很简单，有一个自己喜欢的域名，有一个自己所喜欢风格和版式的站点，有一个自己可以发牢骚的空间。考虑到自己没有任何搭建独立站点的经验（虽然我是软件工程专业毕业，除了会一点C，其他一窍不通，提到此无比汗颜，要不我可能不会在临毕业之际考虑做Oracle DBA），于是便开始Google，甚至百度去了解建站的步骤。

正如刚提到的，作为软件工程毕业的学生，我真的给这个专业拖后腿了，编程能力基本为0，后来在Beanbee的建议下决定使用WordPress建站。今年4月份在godaddy买了域名oracldream.com，用DNSPod做解析，又在衡天买了美国的服务器，看似一切工作都就绪了，后来却因为工作的事情阁下了，直到前不久工作不是很忙，想起这事还一直搁着，就想趁这个机会把这个站点搭起来。经过4天的时间，自己的站点终于上线，这个过程可谓苦中作乐，痛苦的是在茫茫WordPress主题中寻找一款自己满意的主题谈何容易，而且还要在此基础上修改站点风格版式，快乐的是当看着自己的站点上线，域名是自己喜欢的，站点风格和版式也是自己喜欢的，这种满足感不言而喻！

如今自己的站点正式上线，在这里我会记录自己的成长，包括工作、学习、生活、情感，甚至有时可能还会吐槽，虽然现在还没有什么内容，但是这会是一个从无到有的过程，就像亲眼目睹一个孩子的成长一样温暖！