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…
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
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
