Autoconfig failed during bisdblrp.sql

Autoconfig failed during bisdblrp.sql

Issue: Found Ora-600 error in alert log while dropping a database link.

Error Message :
ORA-00600: internal error code, arguments: [16500], [kqdDBLinkUpdate], [3], [2], [EDW_APPS_TO_WH.WORLD], [], [], [], [], [], [], []

Current SQL from trace file:
drop database link EDW_APPS_TO_WH.WORLD;

Reason:
There are 2 database links in the database with same name.

SQL> select * from dba_db_links where db_link='EDW_APPS_TO_WH.WORLD';

APPS       EDW_APPS_TO_WH.WORLD    APPS    TEST   24-JUL-12
APPS       EDW_APPS_TO_WH.WORLD    APPS    TEST   25-JUL-12


Solution:
Drop the database links from SYS.LINK$ table using below command:

SQL> delete from sys.link$
 2  where NAME='EDW_APPS_TO_WH.WORLD';

Patch driver logfile has jdbc connection error

Issue:

adwork001.log has following errors:

Calling /d01apps/TS/10.1.3/appsutil/jdk/jre/bin/java ...
Exception in thread "main" java.sql.SQLException: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:480)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:413)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:510)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:203)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:510)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at oracle.apps.ad.worker.AdJavaWorker.getAppsConnection(AdJavaWorker.java:1041)
        at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:276)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:328)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:328)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:421)
        at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:630)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:206)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:966)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:292)
        ... 8 more
Caused by: java.net.ConnectException: Connection refused
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:352)
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:214)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:201)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:377)
        at java.net.Socket.connect(Socket.java:530)
        at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:120)
        at oracle.net.nt.ConnOption.connect(ConnOption.java:126)
        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:306)
        ... 13 more

Cause:

jdbc connect string has wrong node entry. (Instead of DB hostname, the tnsentry has Apps tier hostname in the jdbc connect string)

Verification:

echo $APPS_JDBC_URL

verify "s_apps_jdbc_connect_descriptor" in $CONTEXT_FILE

Also verify, select node_name from fnd_nodes;

Solution:

If autoconfig is not run on DB Tier, bring down services, Run Autoconfig on DB Tier and then on APP Tier.

If Autoconfig is already ran on DB Tier, change the "s_apps_jdbc_connect_descriptor" parameter with connect DB host entry and run autoconfig in App node. Then resume the patch from where it stopped.

How to find whether CPU patch is applied to RDBMS Oracle Home

Problem:

I want to find out what all CPU patches/Patch set updates have been applied to my RDBMS Oracle Home.

Answer:

Use the below SQL command to see all the PSUs/CPUs applied to your database.

select * from dba_registry_history ;

Alternately, we can check successful CPU patch installation logs under $ORACLE_HOME/cfgtoollogs/catbundle directory.

ORA-01031: insufficient privileges while creating a database link

Problem:

While creating a database link, the user is getting insufficient privileges error:

SQL> create database link ASCP_TO_PROD connect to apps_ro identified by apps_ro using 'PROD';

ORA-01031: insufficient privileges while creating a database link


Fix:

SQL> alter session set current_schema = APPS_RO;

Session altered.

SQL> create database link ASCP_TO_PROD connect to apps_ro identified by apps_ro using 'PROD';

Database link created.

User wants to query objects without prefix

Problem:

We have granted user DUMMY with select on all tables and views of APPS schema. But the user wants to access the tables of APPS schema without using prefix.

SQL> select count(*) from fnd_user;

ORA-00942: table or view does not exist

Fix:

Create a trigger for schema to set current_schema at session level to apps and user need not use the prefix anymore.

SQL> conn apps/apps

SQL> CREATE OR REPLACE TRIGGER dummy_logon_trg
AFTER logon ON DUMMY.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

SQL> conn dummy/dummy

SQL> select count(*) from fnd_user;

COUNT(*)
----------
43

Gather Schema Statistics failing after upgrading DB to 11.2.0.2

Problem:

After upgrading the database to 11.2.0.2, Gather Schema Statistics was failing with following errors in the log file:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GME.GME_BATCH_HEADER***ORA-12805: parallel query server died unexpectedly***

Following errors are observed in alert log as well:

ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2B5FAA85D510], [], [], [], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [qesaMSR2SorRecMerge()+265] [SIGSEGV] [ADDR:0x30] [PC:0x139B739] [Address not mapped to object] []

ORA-00603: ORACLE server session terminated by fatal error
ORA-24557: error 600 encountered while handling error 600; exiting server process
ORA-00600: internal error code, arguments: [17147], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17147], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
ORA-00039: error during periodic action
ORA-00600: internal error code, arguments: [17114], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
opidrv aborting process P007 ospid (23806) as a result of ORA-603

Fix:
Bug 10013177: FUNCTIONAL INDEX CAUSES MAX DECODE GROUP BY SQL TO TRUNCATE VALUES:

Workaround is:

1) Set _replace_virtual_columns=false

2) Set _disable_function_based_index=true

3) Gathering table stats using degree 1.

Permanent fix is:

Apply Interim patch 10013177