RE: Data backup/migration strategy..

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 Received on Mon May 11 2015 - 15:15:39 CEST