Ilmar Kerm

Oracle, databases, Linux and maybe more

When cloning pluggable databases Oracle gives you also SNAPSHOT COPY clause to utilize storage system snapshot capabilities to save on storage space. But it works only when datafiles are placed on ACFS, ZFS Appliance over DNFS or Netapp (Oracle Multitenant Pluggable Database Snapshot Cloning: Use Cases and Supported Platforms Doc ID 1597027.1) and you also cannot unplug/drop the source PDB and you also cannot unplug the clone PDB.

Recently I’ve started to like BTRFS (only on test systems!) and it also has builting snapshot capabilities. In this article I’ll try to use BTRFS snapshots to create pluggable database clones. It is not as automated as Oracle builtin snapshots, but possible.

What I have before cloning:

SQL> select name, open_mode, guid from v$pdbs;

NAME                           OPEN_MODE  GUID
------------------------------ ---------- --------------------------------
PDB$SEED                       READ ONLY  FFCDD773320B2767E0432700FA0A06D0
DB1                            MOUNTED    FFCC6C3F473E124BE0432700FA0A429E
DB2                            READ WRITE 000A7D190B3C15A1E0532700FA0A3C10

SQL> alter session set container=db2;

Session altered.

SQL> select round(bytes/1024/1024) mb, file_name from dba_data_files;

        MB FILE_NAME
---------- -------------
       280 /u01/db2/system01.dbf
       690 /u01/db2/sysaux01.dbf
         5 /u01/db2/users01.dbf
       100 /u01/db2/tdata01.dbf
      2048 /u01/db2/tlarge01.dbf

My goal is to clone DB2 to new PDB called DB3. Currently my DB2 is stored under /u01/db2/ that is a dedicated subvolume under BTRFS filesystem /u01. The file system is currently using 21.23GB.

[root@cdbtest u01]# btrfs subvolume list -a -t /u01
ID      gen     top level       path
--      ---     ---------       ----
256     1543    5               app
281     736     5               app_bak
285     1545    5               data
286     1297    5               data_bak
287     1544    5               db2
289     1333    5               db2_bak

[root@cdbtest u01]# ls -l /u01/db2
total 3198048
-rw-r----- 1 oracle oinstall  723525632 Aug  7 17:40 sysaux01.dbf
-rw-r----- 1 oracle oinstall  293609472 Aug  7 17:42 system01.dbf
-rw-r----- 1 oracle oinstall  104865792 Aug  7 17:21 tdata01.dbf
-rw-r----- 1 oracle oinstall   20979712 Aug  7 16:56 temp01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Aug  7 17:21 tlarge01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 17:21 users01.dbf

[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=21.23GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=124.73MB
Metadata: total=8.00MB, used=0.00

Now, lets start. First need to open DB2 read only and create description XML file from it:

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/u01/db2_pdb.xml');

PL/SQL procedure successfully completed.

And create a BTRFS snapshot under directory /u01/db3

[root@cdbtest u01]# btrfs subvolume snapshot db2 db3
Create a snapshot of 'db2' in './db3'

[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=21.23GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=124.75MB
Metadata: total=8.00MB, used=0.00

[root@cdbtest u01]# ls -l
total 8
drwxr-xr-x 1 oracle oinstall   50 Aug  4 11:55 app
drwxr-xr-x 1 oracle oinstall   50 Aug  4 11:55 app_bak
drwxrwxr-x 1 oracle oinstall   60 Aug  5 15:16 data
drwxrwxr-x 1 oracle oinstall   60 Aug  5 15:16 data_bak
drwxr-xr-x 1 oracle oinstall  136 Aug  7 17:53 db2
drwxr-xr-x 1 oracle oinstall  112 Aug  7 15:47 db2_bak
-rw-r--r-- 1 oracle oinstall 6370 Aug  7 17:53 db2_pdb.xml
drwxr-xr-x 1 oracle oinstall  136 Aug  7 17:53 db3

[root@cdbtest u01]# ls -l db3
total 3198048
-rw-r----- 1 oracle oinstall  723525632 Aug  7 17:49 sysaux01.dbf
-rw-r----- 1 oracle oinstall  293609472 Aug  7 17:49 system01.dbf
-rw-r----- 1 oracle oinstall  104865792 Aug  7 17:49 tdata01.dbf
-rw-r----- 1 oracle oinstall   20979712 Aug  7 16:56 temp01.dbf
-rw-r----- 1 oracle oinstall 2147491840 Aug  7 17:49 tlarge01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug  7 17:49 users01.dbf

I have a snapshot now and no additional space used! 🙂 Lets try to connect it as a new PDB.

SQL> alter session set container=cdb$root;

Session altered.

SQL> create pluggable database db3 using '/u01/db2_pdb.xml' source_file_name_convert=('/u01/db2/','/u01/db3/') nocopy tempfile reuse;
create pluggable database db3 using '/u01/db2_pdb.xml' source_file_name_convert=('/u01/db2/','/u01/db3/') nocopy tempfile reuse
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

Simple plug in does not work, since the database already has a PDB with the same GUID. Create pluggable database command has a AS CLONE clause to generate new GUID and DBID values.

SQL> create pluggable database db3 AS CLONE using '/u01/db2_pdb.xml' source_file_name_convert=('/u01/db2/','/u01/db3/') nocopy tempfile reuse;

Pluggable database created.

SQL> select name, open_mode, guid from v$pdbs;

NAME                           OPEN_MODE  GUID
------------------------------ ---------- --------------------------------
PDB$SEED                       READ ONLY  FFCDD773320B2767E0432700FA0A06D0
DB1                            MOUNTED    FFCC6C3F473E124BE0432700FA0A429E
DB2                            READ ONLY  000A7D190B3C15A1E0532700FA0A3C10
DB3                            MOUNTED    000C54AE44352331E0532700FA0AFA69

SQL> alter session set container=db3;

Session altered.

SQL> select name from v$datafile;

NAME
----------------------------------------------------
/u01/data/CDB2/datafile/o1_mf_undotbs1_9xyyw5dc_.dbf
/u01/db3/system01.dbf
/u01/db3/sysaux01.dbf
/u01/db3/users01.dbf
/u01/db3/tdata01.dbf
/u01/db3/tlarge01.dbf

6 rows selected.

Looks good from the DB side (ignore the UNDO datafile, this is CDB undo datafile, since PDB does not have its own). How does the storage usage look?

[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=21.23GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=124.87MB
Metadata: total=8.00MB, used=0.00

Still 21.23GB. Lets open both DB2 and DB3 and see what happens.

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database db2 close;

Pluggable database altered.

SQL> alter pluggable database db2 open;

Pluggable database altered.

SQL> alter pluggable database db3 open;

Pluggable database altered.

SQL> select name, open_mode, guid from v$pdbs;

NAME                           OPEN_MODE  GUID
------------------------------ ---------- --------------------------------
PDB$SEED                       READ ONLY  FFCDD773320B2767E0432700FA0A06D0
DB1                            MOUNTED    FFCC6C3F473E124BE0432700FA0A429E
DB2                            READ WRITE 000A7D190B3C15A1E0532700FA0A3C10
DB3                            READ WRITE 000C54AE44352331E0532700FA0AFA69
[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=21.23GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=124.90MB
Metadata: total=8.00MB, used=0.00

Still no additional storage used by the snapshot. I’ll modify some data in DB3 tables, then it must consume additional space.

SQL> alter session set container=db3;

Session altered.

SQL> select count(*) from t1.large;

  COUNT(*)
----------
   1897686

SQL> desc t1.large
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(128)
 OBJECT_NAME                                           NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                                 VARCHAR2(128)
 OBJECT_ID                                             NOT NULL NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(23)
 CREATED                                               NOT NULL DATE
 LAST_DDL_TIME                                         NOT NULL DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
 NAMESPACE                                             NOT NULL NUMBER
 EDITION_NAME                                                   VARCHAR2(128)
 SHARING                                                        VARCHAR2(13)
 EDITIONABLE                                                    VARCHAR2(1)
 ORACLE_MAINTAINED                                              VARCHAR2(1)

SQL> update t1.large set owner = substr(owner, 1, 2);

1897686 rows updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select round(bytes/1024/1024) mb from dba_segments where owner='T1' and segment_name = 'LARGE';

        MB
----------
       256
[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=21.78GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=130.33MB
Metadata: total=8.00MB, used=0.00

I tried to change every block on that table. Table is 256MB and 21.78-21.23=0.55GB was changed on the disk. Just for a test, lets try the update again (just to see how BTRFS snapshots work a little).

SQL> update t1.large set owner = 'AX';

1897686 rows updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.
[root@cdbtest u01]# btrfs filesystem df /u01
Data: total=29.01GB, used=22.00GB
System, DUP: total=8.00MB, used=4.00KB
System: total=4.00MB, used=0.00
Metadata, DUP: total=1.00GB, used=132.34MB
Metadata: total=8.00MB, used=0.00

I should have modified the same blocks, but BTRFS used another 0.22GB of disk space. Need to look more into how BTRFS snapshots work. But here you have it, PDB snapshot cloning without restrictions 🙂