Home » Articles » 19c » Here

Multitenant : DBCA PDB Relocate in Oracle Database 19c

Oracle Database 12c Release 2 (12.2) introduced the ability to relocate pluggable database (PDB) using the CREATE PLUGGABLE DATABASE command. In Oracle 19c it's now possible to perform a relocate of a pluggable database (PDB) using the Database Configuration Assistant (DBCA).

• Prerequisites
• Relocate a PDB with the DBCA
• Appendix

Related articles.

• Multitenant : Relocate a PDB in Oracle Database 12c Release 2 (12.2)
• Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
• Multitenant : All Articles
• Multitenant : YouTube Playlist

Prerequisites

Connect to the remote CDB and prepare it for relocating.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a user in the remote database for use with the database link. In this case, we will use a common user in the remote PDB.

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;

Unlike a regular relocate, we don't need to create a database link. We just need to supply the credentials we would use to create the link. The DBCA does the rest.

Check the remote CDB is in local undo mode and archivelog mode.

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Because the remote CDB is in local undo mode and archivelog mode, we don't need to turn the remote database into read-only mode.

Connect to the local CDB and prepare it for relocating.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Check the local CDB is in local undo mode and archivelog mode.

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode
FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Relocate a PDB with the DBCA

In 19c the DBCA -relocatePDB command has been introduced, allowing us to relocate a PDB to a different container database (CDB).

   -relocatePDB - Command to Relocate a pluggable database.
      -remotePDBName <Name of the pluggable database to clone/relocate>
      -pdbName <Pluggable database name>
      -dbLinkUsername <Common user of a remote CDB, used by database link to connect to remote CDB.>
      -remoteDBConnString <EZCONNECT string to connect to Source database for example "host:port/servicename">
      -sourceDB <Database unique name for RAC database or SID for Single Instance database>
      [-remoteDBSYSDBAUserName <User name with SYSDBA privileges of remote database>]
      [-dbLinkUserPassword <Common user password of a remote CDB, used by database link to connect to remote CDB.>]
      [-useWalletForDBCredentials <true | false> Specify true to load database credentials from wallet]
         -dbCredentialsWalletLocation <Path of the directory containing the wallet files>
         [-dbCredentialsWalletPassword <Password to open wallet with auto login disabled>]
      [-remoteDBSYSDBAUserPassword <Password for remoteDBSYSDBAUserName user of remote database.>]
      [-sysDBAUserName <User name  with SYSDBA privileges>]
      [-sysDBAPassword <Password for sysDBAUserName user name>]

You can see the full syntax for the -relocatePDB command here, although at the time of writing the documentation doesn't match the utility usage presented by the dbca -relocatePDB -help command.

Make sure the environment is set up to point to the local instance "cdb1" and relocate the PDB called "pdb5" from the remote PDB called "pdb5" in the "cdb3" instance.

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -relocatePDB \
  -pdbName pdb5 \
  -sourceDB cdb1 \
  -remotePDBName pdb5 \
  -remoteDBConnString localhost:1521/cdb3 \
  -remoteDBSYSDBAUserName sys \
  -remoteDBSYSDBAUserPassword SysPassword1 \
  -dbLinkUsername c##remote_clone_user \
  -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "pdb5" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5/cdb10.log" for further details.
$

Connect to the local root container and check the status of the new PDB.

COLUMN name FORMAT A30

SELECT con_id, name, open_mode
FROM   v$pdbs
ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB5                           READ WRITE

SQL>

To relocate it back we need to create the link user in the local database.

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;

Make sure the environment is set up to point to the remote instance "cdb3" and relocate the PDB called "pdb5" from the local PDB called "pdb5" in the "cdb1" instance.

export ORACLE_SID=cdb3
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -relocatePDB \
  -pdbName pdb5 \
  -sourceDB cdb3 \
  -remotePDBName pdb5 \
  -remoteDBConnString localhost:1521/cdb1 \
  -remoteDBSYSDBAUserName sys \
  -remoteDBSYSDBAUserPassword SysPassword1 \
  -dbLinkUsername c##remote_clone_user \
  -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "pdb5" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/pdb5/cdb10.log" for further details.
$

Connect to the remote root container and check the status of the new PDB.

COLUMN name FORMAT A30

SELECT con_id, name, open_mode
FROM   v$pdbs
ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB5                           READ WRITE

SQL>

Appendix

The instances and pluggable databases used in these examples are created using the following commands.

# Local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

 

# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1

The databases have Oracle Managed Files (OMF) enabled and are switched to archivelog mode.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF


export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

EXIT;
EOF

For more information see:

• Multitenant : All Articles
• createPluggableDatabase
• Relocating a PDB Using DBCA: Example
• Multitenant : Relocate a PDB in Oracle Database 12c Release 2 (12.2)
• Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
• Multitenant : All Articles
• Multitenant : YouTube Playlist

Hope this helps. Regards Tim...

Back to the Top.

Created: 2019-03-02  Updated: 2020-06-30

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer