Multitenant Snapshot Copy

Snapshot clones are available with various techniques, like clonedb with 11.2.0.3 or the already mentioned software company Delphix. The most common ones are using storage technologies like ZFS or NetApp. Starting with Oracle 12.1.0.1 it was also possible using snapshot clones with pluggable databases within the multitenant database architecture. But it was limited to file systems that support sparseness and ACFS.

With the first patch set 12.1.0.2 it’s possible to use direct NFS as well for snapshot copies. As direct NFS becomes more and more popular for database storage this is a real shift towards the usage of snapshot copies.

Direct NFS

Direct NFS has been discussed and described in detail in the blog from my colleage Sebastian Winkler https://www.carajandb.com/en/blogs/blog-swinkler-en/178-oracle-direct-nfs-configuration-using-linux-or-windows-with-11g-and-12c

Please checkout that blog for further information about Direct NFS.

Keystore

Before snapshot copies can be created it is required to setup a keystore with the password of the nfs server. Advanced security is not my personal favorite so I apologize if this is not the best practice setup for a keystore but it works. The location of the keystore must be specified in sqlnet.ora like in the following example:

% cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
   (SOURCE=
      (METHOD=FILE)
      (METHOD_DATA=
         (DIRECTORY=/home/oracle)))

Now a keystore for all kind of keys can be created in the directory /home/oracle.

SQL> ADMINISTER KEY MANAGEMENT
     CREATE KEYSTORE '/home/oracle/' IDENTIFIED BY oracle;

The password should be a little more secure as shown above!

Next the keystore can be opened to store a password for the NFS Server:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
     IDENTIFIED BY oracle CONTAINER=ALL;
SQL> ADMINISTER KEY MANAGEMENT
     ADD SECRET 'manager' FOR CLIENT 'synology1'
     USING TAG 'oracle' IDENTIFIED BY oracle WITH BACKUP;

Again it should be obvious to use more complex passwords!

Snapshot Copy

One last change has to be done before a snapshot copy can be created: the database instance must be enabled for clones:

SQL> ALTER SYSTEM SET clonedb=true SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

The pluggable database which acts as the source of our clone must resist on the same store (so direct NFS in our case) as the snapshot clone. So we need to create a pluggable database on the new NFS storage first and this has to be opened read only at any time as long as there is at minimum one pluggable database relying on it.

With 12.1.0.1 it was necessary to create an “On Startup Trigger” to ensure that the PDB will be started in read only mode automatically. With 12.1.0.2 a new feature named “PDB State Management across CDB Restart” will allow individual settings for PDBs.

Another new feature in 12.1.0.2 is the ability to specify an individual path (CREATE_FILE_DEST) for the pluggable database while using Oracle Managed Files (OMF). Please don’t mix up CREATE_FILE_DEST with DB_CREATE_FILE_DEST. The first one is an optional parameter with the CREATE PLUGGABLE DATABASE command while the other one is a server parameter for the general location of data files when using OMF.

SQL> CREATE PLUGGABLE DATABASE sonate1
      ADMIN USER pdb_admin IDENTIFIED BY manager
      CREATE_FILE_DEST='/u02/oranfs/JOSEPH/SONATE1';
SQL> ALTER PLUGGABLE DATABASE sonate1 OPEN;
SQL> ALTER PLUGGABLE DATABASE sonate1 OPEN READ ONLY FORCE;
SQL> ALTER PLUGGABLE DATABASE sonate1 SAVE STATE;

The directory “SONATE1” must exist before creating the pluggable database.

And now it’s Show Time:

SQL> CREATE PLUGGABLE DATABASE sonatine
       FROM sonate1 CREATE_FILE_DEST='/u02/oranfs/JOSEPH/SONATINE' 
       SNAPSHOT COPY;
SQL> ALTER PLUGGABLE DATABASE sonatine OPEN;

Verification

If we look into the database there is no difference to an ordinary PDB:

The size of the data files for PDB 7 and 8 are the same. If we look at the operating system level it looks similar:

$ ls -l SONATE1/JOSEPH/00AB52D5EA461844E053191E10ACC948/datafile/
total 819276
-rwxrwxrwx 1 oracle oinstall 576724992 Aug 15 14:43 o1_mf_sysaux_9yvzxzcq_.dbf
-rwxrwxrwx 1 oracle oinstall 262152192 Aug 15 14:43 o1_mf_system_9yvzxzc8_.dbf
-rwxrwxrwx 1 oracle oinstall  20979712 Aug 15 14:40 o1_mf_temp_9yvzxzd1_.dbf

$ ls -l SONATINE/JOSEPH/00AB52D5EA481844E053191E10ACC948/datafile/
total 196
-rwxrwxrwx 1 oracle oinstall 576724992 Aug 15 14:45 o1_mf_sysaux_9yw037m1_.dbf
-rwxrwxrwx 1 oracle oinstall 262152192 Aug 15 14:45 o1_mf_system_9yw037k6_.dbf
-rwxrwxrwx 1 oracle oinstall  20979712 Aug 15 14:43 o1_mf_temp_9yw037ns_.dbf

So is this really using copy on write?

UNIX “du” command gives the answer:

$ du -sh *
801M SONATE1
952K SONATINE

This result shows that the PDB SONATINE is only occupying 1 MB on storage while the original PDB SONATE1 is using 800 MB.

Conclusion

Multitenant Database with direct NFS is the next step towards a DBaaS offering. It’s now very easy to create even tenths of clones of identical database for test or development purpose without large amounts of storage and without the need for a specific storage vendor.