{"id":698,"date":"2024-03-23T16:16:52","date_gmt":"2024-03-23T15:16:52","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=698"},"modified":"2024-03-23T16:16:52","modified_gmt":"2024-03-23T15:16:52","slug":"efficient-test-database-refresh-using-data-guard-snapshot-standby-multitenant-and-storage-snapshots","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2024\/03\/efficient-test-database-refresh-using-data-guard-snapshot-standby-multitenant-and-storage-snapshots\/","title":{"rendered":"Efficient test database refresh using Data Guard Snapshot Standby, Multitenant and storage snapshots"},"content":{"rendered":"\n<p>There are many different ways to refresh test database with production data. Here I&#8217;m going to describe another one, that might be more on the complex side, but the basic requirements I had are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data must be cleaned before opening it up for testing. Cleaning process is extensive and usually takes a few days to run.<\/li>\n\n\n\n<li>Switching testing from old copy to a new copy must be relatively fast. Also if problems are discovered, we should be able to switch back to the old copy relatively fast.<\/li>\n\n\n\n<li>Databases are large, hundreds of terabytes, so any kind of data copy over network (like PDB cloning over database link) would take unacceptable amount of time and resources.<\/li>\n\n\n\n<li>There is no network connection between test database network and production database network, so PDB cloning over database link is not possible anyway.<\/li>\n\n\n\n<li>The system should work also with databases that have a Data Guard standby with Fast-Start Failover configured.<\/li>\n<\/ul>\n\n\n\n<p>Any kind of copying the data will not work, since the databases are really large &#8211; so some kind of storage level snapshot is needed &#8211; and we can use a modern NAS system (that databases utilise over NFS) that can do snapshots and thin clones. Although not in multiple layers.<\/p>\n\n\n\n<p>Here is one attempt to draw out the system. With a more complicated case where the test database actually has two database instances, in Data Guard Fast-Start Failover mode.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"415\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438-1024x415.png\" alt=\"\" class=\"wp-image-700\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438-1024x415.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438-300x122.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438-768x312.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438-1536x623.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2024\/03\/Untitled-2024-03-23-1438.png 1649w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Basically:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>There is a special Data Guard standby for the cleaning process. This standby is using an external NAS storage for storing all PDB datafiles and tempfiles. No CDB files, like CDB datafiles, online logs, standby logs or recovery area should be stored in that NAS folder &#8211; keep CDB files separate.<\/li>\n\n\n\n<li>To start the cleaning process, put the cleaning standby in Snapshot Standby mode (<a href=\"https:\/\/ilmarkerm.eu\/blog\/2024\/02\/snapshot-standby\/\" data-type=\"post\" data-id=\"686\">instructions are here<\/a>) and run all cleaning processes and scripts on the snapshot standby. After cleaning is finished successfully, create PDB metadata XML files and shut down the snapshot standby.<\/li>\n\n\n\n<li>Create storage level snapshot of the NAS folder storing the PDB files.<\/li>\n\n\n\n<li>Restore Snapshot Standby back to being a normal physical standby.<\/li>\n\n\n\n<li>Create writable clone from the same storage snapshot for the test database. If the test database consists of more Data Guard databases, then create a clone (from the same snapshot) for each one of them and mount them to the test database hosts.<\/li>\n\n\n\n<li>On the test database create a new pluggable database as clone (using NOCOPY to avoid any data movement). In case of Data Guard, do not forget to set standby_pdb_source_file_directory so the standby database would know where to find the data files of the plugged in database.<\/li>\n<\/ul>\n\n\n\n<p>Done. Currently both old and new copy are attached to the same test CDB as independent PDBs, when you are happy with the new copy, just direct testing traffic to the new PDB. Then old one is not needed, drop it and clean up the filesystems.<\/p>\n\n\n\n<p>Sounds maybe too complex, but the whole point is to avoid moving around large amounts of data. And key part of support rapid switching to the new copy and supporting Data Guard setups (on the test environment side) is keeping the test database CDB intact.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The steps<\/h2>\n\n\n\n<p>I have a post about using Snapshot Standby, <a href=\"https:\/\/ilmarkerm.eu\/blog\/2024\/02\/snapshot-standby\/\" data-type=\"post\" data-id=\"686\">you can read it here<\/a>. All the code below is for the experts&#8230;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The preparation<\/h2>\n\n\n\n<p>Big part of the system is preparing the Cleaning DB that will be used for Snapshot Database.<\/p>\n\n\n\n<p>Here I use the following paths:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\/oradata &#8211; Location for standby database CDB files, online logs, standby logs, recovery area<\/li>\n\n\n\n<li>\/nfs\/pdb &#8211; NFS folder from external NAS, that will ONLY store all PDB datafiles and tempfiles<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># Parameters\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ndb_create_file_dest                  string      \/nfs\/pdb\ndb_create_online_log_dest_1          string      \/oradata\ndb_recovery_file_dest                string      \/oradata\ndb_recovery_file_dest_size           big integer 10T\n\n# Datafiles\n    CON_ID NAME\n---------- --------------------------------------------------------------------------------------------------------------------------\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_audit_tb__0d68332o_.dbf\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_sysaux__0d5fjj5y_.dbf\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_system__0d4zdst4_.dbf\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_undotbs1__0d5yw58n_.dbf\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_users__0d65hljg_.dbf\n         2 \/oradata\/PROD1MT101CL\/C24DD111D3215498E0536105500AAC63\/datafile\/data_D-WI93OP21_I-1717445842_TS-SYSAUX_FNO-4_q92k3val\n         2 \/oradata\/PROD1MT101CL\/C24DD111D3215498E0536105500AAC63\/datafile\/data_D-WI93OP21_I-1717445842_TS-SYSTEM_FNO-2_p32k3v89\n         2 \/oradata\/PROD1MT101CL\/C24DD111D3215498E0536105500AAC63\/datafile\/data_D-WI93OP21_I-1717445842_TS-UNDOTBS1_FNO-6_q22k3vag\n         3 \/nfs\/pdb\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/o1_mf_audit_tb__0fbbwglb_.dbf\n         4 \/nfs\/pdb\/PROD1MT101CL\/C2811CAFC29D1DC9E053F905500A7EC9\/datafile\/o1_mf_audit_tb__0fk2wkxy_.dbf\n         4 \/nfs\/pdb\/PROD1MT101CL\/C2811CAFC29D1DC9E053F905500A7EC9\/datafile\/o1_mf_users__0fj4hm5h_.dbf\n         5 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61EF92940E053F905500AFB8E\/datafile\/o1_mf_audit_tb__0fsslsvv_.dbf\n         5 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61EF92940E053F905500AFB8E\/datafile\/o1_mf_users__0frm1yg5_.dbf\n         6 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F3E2940E053F905500AFB8E\/datafile\/o1_mf_audit_tb__0fz8nq60_.dbf\n         6 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F3E2940E053F905500AFB8E\/datafile\/o1_mf_users__0fy4j81x_.dbf\n         7 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F832940E053F905500AFB8E\/datafile\/o1_mf_audit_tb__0f46dfvs_.dbf\n         7 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F832940E053F905500AFB8E\/datafile\/o1_mf_users__0f3cn007_.dbf\n         8 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61FC82940E053F905500AFB8E\/datafile\/o1_mf_audit_tb__0g5h842b_.dbf\n         8 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61FC82940E053F905500AFB8E\/datafile\/o1_mf_users__0g2v64l9_.dbf\n         9 \/nfs\/pdb\/PROD1MT101CL\/05126406D61853B3E0635005500AA12B\/datafile\/o1_mf_audit_tb__0g9rsqpo_.dbf\n         9 \/nfs\/pdb\/PROD1MT101CL\/05126406D61853B3E0635005500AA12B\/datafile\/o1_mf_inmemory__0g8yrdvh_.dbf\n \n# Tempfiles\n    CON_ID NAME\n---------- -------------------------------------------------------------------------------------------------\n         1 \/oradata\/PROD1MT101CL\/datafile\/o1_mf_temp__0d35c01p_.tmp\n         2 \/oradata\/PROD1MT101CL\/C24DD111D3215498E0536105500AAC63\/datafile\/o1_mf_temp__0d36q7dx_.tmp\n         3 \/nfs\/pdb\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/o1_mf_temp__0gr02w4t_.tmp\n         3 \/nfs\/pdb\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/o1_mf_temp__0gr0j0wr_.tmp\n         3 \/nfs\/pdb\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/o1_mf_temp_dba__0gqzydts_.tmp\n         4 \/nfs\/pdb\/PROD1MT101CL\/C2811CAFC29D1DC9E053F905500A7EC9\/datafile\/o1_mf_temp__0grhwybg_.tmp\n         4 \/nfs\/pdb\/PROD1MT101CL\/C2811CAFC29D1DC9E053F905500A7EC9\/datafile\/o1_mf_temp__0grjcvk4_.tmp\n         4 \/nfs\/pdb\/PROD1MT101CL\/C2811CAFC29D1DC9E053F905500A7EC9\/datafile\/o1_mf_temp_dba__0h6slfch_.tmp\n         5 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61EF92940E053F905500AFB8E\/datafile\/o1_mf_temp__0gokwd9n_.tmp\n         5 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61EF92940E053F905500AFB8E\/datafile\/o1_mf_temp__0gol9jc6_.tmp\n         5 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61EF92940E053F905500AFB8E\/datafile\/o1_mf_temp_dba__0gokr9ng_.tmp\n         6 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F3E2940E053F905500AFB8E\/datafile\/o1_mf_temp__0gnrh7x8_.tmp\n         6 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F3E2940E053F905500AFB8E\/datafile\/o1_mf_temp__0gnrw0dj_.tmp\n         6 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F3E2940E053F905500AFB8E\/datafile\/o1_mf_temp_dba__0gnrb2gg_.tmp\n         7 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F832940E053F905500AFB8E\/datafile\/o1_mf_temp__0gmw72co_.tmp\n         7 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F832940E053F905500AFB8E\/datafile\/o1_mf_temp__0gmwnqsp_.tmp\n         7 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61F832940E053F905500AFB8E\/datafile\/o1_mf_temp_dba__0gmw1xm3_.tmp\n         8 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61FC82940E053F905500AFB8E\/datafile\/o1_mf_temp__0gm118wt_.tmp\n         8 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61FC82940E053F905500AFB8E\/datafile\/o1_mf_temp__0gm1dv5n_.tmp\n         8 \/nfs\/pdb\/PROD1MT101CL\/CF6A94E61FC82940E053F905500AFB8E\/datafile\/o1_mf_temp_dba__0gm0wzcc_.tmp\n         9 \/nfs\/pdb\/PROD1MT101CL\/05126406D61853B3E0635005500AA12B\/datafile\/o1_mf_temp__0gj66tgb_.tmp\n         9 \/nfs\/pdb\/PROD1MT101CL\/05126406D61853B3E0635005500AA12B\/datafile\/o1_mf_temp_dba__0gh7wbpf_.tmp\n \n# Logs\nMEMBER\n--------------------------------------------------------\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_1__0b0frl6b_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_2__0b1qco5j_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_3__0b2bd0od_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_4__0b2z3qvo_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_5__0b3tkddo_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_6__0b4gvg2h_.log\n\/oradata\/PROD1MT101CL\/onlinelog\/o1_mf_7__0b51zbqp_.log<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Cleaning<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code># Convert the special Data Guard standby database to a snapshot standby\nDGMGRL> convert database prod1mt101cl to snapshot standby;\n\n# Now run all your cleaning scripts on this special database\nSQL> @all_of_my_special_cleaning_scripts\n\n# Cleaning is now finished successfully\n# Close and reopen each PDB in READ ONLY mode\nSQL> alter pluggable database all close;\nSQL> alter pluggable database all open read only;\n\n# Create XML metadata file for each PDB\nSQL> exec DBMS_PDB.DESCRIBE('\/nfs\/pdb\/apppdb1.xml','APPPDB1');\nSQL> exec DBMS_PDB.DESCRIBE('\/nfs\/pdb\/apppdb2.xml','APPPDB2');\nSQL> exec DBMS_PDB.DESCRIBE('\/nfs\/pdb\/apppdb3.xml','APPPDB3');\n\n# Shut down the cleaning database\nSQL> shutdown immediate<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Snapshot<\/h2>\n\n\n\n<p>Now use your NAS toolkit (REST API for example) to create a storage level snapshot from the \/nfs\/pdb filesystem.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Convert Snapshot Standby back to physical standby<\/h2>\n\n\n\n<p><a href=\"https:\/\/ilmarkerm.eu\/blog\/2024\/02\/snapshot-standby\/\" data-type=\"post\" data-id=\"686\">Use the instructions in this post to restore the cleaning instance back to being a physical standby.<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Attach PDBs to test database CDB<\/h2>\n\n\n\n<p>First, use your NAS toolkit to create a thin writable from from the snapshot created earlier (or multiple clones if the test setup has a Data Guard standby, a dedicated copy for each) and mount them to the test database hosts as \/nfs\/newclone.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># If the test setup has Data Guard, set standby_pdb_source_file_directory\nalter system set standby_pdb_source_file_directory='\/nfs\/newclone\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/' scope=memory;\n\n# On test primary create new pluggable database as clone\ncreate pluggable database apppdb1_newclone as clone using '\/nfs\/newclone\/apppdb1.xml' source_file_directory='\/nfs\/newclone\/PROD1MT101CL\/CC1BE00B92E672FFE053F905500A1BAE\/datafile\/' nocopy tempfile reuse;\n\n# Open it\nalter pluggable database apppdb1_newclone;<\/code><\/pre>\n\n\n\n<p>Done.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are many different ways to refresh test database with production data. Here I&#8217;m going to describe another one, that might be more on the complex side, but the basic requirements I had are: Any kind of copying the data will not work, since the databases are really large &#8211; so some kind of storage [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[64,47,4],"class_list":["post-698","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-data-guard","tag-multitenant","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/698","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=698"}],"version-history":[{"count":4,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/698\/revisions"}],"predecessor-version":[{"id":703,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/698\/revisions\/703"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=698"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}