Tuesday, February 24, 2009

Invalid CATALOG and CATAPROC after after upgradation to 10g

HI ,

We upgraded our 9i database to 10g and after that our CATALOG & CATALOG got Invalid ..


SQL> select comp_name, version, status,comp_id from dba_registry;

COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- -----------
Oracle Database Catalog Views 10.2.0.3.0 INVALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD

Solution :

$sqlplus "/as sysdba"
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 9.2.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

SQL> select comp_name, version, status,comp_id from dba_registry;

COMP_NAME VERSION STATUS COMP_ID
--------------------------------------------- ------------------------------ ----------- ---------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG
Oracle Database Packages and Types 10.2.0.3.0 INVALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA
Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS
Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD


CATALOG is valid now . But CATAPROC is still invalid . To valid the same ...

SQL> exec DBMS_REGISTRY.valid('CATPROC');



COMP_NAME VERSION STATUS COMP_ID
------------------------- ---------------------------- ----------- ------------------------------
Oracle Database Catalog Views 10.2.0.3.0 VALID CATALOG

Oracle Database Packages and Types 10.2.0.3.0 VALID CATPROC
Oracle Real Application Clusters 10.2.0.3.0 INVALID
RAC
JServer JAVA Virtual Machine 10.2.0.3.0 VALID
JAVAVM
Oracle XDK 10.2.0.3.0 VALID XML
Oracle Database Java Packages 10.2.0.3.0 VALID CATJAVA

Oracle interMedia 10.2.0.3.0 VALID ORDIM
Spatial 10.2.0.3.0 VALID
SDO
Oracle Text 10.2.0.3.0 VALID CONTEXT
OLAP Analytic Workspace 10.2.0.3.0 INVALID APS

Oracle OLAP API 10.2.0.3.0 VALID XOQ
OLAP Catalog 10.2.0.3.0 VALID AMD



For more information refer to the docs 457861.1

Saturday, February 14, 2009

Metalink Important Note IDs

1. How to run OATM migration utility ---- Note:404954.1

2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1

3. Upgrading Oracle Applications ---- Note:289788.1

4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1

6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1

7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1

8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1

9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1

10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1

11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1

12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1

13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1

14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1

15.Shared APPL_TOP FAQ ---- Note:243880.1

16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1

17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1

18.Release 11i Adpatch Basics ---- Note:181665.1

19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1

20.How to Create a Custom Concurrent Manager ---- Note:170524.1

21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1

22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

Tuesday, February 10, 2009

Solution for 500 Internal server Error in R12

Error:
500 Internal Server Error

java.lang.NoClassDefFoundError
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:161)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.loadServlet(HttpApplication.java:2231)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4617)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.findServlet(HttpApplication.java:4541)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpApplication.getRequestDispatcher(HttpApplication.java:2821)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:740)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:451)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:299)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0)
].server.http.AJPRequestHandler.run(AJPRequestHandler.java:187)


Solution:

Autoconfig

1) Bring down opmn services.
Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh stop apps/apps

2) Verify no process is listening for s_java_object_cache_port value

3) Change s_java_object_cache_port on all the nodes in the context file

Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/appl/admin>vi VIS_bkpsvr.xml

Before:
12348

After:
12349

4) Run autoconfig on apps node.

Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./ adautocfg.sh

5) Start opmn services on apps node.

Path: /tst03/oracle/VIS/inst/apps/VIS_bkpsvr/admin/scripts>./adopmnctl.sh start apps/apps

ORA-19595: archivelog already included in backup conversation

If a standby database is brought down and then later
restarted then RMAN backups may fail with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/18/2004 10:00:56
ORA-19595: archivelog /ora_logs/archivelogs/2_6512.arc already included in backup conversation

Workaround:
Find the duplicate archivelog names using following query -
select al.recid, al.name from v$archived_log al
where al.status = 'A' and al.standby_dest= 'NO'
minus
select al.recid, al.name from v$archived_log al,
(select max(recid) recid
from v$archived_log
where status = 'A'
group by name) keepal
where al.status = 'A'
and al.standby_dest = 'NO'
and keepal.recid = al.recid;

Then:
RMAN> catalog archivelog '';

This will register the missing archivelogs.

Monday, February 9, 2009

ORA-12557: TNS:protocol adapter not loadable

Problem Description
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,

With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12557: TNS:protocol adapter not loadable

Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.

Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.

C:\>sqlplus.exe maximsg/a@192.168.100.160/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12557: TNS:protocol adapter not loadable

Connecting to old oracle database using new binaries are not supported in oracle and error will return.

Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.

Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Alternatively you can follow below.

Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.

Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.