Oracle Database 12c version 2 (12.2) introduces the ability to relocate pluggable databases (PDBs) using the CREATE PLUGGABLE DATABASE command. In Oracle 19c, relocation of pluggable databases (PDBs) can now be performed using the database configuration assistant (DBCA).
1, Prerequisites
Connect to the remote CDB and prepare for relocation:
export ORAENV_ASK=NO export ORACLE_SID=cdb3 . oraenv export ORAENV_ASK=YES sqlplus / as sysdba
Create a user in the remote database to use with the database link. In this case, we will use a normal 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 regular relocation, we do not need to create database links. We only need to provide the credentials used to create the link. DBCA completes the rest of the work.
Check whether the remote CDB is in local undo mode and archive 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 local undo mode and archive mode, we do not need to change the remote database into read-only mode.
Connect to the local CDB and prepare for migration:
export ORAENV_ASK=NO export ORACLE_SID=cdb1 . oraenv export ORAENV_ASK=YES sqlplus / as sysdba
Check whether the local CDB is in local undo mode and Archive 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>
2, Relocate PDB using DBCA
In 19c, - relocatePDB introduces the DBCA command, which allows us to relocate the 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 here View the full syntax of the - relocatePDB command, although it does not match the utility usage provided by the command when writing the document.
dbca -relocatePDB -help
Ensure that the environment is set to point to the local instance "cdb1" and relocate the PDB named "pdb5" from the remote PDB named "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, we need to create linked users 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;
Ensure that the environment is set to point to the remote instance "cdb3" and relocate the PDB named "pdb5" from the local PDB named "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>3, Appendix
The instances and pluggable databases used in these examples were created using the following command:
# 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
Oracle Managed Files (OMF) is enabled for the database and switched to archive log 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