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@xxxxxxxxx
To: nupendra@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx> 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
