{"id":6618,"date":"2016-01-01T20:17:45","date_gmt":"2016-01-01T19:17:45","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-copy-for-continuous-integration-testing\/"},"modified":"2016-01-01T20:17:45","modified_gmt":"2016-01-01T19:17:45","slug":"pdb-snapshot-copy-for-continuous-integration-testing","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-copy-for-continuous-integration-testing\/","title":{"rendered":"PDB snapshot copy for continuous integration testing"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nHow 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.
\nLet’s see how multitenant can help here.
\n<\/p>\n

Flashback database<\/h3>\n

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.<\/p>\n

Alter database close<\/h3>\n

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<\/i>. This statement is not true anymore with multitenant: you can re-open a closed pluggable database very quickly, without having to restart the instance.
\nThis 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.<\/p>\n

Thin provisioning<\/h3>\n

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.<\/p>\n

Snapshot copy<\/h3>\n

Here is the simple command to do that:<\/p>\n

\nSQL> create pluggable database PDBSNAP from PDB snapshot copy file_name_convert=('\/PDB\/','\/PDBSNAP\/');\n<\/code><\/pre>\n
but you need a storage that allow thin provisioning to use that or you get the following error:<\/p>\n
\ncreate pluggable database PDBSNAP from PDB snapshot copy file_name_convert=('\/PDB\/','\/PDBSNAP\/')\n*\nERROR at line 1:\nORA-65169: error encountered while attempting to copy file\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf\nORA-17525: Database clone using storage snapshot not supported on file\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf\n<\/code><\/pre>\n
The solution is:<\/p>\n
\n$ oerr ora 17525\n17525, 00000, \"Database clone using storage snapshot not supported on file %s\"\n\/\/ *Cause:  Cloning a database using storage level snapshot is not supported on the underlying storage.\n\/\/ *Action: Use storage product that has snapshot based cloning support for Oracle.\n<\/code><\/pre>\n
CLONEDB=true<\/h3>\n
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:<\/p>\n