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.

Advertisements

6 thoughts on “Oracle 12c PDB snapshot copy on regular filesystem

  1. I don’t think it’s that easy, you would need Multitenant add-on licensing option (licensed per core).
    As per https://docs.oracle.com/database/121/DBLIC/options.htm#DBLIC2166 “The multitenant architecture with one user-created pluggable database (single tenant) is available in all editions without the Multitenant Option.”.

    While according to the https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109 for ACFS even with snapshots you “only” require EE.

    Of course please correct me if I’m wrong because those are pretty bleeding edge new features. I would be more than happy to listen real world use cases.

  2. The aim of this post is to show it is possible to use this snapshot technology on “regular filesystem”.
    Of course, you need to take care of the license. One common usage is to be able to have N PDBs for developpers to work on production-like database without needing N x space used by production.

  3. Great Blog! I wasn’t yet aware of the fact the snapshot clones work without special filesystem (ACFS or NFS). Unfortunately while testing it I encountered that RMAN backup isn’t aware of the snapshot clones. So all PDBs have the same backup size 😦

  4. Hi Jean-Christophe, this is a great article. Have you seen any reference that would say whether this is supported by Oracle or not?
    Thanks,
    Stephen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s