8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Multitenant : Relocate

The process is similar to performing a remote clone of a PDB.

Related articles.

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.

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.

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. It is a massive security problem to leave this in place!

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
------------------------------ ----------
PDB                            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.

Appendix

These tests were performed on a free trial of the Oracle Database Cloud Service, where the CDB1 instance and PDB1 pluggable database were created as part of the service creation. The additional instance was built on the same virtual machine using the commands below. I've included the DBCA commands to create and delete the CDB1 instance for completeness. They were not actually used.

# Empty local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -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 OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs
 

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

As explained earlier, in all cases Oracle Managed Files (OMF) was used so no file name conversions were needed. Also, the databases were 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 = '/u01/app/oracle/oradata';

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

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

EXIT;
EOF

For more information see:

Hope this helps. Regards Tim...

Back to the Top.