MIGRATE PLUGGABLE DATABASE

The MIGRATE PLUGGABLE DATABASE command lets you migrate a pluggable database (PDB) from one multitenant container database (CDB) to another on the same host. You can migrate a PDB from a primary CDB to another primary CDB or failover a PDB from a standby CDB to a primary CDB.

With the introduction of Oracle AI Database 26ai, the MIGRATE PLUGGABLE DATABASE command can reuse the standby data files when moving a pluggable database from a primary CDB in a Data Guard Broker's configuration to another primary CDB in a different broker's configuration. To reuse standby data files, you must ensure that:
  • All databases in the source/destination configurations must use OMF and ASM.
  • The source standby CDB must share the same ASM disk group as the destination standby CDB.

Format

MIGRATE PLUGGABLE DATABASE [VERIFY][IMMEDIATE] <pdb_name>
TO CONTAINER <dest_cdb_name>
USING XML_description_file
[CONNECT AS { /@<dest_cdb_connect_identifer>  | <dest_cdb_user>/<dest_cdb_pass>@<dest_cdb_connect_identifer>} ]
[ SECRET secret KEYSTORE IDENTIFIED BY ( EXTERNAL STORE | <wallet_password>) ][STANDBY FILES { /@<asm_instance_connect_identifer>  | sysasm_user/sysasm_pass@<asm_instance_connect_identifer>} ]
[SOURCE STANDBY <source_standby_cdb_name>
[DESTINATION STANDBY <dest_standby_cdb_name>
[TIMEOUT timeout]
[KEYFILE <key_file>]
[ SOURCE KEYSTORE IDENTIFIED BY ( EXTERNAL STORE | <source_wallet_password>) ];

Prerequisites

The MIGRATE PLUGGABLE DATABASE command has the following prerequisites:

  • The destination CDB must be created and started in such a way that it can access the PDB data files at the same file path as the source CDB.

  • The source and destination CDBs must each be in a different Data Guard broker configuration.

  • The source CDB can either be a primary database or a physical standby database.

  • If the source PDB uses Transparent Data Encryption (TDE), the encryption keys associated with the source PDB must also be migrated. Connect to the database root and use the ADMINISTER KEY MANAGEMENT command to export the master encryption keys. The clauses used with this command depend on the PDB mode (isolated mode or united mode) and the type of keystore (software-based keystore or password keystore).

    The file containing the exported keys must be accessible to the destination CDB either using Secure File Copy (SCP) or Oracle Automatic Storage Management Cluster File System (Oracle ACFS).

  • If the source CDB is a physical standby database:

    • source CDB and destination CDB must be running the same Oracle version and patches.

    • source CDB and destination CDB must have the same setting for the COMPATIBLE initialization parameter.

    • PDB to be migrated must be closed on its primary CDB.

    • execute the failover

      if a PDB failover will result in lost data, then you must specifically choose to execute the failover by using the IMMEDIATE option.

  • If the source CDB is a primary database, then

    • the destination CDB cannot be running a lower version of Oracle.

    • the setting of the COMPATIBLE initialization parameter cannot be set to a lower value on the destination CDB than on the source CDB.

  • The services for the PDB must be stopped and removed from the Oracle Clusterware repository of the source database to be migrated. This should be done for all databases in the Data Guard broker configuration of the source database.

  • The destination CDB must be open.

  • The user executing this command must have SYSDBA privileges for both the source and destination CDBs.

  • If reusing source standby database files is needed, then:
    • The clause STANDBY FILES must be used to specify the connect identifier to the ASM instance having the source standby database files
    • The parameter DB_CREATE_FILE_DEST in the source and destination CDB standby databases must be set to the same ASM disk group name.
    • The parameter DB_FILE_NAME_CONVERT in the destination CDB standby database must be null.
    • The parameter STANDBY_FILE_MANAGEMENT must be AUTO on the source standby database.

Command Parameters

pdb_name
The name of the PDB to be migrated.
dest_cdb_name
The database unique name of the CDB to receive the PDB to be migrated.
XML_description_file
An XML file that contains the description of the PDB to be migrated. This file is automatically created by the SQL statements executed by the MIGRATE PLUGGABLE DATABASE command.
dest_cdb_user
The user name of the user that has SYSDBA access to the destination CDB.
dest_cdb_pass
The password associated with the user name specified for dest_cdb_user.
dest_cdb_connect_identifier
An Oracle Net connect identifier used to reach the destination CDB.
secret
The password that was used to encrypt the export file containing the exported encryption keys of the source PDB. This password would have been specified as part of the ADMINISTER KEY MANAGEMENT command, when exporting the PDB encryption keys.
wallet_password
The password of the keystore containing the encryption keys. This is required if the source PDB was encrypted using a password keystore.
asm_instance_connect_identifer
The connect identifier to the ASM instance having the source standby database file.
sysasm_user
A user having SYSASM privilege for ASM instance.
sysasm_pass
The password for sysasm_user.
source_standby_cdb_name
DB_UNIUE_NAME of the migration source CDB’s standby database.
dest_standby_cdb_name
DB_UNIUE_NAME of the migration destination CDB’s standby database.
timeout
The timeout value in seconds when waiting for the destination standby database picks up the data files during migration.
key_file
The key file location for exporting TDE master keys.
source_wallet_password
The keystore password of the source CDB/PDB of a PDB to migrate.

Usage Notes

  • By default, when this command is used for PDB failover, the failover attempt is rejected if there is a possibility of data loss. You can override this default behavior by using the IMMEDIATE option.

  • The IMMEDIATE option is ignored if the source database is a primary database.

  • The VERIFY option performs a pre-check to determine if the PDB can be migrated successfully.

  • If a connect identifier is specified, then database credentials are used to authenticate the user on the destination CDB.

  • Operating system credentials cannot be used to authenticate the user on the destination CDB. A connect identifier must be specified; a slash (/) is not supported.

  • For cases in which a slash (/) is used to specify a connect identifier (for example, /@boston), the credentials are fetched from the wallet.

  • The following options are available if you want to specify a connect string:

    • /@dest_cdb_connect_identifier (credentials are fetched from the wallet)

    • dest_cdb_user/dest_cdb_pass@dest_cdb_connect_identifier (uses database credentials)

  • To prevent the password from being visible on the command line, specify only a user name with a connect identifier. You will then be prompted for a password. The following options are supported when you supply only a user name and connect identifier:

    • dest_cdb_user@dest_cdb_connect_identifier (uses database credentials)

    • dest_cdb_user/@dest_cdb_connect_identifier (uses database credentials)

  • If you omit the connect string entirely from the command line, then you will be prompted for a user name and password. The following options are supported:

    • /@dest_cdb_connect_identifier (no prompt for password, credentials are fetched from the wallet)

    • dest_cdb_user@dest_cdb_connect_identifier (uses database credentials)

    • dest_cdb_user/@dest_cdb_connect_identifier (uses database credentials)

  • If you omit sysasm_pass, then you will be prompted for password for sysasm_user.
  • The database specified in SOURCE STANDBY clause must be a physical standby database. This clause is optional if the migration source primary database has only one physical standby
  • The database specified in DESTINATION STANDBY clause must be a physical standby database. This clause is optional if the migration destination primary database has only one physical standby.
  • If the clause TIMEOUT is not specified, the default 1800 seconds will be used.
  • For a failover from a standby database when TDE is enabled, the clause KEYFILE is needed to specify the key file location for exporting TDE master keys, and followed by the clause SOURCE KEYSTORE IDENTIFIED BY to specify the keystore password of the source CDB.

Command Examples

Example 1: Migrating a PDB From a Primary CDB

DGMGRL> MIGRATE PLUGGABLE DATABASE REGION1 TO CONTAINER NORTH_SALES_NEW USING REGION1.xml 
CONNECT AS sys@NORTH_SALES_NEW;
Connected to "NORTH_SALES_NEW"
Connected.

Beginning migration of pluggable database REGION1.
Source multitenant container database is NORTH_SALES.
Destination multitenant container database is NORTH_SALES_NEW.

Closing pluggable database REGION1 on all instances of multitenant container database NORTH_SALES.
Unplugging pluggable database REGION1 from multitenant container database NORTH_SALES.
Pluggable database description will be written to REGION1.xml.
Dropping pluggable database REGION1 from multitenant container database NORTH_SALES.
Creating pluggable database REGION1 on multitenant container database NORTH_SALES_NEW.
Opening pluggable database REGION1 on all instances of multitenant container database NORTH_SALES_NEW.
Succeeded.

Example 2: Failing over a PDB from a Physical Standby

DGMGRL> MIGRATE PLUGGABLE DATABASE REGION1 TO CONTAINER SOUTH_SALES_NEW 
USING REGION1.xml CONNECT AS sys@SOUTH_SALES_NEW;
Connected to "SOUTH_SALES_NEW"
Connected.

Beginning migration of pluggable database REGION1.
Source multitenant container database is SOUTH_SALES.
Destination multitenant container database is SOUTH_SALES_NEW.

Continuing with migration of pluggable database REGION1 to multitenant container database SOUTH_SALES_NEW.
Stopping Redo Apply services on source multitenant container database SOUTH_SALES.
Succeeded.
Opening database SOUTH_SALES.
Opening pluggable database REGION1 on source multitenant container database SOUTH_SALES to prepare for migration.
Pluggable database description will be written to REGION1.xml.
Closing pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES.
Disabling media recovery for pluggable database REGION1.
Closing database SOUTH_SALES.
Restarting redo apply services on source multitenant container database SOUTH_SALES.
Succeeded.
Creating pluggable database REGION1 on multitenant container database SOUTH_SALES_NEW.
Opening pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES_NEW.
Unplugging pluggable database REGION1 from multitenant container database NORTH_SALES.
Dropping pluggable database REGION1 from multitenant container database NORTH_SALES.
Succeeded.

Example 3: Reusing the Source Standby Database Files When Plugging a PDB into the Primary Database of a Data Guard Configuration

MIGRATE PLUGGABLE DATABASE REGION1 TO CONTAINER SOUTH_SALES_NEW USING REGION1.xml STANDBY FILES sysasm@asm_tns SOURCE STANDBY SOUTH_SALES_STANDBY DESTINATION STANDBY SOUTH_SALES_NEW_STANDBY ; 
Connected.

Beginning migration of pluggable database REGION1.
Source multitenant container database is SOUTH_SALES.
Destination multitenant container database is SOUTH_SALES_NEW.

Connecting to "inst11".
Connected as SYSASM.
Stopping Redo Apply services on multitenant container database SOUTH_SALES_NEW_STANDBY.
The guaranteed restore point "…" was created for multitenant container database "SOUTH_SALES_NEW_STANDBY".
Restarting redo apply services on multitenant container database SOUTH_SALES_NEW_STANDBY.
Closing pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES.
Unplugging pluggable database REGION1 from multitenant container database SOUTH_SALES.
Pluggable database description will be written to ...
Dropping pluggable database REGION1 from multitenant container database SOUTH_SALES.
Waiting for the pluggable database REGION1 to be dropped from standby multitenant container database SOUTH_SALES_STANDBY.
Creating pluggable database REGION1 on multitenant container database SOUTH_SALES_NEW.
Checking whether standby multitenant container database SOUTH_SALES_NEW_STANDBY has added all data files for pluggable database REGION1.
Opening pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES_NEW.
The guaranteed restore point "…" was dropped for multitenant container database "SOUTH_SALES_NEW_STANDBY".
Migration of pluggable database REGION1 completed.
Succeeded.

Example 4: Reusing the Source Standby Database Files with TDE Enabled When Plugging a PDB into the Primary Database of a Data Guard Configuration

DGMGRL> MIGRATE PLUGGABLE DATABASE REGION1 TO CONTAINER SOUTH_SALES_NEW 
USING REGION1.xml CONNECT AS sys@SOUTH_SALES_NEW;
Connected to "SOUTH_SALES_NEW"
Connected.

Beginning migration of pluggable database REGION1.
Source multitenant container database is SOUTH_SALES.
Destination multitenant container database is SOUTH_SALES_NEW.

Continuing with migration of pluggable database REGION1 to multitenant container database SOUTH_SALES_NEW.
Stopping Redo Apply services on source multitenant container database SOUTH_SALES.
Succeeded.
Opening database SOUTH_SALES.
Opening pluggable database REGION1 on source multitenant container database SOUTH_SALES to prepare for migration.
Pluggable database description will be written to REGION1.xml.
Closing pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES.
Disabling media recovery for pluggable database REGION1.
Closing database SOUTH_SALES.
Restarting redo apply services on source multitenant container database SOUTH_SALES.
Succeeded.
Creating pluggable database REGION1 on multitenant container database SOUTH_SALES_NEW.
Opening pluggable database REGION1 on all instances of multitenant container database SOUTH_SALES_NEW.
Unplugging pluggable database REGION1 from multitenant container database NORTH_SALES.
Dropping pluggable database REGION1 from multitenant container database NORTH_SALES.
Succeeded.

Example 5: Performing a Pre-Migration Verification of a Source PDB

DGMGRL> MIGRATE PLUGGABLE DATABASE REGION10 TO CONTAINER SOUTH_SALES_NEW USING .* CONNECT AS sys/knl_test7@tkdg4_tns SECRET "s123" KEYSTORE IDENTIFIED BY "Welcome4c" STANDBY FILES sys/knl_test7@inst11 ; 
Connected.
Master keys of the pluggable database REGION10 to need to be migrated.
Keystore of pluggable database REGION10 is open.

Beginning migration of pluggable database REGION10.
Source multitenant container database is SOUTH_SALES.
Destination multitenant container database is SOUTH_SALES_NEW.

Connecting to "inst11".
Connected as SYSASM.
Stopping Redo Apply services on multitenant container database SOUTH_SALES_NEW_STANDBY.
The guaranteed restore point "..." was created for multitenant container database "SOUTH_SALES_NEW_STANDBY".
Restarting redo apply services on multitenant container database SOUTH_SALES_NEW_STANDBY.
Closing pluggable database REGION10 on all instances of multitenant container database SOUTH_SALES.
Unplugging pluggable database REGION10 from multitenant container database SOUTH_SALES.
Pluggable database description will be written to ...
Dropping pluggable database REGION10 from multitenant container database SOUTH_SALES.
Waiting for the pluggable database REGION10 to be dropped from standby multitenant container database SOUTH_SALES_STANDBY.
Creating pluggable database REGION10 on multitenant container database SOUTH_SALES_NEW.
Checking whether standby multitenant container database SOUTH_SALES_NEW_STANDBY has added all data files for pluggable database REGION10.
Stopping Redo Apply services on multitenant container database SOUTH_SALES_NEW_STANDBY.
Opening pluggable database REGION10 on all instances of multitenant container database SOUTH_SALES_NEW.
The guaranteed restore point "..." was dropped for multitenant container database "SOUTH_SALES_NEW_STANDBY".
Please complete the following steps to finish the operation:
1. Copy keystore located in ... for migration destination primary database to ... for migration destination standby database.
2. Start DGMGRL, connect to multitenant container database SOUTH_SALES_NEW, and issue command "EDIT DATABASE SOUTH_SALES_NEW_STANDBY SET STATE=APPLY-ON".
3. If the clusterware is configured on multitenant container databases SOUTH_SALES_NEW or SOUTH_SALES_NEW_STANDBY, add all non-default services for the migrated pluggable database in cluster ready services.
Migration of pluggable database REGION10 completed.
Succeeded.