Last week an interesting issue popped up on a mission critical production app (MCPA). A statement that was run as part of a nightly batch process ran long. In fact, the statement never finished and the job had to be killed and restarted. This particular system is prone to plan stability issues due to various factors outside the scope of this post, so the first thing that the guys checked was if there had been a plan change. Surprisingly the plan_hash_value was the same as it had been for the past several months. The statement was very simple and a quick look at the xplan output showed that the plan was indeed the same with one exception. The predicate section was slightly different.

As a quick diversion, you probably already know that the plan_hash_value is calculated based on partial information about the plan. Arguably it’s the most important parts, but there are some important parts of the plan that are not included (namely the stuff that shows up in the predicate section of the plan). Randolf Geist explained which parts of the plan are used in calculating the plan_hash_value well in a post on How PLAN_HASH_VALUES Are Calculated several years ago. His summary was this:

So in summary the following conclusions can be made:

- The same PLAN_HASH_VALUE is merely an indicator that the same operations on objects of the same name are performed in the same order.

- It tells nothing about the similarity of the expected runtime performance of the execution plan, due to various reasons as demonstrated. The most significant information that is not covered by the PLAN_HASH_VALUE are the filter and access predicates, but there are other attributes, too, that are not part of the hash value calculation.

The production issue got me thinking about several things:

Could I come up with a simple test case to show a severe performance degradation between executions of a statement with the same plan_hash_value because of a change in the predicates? (the answer is it’s pretty easy actually)

What forensics could be used to determine when this situation has occurred?

How do you fix the problem?

So the first thing I did was create a test case that consists of a query with two predicates, one that is fairly CPU intensive and the other that does a simple equality comparison (which uses very little CPU). Both of the filters are very selective (and the optimizer actually estimate that the same number of rows will be returned on my test system) but the CPU cost of executing them is very different. So the order in which the filters are applied should make a big difference. I also put the low CPU filter last so that I could use a rarely used hint (ordered_predicates) to rearrange the way the optimizer wants to do it (i.e. apply the expensive one first). So here’s the set up.

 
SYS@DEMO1> -- fast filter 
SYS@DEMO1> select count(*) from kso.skew2 where col4='F';
 
  COUNT(*)
----------
         1
 
Elapsed: 00:00:00.82 <======== about 1 second with no smart scan tricks
 
SYS@DEMO1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gzvkkhaykkphp, child number 0
-------------------------------------
select count(*) from kso.skew2 where col4='F'
 
Plan hash value: 4220890033
 
-----------------------------------------------------
| Id  | Operation                  | Name  | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT           |       |        |
|   1 |  SORT AGGREGATE            |       |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW2 |   3610 | <=== optimizer estimates 3610
-----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL4"='F')
       filter("COL4"='F')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
27 rows selected.
 
Elapsed: 00:00:00.01
SYS@DEMO1> -- slow filter
SYS@DEMO1> select count(*) from kso.skew2 where TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a';
 
  COUNT(*)
----------
         0
Elapsed: 00:05:49.85 <==== almost 6 minutes with no smart scan
 
SYS@DEMO1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gatc45y5cza17, child number 0
-------------------------------------
select count(*) from kso.skew2 where
TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a'
 
Plan hash value: 4220890033
 
-----------------------------------------------------
| Id  | Operation                  | Name  | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT           |       |        |
|   1 |  SORT AGGREGATE            |       |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW2 |   3610 | <=== optimizer still expects 3610 rows
-----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage(TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a')
       filter(TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a')
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
28 rows selected.
Elapsed: 00:00:00.02

The first section shows how long it takes to read through the table and apply each of the 2 filters. As you can see, the COL4=’F’ filter takes much less time (note that I don’t want to get bogged down here, but I hope you’ll trust me that no Exadata magic kicked in and they did the same amount of work in terms of lio/pio, etc…). Also note that the STORAGE predicate is present merely because we are on an Exadata (as was the original problem query) and it only signifies that a predicate could potentially be offloaded, not that it actually has been offloaded. Please take my word for it that no smarts kick in for any of the testing in this post.

The next bit of code shows the actual test case which combines the two filters.

SYS@DEMO1> select /*+ monitor */ avg(pk_col) from kso.skew2
  2  where translate(to_char(sin(col1)),'1','a') = 'a'   
  3  and col4 = 'F'
  4  /
 
AVG(PK_COL)
-----------
 
 
Elapsed: 00:00:01.72
SYS@DEMO1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  5p4610dk709pj, child number 0
-------------------------------------
select /*+ monitor */ avg(pk_col) from kso.skew2 where
translate(to_char(sin(col1)),'1','a') = 'a' and col4 = 'F'
 
Plan hash value: 4220890033
 
-----------------------------------------------------
| Id  | Operation                  | Name  | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT           |       |        |
|   1 |  SORT AGGREGATE            |       |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW2 |   3784 |
-----------------------------------------------------
 
Predicate Information (identified by operation id): <==== note that the fast filter is applied first
---------------------------------------------------
 
   2 - storage(("COL4"='F' AND TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='
              a'))
       filter(("COL4"='F' AND TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a
              '))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
30 rows selected.
 
Elapsed: 00:00:00.01
SYS@DEMO1> @sm
Enter value for sid: 
Enter value for sql_id: 5p4610dk709pj
Enter value for sql_exec_id: 
 
 SID    SERIAL# SQL_ID        SQL_EXEC_ID SQL_EXEC_START       PLAN_HASH_VALUE         ETIME BUFFER_GETS DISK_READS
---- ---------- ------------- ----------- -------------------- --------------- ------------- ----------- ----------
####       2853 5p4610dk709pj    16777216 01-Jun-13 22:50:34        4220890033          1.63      325662          0
 
SYS@DEMO1> @rsm
Enter value for sid: 
Enter value for sql_id: 5p4610dk709pj
Enter value for sql_exec_id: 
 
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
 
SQL Text
------------------------------
select /*+ monitor */ avg(pk_col) from kso.skew2 where translate(to_char(sin(col1)),'1','a') = 'a' and col4 = 'F'
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (1366:2853)
 SQL ID              :  5p4610dk709pj
 SQL Execution ID    :  16777216
 Execution Started   :  06/01/2013 22:50:34
 First Refresh Time  :  06/01/2013 22:50:34
 Last Refresh Time   :  06/01/2013 22:50:36
 Duration            :  2s
 Module/Action       :  sqlplus@enkdb03.enkitec.com (TNS V1-V3)/-
 Service             :  SYS$USERS
 Program             :  sqlplus@enkdb03.enkitec.com (TNS V1-V3)
 Fetch Calls         :  1
 
Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    1.63 |    1.62 |     0.02 |     1 |   326K |
=================================================
 
SQL Plan Monitoring Details (Plan Hash Value=4220890033)
====================================================================================================================================
| Id |          Operation           | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                              |       | (Estim) |       | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
====================================================================================================================================
|  0 | SELECT STATEMENT             |       |         |       |         1 |     +2 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE             |       |       1 |       |         1 |     +2 |     1 |        1 |          |                 |
|  2 |    TABLE ACCESS STORAGE FULL | SKEW2 |    3784 | 89309 |         2 |     +1 |     1 |        0 |   100.00 | Cpu (2)         |
====================================================================================================================================

So the optimizer reordered the filters correctly (fast filter applied first) as we expected and the statement executed in less than 2 seconds. Note that this happened even though we had no stats on the table and dynamic sampling had to kick in and that the estimated cardinality of the two filters was the same. So this reordering was done based on estimated CPU cost.

Now let’s see what happens when we trick the optimizer into applying the filters in the wrong order. I could do this by changing the statistics but I’ll use a SQL Patch to apply the ordered_predicates hint. To do this I’ll use a little script I wrote called create_sql_patch.sql which prompts for a hint and applies it to a statement behind the scenes (much like a SQL Profile or Baseline).

 
SYS@DEMO1> @create_sql_patch
Enter value for sql_id: 5p4610dk709pj
Enter value for profile_name (PATCH_sqlid): ordered_predicates
Enter value for category (DEFAULT): 
Enter value for hint_text: ordered_predicates
Enter value for validate (false): 
 
SQL Patch ordered_predicates created.
 
Elapsed: 00:00:00.04
SYS@DEMO1> select /*+ monitor */ avg(pk_col) from kso.skew2
  2  where translate(to_char(sin(col1)),'1','a') = 'a'
  3  and col4 = 'F'
  4  /
 
AVG(PK_COL)
-----------
 
 
Elapsed: 00:05:54.05 <=== clearly something's changed!
SYS@DEMO1> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5p4610dk709pj, child number 0
-------------------------------------
select /*+ monitor */ avg(pk_col) from kso.skew2 where
translate(to_char(sin(col1)),'1','a') = 'a' and col4 = 'F'
 
Plan hash value: 4220890033
 
-----------------------------------------------------
| Id  | Operation                  | Name  | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT           |       |        |
|   1 |  SORT AGGREGATE            |       |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW2 |   3784 |
-----------------------------------------------------
 
Predicate Information (identified by operation id): <==== filters now in wrong order
---------------------------------------------------
 
   2 - storage((TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a' AND
              "COL4"='F'))
       filter((TRANSLATE(TO_CHAR(SIN("COL1")),'1','a')='a' AND
              "COL4"='F'))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL patch "ordered_predicates" used for this statement <==== Patch applied
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
 
32 rows selected.
 
Elapsed: 00:00:00.01

So now we have the same statement (sql_id: 5p4610dk709pj) and the same plan (plan_hash_value: 4220890033) but the performance is vastly different (~6 minutes vs. < 2 seconds). So yes this can and does happen. Fortunately it doesn’t happen very often, at least in my experience. Or at least I should say, it doesn’t seem to happen very often where the performance is vastly different. I should note that this specific test case is exceedingly simple and not very likely in real life because the optimizer should always know how expensive a filter is in terms of CPU usage. The more common case would be a change in estimated selectivity of a filter due to changes in statistics. Also, this simple test case is just reversing 2 filters on a single line in a plan but it is also possible that a filter would be moved to another line or that an access path may become a filter or some internal function may be applied inside a filter or access predicate. Since this post is getting long I will plan to do a Part 2 that discusses the forensic aspects (i.e. how do you find this situation?). As Tanel says, “the rabbithole gets deeper, as always”.

This post is part of an ongoing series:

• Part I: Database Thin Cloning, clonedb (oracle)
• Part II : Database Thin Cloning: Copy on Write (EMC)
• Part III: Database Thin Cloning: WAFL (Netapp)
• Part IV: Database Thin Cloning: Allocate on Write (ZFS)
• Part V: Database Thin Cloning: Summary

(very interested in real life experiences and alternative strategies to using ZFS for thin cloning. Please comment and or email me at kylelf@gmail.com)

Allocate on Write Thin Cloning

Three challenges specifically stand out when considering Copy on Write filesystem snapshots described in the previous section:

• The number of snapshots you can take of source database LUNs is limited
• The size of the snapshots can also be limited
• Difficulties arise sharing the base image of source databases at multiple points in time. In some cases it is not possible, in others difficult or resource heavy.

These challenges highlight a specific need: to create thin provision clones of a source database from multiple points of time at the same time without using any additional space consumption. This requirement is important, as it allows one base image to serve as the foundation for all subsequent clones and imposes no unplanned storage or refresh requirements on users of the target (cloned) systems.

With a filesystem storage technology called Allocate on Write, these challenges can be met. In allocate on write filesystems, data blocks are never modified. When modifications are requested to a block, the block with the new changes is written to a new location. After a request to modify a block has been issued and completed there will be two versions of the block: the version that existed prior to modification and the modified block. The location of the blocks and the versioning information for each block is located in a metadata area that is in turn managed by the same allocate on write mechanism. When a new version of a block has been written to a new location, the metadata has to be modified. However, instead of modifying the contents of the relevant metadata block, the new metadata block is written to a new location. These allocations of new metadata blocks with points to the new block ripple up the metadata structures all the way to the root block of the metadata. Ultimately, the root metadata block will be allocated in a new place pointing to the new versions of all blocks, meaning that the previous root block points to the filesystem at a previous point in time. The current, recently modified root block points to the filesystem at the current point in time. Through this mechanism an allocate on write system is capable of holding complete version history of not only a block, but all blocks involved in that block’s tracking.

Figure 10. When a datablock in the bottom left is modified, instead of modifying the current block a new block is allocated with the modified contents. The metadata pointing to this new location has to be modified as well, and again instead of modifying the current metadata block, a new metadata block is allocated. These changes ripple up the structure such that the current root block points to the filesystem at the current point in time while the previous root block points to the filesystem at the previous point in time.

ZFS

Allocate on write has many similar properties with EMC’s VNX copy on write and NetApp’s WAFL systems, but the way allocate on write has been implemented in ZFS eliminates the boundaries found in both. With ZFS there is no practical size limitations to snapshots, no practical limit to the number of snapshots, and snapshots are almost instantaneously and practical zero space (on the order of a few kilobytes).

ZFS was developed by Sun Microsystems to address the limitations and complexity of filesystems and storage. Storage capacity is growing rapidly, yet filesystems have many limitations on how many files can be in a directory or how big a volume can be. Volume sizes are predetermined and have to be shrunk or expanded later depending on how far off the original calculation was, making capacity planning an incredibly important task. Any requirement to change filesystem sizes could cause hours of outages while filesystems are remounted and fsck is run. ZFS has no need for filesystem checks because it is designed to always be consistent on disk. The filesystems can be allocated without size constraints because they are allocated out of a storage pool that can easily be extended on the fly. The storage pool is a set of disks or LUNs. All disks are generally assigned to one pool on a system, and thus all ZFS filesystems using that pool have access to the entire space in the pool. More importantly, they have access to all the I/O operations for the spindles in that pool. In many ways, it completely eliminates the traditional idea of volumes.

On a non-ZFS filesystem the interface is a block device. Writes are done per block and there are no transaction boundaries. In the case of a loss of power or other critical issue there is also a loss of consistency. While the inconsistency issues have been addressed by journaling, that solution impacts performance and can be complex.

In a ZFS filesystem all writes are executed via allocate on write, and thus no data is overwritten. Writes are written in transaction groups such that all related writes succeed or fail as a whole, alleviating the need for fsck operations or journaling. On-disk states are always valid and there are no on-disk “windows of vulnerability”. Everything is checksummed and there is no silent data corruption.

Figure 11. Comparison of non-ZFS filesystems on top and ZFS filesystems on the bottom. The ZFS filesystems are created in a storage pool that has all the available spindles, giving filesystems access to all the storage and IOPS from the entire pool. On the other hand, the non-ZFS filesystems are created on volumes and those volumes are attached to a specific set of spindles, creating islands of storage and limiting the IOPS for each filesystem.

Excepting certain hardware or volume manager specific software packages, the general comparison between non-ZFS and ZFS filesystems is as follows:

Filesystem (non-ZFS)

• One filesystem per volume
• Filesystem has limited bandwidth
• Storage is stranded on the volume

ZFS Filesystem

• Many filesystems in a pool
• Filesystems grow automatically
• Filesystems have access to all bandwidth

Along with many filesystem improvements, ZFS basically has moved the size barrier beyond any existing hardware that has yet been created and has no limitations on the number of snapshots that can be created. The maximum number of snapshots is 2^64 (18 quintillion) and the maximum size of a filesystem is 2^64 bytes (18.45 Exabytes).

A ZFS snapshot is a read-only copy of a filesystem. Snapshot creation is basically instantaneous and the number of snapshots is practically unlimited. Each snapshot takes up no additional space until original blocks become modified or deleted. As snapshots are used for clones and the clones are modified, the new modified blocks will take up additional space. A clone is a writeable copy of a snapshot. Creation of a clone is practically instantaneous and for all practical purposes the number of clones is unlimited.

Snapshots can be sent to a remote ZFS array via a send and receive protocol. Either a full snapshot or incremental changes between snapshots can be sent. Incremental snaps generally send and receive quickly and can efficiently locate modified blocks.

One concern with allocate on write technology is that a single block modification can set off a cascade of block allocations. First, the datablock to be modified is not overwritten but a new block is allocated and the modified contents are written into the new block (similar to copy on write). The metadata that points to the new datablock location has to be modified; but again, instead of overwriting the metadata block, a new block is allocated and the modified data is written into the new block. These changes cascade all the way up the metadata tree to the root block or uber block (see Figure 10). Thus for one data block change there can be 5 new blocks allocated. These allocations are quick as they take place in memory, but what happens when they are written out to disk? Blocks are written out to disk in batches every few seconds for non-synchronous writes. On an idle or low activity filesystem a single block change could create 5 writes to disk, but on an active filesystem the total number of metadata blocks changed will be small compared to the number of datablocks. For every metadata block written there will typically be several datablocks that have been modified. On an active filesystem typically a single metadata block covers the modifications of 10 or 20 datablocks and thus the extra number of blocks written to disk is usually on the order of 10% the actual metadata block count.

Figure 12. The flow of transaction data through in-memory buffers and disk.

But what happens for sync writes that can’t wait for block write batches that happen every few seconds? In those cases the sync writes must be written out immediately. Sync writes depend on another structure called the ZFS Intent Log (ZIL). The ZIL is like a database change log or redo log. It contains just the change vectors and is written sequentially and continuously such that a synchronous write request for a datablock change only has to wait for the write to the ZIL to complete. There is a ZIL per filesystem, and it is responsible for handling synchronous write semantics. The ZIL creates log records for events that change the filesystem (write, create, etc.). The log records will have enough information to replay any changes that might be lost in memory in case of a power outage where the block changes in memory are lost. Log records are stored in memory until either:

• Transaction group commits
• A synchronous write requirement is encountered (e.g. fsync() or O_DSYNC)

In the event of a power failure or panic, log records are replayed. Synchronous writes will not return until ZIL log records are committed to disk.

Another concern is that blocks that were initially written sequentially next to each other may end up spread over the disk after modifications to those blocks due to the updates resulting in a new block being allocated to a different location. This fragmentation has little effect on random read workloads but multiblock reads can suffer from this because a simple request for a continuous number of blocks may turn into several individual reads by ZFS.

ZFS also introduced the concept of hybrid storage pools where both traditional spinning disks and modern flash-based SSDs are used in conjunction. In general, disks are cheap and large in size but are limited both in latency and throughput by mechanics. Flash devices on the other hand provide I/O requests with latency that is only a small fraction of that of disks; however, they are very expensive per gigabytes. So while it may be tempting to achieve the best possible performance by putting all data on SSDs, this is usually still too cost prohibitive. ZFS allows mixing these two storage technologies in a storage pool, after which the ZIL can be placed on a mirror of flash devices to speed up synchronous write requests where latency is crucial.

Another use for SSDs in ZFS is for cache devices. ZFS caches blocks in a memory area called the Adaptive Replacement Cache—also the name of the algorithm used to determine which blocks have a higher chance of being requested again. The ARC is limited in size by the available system memory; however, a stripe of SSD devices for a level 2 ARC can be configured to extend the size of the cache. Since many clones can be dependent on one snapshot, being able to cache that snapshot will speed up access to all the thin clones based off of that snapshot.

Figure 13. A storage pool with an SSD caching layer and ZFS Intent Log for syncing.

With these capabilities in mind, there are several methods available to use this technology for database thin clones:

• Open Source ZFS snapshots and clones
• ZFS Storage Appliance from Oracle with RMAN
• ZFS Storage Appliance from Oracle with Dataguard

(Open) Solaris ZFS

ZFS is available in a number of operating systems today. It was released in Solaris 10 and has gained even more features and importance in Solaris 11. After the acquisition of Sun by Oracle, the OpenSolaris project was abandoned but the community forked a number of open source projects, the most notable of which is Illumos and OpenIndiana. These releases are still actively being developed and maintained. Many commercial products are built on these open source projects.

Any one of these systems can be used to build your own ZFS based storage system to support thin cloning:

• Database storage on local ZFS
• ZFS storage as an NFS filer
• ZFS storage as an iSCSI/block storage array

When a database is already running on Solaris with local disks, a ZFS filesystem can be used to hold all database files. Creating snapshots and clones on that filesystem is a simple matter of using a few ZFS commands; however, one does not have to bother with storage protocols like NFS. If Solaris is in use and datafiles are on ZFS anyways, it may also be a good idea to automate regular snapshots as an extra layer of security and to enable a “poor man’s flashback database”.

When a database is not running locally on a Solaris server, you can still benefit from ZFS features by building your own ZFS storage server. You can share ZFS volumes via iSCSI or fibre channel and use ASM on the database server for datafiles but instead we will focus on the easier setup with ZFS filesystems and the NFS protocol to share the volumes.

On a Solaris Storage server

• Create a zpool (ZFS pool)
• Create a ZFS filesystem in the pool
• Export that filesystem via NFS

On the source database server

• Mount the NFS filesystem
• Put datafiles on the NFS mount as one of:
  • “live” data (this may have performance implications)
  • backup image copies (or an RMAN clone)
  • a replication target

On the Solaris Storage server

• Take snapshots whenever necessary
• Create clones from the snapshots
• Export the clones via NFS

On the target database server

• Mount NFS clones
• Use this thin clone

ZFS Storage Appliance with RMAN

Oracle sells a ZFS storage appliance preconfigured with disks, memory, ZFS filesystem, and a powerful monitoring and analytics dashboard. One of these appliances can be used to create database thin clones; in fact, Oracle has published a 44-page white paper outlining the steps (found at http://www.oracle.com/technetwork/articles/systems-hardware-architecture/cloning-solution-353626.pdf). In brief, the steps involved are:

On the ZFS Appliance

• Create a “db_master” project
• Create a “db_clone” project
• For both the “db_clone” and “db_master” project, create 4 filesystems:
  • datafile
  • redo
  • archive
  • alerts

On the Source Database

• Mount a directory from the ZFS Appliance via NFS
• Backup the source database with RMAN to the NFS mount directory

On the ZFS Appliance

• Select the “db_master” project
• Snapshot the “db_master” project
• Clone each filesystem on “db_master” to the “db_clone” project

On the Target Host

• Mount the 4 filesystems from the db_clone project via NFS
• Startup the clone database on the target host using the directories from the db_clone project mount via NFS from the ZFS storage appliance

Figure 14. A diagram of the procedure used to clone databases using the ZFS storage appliance and RMAN. First a directory is mounted on the source machine from the ZFS storage appliance via NFS. Then an RMAN backup is taken of the source database onto the NFS mounted directory. The snapshot can be taken off the RMAN backup on the ZFS storage appliance and then used to create thin clones.

ZFS Storage Appliance with DataGuard

One way to efficiently address getting changes from a source database onto a ZFS storage appliance is by using Dataguard as outlined in Oracle’s white paper on Maximum Availability Architecture (MAA) DB Cloning. You can find the document at the following link:

http://www.oracle.com/technetwork/database/features/availability/maa-db-clone-szfssa-172997.pdf

The concept revolves around using Dataguard to host the datafiles from a Dataguard instance on the ZFS storage appliance. With the datafiles hosted in ZFS, all changes from the source database will be propagated to the ZFS Storage Appliance via the Dataguard instance. Once the Dataguard datafiles are hosted on the ZFS storage appliance, the snapshots of the datafiles can easily be taken at desired points in time and clones can be made from the snapshots. The ZFS clones can be used to start up database thin clones on target database hosts by mounting those datafiles via NFS to the target hosts.

Figure 15. Using Dataguard, files can be shared with a ZFS storage appliance via NFS to use for thin cloning of a target database.

Just a little reminder – next week (10-11th June) I’ll be delivering my last training session before autumn – a short 1-day (2 x 0.5 days actually) seminar about Getting the Most Out of Oracle’s Active Session History. In the future it will act as sort of a prequel (or preparation) for my Advanced Oracle Troubleshooting class, as the latter one deliberately goes very deep. The ASH seminar’s 1st half is actually mostly about the GUI way of troubleshooting the usual performance problems (EM/Grid Control) and the 2nd half is about all my ASH scripts for diagnosing more complex stuff.

P.S. I’ll also have multiple very cool news in a few months ;-)

This post is part of an ongoing series:

• Part I: Database Thin Cloning, clonedb (oracle)
• Part II : Database Thin Cloning: Copy on Write (EMC)
• Part III: Database Thin Cloning: WAFL (Netapp)
• Part IV: Database Thin Cloning: Allocate on Write (ZFS)
• Part V: Database Thin Cloning: Summary

(very interested in real life experiences and alternative strategies to using Netapp for thin cloning. Please comment and or email me at kylelf@gmail.com)

Write Anywhere File Layout (WAFL)

With EMC, thin cloning can only be achieved by using backup technology; in essence, the process has to be architected manually in order to support databases. How can the same goals be achieved but with database thin cloning specifically in mind?

A more seamless approach to database thin cloning is SnapManager for Oracle and SnapManager for SQL Server offered by NetApp. NetApp employs a technology called Write Anywhere File Layout (WAFL) that sounds on the surface like EMC VNX copy on write but is different.  WAFL has been around far longer and has a track record of being used for database thin cloning. WAFL allows quick, easy, and efficient snapshots to be taken of a filesystem. New writes don’t overwrite previous blocks with WAFL; instead, the new writes go to a new location. With this architecture it is easy to snapshot files, filesystems or LUNs in minutes.

Up to 255 snapshots can be created from a single LUN. (The 255 limitation is  per volume) An entire LUN can be the source of a snapshot, or snapshots can be made of specific sets of files. Along with the quick and easy snapshot technology, NetApp provides a feature called SnapMirror that will propagate snapshots to a secondary filer. The secondary filer in turn can use a feature called FlexClone that can be used to create clones.

Clones created in this manner will share duplicate blocks and thus can be used to create database thin clones on a secondary filer. The snapshots on the source array can be managed specifically for databases with NetApp Snapshot Manager for Oracle (SMO), or Snapshot Manager for SQL Server. SMO connects to the database, and in the case of Oracle will put all tablespaces in hot backup mode before taking snapshots then take them out of hot backup mode when the snapshot is complete. Information about the snapshots is tracked and managed within SMO inside an Oracle database that serves as a repository.

The technology involved with snapshot cloning in WAFL is solid but very component heavy. On top of the components already listed is a required installation on the target array called NetApp SnapDrive for UNIX. Snapshots are propagated to the secondary array with SnapMirror but a feature called Protection Manager manages the process. A critical step in cloning operations is correctly synchronizing the snapshot schedule of SMO with the transfer schedule of Protection Manager so that the same retention class is maintained on the source and target arrays. On the destination array it is important to manage and track how many clones are made and which snapshot is used for the basis of each clone. If more than 255 clones are made of a single LUN, the next clone will no longer be a logic (virtual) clone sharing duplicate data blocks but a physical clone with a completely new copy of the datafiles.

Figure 8. Using NetApp filer technologies including WAFL, SnapMirror, SMO, and FlexClone to create thin provisioned database clones.

An important consideration on WAFL volumes on NetApp is the aggregate pool. The aggregate pool defines which LUNs will be included in a snapshot. The size limitation on this pool varies between 16TB and 100TB depending on the model of the NetApp array. The limits on the size of this pool and the limit of 255 snapshots should be considered when evaluating the capabilities of SMO and FlexClone on NetApp.

Reference

• https://communities.netapp.com/docs/DOC-10323 Back to Basics: FlexClone
• http://media.netapp.com/documents/tr-3347.pdf A Thorough Introduction to FlexCloneTM Volumes
• http://media.netapp.com/documents/tr-3446.pdf SnapMirror Async Overview and Best Practices Guide
• http://media.netapp.com/documents/tr-3742.pdf Using FlexClone to Clone Files and LUNs
• http://media.netapp.com/documents/tr-3761.pdf SnapManager 3.0 for Oracle Best Practices
• http://www.dataprotection.nu/blog/index.php/56 Snapmirror

from http://media.netapp.com/documents/tr-3761.pdf 

• http://oraclestorageguy.typepad.com/oraclestorageguy/2007/07/oracle-backup-w.html
• http://oraclestorageguy.typepad.com/oraclestorageguy/2007/07/oracle-backup-1.html
• http://oraclestorageguy.typepad.com/oraclestorageguy/2007/08/oracle-backup-w.html
• http://oraclestorageguy.typepad.com/oraclestorageguy/2007/08/oracle-backup-1.html