RE: Question re applying archive logs

I use a similar mechanism for an investment bank for their main trading system.  

They had a requirement to have a trimmed down copy made available at the end of every day in reporting, UAT, dev databases. One of main table which has about 400million records (50GB in size) could be reduced to 3 million for EOD (end-of-day) copies.  

Solution i used is transportable tablespaces:-  

1. Setup an physical standby database
2. Stop managed recover at 10pm
3. Enable flashback
4. Create guaranteed restore point
5. Convert standby database into read-write mode.
6. Put tablespace in read only mode.
7. Generate transportable tablespace set (TTS).
8. Export source code etc.
9. Transfer these files (dmp and datafiles) using DBMS_FILE_TRANSFER, so its DR proof. TNS alias takes care if database is failed over on reporting, UAT, DEV etc.
10. Plug TTS into remote db
11. Data pump import into remote db by running impdp standby DB box itself.
12. Repeat steps 9-11 for other dbs
13. Start force mount standby db
14. Flashback to restore point
15. Drop restore point
16. Disable Flashback (So that there is no flashback generation during normal day redo apply)
17. Put standby db in managed recovery mode.
18. Next day at 10pm start again from step 2.

It works very well for us. Main database is 200GB in size with undo, temp and other tablespace overheads. Copy they need after trim down of main table is only 11GB in size. And last i count there were about 25 copies of production across DEV, UAT, Reporting, Risk DBs etc.  

25 * (200GB) = 5,000GB has been reduced to 25*11GB = 275GB.  

Not bad i would say!!!      

-----Original Message-----
From: Vishal Gupta [mailto:vishal_at_vishalgupta.co.uk] Sent: 23 November 2007 21:26
To: 'wjwagman_at_ucdavis.edu'; 'oracle-l_at_freelists.org' Subject: RE: Question re applying archive logs  

Bill,  

What you can do is  

• Restore from backup
• Mount DB
• Recover until 6am.
• Enable flashback. (Make sure you have enough room in flashback area)
• Create an guaranteed named restore point.
• Open resetlogs
• let users do their changes

To revert back and rollforward

• startup force mount
• flashback to named restore point created above.
• Drop restore point (Guaranteed restored points needs to be dropped manually other it keeps using space in flashback area
• Recover until next day 6am (rollforward)
• Create an guaranteed named restore point.
• Open resetlogs
• let users do their changes

Cheers,

Vishal  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman

Sent: 20 November 2007 18:57

To: oracle-l_at_freelists.org

Subject: Question re applying archive logs  

Greetings,  

We are trying to track down a problem which occurred a month ago and the

developers have requested that I restore a database incrementally. This

is Oracle 10gR2 and we are using RMAN to do backups. They wish the

database be recovered and restored to 6:00am on a particular day, they

will look at the data. They then want to see the state of the database 4

hours later. I have restored the database to 6:00am on that day and I

have archive logs generated over the next 24 hour period. The database

is in a consistent state and open now, how do I apply the next two

archive logs? Can I do that? I haven't been able to figure it out. I

hope I am stating the problem clearly.  

Thanks.  

Bill Wagman

Univ. of California at Davis

IET Campus Data Center

wjwagman_at_ucdavis.edu

(530) 754-6208

--

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

 

 



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