MOS Doc ID 2175443.1 goes into more detail, including the following quote:
It is possible to set job_queue_processes at pdb level on 12.1.0.2 multitenant database, but it does not work correctly.
I have managed to reproduce the actual behavior. Of particular concern is that this allows a local user on one PDB to disrupt things on other PDBs.
My test system is running 12.1.0.2, multi-tenant with two PDBs, PDB1 and PDB2. Each PDB has a local user with DBA privileges, dba1, and dba2 respectively.
[oracle@awrw admin]$ sqlplus dba1/oracle@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:34:41 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 02 2017 13:33:46 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 1000 SQL> alter system set job_queue_processes=0 container=current; System altered. SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 0 SQL>
So dba1 seems to have changed job_queue_processes, in their own PDB, pdb1.
The alert log reveals some cause for concern:
CJQ0 started with pid=34, OS id=31546 Stopping background process CJQ0 Sun Apr 02 13:35:01 2017 ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
Sure enough, in PDB2, even though job_queue_processes parameter seems to be set to it’s default value, we are unable to use scheduler:
[oracle@awrw admin]$ sqlplus dba2/oracle@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:44:19 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 02 2017 13:43:25 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 1000 SQL> exec sys.dbms_scheduler.create_job( 'DUMMY_JOB', job_type=>'PLSQL_BLOCK', job_action=>'BEGIN NULL; END;'); PL/SQL procedure successfully completed. SQL> exec sys.dbms_scheduler.run_job( 'DUMMY_JOB', false); BEGIN sys.dbms_scheduler.run_job( 'DUMMY_JOB', false); END; * ERROR at line 1: ORA-27492: unable to run job "DBA2"."DUMMY_JOB": scheduler unavailable ORA-06512: at "SYS.DBMS_ISCHED", line 209 ORA-06512: at "SYS.DBMS_SCHEDULER", line 594 ORA-06512: at line 1
I connect back to PDB1 and reset the parameter:
[oracle@awrw admin]$ sqlplus dba1/oracle@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:46:40 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 02 2017 13:34:41 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter system set job_queue_processes=1000 container=current; System altered.
In the alert log I can see Job Queue Co-ordinator process is restarted:
Starting background process CJQ0 Sun Apr 02 13:47:08 2017 ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH; Sun Apr 02 13:47:08 2017 CJQ0 started with pid=34, OS id=2227
And sure enough I am now able to run schedule jobs in PDB2.
[oracle@awrw ~]$ sqlplus dba2/oracle@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:48:51 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 02 2017 13:44:19 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> exec sys.dbms_scheduler.run_job( 'DUMMY_JOB', false); PL/SQL procedure successfully completed.
Pingback: Parameter Recommendations for Oracle Database 12c – Part II | Upgrade your Database - NOW!