8 Relocating a PDB

You can move a PDB to a different CDB or application container.

This chapter contains the following topics:

8.1 About PDB Relocation

During relocation, the source PDB can be open in read/write mode and fully functional.

When you open the relocated PDB for the first time, Oracle Database drains active sessions on the source PDB and redirects client connections to the relocated PDB services. Opening the relocated PDB initiates the shutdown of the original source PDB. The source and relocated PDBs are never open at the same time.

The following graphic shows the relocation of a common PDB (that is, not an application PDB) to a new CDB. The source PDB is plugged in to the CDB root, and the target PDB is plugged in to the CDB root. Note that the CREATE PLUGGABLE DATABASE ... RELOCATE statement copies the data blocks, undo blocks, and redo blocks to the new location. A database link is required.

Figure 8-1 Relocate a PDB into the Root Container

Description of Figure 8-1 follows
Description of "Figure 8-1 Relocate a PDB into the Root Container"

When the target PDB is an application PDB or application root, you have the following options:

  • You can relocate a PDB into an application container as an application PDB. The target PDB can be in the same CDB or a different CDB.

  • You can relocate an application PDB from one application root to another. The target PDB can be in the same CDB or a different CDB.

  • You can relocate an empty application root from one CDB to another, but the application root must not have any hosted application PDBs.

The following graphic illustrates how this technique creates a new application PDB in an application container.

Figure 8-2 Relocate a PDB into an Application Container

Description of Figure 8-2 follows
Description of "Figure 8-2 Relocate a PDB into an Application Container"

See Also:

"PDB Storage"

8.2 Purpose of PDB Relocation

This technique is the fastest way to move a PDB with minimal or no down time. Otherwise, unplugging the source PDB requires a PDB outage until the PDB is plugged in to the target CDB.

When moving a PDB between data centers, or from an on-premises environment to a cloud environment, all the data must physically move. For large PDBs, this process may take considerable time, possibly violating availability components of an SLA. PDB relocation eliminates the outage completely. You can relocate the PDB without taking the application offline, changing the application, or changing network connection strings.

8.3 How PDB Relocation Works

The operation moves the files associated with the PDB to a new location, adds the PDB to the target CDB, and then opens the PDB.

PDB relocation executes an online block level copy or the source PDB data files, redo, and undo while the source PDB is open with active sessions. When the target PDB comes online because of an ALTER PLUGGABLE DATABASE OPEN statement, Oracle Database terminates the active sessions and closes the source PDB.

Oracle Database 18c introduces database-generic session draining, where active sessions can exit gracefully under a timer. After the timer has expired, Oracle Database terminates all active sessions and reconnects them to the relocated PDB. This feature is enabled by default for all maintenance operations invoked at the database service and PDB levels: stop service, relocate service, relocate PDB, and stop PDB.

PDB relocation services are available in Oracle RAC and single-instance environments in which a PDB relocates between CDB instances in the same clustered database or between distinct CDBs. An example of finer-grained relocation in an Oracle RAC environment is service relocation between PDB instances. Oracle RAC and Oracle Clusterware offer a rich high availability environment that further minimizes the effect on connected clients during relocation. For example, shared storage may minimize or remove the necessity to copy data files.

This section contains the following topics:

Note:

In an Oracle Clusterware environment, when relocating a PDB between different CDBs, you must create the non-default service resource using SRVCTL.

See Also:

Oracle Clusterware Administration and Deployment Guide for the SRVCTL reference

8.3.1 Server Session Draining When Relocating or Stopping PDBs

A key requirement of planned maintenance is draining or failing over PDB sessions so that application work is not interrupted.

Beginning with Oracle Database 18c, the database automatically marks sessions for draining when a PDB is relocated or stopped. The database uses an extensible set of rules to determine when to drain a database session, which persists until a rule is satisfied. The rules include the following:

  • Standard application server tests for validity

  • Custom SQL tests for validity

  • Request boundaries are in use and no request is active

  • Request boundaries are in use and the current request has ended

  • The session has one or more session states that are recoverable, and can be recreated at failover

A typical use case is application servers and pooled applications that test connections when borrowing from connection pools, returning connections to the pool, and at batch commits. When draining sessions, the database automatically intercepts the connection test, closes the connection, and then returns a failed status for the test. After receiving the failed status, the application layer can request a different connection. In this way, the application is not disrupted.

For an optimal configuration that minimizes the effect on the client, consider using Oracle RAC with FAN and Application Continuity.

See Also:

Oracle Real Application Clusters Administration and Deployment Guide to learn more about draining database sessions for planned maintenance

8.3.2 Stages of PDB Relocation

The details of PDB relocation vary depending on the listener networks.

This section contains the following topics:

8.3.2.1 PDB Relocation in a Common Listener Network

When the source and target location share a common listener network, forwarding client connections is not necessary because the SQL*Net layer forwards client connections implicitly.

AVAILABILITY NORMAL

When the listener network is common, specify the AVAILABILITY NORMAL clause in CREATE PLUGGABLE DATABASE ... RELOCATE. This option is the default. The following situations are typical use cases for AVAILABILITY NORMAL:

  • Shared listener

    If you use the same listener for the PDB in its old and new locations, then new connections are automatically routed to the new location when relocation completes. This situation is typical of a relocation between CDBs in the same host. In this case, the PDB is re-registered with the listener in its new location. Additional connection handling is not required.

  • Cross-registered listeners

    If the PDBs use different listeners, and if you employ cross-registration of their respective listeners through configuration of the local_listener and remote_listener parameters, then relocation is seamless. The availability and location of the PDB’s services are automatically registered with both listeners. This situation is typical of relocation between hosts within a data center, perhaps for load balancing purposes.

In shared and cross registered listener environments, services from all databases are published to the common listener network. For this reason, services for relocated PDBs are immediately known to the common listener network. To avoid service name space collisions, PDB service definitions must be unique in the common listener network.

Stages of Relocation in a Common Listener Network

  1. The user issues CREATE PLUGGABLE DATABASE ... RELOCATE AVAILABILITY NORMAL.

    This step executes a hot clone of the source PDB from its original location to its target location. The source PDB copies data files, undo blocks, and redo blocks to the target PDB as of an implicit begin SCN marker.

    When this step completes, two transactionally consistent copies of this PDB exist: one in the source container and one in the target container. For the duration of the operation, processing continues uninterrupted on the source PDB. Users of an application or applications connected to the source PDB are unaware that a relocation is underway.

    All existing application connections, and new connections created during this step, continue to connect to the source PDB.

  2. The user issues ALTER PLUGGABLE DATABASE OPEN.

    The following actions occur in the background:

    1. The target PDB implicitly sets the end SCN marker, and applies any redo or undo required to complete media recovery to satisfy the implicit end SCN marker.

    2. When media recovery occurs on the target PDB, Oracle Database initiates active session draining on the source PDB.

    3. PDB services are registered with the listener and are available on the target CDB.

    4. The source PDB is closed.

    5. The target PDB opens in read/write mode.

      This step completes the relocation of the PDB to the target CDB. At the end of the operation, connections point to the newly relocated PDB.

      After the PDB is opened in read/write mode, its status is NORMAL. The database returns an error if you attempt to open the PDB in read-only mode.

See Also:

8.3.2.2 PDB Relocation in Isolated Listener Networks

When independent listeners do not use cross-registration, the listener in the target CDB and source CDB have no knowledge of each other or of their respective published services.

AVAILABILITY MAX

The AVAILABILITY MAX clause in CREATE PLUGGABLE DATABASE ... RELOCATE implicitly instructs the SQL*Net layer to reconfigure the original listener. This situation may be common when relocating a PDB between data centers. This configuration is intended to be temporary while the Oracle Internet Directory (OID) or LDAP server is updated or the client connections are modified.

If a local listener redirects to a Single Client Access Name (SCAN) listener in an Oracle RAC configuration, then this listener may need to further redirect the client connection request to another cluster node. Multiple redirects are not supported by Oracle Net listeners by default. Because any SCAN listener can route the connection request to any node, set the ALLOW_MULTIPLE_REDIRECTS_listener_name parameter to the listener_name of every SCAN listener, and set it in every listener.ora file in the cluster. For example, if the SCAN listeners are named listener_scan1, listener_scan2, and listener_scan3, then the listener.ora file on every destination host should have the following settings:

ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN1=YES
ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN2=YES
ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN3=YES

Caution:

Do not set the ALLOW_MULTIPLE_REDIRECTS_listener_name parameter for node listeners because it may allow infinite redirection loops in certain network configurations.

Stages of Relocation in an Isolated Listener Network

  1. The user issues CREATE PLUGGABLE DATABASE ... RELOCATE AVAILABILITY MAX.

    This step executes a hot clone of the source PDB from its original location to its target location. The source PDB copies data files, undo blocks, and redo blocks to the target PDB as of an implicit begin SCN marker.

  2. The user issues ALTER PLUGGABLE DATABASE OPEN.

    The following actions occur in the background:

    1. The target PDB implicitly sets the end SCN marker, and applies any redo or undo required to complete media recovery to satisfy the implicit end SCN marker.

    2. When media recovery occurs on the target PDB, Oracle Database initiates active session draining on the source PDB.

    3. The LISTENER_NETWORKS initialization parameter is implicitly updated in the source PDB with the forwarding address, and the listener PDB services for the source CDB are updated with the forwarding address.

    4. The target PDB opens in read-only mode while media recovery completes.

      At this stage, only queries of the target PDB are permitted. Queries behave exactly as if they had been run on the source PDB. However, connections attempting DML do not complete.

    5. Read-only connections are immediately forwarded to the new hosting listener, and new read/write connections are forwarded to the new hosting listener, where they spin until the target PDB is opened in a consistent state.

    6. The source PDB executes a SHUTDOWN IMMEDIATE, terminating persistent connections.

    7. The target PDB opens in read/write mode.

      This step completes the relocation of the PDB to the target CDB. At the end of the operation, connections point to the newly relocated PDB.

      After the PDB is opened in read/write mode, its status is NORMAL. The database returns an error if you attempt to open the PDB in read-only mode.

Note:

An artifact known as a tombstone PDB remains in the source CDB to protect the PDB’s namespace and preserve the listener forwarding configuration until the updates are complete. In the root of the source CDB, the tombstone PDB is visible in V$CONTAINERS with a status of RELOCATED. When you change the application connect strings to provide direct connections to the target PDB, you can drop the tombstone PDB from the source CDB.

See Also:

8.4 Creating a PDB by Relocating It

The CREATE PLUGGABLE DATABASE ... RELOCATE statement moves a PDB to a different container.

The target CDB is the CDB to which the PDB is being relocated. The target PDB is the relocated PDB. After the CREATE PLUGGABLE DATABASE ... RELOCATE operation completes, Oracle Database moves the PDB from the source CDB and then adds it to the target CDB.

General Prerequisites

Address the questions that apply to relocating a PDB in "Table 5-3". The table describes which CREATE PLUGGABLE DATABASE clauses you must specify based on different factors. Also, complete the prerequisites described in "General Prerequisites for PDB Creation".

Specific Prerequisites

Before relocating the PDB, you must meet the following prerequisites:

  • The source CDB must be in local undo mode.

  • In the source CDB, you must save the service and open state of the PDBs in all database instances. Log in to the CDB root as an administrator and issue the following statement:

    ALTER PLUGGABLE DATABASE ALL SAVE STATE INSTANCES=ALL;

    This step ensures that the PDB relocation operation automatically starts the PDB services in the target CDB.

  • In the target CDB, the current user must have the CREATE PLUGGABLE DATABASE system privilege in the CDB root.

  • If the target CDB is not in ARCHIVELOG mode, then the PDB must be in open read-only mode during the operation. This requirement does not apply if the target CDB is in ARCHIVELOG mode.

  • The following prerequisites apply to the database link located in the target CDB:

    • A database link must enable a connection from the target CDB to the source CDB. The database link must connect to the root of the source CDB. If the PDB is an application PDB, then the database link must connect to its application root.

    • The user that the database link connects with in the PDB’s current CDB must have either the CREATE PLUGGABLE DATABASE system privilege or the SYSOPER administrative privilege.

    • If the database link connects to the CDB root in the PDB’s current CDB, then the user that the database link connects with must be a common user.

  • The platforms of the source CDB and the target CDB must meet the following requirements:

    • They must have the same endianness.

    • The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.

  • If the character set of the target CDB is not AL32UTF8, then the PDB’s current CDB and the CDB to which it is being relocated must have compatible character sets and national character sets.

    If the character set of the target CDB is AL32UTF8, then this requirement does not apply.

  • If you are creating an application PDB, then the source PDB and target application container must have the same application name and version.

To relocate a PDB:

  1. In SQL*Plus, log in to the target CDB as a user with the CREATE PLUGGABLE DATABASE system privilege.

  2. Ensure that the current container is the root of the target CDB or target application container.

  3. Run the CREATE PLUGGABLE DATABASE ... RELOCATE statement with the FROM clause.

    Specify the source PDB in the FROM clause, and include the RELOCATE clause. To redirect connections from the old location of the PDB to the new location, specify the AVAILABILITY MAX clause. Specify other clauses when they are required.

    After you relocate the PDB, it is in mounted mode, and its status is RELOCATING. You can view the open mode of a PDB by querying the OPEN_MODE column in the V$PDBS view. You can view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view.

    A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service.

  4. Optionally, to determine the status of the file copy operation, query V$SESSION_LONGOPS.

    The OPNAMES column shows kpdbfCopyTaskCbk for the data file copy and kcrfremnoc for the redo file copy.

  5. Open the new PDB in read/write mode.

    This step is required to complete the integration of the new PDB into the CDB. After the PDB is opened in read/write mode, its status is NORMAL. An error is returned if you attempt to open the PDB in read-only mode.

  6. Back up the PDB.

    A PDB cannot be recovered unless it is backed up.

    Note:

    If an error is returned during PDB relocation, then the PDB being created might be in an UNUSABLE state. You can check the PDB state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

Example 8-1 Relocating a PDB from a Remote CDB

In this example, the root to which the new PDB belongs depends on the current container when the CREATE PLUGGABLE DATABASE statement is run:

  • When the current container is the CDB root, the new PDB is created in the CDB root.

  • When the current container is an application root in an application container, the new PDB is created as an application PDB in the application root.

This example relocates a PDB named pdb1 from a remote CDB given different factors. This example assumes the following factors:

  • The current user has the CREATE PLUGGABLE DATABASE system privilege in the root of the target CDB.

  • The database link name to the source CDB is lnk2src. This database link was created with the following SQL statement:

    CREATE PUBLIC DATABASE LINK lnk2src CONNECT TO c##myadmin IDENTIFIED BY password USING 'MYCDB';

    The common user c##myadmin has SYSOPER administrative privilege and CREATE PLUGGABLE DATABASE system privilege in the source CDB.

  • The PATH_PREFIX clause is not required.

  • The FILE_NAME_CONVERT clause and the CREATE_FILE_DEST clause are not required.

    Either Oracle Managed Files is enabled, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files will be moved to a new location based on the Oracle Managed Files configuration or the initialization parameter setting.

  • Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.

  • There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

  • Connections should be relocated automatically from the source PDB to the relocated PDB. Therefore, the AVAILABILITY MAX clause is included.

The following statement relocates the pdb1 PDB from the source CDB to the current CDB:

CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@lnk2src RELOCATE AVAILABILITY MAX;

See Also: