ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter in Oracle

ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter in Oracle

ENABLE_AUTOMATIC_MAINTENANCE_PDB parameter will introduced in 12c for automatic maintenance task configured by Oracle will run on all PDB’s plus CDB. It is set default to TRUE means enabled for all PDB’s and CDB.

Check the value of parameter in PDBs and CDB

SQL> show parameter ENABLE_AUTOMATIC_MAINTENANCE_PDB

NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean TRUE

Change the value with ALTER SYSTEM for current connected PDB or CDB

ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE;
ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE CONTAINER=CURRENT;

Change the value for all PDBs and CDB Databases

ALTER SYSTEM SET enable_automatic_maintenance_pdb=TRUE CONTAINER=ALL;

For changes in spfile for permanent effect

ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE scope=both;


ALTER SYSTEM SET enable_automatic_maintenance_pdb=TRUE CONTAINER=ALL scope=both;

Note: FOR CDB Changes reflect in SPFILE present in ORACLE Home location.
For PDB connect changes found in dictionary view PDB_SPFILE$
col db_uniq_name format a10
col name format a15
col value$ format a10
select db_uniq_name, pdb_uid, name, value$ from pdb_spfile$;

Example: I change the value of CDB to root, but my PDB database is true.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> ALTER SYSTEM SET enable_automatic_maintenance_pdb=FALSE scope=both;
System altered.
SQL>
SQL> conn hr@xepdb1
Enter password:
Connected.

SQL> show parameter enable_automatic
NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean TRUE

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> show parameter enable_automatic
NAME                             TYPE    VALUE
-------------------------------- ------- -----
enable_automatic_maintenance_pdb boolean FALSE

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.