By Franck Pachot

.
How do you manage your continuous integration tests on the database? You need to restart at the same state for probably a hundred of tests. Recreating the schema and test data takes too long. Oracle is not optimized for DDL. Restoring the database takes too long. Even if you data is small, there is those SYSTEM, SYSAUX tablespaces. Flashback database can help, but it still takes time because it requires to restart the instance.
Let’s see how multitenant can help here.

Flashback database

The flashback database solution is a good solution, and I’ve implemented that for continuous integration tests. It seems that having thousands of incarnations is not a problem (at least for a test database) and in current versions I’ve not seen many bugs where flashback logs or archived logs remain without becoming obsolete. But there is a problem: you need to close the database, which means that you need to restart the instance to open it again. This takes time. There are a lot of processes in current versions and linux is not optimized for process creation.

Alter database close

But now multitenant is there. 12c Multitenant is a major change of oracle architecture. If you don’t share my opinion about it, just look at the first paragraph of Tom Kyte’s ‘Expert Oracle’ about architecture overview: An instance can mount and open at most one database in its life. This statement is not true anymore with multitenant: you can re-open a closed pluggable database very quickly, without having to restart the instance.
This makes flashback database a very nice solution for our problem, as it can be done in few seconds, but for the moment, in 12.1, the problem is that you cannot flashback database at PDB level. So we need a workaround.

Thin provisioning

Here is the most simple workaround: once you have your reference PDB with your test data in the state you want at the beginning of each tests, you can make it read only, and then create new PDB from it for the tests. However, I don’t want to copy the SYSTEM and SYSAUX tablespaces each time, so I’ll use thin provisioning.

Snapshot copy

Here is the simple command to do that:


SQL> create pluggable database PDBSNAP from PDB snapshot copy file_name_convert=('/PDB/','/PDBSNAP/');

but you need a storage that allow thin provisioning to use that or you get the following error:


create pluggable database PDBSNAP from PDB snapshot copy file_name_convert=('/PDB/','/PDBSNAP/')
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
/u02/app/oracle/oradata/CDB/PDB/system01.dbf
ORA-17525: Database clone using storage snapshot not supported on file
/u02/app/oracle/oradata/CDB/PDB/system01.dbf

The solution is:


$ oerr ora 17525
17525, 00000, "Database clone using storage snapshot not supported on file %s"
// *Cause:  Cloning a database using storage level snapshot is not supported on the underlying storage.
// *Action: Use storage product that has snapshot based cloning support for Oracle.

CLONEDB=true

But you can do it also without any special storage as long as your filesystem supports sparse files. Then, the original files will not be touched and only the modified blocks will be written in the new files (it’s copy-on-write). Which means two things:

  • that the PDB creation is very fast as there is nothing to copy
  • that once dropped, the base files are available to create a new one

In order to use that, you need to set the instance parameter clonedb=true (needs restart):


SQL> alter system set clonedb=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
SQL> startup
ORACLE instance started
...
Database mounted.
Database opened.
 
SQL> show parameter clonedb
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
clonedb                              boolean     TRUE

and then I can thin clone my reference PDB in few seconds:


17:16:21 SQL> create pluggable database PDBSNAP from PDB snapshot copy file_name_convert=('/PDB/','/PDBSNAP/');
Pluggable database created.
17:16:31 SQL>

and if you look at the files you see that the new ones are very small:


17:16:31 SQL> host  du -ka /u02/app/oracle/oradata/CDB/PDB*/*
276488  /u02/app/oracle/oradata/CDB/PDB/system01.dbf
614412  /u02/app/oracle/oradata/CDB/PDB/sysaux01.dbf
1273612 /u02/app/oracle/oradata/CDB/PDB/example01.dbf
15368   /u02/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf
172     /u02/app/oracle/oradata/CDB/PDBSNAP/system01.dbf
16      /u02/app/oracle/oradata/CDB/PDBSNAP/sysaux01.dbf
16      /u02/app/oracle/oradata/CDB/PDBSNAP/example01.dbf
16      /u02/app/oracle/oradata/CDB/PDBSNAP/SAMPLE_SCHEMA_users01.dbf

16k (two blocks) only are different for the application datafiles. SYSTEM had a few more change, but it’s only few blocks here.
It took 10 seconds here on 1GB datafiles and it will still take 10 seconds if you have 100GB. That makes the thin provisioning very fast.

Then you open it and can use it in few seconds:


17:16:31 SQL> alter pluggable database PDBSNAP open;
Pluggable database altered.
 
17:16:34 SQL> connect hr/hr@//localhost/PDBSNAP
Connected.

Do everything you want here and then drop it, which takes only few seconds again.


17:16:35 SQL> connect / as sysdba
Connected.
17:16:35 SQL> alter pluggable database PDBSNAP close;
Pluggable database altered.
17:16:36 SQL> drop pluggable database PDBSNAP including datafiles;
Pluggable database dropped.
17:16:37 SQL>

You can see that I’ve dropped it ‘including datafiles’ and it drops only the sparse files:


17:16:37 SQL> host  du -ka /u02/app/oracle/oradata/CDB/PDB*/*
276488  /u02/app/oracle/oradata/CDB/PDB/system01.dbf
614412  /u02/app/oracle/oradata/CDB/PDB/sysaux01.dbf
1273612 /u02/app/oracle/oradata/CDB/PDB/example01.dbf
15368   /u02/app/oracle/oradata/CDB/PDB/SAMPLE_SCHEMA_users01.dbf

The files from my reference PDB are still there, ready for another provisioning.

Expensive option?

CREATE PLUGGABLE DATABASE … SNAPSHOT COPY is a good alternative to flashback database here. However, keep in mind that you need to have more than one PDB in your CDB which means that you need to have multitenant option for that. You may find that expensive, but think about what it can bring you: you can provision multiple test environments and run your test in parallel, you same lot of storage, you can provision one database for each developer without a big overhead,…

Cloud

The Oracle cloud service is a good solution for those environments. You probably don’t need the same number of databases for the whole application lifecycle. In the cloud you can provision those environments when a project needs it. And the cost of additional options is not very high there: the ‘high performance’ service price is 30% higher than the ‘enterprise edition’ one and brings nearly all options (except In-Memory and Active Data Guard). But take care: if you give that environment to your developers, they may use a lot of options that you don’t want to buy for production. Keep an eye on DBA_FEATURE_USAGE_STATISTICS.

single-tenant

If you don’t want to pay for the multitenant option yet you can have only one pluggable database (in addition to seed). And you can do snapshot copy from remote CDB through db link, so you keep one PDB per CDB. It seems to take a few more seconds (in ‘remote operation’ and ‘external table read’ to read opatch inventory from the remote database).

Even without multitenant option, not having to re-start the instance, and to possibility to easily use thin provisioning is a very good reason to start to look at pluggable databases.

Update

In a new blog post I’ve detailed another solution in single-tenant with only one instance, using dbms_dnfs.clonedb_renamefile: https://www.dbi-services.com/blog/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile/