This post describes how to implement Oracle Database Snapshot Technology on Exadata Machine.
Because Exadata Storage Cell Smart Features, Storage Indexes, IORM and Network Resource Manager work at level of ASM Volume Manager only, (and they don’t work on top of ACFS Cluster File System), the implementation of the snapshot technology is different compared to any other non-Exadata environment.
At this purpuse Oracle has developed a new type of ASM Disk Group called SPARSE Disk Group. It uses ASM SPARSE Grid Disk based on Thin Provisioning to save the database snapshot copies and the associated metadata, and it supports non-CDB and PDB snapshot copy.
The implementation requires the following minimal software versions :
- Exadata Storage Software version 12.1.2.1.0.
- Oracle Database version 12.1.0.2 with bundle patch 5.
One major restriction applies to Exadata Storage Sanpshot compared to ACFS;
the source database must be a shared copy open on read only and called Test Master. The Test Master Database can not be modified or deleted as long the latest child snapshot is in use.
This restriction exists because Exadata Snapshot technology uses “allocate on first write”, and not “copy on write” (like for ACFS), and the snapshot is per-database-datafile.
When a child snapshot issue a write, the write goes to a private copy of that block inside the snapshot, preserving the original block value which can be accessed by other child snapshots of the same Test Master.
How to Implement Exadata Storage Snapshots in a PDB Environment
Check the celldisks for available free space to allocate to a new SPARSE Disk Group
[root@strgceladm01 ~]# cellcli -e list celldisk attributes name,freespace CD_00_strgceladm01 853.34375G CD_01_strgceladm01 853.34375G CD_02_strgceladm01 853.34375G CD_03_strgceladm01 853.34375G CD_04_strgceladm01 853.34375G CD_05_strgceladm01 853.34375G CD_06_strgceladm01 853.34375G CD_07_strgceladm01 853.34375G CD_08_strgceladm01 853.34375G CD_09_strgceladm01 853.34375G CD_10_strgceladm01 853.34375G CD_11_strgceladm01 853.34375G FD_00_strgceladm01 0 FD_01_strgceladm01 0 FD_02_strgceladm01 0 FD_03_strgceladm01 0 [root@strgceladm01 ~]# [root@strgceladm02 ~]# cellcli -e list celldisk attributes name,freespace CD_00_strgceladm02 853.34375G CD_01_strgceladm02 853.34375G CD_02_strgceladm02 853.34375G CD_03_strgceladm02 853.34375G CD_04_strgceladm02 853.34375G CD_05_strgceladm02 853.34375G CD_06_strgceladm02 853.34375G CD_07_strgceladm02 853.34375G CD_08_strgceladm02 853.34375G CD_09_strgceladm02 853.34375G CD_10_strgceladm02 853.34375G CD_11_strgceladm02 853.34375G FD_00_strgceladm02 0 FD_01_strgceladm02 0 FD_02_strgceladm02 0 FD_03_strgceladm02 0 [root@strgceladm02 ~]# [root@strgceladm03 ~]# cellcli -e list celldisk attributes name,freespace CD_00_strgceladm03 853.34375G CD_01_strgceladm03 853.34375G CD_02_strgceladm03 853.34375G CD_03_strgceladm03 853.34375G CD_04_strgceladm03 853.34375G CD_05_strgceladm03 853.34375G CD_06_strgceladm03 853.34375G CD_07_strgceladm03 853.34375G CD_08_strgceladm03 853.34375G CD_09_strgceladm03 853.34375G CD_10_strgceladm03 853.34375G CD_11_strgceladm03 853.34375G FD_00_strgceladm03 0 FD_01_strgceladm03 0 FD_02_strgceladm03 0 FD_03_strgceladm03 0 [root@strgceladm03 ~]#
For each Storage Cell Create a SPARSE Grid Disks as described below
[root@strgceladm01 ~]# cellcli -e CREATE GRIDDISK ALL PREFIX=SPARSE, sparse=true, SIZE=853.34375G Cell disks were skipped because they had no freespace for grid disks: FD_00_strgceladm01, FD_01_strgceladm01, FD_02_strgceladm01, FD_03_strgceladm01. GridDisk SPARSE_CD_00_strgceladm01 successfully created GridDisk SPARSE_CD_01_strgceladm01 successfully created GridDisk SPARSE_CD_02_strgceladm01 successfully created GridDisk SPARSE_CD_03_strgceladm01 successfully created GridDisk SPARSE_CD_04_strgceladm01 successfully created GridDisk SPARSE_CD_05_strgceladm01 successfully created GridDisk SPARSE_CD_06_strgceladm01 successfully created GridDisk SPARSE_CD_07_strgceladm01 successfully created GridDisk SPARSE_CD_08_strgceladm01 successfully created GridDisk SPARSE_CD_09_strgceladm01 successfully created GridDisk SPARSE_CD_10_strgceladm01 successfully created GridDisk SPARSE_CD_11_strgceladm01 successfully created [root@strgceladm01 ~]#
For each Storage Cell List all Grid Disks
[root@strgceladm01 ~]# cellcli -e list griddisk attributes name,size DATAC1_CD_00_strgceladm01 6.294586181640625T DATAC1_CD_01_strgceladm01 6.294586181640625T DATAC1_CD_02_strgceladm01 6.294586181640625T DATAC1_CD_03_strgceladm01 6.294586181640625T DATAC1_CD_04_strgceladm01 6.294586181640625T DATAC1_CD_05_strgceladm01 6.294586181640625T DATAC1_CD_06_strgceladm01 6.294586181640625T DATAC1_CD_07_strgceladm01 6.294586181640625T DATAC1_CD_08_strgceladm01 6.294586181640625T DATAC1_CD_09_strgceladm01 6.294586181640625T DATAC1_CD_10_strgceladm01 6.294586181640625T DATAC1_CD_11_strgceladm01 6.294586181640625T FGRID_FD_00_strgceladm01 2.0717315673828125T FGRID_FD_01_strgceladm01 2.0717315673828125T FGRID_FD_02_strgceladm01 2.0717315673828125T FGRID_FD_03_strgceladm01 2.0717315673828125T RECOC1_CD_00_strgceladm01 1.78143310546875T RECOC1_CD_01_strgceladm01 1.78143310546875T RECOC1_CD_02_strgceladm01 1.78143310546875T RECOC1_CD_03_strgceladm01 1.78143310546875T RECOC1_CD_04_strgceladm01 1.78143310546875T RECOC1_CD_05_strgceladm01 1.78143310546875T RECOC1_CD_06_strgceladm01 1.78143310546875T RECOC1_CD_07_strgceladm01 1.78143310546875T RECOC1_CD_08_strgceladm01 1.78143310546875T RECOC1_CD_09_strgceladm01 1.78143310546875T RECOC1_CD_10_strgceladm01 1.78143310546875T RECOC1_CD_11_strgceladm01 1.78143310546875T SPARSE_CD_00_strgceladm01 853.34375G SPARSE_CD_01_strgceladm01 853.34375G SPARSE_CD_02_strgceladm01 853.34375G SPARSE_CD_03_strgceladm01 853.34375G SPARSE_CD_04_strgceladm01 853.34375G SPARSE_CD_05_strgceladm01 853.34375G SPARSE_CD_06_strgceladm01 853.34375G SPARSE_CD_07_strgceladm01 853.34375G SPARSE_CD_08_strgceladm01 853.34375G SPARSE_CD_09_strgceladm01 853.34375G SPARSE_CD_10_strgceladm01 853.34375G SPARSE_CD_11_strgceladm01 853.34375G [root@strgceladm01 ~]#
From ASM Instance Create a SPARSE Disk Group
SQL> CREATE DISKGROUP SPARSEC1 EXTERNAL REDUNDANCY DISK 'o/*/SPARSE_CD_*' ATTRIBUTE 'compatible.asm' = '12.2.0.1', 'compatible.rdbms' = '12.2.0.1', 'cell.smart_scan_capable'='TRUE', 'cell.sparse_dg' = 'allsparse', 'AU_SIZE' = '4M'; Diskgroup created.
Set the following ASM attributes on the Disk Group hosting the Test Master Database
ALTER DISKGROUP DATAC1 SET ATTRIBUTE 'access_control.enabled' = 'true';
Grant access to the OS RDBMS user used to access to the Disk Group
ALTER DISKGROUP DATAC1 ADD USER 'oracle';
From an ASM Instance Set ownership permissions for every file that belongs solely to the PDB being snapped cloned as per example below
alter diskgroup DATAC1 set ownership owner='oracle' for file '+DATAC1/CDBT/<xxxxxxxxxxxxxxxxxxx>/DATAFILE/system.xxx.xxxxxxx'; alter diskgroup DATAC1 set ownership owner='oracle' for file '+DATAC1/CDBT/<xxxxxxxxxxxxxxxxxxx>/DATAFILE/sysaux.xxx.xxxxxxx'; alter diskgroup DATAC1 set ownership owner='oracle' for file '+DATAC1/CDBT/<xxxxxxxxxxxxxxxxxxx>/DATAFILE/users.xxx.xxxxxxx'; ... ..
Restart the Master Test PDB in Read Only
alter pluggable database PDBTESTMASTER close immediate instances=all; alter pluggable database PDBTESTMASTER open read only;
Create the first PDB Snapshot Copy on Exadata SPARSE Disk Group
Create pluggable database PDBDEV01 from PDBTESTMASTER tempfile reuse create_file_dest='+SPARSEC1' snapshot copy;
Feedback of the Exadata Storage Snapshots
The ability to create storage efficient database copies in a few seconds, independently from the size of the Test Master is very useful for today IT departments; but such extreme velocity and flexibility is not entirely free. In fact performance tests on a I/O bound workload have highlighted important performance degradation. This reminds us that as defined by Oracle Corporation, the Snapshot Technology, included on Exadata Machine remains a non-production option.
My Oracle Notes 
Reblogged this on jee's blog.
LikeLike
Can snapshots support DML changes or would they need to be made in the test master before the snapshot is created?
LikeLike
Yes indeed, snapshots support DML operations.
LikeLike
Hi Emiliano, we have an exadata and plan to use two masters (each has a size of about 30TB) with each 5 snapshots. I have some questions:
1) can I have more than one sparse disk group?
2) How large can be every sparse diskgroup at maximum? To our calculation we need 124 TB sparse, but having only 3 cell server with each 10 disks and found a requirement of max. sparse disk = 4 TB – is this correct?
3) How ro calculate the space we need for sparce disk group and what are the requirements?
4) the two masters are the located in the sparse dg oder in DATA dg?
5) the TEMP, REDO and control files of the snapshots are located in DATA ?
LikeLike
Hi Rainer,
I try to answer to all your questions:
1) Yes.
2) The maximum physical size allocated to sparse grid disks from a single cell disk is 4TB, and the maximum allowed virtual size is 100TB.
3) The amount of space consumed on the sparce DG is proportional to the number of unique blocks changed on each snapshot database.
4) The masters are located on DATA DG.
5) PDB Snapshot Clone do not copy control files and redo logs, regarding the temp use the clause “tempfile reuse” as reported on the example.
Enjoy
Emiliano
LikeLike
Hi,
I think you will write about something other but not about SPARSE and Snapshot.
We have X8M with Exa 21 and Grid & RDBMS 19.11.
We have setup an SPARSE Diskgroup and we create a Master PDB TKK5G.
But when doing an initial “alter pluggable database snapshot” we get an error message
which says that the *.pdb File can not be created.
Yes, and this command is never used in your blog….
So I think you had never created a snap_master from which you can create snapshot copies.
SQL>
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT;
ALTER PLUGGABLE DATABASE SNAPSHOT
*
ERROR at line 1:
ORA-65227: unable to open pdb archive file
+SPARSEC1/snap_2115255006_3024431416.pdb
Could you explain what you infact setup in your environment?
LikeLike
Dear Stefan,
I’m not sure to have fully understood your message correctly, except for the sentence where you say my procedure doesn’t work.
In any case I’m not using the command “ALTER PLUGGABLE DATABASE SNAPSHOT;” because on Exadata with Sparse Disk Group the following syntax should be used “Create pluggable database from tempfile reuse create_file_dest=’+SPARSEC1′ snapshot copy;”
I also send you the following free advice, the next time you have to write a comment like that, be more polite when addressing people.
LikeLike