Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Testing Refresh Procedure

RE: Testing Refresh Procedure

From: hitender chugh <>
Date: Wed, 22 Mar 2006 16:54:56 -0500
Message-ID: <BAY105-F1814358071BC16F009F3A4BDD90@phx.gbl>

My requirements is also similar where I have to refresh the lower regions (databases - development,system,acceptance etc.) from production data but not the functions,procedures,packages etc. very frequently. I use exp, and imp data after disabling triggers,fk constraints and truncating tables.

Is there any better procedure?

Can this RMAN technique work if tablespaces, datafiles are named differently than Production's?

Moreover the objects in tablespaces in lower region might be different(more or less in number) than in Production. How do you handle that? Like in Lower region we might have a new table in a tablespace but which is not there in Prodcution.

CAn any body provide some guidance whether Oracle streams can be used for the refresh procedure? Can the Oracle streams be scheduled as nightly job and can be switched on or off at will? Like if it can be scheduled as night job say on Monday night for database A can this be rescheduled to run on Tuesday night for the database.


From:  Daniel Fink <>
Subject:  RE: Testing Refresh Procedure
Date:  Wed, 22 Mar 2006 13:19:04 -0800 (PST)

Make a sign and hang it above your monitor so you see it every day

"The job of the dba is not to back up the database, but to recover the database." (To paraphrase Tim Gorman)

"Coleman, Kelley (HAC)" <> wrote:
It’s backed up nightly, fulls on Sundays, incrementals the rest of the week.  I have not tested the backups, it’s always been one of those “I really need to do that one of these days…” type activities. Given the suggestions from this group, I will be pursuing the RMAN approach to refreshing the lower DBs.
From: Dennis Williams []
Sent: Wednesday, March 22, 2006 1:49 PM
To: Coleman,  Kelley (HAC)
Subject: Re: Testing Refresh Procedure
Is the database backed up on a regular basis? Do you trust the procedure? How recently has it been tested? Ever?
I advocate where possible, creating the test and development databases by recovering a backup. Nobody has enough time to test backups. But if you can combine it with the request to create a test or development database then you have a double winner.
Dennis Williams

On 3/21/06, Coleman, Kelley (HAC) <> wrote:
Does anyone have a standard procedure for doing db refreshes that they'd be willing to share? I'd be going from Production down to test and development dbs. These were usually done by a co-worker who recently moved on to greener
pastures.  He didn't leave any desk procedures, so I've been winging it, but it seems like I'm making it harder than it needs to be.
Should I drop relevant schemas before importing, so it's basically from scratch?
I know the pastures guy would essentially do two imports, one just of table structures – no rows – then one just of data.  Does that seem like a sound procedure?
Kelley Coleman
Database Administrator
VA Health Administration Center
Denver, Colorado

Confidentiality Note:  This e-mail is intended only for the person or entity to which it is addressed, and may contain information that is privileged, confidential, or otherwise protected from disclosure.  Dissemination, distribution, or copying of this e-mail or the information herein by anyone other than the intended recipient is prohibited.  If you have received this e-mail in error, please notify the sender by reply e-mail, phone, or fax, and destroy the original message and all copies. Thank you

-- Received on Wed Mar 22 2006 - 15:54:56 CST

Original text of this message