{"id":6619,"date":"2016-01-02T18:16:43","date_gmt":"2016-01-02T17:16:43","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/"},"modified":"2016-01-02T18:16:43","modified_gmt":"2016-01-02T17:16:43","slug":"pdb-snapshot-using-dbms_dnfs-clonedb_renamefile","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/","title":{"rendered":"PDB Snapshot using dbms_dnfs.clonedb_renamefile"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn the previous post<\/a> I’ve explained how to use ‘snapshot copy’ PDB creation for thin provisioning and quick restore of initial state for continuous integration tests. If you don’t have the multitenant option, you need a remote CDB to do it. Here is a way to do the same on only one single-tenant CDB.
\n
\nThe idea here is to use dbms_dnfs.clonedb_renamefile thatblog-thin-arch-121.txt can do snapshots using sparse files.<\/p>\n

clonedb=true<\/h3>\n

This is required to do thin clones in a regular filesystem:<\/p>\n

\n18:31:09 SQL> show parameter clonedb\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nclonedb                              boolean     TRUE\n<\/code><\/pre>\n
Unplug<\/h3>\n
I’ve a PDB with the state I want to snapshot. I close it and unplug it in order to be able to plug it later. My idea here is to be able to do that in single tenant, so with only one PDB in the CDB.<\/p>\n
\n18:31:09 SQL> alter pluggable database PDB close;\nPluggable database altered.\n<\/code><\/pre>\n
At that point I thought to unplug that, then plug-in and clone. The idea was to have nothing to copy: the clone creates the sparse file without touching the original file, so it can be used later to plug it again. However, the plug-in operation modifies the file headers which means that they cannot be plugged-in again with the same unplug xml file. Currently, I don’t know how to plug-in a read-only set of datafiles.<\/p>\n
clone<\/h3>\n
So, finally I do the clone before unplugging. <\/p>\n
\n18:32:04 SQL> exec dbms_dnfs.clonedb_renamefile('\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf','\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf.cow');\nPL\/SQL procedure successfully completed.\n18:32:04 SQL> exec dbms_dnfs.clonedb_renamefile('\/u02\/app\/oracle\/oradata\/CDB\/PDB\/sysaux01.dbf','\/u02\/app\/oracle\/oradata\/CDB\/PDB\/sysaux01.dbf.cow');\nPL\/SQL procedure successfully completed.\n18:32:04 SQL> exec dbms_dnfs.clonedb_renamefile('\/u02\/app\/oracle\/oradata\/CDB\/PDB\/SAMPLE_SCHEMA_users01.dbf','\/u02\/app\/oracle\/oradata\/CDB\/PDB\/SAMPLE_SCHEMA_users01.dbf.cow');\nPL\/SQL procedure successfully completed.\n18:32:04 SQL> exec dbms_dnfs.clonedb_renamefile('\/u02\/app\/oracle\/oradata\/CDB\/PDB\/example01.dbf','\/u02\/app\/oracle\/oradata\/CDB\/PDB\/example01.dbf.cow');\nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\n
This keeps the original datafiles read only and creates the sparse files (.cow) where only the modified blocks are copied.<\/p>\n
unplug<\/h3>\n
Then the unplug creates the .xml file that references only those .cow ones.<\/p>\n
\n18:33:20 SQL> alter pluggable database PDB unplug into '\/tmp\/pdbcow.xml';\nPluggable database altered.\n<\/code><\/pre>\n
Then as I want to be able to plug-in several times, I have to archive the files to be able to restore them. But here it’s only small files (the .cow ones) as the original ones are not modified anymore.<\/p>\n
\n18:33:32 SQL> host tar -Pcvf \/tmp\/pdbcow.tar \/u02\/app\/oracle\/oradata\/CDB\/PDB\/*.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/example01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/SAMPLE_SCHEMA_users01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/sysaux01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf.cow\n<\/code><\/pre>\n
and you can check that this tar file is small:<\/p>\n
\n18:33:36 SQL> host du -k \/tmp\/pdbcow.tar\n2179880 \/tmp\/pdbcow.tar\n<\/code><\/pre>\n
Now, I have everything to be able to plug-in several times the PDB in order to restore the original state. I can drop it:<\/p>\n
\n18:33:37 SQL> drop pluggable database PDB;\nPluggable database dropped.\n<\/code><\/pre>\n
plug<\/h3>\n
Now I can do the following as many as I want.
\nPlug:<\/p>\n
\n18:33:56 SQL> create pluggable database PDB using '\/tmp\/pdbcow.xml' nocopy;\nPluggable database created.\n<\/code><\/pre>\n
then use it:<\/p>\n
\n18:34:03 SQL> alter session set container=PDB;\nSession altered.\n18:34:03 SQL> startup\nPluggable Database opened.\n18:34:04 SQL> connect hr\/hr@\/\/localhost\/PDB\nConnected.\n<\/code><\/pre>\n
Here I can run my test.
\nAnd when I’ve finished I can drop it:<\/p>\n
\n18:34:04 SQL> connect \/ as sysdba\nConnected.\n18:34:05 SQL> alter session set container=CDB$ROOT;\nSession altered.\n18:34:05 SQL> alter pluggable database PDB close;\nPluggable database altered.\n18:34:06 SQL> drop pluggable database PDB including datafiles;\nPluggable database dropped.\n<\/code><\/pre>\n
I’ve dropped it ‘including datafiles’ because those (the .cow ones) cannot be used anymore. However the original files remain and have not changed.
\nSo the only thing I have to do to start again is restore those small .cow files: <\/p>\n
\n18:34:06 SQL> host tar -Pxvf \/tmp\/pdbcow.tar\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/example01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/SAMPLE_SCHEMA_users01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/sysaux01.dbf.cow\n\/u02\/app\/oracle\/oradata\/CDB\/PDB\/system01.dbf.cow\n18:34:14 SQL>\n<\/code><\/pre>\n
which is very fast, and it’s ready to start again with:<\/p>\n
\ncreate pluggable database PDB using '\/tmp\/pdbcow.xml' nocopy;\n<\/code><\/pre>\n
as above. So you can restore the same saved state in a few seconds, without having to restore the full datafiles.<\/p>\n
Conclusion<\/h3>\n
I show this only for the context of continuous integration testing, as an alternative to rebuild or restore or flashback the database. I would never do that in production because I don’t know exactly how it works. I mean, in order to work, you need to keep the original files. They are referenced in the controlfile so that the processes can read them when the block is not found in the sparse file, but I don’t know the lifecycle of that. The drop commands ignores them. If you trace controlfile to trace, you will not see them. And you don’t see them in v$datafile. So there is a high risk of losing them accidently. And when you drop all PDB that can reference them, they are still there. So how to clean that up?
\nAnyway, for a database build only for automatic tests, it’s a good alternative to snapshot copy if you don’t have multitenant option and don’t want to keep a remote CDB to hold the original datafiles.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In the previous post I’ve explained how to use ‘snapshot copy’ PDB creation for thin provisioning and quick restore of initial state for continuous integration tests. If you don’t have the multitenant option, you need a remote CDB to do it. Here is a way to do the same on only […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[220,64,96,209,66,223,728],"type_dbi":[],"class_list":["post-6619","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-cdb","tag-multitenant","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases","tag-thin-provisioning"],"acf":[],"yoast_head":"\nPDB Snapshot using dbms_dnfs.clonedb_renamefile - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PDB Snapshot using dbms_dnfs.clonedb_renamefile\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In the previous post I’ve explained how to use ‘snapshot copy’ PDB creation for thin provisioning and quick restore of initial state for continuous integration tests. If you don’t have the multitenant option, you need a remote CDB to do it. Here is a way to do the same on only […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-02T17:16:43+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"PDB Snapshot using dbms_dnfs.clonedb_renamefile\",\"datePublished\":\"2016-01-02T17:16:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\"},\"wordCount\":621,\"commentCount\":0,\"keywords\":[\"CDB\",\"multitenant\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\",\"thin provisioning\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\",\"name\":\"PDB Snapshot using dbms_dnfs.clonedb_renamefile - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-01-02T17:16:43+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PDB Snapshot using dbms_dnfs.clonedb_renamefile\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PDB Snapshot using dbms_dnfs.clonedb_renamefile - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/","og_locale":"en_US","og_type":"article","og_title":"PDB Snapshot using dbms_dnfs.clonedb_renamefile","og_description":"By Franck Pachot . In the previous post I’ve explained how to use ‘snapshot copy’ PDB creation for thin provisioning and quick restore of initial state for continuous integration tests. If you don’t have the multitenant option, you need a remote CDB to do it. Here is a way to do the same on only […]","og_url":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/","og_site_name":"dbi Blog","article_published_time":"2016-01-02T17:16:43+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"PDB Snapshot using dbms_dnfs.clonedb_renamefile","datePublished":"2016-01-02T17:16:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/"},"wordCount":621,"commentCount":0,"keywords":["CDB","multitenant","Oracle","Oracle 12c","PDB","Pluggable Databases","thin provisioning"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/","url":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/","name":"PDB Snapshot using dbms_dnfs.clonedb_renamefile - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-01-02T17:16:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pdb-snapshot-using-dbms_dnfs-clonedb_renamefile\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PDB Snapshot using dbms_dnfs.clonedb_renamefile"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6619"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6619\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6619"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}