Re: Database copy -- recover database question

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)

Sounds like a little bit of confusion, then. Because if you only have two groups, three log switches is sufficient to cycle right through them all and back round again. That the groups have multiple members is irrelevant to that.

> System altered.
> SQL> alter database backup controlfile to trace;

Right... you take a backup of the control file (though only to trace, which has issues of its own. Bear with me).

> SQL> shutdown

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

[snip a perfectly standard trace file, which you could have usefully done too!]

> 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.

That script contains a long list of the location of every single data file for a very good reason: it's an instruction to the system to visit every one of those files and ask what its SCN is. The brand new control file is then created with the highest SCN found force-written into its own header block. Accordingly, you don't have a backup control file at all. You have a brand, spanking new, gleamingly refreshed indeed, control file. And the command should therefore have been 'recover database'.

> ORA-00279: change 573410321 generated at 07/27/2004 22:11:01 needed
> for thread
> 1
> ORA-00289: suggestion : /oracle/Q02/oraarch/Q02arch1_8320.dbf
> ORA-00280: change 573410321 for thread 1 is in sequence #8320
> Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
>
> *****************************************???
> /oracle/Q02/origlogB/log_g14m1.dbf
> *****************************************???
>
> Log applied.
> Media recovery complete.
> SQL> alter database open resetlogs;
> Database altered.
>
>
>
> 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
Received on Sun Aug 29 2004 - 05:01:27 CDT