【Oracle DBA Scripts】表空间数据文件存放目录校验触发器

本期将为大家分享“表空间数据文件存放目录校验触发器”运维管理脚本。

在运维Oracle RAC集群时，偶尔会发现表空间对应的数据文件路径指定到服务器本地磁盘而不是ASM磁盘组，从而导致其中一个集群节点上实例无法读到该数据文件里的数据。为了规避技术人员将数据文件指向本地磁盘，DBA可以编写触发器对数据文件脚本进行校验。

脚本使用方法

1、登录sys用户创建触发器
create or replace trigger datafile_path_verify
  before create or alter on database
declare
  l_errmsg varchar2(100) := 'Not specify datafile to ASM( DATAFILE ''+XXX''),Or Call us for help! Invalid DDL: ';
  sql_text ora_name_list_t;
  stmt     VARCHAR2(2000);
  n        number;
begin
  n    := ora_sql_txt(sql_text);
  stmt := sql_text(1);
  if n > 0 then
    if (instr(upper(stmt), 'CREATE') <> 0 and
       instr(upper(stmt), 'TABLESPACE') <> 0 and
       instr(upper(stmt), 'DATAFILE') <> 0) or
       (instr(upper(stmt), 'ALTER') <> 0 and
       instr(upper(stmt), 'TABLESPACE') <> 0 and
       instr(upper(stmt), 'ADD') <> 0) then
      if instr(stmt, '+') = 0 then
        RAISE_APPLICATION_ERROR(-20000, l_errmsg || ' ' || stmt);
      end if;
    end if;
  end if;
end;
/


2、检查触发器状态是否生效
select owner, trigger_name, trigger_type, status
  from dba_triggers
 where trigger_name = 'DATAFILE_PATH_VERIFY';


3、若不需要或回退，可以直接删除触发器
drop trigger sys.datafile_path_verify;


4、登录数据库验证触发器
不规范SQL> create tablespace tbs_test datafile '/home/oracle/tbs_test01.dbf' size 10m;
create tablespace tbs_test datafile '/home/oracle/tbs_test01.dbf' size 10m
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DATAFILE_PATH_VERIFY'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Not specify datafile to ASM( DATAFILE '+XXX'),Or Call us for help!
Invalid DDL:  create tablespace tbs_test datafile '/home/oracle/tbs_test01.db
ORA-06512: at line 16


规范SQL> create tablespace tbs_test datafile '+YWZD' size 10m;


5、删除测试的表空间
drop Tablespace tbs_test including contents and datafiles;

错误截图：

数据库触发器是特殊的存储程序。通常不直接调用它们，而要由数据库中的事件触发。它们在执行命令时和执行数据库管理系统动作之间运行。触发器可以用PL/SQL或Java编写。触发器可以捕获创建、修改或删除对象的事件，也可以捕获表或视图中的插入、更新或删除操作。它们还可以监控数据库或模式的状态变化及用户动作的变化。其中系统定义的事件属性函数ora_sql_txt，有一个OUT模式的VARCHAR2参数，该参数类型定义在DBMS_STANDARD包中: type ora_name_list_t is table of varchar2(64)，该参数返回触发事件的sql语句。

官方参考（Database PL/SQL Language Reference）：

【Oracle DBA Scripts】表空间数据文件存放目录校验触发器

评论