Re: Database copy -- recover database question

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 02 Sep 2004 01:36:45 GMT
Message-ID: <NOuZc.14321$hR7.2221@newssvr27.news.prodigy.com>

Howard J. Rogers wrote:

> James wrote:
>
>

>>I am coping SAP/Oracle database R3Q to Q02,
>>
>>For R3Q:
>>SQL> connect / as sysdba
>>Connected.
>>SQL> alter system switch logfile; (4 times, we have 2 groups , 2
>>memebers per group)

> SQL> shutdown
> Database closed.
> Database dismounted.
> ORACLE instance shut down.

[...]
>
>

>>SQL> recover database using backup controlfile

>
>
> Wrong command. I don't know why people insist on doing this, but they
> invariably do. There was a rash of posts doing it unnecessarily a few
> months back. The phrase 'using backup controlfile' has a very specific
> meaning: "I am using a control file which was backed up some time ago".
> You, like most other people I see using that command, are doing no such
> thing. You are using a control file which has just been re-created from
> scratch by virtue of the trace script technique.
>

[...]

>>
>>
>>Question: why media recovery is needed? 

>
>

>>I've switched all redologs to 
>>database and close database before database copy. 
>>All database files 
>>should be in consistant status 

>
>
> But when you tell Oracle your control file is out of date when it isn't, you
> are setting yourself up for quite a lot of unnecessary hassle, aren't you.
>
> Did you try a simple 'recover database'? What happened if so?
>
> Regards
> HJR
>
>

>>and should be "open resetlogs" 
>>successfully without media recovery.
>>
>>Thanks
>>JamesYang_at_163.net

>
>

What Howard said was correct. But I would take it one step further. You don't need to recover your database in this scenario, and you don't need to switch any redologs, and you don't need to create a backup controlfile -- since you have the luxury of performing a cold (closed database) backup.

If you copy every file of a closed database (datafile, tempfile, controlfile, online redo log file) to another server in the same directory locations, how is the software to know? Every byte is in the right place, so it will start up OK, just as if it were the original.

Now, the Oracle documentation will tell you that you should never make backup copies of your online redo log files, for some very good reasons. However if you understand those reasons and are willing to take responsibility for your actions, you can indeed backup your (closed database) on-line redo log files to another server and successfully startup your database with no explicit recovery required.

Think "image backup at the OS level". Again, this only works with a closed database file-level copy.

In the case where your copy cannot use the exact same directory structure as your source, you can use the following (s)pfile parameters to adjust for that:

db_file_name_convert
log_file_name_convert

--Mark Bole Received on Wed Sep 01 2004 - 20:36:45 CDT