Too many AQ$_PLSQL_NTFN_XXXXX jobs In Oracle Database

SYMPTOM

There are more than one million  AQ$_PLSQL_NTFN_xxxxxxxx scheduler jobs in oracle database.  

sql hangs for querying dba_scheduler_jobs.

Please note there are a couple of bugs on this issue, the steps are not necessarily working for every database versions.

SQL>select count(*) from dba_scheduler_jobs;

WORKAROUND

1)set job_queue_processes =0

SQL>alter system set job_queue_processes=0 scope=memory;

2)Stop and Drop all those AQ$_PLSQL_NTFN scheduler jobs:

— add rownum<100000 into below sqls, if the sql hangs.

SQL>select 'EXEC DBMS_SCHEDULER.STOP_JOB ('''|| job_name ||''', force => TRUE);' 
    from dba_scheduler_jobs 
    where job_name like 'AQ$_PLSQL_NTFN_%'
    and state='RUNNING';


SQL> select ' exec dbms_scheduler.drop_job('''||job_name ||''');' 
     from dba_scheduler_jobs 
     where job_name like 'AQ$_PLSQL_NTFN_%';

3)

SQL>select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
    where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

DECLARE 
po dbms_aqadm.aq$_purge_options_t; 
BEGIN 
po.block := TRUE; 
DBMS_AQADM.PURGE_QUEUE_TABLE( 
queue_table => 'SYS.AQ_SRVNTFN_TABLE_1', 
purge_condition => 'qtview.queue = ''AQ$_AQ_SRVNTFN_TABLE_1_E''', 
purge_options => po); 
END; 
/

select count(*) from sys.aq$AQ_SRVNTFN_TABLE_1 
where queue='AQ$_AQ_SRVNTFN_TABLE_1_E';

4) Collect stats.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

5) Rebuild indexes of DBA_SCHEDULER_JOBS

6) Bounce the instance.

SQL>shutdown immediate ;
SQL>startup;
SQL>alter system set job_queue_processes=1000 scope=memory;

PMON (ospid: nnnn): terminating the instance due to error 481

SYMPTOM

ASM failed to start up with below messages in ASM alert log:

Tue Oct 02 10:54:12 2018
ERROR: Network OS Ping failed to inst 1 on IP (169.254.255.151),
Tue Oct 02 10:54:55 2018
Instance Critical Process (pid: 11, ospid: 8470, LMON) died unexpectedly
PMON (ospid: 8443): terminating the instance due to error 481

Subscribe to get access

Read more of this content when you subscribe today.

aws dms table error

AWS DMS ( Data Migration Service ) task has been running for many hours, then failed with error “Table error” for Load State.

1)Check target database,  99% records have been migrated from Oracle source database to PostgreSQL RDS.

Subscribe to get access

Read more of this content when you subscribe today.