I found this on MetaLink which describes exactly what you are getting and a
possible workaround.
Rick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[BUG:1362374] ORA-604 / ORA-1405 Trying to Compile Triggers
after a Migration from Oracle7 or Upgrade from
Oracle8.0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If a database is migrated from Oracle7 to Oracle8.1.6 or 8.1.7 ,
or upgraded from 8.0 to 8.1.6 or 8.1.7 then it is possible for
the upgrade scripts to error when updating TRIGGER$.
This can cause subsequent trigger compilations to error with:
ORA-604 error occurred at recursive SQL level 1
ORA-1405 fetched column value is NULL
It is also possible, especially after migration, that an
attempt to open the database under the new release may error
with ORA-3113 or ORA-6553 / PLS-213 errors.
Versions Affected
~~~~~~~~~~~~~~~~~
This bug can affect databases migrated from Oracle7 to 8.1.6
or 8.1.7 and databases upgraded from Oracle8.0 to 8.1
Confirmation of the problem
~~~~~~~~~~~~~~~~~~~~~~~~~~~
If the database will open then perform the following select.
If this returns count of anything other than 0 and you
get ORA-604 / ORA-1405 compiling triggers which were
created under an earlier release then you have probably
hit this issue:
select count(*) from trigger$
where sys_evts IS NULL
or nttrigcol IS NULL;
If the database will not open then see if it will open when
the parameter _SYSTEM_TRIG_ENABLED=FALSE as described in the
workaround below.
Workaround
~~~~~~~~~~
IF the problem occurred during a migration from Oracle7 to Oracle8i
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The workaround to this bug is to add the following to the
init$ORACLE_SID.ora file under Oracle8i prior to CONVERTING and
opening the database during the migrate procedure.
# Do not fire system triggers to allow u0703040.sql to run
_system_trig_enabled=FALSE
# Do not use CBO for upgrade steps
OPTIMIZER_MODE=RULE
Note: If the database has already been migrated and u0703040.sql
run then the workaround requires you to re-run the migration
adding the above parameter before the CONVERT.
Adding this parameter after u0703040.sql has been run
will have no effect as the dictionary contains bad values
in TRIGGER$.
Note-2:
If you already have OPTIMIZER_MODE specified as a different
value to RULE then comment it out.
IF the problem occurred during an UPGRADE from 8.0 to Oracle8i
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The workaround to this bug is to add the following to the
init$ORACLE_SID.ora file prior to running any of the UPGRADE
scripts:
# Do not fire system triggers to allow upgrade to run
_system_trig_enabled=FALSE
# Do not use CBO for upgrade steps
OPTIMIZER_MODE=RULE
Note: If the database has already been upgraded then this
workaround requires you to restore the 8.0.X database
and re-run the UPGRADE scripts AFTER adding the above
parameter.
Note-2:
If you already have OPTIMIZER_MODE specified as a different
value to RULE then comment it out.
In both cases above the parameters can be removed from the init.ora
once all upgrade scripts have run to completion.
.
-----Original Message-----
Sent: Wednesday, August 01, 2001 1:27 PM
To: Multiple recipients of list ORACLE-L
Fawzia,
Looks like you need to increase your SHARED_POOL_SIZE parameter.
These errors are on indication that Oracle is starving for memory, not that
the trigger will not compile.
My development DB is set at 80M. Try increasing your's to at least this,
higher if you have many people on the database.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, August 01, 2001 12:52 PM
To: Multiple recipients of list ORACLE-L
Hi all,
Thanks for responding. I did run the script outlined below. The invalid
objects are triggers. If I try to compile manually I get the following:
SQL> alter TRIGGER SCER10.VRSS_BRI compile ;
alter TRIGGER SCER10.VRSS_BRI compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL
These were all valid prior to the upgrade so maybe its code related????
Thanks again
Fawzia
-----Original Message-----
Sent: 01 August 2001 17:26
To: Multiple recipients of list ORACLE-L
What type errors are you getting? That may help in determing if you need to
run some Oracle scripts.
Rick
-----Original Message-----
Sent: Wednesday, August 01, 2001 9:51 AM
To: Multiple recipients of list ORACLE-L
Hi,
I have just upgraded 8.0.5.2 to 8.1.6 and some of my objects are invalid. I
have tried to recompile them to no avail. I did the upgrade manually running
the script :u0800050. Is there anything else I needed to run???
Rgds
Fawzia
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
