RE: Data backup/migration strategy..

From: Upendra nerilla <>
Date: Fri, 15 May 2015 12:36:55 -0400
Message-ID: <BLU181-W31D5AFEA5F79783C506341D8C70_at_phx.gbl>

Hi Jeremy,
Thanks for sharing your experiences.
See below my response..


> Date: Tue, 12 May 2015 18:19:23 -0500
> Subject: Re: Data backup/migration strategy..
> From:
> To:
> CC:
> Hi Upendra -
> I've got a very similar estate to you (many large databases, multiple
> TB, with lots of LOBs). I am using all of these proposed ideas
> successfully.
> Well, with one exception - TTS doesn't play nicely with standby
> databases. However I have standbys for the production but not
> dev/uat. And I am generally using TTS on dev/uat.

Sounds our problems are pretty similar.. :) From time to time we do have to move data from non-prod to prod and we are in the same situation like you are.. no stby in dev/uat but have stby in prod.

> For cloning to dev, we have a script that stands up a temporary
> instance, does a point-in-time recovery of only one tablespace
> (because we also have heavy consolidation and need to recover
> individual schemas), then uses TTS to detach the tablespace and attach
> it to a target DB. As long as you don't use TDE, this method works
> great - especially since you can do the restore directly into ASM and
> so you don't need to copy the [large] datafiles at all but can attach
> them to the target dev/uat system. I'm doing this because of the
> desire to do tablespace-level PIT recoveries; if you're doing
> whole-database clones then you can skip the TTS and use duplicate as
> others have pointed out.

We don't have the variable of TDE in this environment. My requirement is always at the schema level. If it is not too much trouble, would you mind the sharing your script?

> We have also done both data pumps and rman backups from standby
> databases, which Mark was proposing. Works great.
> One note, duplicate database and TSPITR are awesome features - they
> can automate much of the process for you. But I have run into issues
> in the past where a lack of flexibility prevented me from using them.
> So I'd definitely try to use those first, and fall back to more manual
> processes if needed.

Would be interested in understanding the limitations or issues you ran into with TSPITR?

> We will soon be implementing standbys for the dev/uat systems. I'm
> currently thinking that we'll follow a similar process, but use data
> pump and copy the data from the temp restore instance to the
> destination db. I don't like the extra copy, but it might be
> necessary - also we may be implementing TDE and it's *really* a hassle
> to use TDE with TTS in 11g (almost impossible).

One thing we do with LOB expdp job is to break the table in several logical chunks and run multiple export jobs with "where clause". Kinda simulate parallelism to the LOB exports.. We pick a couple of large tables and break them this way. crude but works. :)

> Interested to hear what you come up with since it sounds so similar.
> -Jeremy
> On Fri, May 8, 2015 at 5:08 PM, Upendra nerilla <> wrote:
> > Requirement:
> > We should be able to restore the backup to the same or another database
> > environment like UAT - same version of oracle and same OS etc. Restore
> > possibility is about 40%.
> >
> > Potential solutions..
> > 1. RMAN backups: Works better and easy. Drawback is I won't be able to
> > restore the backup to another environment. My understanding is that the DBID
> > must be the same for the restore to work.
> >
> > 2. Transportable tablespaces: Probably efficient, haven't worked much with
> > it. Drawback is we'd have to transfer the files back and forth from ASM to
> > file system (if we need to send it to another environment)..
> >
> > 3. RMAN backup from standby: We do have a standby that we could potentially
> > stop the log apply. Not sure if we could perform transportable tablespace
> > operations easily on standby?
> --
> --

Received on Fri May 15 2015 - 18:36:55 CEST

Original text of this message