Oracle Scratchpad

January 6, 2011


Filed under: Infrastructure — Jonathan Lewis @ 9:07 am GMT Jan 6,2011

I’ve lost count of the number of times I’ve said something like: “Ideally you really need to do your testing on the production system”. Kevin Closson has recently written some interesting notes (with more to come) about clonedb – an 11.2 feature that may make it possible to get pretty close to this ideal.

With a follow-up article from Tim Hall (Feb 2011)

Footnote: for purposes of removing any confusion, please note that actually testing on your production system is not a good idea


  1. It’s one of the hardest things to do: cloning a production system in such a way that testing in the clone becomes truly significant and relevant.

    One I’ve had success with is VMs (lpars) in unix environments and using the disaster recovery systems to do this. At least that way I have an approximation of the true production conditions and I can easily “clone” a true production copy.

    This stuff in 11.2 looks very promising.

    Comment by Noons — January 6, 2011 @ 10:35 am GMT Jan 6,2011 | Reply

  2. When I have needed to create a real copy of production and space allows I’ve:

    – taken a copy of the datafiles
    – taken a backup “controlfile to trace”
    – edited the controlfile trace to rename the instance and point to the new location of the datafiles (renamed as necessary)
    – run the controlfile trace script with an alter database open resetlogs
    – taken a backup.

    This gives a physical copy of the live database, but of course will not necessarily be running on the same hardware or O/S configuration.

    There are other approaches when the full database won’t fit onto the test hardware that help, but these require you to have considered this requirement at physical database design, use of some Oracle features, and knowledge of the application and data. (I’m thinking use of partitioning, thoughtful placement of partitions into tablespaces, and tablespace point in time recovery).

    I’m interested to hear of any other approaches that are available that readers have found useful.

    The 11.2 clonedb looks very interesting – though something I’ll have to wait for. So far, I haven’t managed to get my hands on 11.2 in commercial dev or production, although have come close a few times.


    Comment by Mathew Butler — January 6, 2011 @ 10:57 am GMT Jan 6,2011 | Reply

    • Hi Matthew,
      Check out Delphix:
      Delphix in short takes an RMAN backup and compresses it. Delphix then presents the RMAN files as regular datafiles on an NFS mount. The NFS mounted datafiles can be open *concurrently* by as many databases as your hardware allows. Any changes to the RMAN files don’t actually change the files but do a copy on write. Thus each “virtual” databases shares the bulk of the datafiles and keeps private block copies of any blocks that are modified. One could give every developer in the team a copy of production ! Pretty awesome.
      It works now on 10g as well as 11.1 and 11.2
      I think the technology is so cool that I just joined the company. :)
      – Kyle

      Comment by Kyle Hailey — January 6, 2011 @ 6:25 pm GMT Jan 6,2011 | Reply

      • Some people have been asking for more details on differences and similarities between Delphix and Clonedb. Both enable multiple copies of a database to share blocks that they have in common. Both products use COW (Copy on Write) technology to make it appear that each copy is a private read-write copy of the database.

        But that is where the similarities end.

        Oracle’s clonedb is only for and higher whereas Delphix is for Oracle versions 9.2 and up.

        Oracle’s clonedb stores the original RMAN backup in one location and then exports that location to the target machine via NFS. Any blocks the target database changes are stored locally on the target host thus ruling out mobility of the cloned databases. Once a cloned is created, it’s stuck on that initial target host. Delphix stores everything on the appliance so virtual databases can be stopped on one target host and started up on another. Delphix also provides centralized management with NDMP-based backup and/or replication of a the entire Delphix appliance. The latter two features enable centralized backup of the appliance and all VDBs hosted in it.

        Delphix compresses data which saves storage. Typical compression on the initial RMAN level 0 backup is 1/3 of the original size. Oracle’s cloneDB does not offer compression of the shared blocks or the changed blocks.

        Finally, I’ve heard of no adoption of Oracle clonedb feature. Oracle has a long history of new features having major gotchas and bugs in the first or even second version of the feature. It’s one of the reasons for slow adoption of new releases. As a DBA, I would avoid a new feature until other major customers had put them into production and flushed out the major bugs.

        In comparison, Delphix is well-seasoned and battle tested. Delphix is running 1000s of virtual databases across customers such as Facebook, Comcast, Tivo, P&G, Staples, eBay, Qualcomm, KLA-Tencor, Successfactors, Deutsche Bank etc.

        A major issue that I have with Oracle clonedb (or using a ZFS appliance for that matter) is that it requires storage admins, system admins and DBAs all working together writing scripts and coordinating resources and activities for it to work. It’s a bear to operate and maintain. It’s fragile and breaks easily; thus it lacks agility. Delphix, on the other hand,is the epitome of ease-of-use and agility. A developer who has been granted access to Delphix can, in 3 clicks, by themselves,in a matter of minutes clone a copy of multi-terabyte database. The clone, i.e. the virtual database, for all intents and purposes takes up no extra space.

        Delphix also provides (and Oracle does not):

        1. A time-continuous image of the source database. We call this the TimeFlow. The Oracle clonedb product works with a single, static RMAN backup copy of the source database and Oracle has no concept of timeflow. If you wanted to simulate TimeFlow with Clonedb, you would need to make and keep multiple RMAN level 0 backups. Delphix works with one single level 0 backup and then uses incremental backups and archive log files to provide the capability of provisioning a virtual database that is a clone of what the source database looked like at any point in time or SCN.

        2. Both snapshots and archive log collection to facilitate management of the Time Flow.

        3. Policies to manage the amount of storage consumed by the Time Flow of the source database.

        4. The ability to provision a virtual copy of the source database from any point in time in the life of that database.

        5. The workflow orchestration to:
        — Create the virtual copy of the database
        — Assign a SID to the DB instance that will open the database
        — Modify the parameters for the database instance that will open the database
        — Mount the copy of the database on a target server
        — Start an Oracle instance on that server to recover, open and manage the database
        — Register that instance with the Oracle Listener process so that the database if visible to the user community

        6. The ability to create a virtual copy of a virtual copy. This enables the natural workflow used during app/database
        development in which a database migrates from dev to test to QA to pre-prod environments. In order to have such functionality on Oracle an RMAN level 0 would be required per copy of virtual database, ie a branch of the database. With Delphix the bulk of the data is shared between original database and branch or a branch of a branch.

        7. Automated refresh of the virtual database copies using the latest version of the source database

        8. The tools and UI to manage multiple source databases and their associated time flows from a single console.

        9. The tools and UI to monitor and manage the virtual copies for any and all source databases. This includes tools to audit who creates a copy of a database (physical or virtual), when/where that database is being used. Useful to establish a chain of custody for databases that contain sensitive information.

        10. The ability to create virtual copies of two different source databases such that the virtual copies are synchronized in time. This is important for applications that work with more than one database

        11. The ability to take ‘instantaneous’ snapshots of the virtual copies of a database. Very useful if the need arises to roll a virtual copy back to a prior point in time

        12. The ability to create a new physical copy of the source database such that the new copy looks like the source database at a specified point in time in the life of source database. Useful when creating a pre-production environment for performance testing and also useful if it becomes necessary to restore the actual source database to a particular point in time (e.g., to recover from logical corruption of the DB).

        13. The ability to manage and/or prioritize the server resources allocated to process IO requests for each virtual database copy. This is important in environments where the DBA team must meet SLAs with certain business teams (e.g., different app development teams, reporting environments vs. dev environments, etc.)

        14. The ability to easily assign storage caps (and guaranteed minimum allocations) to each virtual database copy.

        15. A very slick user interface. The interface of Delphix is one of my favorites and a long cry from the weak UIs at Oracle that lack power, user friendliness and speed not to mention aesthetics.

        Comment by Kyle Hailey — July 28, 2012 @ 12:27 am BST Jul 28,2012 | Reply

        • Kyle,

          I’m not sure I approve of people who use my blog to advertise products.
          Lucky we’re old friends !

          Comment by Jonathan Lewis — August 7, 2012 @ 6:24 pm BST Aug 7,2012

        • haha – Jonathan just saw your response almost 2 years later. I had forgotten that I’d written a list of difference with clonedb. Decent list. I can use that list and point people to your blog :) Question still comes up. Sorry it came of as product placement and glad we’ve been friends for friends for a long while – phew !

          Comment by Kyle Hailey — March 29, 2014 @ 12:15 am GMT Mar 29,2014

  3. Call me a cynic, but I’ve practically given up on trying to do significant performance testing (at least without the disclaimer that “we’ve done our best but on production …”).

    Possibly the final straw was finding out that at a previous site the SAN used for development and testing (including performance) was very slow at reading but very fast at writing, and the production SAN was exactly the other way round — or have I got that reversed in my head? Under such circumstances even with an exact copy of production data files we were doomed. If we’d been able to use the production SAN then the performance testing warehouse would then not be sharing spindles with the CRM system, as the production system was.

    I love these Oracle features, but steering an organisation towards their use can be a tricky business, and it seems to get worse the larger the organisation gets.

    New year’s blues on my part, possibly.

    Comment by David Aldridge — January 6, 2011 @ 1:16 pm GMT Jan 6,2011 | Reply

    • Not at all, David. It’s a common issue.

      Cripes, I even have problems trying to get RAID groups in the *same* SAN to behave the same way, let alone across SAN devices.

      At one stage our SAN admin was so desperate to try and get the SAN setup to behave in a predictable manner he started to configure *all* the RAID groups the same way, both in production and the DR SAN!

      Fortunately we were able to find what the problem was before that policy resulted in serious underuse of the available storage.

      You are quite right in being slightly cynic about this Oracle feature: it totally does *not* provide a way around such problems!

      This is where IT knowledge in general and specifically of one’s systems and how they are setup becomes all important. I did a talk last year on the Sydney Oracle Meetup precisely on this same subject: the potential problems caused by unbalanced SAN technologies.

      It is a real problem. One that Oracle has no chance whatsoever of addressing, and one that very few SAN makers are willing to admit exists, let alone correct.

      But don’t expect those proposing “cloud” solutions to even understand they are subject to exactly the same problems. After all, they’re all “experts”, aren’t they?

      Comment by Noons — January 6, 2011 @ 1:26 pm GMT Jan 6,2011 | Reply

  4. How many times has “damagement” asked you (or me !) : “If we implement the change you recommend what (exactly) will the runtime be for this 1hour job ?”. How do you (or me !) provide exact quantification without “doing the real thing” ?

    Comment by Hemant K Chitale — February 11, 2011 @ 8:15 am GMT Feb 11,2011 | Reply

    • Hemant,

      This is a very late reply to your (rhetorical) question – but someone did ask me once to sign a piece of paper guaranteeing that nothing would ever go wrong ever again if they implemented my suggested solution to a problem they had. I did try to explain why it wasn’t possible to offer such an extreme guarantee, but I’m not sure they were convinced.

      Comment by Jonathan Lewis — August 7, 2012 @ 6:21 pm BST Aug 7,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: