ORA-28575 Unable to open RPC connection to external procedure agent

After patching ,In oracle database user is facing the below error.

Error : ORA-28575

Resolution:

Please add the EXTPROC  entry in Listener.ora and tnsnames.ora and reload the LISTENER on all nodes of a cluster.

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

    )

  )

Note: Please make sure soft links are pointed to our common location from GRID_HOME/network/admin and ORACLE_HOME/network/admin.

[frbucoras1l.em.health.ge.com:/u01/app/oracle/product/11.2.0.3/dbee_1/network/admin]

stgcrln1:oracle>ls -ltrh

total 28K

lrwxrwxrwx 1 oracle oinstall   51 Apr 19  2013 tnsnames.ora -> /u02/app/oracle/admin/ORABUC1S/network/tnsnames.ora

lrwxrwxrwx 1 oracle oinstall   47 Apr 19  2013 ldap.ora -> /u02/app/oracle/admin/ORABUC1S/network/ldap.ora

lrwxrwxrwx 1 oracle oinstall   51 Apr 19  2013 listener.ora -> /u02/app/oracle/admin/ORABUC1S/network/listener.ora

** ** There was a problem getting a list: code: 29548 SQLState: 99999 Message: ORA-29548: Java system class reported: could not identify release specified in classes.bin

Run the post patch steps of JAN CPU 2015 (particularly OJVM post patch) once again in proper order as per the patch document to resolve the JAVA error.

ISSUE : Oracle DOC: OWB Component was not Upgraded While Upgrading The Oracle Database From 11.1 To 11.2 (Doc ID 1587473.1)

After upgrading below databases from 11.2.0.3 to 11.2.0.4. We found OWB (Oracle Warehouse Builder) was still in 11.2.0.3.0. As per oracle Doc I have followed below steps to upgrade OWD to 11.2.0.4.

Before Status:

COMP_ID         COMP_NAME         VERSION                        STATUS

--------------- ----------------- ------------------------------ ----------

OWB             OWB               11.2.0.3.0                     VALID

Work Around:

SQL>$ORACLE_HOME/owb/UnifiedRepos/clean_owbsys.sql

SQL>$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

After Status:

COMP_ID         COMP_NAME     VERSION                        STATUS

--------------- ------------- ------------------------------ ----------

OWB             OWB           11.2.0.4.0                     VALID

User error while running the application

ERROR:

Nested exception is:
java.sql.SQLException: [SQLState=72000][ErrorCode=1591]ORA-01591: lock held by in-doubt distributed transaction 21.3.3075

       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
       at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:911)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)

WORK AROUND:

As per meta link note 1012842.102 we followed below steps:

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from pending_trans$;

select * from pending_sessions$;

select * from pending_sub_sessions$;

From the above queries keep all the value of LOCAL_TRAN_ID in each table and try to force commit or rollback.

1)     ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

2)     EXEC DBMS_TRANSACTION.rollback_force('LOCAL_TRAN_ID');  (Need to mention the TRAN_ID which we got from the above queries)

3)   COMMIT ;

4)   ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

5)   EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');

 6)    COMMIT;

Repeat each steps for each LOCAL_TRAN_ID, turn on recovery for distributed transaction

crosscheck these tables (must be empty):

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from pending_trans$;

select * from pending_sessions$;

select * from pending_sub_sessions$;

Component status was INVALID after upgrade of 11.2.0.3 Oracle Database

Solution:

As per oracle metalink i have followed below steps.

1- Deinstall Multimedia

sqlplus '/as sysdba'
@$ORACLE_HOME/rdbms/admin/catcmprm.sql ORDIM

2- Review those objects and if they exist remove them

3- Install Multimedia

sqlplus '/as sysdba'
@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX
@$ORACLE_HOME/ord/im/admin/catim.sql

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=501353721580072&id=1932947.1&_afrWindowMode=0&_adf.ctrl-state=i52nhyv48_436

Locally vs. Dictionary Managed Tablespaces

 When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.


Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.
Execute the following statement to create a dictionary managed
tablespace:

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M 
      EXTENT MANAGEMENT DICTIONARY 
      DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed
tablespace:
SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;