Why "_SYSTEM_TRIG_ENABLED=FALSE" needs to be set when Upgrading / Downgrading / Applying Patch Sets (문서 ID 149948.1)
12c->19c Upgrade 하면서, 형상관리 이력을 위한 DDL Trigger 로 오류 발생,
사전에 설정한 flashback 설정으로, flashback database 로 원복 하고 다시 업그레이드
이력이 있는데, 왜 (DDL) Trigger 를 disable 해야 하는지 설명하는 내용이 있다.
아쉬운건 업그레이드나 이런 경우, 내부적으로 다이나믹 하게 disable 한다고 하는데
, 말 그대로 시스템 트리거만 disalbe 하는 듯 싶다.
문제가 된 트리거는 일반 유저의 시스템 레벨 DDL TRIGGER 였다.
>> 체크 결과,히든 파라미터로 SYSTEM TRIGGER 를 FALSE 로 하여도 일반 유저 TRIGGER 는 정상 작동 함
"The parameter should be set to FALSE for scripts which perform dictionary operations as the objects on which the triggers depend
may become invalid or be dropped, causing the triggers to fail and thus preventing the scripts from running successfully. Some examples are given below."
Versions & Platforms Affected
o The information here applies to Oracle 8i, Oracle 9i, and Oracle 10g on all platforms.
o It does NOT apply to Oracle8.0 or Oracle7 databases UNLESS they are being upgraded / migrated to an Oracle8i or 9i release or later.
Description
When performing any of the following actions on an Oracle database:
o Installing a patch set
o Upgrading
o Downgrading
o Performing any other operation which requires catalog or catproc to be run (Except when we create a DB in 8i / 9i)
o Installing Java (initjvm)
o Any other action which runs scripts which modify objects owned by SYS
then you should set the hidden init.ora parameter _SYSTEM_TRIG_ENABLED to FALSE before starting the instance under Oracle8i (or 9i) to perform the
respective maintenance operation unless the steps you are following advise otherwise.
Eg: In Oracle8i add the lines below to the init.ora file used to start the instance then stop and restart the instance before performing the
maintenance actions.
# Disable system triggers for the duration of the maintenance
# operation.
_SYSTEM_TRIG_ENABLED=FALSE
IMPORTANT: This parameter must be commented out and the instance re-started once the required maintenance operations have been performed.
Oracle9i and later
Most Oracle9i scripts include statements to dynamically set _SYSTEM_TRIG_ENABLED to FALSE when required.
However it is possible that some scripts have omitted this step so it is still advisable to set this to FALSE.
You can do this in Oracle9i using the command:
ALTER SYSTEM SET "_system_trig_enabled"=FALSE;
Once the required steps are complete you can reenable triggers thus:
ALTER SYSTEM SET "_system_trig_enabled"=TRUE;
If you are using an init.ora file (rather than an SPFILE) then the parameter can also be set there as described above.
Explanation
What does _SYSTEM_TRIG_ENABLED do ?
This hidden parameter is described in Note:68636.1.
A value of FALSE stops system triggers from firing (eg: triggers on various DDL or database events are disabled).
Why should it be set to false ?
The parameter should be set to FALSE for scripts which perform dictionary operations as the objects on which the triggers depend may become
invalid or be dropped, causing the triggers to fail and thus preventing the scripts from running successfully. Some examples are given below.
Example Problem Scenarios
There are many potential problem scenarios if you have system triggers in place when performing dictionary maintenance operations.
Some of the more common symptoms are described below.
In most cases setting _SYSTEM_TRIG_ENABLED=FALSE and re-performing the operation will allow you to proceed.
Q: ALL TRIGGER DISABLE ? 혹은 USER TRIGGER 는 예외 ?
User Trigger 는 제외!!!
create table master_table ( a1 date, a2 varchar2(200) ) ;
create table trigger_table ( a1 date, a2 varchar2(200) ) ;
create or replace trigger tr_check_trigger_table
after insert on master_table
for each row
begin
insert into trigger_table values ( :new.a1, :new.a2 ) ;
end ;
select OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, STATUS
from dba_triggers where trigger_name =UPPER('tr_check_trigger_table');
select a.ksppinm "Parameter", c.ksppstvl "Instance Value", ksppdesc "Desc"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm in ('_system_trig_enabled');
INSERT INTO master_table VALUES ( SYSDATE, TO_CHAR(SYSDATE) ) ;
COMMIT ;
SELECT * FROM master_table ;
SELECT * FROM trigger_table ;
alter system set "_system_trig_enabled" = false scope=memory;
select a.ksppinm "Parameter", c.ksppstvl "Instance Value", ksppdesc "Desc"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm in ('_system_trig_enabled');
INSERT INTO master_table VALUES ( SYSDATE, TO_CHAR(SYSDATE) ) ;
COMMIT ;
SELECT * FROM master_table ;
SELECT * FROM trigger_table ;
>> 정상 작동 확인됨 !!!
alter system set "c" = true scope=memory;
select a.ksppinm "Parameter", c.ksppstvl "Instance Value", ksppdesc "Desc"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm in ('_system_trig_enabled');