1. Administrator’s Guide
  2. Administering a Multitenant Environment
  3. Administering PDBs

15 Administering PDBs

Administering PDBs includes tasks such as connecting to a PDB, modifying a PDB, and managing services associated with PDBs.

This chapter contains the following topics:

Related Topics

Parent topic: Administering a Multitenant Environment

15.1 About PDB Administration

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).

This section contains the following topics:

See Also:

"Modifying a PDB at the Database Level" for more information about changing the open mode of the current PDB

Parent topic: Administering PDBs

15.1.1 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.

Table 15-1 Administrative Tasks Common to PDBs and Non-CDBs

Task Description Additional Information

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.

"Modifying a PDB at the Database Level"

Oracle Database Administrator’s Guide for information about managing tablespaces

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.

"Modifying a PDB at the Database Level"

Oracle Database Administrator’s Guide for information about managing data files and temp files

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.

Oracle Database Administrator’s Guide for more information about schema objects

Oracle Database Administrator’s Guide

Oracle Database PL/SQL Language Reference for information about creating triggers in a CDB

Parent topic: About PDB Administration

15.1.2 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.

See Also:

Parent topic: About PDB Administration

15.2 Managing Connections to a PDB

You manage connections for a PDB in the same way as for a non-CDB, with some special considerations.

This section contains the following topics:

Parent topic: Administering PDBs

15.2.1 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

Prerequisites

The following prerequisites must be met:

  • The user connecting to the PDB must be granted the CREATE SESSION privilege in the PDB.

  • To connect to a PDB as a user that does not have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege, the PDB must be open.

Note:

This section assumes that the user connecting to the PDB using a local user account. You can also connect to the PDB as a common user, and you can connect to the root as a common user and switch to the PDB.

To connect to a PDB using the SQL*Plus CONNECT command:

  1. Configure your environment so that you can open SQL*Plus.

  2. Start SQL*Plus with the /NOLOG argument: 

    sqlplus /nolog

  3. 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.

Example 15-1 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 hr@hrapp

See Also:

Parent topic: Managing Connections to a PDB

15.2.2 Managing Services for PDBs

You can create, modify, or remove services for a PDB.

This section contains the following topics:

See Also:

Oracle Database Administrator’s Guide

Parent topic: Managing Connections to a PDB

15.2.2.1 About Services for PDBs

Each PDB has a default service, but you can create your own using SRVCTL or DBMS_SERVICE.

This section contains the following topics:

  • 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.
  • Default and User-Defined Services
    Creating a PDB creates a new default service for the PDB automatically.
  • Tools for Managing Services
    Oracle recommends using the SRVCTL utility to create and modify services. Alternatively, you can use the DBMS_SERVICE package.

Parent topic: Managing Services for PDBs

15.2.2.1.1 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.

15.2.2.1.2 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.

Note:

Do not associate a service with a proxy PDB.

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.

15.2.2.1.3 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_SESSION 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.

See Also:

Parent topic: About Services for PDBs

15.2.2.2 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:

  1. Log in to the host computer with the correct user account.

  2. Ensure that you run SRVCTL from the correct Oracle home.

  3. 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 the modify service command, and specify the PDB in the -pdb parameter.

    • To remove a service, run the remove service command.

To create or remove a service for a PDB using the DBMS_SERVICE package:

  1. In SQL*Plus, ensure that the current container is a PDB.

    See "Connecting to a PDB".

  2. Run the appropriate subprogram in the DBMS_SERVICE package.

Note:

If your database is being managed by Oracle Restart or Oracle Clusterware, then use the SRVCTL utility to manage services. Do not use the DBMS_SERVICE package.

Example 15-2 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

Example 15-3 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

Example 15-4 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

Example 15-5 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

Example 15-6 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.

Example 15-7 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;
/

See Also:

Parent topic: Managing Services for PDBs

15.2.3 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.

This section contains the following topics:

  • 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.
  • 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.

Related Topics

Parent topic: Managing Connections to a PDB

15.2.3.1 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.
  1. In SQL*Plus, ensure that the current container is the referenced PDB.
  2. Run an ALTER PLUGGABLE DATABASE CONTAINERS HOST statement and specify the new host name, or include the RESET keyword to return the host name to its default setting, which is the host name of the referenced PDB.
  3. Drop and re-create the proxy PDBs that reference the referenced PDB to reestablish communication for each proxy PDB and its referenced PDB.

Example 15-8 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';

Example 15-9 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;
15.2.3.2 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.
  1. In SQL*Plus, ensure that the current container is the referenced PDB.
  2. Run an ALTER PLUGGABLE DATABASE CONTAINERS PORT statement and specify the new port number, or include the RESET keyword to return the port number to its default setting, which is 1521.
  3. Drop and re-create the proxy PDBs that reference the referenced PDB to re-establish communication for each proxy PDB and its referenced PDB.

Example 15-10 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;

Example 15-11 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;

15.3 Modifying a PDB at the System Level

You can use the ALTER SYSTEM statement to modify a PDB.

This section contains the following topics:

Parent topic: Administering PDBs

15.3.1 About System-Level Modifications of a PDB

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.

Note:

A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.

See Also:

Parent topic: Modifying a PDB at the System Level

15.3.2 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:

  1. In SQL*Plus, ensure that the current container is a PDB.

    See "Connecting to a PDB".

  2. Run the ALTER SYSTEM statement.

Example 15-12 Enable Restricted Sessions in a PDB

To restrict sessions in a PDB, issue the following statement:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Example 15-13 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;

See Also:

Parent topic: Modifying a PDB at the System Level

15.4 Modifying a PDB at the Database Level

You can modify a PDB using the ALTER PLUGGABLE DATABASE statement.

This section contains the following topics:

Parent topic: Administering PDBs

15.4.1 About Database-Level Modifications of a PDB

The ALTER PLUGGABLE DATABASE for a PDB is analogous to the ALTER DATABASE for a non-CDB.

Note:

An ALTER DATABASE statement issued when the current container is a PDB that includes clauses that are supported for an ALTER PLUGGABLE DATABASE statement have the same effect as the corresponding ALTER PLUGGABLE DATABASE statement. However, these statements cannot include clauses that are specific to PDBs, such as the pdb_storage_clause, the pdb_change_state_clause, the logging_clause, and the pdb_recovery_clause.

This section contains the following topics:

  • Storage Clauses
    Use ALTER PLUGGABLE DATABASE to configure storage at the PDB level.
  • Logging and Recovery Clauses
    Use ALTER PLUGGABLE DATABASE to set logging and recovery and recovery modes at the PDB level.
  • Miscellaneous Clauses
    You can use ALTER PLUGGABLE DATABASE to modify the open mode, global name, time zone, and default edition.

Parent topic: Modifying a PDB at the Database Level

15.4.1.1 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.

15.4.1.2 Logging and Recovery Clauses

Use ALTER PLUGGABLE DATABASE to set logging and recovery and recovery modes at the PDB level.

logging_clause

Note:

This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).

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 the LOGGING attribute by default. You can override this default logging attribute by specifying NOLOGGING at the schema object level, in a CREATE TABLE statement for example.

  • NOLOGGING indicates that any future tablespaces created within the PDB will be created with the NOLOGGING attribute by default. You can override this default logging attribute by specifying LOGGING at the schema object level, in a CREATE 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.

Note:

The PDB must be open in restricted mode to use this clause.

pdb_force_logging_clause

Note:

This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).

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 specifying DISABLE 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 specifying DISABLE FORCE NOLOGGING results in an error.

The DBA_PDBS view shows whether a PDB is in force logging or force nologging mode.

Note:

The PDB must be open in restricted mode to use this clause.

pdb_recovery_clause

Note:

This clause is available starting with Oracle Database 12c Release 1 (12.1.0.2).

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.

See Also:

Parent topic: About Database-Level Modifications of a PDB

15.4.1.3 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.

15.4.2 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.

Prerequisites

The following prerequisites must be met:

  • To change the open mode of the PDB from mounted to opened or from opened to mounted, the current user must have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege. The privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS sys_privilege_name at connect time.

  • For all other operations performed using the ALTER PLUGGABLE DATABASE statement, the current user must have the ALTER DATABASE system privilege, and the privilege must be either commonly granted or locally granted in the PDB.

  • To close a PDB, the PDB must be open.

Note:

This section does not cover changing the global database name of a PDB using the ALTER PLUGGABLE DATABASE statement.

To modify a PDB:

  1. In SQL*Plus, ensure that the current container is a PDB.

  2. Run an ALTER PLUGGABLE DATABASE statement.

Example 15-14 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;

Example 15-15 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;

Example 15-16 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.

Example 15-17 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;

Example 15-18 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;

Example 15-19 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;

Example 15-20 Setting the Force Logging Mode of a PDB

This statement enables force logging mode for the PDB:

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

Example 15-21 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;

See Also:

Parent topic: Modifying a PDB at the Database Level

15.4.3 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.

Prerequisites

The following prerequisites must be met:

  • The current user must have the ALTER DATABASE system privilege, and the privilege must be either commonly granted or locally granted in the PDB.

  • For an Oracle Real Application Clusters (Oracle RAC) database, the PDB must be open on the current instance only. The PDB must be closed on all other instances.

  • The PDB being modified must be opened on the current instance in read/write mode with RESTRICTED specified so that it is accessible only to users with RESTRICTED SESSION privilege in the PDB.

To change the global database name of a PDB:

  1. In SQL*Plus, ensure that the current container is a PDB.

  2. 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;

  3. Close the PDB.

  4. 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.

See Also:

Parent topic: Modifying a PDB at the Database Level

15.4.4 Managing Refreshable Clone PDBs

A refreshable clone PDB is a read-only clone that can periodically synchronize with its source PDB.

This section contains the following topics:

Parent topic: Modifying a PDB at the Database Level

15.4.4.1 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.

Prerequisites

To refresh a PDB, the PDB must have been created as a clone with the REFRESH MODE MANUAL or REFRESH MODE EVERY minutes clause included.

  1. In SQL*Plus, ensure that the current container is the PDB you want to refresh.
  2. If the PDB is not closed, then close the PDB. For example, issue the following SQL statement:
    ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
  3. Issue the following SQL statement:
    ALTER PLUGGABLE DATABASE REFRESH;
15.4.4.2 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_mode FROM clonepdb@dblink 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.

Prerequisites

You must meet the following prerequisites:

  • You must be connected to the source PDB when you issue ALTER PLUGGABLE DATABASE ... SWITCHOVER.

  • If the source PDB and clone PDB are in separate CDBs, then the user specified in the database link must have the same name and password in the source PDB and clone PDB.

To switch the roles of the source and clone PDBs:

  1. In SQL*Plus or SQL Developer, log in to the source PDB.

  2. Execute the ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER statement.

    After the statement completes, the currently connected PDB is now the refreshable clone PDB.

  3. Optionally, refresh the clone PDB:

    ALTER PLUGGABLE DATABASE REFRESH;

Example 15-22 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.

  1. In SQL*Plus, connect to cdb1 as a user with administrator privileges, and then ensure sure that cdb1_pdb1 is open in read/write mode (sample output included): 

    CONNECT SYS@cdb1 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

  2. 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;

  3. Set the container to cdb1_pdb1, and then create a table t1 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

  4. Connect to cdb2 as a user with administrator privileges, and then create the common user named c##u1 (replace pwd with a user-specified password): 

    CONNECT SYS@cdb2 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.

  5. 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 pwd USING 'cdb1';

  6. 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 cdb1_pdb1@cdb1_datalink 
  CREATE_FILE_DEST='/dsk1/df' 
  REFRESH MODE MANUAL;

  7. Refresh cdb1_pdb1_ref: 

    ALTER SESSION SET CONTAINER = cdb1_pdb1_ref;
ALTER PLUGGABLE DATABASE REFRESH;

  8. 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

  9. Connect to cdb1 as a user with administrator privileges, and then create a database link to cdb2: 

    CONNECT SYS@cdb1 AS SYSDBA
Enter password: *******

CREATE DATABASE LINK cdb2_datalink CONNECT TO c##u1 IDENTIFIED BY pwd USING 'cdb2';

    The preceding statement specifies user c##u1, password pwd, and service name cdb2.

  10. Set the container to cdb1_pdb1, and then switch over so that cdb1_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 cdb1_pdb1_ref@cdb2_datalink 
  SWITCHOVER;

  11. 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

  12. Connect to cdb2 as a user with administrator privileges, set the container to the new source PDB cdb1_pdb1_ref, and then insert a new row into table t1 (sample output included): 

    CONNECT SYS@cdb2 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

  13. Connect to cdb1 as a user with administrator privileges, set the container to cdb1_pdb1 (which is the new clone), refresh it, and then query t1: 

    CONNECT SYS@cdb1 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.

15.5 Starting Up and Shutting Down a PDB

When the current container is a PDB, you can use the SQL*Plus STARTUP command to open the PDB and the SQL*Plus SHUTDOWN command to close the PDB.

This section contains the following topics:

Parent topic: Administering PDBs

15.5.1 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 issue the STARTUP command when the current container is a PDB, the following prerequisites must be met:

  • The current user must have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG, respectively, at connect time.

  • Excluding the use of the FORCE option, the PDB must be in mounted mode to open it.

  • To place a PDB in mounted mode, the PDB must be in open read-only or open read/write mode.

To modify a PDB with the STARTUP command:

  1. In SQL*Plus, ensure that the current container is a PDB.

  2. Run the STARTUP command.

Example 15-23 Opening a PDB in Read/Write Mode with the STARTUP Command

STARTUP OPEN

Example 15-24 Opening a PDB in Read-Only Mode with the STARTUP Command

STARTUP OPEN READ ONLY

Example 15-25 Opening a PDB in Read-Only Restricted Mode with the STARTUP Command

STARTUP RESTRICT OPEN READ ONLY

Example 15-26 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

See Also:

Parent topic: Starting Up and Shutting Down a PDB

15.5.2 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.

Prerequisites

To issue the SHUTDOWN command when the current container is a PDB, the following prerequisites must be met:

  • The current user must have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG, respectively, at connect time.

  • To close a PDB, the PDB must be open.

To modify a PDB with the SHUTDOWN command:

  1. In SQL*Plus, ensure that the current container is a PDB.

  2. Run the SHUTDOWN command.

Note:

  • When the current container is a PDB, the SHUTDOWN command only closes the PDB, not the CDB instance.

  • There is no SHUTDOWN command for a PDB that is equivalent to SHUTDOWN TRANSACTIONAL for a non-CDB.

Example 15-27 Closing a PDB with the SHUTDOWN IMMEDIATE Command

SHUTDOWN IMMEDIATE

See Also:

Parent topic: Starting Up and Shutting Down a PDB