_SYSTEM_TRIG_ENABLED -----trigger

_SYSTEM_TRIG_ENABLED

Subject:  IMPORTANT: Set "_SYSTEM_TRIG_ENABLED=FALSE" 
When Upgrading / Downgrading / Applying  
Important Step when Upgrading / Downgrading / Installing / Deinstalling Options
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Versions & Platforms Affected 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
  o The information here applies to Oracle8i and Oracle9i 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.
 
Description 
~~~~~~~~~~~ 
  When performing any of the following actions on an Oracle8i (or 9i) 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

[@more@] 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
~~~~~~~~
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.

Oracle8.0 / Oracle7
~~~~~~~~~~~~~~~~~~~
The parameter _SYSTEM_TRIG_ENABLED does not exist in Oracle8.0 or earlier
so should not be present when starting an instance under 8.0 or Oracle7.
However, if the operation being performed involves some steps under
Oracle8i/9i and some under 8.0/7.X then set the parameter for the 8i/9i
steps.

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.

After running "rmjvm"
~~~~~~~~~~~~~~~~~~~~~
If you have JIS installed and then deinstall Java using the "rmjvm"
script then any subsequent attempt to DROP a ROLE will error.
This is described in Bug 1751857 - "rmjvm" does not drop all Java
related objects. It leaves a database trigger behind which then cannot
execute:
eg:
SVRMGR> drop role a;
drop role a
*
ORA-00604: error occurred at recursive SQL level 1
ORA-29540: class oracle/aurora/mts/http/security/RdbmsHttpPolicy does nt
ORA-06512: at "SYS.HTTP_SECURITY_CASCADE", line 0
ORA-06512: at line 3


Running CATALOG / CATPROC
~~~~~~~~~~~~~~~~~~~~~~~~~
If you have JIS installed and re-run CATALOG and CATPROC then the same
trigger can become INVALID. This causes errors on many DROP SYNONYM
commands as described in the bug entry Bug 1733297 .
eg:
SVRMGR> drop public synonym dba_segments;
*
ORA-4098: trigger 'SYS.JIS$ROLE_TRIGGER$' is invalid and failed re-valin


When upgrading from 8.0 or migrating from 7.3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If _SYSTEM_TRIG_ENABLED is not set to FALSE then you may encounter Bug 1362374 which can cause ORA-604 , ORA-6553 , PLS-213 in package
STANDARD errors when opening the database under Oracle8i/9i.

If you are using ODMA (Database Migration Assistant) to upgrade from
Oracle7 or 8.0 then it is not possible to set the _SYSTEM_TRIG_ENABLED
before invoking ODMA itself. In this case it is advisable to test if
the upgrade / migration completes successfully when using ODMA and if
not revert to using manual upgrade / migration steps so that the
parameter can be set.


Upgrading to Oracle9i
~~~~~~~~~~~~~~~~~~~~~ Bug 1747065 can give ORA-600 [16201] errors opening a database under
Oracle9i for the first time when _SYSTEM_TRIG_ENABLED is not set to
FALSE.
______________________________________________________________________________
Oracle Support Services