Oracle Scratchpad

March 10, 2014

Duplicate database

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 6:19 pm BST Mar 10,2014

One of the people attending my seminar in Munich last week has emailed me some details about a nasty little surprise you may get if you’re thinking about TSPITR (tablespace point in time recovery), and happen to have a few materialized views in your database.

You might have wanted to use the “duplicate target database”  from rman with the “skip tablespace” option to create a duplicate database, excluding tablespaces that you’re not interested in, if you needed to recover a particular tablespace (or set of tablespaces) to a specific point in time.  (Once you’ve done that you can extract the data that you want and then discard the duplicate database). But there’s a requirement for recovery is that the set of tablespaces should be “self-contained”; but what does “self-contained” mean ?

Amongst other things it means that none of the tablespace you skip should contain materialized views. Note carefully, that’s not saying the schema you’re interested in shouldn’t have created any materialized view, or the tablespace you want to duplicate contains a base table for a materialized view in another table; it really does mean – if you’ve got ANY materialized view ANYWHERE in the database, you have to duplicate those tablespaces as part of the process.

Here’s the restriction note from MoS (note the exclamation mark – maybe the Oracle analyst was surprised too):

Restriction Note: 
You MUST NOT exclude 
- SYS-owned objects 
- or tablespaces with rollback segments, 
- nor tablespaces containing “MATERIALIZED VIEWS”! 

Implementation suggestion – always put materialized views (and materialized view logs, and indexes on materialized views) in their own tablespace(s), just in case one day you want to do a tablespace point in time recovery and find you’ve got a few bits of materialized views scattered all around your database.


When I first heard this comment I didn’t believe it (but take a look at MoS document ID: 1287276.1 if you’re finding it hard to believe). Naturally my engine of doom went into overdrive immediately after I was convinced and made me wonder what would happen in a 12c container database with several plugged databases; obviously the existence of a materialized view in one plugged database shouldn’t have any impact on TSPITR for another pluggable database – but I wouldn’t mind if someone tested the hypothesis and reported back what they found.


It’s just occurred to me that this type of TSPITR problem simply won’t exist if you’re using Delphix as part of your working environment.


  1. Jonathan,

    I’ve also experimented with TSPITR before, and while it says the tablespaces should be self-contained, which is self explanatory, it doesn’t say anything about keys. Maybe I’ve done something horribly wrong, but I’ve tested it with two tablespaces, one with a master table with a primary key, and the other with a child table with a foreign key referencing the master. Now it all seems cool, but when it builds the auxilliary instance and tries to drop the tablespaces afterwards (drop tablespace xxx including contents) it fails because it can’t drop the tablespace with the master table because of the primary key is still active in it! So I had to change the order of the two tablespace, when I did the restore in rman (child tablespace first, master second), then it succeeded. But I’m not sure that it’s the intended behaviour.

    Comment by asdPro — March 10, 2014 @ 6:31 pm BST Mar 10,2014 | Reply

  2. asdPro,

    Thanks for the note. I’m not sure I understand exactly what you did, though. I would expect “drop (parent) tablespace including contents” to fail under any circumstances if the tablespace held a table that had a key that was the target of an enabled foreign key constraint on a table in a different tablespace. Could you elaborate a little on the strategy that failed compared to the strategy that succeeded.

    Comment by Jonathan Lewis — March 10, 2014 @ 8:41 pm BST Mar 10,2014 | Reply

    • Basically, when I failed, I specified the order of tablespaces as “recover tablespace parent, child”. Then exactly that happened, what you mentioned, which is expected, because that’s how Oracle supposed to work, but my problem is
      a) the validation check that determines if the tablespace(s) are eligible for TSPITR didn’t fail, which is somehow expected, because the two tablespaces were self-contained
      b) if you, as a DBA, supposed to do a TSPITR, because the business requires you to do so, and do it ASAP, but you know nothing of the underlying connections between the tables in the tablespaces, you could run into this kind of problem.

      When I succeeded, I just switched the order of the two tablespaces in the recover command as “recover tablespace child, parent”, then it worked, because in the auxiliary instance, it dropped the child tablespace first, which didn’t cause any constraint violation errors.

      My point is, like the example in your article, you need to consider these kinds of things (though, the materialized view limitation thing is NOT self-explanatory, not for me at least) before you jump into the TSPITR process. But aside from these little things, it’s a very nice feature to have.

      Comment by asdPro — March 11, 2014 @ 8:10 am BST Mar 11,2014 | Reply

      • asdPro,

        Thanks for the clarification

        It raises the question of how you get around a problem if you’ve got (parentA in tablespace1, childA in tablespace2) with (parentB in tablespace2, childB in tablespace1) – it the equivalent of a “tablespace recovery deadlock”. In your scenario you can’t recover tablespace1 first because it holds childB, and you can’t recover tablespace1 first because it holds childA.

        I don’t think it’s ridiculous scenario to consider since there are some sprawling applications in the world using multiple schemas with hideously complex relationships between tables across schemas – it would probably be quite easy to find exactly this sort of cross-over somewhere in the wild. (Looks like an even bigger selling point for Delphix, which lets you do a full database recovery without making an extra copy of the whole database.)

        Comment by Jonathan Lewis — March 11, 2014 @ 8:23 am BST Mar 11,2014 | Reply

  3. That’s pretty fascinating and surprising. Good to know and thanks for pointing this out.
    Wonder why that’s a constraint and like you, curious it affects VDBs on 12c.
    Yes, Delphix definitely makes this kind of operation effortless without such constraints.

    Comment by Kyle Hailey — March 10, 2014 @ 10:07 pm BST Mar 10,2014 | Reply

  4. I wonder why a tablespace holding MVs cannot be excluded Does it mean *NO* MV can be skipped, whether they relate to the source table(s) in the tablespace being recovered.

    I suppose a manual RESTORE should still work.

    Comment by Hemant K Chitale — March 11, 2014 @ 8:23 am BST Mar 11,2014 | Reply

    • Hemant,

      I said it, but I didn’t believe it: “if you’ve got ANY materialized view ANYWHERE in the database”; it doesn’t matter what schema they’re in according to MoS.

      I’ve been resisting the temptation of running an rman duplicate with SQL tracing enabled to see if it’s a special SQL check that rman does, or whether it’s an “ordinary” Oracle error that’s getting re-interpreted. If it’s the former then I would expect a manual restore to work, if it’s triggered by a statement that triggers a standard Oracle error then I guess a manual restore might not work. I’ve also resisted the temptation of setting up a test of a manual TSPITR.

      One oddity about the whole MV thing, though, that I’d want to work through: there are some tables in the SYS schema that track materialized views and their logs and refreshes. I’m wondering whether you’d have to do a full refresh of any materialized views that you had recovered to a point in time in case the SYS information said that it was last refreshed at a point in time in its apparent future.

      Again, I’m going to resist the temptation of doing the experiment.

      Comment by Jonathan Lewis — March 11, 2014 @ 8:34 am BST Mar 11,2014 | Reply

  5. Jonathan,

    This may be a workaround:

    I believe that you can postpone the “self-containment” check to the end of the DUPLICATE command by running the DUPLICATE without being connecting to the target database
    That is, RMAN will retrieve the requested tablespaces/datafiles, and then give you an error/warning. I have not tested that behavior in Oracle 12c though.

    I stumbled upon that behavior when I tried to share read-only tablespaces between instances –

    Comment by iiotzov — March 11, 2014 @ 7:58 pm BST Mar 11,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 3,873 other followers