Administering a pluggable database (PDB) involves a subset of the tasks required to administer a non-CDB.
In this subset of tasks, most are the same for a PDB and a non-CDB, but differences exist. For example, there are differences when you modify the open mode of a PDB. Also, a PDB administrator is limited to managing a single PDB and cannot manage other PDBs in the multitenant container database (CDB).
Tasks Common to PDBs and Non-CDBs
Most administrative tasks are the same for a PDB and a non-CDB.
When you are administering a PDB, you can modify the PDB with an ALTER DATABASE
, ALTER PLUGGABLE DATABASE
, or ALTER SYSTEM
statement. You can also execute DDL statements on the PDB. The following table describes some of these tasks common to a PDB and non-CDB.
Administrative Tasks Common to PDBs and Non-CDBs
Task | Description |
Managing tablespaces | You can create, modify, and drop tablespaces for a PDB. You can specify a default tablespace and default tablespace type for each PDB. Also, there is a default temporary tablespace for each PDB. You optionally can create additional temporary tablespaces for use by individual PDBs. |
Managing data files and temp files | Each PDB has its own data files. You can manage data files and temp files in the same way that you would manage them for a non-CDB. You can also limit the amount of storage used by the data files for a PDB by using the STORAGE clause in a CREATE PLUGGABLE DATABASE or ALTER PLUGGABLE DATABASE statement. |
Managing schema objects | You can create, modify, and drop schema objects in a PDB in the same way that you would in a non-CDB. You can also create triggers that fire for a specific PDB. When you manage database links in a CDB, the root has a unique global database name, and so does each PDB. The global name of the root is defined by the DB_NAME and DB_DOMAIN initialization parameters. The global database name of a PDB is defined by the PDB name and the DB_DOMAIN initialization parameter. The global database name of each PDB must be unique within the domain. |
Tasks Specific to CDBs
Some administrative tasks cannot be performed when the current container is a PDB.
The following tasks are performed by a common user for the entire CDB or for the CDB root when the current container is the root:
- Starting up and shutting down a CDB instance
- Modifying the CDB or the root with an
ALTER DATABASE
statement - Modifying the CDB or the root with an
ALTER SYSTEM
statement - Executing data definition language (DDL) statements on a CDB or the root
- Managing the following components:
- Processes
- Memory
- Errors and alerts
- Diagnostic data
- Control files
- The online redo log and the archived redo log files
- Undo
- Creating, plugging in, unplugging, and dropping PDBs
A common user whose current container is the root can also change the open mode of one or more PDBs. Similarly, a common user or local user whose current container is a PDB can change the open mode of the current PDB.
Managing Connections to a PDB
You manage connections for a PDB in the same way as for a non-CDB, with some special considerations.
Connecting to a PDB
You can use several techniques to connect to a PDB with the SQL*Plus CONNECT
command.
This section assumes that you understand how to connect to a non-CDB in SQL*Plus.
You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT
command:
- Local connection with operating system authentication
- Database connection using easy connect
- Database connection using a net service name
To connect to a PDB using the SQL*Plus CONNECT command:
- Configure your environment so that you can open SQL*Plus.
- Start SQL*Plus with the
/NOLOG
argument:
sqlplus /nolog
- Issue a
CONNECT
command using easy connect or a net service name to connect to the PDB.
To connect to a PDB, connect to a service with a PDB
property.
Connecting to a PDB in SQL*Plus Using the PDB’s Net Service Name
The following command connects to the hr
user using the hrapp
service. The hrapp
service has a PDB
property for the hrpdb
PDB. This example assumes that the client is configured to have a net service name for the hrapp
service.
CONNECT [email protected]
Managing Services for PDBs
You can create, modify, or remove services for a PDB.
Services for PDBs
Each PDB has a default service, but you can create your own using SRVCTL or DBMS_SERVICE
.
The PDB Property
The PDB
property associates a service with a PDB. When a client connects to a service with a PDB
property, the current container for the connection is the PDB.
The PDB
property is required only when you do either of the following:
- Create a service
- Modify the
PDB
property of a service
You do not specify a PDB
property when you start, stop, or remove a service. Also, you do not need to specify a PDB
property when you modify a service without modifying its PDB
property.
You can view the PDB
property for a service by querying the ALL_SERVICES
data dictionary view. Alternatively, when using the SRVCTL utility, you can use the srvctl config service
command.
Default and User-Defined Services
Creating a PDB creates a new default service for the PDB automatically.
Each database service name must be unique in a CDB, and each database service name must be unique within the scope of all the CDBs whose instances are reached through a specific listener. The default service has the same name as the PDB. You cannot manage this service, which you should only use for administrative tasks.
Always use user-defined services for applications. The reason is that you can customize user-defined services to fit the requirements of your applications. Oracle recommends that you not use the default PDB service for applications.
In an Oracle Clusterware environment, you must create an Oracle Clusterware resource for each service that is created for the PDB. When your database is being managed by Oracle Restart or Oracle Clusterware, and when you use the SRVCTL utility to start a service with a PDB property for a PDB that is closed, the PDB is opened in read/write mode on the nodes where the service is started. However, stopping a PDB service does not change the open mode of the PDB.
When you unplug or drop a PDB, the services of the unplugged or dropped PDB are not removed automatically. You can remove these services manually.
Tools for Managing Services
Oracle recommends using the SRVCTL utility to create and modify services. Alternatively, you can use the DBMS_SERVICE
package.
SRVCTL
If your single-instance database is being managed by Oracle Restart or your Oracle RAC database is being managed by Oracle Clusterware, then use the Server Control (SRVCTL) utility to create, modify, or remove the service.
To create a service for a PDB using the SRVCTL utility, use the add
service
command and specify the PDB in the -pdb
parameter. If you do not specify -pdb
, then the service is associated with the root.
To modify the PDB
property of a service using the SRVCTL utility, use the modify
service
command and specify the PDB in the -pdb
parameter. To remove a service for a PDB using the SRVCTL utility, use the remove
service
command.
You can use other SRVCTL commands to manage the service, such as the start service
, stop service
, and relocate service
commands, even if they do not include the -pdb
parameter.
The PDB name is not validated when you create or modify a service with the SRVCTL utility. However, an attempt to start a service with invalid PDB name results in an error.
DBMS_SERVICE
If your database is not being managed by Oracle Restart or Oracle Clusterware, then use the DBMS_SERVICE
package to create or remove a database service.
DBMS_SERVICE
exists at the root level and in each PDB. It is owned and executed by SYS
at each level. A PDB administrator cannot stop, relocate, or test the connection for a service that is owned by another PDB.
When you create a service with the DBMS_SERVICE
package, the PDB
property of the service is set to the current container. Therefore, to create a service with a PDB
property set to a specific PDB using the DBMS_SERVICE
package, run the CREATE_SERVICE
procedure when the PDB is the current container. If you create a service using the CREATE_SERVICE
procedure when the current container is the root, then the service is associated with the root.
You cannot modify the PDB
property of a service with the DBMS_SERVICE
package. However, you can remove a service in one PDB and create a similar service in a different PDB. In this case, the new service has the PDB
property of the PDB in which it was created.
You can also use other DBMS_SERVICE
subprograms to manage the service, such as the START_SERVICE
and STOP_SERVICE
procedures. You can use DBMS_SERVICE.
*_CONNECTION_TEST
procedures to check the health of a database connection during planned maintenance. Use the DELETE_SERVICE
procedure to remove a service.
Managing Services for a PDB Using SRVCTL and DBMS_SERVICE
You can create, modify, or remove a service with a PDB
property.
To manage a service with a PDB property using the SRVCTL utility:
- Log in to the host computer with the correct user account.
- Ensure that you run SRVCTL from the correct Oracle home.
- Perform one of the following operations:
- To create or modify a service, run the
add service
command, and specify the PDB in the-pdb
parameter.
- To modify the
PDB
property of a service, run themodify service
command, and specify the PDB in the-pdb
parameter.
- To remove a service, run the
remove service
command.
- To create or modify a service, run the
To create or remove a service for a PDB using the DBMS_SERVICE package:
- In SQL*Plus, ensure that the current container is a PDB.
- Run the appropriate subprogram in the
DBMS_SERVICE
package.
Creating a Service for a PDB Using the SRVCTL Utility
This example adds the salesrep
service for the PDB salespdb
in the CDB with DB_UNIQUE_NAME mycdb
:
srvctl add service -db mycdb -service salesrep -pdb salespdb
Modifying the PDB Property of a Service Using the SRVCTL Utility
This example modifies the salesrep
service in the CDB with DB_UNIQUE_NAME mycdb
to associate the service with the hrpdb
PDB:
srvctl modify service
-db mycdb
-service salesrep
-pdb hrpdb
Relocating a Service in Oracle RAC Using the SRVCTL Utility
You can use the relocate service
command to relocate a service from one Oracle RAC instance, where the service is currently running, to another instance, where it can run. This technique applies both to services for administrator-managed databases as well as singleton services for policy-managed databases.
The following command relocates service svc1
from Oracle RAC instance cdb_inst1
, where it is currently running, to instance cdb_inst2
, where it is currently not running:
srvctl relocate service
db cdb
service svc1
oldinst cdb_inst1
newinst cdb_inst2
–drain_timeout NNN
–stopoption immediate
The following command performs the same operation for a policy-managed database:
srvctl relocate service
db cdb
service svc1
currentnode cdb_inst1
targetnode cdb_inst2
–drain_timeout NNN
–stopoption immediate
Removing a Service Using the SRVCTL Utility
This example removes the salesrep
service in the CDB with DB_UNIQUE_NAME
mycdb
:
srvctl remove service
-db mycdb
-service salesrep
Creating a Service for a PDB Using the DBMS_SERVICE Package
This example creates the salesrep
service for the current PDB:
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'salesrep',
network_name => 'salesrep.example.com');
END;
/
The PDB
property of the service is set to the current container. For example, if the current container is the salespdb
PDB, then the PDB property of the service is salespdb
.
Removing a Service Using the DBMS_SERVICE Package
This example removes the salesrep
service in the current PDB.
BEGIN
DBMS_SERVICE.DELETE_SERVICE(
service_name => 'salesrep');
END;
/
Modifying the Listener Settings of a Referenced PDB
A PDB that is referenced by a proxy PDB is called a referenced PDB.
When the port or host name changes for the listener of the referenced PDB, you must modify the listener settings of the referenced PDB so that its proxy PDBs continue to function properly.
Altering the Listener Host Name of a Referenced PDB
When the host name of the listener for a referenced PDB changes, you must run an ALTER PLUGGABLE DATABASE CONTAINERS HOST
statement to reset the host name of the referenced PDB so that its proxy PDBs continue to function properly.
A proxy PDB uses a database link to establish communication with its referenced PDB during PDB creation. After communication is established, the proxy PDB communicates directly with the referenced PDB without using a database link, and the database link can be dropped. When the listener host name changes for the referenced PDB, each proxy PDB must reestablish communication with its referenced PDB.
The listener host name of a referenced PDB is a database property. When it is set, you can view the current setting by querying the DATABASE_PROPERTIES
data dictionary view.
The current user must have the ALTER DATABASE
system privilege, and the privilege must be either commonly granted or locally granted in the PDB.
- In SQL*Plus, ensure that the current container is the referenced PDB.
- Run an
ALTER PLUGGABLE DATABASE CONTAINERS HOST
statement and specify the new host name, or include theRESET
keyword to return the host name to its default setting, which is the host name of the referenced PDB. - Drop and re-create the proxy PDBs that reference the referenced PDB to reestablish communication for each proxy PDB and its referenced PDB.
Altering the Listener Host Name of a Referenced PDB
This example changes the host name for the referenced PDB to myhost.example.com
.
ALTER PLUGGABLE DATABASE CONTAINERS HOST='myhost.example.com';
Resetting the Listener Host Name to the Default Value
This example resets the host name for the referenced PDB to its default value. The default value is the host name of the referenced PDB.
ALTER PLUGGABLE DATABASE CONTAINERS HOST RESET;
Altering the Listener Port Number of a Referenced PDB
When the port number of the listener for a referenced PDB changes, you must run an ALTER PLUGGABLE DATABASE CONTAINERS PORT
statement to reset the port number of the referenced PDB so that its proxy PDBs continue to function properly.
A proxy PDB uses a database link to establish communication with its referenced PDB during PDB creation. After communication is established, the proxy PDB communicates directly with the referenced PDB without using a database link, and the database link can be dropped. When the listener port number changes for the referenced PDB, each proxy PDB must re-establish communication with its referenced PDB.
The listener port number of a referenced PDB is a database property. When it is set, you can view the current setting by querying the DATABASE_PROPERTIES
data dictionary view.
The current user must have the ALTER
DATABASE
system privilege, and the privilege must be either commonly granted or locally granted in the PDB.
- In SQL*Plus, ensure that the current container is the referenced PDB.
- Run an
ALTER PLUGGABLE DATABASE CONTAINERS PORT
statement and specify the new port number, or include theRESET
keyword to return the port number to its default setting, which is 1521. - Drop and re-create the proxy PDBs that reference the referenced PDB to re-establish communication for each proxy PDB and its referenced PDB.
Altering the Listener Port Number of a Referenced PDB
This example changes the port number for the referenced PDB to 1543
.
ALTER PLUGGABLE DATABASE CONTAINERS PORT=1543;
Resetting the Listener Port Number to the Default Value
This example resets the port number for the referenced PDB to its default value. The default value for the port number is 1521.
ALTER PLUGGABLE DATABASE CONTAINERS PORT RESET;
Modifying a PDB at the System Level
The ALTER SYSTEM
statement can dynamically alter a PDB. You can issue an ALTER SYSTEM
statement when you want to change the way a PDB operates.
When the current container is a PDB, you can run the following ALTER SYSTEM
statements:
ALTER SYSTEM FLUSH { SHARED_POOL | BUFFER_CACHE | FLASH_CACHE }
ALTER SYSTEM { ENABLE | DISABLE } RESTRICTED SESSION
ALTER SYSTEM SET USE_STORED_OUTLINES
ALTER SYSTEM { SUSPEND | RESUME }
ALTER SYSTEM CHECKPOINT
ALTER SYSTEM CHECK DATAFILES
ALTER SYSTEM REGISTER
ALTER SYSTEM { KILL | DISCONNECT } SESSION
ALTER SYSTEM SET
initialization_parameter (for a subset of initialization parameters)
All other ALTER SYSTEM
statements affect the entire CDB and must be run by a common user in the root.
The ALTER SYSTEM SET
initialization_parameter statement can modify only some initialization parameters for PDBs. All initialization parameters can be set for the root. For any initialization parameter that is not set explicitly for a PDB, the PDB inherits the parameter value from the root.
You can modify an initialization parameter for a PDB when the ISPDB_MODIFIABLE
column is TRUE
for the parameter in the V$SYSTEM_PARAMETER
view. The following query lists all initialization parameters that are modifiable for a PDB:
SELECT NAME
FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE='TRUE'
ORDER BY NAME;
When the current container is a PDB, run the ALTER SYSTEM SET
initialization_parameter statement to modify the PDB. The statement does not affect the root or other PDBs. The following table describes the behavior of the SCOPE
clause when you use a server parameter file (SPFILE) and run the ALTER SYSTEM SET
statement on a PDB.
SCOPE Setting | Behavior |
MEMORY | The initialization parameter setting is changed in memory and takes effect immediately in the PDB. The new setting affects only the PDB. The setting reverts to the value set in the root in the any of the following cases: An ALTER SYSTEM SET statement sets the value of the parameter in the root with SCOPE equal to BOTH or MEMORY , and the PDB is closed and re-opened. The parameter value in the PDB is not changed if SCOPE is equal to SPFILE , and the PDB is closed and re-opened.The PDB is closed and re-opened.The CDB is shut down and re-opened. |
SPFILE | The initialization parameter setting is changed for the PDB and stored persistently. The new setting takes effect in any of the following cases: The PDB is closed and re-opened.The CDB is shut down and re-opened. In these cases, the new setting affects only the PDB. |
BOTH | The initialization parameter setting is changed in memory, and it is changed for the PDB and stored persistently. The new setting takes effect immediately in the PDB and persists after the PDB is closed and re-opened or the CDB is shut down and re-opened. The new setting affects only the PDB. |
When a PDB is unplugged from a CDB, the values of the initialization parameters that were specified for the PDB with SCOPE=BOTH
or SCOPE=SPFILE
are added to the PDB’s XML metadata file. These values are restored for the PDB when it is plugged in to a CDB.
Modifying a PDB with ALTER SYSTEM
To modify a PDB at the system level, use the ALTER SYSTEM
statement (just as for a non-CDB).
Prerequisites
The current user must be granted the following privileges, which must be either commonly granted or locally granted in the PDB:
CREATE SESSION
ALTER SYSTEM
To use ALTER SYSTEM to modify a PDB:
- In SQL*Plus, ensure that the current container is a PDB.
- Run the
ALTER SYSTEM
statement.
Enable Restricted Sessions in a PDB
To restrict sessions in a PDB, issue the following statement:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
Changing the Statistics Gathering Level for the PDB
This ALTER SYSTEM
statement sets the STATISTICS_LEVEL
initialization parameter to ALL
for the current PDB:
ALTER SYSTEM SET STATISTICS_LEVEL = ALL SCOPE = MEMORY;
Modifying a PDB at the Database Level
The ALTER PLUGGABLE DATABASE
for a PDB is analogous to the ALTER DATABASE
for a non-CDB.
Storage Clauses
Use ALTER PLUGGABLE DATABASE
to configure storage at the PDB level.
The following clauses of ALTER PLUGGABLE DATABASE
modify PDB storage:
- database_file_clauses
These clauses work the same as they would in an ALTER DATABASE
statement, but the statement applies to the current PDB.
DEFAULT TABLESPACE
clause
For users created while the current container is a PDB, this clause specifies the default tablespace for the user if the default tablespace is not specified in the CREATE USER
statement.
DEFAULT TEMPORARY TABLESPACE
clause
For users created while the current container is a PDB, this clause specifies the default temporary tablespace for the user if the default temporary tablespace is not specified in the CREATE USER
statement.
SET DEFAULT { BIGFILE | SMALLFILE } TABLESPACE
clause
This clause changes the default type of subsequently created tablespaces in the PDB to either bigfile or smallfile. This clause works the same as it would in an ALTER DATABASE
statement, but it applies to the current PDB.
- pdb_storage_clause
This clause sets a limit on the amount of storage used by all tablespaces that belong to a PDB. This limit applies to the total size of all data files and temp files comprising tablespaces that belong to the PDB.
This clause can also set a limit on the amount of storage that can be used by unified audit OS spillover (.bin format) files in the PDB. If the limit is reached, then no additional storage is available for these files.
This clause can also set a limit on the amount of storage in a shared temporary tablespace that can be used by sessions connected to the PDB. If the limit is reached, then no additional storage in the shared temporary tablespace is available to sessions connected to the PDB.
Logging and Recovery Clauses
Use ALTER PLUGGABLE DATABASE
to set logging and recovery and recovery modes at the PDB level.
logging_clause
This clause specifies the logging attribute of the PDB. The logging attribute controls whether certain DML operations are logged in the redo log file (LOGGING
) or not (NOLOGGING
).
You can use this clause to specify one of the following attributes:
LOGGING
indicates that any future tablespaces created within the PDB will be created with theLOGGING
attribute by default. You can override this default logging attribute by specifyingNOLOGGING
at the schema object level, in aCREATE TABLE
statement for example.NOLOGGING
indicates that any future tablespaces created within the PDB will be created with theNOLOGGING
attribute by default. You can override this default logging attribute by specifyingLOGGING
at the schema object level, in aCREATE TABLE
statement for example.
The specified attribute is used to establish the logging attribute of tablespaces created within the PDB if the logging_clause is not specified in the CREATE TABLESPACE
statement.
The DBA_PDBS
view shows the current logging attribute for a PDB.
pdb_force_logging_clause
This clause places a PDB into force logging or force nologging mode or takes a PDB out of force logging or force nologging mode.
You can use this clause to specify one of the following attributes:
ENABLE FORCE LOGGING
places the PDB in force logging mode, which causes all changes in the PDB, except changes in temporary tablespaces and temporary segments, to be logged. Force logging mode cannot be overridden at the schema object level.
PDB-level force logging mode takes precedence over and is independent of any NOLOGGING
or FORCE LOGGING
settings you specify for individual tablespaces in the PDB and any NOLOGGING
settings you specify for individual database objects in the PDB.
ENABLE FORCE LOGGING
cannot be specified if a PDB is in force nologging mode. DISABLE FORCE NOLOGGING
must be specified first.
DISABLE FORCE LOGGING
takes a PDB which is currently in force logging mode out of that mode. If the PDB is not in force logging mode currently, then specifyingDISABLE FORCE LOGGING
results in an error.ENABLE FORCE NOLOGGING
places the PDB in force nologging mode, which causes no changes in the PDB to be logged. Force nologging mode cannot be overridden at the schema object level.
CDB-wide force logging mode supersedes PDB-level force nologging mode. PDB-level force nologging mode takes precedence over and is independent of any LOGGING
or FORCE LOGGING
settings you specify for individual tablespaces in the PDB and any LOGGING
settings you specify for individual database objects in the PDB.
ENABLE FORCE NOLOGGING
cannot be specified if a PDB is in force logging mode. DISABLE FORCE LOGGING
must be specified first.
DISABLE FORCE NOLOGGING
takes a PDB that is currently in force nologging mode out of that mode. If the PDB is not in force nologging mode currently, then specifyingDISABLE FORCE NOLOGGING
results in an error.
The DBA_PDBS
view shows whether a PDB is in force logging or force nologging mode.
pdb_recovery_clause
ALTER PLUGGABLE DATABASE DISABLE RECOVERY
takes the data files that belong to the PDB offline and disables recovery of the PDB. The PDB data files are not part of any recovery session until it is enabled again. Any new data files created while recovery is disabled are created as unnamed files for the PDB.
ALTER PLUGGABLE DATABASE ENABLE RECOVERY
brings the data files that belong to the PDB online and marks the PDB for active recovery. Recovery sessions include these files.
Check the recovery status of a PDB by querying the RECOVERY_STATUS
column in the V$PDBS
view.
Miscellaneous Clauses
You can use ALTER PLUGGABLE DATABASE
to modify the open mode, global name, time zone, and default edition.
When the current container is a PDB, an ALTER PLUGGABLE DATABASE
statement with any of the following clauses modifies the PDB:
- pdb_change_state_clause
This clause changes the open mode of the current PDB.
If you specify the optional RESTRICTED
keyword, then the PDB is accessible only to users with the RESTRICTED SESSION
privilege in the PDB.
Specifying FORCE
in this clause changes semantics of the ALTER PLUGGABLE DATABASE
statement so that, in addition to opening a PDB that is currently closed, it can be used to change the open mode of a PDB that is already open.
RENAME GLOBAL_NAME
clause
This clause changes the unique global database name for the PDB. The new global database name must be different from that of any container in the CDB. When you change the global database name of a PDB, the PDB name is changed to the name before the first period in the global database name.
You must change the PDB
property of database services used to connect to the PDB when you change the global database name.
- set_time_zone_clause
This clause works the same as it would in an ALTER DATABASE
statement, but it applies to the current PDB.
DEFAULT EDITION
clause
This clause works the same as it would in an ALTER DATABASE
statement, but it applies to the current PDB. Each PDB can use edition-based redefinition, and editions in one PDB do not affect editions in other PDBs. In a multitenant environment in which each PDB has its own application, you can use edition-based redefinition independently for each distinct application.
Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement
To modify the attributes of a single PDB, use the ALTER PLUGGABLE DATABASE
statement.
When the current container is a PDB, an ALTER PLUGGABLE DATABASE
statement modifies the PDB. The modifications overwrite the defaults set for the root in the PDB. The modifications do not affect the CDB root or other PDBs.
To modify a PDB:
- In SQL*Plus, ensure that the current container is a PDB.
- Run an
ALTER PLUGGABLE DATABASE
statement.
Changing the Open Mode of a PDB
This ALTER PLUGGABLE DATABASE
statement changes the open mode of the current PDB to mounted.
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
The following statement changes the open mode of the current PDB to open read-only.
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
A PDB must be in mounted mode to change its open mode to read-only or read/write unless you specify the FORCE
keyword.
The following statement changes the open mode of the current PDB from mounted or open read-only to open read/write.
ALTER PLUGGABLE DATABASE OPEN FORCE;
The following statement changes the open mode of the current PDB from mounted to migrate.
ALTER PLUGGABLE DATABASE OPEN UPGRADE;
Bringing a Data File Online for a PDB
This ALTER PLUGGABLE DATABASE
statement uses a database_file_clause to bring the /u03/oracle/pdb1_01.dbf
data file online.
ALTER PLUGGABLE DATABASE DATAFILE '/u03/oracle/pdb1_01.dbf' ONLINE;
Changing the Default Tablespaces for a PDB
This ALTER PLUGGABLE DATABASE
statement uses a DEFAULT TABLESPACE
clause to set the default permanent tablespace to pdb1_tbs
for the PDB.
ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;
This ALTER PLUGGABLE DATABASE
statement uses a DEFAULT TEMPORARY TABLESPACE
clause to set the default temporary tablespace to pdb1_temp
for the PDB.
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb1_temp;
The tablespace or tablespace group specified in the ALTER PLUGGABLE DATABASE
statement must exist in the PDB. Users whose current container is a PDB that are not explicitly assigned a default tablespace or default temporary tablespace use the default tablespace or default temporary tablespace for the PDB.
Changing the Default Tablespace Type for a PDB
This ALTER DATABASE
statement uses a SET DEFAULT TABLESPACE
clause to change the default tablespace type to bigfile for the PDB.
ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;
Setting Storage Limits for a PDB
This statement sets the storage limit for all tablespaces that belong to a PDB to two gigabytes.
ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE 2G);
This statement specifies that there is no storage limit for the tablespaces that belong to the PDB.
ALTER PLUGGABLE DATABASE STORAGE(MAXSIZE UNLIMITED);
This statement specifies that there is no storage limit for the tablespaces that belong to the PDB and that there is no storage limit for the shared temporary tablespace that can be used by sessions connected to the PDB.
ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;
Setting the Logging Attribute of a PDB
With the PDB open in restricted mode, this statement specifies the NOLOGGING
attribute for the PDB:
ALTER PLUGGABLE DATABASE NOLOGGING;
Setting the Force Logging Mode of a PDB
This statement enables force logging mode for the PDB:
ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;
Setting the Default Edition for a PDB
This example sets the default edition for the current PDB to PDB1E3
.
ALTER PLUGGABLE DATABASE DEFAULT EDITION = PDB1E3;
Changing the Global Database Name of a PDB
You can change the global database name of a PDB with the ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO
statement.
When you change the global database name of a PDB, the new global database name must be different from that of any container in the CDB.
To change the global database name of a PDB:
- In SQL*Plus, ensure that the current container is a PDB.
- Run an
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO
statement.
The following example changes the global database name of the PDB to salespdb.example.com
:
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO salespdb.example.com;
- Close the PDB.
- Open the PDB in read/write mode.
When you change the global database name of a PDB, the PDB name is changed to the first part of the new global name, which is the part before the first period. Also, Oracle Database changes the name of the default database service for the PDB automatically. Oracle Database also changes the PDB
property of all database services in the PDB to the new global name of the PDB. You must close the PDB and open it in read/write mode for Oracle Database to complete the integration of the new PDB service name into the CDB.
Oracle Net Services must be configured properly for clients to access database services. You might need to alter your Oracle Net Services configuration because of the PDB name change.
Managing Refreshable Clone PDBs
A refreshable clone PDB is a read-only clone that can periodically synchronize with its source PDB.
Refreshing a PDB
You can refresh a PDB that was created as a refreshable clone.
When you refresh a PDB manually, changes made to the source PDB since the last refresh are propagated to the PDB being refreshed. You can manually refresh a PDB that is configured for automatic refresh.
Switching Over a Refreshable Clone PDB
You can switch the roles of a source PDB and its refreshable clone PDB.
The following statement performs a switchover:
ALTER PLUGGABLE DATABASE
refresh_modeFROM
[email protected]SWITCHOVER;
You must not specify REFRESH MODE NONE
for refresh_mode. The database link specified in the FROM
clause must point to the root of the CDB in which the clone PDB resides.
After the switchover completes, the source PDB becomes the refreshable clone PDB, which can only be opened in READ ONLY
mode.
To switch the roles of the source and clone PDBs:
- In SQL*Plus or SQL Developer, log in to the source PDB.
- Execute the
ALTER PLUGGABLE DATABASE
refresh_modeFROM
[email protected]SWITCHOVER
statement.
After the statement completes, the currently connected PDB is now the refreshable clone PDB.
- Optionally, refresh the clone PDB:
ALTER PLUGGABLE DATABASE REFRESH;
Switching Over a Refreshable Clone PDB
This example assumes that your data center contains CDBs named cdb1
and cdb2
. The PDB named cdb1_pdb1
resides in cdb1
. You want to create a refreshable clone of this PDB in cdb2
and name it cdb1_pdb1_ref
. Your goal is to switch over cdb1_pdb1_ref
so that it becomes the source PDB and cdb1_pdb1
becomes the clone PDB.
- In SQL*Plus, connect to
cdb1
as a user with administrator privileges, and then ensure sure thatcdb1_pdb1
is open in read/write mode (sample output included):
CONNECT [email protected] AS SYSDBA
Enter password: *******
ALTER PLUGGABLE DATABASE ALL CLOSE;
ALTER PLUGGABLE DATABASE cdb1_pdb1 OPEN READ WRITE;
SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 CDB1_PDB1 READ WRITE NO
- Create a common user named
c##u1
(replace pwd with a user-specified password):
DROP USER c##u1 CASCADE;
CREATE USER c##u1 IDENTIFIED BY
pwd;
GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;
- Set the container to
cdb1_pdb1
, and then create a tablet1
to use for testing (sample output included):
ALTER SESSION SET CONTAINER = cdb1_pdb1;
CREATE TABLE t1(n1 NUMBER);
INSERT INTO t1 VALUES(1);
COMMIT;
SELECT * FROM t1;
N1
----------
1
- Connect to
cdb2
as a user with administrator privileges, and then create the common user namedc##u1
(replace pwd with a user-specified password):
CONNECT [email protected] AS SYSDBA
Enter password: *******
DROP USER c##u1 CASCADE;
CREATE USER c##u1 IDENTIFIED BY
pwd;
GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;
Now cdb1
and cdb2
both have a common user with the same name (c##u1
) and password.
- Create a database link to
cdb1
.
The following command specifies user c##u1
, password pwd, and service name cdb1
:
CREATE DATABASE LINK cdb1_datalink CONNECT TO c##u1 IDENTIFIED BY
pwdUSING 'cdb1';
- Create the manually refreshable PDB named
cdb1_pdb1_ref
.
The following statement specifies the database link cdb1_datalink
and the file destination /dsk1/df
:
CREATE PLUGGABLE DATABASE cdb1_pdb1_ref FROM [email protected]_datalink
CREATE_FILE_DEST='/dsk1/df'
REFRESH MODE MANUAL;
- Refresh
cdb1_pdb1_ref
:
ALTER SESSION SET CONTAINER = cdb1_pdb1_ref;
ALTER PLUGGABLE DATABASE REFRESH;
- Query
t1
to check that the refreshable clone PDB contains the correct contents (sample output included):
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
SELECT * FROM t1;
N1
----------
1
- Connect to
cdb1
as a user with administrator privileges, and then create a database link tocdb2
:
CONNECT [email protected] AS SYSDBA
Enter password: *******
CREATE DATABASE LINK cdb2_datalink CONNECT TO c##u1 IDENTIFIED BY
pwdUSING 'cdb2';
The preceding statement specifies user c##u1
, password pwd, and service name cdb2
.
- Set the container to
cdb1_pdb1
, and then switch over so thatcdb1_pdb1_ref
is the primary PDB and the current PDB is the clone:
ALTER SESSION SET CONTAINER = cdb1_pdb1;
ALTER PLUGGABLE DATABASE
REFRESH MODE MANUAL
FROM [email protected]_datalink
SWITCHOVER;
- Query
t1
to check that the current PDB, which is now the refreshable clone PDB, contains the correct contents (sample output included):
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
SELECT * FROM t1;
N1
----------
1
- Connect to
cdb2
as a user with administrator privileges, set the container to the new source PDBcdb1_pdb1_ref
, and then insert a new row into tablet1
(sample output included):
CONNECT [email protected] AS SYSDBA
Enter password: *******
ALTER SESSION SET CONTAINER = cdb1_pdb1_ref;
SELECT * FROM t1;
N1
----------
1
INSERT INTO t1 VALUES(2);
COMMIT;
SELECT * FROM t1;
N1
----------
1
2
- Connect to
cdb1
as a user with administrator privileges, set the container tocdb1_pdb1
(which is the new clone), refresh it, and then queryt1
:
CONNECT [email protected] AS SYSDBA
Enter password: *******
ALTER SESSION SET CONTAINER = cdb1_pdb1;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE REFRESH;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
SELECT * FROM t1;
N1
----------
1
2
The preceding output shows that the clone cdb1_pdb1
was refreshed from the source cdb1_pdb1_ref
.
Modifying the Open Mode of PDBs
You can modify the open mode of a PDB by using the ALTER PLUGGABLE DATABASE
SQL statement or the SQL*Plus STARTUP
command.
When a PDB is mounted, you can open it in read/write, read-only, or MIGRATE
mode. You can also mount a PDB without opening it.
Open Modes of a PDB
You can view the open mode of a PDB by querying the OPEN_MODE
column of the V$PDBS
view.
The following table describes the possible open modes.
Mount and Open Modes
Mode | Description | Notes |
OPEN READ WRITE | A PDB in open read/write mode allows queries and user transactions to proceed and allows users to generate redo logs. This is the default open mode except when a PDB belongs to a physical standby database. | If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB. If you also specify FORCE , then all sessions connected to the PDB that do not have the RESTRICTED SESSION privilege in the PDB are terminated, and their transactions are rolled back. |
OPEN READ ONLY | A PDB in open read-only mode allows queries but does not allow user changes. This is the default open mode when a PDB belongs to a physical standby database. | Database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB. If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB. If you also specify FORCE , then all sessions connected to the PDB that do not have the RESTRICTED SESSION privilege in the PDB are terminated, and their transactions are rolled back. |
OPEN MIGRATE | When a PDB is in open migrate mode, you can run database upgrade scripts on the PDB. A PDB is in this mode after you run an ALTER DATABASE OPEN UPGRADE statement. | If you specify the optional RESTRICTED keyword, then the PDB is accessible only to users with the RESTRICTED SESSION privilege in the PDB. |
MOUNTED | When a PDB is mounted, it does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Consistent backups of the PDB are supported. | Database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB. |
Automatic Compatibility Check
When a PDB is opened, Oracle Database checks the compatibility of the PDB with the CDB. Each compatibility violation is either of the following:
- Warning
The database records the warning in the alert log, and then opens the PDB normally without displaying a warning message.
- Error
The database displays a message when the PDB is opened stating that the PDB was altered with errors, and records the errors in the alert log. You must correct the condition that caused each error. When there are errors, the PDB is opened, but access to the PDB is limited to users with RESTRICTED SESSION
privilege so that the compatibility violations can be addressed. You can view descriptions of violations by querying PDB_PLUG_IN_VIOLATIONS
view.
Clauses for Changing the Open State of PDBs
To change the open mode of a PDB when the current container is the CDB root, specify the pdb_change_state clause of ALTER PLUGGABLE DATABASE
.
OPEN and CLOSE Clauses
READ WRITE
is the default for ALTER PLUGGABLE DATABASE OPEN
unless a PDB being opened belongs to a CDB used as a physical standby database, in which case READ ONLY
is the default.
When you specify PDBs to open or close, you can do the following:
- List one or more PDBs.
- Specify
ALL
to modify all PDBs. - Specify
ALL EXCEPT
to modify all PDBs, except for the PDBs listed.
The following table describes the clauses of the ALTER PLUGGABLE DATABASE
statement that modify the mode of a PDB.
ALTER PLUGGABLE DATABASE Clauses That Modify the Mode of a PDB
Clause | Description |
OPEN READ WRITE [RESTRICTED] [FORCE] | Opens the PDB in read/write mode. When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB. All sessions connected to the PDB that do not have RESTRICTED SESSION privilege on it are terminated, and their transactions are rolled back. When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode of a PDB that is in open read-only mode. |
OPEN READ ONLY[RESTRICTED] [FORCE] | Opens the PDB in read-only mode. When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB. All sessions connected to the PDB that do not have RESTRICTED SESSION privilege on it are terminated. When FORCE is specified, the statement opens a PDB that is currently closed and changes the open mode of a PDB that is in open read/write mode. |
OPEN UPGRADE [RESTRICTED] | Opens the PDB in migrate mode. When RESTRICTED is specified, the PDB is accessible only to users with RESTRICTED SESSION privilege in the PDB. |
CLOSE [IMMEDIATE|ABORT] | Places the PDB in mounted mode. The CLOSE statement is the PDB equivalent of the SQL*Plus SHUTDOWN command. If you do not specify IMMEDIATE or ABORT , then the PDB is shut down with the normal mode. When IMMEDIATE is specified, this statement is the PDB equivalent of the SQL*Plus SHUTDOWN IMMEDIATE command. If the CDB is in ARCHIVELOG mode, and if ABORT is specified, then the PDB is forcefully closed. The PDB data files are not checkpointed or accessed during this process. If other instances have the PDB open, then an available instance performs instance recovery automatically. During this time, access to the PDB on other instances may observe a brown-out time. If no instance has the PDB open, then the next PDB open may cause automatic media recovery. If automatic media recovery fails (for example, because of inaccessible files), then you must manually recover the PDB before opening it. If the PDB keystore was in an open state, then ALTER PLUGGABLE DATABASE CLOSE does not close it. To close the keystore, run the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY " pdb_ks_pwd" command. |
SERVICES Clause
You can use the services clause to specify the services that are started when a single PDB is opened.
The clause has the following variations:
- List one or more services in the services clause in the following form:
SERVICES = ('
service_name' [,'
service_name'] … )
- Specify
ALL
in the services clause to start all PDB’s services, as in the following example:
SERVICES = ALL
- Specify
ALL EXCEPT
in the services clause to start all PDB’s services, except for the services listed, in the following form:
SERVICES = ALL EXCEPT('
service_name' [,'
service_name'] … )
- Specify
NONE
in the services clause to start only the PDB’s default service and none of the other PDB’s services, as in the following example:
SERVICES = NONE
NONE
is the default setting for the services clause. A PDB’s default service is always started, regardless of the setting for the services clause.
INSTANCES Clause
In an Oracle RAC CDB, you can use the instances clause to specify the instances on which the PDB is modified.
You can close a PDB in some instances and leave it open in others. The instances clause has the following variations:
- List one or more instances in the instances clause in the following form:
INSTANCES = ('
instance_name' [,'
instance_name'] … )
- Specify
ALL
in the instances clause to modify the PDB in all running instances, as in the following example:
INSTANCES = ALL
- Specify
ALL EXCEPT
in the instances clause to modify the PDB in all instances, except for the instances listed, in the following form:
INSTANCES = ALL EXCEPT('
instance_name' [,'
instance_name'] … )
The RELOCATE Clause
In an Oracle Real Application Clusters environment, use RELOCATE
to instruct the database to reopen the PDB on a different Oracle RAC instance.
You can use the following options:
- Specify
NORELOCATE
, the default, to close the PDB in the current instance. - Specify
RELOCATE TO
and specify an instance name to reopen the PDB in the specified instance. - Specify
RELOCATE
to reopen the PDB on a different instance that is selected by Oracle Database.
Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
You can modify the open mode of PDBs with the ALTER PLUGGABLE DATABASE
statement with a pdb_change_state clause.
Prerequisites
To change the open mode of PDBs with the ALTER PLUGGABLE DATABASE
statement, you must meet the following prerequisites:
- The current user must have one of the following administrative privileges, which must be either commonly granted or locally granted in the PDB:
SYSDBA
, exercised usingAS SYSDBA
at connect time
SYSOPER
, exercised usingAS SYSOPER
at connect time
SYSBACKUP
, exercised usingSYSBACKUP
at connect time
SYSDG
, exercised usingAS SYSDG
at connect time
- When
RESTRICTED SESSION
is enabled, you must specifyRESTRICTED
when a PDB is opened. - In an Oracle RAC CDB, if a PDB is open in one or more Oracle RAC instances, then it can be opened in additional instances. However, the PDB must be opened in the same mode as in the instances in which it is already open. A PDB can be closed in some instances and opened on others.
To place PDBs in a target mode with the ALTER PLUGGABLE DATABASE
statement, you must meet the requirements described in the following table.
Modifying the Open Mode of PDBs with ALTER PLUGGABLE DATABASE
Target Mode of PDBs | ALL Keyword Included | FORCE Keyword Included | Required Mode for the Root | Required Mode for Each PDB Being Modified |
Read/write | Yes | Yes | Read/write | Mounted, read-only, or read/write |
Read/write | Yes | No | Read/write | Mounted or read/write |
Read/write | No | Yes | Read/write | Mounted, read-only, or read/write |
Read/write | No | No | Read/write | Mounted |
Read-only | Yes | Yes | Read-only or read/write | Mounted, read-only, or read/write |
Read-only | Yes | No | Read-only or read/write | Mounted or read-only |
Read-only | No | Yes | Read-only or read/write | Mounted, read-only, or read/write |
Read-only | No | No | Read-only or read/write | Mounted |
Migrate | Yes | Not applicable | Read-only or read/write | Mounted |
Migrate | No | Not applicable | Read-only or read/write | Mounted |
Mounted | Yes | Not applicable | Read-only or read/write | Mounted, read-only, migrate, or read/write |
Mounted | No | Not applicable | Read-only or read/write | Read-only, migrate, or read/write |
To modify the open mode:
- In SQL*Plus, ensure that the current container is the root.
- Run an
ALTER PLUGGABLE DATABASE
statement with a pdb_change_state clause.
Changing the Open Mode of Listed PDBs
This statement changes the open mode of PDBs salespdb
and hrpdb
to open in read/write mode.
ALTER PLUGGABLE DATABASE salespdb, hrpdb
OPEN READ WRITE;
This statement changes the open mode of PDB salespdb
to open in read-only mode. RESTRICTED
specifies that the PDB is accessible only to users with RESTRICTED
SESSION
privilege in the PDB.
ALTER PLUGGABLE DATABASE salespdb
OPEN READ ONLY RESTRICTED;
This statement changes the open mode of PDB salespdb
to open in migrate mode:
ALTER PLUGGABLE DATABASE salespdb
OPEN UPGRADE;
Changing the Open Mode of All PDBs
Run the following query to display the open mode of each PDB associated with a CDB:
SELECT NAME, OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;
NAME OPEN_MODE
------------------------------ ----------
HRPDB READ WRITE
SALESPDB MOUNTED
DWPDB MOUNTED
Notice that hrpdb
is already in read/write mode. To change the open mode of salespdb
and dwpdb
to open in read/write mode, use the following statement:
ALTER PLUGGABLE DATABASE ALL
OPEN READ WRITE;
The hrpdb
PDB is not modified because it is already in open read/write mode. The statement does not return an error because two PDBs are in mounted mode and one PDB (hrpdb
) is in the specified mode (read/write). Similarly, the statement does not return an error if all PDBs are in mounted mode.
However, if any PDB is in read-only mode, then the statement returns an error. To avoid an error and open all PDBs in the CDB in read/write mode, specify the FORCE
keyword:
ALTER PLUGGABLE DATABASE ALL
OPEN READ WRITE FORCE;
With the FORCE
keyword included, all PDBs are opened in read/write mode, including PDBs in read-only mode.
Changing the Open Mode of All PDBs Except for Listed Ones
This statement changes the mode of all PDBs except for salespdb
and hrpdb
to mounted mode.
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb
CLOSE IMMEDIATE;
Preserving or Discarding the Open Mode of PDBs When the CDB Restarts
You can preserve the open mode of one or more PDBs when the CDB restarts by using the ALTER PLUGGABLE DATABASE
SQL statement with a pdb_save_or_discard_state clause.
You can specify which PDBs to modify in the following ways:
- List one or more PDBs.
- Specify
ALL
to modify all PDBs. - Specify
ALL EXCEPT
to modify all PDBs, except for the PDBs listed.
For an Oracle RAC CDB, you can use the instances clause in the pdb_save_or_discard_state clause to specify the instances on which a PDB’s open mode is preserved in the following ways:
- List one or more instances in the instances clause in the following form:
INSTANCES = ('
instance_name' [,'
instance_name'] … )
- Specify
ALL
in the instances clause to modify the PDB in all running instances, as in the following example:
INSTANCES = ALL
- Specify
ALL EXCEPT
in the instances clause to modify the PDB in all instances, except for the instances listed, in the following form:
INSTANCES = ALL EXCEPT('
instance_name' [,'
instance_name'] … )
For a PDB in an Oracle RAC CDB, SAVE STATE
and DISCARD STATE
only affect the mode of the current instance. They do not affect the mode of other instances, even if more than one instance is specified in the instances clause.
To issue an ALTER PLUGGABLE DATABASE
SQL statement with a pdb_save_or_discard_state clause, the current user must have the ALTER DATABASE
privilege in the root.
You can check the saved states for the PDBs in a CDB by querying the DBA_PDB_SAVED_STATES
view.
To preserve or discard a PDB’s open mode when the CDB restarts:
- In SQL*Plus, ensure that the current container is the root.
- Run an
ALTER PLUGGABLE DATABASE
statement with a pdb_save_or_discard_state clause.
The following examples either preserve or discard the open mode of one or more PDBs when the CDB restarts.
Preserving the Open Mode of a PDB When the CDB Restarts
This statement preserves the open mode of the salespdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb SAVE STATE;
Discarding the Open Mode of a PDB When the CDB Restarts
This statement discards the open mode of the salespdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb DISCARD STATE;
Preserving the Open Mode of All PDBs When the CDB Restarts
This statement preserves the open mode of all PDBs when the CDB restarts.
ALTER PLUGGABLE DATABASE ALL SAVE STATE;
Preserving the Open Mode of Listed PDBs When the CDB Restarts
This statement preserves the open mode of the salespdb
and hrpdb
when the CDB restarts.
ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;
Preserving the Open Mode of All PDBs Except for Listed Ones When the CDB Restarts
This statement preserves the open mode of all PDBs except for salespdb
and hrpdb
.
ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;
Altering the Open Mode of a PDB Using STARTUP and SHUTDOWN
When the current container is the root, the STARTUP PLUGGABLE DATABASE
command can open a single PDB.
Use the following options of the STARTUP PLUGGABLE DATABASE
command to open a PDB:
FORCE
Closes an open PDB before re-opening it in read/write mode. When this option is specified, no other options are allowed.
RESTRICT
Enables only users with the RESTRICTED SESSION
system privilege in the PDB to access the PDB.
If neither OPEN READ WRITE
nor OPEN READ ONLY
is specified, then the PDB is opened in read-only mode when the CDB to which it belongs is a physical standby database. Otherwise, the PDB is opened in read/write mode.
OPEN
open_pdb_options
Opens the PDB in either read/write mode or read-only mode. You can specify OPEN READ WRITE
or OPEN READ ONLY
. When you specify OPEN
without any other options, READ WRITE
is the default.
In addition, to place PDBs in a target mode with the STARTUP PLUGGABLE DATABASE
command, you must meet the requirements described in the following table.
Modifying the Open Mode of a PDB with STARTUP PLUGGABLE DATABASE
Target Mode of the PDB | FORCE Option Included | Required Mode for the Root | Required Mode of the PDB Being Modified |
Read/write | Yes | Read/write | Mounted, read-only, or read/write |
Read/write | No | Read/write | Mounted |
Read-only | No | Read-only or read/write | Mounted |
Starting Up a PDB Using the STARTUP Command
When the current container is a PDB, the SQL*Plus STARTUP
command opens the PDB.
Use the following options of the STARTUP
command to open a PDB:
FORCE
Closes an open PDB before re-opening it in read/write mode. When this option is specified, no other options are allowed.
RESTRICT
Enables only users with the RESTRICTED
SESSION
system privilege in the PDB to access the PDB.
If neither OPEN READ WRITE
nor OPEN READ ONLY
is specified and RESTRICT
is specified, then the PDB is opened in read-only mode when the CDB to which it belongs is a physical standby database. Otherwise, the PDB is opened in read/write mode.
OPEN
open_pdb_options
Opens the PDB in either read/write mode or read-only mode. Specify OPEN READ WRITE
or OPEN READ ONLY
. When RESTRICT
is not specified, READ WRITE
is always the default.
To modify a PDB with the STARTUP command:
- In SQL*Plus, ensure that the current container is a PDB.
- Run the
STARTUP
command.
Opening a PDB in Read/Write Mode with the STARTUP Command
STARTUP OPEN
Opening a PDB in Read-Only Mode with the STARTUP Command
STARTUP OPEN READ ONLY
Opening a PDB in Read-Only Restricted Mode with the STARTUP Command
STARTUP RESTRICT OPEN READ ONLY
Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option
This example assumes that the PDB is currently open. The FORCE
option closes the PDB and then opens it in the read/write mode.
STARTUP FORCE
Modifying the Open Mode of PDBs with the SQL*Plus STARTUP Command
You can use the STARTUP PLUGGABLE DATABASE
command to open a single PDB.
To modify a PDB with the STARTUP PLUGGABLE DATABASE command:
- In SQL*Plus, ensure that the current container is the root.
- Run the
STARTUP PLUGGABLE DATABASE
command.
Opening a PDB in Read/Write Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN
Opening a PDB in Read/Write Restricted Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb RESTRICT
Opening a PDB in Read-Only Restricted Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT
Opening a PDB in Read-Only Mode with the STARTUP Command
STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY
Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option
This example assumes that the hrpdb
PDB is currently open. The FORCE
option closes the PDB and then opens it in the read/write mode.
STARTUP PLUGGABLE DATABASE hrpdb FORCE
Shutting Down a PDB Using the SHUTDOWN Command
When the current container is a PDB, the SQL*Plus SHUTDOWN
command closes the PDB.
After the SHUTDOWN
command is issued on a PDB successfully, it is in mounted mode.
The following SHUTDOWN
modes are possible:
- When you specify
SHUTDOWN
only, then the PDB is shut down with the normal mode. - When you specify
SHUTDOWN IMMEDIATE
, the PDB is shut down with the immediate mode. - When you specify
SHUTDOWN ABORT
, the PDB is forcefully closed.
For a single-instance CDB, PDB media recovery is required when you specify SHUTDOWN ABORT
. For an Oracle Real Application Clusters (Oracle RAC) CDB, PDB media recovery is required if the SHUTDOWN ABORT
command closes the last open instance.
To modify a PDB with the SHUTDOWN
command:
- In SQL*Plus, ensure that the current container is a PDB.
- Run the
SHUTDOWN
command.
Closing a PDB with the SHUTDOWN IMMEDIATE Command
SHUTDOWN IMMEDIATE
Starting and Stopping PDBs in Oracle RAC
Administering a pluggable database (PDB) involves a small subset of the tasks required to administer a non-CDB.
Administering an Oracle RAC-based multitenant container database (CDB) is similar to administering a non-CDB. The differences are that some administrative tasks apply to the entire CDB, some to the CDB root, and some to specific PDBs. In this subset of tasks, most are the same for a PDB and a non-CDB. There are some differences, however, such as when you modify the open mode of a PDB. Also, a PDB administrator is limited to managing a single PDB and is not affected by other PDBs in the CDB.
You manage PDBs in an Oracle RAC CDB by managing services. This is true regardless of whether the PDBs are policy managed or administrator managed. Assign one dynamic database service to each PDB to coordinate start, stop, and placement of PDBs across instances in a clustered container database.
For example, if you have a CDB called raccont
with a policy-managed PDB called spark
in a server pool called prod
, then assign a service called plug
to this database using the following command:
srvctl add service –db raccont –pdb spark –service plug –serverpool prod
The service plug
is uniformly managed across all nodes in the server pool. If you want to have this service running as a singleton service in the same server pool, then use the -cardinality singleton
parameter with the preceding command.
To open the PDB spark
, you must start the service plug
as follows:
srvctl start service -db raccont -service plug
To stop the service plug
:
srvctl stop service -db raccont -service plug
The PDB spark
remains open until you close the PDB using the SQL command ALTER PLUGGABLE DATABASE
PDB_NAME CLOSE IMMEDIATE
. You can check the status of the database using the srvctl status service
command.
Because PDBs are managed using dynamic database services, typical Oracle RAC-based management practices apply. For this reason, if the service plug
is in the online state when Oracle Clusterware is shut down on a server hosting this service, then the service is restored to its original state after the restart of Oracle Clusterware on this server. Thus, starting PDBs is automated as with any other Oracle RAC database.