During the Oracle Apps Database upgrade to 10g that I wrote about in the previous post, I ran into some errors.
In this post I will write about the errors symptoms, causes and the solutions that worked for me. :-)
Case 1: XDB (Oracle XML Database) schema is invalid
Symptom:
During the upgrade process an error message shows up:
ORA-06576: not a valid function or procedure name
In Oracle_Server.log: ($ORACLE_HOME/cfgtoollogs/dbua/[SID]/upgrade/)
call dbms_xdb.CleanSGAForUpgrade();
call dbms_xdb.CleanSGAForUpgrade();
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
When click on “Ignore” an error message shows up:
ORA-31159: XML DB is in an invalid state
ORA-00600: internal error code, arguments:
[qmtb_init_len], [], [], [], [], [],
Cause:
Oracle XML Database (XDB) is in an Invalid state.
Execute this query to check the XDB state:
select comp_name, status, version
from DBA_REGISTRY
where comp_name= 'Oracle XML Database’;
COMP_NAME STATUS VERSION
Oracle XML Database INVALID 9.2.0.6.0
Solution:
The XDB schema should be reinstalled.
I’ve Followed Note 243554.1 - How to Deinstall and Reinstall XML Database (XDB)
Step by Step Action Plan:
1) Restart the DB
2) Connect to DB as sysdba
3) Run $ORACLE_HOME/rdbms/admin/catnoqm.sql to remove XDB from database.
4) Update init.ora with the following (at least):
Shared_pool_size=150MB
Java_pool_size=150MB
5) Shutdown immediate
6) Startup
7) Run $ORACLE_HOME/rdbms/admin/catqm.sql XDB XDB TEMP
to create the XDB schema with all required objects
8) Run $ORACLE_HOME/rdbms/admin/catxdbj.sql
to register XDB java api jar files.
9) Check invalid objects for xdb users
select count(*)
from dba_objects
where owner='XDB'
and status='INVALID';
10) Check dba_registry (should be valid now…)
select comp_name, status, version
from DBA_REGISTRY
where comp_name= 'Oracle XML Database’;
Case 2: ORA-06512 AT SYS.UTL_RECOMP
Symptoms:
During the upgrade process, an error message shows up:
ORA-01003: no statement parsed
ORA-06512: at "SYS.UTL_RECOMP", line 464
ORA-06512: at "SYS.UTL_RECOMP", line 467
ORA-06512: at "SYS.UTL_RECOMP", line 607
ORA-01003: no statement parsed
ORA-06512: at line 4
I’ve take a look on log file $ORACLE_HOME/cfgtoollogs/dbua/[SID]/upgrade/PostUpgrade.log:
DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01003: no statement parsed
ORA-06512: at "SYS.UTL_RECOMP", line 464
ORA-06512: at "SYS.UTL_RECOMP", line 467
ORA-06512: at "SYS.UTL_RECOMP", line 607
ORA-01003: no statement parsed
ORA-06512: at line 4
And those are the last lines of the related trace file:
[Thread-23] [18:12:10:580] [SQLEngine.setSpool:1744] Setting Spool =
/oracle/tst10db/10.2/cfgtoollogs/dbua/TST10/upgrade/PostUpgrade.log
[Thread-23] [18:12:10:581] [SQLEngine.setSpool:1745] Is spool appendable? -->
true
[Thread-23] [18:12:10:584] [SQLEngine.reInitialize:602] Reinitializing SQLEngine...
[Thread-23] [18:12:10:591] [SQLEngine.initialize:241] Execing SQLPLUS/SVRMGR process...
[Thread-23] [18:12:10:609] [SQLEngine.initialize:269] m_bReaderStarted: false
[Thread-23] [18:12:10:610] [SQLEngine.initialize:273] Starting Reader Thread...
[Thread-23] [18:12:10:617] [SQLEngine.initialize:322] Waiting for m_bReaderStarted to be true
[Thread-57] [18:58:44:357] [BasicStep.handleNonIgnorableError:430]
oracle.sysman.assistants.util.UIMessageHandler@c8e4de:messageHandler
[Thread-57] [18:58:44:361] [BasicStep.handleNonIgnorableError:431] ORA-01003: no
statement parsed
ORA-06512: at "SYS.UTL_RECOMP", line 464
ORA-06512: at "SYS.UTL_RECOMP", line 467
ORA-06512: at "SYS.UTL_RECOMP", line 607
ORA-01003: no statement parsed
ORA-06512: at line 4
Cause:
Database initialization parameters are not set properly.
Solution:
Set the following initialization parameters:
SHARED_POOL_SIZE = 250 Mb or greater
LARGE_POOL_SIZE = 150 Mb or greater
JAVA_POOL_SIZE = 150 Mb or greater
Then rerunning:
sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> startup upgrade
SQL> spool my_upgrade.log
SQL> @/rdbms/admin/catpupgrd.sql
SQL> @/rdbms/admin/utlrp.sql
SQL> spool off
Case 3: Shutdown Database Error ORA-600 [Librarycachenotemptyonclose]
Symptom:
The first shutdown after upgrade to 10g shows an error message:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []
Cause:
Related note 435926.1 - Shutdown Database Erroring ORA-600 [Librarycachenotemptyonclose]
An unpublished bug: 4483084 say that this particular ORA-600 error can be ignored as it means nothing - actually there is no error.
Solution:
Do nothing…
When it will be available, upgrade to 10.2.0.4.0.
Case 4: OLAP Catalog component (AMD) is invalid after upgrade
Symptom:
Check OLAP catalog state:
select comp_name, status, version
from DBA_REGISTRY
where comp_name= 'OLAP Catalog';
COMP_NAME STATUS VERSION
OLAP Catalog INVALID 10.2.0.3.0
Solution:
Recreate OLAP Catalog.
ORACLE_HOME=/oracle/livedb/10.2
ORACLE_SID=TST10
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL>spool olap_spool.log
SQL>connect SYS as SYSDBA
SQL>@?/olap/admin/olap.sql SYSAUX TEMP;
-- Recompile everything
SQL>@?/rdbms/admin/utlrp.sql
SQL>spool off
Check status of AMD again – should be VALID:
select comp_name,version,status
from dba_registry
where comp_name like '%OLAP%';
Case 5: A lot of invalid objects in DB after upgrade
Symptom:
A lot of invalid objects in DB which cannot be compiled because of this error: (examples)
Compilation errors for PACKAGE BODY APPS.HR_DM_LIBRARY
#13#10Error: PLS-00907: cannot load library unit PUBLIC.DBMS_SQL (referenced by APPS.HR_DM_LIBRARY)
Line: 0
Compilation errors for PACKAGE BODY APPS.HR_DU_DI_INSERT
#13#10Error: PLS-00907: cannot load library unit PUBLIC.UTL_FILE (referenced by APPS.HR_DU_DI_INSERT)
Line: 0
Compilation errors for PACKAGE BODY APPS.IBE_QUOTE_SAVE_PVT
#13#10Error: PLS-00907: cannot load library unit PUBLIC.UTL_HTTP (referenced by
APPS.CZ_CF_API)
Line: 0
Cause:
Existing PL/SQL modules should recompiled in the FORMAT required by the NEW DATABASE.
The utlirp.sql hasn't been run after the upgrade.
Solution:
conn /as sysdba
shutdown
startup upgrade
@$ORACLE_HOME/rdbms/admin/utlirp.sql
shutdown
startup
@$ORACLE_HOME/rdbms/admin/utlrp.sql
About the utlirp.sql script:
The utlirp.sql script invalidate all existing PL/SQL modules (procedures, functions, packages, types, triggers, views) in a database.
This script must be run when it is necessary to regenerate the
compiled code because the PL/SQL code format is inconsistent with the Oracle executable.
Please note that this script does not recompile invalid objects automatically so you must restart the database and explicitly invoke utlrp.sql to recompile invalid objects.
Hope you won't get into this errors, but if so, you can use this post as a reference... :-) Feel free to leave a comment or to share your experience with 10g upgrade! You can also register for email subscription to this blog using the box at the right side menu bar. Aviad |
