8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Relocate a PDB in Oracle Database 12c Release 2 (12.2)
From Oracle 12.2 onward you can relocate a PDB by moving it between two root containers with near zero-downtime.
The process is similar to performing a remote clone of a PDB.
Related articles.
- Multitenant : Relocate a Pluggable Database (PDB)
- Multitenant : All Articles
- Multitenant : Local Undo Mode in Oracle Database 12c Release 2 (12.2)
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2)
Prerequisites
In this context, the word "local" refers to the destination or target CDB that will house the relocated PDB. The word "remote" refers to the PDB that is to be relocated.
- The user in the local database must have the
CREATE PLUGGABLE DATABASE
privilege in the root container. - The remote CDB must use local undo mode. Without this you must open the remote PDB.
- The remote and local databases should be in archivelog mode.
- The local database must have a public database link to the remote CDB using a common user.
- The common user in the remote database that the database link connects to must have the
CREATE PLUGGABLE DATABASE
and SYSDBA or SYSOPER privilege. - The local and remote databases must have the same endianness.
- The local and remote databases must be the same version, and the
COMPATIBLE
parameter of the remote database can't be higher than the local database. - The local and remote databases must either have the same options installed, or the remote database must have a subset of those present on the local database.
- If the character set of the local CDB is AL32UTF8, the remote database can be any character set. If the local CDB does not use AL32UTF8, the character sets of the remote and local databases much match.
- If the remote database uses Transparent Data Encryption (TDE) the local CDB must be configured appropriately before attempting the relocate. If not you will be left with a new PDB that will only open in restricted mode.
- Bug 19174942 is marked as fixed in 12.2. I can't confirm this, so just in case I'll leave this here, but it should no longer be the case. The default tablespaces for each common user in the remote PDB *must* exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don't do this your new PDB will only be able to open in restricted mode (Bug 19174942).
In the examples below I have two databases running on the same virtual machine, but they could be running on separate physical or virtual servers.
- cdb1 : The local database that will eventually house the relocated PDB.
- cdb3 : The remote CDB that houses the PDB (pdb5) to be relocated.
Prepare Remote CDB
Connect to the remote CDB and prepare the remote PDB 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 must use a comon user in the remote CDB.
CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL; GRANT CREATE SESSION, SYSOPER, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
Check the remote CDB is in local undo mode and archivelog mode.
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.
Prepare Local CDB
Switch to the local server and create a "tnsnames.ora" entry pointing to the remote CDB for use in the USING
clause of the database link. The connection details must include the "(SERVER = DEDICATED)" entry, or you will receive a "ORA-01031: insufficient privileges" error.
CDB3 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = my-server.my-domain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb3) ) )
Connect to the local database to initiate the relocate.
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.
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>
Create a public database link in the local CDB, pointing to the remote CDB.
Remember to remove this once the relocate is complete.
DROP PUBLIC DATABASE LINK clone_link; CREATE PUBLIC DATABASE LINK clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user USING 'cdb3'; -- Test link. DESC user_tables@clone_link
Relocate a PDB
Create a new PDB in the local CDB by relocating the remote PDB. In this case we are using Oracle Managed Files (OMF), so we don't need to bother with FILE_NAME_CONVERT
parameter for file name conversions.
CREATE PLUGGABLE DATABASE pdb5 FROM pdb5@clone_link RELOCATE; Pluggable database created. SQL>
We can see the new PDB has been created, but it is in the MOUNTED state.
COLUMN name FORMAT A30 SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5'; NAME OPEN_MODE ------------------------------ ---------- PDB5 MOUNTED SQL>
The PDB is opened in read-write mode to complete the process.
ALTER PLUGGABLE DATABASE pdb5 OPEN; SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5'; NAME OPEN_MODE ------------------------------ ---------- PDB5 READ WRITE SQL>
Drop the public database link.
DROP PUBLIC DATABASE LINK clone_link;
As with any PDB clone, check common users and the temporary tablespace is configured as expected.
If we switch back to the remote instance we can see PDB5 has been dropped.
export ORAENV_ASK=NO export ORACLE_SID=cdb3 . oraenv export ORAENV_ASK=YES sqlplus / as sysdba SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5'; no rows selected SQL>
Managing Connections
Moving the database is only one aspect of keeping a system running. Once the database is in the new location, you need to make sure connections can still me made to it. The options are as follows.
- If your connection information is centralised in an LDAP server (OID, AD etc.) then the definition can be altered centrally.
- If both CBSs use the same listener, the relocated PDB will auto-register once the relocate is complete.
- If both CDBs use different listeners, you can specify
RELOCATE AVAILABILITY MAX
to instruct the initial listener to forward connections to the new listener. - If both CDBs use different listeners, the
LOCAL_LISTENER
andREMOTE_LISTENER
parameters can be used to configure cross-registration.
Appendix
Here are the DBCA commands to create and delete the CDB instances and PDBs used by these examples.
# Empty 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 SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u02/oradata/" \ -redoLogFileSize 50 \ -initParams encrypt_new_tablespaces=DDL \ -emConfiguration NONE \ -ignorePreReqs 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 # 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 SysPassword1 \ -databaseType MULTIPURPOSE \ -memoryMgmtType auto_sga \ -totalMemory 2048 \ -storageType FS \ -datafileDestination "/u02/oradata/" \ -redoLogFileSize 50 \ -initParams encrypt_new_tablespaces=DDL \ -emConfiguration NONE \ -ignorePreReqs 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 # Delete the instances. #dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1 #dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword SysPassword1
For more information see:
- Multitenant : Relocate a Pluggable Database (PDB)
- Multitenant : All Articles
- Multitenant : Local Undo Mode in Oracle Database 12c Release 2 (12.2)
- Creating a PDB by Relocating It
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c (12.1 and 12.2)
Hope this helps. Regards Tim...