Multitenant pluggable database cloning and moving – part 2

Posted on , updated on by Yannick Jaquier
Oracle

Table of contents

Preamble

After a first post where we have seen on how to migrate from 19c to 21c a pluggable database and the features that are restricted to special hardware of Oracle. Let see on how to perform pluggable database cloning and rellocation.

For this testing, done on Red Hat Enterprise Linux Server release 7.9 (Maipo), I’m using two instances of Oracle Enterprise Edition 21c (21.6).

Pluggable database cloning

Cloning a PDB from an existing PDB is exactly what you would do to create a copy of your production database for testing purposes (application upgrade, tuning or whatever). As we will see not all method are able to perform a remote copy. Means that your copy will be side by side your production database. Which is clearly unwanted in a real life production environment. You can still move this copy to aonther server but you will need some additional storage on your production node for a while…

multitenant_pdb_clone
multitenant_pdb_clone

Cloning local

The easiest pluggable database cloning is the local one from an existing PDB:

SQL> CREATE pluggable DATABASE pdb2 FROM pdb1 
     path_prefix = '/u01/app/oracle/oradata/ORCL2/pdb2/'
     file_name_convert = ('/u01/app/oracle/oradata/ORCL2/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb2/');
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> 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

All figures of PDB1 have been copied to PDB2. The only “drawback” is that this copy is local and you might not want to do your testing on your production server. So an additional step must be done to move this copy to your development server (unplug/plug).

Remark:
With USER_TABLESPACES keyword you can exclude all or some users’ tablespaces from the copy. With NO DATA keyword you copy only the metadata of users’ objects, all figures are discarded.

Cloning remote

This one is more interesting because you can create a copy of you production PDB on a development server in another container. I plan to create in CDB orcl2 from CDB orcl1. Even if they are on same server it makes no difference. Let’s start first by creating a public database from orcl2 to orcl1:

SQL> CREATE PUBLIC DATABASE LINK orcl2_to_orcl1 CONNECT TO c##pdbadmin IDENTIFIED BY "secure_password" USING '//server01.domain.com:1531/orcl1';
 
DATABASE LINK created.

Now create the remote clone with:

SQL> CREATE pluggable DATABASE pdb2 FROM pdb1@orcl2_to_orcl1
     path_prefix = '/u01/app/oracle/oradata/ORCL2/pdb2/'
     file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb2/');
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           READ WRITE NO
         4 PDB1                           READ WRITE NO
SQL> ALTER SESSION SET container=pdb2;
 
SESSION altered.
 
SQL> SELECT * FROM yjaquier.test01;
 
        ID DESCR
---------- --------------------------------------------------
         1 One
         2 Two

Refreshable clone PDB

The refreshable (or not) clone must be created in a remote container from your source PDB. I already have the public database link now let’s try to create this refreshable clone PDB:

SQL> CREATE pluggable DATABASE pdb2 FROM pdb1@orcl2_to_orcl1
     path_prefix = '/u01/app/oracle/oradata/ORCL2/pdb2/'
     file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb2/')
     refresh MODE manual;
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           READ ONLY  NO
         4 PDB1                           READ WRITE NO

Remark:
I have chosen a manual refresh but it can also REFRESH MODE NONE or REFRESH MODE EVERY refresh_interval [HOURS | MINUTES].

The refreshable clone PDB is automatically in read only mode. To refresh it you must close it. I have inserted a new row in my source PDB TEST01 test table. Let see if the row is refreshed:

SQL> ALTER pluggable DATABASE pdb2 refresh;
ALTER pluggable DATABASE pdb2 refresh
*
ERROR AT line 1:
ORA-65025: Pluggable DATABASE PDB2 IS NOT closed ON ALL instances.
 
 
SQL> ALTER pluggable DATABASE pdb2 CLOSE IMMEDIATE;
 
Pluggable DATABASE altered.
 
SQL> ALTER pluggable DATABASE pdb2 refresh;
 
Pluggable DATABASE altered.
 
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           READ ONLY  NO
         4 PDB1                           READ WRITE NO
SQL> ALTER SESSION SET container=pdb2;
 
SESSION altered.
 
SQL> SELECT * FROM yjaquier.test01;
 
        ID DESCR
---------- --------------------------------------------------
         1 One
         2 Two
         3 Three

Proxy PDB

The proxy PDB as it names stand for is a virtual image of a remote physical PDB in another container. Every single statement executed on the proxy PDB are transferred to the remote physical source PDB. For creation I will still use the public database link:

SQL> CREATE pluggable DATABASE pdb2 AS proxy FROM pdb1@orcl2_to_orcl1
     path_prefix = '/u01/app/oracle/oradata/ORCL2/pdb2/'
     file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb2/');
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           READ WRITE NO
         4 PDB1                           READ WRITE NO
SQL> !ls -l /u01/app/oracle/oradata/ORCL2/pdb2
total 798832
-rw-r----- 1 oracle dba 408952832 Jul 13 17:38 sysaux01.dbf
-rw-r----- 1 oracle dba 304095232 Jul 13 17:38 system01.dbf
-rw-r----- 1 oracle dba 104865792 Jul 13 17:31 temp01.dbf
-rw-r----- 1 oracle dba 104865792 Jul 13 17:38 undotbs01.dbf

As we can see all the system datafiles for system tablespaces (SYSTEM, TEMP, UNDOTBS, SYSAUX) are here but the users01.dbf datafile of my USERS tablespace has not been copied.

But I can query and DML my remote table like if I was connected locally. Strangely you cannot go through the changing container method:

SQL> ALTER SESSION SET container=pdb2;
 
SESSION altered.
 
SQL> SELECT * FROM yjaquier.test01;
SELECT * FROM yjaquier.test01
                       *
ERROR AT line 1:
ORA-00376: FILE 44 cannot be read AT this TIME
ORA-01110: data FILE 44: 'No file with this number, file does not exist'
 
SQL> SELECT name FROM v$datafile;
 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL2/pdb2/system01.dbf
/u01/app/oracle/oradata/ORCL2/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/ORCL2/pdb2/undotbs01.dbf

But if you go directly it works. Inserting a row in proxy PDB is immediately reflected in source PDB:

[oracle_orcl2@server01 ~]$ sqlplus yjaquier/"secure_password"@pdb2
 
SQL*Plus: RELEASE 21.0.0.0.0 - Production ON Wed Jul 13 18:00:14 2022
Version 21.6.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  ALL rights reserved.
 
LAST SUCCESSFUL login TIME: Wed Jul 13 2022 15:34:21 +02:00
 
Connected TO:
Oracle DATABASE 21c Enterprise Edition RELEASE 21.0.0.0.0 - Production
Version 21.6.0.0.0
 
SQL> SELECT * FROM test01;
 
        ID DESCR
---------- --------------------------------------------------
         1 One
         2 Two
         3 Three
 
SQL> SELECT name FROM v$datafile;
 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL1/pdb1/system01.dbf
/u01/app/oracle/oradata/ORCL1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/ORCL1/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/ORCL1/pdb1/users01.dbf

Moving pluggable database across containers

multitenant_pdb_move
multitenant_pdb_move

Unplug/plug a PDB

This offline move a PDB from a container to a new one. The structure of the PDB is written donw in an XML file. To simulate a real life activity I will copy the datafiles from one directory to another (we could also imagine re-allocating the external storage to another server).

Unplug the the PDB:

SQL> ALTER pluggable DATABASE pdb1 CLOSE IMMEDIATE;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL> ALTER pluggable DATABASE pdb1 unplug INTO '/tmp/pdb1.xml';
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
SQL> DROP pluggable DATABASE pdb1 keep datafiles;
 
Pluggable DATABASE dropped.

Remark:
You can also use DBMS_PDB.DESCRIBE(pdb_descr_file => ‘/tmp/pdb1.xml’, pdb_name => ‘PDB1’) to generate this XML file.

Then I copy the datafiles at OS level (but again, I’m on one single server, any other more advanced technique is welcome). The XML file must also be transferred to destination server:

[oracle_orcl2@server01 ~]$ cp -rp /u01/app/oracle/oradata/ORCL1/pdb1 /u01/app/oracle/oradata/ORCL2

Before performing the action you can check if your XML file is compatible with the new container:

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/pdb1.xml', pdb_name => 'PDB1')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
 
YES
 
PL/SQL PROCEDURE successfully completed.

Plug the PDB with. I use NOCOPY because the datafiles have been ove at OS level::

SQL> CREATE pluggable DATABASE pdb1 USING '/tmp/pdb1.xml'
     NOCOPY
     source_file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb1/');
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> CONNECT yjaquier/"secure_password"@pdb1
Connected.
SQL> SELECT * FROM test01 ORDER BY id;
 
        ID DESCR
---------- --------------------------------------------------
         1 One
         2 Two
 
SQL> SELECT name FROM v$datafile;
 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL2/pdb1/system01.dbf
/u01/app/oracle/oradata/ORCL2/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/ORCL2/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/ORCL2/pdb1/users01.dbf

Relocating a PDB

This operation is the online version of the unplug/plug operation. As written in official documentation:

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.

You have a minimal downtime to switch the applicative connection to newly created PDB. This downtime can even be made transparent using Application Continuity (https://blog.yannickjaquier.com/oracle/application-continuity-ac-jdbc-ha-part-6.html) or RAC FAN (https://blog.yannickjaquier.com/oracle/fast-application-notification-fan-jdbc-ha-part-3.html) features.

Create the relocated PDB with. Initially the PDB is mounted:

SQL> CREATE pluggable DATABASE pdb2 FROM pdb1@orcl2_to_orcl1
     path_prefix = '/u01/app/oracle/oradata/ORCL2/pdb2/'
     file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdb1/', '/u01/app/oracle/oradata/ORCL2/pdb2/')
     relocate availability normal;
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           READ WRITE NO
         5 PDB2                           MOUNTED
SQL> ALTER pluggable DATABASE pdb2 OPEN;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO

While the copy operation is in progress the master PDB is still accessible and once you open it on the new remote container the original PDB disappear from its container:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDB1                           READ WRITE NO
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>