触发器（七、DDL触发器在OGG维护中的应用）
程序员文章站
2024-03-21 09:12:22
...
场景描述
有一个业务系统，做了OGG备库用于统计查询。
生产库的维护由系统管理员负责，而备库的日常维护由专职DBA负责。
系统厂商在对生产环境进行升级时，经常会修改数据结构。
DBA很快发现了OGG报错，查看日志是数据结构变更，于是向生产库系统管理员要数据变更清单。
生产库管理员再向系统厂商要，有时候系统厂商没有事先准备脚本，升级时在线的部分修改没有记录，导致DBA不停的跟踪错误信息，两边比对数据结构，再到备库中进行修改，烦不胜烦。
之后，DBA在生产库中创建了DDL触发器，用于跟踪数据结构变更，用来解决这个烦恼。
DDL触发器能对数据库中的DDL操作进行触发，包括
实施步骤如下：
1.在生产环境的监控用户CZFX下创建DDL操作记录表
SQL> CREATE TABLE CZFX.T_DDL_OGG
2 (GUID VARCHAR2(50) DEFAULT SYS_GUID() PRIMARY KEY
3 ,CREATED DATE DEFAULT SYSDATE
4 ,EVENT VARCHAR2(30)
5 ,LOGIN_USER VARCHAR2(30)
6 ,OBJ_NAME VARCHAR2(30)
7 ,OBJ_OWNER VARCHAR2(30)
8 ,OBJ_TYPE VARCHAR2(30)
9 ,COLUMN_NAME VARCHAR2(30)
10 ,DDL_SQL VARCHAR2(4000)
11 ,ISDEAL VARCHAR2(1) DEFAULT '0'
12 ) TABLESPACE CZFX;
Table created
Executed in 0.031 seconds2.用SYS用户创建DDL触发器
（注意：其他数据库用户也可以创建DDL触发器，但经测试只能捕捉到用户自己的DDL操作）
SQL> CREATE OR REPLACE TRIGGER TRI_DBDDL
2 AFTER CREATE OR DROP OR ALTER ON DATABASE
3 WHEN (ORA_DICT_OBJ_TYPE='TABLE')
4 DECLARE
5 V_GUID VARCHAR2(50);
6 T_SQL_TXT ORA_NAME_LIST_T:=ORA_NAME_LIST_T();
7 N_COUNT PLS_INTEGER;
8 V_SQL_TXT VARCHAR2(4000);
9 BEGIN
10 V_GUID:=SYS_GUID();
11 N_COUNT:=ORA_SQL_TXT(sql_text => T_SQL_TXT);
12 FOR I IN 1..N_COUNT LOOP
13 V_SQL_TXT:=V_SQL_TXT||T_SQL_TXT(I)||CHR(10);
14 END LOOP;
15 INSERT INTO CZFX.T_DDL_OGG(GUID,CREATED,LOGIN_USER,EVENT,OBJ_NAME,OBJ_OWNER,OBJ_TYPE,DDL_SQL)
16 VALUES(V_GUID,SYSDATE,ORA_LOGIN_USER,ORA_SYSEVENT,ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_TYPE
17 ,V_SQL_TXT);
18 FOR REC_COLUMN IN(SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER=ORA_DICT_OBJ_OWNER AND TABLE_NAME=ORA_DICT_OBJ_NAME)
19 LOOP
20 IF ORA_IS_ALTER_COLUMN(REC_COLUMN.COLUMN_NAME) THEN
21 UPDATE CZFX.T_DDL_OGG SET COLUMN_NAME=LTRIM(COLUMN_NAME||','||REC_COLUMN.COLUMN_NAME,',')
22 WHERE GUID=V_GUID;
23 END IF;
24 END LOOP;
25 END;
26 /
Trigger created
Executed in 0.031 seconds这个触发器起到了以下效果：
（1）捕捉并记录了数据库中任何用户对表（table）的create、alter、drop操作；
（2）记录了DDL原始语句，这个非常有用，在备库可以直接粘贴；
（3）记录了是哪个用户（登录用户）操作了哪个用户（对象拥有者）的表
3.测试在不同数据库用户下做不同的DDL操作
查看日志表
SQL> SHOW USER
User is "SYS"
SQL> CREATE TABLE TMP_DDL_SYS(COL1 VARCHAR2(1));
Table created
Executed in 0.063 seconds
SQL> select * from CZFX.T_DDL_OGG;
GUID CREATED EVENT LOGIN_USER OBJ_NAME OBJ_OWNER OBJ_TYPE COLUMN_NAME DDL_SQL ISDEAL
-------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------
670898870AB51B9BE0532582290AF84C 2018/3/10 1 CREATE SYS TMP_DDL_SYS SYS TABLE CREATE TABLE TMP_DDL_SYS(COL1 VARCHAR2(1)) 0
Executed in 0.094 seconds
SQL> conn system/aaa@qq.com;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aaa@qq.com
SQL>
SQL> CREATE TABLE TMP_DDL_SYSTEM(COL1 VARCHAR2(1));
Table created
Executed in 0.031 seconds
SQL> select * from CZFX.T_DDL_OGG;
GUID CREATED EVENT LOGIN_USER OBJ_NAME OBJ_OWNER OBJ_TYPE COLUMN_NAME DDL_SQL ISDEAL
-------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------
670898870AB51B9BE0532582290AF84C 2018/3/10 1 CREATE SYS TMP_DDL_SYS SYS TABLE CREATE TABLE TMP_DDL_SYS(COL1 VARCHAR2(1)) 0
6708DEB8ECF41C9EE0532582290AF3B6 2018/3/10 1 CREATE SYSTEM TMP_DDL_SYSTEM SYSTEM TABLE CREATE TABLE TMP_DDL_SYSTEM(COL1 VARCHAR2(1)) 0
Executed in 0.11 seconds
SQL>
SQL> conn czfx/aaa@qq.com;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aaa@qq.com
SQL> CREATE TABLE TMP_DDL_CZFX(COL1 VARCHAR2(1));
Table created
Executed in 0.016 seconds
SQL> ALTER TABLE TMP_DDL_CZFX MODIFY COL1 NUMBER(2);
Table altered
Executed in 0.016 seconds
SQL> ALTER TABLE TMP_DDL_CZFX ADD COL2 VARCHAR2(20);
Table altered
Executed in 0 seconds
SQL> select * from CZFX.T_DDL_OGG;
GUID CREATED EVENT LOGIN_USER OBJ_NAME OBJ_OWNER OBJ_TYPE COLUMN_NAME DDL_SQL ISDEAL
-------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------
670898870AB51B9BE0532582290AF84C 2018/3/10 1 CREATE SYS TMP_DDL_SYS SYS TABLE CREATE TABLE TMP_DDL_SYS(COL1 VARCHAR2(1)) 0
6708DEB8ECF41C9EE0532582290AF3B6 2018/3/10 1 CREATE SYSTEM TMP_DDL_SYSTEM SYSTEM TABLE CREATE TABLE TMP_DDL_SYSTEM(COL1 VARCHAR2(1)) 0
6708E1B7A1CD1F6CE0532582290A83AF 2018/3/10 1 CREATE CZFX TMP_DDL_CZFX CZFX TABLE CREATE TABLE TMP_DDL_CZFX(COL1 VARCHAR2(1)) 0
6708E1B7A1CE1F6CE0532582290A83AF 2018/3/10 1 ALTER CZFX TMP_DDL_CZFX CZFX TABLE COL1 ALTER TABLE TMP_DDL_CZFX MODIFY COL1 NUMBER(2) 0
6708E1B7A1CF1F6CE0532582290A83AF 2018/3/10 1 ALTER CZFX TMP_DDL_CZFX CZFX TABLE ALTER TABLE TMP_DDL_CZFX ADD COL2 VARCHAR2(20) 0
Executed in 0.172 seconds
SQL>
SQL> DROP TABLE TMP_DDL_CZFX PURGE;
Table dropped
Executed in 0.031 seconds
SQL> conn sys/aaa@qq.com AS SYSDBA;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aaa@qq.com AS SYSDBA
SQL> DROP TABLE SYSTEM.TMP_DDL_SYSTEM PURGE;
Table dropped
Executed in 0.047 seconds
SQL> DROP TABLE TMP_DDL_SYS PURGE;
Table dropped
Executed in 0.032 seconds
SQL> select * from CZFX.T_DDL_OGG ORDER BY CREATED;
GUID CREATED EVENT LOGIN_USER OBJ_NAME OBJ_OWNER OBJ_TYPE COLUMN_NAME DDL_SQL ISDEAL
-------------------------------------------------- ----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------
670898870AB51B9BE0532582290AF84C 2018/3/10 1 CREATE SYS TMP_DDL_SYS SYS TABLE CREATE TABLE TMP_DDL_SYS(COL1 VARCHAR2(1)) 0
6708DEB8ECF41C9EE0532582290AF3B6 2018/3/10 1 CREATE SYSTEM TMP_DDL_SYSTEM SYSTEM TABLE CREATE TABLE TMP_DDL_SYSTEM(COL1 VARCHAR2(1)) 0
6708E1B7A1CD1F6CE0532582290A83AF 2018/3/10 1 CREATE CZFX TMP_DDL_CZFX CZFX TABLE CREATE TABLE TMP_DDL_CZFX(COL1 VARCHAR2(1)) 0
6708E1B7A1CE1F6CE0532582290A83AF 2018/3/10 1 ALTER CZFX TMP_DDL_CZFX CZFX TABLE COL1 ALTER TABLE TMP_DDL_CZFX MODIFY COL1 NUMBER(2) 0
6708E1B7A1CF1F6CE0532582290A83AF 2018/3/10 1 ALTER CZFX TMP_DDL_CZFX CZFX TABLE ALTER TABLE TMP_DDL_CZFX ADD COL2 VARCHAR2(20) 0
6708E1B7A1D01F6CE0532582290A83AF 2018/3/10 1 DROP CZFX TMP_DDL_CZFX CZFX TABLE DROP TABLE TMP_DDL_CZFX PURGE 0
6708E56C22271F9BE0532582290AF4DB 2018/3/10 1 DROP SYS TMP_DDL_SYSTEM SYSTEM TABLE DROP TABLE SYSTEM.TMP_DDL_SYSTEM PURGE 0
6708E56C22281F9BE0532582290AF4DB 2018/3/10 1 DROP SYS TMP_DDL_SYS SYS TABLE DROP TABLE TMP_DDL_SYS PURGE 0
8 rows selected
Executed in 0.234 seconds接下来就通过各种方式通知DBA就可以了，DBA对新增的DDL操作在备库中进行处理，并把记录删掉或标记为已处理。
这样就完成了一个OGG的DDL操作自动管理机制，本人部署完这套东西后，添加了短信提醒功能，在GoldenGate主库出现异常时能够在不联系主库管理员的情况下迅速恢复OGG运行，收益匪浅。
当然，DDL触发器还可以用于跟踪其他数据库对象的DDL操作，支持的DDL命令有以下这些：