数据库升级和DDL trigger

联系:QQ(5163721)

标题:数据库升级和DDL trigger

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

升级数据库时,比如从9i或者10g升级到11.2,官方文档强调需要禁用DDL trigger。
这里尤其要说的是OGG的DDL TRIGGER,如果升级前不禁用,很坑爹,具体参见《OGG DDL trigger造成升级数据库后大量SYS对象失效和数据库DDL失效
那么什么是DDL trigger呢?
我们来做个测试:

21:07:17 @LUNAR>CREATE OR REPLACE TRIGGER Lunar_ddl_test
   BEFORE DROP ON DATABASE
21:08:54   2  21:08:54   3  BEGIN
   IF LOWER (ora_dict_obj_name ()) = 'test'
21:08:54   4  21:08:54   5     THEN
21:08:54   6        raise_application_error (num      => -20000,
21:08:54   7                                 msg      =>    'Lunar test '
21:08:54   8                                             || ora_dict_obj_name ()
21:08:54   9                                             || ' ?!!!!!'
21:08:54  10                                             || 'Lunar test.....'
21:08:54  11                                );
21:08:54  12     END IF;
21:08:54  13  END;
21:08:54  14  /       

Trigger created.

Elapsed: 00:00:00.01
21:09:26 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where TRIGGER_NAME='LUNAR_DDL_TEST';

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
SYS                            LUNAR_DDL_TEST                 BEFORE EVENT

Elapsed: 00:00:00.05
21:09:35 @LUNAR>

这里我们看到了,TRIGGER_TYPE为BEFORE EVENT,这个显然是DDL trigger。
那么还有哪些类似的呢,我们来查看一下:


20:59:14 @LUNAR>select distinct TRIGGER_TYPE from dba_triggers;

TRIGGER_TYPE
----------------
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
BEFORE EVENT
AFTER STATEMENT
AFTER EVENT
INSTEAD OF

7 rows selected.

Elapsed: 00:00:00.00
20:59:19 @LUNAR>

上面trigger type,从名字也很容易看出,至少两种: BEFORE EVENT和AFTER EVENT。
如果查询trigger event,你会看到有明显的DDL字样的类型的trigger:

21:10:08 @LUNAR>select distinct TRIGGERING_EVENT from dba_triggers;

TRIGGERING_EVENT
----------------------------------------
UPDATE OR DELETE
DROP
ALTER OR RENAME
INSERT OR UPDATE OR DELETE
INSERT OR UPDATE
DELETE
UPDATE
DDL
TRUNCATE
RENAME
CREATE OR ALTER OR DROP OR RENAME
ALTER
CREATE
CREATE OR ALTER
INSERT
DROP OR TRUNCATE
STARTUP

17 rows selected.

Elapsed: 00:00:00.08
21:10:35 @LUNAR>

上述具体含义可以查询Oracle官方文档,大概解释如下:

事件				允许的时机	说明
STARTUP				AFTER		启动数据库实例之后触发
SHUTDOWN			BEFORE		关闭数据库实例之前触发(非正常关闭不触发)
SERVERERROR			AFTER		数据库服务器发生错误之后触发
LOGON				AFTER		成功登录连接到数据库后触发
LOGOFF				BEFORE		开始断开数据库连接之前触发
CREATE				BEFORE,AFTER	在执行CREATE语句创建数据库对象之前、之后触发
DROP				BEFORE,AFTER	在执行DROP语句删除数据库对象之前、之后触发
ALTER				BEFORE,AFTER	在执行ALTER语句更新数据库对象之前、之后触发
DDL				BEFORE,AFTER	在执行大多数DDL语句之前、之后触发
GRANT				BEFORE,AFTER	执行GRANT语句授予权限之前、之后触发
REVOKE				BEFORE,AFTER	执行REVOKE语句收权限之前、之后触犯发
RENAME				BEFORE,AFTER	执行RENAME语句更改数据库对象名称之前、之后触犯发
AUDIT / NOAUDIT	BEFORE,AFTER	执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

那么文档中所说的禁用DDL trigger到底是哪一种呢?下面我们测试一下,如果不禁用DDL trigger会是什么情况。
首先我们查询当前数据库中的trigger_type in (‘BEFORE EVENT’,'AFTER EVENT’)的DDL:

21:10:35 @LUNAR>drop TRIGGER Lunar_ddl_test;

Trigger dropped.

Elapsed: 00:00:00.00
21:11:06 @LUNAR>
21:17:50 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where trigger_type in ('BEFORE EVENT','AFTER EVENT');

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
SYS                            LOGMNRGGC_TRIGGER              BEFORE EVENT
SYS                            AW_TRUNC_TRG                   AFTER EVENT
SYS                            AW_REN_TRG                     AFTER EVENT
SYS                            AW_DROP_TRG                    AFTER EVENT
WMSYS                          NO_VM_DDL                      BEFORE EVENT
WMSYS                          NO_VM_DROP_A                   AFTER EVENT
SYS                            CDC_ALTER_CTABLE_BEFORE        BEFORE EVENT
SYS                            CDC_CREATE_CTABLE_AFTER        AFTER EVENT
SYS                            CDC_CREATE_CTABLE_BEFORE       BEFORE EVENT
SYS                            CDC_DROP_CTABLE_BEFORE         BEFORE EVENT
EXFSYS                         EXPFIL_RESTRICT_TYPEEVOLVE     BEFORE EVENT
EXFSYS                         EXPFIL_ALTEREXPTAB_MAINT       AFTER EVENT
SYS                            XDB_PI_TRIG                    BEFORE EVENT
EXFSYS                         EXPFIL_DROPOBJ_MAINT           BEFORE EVENT
EXFSYS                         EXPFIL_DROPUSR_MAINT           AFTER EVENT
EXFSYS                         RLMGR_TRUNCATE_MAINT           BEFORE EVENT
MDSYS                          SDO_DROP_USER                  AFTER EVENT
MDSYS                          SDO_ST_SYN_CREATE              BEFORE EVENT
MDSYS                          SDO_TOPO_DROP_FTBL             BEFORE EVENT
MDSYS                          SDO_GEOR_BDDL_TRIGGER          BEFORE EVENT
MDSYS                          SDO_GEOR_ADDL_TRIGGER          AFTER EVENT
MDSYS                          SDO_NETWORK_DROP_USER          AFTER EVENT
SYSMAN                         MGMT_STARTUP                   AFTER EVENT

23 rows selected.

Elapsed: 00:00:00.04

下面我们只把TRIGGERING_EVENT为DDL的trigger进行disable,然后执行数据库升级操作,之后来看看效果:

21:19:03 @LUNAR>select OWNER,TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,status from dba_triggers where  TRIGGERING_EVENT like 'DDL%';     
OWNER                          TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT                    STATUS
------------------------------ ------------------------------ ---------------- ----------------------------------- --------
SYS                            GGS_DDL_TRIGGER_BEFORE         BEFORE EVENT     DDL                                 DISABLED
MDSYS                          SDO_GEOR_BDDL_TRIGGER          BEFORE EVENT     DDL                                 DISABLED
MDSYS                          SDO_GEOR_ADDL_TRIGGER          AFTER EVENT      DDL                                 DISABLED
SYS                            LOGMNRGGC_TRIGGER              BEFORE EVENT     DDL                                 DISABLED

Elapsed: 00:00:00.04
21:19:07 @LUNAR>

升级过程后续会单独写一个BLOG,这里不赘述。

升级过程没有任何报错,完美升级结束后,我们来检查一下数据库无效对象,发现确实有4个无效对象。
不过,数据库所有组件正如我们看到的升级过程一样,都是有效的,且已经升级成功:

19:04:56 sys@LUNAR>select owner,object_name,object_type,status from dba_objects where status != 'VALID' and owner in ('SYS','SYSTEM'); 

OWNER                          OBJECT_NAME                                        OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------- ------------------- -------
SYS                            DDLCTXINFO                                         PACKAGE BODY        INVALID

Elapsed: 00:00:00.03
19:04:56 sys@LUNAR>

19:05:07 sys@LUNAR>SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, SUBSTR(comp_name,1,35) comp_name FROM dba_registry;

COMP_ID                        STATUS                 VERSION              COMP_NAME
------------------------------ ---------------------- -------------------- -----------------------------------
OWB                            VALID                  11.2.0.3.0           OWB
APEX                           VALID                  3.2.1.00.1           Oracle Application Express
EM                             VALID                  11.2.0.4.0           Oracle Enterprise Manager
AMD                            VALID                  11.2.0.4.0           OLAP Catalog
SDO                            VALID                  11.2.0.4.0           Spatial
ORDIM                          VALID                  11.2.0.4.0           Oracle Multimedia
XDB                            VALID                  11.2.0.4.0           Oracle XML Database
CONTEXT                        VALID                  11.2.0.4.0           Oracle Text
EXF                            VALID                  11.2.0.4.0           Oracle Expression Filter
RUL                            VALID                  11.2.0.4.0           Oracle Rules Manager
OWM                            VALID                  11.2.0.4.0           Oracle Workspace Manager
CATALOG                        VALID                  11.2.0.4.0           Oracle Database Catalog Views
CATPROC                        VALID                  11.2.0.4.0           Oracle Database Packages and Types
JAVAVM                         VALID                  11.2.0.4.0           JServer JAVA Virtual Machine
XML                            VALID                  11.2.0.4.0           Oracle XDK
CATJAVA                        VALID                  11.2.0.4.0           Oracle Database Java Packages
APS                            VALID                  11.2.0.4.0           OLAP Analytic Workspace
XOQ                            VALID                  11.2.0.4.0           Oracle OLAP API

18 rows selected.

Elapsed: 00:00:00.01
19:05:07 sys@LUNAR>

19:05:07 sys@LUNAR>select count(*) from dba_objects where status='INVALID';

        COUNT(*)
----------------
               4

Elapsed: 00:00:00.04
19:05:24 sys@LUNAR>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

OWNER                          OBJECT_TYPE                 COUNT(*)
------------------------------ ------------------- ----------------
SYS                            PACKAGE BODY                       1
OGG                            PACKAGE BODY                       1
OGG                            FUNCTION                           1
OGG                            PROCEDURE                          1

Elapsed: 00:00:00.03
19:05:28 sys@LUNAR>select owner, object_name,object_type from dba_objects where status ='INVALID';

OWNER                          OBJECT_NAME                                        OBJECT_TYPE
------------------------------ -------------------------------------------------- -------------------
SYS                            DDLCTXINFO                                         PACKAGE BODY
OGG                            DDLORA_GETLOBS                                     PROCEDURE
OGG                            FILTERDDL                                          FUNCTION
OGG                            DDLAUX                                             PACKAGE BODY

Elapsed: 00:00:00.02
19:05:33 sys@LUNAR>

上面看到有3个是OGG的,不用想,这个坑爹的OGG DDL功能造成的,另外一个SYS的DDLCTXINFO,这个比较眼生,检查它是什么来路:


20:02:47 sys@LUNAR>20:02:47 sys@LUNAR>select dbms_metadata.get_ddl('PACKAGE','DDLCTXINFO','SYS') from dual;


  CREATE OR REPLACE PACKAGE "SYS"."DDLCTXINFO" AS
    PROCEDURE setCtxInfo(objNum  IN NUMBER, baseObjNum IN NUMBER,
                         objUserId IN NUMBER, baseObjUserId IN NUMBER,
                         baseObjProperty IN NUMBER) ;
END DDLCtxInfo ;
CREATE OR REPLACE PACKAGE BODY "SYS"."DDLCTXINFO" AS
   PROCEDURE setCtxInfo(objNum  IN NUMBER, baseObjNum IN NUMBER,
                         objUserId IN NUMBER, baseObjUserId IN NUMBER,
                         baseObjProperty IN NUMBER) IS
   BEGIN
       "OGG".DDLReplication.setCtxInfo(objNum , baseObjNum ,
                         objUserId , baseObjUserId ,
                         baseObjProperty ) ;
   END;

END DDLCtxInfo;


Elapsed: 00:00:00.41
20:02:47 sys@LUNAR>

可见,还是OGG DDL的东西,于是清理上述4个无效对象:

20:02:51 sys@LUNAR>drop PACKAGE BODY SYS.DDLCTXINFO;

Package body dropped.

Elapsed: 00:00:00.03
20:02:52 sys@LUNAR>drop PROCEDURE OGG.DDLORA_GETLOBS;

Procedure dropped.

Elapsed: 00:00:00.00
20:02:57 sys@LUNAR>drop FUNCTION OGG.FILTERDDL;

Function dropped.

Elapsed: 00:00:00.01
20:02:58 sys@LUNAR>drop PACKAGE BODY OGG.DDLAUX;

Package body dropped.

Elapsed: 00:00:00.02
20:02:58 sys@LUNAR>

再次检查,已经没有无效对象,且数据库组件全部有效:

20:02:59 sys@LUNAR>select owner,object_name,object_type,status from dba_objects where status != 'VALID' and owner in ('SYS','SYSTEM'); 

no rows selected

Elapsed: 00:00:00.02
20:06:00 sys@LUNAR>select owner, object_name,object_type from dba_objects where status ='INVALID';

no rows selected

Elapsed: 00:00:00.01
20:06:01 sys@LUNAR>
[oracle@dm01db01 ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 3 20:07:10 2015

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

20:07:10 sys@LUNAR>col COMP_NAME for a35        
20:07:18 sys@LUNAR>SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, SUBSTR(comp_name,1,35) comp_name FROM dba_registry; 

COMP_ID                        STATUS                 VERSION              COMP_NAME
------------------------------ ---------------------- -------------------- -----------------------------------
OWB                            VALID                  11.2.0.3.0           OWB
APEX                           VALID                  3.2.1.00.1           Oracle Application Express
EM                             VALID                  11.2.0.4.0           Oracle Enterprise Manager
AMD                            VALID                  11.2.0.4.0           OLAP Catalog
SDO                            VALID                  11.2.0.4.0           Spatial
ORDIM                          VALID                  11.2.0.4.0           Oracle Multimedia
XDB                            VALID                  11.2.0.4.0           Oracle XML Database
CONTEXT                        VALID                  11.2.0.4.0           Oracle Text
EXF                            VALID                  11.2.0.4.0           Oracle Expression Filter
RUL                            VALID                  11.2.0.4.0           Oracle Rules Manager
OWM                            VALID                  11.2.0.4.0           Oracle Workspace Manager
CATALOG                        VALID                  11.2.0.4.0           Oracle Database Catalog Views
CATPROC                        VALID                  11.2.0.4.0           Oracle Database Packages and Types
JAVAVM                         VALID                  11.2.0.4.0           JServer JAVA Virtual Machine
XML                            VALID                  11.2.0.4.0           Oracle XDK
CATJAVA                        VALID                  11.2.0.4.0           Oracle Database Java Packages
APS                            VALID                  11.2.0.4.0           OLAP Analytic Workspace
XOQ                            VALID                  11.2.0.4.0           Oracle OLAP API

18 rows selected.

Elapsed: 00:00:00.00
20:07:25 sys@LUNAR>

总结:
1,数据库升级前,一定要检查OGG用户的DDL功能是否已经被禁用,如果没有,麻利儿禁用它
2,其他系统自带的DDL trigger一般来说,如果是11.2小版本之间的升级,个人感觉不禁用也问题不大
3,官方建议升级前禁用DDL trigger,因此,推荐升级前还是禁用吧。尤其是从9i或者10g升级到11.2.

此条目发表在 数据库 分类目录。将固定链接加入收藏夹。

评论功能已关闭。