PDB relocate using "AVAILABILITY MAX" clause

A PDB relocation executes an online block level copy of the source PDB data files, redo, and undo while the source PDB is open with active sessions. When the target PDB comes online because of an ALTER PLUGGABLE DATABASE OPEN statement, Oracle Database terminates the active sessions and closes the source PDB. 

The AVAILABILITY MAX clause in CREATE PLUGGABLE DATABASE ... RELOCATE implicitly instructs the SQL*Net layer to reconfigure the original listener. This situation may be common when relocating a PDB between data centers. This configuration is intended to be temporary while the Oracle Internet Directory (OID) or LDAP server is updated or the client connections are modified.

Overview image Relocate PDB

Requirements for executing a relocate PDB between two CDB's

1. The user in the local database has the CREATE PLUGGABLE DATABASE privilege in the root container.
2. The remote CDB is in local undo mode.
3. The remote and local CDBs are in ARCHIVELOG mode.
4. The common user in the remote CDB to whom the database link connects has the CREATE PLUGGABLE DATABASE, SESSION, and SYSOPER privilege.
5. The local and remote CDBs have the same options installed.

Environment

• CDB1 with multiple PDB's including the RELOCATE pdb 
• A default listener used bij de CDB1 on port 1521
• CDB2 without pdb's
• A second listener used by CDB2 on port 1525

Steps to execute a PDB relocation with minimal downtime

1. Create common user in both CDB's

> CDB1> create user c##relocate identified by oracle container=all;

 CDB1> grant create session, SYSOPER to C##RELOCATE container=all;

2. Create databaselink to source from the target

 CDB2> create database link lnk_src connect to c##relocate identified by oracle using 'hol:1521/cdb1';

3. Create test table for validation

CDB1_relocate> create table scott.relocate ( step number, text varchar2(100)) tablespace users;
CDB1_relocate> insert into scott.relocate values (1, 'before relocation insert')
CDB1_relocate> commit;

4. Execute the relocate PDB command

CDB2> create pluggable database relocate from relocate@lnk_src relocate AVAILABILITY MAX;

5. Insert new data to the test table

CDB1_relocate> insert into scott.relocate values (2, 'after relocation insert');
CDB1_relocate> commit;
CDB1_relocate> update scott.relocate set text = 'after relocation update' where step=2 ;
CDB1_relocate> commit;

6. Open the new PDB on CDB2

CDB2> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RELOCATE                       MOUNTED

CDB2> alter pluggable database relocate open;

CDB2> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 RELOCATE                       READ WRITE NO

CDB2> ALTER SESSION SET CONTAINER=RELOCATE;

CDB2_relocate>select * from scott.relocate;

      STEP TEXT                       
---------- ------------------------------ 
         1 before relocation insert
         2 after relocation update               

Testing connectivity using the entry of CDB1 on port 1521

We want to see if the sql string from the application can contact to the new relocated pdb database without chanhing the connection string

oracle@hol ~]$ sqlplus c##relocate/oracle@hol:1521/relocate 

When executing this we see the following happening in the listener logs

====> listener log CDB1 ============
2022-09-12T12:29:29.658798+02:00
12-SEP-2022 12:29:29 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=16014)) * establish * relocate * 0

====> listener log CDB2 ============
2022-09-12T12:29:29.671058+02:00
12-SEP-2022 12:29:29 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=58266)) * establish * relocate * 0

SQLPLUS test case in which CDB are we connected 

oracle@hol ~]$ SQL> SELECT SYS_CONTEXT('USERENV','CDB_NAME') database_name from dual;
DATABASE_NAME
--------------------------------------------------------------------------------
cdb2

Perfect the listener of the CDB1  will forward it to the listener CDB2, and we get a session connected on the cdb database CDB2

The old PDB relocate on CDB1 cannot be opened as you can see

CDB1> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 RELOCATE                       MOUNTED

CDB1> alter pluggable database relocate open;
alter pluggable database relocate open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database