DR Media Recovery Failed

[former_member182034]

hi dears

here i am trying to make DR of my Production server, here archives of production server are applied successfully. as you can see but when i try to cancel or alter database open resetlogs then getting errors.

SQL> startup
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2065920 bytes
Variable Size             771754496 bytes
Database Buffers          738197504 bytes
Redo Buffers               14708736 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> recover database using backup controlfile;
ORA-00279: change 266860611 generated at 08/04/2010 16:11:33 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30304_0657865393.001
ORA-00280: change 266860611 for thread 1 is in sequence #30304


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 266863231 generated at 08/04/2010 16:11:37 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30305_0657865393.001
ORA-00280: change 266863231 for thread 1 is in sequence #30305
ORA-00278: log file 'F:\ORACLE\PRD\ORAARCH\PRDARCHARC30304_0657865393.001' no
longer needed for this recovery


ORA-00279: change 266873558 generated at 08/04/2010 16:34:23 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30306_0657865393.001
ORA-00280: change 266873558 for thread 1 is in sequence #30306
ORA-00278: log file 'F:\ORACLE\PRD\ORAARCH\PRDARCHARC30305_0657865393.001' no
longer needed for this recovery


ORA-00279: change 266888138 generated at 08/04/2010 16:55:38 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30307_0657865393.001
ORA-00280: change 266888138 for thread 1 is in sequence #30307
ORA-00278: log file 'F:\ORACLE\PRD\ORAARCH\PRDARCHARC30306_0657865393.001' no
longer needed for this recovery


ORA-00308: cannot open archived log
'F:\ORACLE\PRD\ORAARCH\PRDARCHARC30307_0657865393.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> recover database using backup controlfile;
ORA-00279: change 266888138 generated at 08/04/2010 16:55:38 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30307_0657865393.001
ORA-00280: change 266888138 for thread 1 is in sequence #30307


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

+and when i try to execute following command then+
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 266888138 generated at 08/04/2010 16:55:38 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC30307_0657865393.001
ORA-00280: change 266888138 for thread 1 is in sequence #30307


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

while status of Logs is

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          0          2 UNUSED
         4          0          2 CURRENT
         3          0          2 UNUSED
         2          0          2 UNUSED

SQL> select name from v$datafile where status='RECOVER';



SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ---------------
         1          1          0   52428800          2 YES UNUSED
            0

         4          1          0   52428800          2 YES CURRENT
            0

         3          1          0   52428800          2 YES UNUSED
            0


    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ---------------
         2          1          0   52428800          2 YES UNUSED
            0

I have searched the solution on google and sdn.sap.com but of no avail, so,please guide me how can i recover this system.

[Former Member · Accepted solution]

Hi,

I think you are trying to set up a DR server. If it is the case then u need a standby controlfile created in the PRD server and then use this standby controlfile to make the DR standby and then apply the archives using the command recover standby database

If u r trying to do a recovery test with an online backup, then u need to have the all the archive logs generated during the execution of online backup. After restoring the backup, recover the database with the following command recover database. This should be executed with the database in mount state startup mount.

Once this is done u can open the database with the command alter database open resetlogs

Regards,

Varadharajan M

[former_member182034 · Accepted solution]

hi varadhu..

i have created trace file with following command.

SQL>alter database backup controlfile to trace;

then i edit this file and created control files from this file which is successfully created.

as i explained issue in my initial msg that there are archives are successfully applied but when i try to cancel to recovery or want to open database then i am getting above errors. simply i want to recover the system from current error.

Regards,

majamil

[volker_borowski2 · Accepted solution]

Hi,

you do not have applied all the redologs up to the END BACKUP statement on the source.

So you need additonal logs to make your restore consistent.

How did you copy the datafiles to your DR server ?

begin backup

copy all files

end backup

??

In this case you need

- switch logfile

AND

- recover this last logfile as well on target,

otherwise the "end backup" will not be applied to your target,

so the recovered DB is not yet consistent, because it is missing

the information from this last log up to the "end backup" statement.

Volker

[former_member182034 · Accepted solution]

Hi Volker and Nick,

basically first i make the DR from PRD which was successfully running then I copied the

sapdata1

sapdata2

sapdata3

sapdata4

from Live/online PRD to DR and after that i created trace file on PRD and edit control file like

CREATE CONTROLFILE REUSE DATABASE "PRD" RESETLOGS  ARCHIVELOGMAXLOGFILES 255

then i deleted control file from ORIGLOGA,B and SAPDATA1 and made control file from following command.

SQL> @e:\dr.txt

and i copied the archive files from PRD (\oracle\PRD\oraarch to DR server and run following command

SQL> Recover database using backup controlfile;

auto

the archives are successfully applied but some recovery successfully cancel but when i try to run

SQL> alter database open resetlogs;

then also got above error...

guide me.

[volker_borowski2 · Accepted solution]

OK,

that is the fault. You can not copy the files without telling Oracle you do so.

Instead do:

archive log list;

Take a note auf the log# which is currently active -> LOG_BEGIN#

alter database begin backup;

NOW -> do the copy of the datafiles

alter database end backup;

archive log list;

Take a note auf the log# which is currently active. -> LOG_END#

alter system switch logfile;

NOW -> copy all logs from LOG_BEGIN# up to LOG_END#

Do the rest as you did (Create controlfile, recover database until cancel, open resetlogs),

and make sure, that LOG_END# is recoverd (you can do more, but not less!)

But keep in mind, for runnning a standby DB, the procedure is a bit diffrent,

you need to create a standby controlfile and you may not open the target db.

Hope this helps

Volker

Oh, just re-checked, you did not mention your release. When you are still on

Oracle 9.2, there is no "alter database begin backup", instead do

"alter tablespace tsp_name begin/end backup;" for all tablespaces.

Edited by: Volker Borowski on Aug 8, 2010 9:42 AM

[former_member182034 · Accepted solution]

hi Volker,

thanks for this gr8 reply,

alter database begin backup;
NOW -> do the copy of the datafiles
alter database end backup;
archive log list;
Take a note auf the log# which is currently active. -> LOG_END#

alter system switch logfile;
NOW -> copy all logs from LOG_BEGIN# up to LOG_END#

onwhich server, i vl run above commands mean on DR or PRD.

fyi... when i copied datafiles file from PRD server and that time oracle was 10.2.0.4 on PRD while 10.2.0.2 on DR,

so i upgrade of DR's oralce from 10.2.0.2 to 10.2.0.4. mean now same patch on DR and PRD.

i vl try and then i vl reply back...

thanks and regards,

[volker_borowski2 · Accepted solution]

Hi,

that would be:

on PRD

> archive log list; --Take a note auf the log# which is currently active -> LOG_BEGIN#

> alter database begin backup;

Copy from PRD to DR

> NOW -> do the copy of the datafiles

on PRD

> alter database end backup;

> archive log list; --Take a note auf the log# which is currently active. -> LOG_END#

> alter system switch logfile;

Copy from PRD to DR

> NOW -> copy all logs from LOG_BEGIN# up to LOG_END#

On DR

> Do the rest as you did (Create controlfile, recover database until cancel, open resetlogs),

> and make sure, that LOG_END# is recoverd (you can do more, but not less!)

Volker

[former_member182034 · Accepted solution]

thanks Volker for explain,

NOW -> copy all logs from LOG_BEGIN# up to LOG_END#

which logs mean Redo Logs(mirrlogA&B,origlogA&B Logs), Archives, and if still there are required some other logs then

please tell me those.

if i run following command

alter database begin backup;
NOW -> do the copy of the datafiles
alter database end backup;

then which impact will be arise on PRD server mean may users face any issue(slow system, dump, etc) during execution of above commands,

regards,

Dear Volker, waiting ur response

Edited by: majamil on Aug 11, 2010 8:42 PM

where r u dear?

Edited by: majamil on Aug 12, 2010 8:11 PM

[volker_borowski2 · Accepted solution]

Hi,

have been on vacation for some days

> which logs mean Redo Logs(mirrlogA&B,origlogA&B Logs), Archives, and if still there are required some other logs then

That was refering to archive logs.

> if i run following command

>

>

alter database begin backup;
> NOW -> do the copy of the datafiles
> alter database end backup;

> then which impact will be arise on PRD server mean may users face any issue(slow system, dump, etc) during execution of above commands,

Nearly the same impact as if you run a standard online backup:

- Backup mode means higher logging to the redologs

- copy of the datafiles means additional read-io to your active datafiles

so the same as happens to a standard online backup.

And while you do this, you can not do a brbackup in addition, because it also toggles the backup mode,

so it might be set back to "normal" before your copy is ready and then the copy is rubbish.

Best regards

Volker

[former_member182034 · Accepted solution]

have been on vacation for some days 🙂

...i was thinking that you exhausted from this post.

Dear Volker, you told me solution for make the DR with sctrach and i did successfully.

but u did not give me solution of

SQL> recover database using backup controlfile until cancel;

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS 
would get error below ORA-01194: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1' 
ORA-01112: media recovery not started

SQL> alter database open resetlogs;

alter database open resetlogs * ERROR at line 1: 
ORA-01194: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

or could i can recover database from ORA-01547 or not possible.

Regards,

[volker_borowski2 · Accepted solution]

Well, in fact I did tell all that.

first: I told you to set the DB to backup mode BEFORE you copy the datafiles.

If you copied the files online without being in backup mode, nothing can be done to get this working.

second: to overcome the "needs more recovery" it is crucial to recover the archive logfile,

that has the "end backup" statement inside. This is why I advised to crosscheck the sequence

number with "archive log list", to make sure that you have at least this logfile recovered.

Volker

[former_member182034 · Accepted solution]

Dear Volker..

Conclusion of this post is following and i need only ur one reply

after copy datafile and trace file

 
SQL> startup
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2065920 bytes
Variable Size             771754496 bytes
Database Buffers          738197504 bytes
Redo Buffers               14708736 bytes
ORA-00214: control file 'G:\ORACLE\PRD\SAPDATA1\CNTRL\CNTRLPRD.DBF' version
913794 inconsistent with file 'G:\ORACLE\PRD\ORIGLOGA\CNTRL\CNTLRPRD.DBF'
version 12


SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2065920 bytes
Variable Size             771754496 bytes
Database Buffers          738197504 bytes
Redo Buffers               14708736 bytes
SQL> @d:\test.txt

Control file created.

SQL> recover database using backup controlfile;
+archive applied successfully.+


ORA-00308: cannot open archived log
'F:\ORACLE\PRD\ORAARCH\PRDARCHARC31156_0657865393.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> recover database using backup controlfile;
ORA-00279: change 296714438 generated at 08/27/2010 07:51:11 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC31156_0657865393.001
ORA-00280: change 296714438 for thread 1 is in sequence #31156


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'G:\ORACLE\PRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

why i got this error while recovery successfully cancel.


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 296714438 generated at 08/27/2010 07:51:11 needed for thread
1
ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC31156_0657865393.001
ORA-00280: change 296714438 for thread 1 is in sequence #31156


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

why now data base altered after execution of SQL> recover database using backup controlfile until cancel;
SQL>

thanks and Regards,

[volker_borowski2 · Accepted solution]

> SQL> recover database using backup controlfile;

> archive applied successfully.

Ah, ok.

Your first try was without "until ..." !

Now this is a REQUEST to oracle to recover everything without

having loss of data. If you copy archive logs, you will allways miss

the last actual log-information from the Online Log in source.

So as you requested to recover EVERYTHING, oracle is telling you

correctly, that you are not done yet ("end backup" being recovered or not).

If your are doing this way, you have to use the "until ...." clause

to get stop the recovery process somewhere, otherwise oracle will

always assume there is work left to be done.

What might work (not sure, never tried), is to shut down the source

cleanly, and copy the "current" online redolog over as well, and

recover it also. It might(?) be, that oracle detects, that the log is

consistent but not written to the end an accepts this as a "complete"

recovery, even if a backup controlfile has been provided, but again,

this is speculating, I never did that, and I found no example after a

short google.

I mind to remember, that an oracle trainer said once in a class, that

if you use a "backup controlfile" you always have to specify an unitl clause,

but I found no proof in the syntax-diagrams for "recover database" yet.

But it makes sense to me.

Volker

[former_member182034 · Accepted solution]

Dear Volker,

now when i m trying to start the these services then i m getting following error.

OraclePRD102TNSListener

error 1067 the process terminated unexpectedly

OracleServicePRD

error 1053 the service did not respond to the start or control in a timely fashion

Regards,

[former_member182034 · Accepted solution]

ok...done with the help of ORADIM utility....now DR is working fine...

thanks Volker for ur tolerant support.

regards,

majamil

[Former Member]

Update this message with your correct requirement.

Are you trying to setup new DR?

If yes, why you are using startup command?

SQL> startup

Are you are trying to recover your DB on existing DR?

If yes first you need to recover your database, then only you can start DB.

And one more thing below command is used to create a new controlfile , it never creates standby controlfile.

use sql> alter database create standby controlfile 'your path';

Regards,

Nick Loy