Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question re applying archive logs

RE: Question re applying archive logs

From: Vishal Gupta <>
Date: Fri, 23 Nov 2007 21:38:51 -0000
Message-ID: <000101c82e19$3e3c04c0$bab40e40$>

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 [] Sent: 23 November 2007 21:26
To: ''; '' Subject: RE: Question re applying archive logs  


What you can do is  

To revert back and rollforward



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

From: [] On Behalf Of William Wagman

Sent: 20 November 2007 18:57


Subject: Question re applying archive logs  


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.  


Bill Wagman

Univ. of California at Davis

IET Campus Data Center

(530) 754-6208




Received on Fri Nov 23 2007 - 15:38:51 CST

Original text of this message