| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: upgrade
I found this on MetaLink which describes exactly what you are getting and a possible workaround.
Rick
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
Confirmation of the problem
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
# 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
# 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: RICHARD.T.CALE_at_saic.com 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: ListGuru_at_fatcity.com (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: NDATFM_at_labor.state.ny.us 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: ListGuru_at_fatcity.com (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: RICHARD.T.CALE_at_saic.com 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: ListGuru_at_fatcity.com (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).Received on Wed Aug 01 2001 - 12:40:18 CDT
 |
 |