Skip to content

ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_AQ

SYMPTOMS

Datapatch throws error in PDB while issuing

datapatch -verbose

Error at line 6507: ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_AQ
Error at line 7235: ORA-01918: user ‘DVSYS’ does not exist

CAUSE

Scheduler job running in SYS schema

select JOB_NAME, STATE, LAST_START_DATE from dba_scheduler_jobs where job_name like ‘AQ%’;

SOLUTION

Stop scheduler job in SYS schema that show up in this sql  and Run data patch again.

Select JOB_NAME, STATE, LAST_START_DATE from dba_scheduler_jobs where job_name like ‘AQ%’;

exec DBMS_SCHEDULER.STOP_JOB(job_name => ‘owner.job_name’,force => TRUE);

datapatch -verbose

let datapatch run and on side keep stopping that scheduler job because it will come back with different name.

exec DBMS_SCHEDULER.STOP_JOB(job_name => ‘owner.job_name’,force => TRUE);

Extras

This sql provides patch information.

sqlplus / as sysdba
set markup html on
spool registry_sqlpatch.html
sho pdbs
select * from pdb_plug_in_violations where STATUS!=’RESOLVED’;
sho con_name
show parameter cpu
select * from dba_registry_sqlpatch;
select comp_id, status, version from dba_registry;
SELECT owner,object_name,object_type FROM dba_objects WHERE status=’INVALID’ AND owner in (‘SYS’,’SYSTEM’,’SYSMAN’,’CTXSYS’,’ORDSYS’,’MDSYS’,’EXFSYS’,’WKSYS’,’WKPROXY’,’WK_TEST’,’OLAPSYS’,’OUTLIN’,’TSMSYS’,
‘FLOWS_FILES’,’SI_INFORMTN_SCHEMA’,’ORACLE_OCM’,’ORDPLUGINS’,’ORDDATA’,’DBSNMP’,’SDO’);
select owner,directory_name,directory_path from dba_directories where directory_name like ‘%OPATCH%’;
select * from OPATCH_XML_INV;
select xmltransform(dbms_qopatch.get_opatch_install_info() , dbms_qopatch.get_opatch_xslt()) from dual;
select xmltransform (dbms_qopatch.get_opatch_lsinventory(), dbms_qopatch.GET_OPATCH_XSLT()) from dual;
select dbms_qopatch.get_opatch_lsinventory() from dual;
select dbms_sqlpatch.verify_queryable_inventory from dual;
exec dbms_qopatch.get_sqlpatch_status;
alter session set container = PDB$SEED;
sho con_name
select * from registry$sqlpatch;
select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;
select OWNER, OBJECT_NAME, STATUS from dba_objects where STATUS = ‘INVALID’;
alter session set container = PDB;
sho con_name
select * from registry$sqlpatch;
select COMP_ID, COMP_NAME, VERSION, STATUS from dba_registry;
select OWNER, OBJECT_NAME, STATUS from dba_objects where STATUS = ‘INVALID’;
set markup html off;
spool off
quit

 

 

 

 

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: