8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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).
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...