Re: Data backup/migration strategy..

Providing you have each of your "schemas" isolated in an independent set of tablespaces, you ought to be able to use TSPITR. In essence, it is the procedure I described earlier, but RMAN automates most of the process.

One thing that TSPITR cannot do, though, is backout changes to stored PL/SQL blocks. If you think you will need to do that, then export/import may still be the best method.

On Mon, May 11, 2015 at 9:15 AM, Upendra nerilla <nupendra_at_hotmail.com> wrote:

> Hi Mark,
> Thanks for your suggestions.
>
> My preference would be to use the RMAN backups as well. I will investigate
> the clone database feature and see how it could be leveraged..
> To answer your question on the frequency of the export/imports, on a
> weekly basis I see a couple of requests for exports, this is likely to
> increase. I agree that the frequency of the imports will be much less
> compared to exports. There are a couple of reasons for the import request..
> 1) Migrating schema across environments - this is known before we start the
> export. 2) Application rollback due to some issues.
>
> If we hit scenario#2, then I need to be able to import the data ASAP, so
> I'd like to complete whatever steps I could upto the point of actual
> restore. I am not sure if all those steps could be automated.. If anyone
> have any suggestions on automating these steps that would be helpful.
>
> Thanks
> -Upendra
>
> ------------------------------
> Date: Fri, 8 May 2015 20:59:09 -0400
> Subject: Re: Data backup/migration strategy..
> From: mark.brinsmead_at_gmail.com
> To: nupendra_at_hotmail.com
> CC: oracle-l_at_freelists.org
>
>
> This one probably deserves some extra thought, but my initial instincts
> direct me to your method #3.
>
> If you have a physical standby already, then it should be practical to
> suspend replication, open the standby, export the tablespaces you want as a
> TTS set (make one set for each schema, of course, so you can import
> individually), and then resume standby operations.
>
> Its something I have never tried before, but I see nothing to prevent this
> from working.
>
> The next best alternative would be to rely on your RMAN backups, I think.
> For this purpose, you would need to "clone" a new database from the backups
> (but you won't need ALL tablespaces, just enough to export the schemas you
> want). Then open the "clone" database, and export the tablespaces you are
> interested in as a TTS set. That can then be imported wherever you want it.
>
> This is pretty much what TSPITR does under the covers. When you are
> prepared to do some of the steps manually, you can remove some of the
> restrictions.
>
> Maybe "rely on the RMAN backups" will be the better choice. It depends on
> how often you need to make these "exports", and more importantly, how often
> you need to restore them. If you restore infrequently, then the "rely on
> RMAN" method will probably be much more efficient in the use of storage.
>
> ---
>
> Now, I will wait to see what mistakes I have made. :-)
>
> On Fri, May 8, 2015 at 6:08 PM, Upendra nerilla <nupendra_at_hotmail.com>
> wrote:
>
> Hello Everyone -
> I am trying to solve a problem, I am hoping some of the smart minds here
> could find a solution for this.. Thanks in advance.
>
> Problem: On a regular basis we need to perform schema backups that contain
> large LOBs.. the schema sizes range 5G to 1.5TB. Each schema has its own
> tablespace(s). Current method we are following is - stop write activities
> on that schema and use datapump to backup. Datapump worked good when the
> schema sizes were small, now they are getting larger and larger. Datapump
> job takes several hours and in some cases much beyond the backup window.
> This is painful and not really scalable.
>
> My objective is to reduce the backup window and make it efficient. Trying
> to perform a backup at the physical level instead of logical..cd tai
>
> 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%.
>
> Constraints:
> We have databases running on 10g and 11g. This environment has data guard
> (physical standby) databases. Any solution we identify should integrate
> with it.
>
> 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?
>
> 4. Open to suggestions..
>
> Thanks much in advance.
>
> -Upendra
>
>
>

--
http://www.freelists.org/webpage/oracle-l