For Scenario #2...
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@xxxxxxxxxxx>
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@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
