Oracle 12c PDB snapshot copy on regular filesystem

A month ago I was at Paris Oracle meetup with 4 great presentations from 2 cool Swiss guys that I knew from their really good blogs : http://www.meetup.com/parisoracle/events/224181879/
– Ludovico Caldara
– Franck Pachot

After the first presentation by Ludovico Caldara about “Oracle ACFS and Oracle Database: the perfect match ?”, I almost ruined the presentation when I said that you don’t need ACFS, Direct-NFS (CloneDB) or other Copy-On-Write filesystem to do this as it is available on the latest Oracle 12c.. working on “conventional” filesystem.

So I promise to write about it.. It is a quite simple post, this is just to “advertise” the feature. I didn’t had the time to look more into it when I discover it. But I am sure, some people will look more at it.

Test : Oracle 12.1.0.2 on RedHat 6.6 (filesystem ext4).
Pre-requisite : CLONEDB initialization parameter is set to TRUE

@pdb
Show PDB information

 CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ---------- ---------- -------- ----------------------
 2 3587124922 3587124922 0ED34F67D0F81298E053ED22070A751B PDB$SEED READ ONLY NO 30-APR-15 03.41.46.723 PM +02:00 1594330 838860800 8192 ENABLED 0
 3 2362709155 2362709155 0ED37F0B74A72742E053ED22070A2DCA DB12C_PDB1 READ WRITE NO 30-APR-15 03.41.47.153 PM +02:00 1792203 2116812800 8192 ENABLED 0
 

Let’s use the PDB called DB12C_PDB1, and create a snapshot from it.

First get a consistent (Closed) PDB to use a source. Then re-opened it as Read-Only.

SQL> alter pluggable database exit close; 

SQL> alter pluggable database DB12C_PDB1 open read only; 

Now, let’s create the directory that will host the sparsed files for the snapshot copies PDB.


SQL> ! mkdir -p /u01/app/oracle/oradata/DB12C/DB12C_JCD1

Here we go, we create the snapshot copy !


SQL> create pluggable database DB12C_JCD1 from DB12C_PDB1 snapshot copy file_name_convert=('/u01/app/oracle/oradata/DB12C/DB12C_PDB1','/u01/app/oracle/oradata/DB12C/DB12C_JCD1');

Pluggable database created.

Then we open the target PDB.

SQL> alter pluggable database DB12C_JCD1 open;

Pluggable database altered.

SQL> alter session set container=DB12C_JCD1;

Now let’s check the datafiles on the ext4 filesystem and check the space actually used (sparse files).

SQL> @datafile %

Show Tablespace information for TBS matching %%%

 ID# filename                                                                   Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr
---- -------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ----------
  81 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA01.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  82 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA02.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  83 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA03.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  84 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES01.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  85 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES02.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  74 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/sysaux01.dbf                      SYSAUX                      580        543         36      32767      74240          1 A          Y             1280
  73 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/system01.dbf                      SYSTEM                      410        405          4      32767      52480          2 A          Y             1280
  75 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/DB12C_PDB1_users01.dbf            USERS                         8          7          1      32767       1120          2 A          Y              160

13 rows selected.

Show Temporary Tablespace information for TBS matching %%%

 ID# filename                                                                         Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr Used
---- -------------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ---------- ------------------------------
   4 /u01/app/oracle/oradata/DB12C/DB12C_JCD1/pdbseed_temp01DB12C.dbf        TEMP                         20         20          0      32767       2560          1 ONLINE     Y               80 |####################|

Let’s check the “actual” space used on the filesystem using “du”. You’ll notice that only a few kilobytes are used on the filesystem. Of course, it will increase as you modify your snapshot PDB !

SQL> ! du -k /u01/app/oracle/oradata/DB12C/DB12C_JCD1/*

16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA01.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA02.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPDATA03.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES01.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/APPINDEXES02.DBF
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/DB12C_PDB1_users01.dbf
56      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/pdbseed_temp01DB12C.dbf
16      /u01/app/oracle/oradata/DB12C/DB12C_JCD1/sysaux01.dbf
172     /u01/app/oracle/oradata/DB12C/DB12C_JCD1/system01.dbf

Now let’s check the real space used on the original PDB.

SQL> alter session set container=DB12C_PDB1;

SQL> @datafile %

Show Tablespace information for TBS matching %%%

 ID# filename                                                                   Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr
---- -------------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ----------
  16 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA01.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  17 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA02.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  18 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA03.DBF                     APPDATA                     100          1         99      32767      12800          1 A          Y            65536
  19 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES01.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
  20 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES02.DBF                  APPINDEXES                  100          1         99      32767      12800          1 A          Y            65536
   9 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/sysaux01.dbf                      SYSAUX                      580        543         36      32767      74240          1 A          Y             1280
   8 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/system01.dbf                      SYSTEM                      410        405          4      32767      52480          2 A          Y             1280
  10 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/DB12C_PDB1_users01.dbf            USERS                         8          7          1      32767       1120          2 A          Y              160

13 rows selected.

Show Temporary Tablespace information for TBS matching %%%

 ID# filename                                                                Tablespace              TotalMB     UsedMB     FreeMB  MaxsizeMB     Blocks    Extents Status     AutoExt       Incr Used
---- ----------------------------------------------------------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ---------- ------------------------------
   3 /u01/app/oracle/oradata/DB12C/DB12C_PDB1/pdbseed_temp01DB12C.dbf        TEMP                         20         20          0      32767       2560          1 OFFLINE    Y               80 |####################|

And on the filesystem.

! du -k /u01/app/oracle/oradata/DB12C/DB12C_PDB1/*
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA01.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA02.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPDATA03.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES01.DBF
102412  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/APPINDEXES02.DBF
8968    /u01/app/oracle/oradata/DB12C/DB12C_PDB1/DB12C_PDB1_users01.dbf
1024    /u01/app/oracle/oradata/DB12C/DB12C_PDB1/pdbseed_temp01DB12C.dbf
593932  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/sysaux01.dbf
419852  /u01/app/oracle/oradata/DB12C/DB12C_PDB1/system01.dbf

That’s it for the demo, really simple. I am sure Ludovico Caldara will be eager to investigate more on the internals of this nice feature that not many people seem to know about.

Hope this helps.