Migration to 8i / 9i: Set "_SYSTEM_TRIG_ENABLED" to FALSE[akadia]

最新推荐文章于 2024-09-20 17:14:37 发布

cuiqingko849622

最新推荐文章于 2024-09-20 17:14:37 发布

阅读量120

点赞数

文章标签：数据库 java

This important Tip was published on Oracle Metalink.

This parameter was introduced in Oracle 8.1. It is a HIDDEN parameter (It begins with an UNDERSCORE). This parameter can only be set in the init.ora file in Oracle8i. It can be changed dynamically using ALTER SYSTEM in Oracle9i.

System triggers are a new feature in Oracle 8.1. When _SYSTEM_TRIG_ENABLED is set to TRUE (the default) then system triggers are enabled. In some cases it may be necessary to disable system triggers from firing by setting this parameter to FALSE. This should only be done for short periods for specific operations. It is not advisable to allow normal users onto the database whilst such triggers are disabled as system triggers may be used for audit or security checking by certain applications.

Description

When performing any of the following actions on an Oracle8i (or 9i) database:

Installing a patch set
Upgrading
Downgrading
Performing any other operation which requires catalog or catproc to be run
Installing Java (initjvm)
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.

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.

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;

The parameter _SYSTEM_TRIG_ENABLED does not exist in Oracle 8.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 Oracle 8i / 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 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, "rmjvm" does not drop all Java related objects. It leaves a database trigger behind which then cannot execute

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.

When upgrading from 8.0 or migrating from 7.3

If _SYSTEM_TRIG_ENABLED is not set to FALSE then you may encounter an ORA-604 , ORA-6553 , PLS-213 in package STANDARD errors when opening the database under Oracle8i/9i.

Upgrading to Oracle9i

Can give ORA-600 [16201] errors opening a database under Oracle9i for the first time when _SYSTEM_TRIG_ENABLED is not set to FALSE.