升级数据库时，比如从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.
