Re: Clone from hot backup

From: makbo <makbo_at_pacbell.net>
Date: Thu, 20 Nov 2003 22:56:34 GMT
Message-ID: <3FBD46A1.6040309@pacbell.net>

Sybrand Bakker wrote:

> On 20 Nov 2003 08:51:21 -0800, iapdk_at_admin.drake.edu (Paul Kline)
> wrote:
> 
> 

>>We upgraded from 8i to 9i last month. Under 8i I frequently cloned
>>our production database to a test version using the hot backups
>>created every night (alter tablespace start/end backup and copy the
>>datafiles). I have done this dozens of times with no problems. I
>>copied the datafiles and redo logs to the new path, rename them, copy
>>the redo logs to new names, create a control file by getting a trace

> 

>>from production and changing the first two lines to:

> 

>>STARTUP NOMOUNT
>>CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
>>
>>After executing the control file script I enter:
>>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
>>
>>Under 8i I would feed it the redo log copies until media recover
>>completed. Under 9i it never completes, just keeps requesting more
>>logs. Even when I supply copies of the archive logs from production
>>up to the latest it never completes.
>>
>>Can this be done under 9i?
>>Could it be that a new process is being run by someone that keeps a
>>commit open for a very long time?
>>???
>>
>>We have gone to auto undo also.
>>
>>Thanks for any help.
>>
>>Paul Kline
>>DBA - Drake University

> 
> 
> 
> Obviously you have always been lucky in Oracle 8i, as you have been
> using an improper procedure to clone a database.
> 
> 
> --
> Sybrand Bakker, Senior Oracle DBA

You can take a hot backup and successfully open it using a backup controlfile, resetting on-line redo logs, under both 8i and 9i. I found that it worked under 9i just as it did under 8i.

What message do you get when you respond with "CANCEL" to the prompt from the RECOVER command, when it prompts you for the next archive redo log beyond the last one you have available? If it says "Media recovery cancelled", then you should be able to "alter database open resetlogs" and be in business (after creating any necessary TEMPFILES for your temporary tablespaces, which are never included in the hot backup).

If instead you get a message to the effect that some tablespace still needs media recovery, it would indicate that you still need to apply more archive redo logs.

A long-running, uncommitted transaction would make no difference to this process (other than the extra time required to apply the uncommitted redo).

--Mark Bole Received on Thu Nov 20 2003 - 16:56:34 CST