Tales From A Lazy Fat DBA

Hi Everyone,

Todays’ post was about an interesting scenario faced by my colleagues while they were doing an application upgrade and migration. Due to some reasons, the upgrade was supposed to be rolled back, and as a part of the strategy, they had to drop specific schemas touched by the failed upgrade and import a healthy backup taken just before the activity.

The steps were simple and were well discussed and vetted. While doing the import, they started getting errors which reported about the AQ (Oracle Advanced Queues). The error pointed out that the import job had failed to create the AQs with a ‘no data found’ message. Many of the AQs were not imported, and the queues were in an INVALID state.

21-SEPT-23 17:20:40.797: ORA-39083: Object type PROCDEPOBJ:"TESTDBSC"."DPF_OUT" failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
	
Failing sql is:
BEGIN
SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('ASHQ18371NDDU1842NXXXXXXXXXXX'),
'AQTST_PTT_OUT','AQ$_AQTST_PTT_OUT_E',1,0,0,0,0,'exception queue');COMMIT; END;

We have even tried to drop the schema, but failed with errors reporting about missing advance queue tables. Tried to drop the the queue table and force all queues to be stopped and dropped by the system, but landed into all different errors.

SQL> drop user TESTDBSC cascade;
drop user TESTDBSC cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24002: QUEUE_TABLE TESTDBSC.AQTST_XXX does not exist
ORA-06512: at "SYS.DBMS_AQADM", line 707
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7735
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7070
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7402
ORA-06512: at "SYS.DBMS_AQADM", line 702
ORA-06512: at line 1




begin dbms_aqadm.stop_queue (queue_name => 'AQ$_AQTST_PTT_IN_E', enqueue => TRUE, dequeue => FALSE); end;
*
ERROR at line 1:
ORA-04063: TESTDBSC.AQTST_XXX_IN has errors
ORA-06512: at "SYS.DBMS_AQADM", line 788
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8702
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 926
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8687
ORA-06512: at "SYS.DBMS_AQADM", line 783
ORA-06512: at line 1

During the investigation, we found there were ~ 70 individual queue tables in the db that need to be cleared. There are no entries in DBA_QUEUE_TABLES so we can’t use the AQ api to force drop them. There were few child or orphan objects that still exist and need to be cleared. These child or dependent objects were – Views, Sequences, EVALUATION CONTEXT etc. apart from Queues and Tables.

OBJECT_ID	   OBJECT_NAME	                    OBJECT_TYPE
-----------    ------------------------------   --------------------------------------- 
124238	       AQ$_AQTST_FATDBA_UPD_V	        EVALUATION CONTEXT
124239	       AQ$_AQTST_FATDBA_UPD_N	        SEQUENCE
124259	       AQ$_AQTST_MONKEYINDC_UPD_N	    VIEW

As a breakthrough, we found all of the objects have the prefix AQTST, so it will be little easy to clean them.

SQL> select QUEUE_TABLE from dba_queue_tables where owner='TESTDBSC';

QUEUE_TABLE
------------------------------------------------------------
AQTST_XXXXXXXXXXXX_UPD
AQTST_XXXXXX_IN_UPD
AQTST_XXXXXXXXXXXX_UPD
AQTST_XXXXXX_IN_UPD
AQTST_XXXXXX_OUT_UPD

We first started dropping SEQUENCES, just to lower down number of elements followed by Views and Queue Tables. For Queue tables, we’ve set 10851 debugging event, this is to allow drop command to drop queue tables. This was kind of a last resort to drop queue table if all other options fail. This makes sense as we were manually cleaning AQ’s metadata after this operation.

select 'drop view "'||view_name||'";' as statements from user_views;

drop sequence AQ$_AQTST_XXXXXXXXXXXX_UPD_N;	
drop sequence AQ$_AQTST_XXXXXXXXXXXX_UPD_N;
drop sequence AQ$_AQTST_XXXX_XXXXX_IN_N;
.....
........

ALTER SESSION SET EVENTS '10851 trace name context forever, level 2';

-- Above command went fine, hence moved to the below step
-- Connected with the same schema that we tried to drop earlier and were failing 
select 'drop table "'||table_name|| ' cascade constraints";' as statements from user_tables;

Next, we need to manually start the cleanup.
Note: Take a valid backup before proceeding with the manual cleanup as it involves deleting from SYS objects.

We started the cleanup for remaining AQ objects related to %AQTST% from user # (TESTDBSC). First we deleted all object IDs of orphan objects from system tables i.e. SYSTEM.AQ$_QUEUES, SYS.OBJ$, SYSTEM.AQ$_QUEUE_TABLES etc.

Note: You can use Oracle’s provided AQ & MGW Health Check and Configuration Script (Doc ID 1193854.1) which will help you to quickly point out INVALID objects, object IDs and AQs consistency information.

SQL>  DELETE FROM SYSTEM.AQ$_QUEUES WHERE table_objno in (<object_id>,<object_id>,<object_id>,<object_id>,<object_id>);
1 rows deleted.

SQL>  DELETE FROM SYS.OBJ$ WHERE obj# IN (<object_id> ,<object_id>,<object_id>,<object_id>,
<object_id>,<object_id>,<object_id>,<object_id>);
34 rows deleted.

DELETE FROM SYSTEM.AQ$_QUEUE_TABLES where objno in (<object_id>,<object_id>,<object_id>,
<object_id>,<object_id>,<object_id><object_id>,<object_id>)
/

SQL> commit;
Commit complete.

Next we decided to drop the rule sets and rule evaluation contexts.

execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT ('TESTDBSC.AQ$_AQTST_XXXXXXXXXXXX_UPD_V',TRUE)
execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT ('TESTDBSC.AQ$_AQTST_XXXX_XXXX_V',TRUE)
..
....
execute DBMS_RULE_ADM.DROP_RULE_SET ('TESTDBSC.AWARD_XXX_XXXX_N',TRUE);
execute DBMS_RULE_ADM.DROP_RULE_SET ('TESTDBSC.AWARD_XXXX_XXXX_R',TRUE);
...
....

Now we have all of the queues named %AQTST% properly cleaned from the impacted schema. Just as a precautionary measure, we flushed our shared pool. We tried to drop the user after manual cleanup and were able to drop the schema which was earlier throwing AQ related errors.

So, in short this all happened because there was a AQ metadata mismatch which lead to failed import as it contained queues and we had to manually cleanup the advance queue’s metadata.

Hope It Helped!
Prashant Dixit