{"id":17130,"date":"2022-02-21T11:57:33","date_gmt":"2022-02-21T10:57:33","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/snapshot-solutions-with-oracle-standard-edition-se2\/"},"modified":"2022-05-31T16:36:32","modified_gmt":"2022-05-31T14:36:32","slug":"snapshot-solutions-with-oracle-standard-edition-se2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/snapshot-solutions-with-oracle-standard-edition-se2\/","title":{"rendered":"Snapshot solutions with Oracle Standard Edition SE2"},"content":{"rendered":"

Would you like to quickly provide your engineering team with a real production environment database with updated data to develop new features? Does your operational team need a copy of the production database to test a new release or a new patch? \u201cEasy\u201d would you say. \u201cLet\u2019s do a clone and duplicate the production database with RMAN!\u201d. Yes, but would you take the same direction if your production database size makes more than a few Terra Bytes? In this article, we will see what interesting added value offers the snapshot technology and compare the solutions available for Oracle Standard Edition SE2 : PDB snapshot, dbvisit snapshot and ACFS snapshot.<\/p>\n

<\/p>\n

What is a snapshot?<\/h3>\n

A snapshot refers to a system\u2019s state at a specific point in time. To do so, the snapshot will need to store the previous version of a modified block. The snapshot has a list of pointers and knows the location of each block.<\/p>\n

There are 2 well-known snapshot methods. The copy-on-write (CoW) method, mostly used, will copy the blocks that are modified. So only modified blocks will require additional storage on the disk. In the example (see following screenshot), a snapshot is created on the source file system having initially at snapshot creation A, B and C blocks. When the B block is deleted by the source, the B block will be copied in the snapshot reserved space in order to make the information still available for the snapshot. The B source block will be deleted. The same process will happen when the source will give an update on the C block. The old version will be copied in the snapshot reserved area. For new information created by the source, refer the D block, only the source will have access to it. For each modified block there will be 3 I\/O : 1 read I\/O and 2 writes I\/O.
\n\"\"<\/a>
\n<\/br>
\n<\/br><\/p>\n

Another method called redirect-on-write (RoW) is also using pointers. The difference resides in the fact that for each block modification the new information is not updated in the current block but will be written in a new block and the pointer will be updated. So in case of write done by the source in the parent block, only 1 write I\/O is needed. This might give an advantage to the previous CoW method. But several drawbacks will make this method more complex. The deletion of the snapshot implies the data from the snapshot storage to be reconciled back into the source volume adding some additional fragmentation problem. Also, the method will be more complicated to be handled if having several snapshots on the source data. This is described in the next example screenshot where the B block is deleted by the source but kept available for the snapshot and all updated information or new information on the parent block will be inserted in the new blocks made available to the source only.
\n\"\"<\/a>
\n<\/br>
\n<\/br><\/p>\n

What is a snapshot database for?<\/h3>\n

A snapshot database will be a point-in-time copy of a database. A snapshot database can be used in READ\/ONLY mode and used to run any reporting application or other query execution, unloading the master production database. The snapshot database can also be opened in READ\/WRITE mode. This will give the opportunity to test any new application version or patch, database upgrade or patching and providing a real copy of the database to the engineering team to create further developing. This will be possible without any impact and changes made on the production database. Finally, the snapshot could also be used to easily restore data on the source.<\/p>\n

The snapshot database is instantaneously created and, knowing only the modified blocks are copied, will not take large amount of disk space. These two advantages, short time creation and few space requirement, make snapshot database a powerful tool. Snapshot databases are not intended to live forever. More modification on the source master database and\/or on the snapshot will be done, more disk space will be required.<\/p>\n

PDB snapshot<\/h3>\n

Multitenant architecture provides possibilities in the Enterprise Edition but also in the Standard Edition. We will be able to create and use PDB snapshots on Standard Edition SE2 in the limit of maximum 3 PDBs for Oracle 19c, including master PDB, cloned PDB and snapshot PDB. There are mainly two instance parameters we need to pay attention to.<\/p>\n

The first one is the clonedb parameter. In case the database is deployed on a storage with specialized storage-based snapshot technology as SunZFS, NetApp or ACFS, the parameter needs to be set to false. The master PDB stays opened READ\/WRITE. In case the database is deployed on simple and standard file systems, which might be most of the case, the clonedb parameter should be set with the true value. The source file system still needs to support sparseness. This would be the case for ext4 or xfs file system, but not the case for ext3 or ASM. The sparse file will contain all the changes made to the blocks when data are written to the snapshot database and will maintain a pointer to the parent file for accessing the unchanged data. Unfortunately, in this case, the source master PDB can only be opened READ\/ONLY. Leaving the PDB1 opened read write for a multitenant database using a LVM xfs file system as database storage (clonedb=true) will result in an ORA-65081 error :<\/p>\n

\nSQL> show pdbs\n\n    CON_ID CON_NAME                      OPEN MODE  RESTRICTED\n---------- ----------------------------- ---------- ----------\n         2 PDB$SEED                      READ ONLY  NO\n         3 PDB1                          READ WRITE NO\n         4 PDB2                          READ WRITE NO\n\nSQL> create pluggable database PDB1snap1 from PDB1 snapshot copy;\ncreate pluggable database PDB1snap1 from PDB1 snapshot copy\n*\nERROR at line 1:\nORA-65081: database or pluggable database is not open in read only mode\n<\/pre>\n
<\/br> <\/p>\n
The other important parameter for PDB snapshot is the instance parameter clonedb_dir. This parameter came with Oracle R12.2 and will mention the directory where the bitmap file should be saved. By default, the bitmap file will be stored in  $ORACLE_HOME\/dbs. This bitmap file, also often called snapshot metadata file, will record the block pointers list.<\/p>\n
Starting PDB1 in read only mode will make possible the creation of a PDB1snap1:<\/p>\n
\nSQL> show parameter clone\n\nNAME                TYPE        VALUE\n------------------- ----------- ------------------------------\nclonedb             boolean     TRUE\nclonedb_dir         string      \/u92\/app\/oracle\/oradata\/snap\n\nSQL> alter pluggable database PDB1 close immediate;\n\nPluggable database altered.\n\nSQL> alter pluggable database PDB1 open read only;\n\nPluggable database altered.\n\nSQL> create pluggable database PDB1snap1 from PDB1 snapshot copy;\n\nPluggable database created.\n\nSQL> alter pluggable database PDB1snap1 open;\n\nPluggable database altered.\n\nSQL> select con_id, name, open_mode, total_size\/1024\/1024\/1024 GB from v$pdbs;\n\n    CON_ID NAME       OPEN_MODE          GB\n---------- ---------- ---------- ----------\n         2 PDB$SEED   READ ONLY  1.00292969\n         3 PDB1       READ ONLY  1.05664063\n         4 PDB2       READ WRITE 1.05664063\n         5 PDB1SNAP1  READ WRITE 1.05664063\n<\/pre>\n
<\/br><\/p>\n
A bitmap file will be created :<\/p>\n
\nSQL> ! ls -ltrh \/u92\/app\/oracle\/oradata\/snap\ntotal 8.0K\n-rw-r-----. 1 oracle oinstall 2.1M Jul 12 09:03 CDB19CSE_3197379858_bitmap.dbf\n<\/pre>\n
<\/br><\/p>\n
We can see that the PDB source PDB1 have a size of 1.1 GB :<\/p>\n
\nSQL> select 'du -ha \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/' || GUID || '\/datafile\/' from dba_pdbs where pdb_name='PDB1';\n\ndu -ha \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/BD987DE079D833A4E0537816A8C0DF5D\/datafile\/\n\nSQL> !du -h \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/BD987DE079D833A4E0537816A8C0DF5D\/datafile\/\n1.1G\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/BD987DE079D833A4E0537816A8C0DF5D\/datafile\/\n<\/pre>\n
<\/br><\/p>\n
And the snapshot PDB, PDB1snap1, will take only 2.2 MB on the disk having a parse file for each parent file :<\/p>\n
\nSQL> select 'du -ha \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/' || GUID || '\/datafile\/' from dba_pdbs where pdb_name='PDB1SNAP1';\n\ndu -ha \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/\n\nSQL> !du -h \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/\n2.2M\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/\n\nSQL> !du -ha \/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/\n16K\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/o1_mf_users_jgqt9zf0_.dbf\n676K\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/o1_mf_undotbs1_jgqt9zf0_.dbf\n1.3M\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/o1_mf_system_jgqt9zdm_.dbf\n220K\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/o1_mf_sysaux_jgqt9zdz_.dbf\n56K\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/o1_mf_temp_jgqt9zf0_.dbf\n2.2M\t\/u02\/app\/oracle\/oradata\/cdb19cse\/CDB19CSE_SITE1\/C6E8AD2215380DB2E0537816A8C04875\/datafile\/\n<\/pre>\n
<\/br><\/p>\n
Creating the PDB snapshot took 1 second in the lab for a source PDB of 1 GB. This confirms one of the real advantages of using snapshot. We can then, of course, drop the PDB snapshot :<\/p>\n
\nSQL> alter pluggable database PDB1snap1 close;\n\nPluggable database altered.\n\nSQL> drop pluggable database PDB1snap1 including datafiles;\n\nPluggable database dropped.\n<\/pre>\n
<\/br><\/p>\n
And we would need to follow Oracle Doc ID 2419236.1 (Unable to Open the ‘Source PDB’ in ‘READ WRITE’ Mode After Dropping its ‘Snapshot Copy PDB’. It fails with ORA-01114, ORA-01110 and ORA-27091) to reopen the source PDB, PDB1, in READ\/WRITE mode. In case we would use a disaster recover solution as dbvisit, the PDB snapshot created on the primary database will induced a normal clone PDB to be created on the standby side.<\/p>\n
We can then easily understand the pros and cons of using PDB snapshot :
\n\"\"<\/a>
\n<\/br>
\n<\/br>
\n<\/br><\/p>\n
dbvisit snapshot<\/h3>\n
dbvisit standby is a well-known disaster recovery solution for Oracle Standard Edition. The system is composed of a primary and at least one standby database. The changes are applied from the primary on the standby databases through the archived logs, offering possibilities for manual switchover (in case of planned maintenance operation) or failover (in case of disaster). The standby database is started in MOUNT mode and available for recovery only. We could start the standby database in READ\/ONLY mode, be able to execute reporting query, but no new changes from the primary can be applied to it during that time. Here is where the new dbvisit snapshot feature, dbvisit snapshot, is going to provide much advantages. The dbvisit snapshot will be a copy of the standby database at a particular point in time, using CoW technology and based on LVM snapshots. The snapshot database can be opened READ\/ONLY or READ\/WRITE, and offers any reporting or application development possibility. Snapshot database can also be used to test a real failover without impacting the solution: testing a failover implies to back up the standby database first or to create it after wise (reinstate is not possible on a Standard Edition as flashback option is not available). This feature came with version dbvisit 9 as an additional option to pay and is now fully included in dbvisit 10 version. In case the standby database does not meet the requirement to create dbvisit snapshot or in order to offload the standby database, it is possible to use a cascaded standby database. The snapshot can be created from the console (GUI) or from command line (CLI), albeit dbvisit recommendation is to emphasize the console. <\/p>\n
There are a few requirements using snapshot with dbvisit:<\/p>\n