Oracle 11.2.0.4 error PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’

After upgrading the database to 11.2.0.4 and applying the latest Patch Set Update(October 2015 PSU) (11.2.0.4.8) , a database account could not perform any update or delete transaction on a table (that has context index) and received the following errors:

 

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘SYNCRN’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

Solution:

There is a patch provided by Oracle to fix this OR perform the following “workaround” by recreating ctxsys.syncrn procedure:

SQL> sqlplus ‘/as sysdba’

SQL>alter session set current_schema=CTXSYS;

SQL>create or replace procedure syncrn (
  ownid IN binary_integer,
  oname IN varchar2,
  idxid IN binary_integer,
  ixpid IN binary_integer,
  rtabnm IN varchar2,
  srcflg IN binary_integer,
  smallr IN binary_integer
)
  authid definer
  as external
  name “comt_cb”
  library dr$lib
  with context
  parameters(
    context,
    ownid  ub4,
    oname  OCISTRING,
    idxid  ub4,
    ixpid  ub4,
    rtabnm OCISTRING,
    srcflg ub1,
    smallr ub1
);
/

// execute the following to recompile all database objects:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

//Then check that your database components are “valid”:

SQL> select * from dba_registry;

//check if there is any invalid objects under CTXSYS:

SQL>select owner, count(*) from dba_objects where status <>’VALID’ group by owner order by 1;

 

Remark: to find the patch set update applied on your database use the following query:

select * from sys.registry$history;

ORA-00604: error occurred at recursive SQL level 1 ORA-06598: insufficient INHERIT PRIVILEGES privilege ORA-06512: at “XDB.DBMS_CSX_INT”, line 1 while upgrading to 12c

while upgrading your database to 12c , its very important that you monitor your alert.log file to check for errors and potential problems.

to find out the location of you alert log file, query the following:

SQL> select * form v$diag_info;

one of the databases that i have upgraded, i found the following error being thrown in the alert.log file:

XDB SGA reset to NULL.

ORA-00604: error occurred at recursive SQL level 1

ORA-06598: insufficient INHERIT PRIVILEGES privilege

ORA-06512: at “XDB.DBMS_CSX_INT”, line 1

this error can be ignored, to ensure that your XDB component is ok run the utilrip after the upgrade is completed:

SQL>@?/rdbms/admin/utlrp.sql

make sure that the XDB is fine by querying:

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

so basically the error is not dangerous but you need to verify that everything is fine.

*************************************** …….*****************************************   🙂