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.
ORAganism 
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.
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.
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 😦
Ok, thanks for the feedback.
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
Hello, Sorry for the delay, I think it should be supported as it is from the documentation :
https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686
When you use the SNAPSHOT COPY clause to create a clone of a source PDB and the CLONEDB initialization parameter is set to TRUE, the underlying file system for the source PDB’s files can be any local file system, network file system (NFS), or clustered file system that has Direct NFS enabled. However, the source PDB must remain in open read-only mode as long as any clones exist.
Pingback: Snapshot cloning and physical IO | dmitry remizov's weblog