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.
