Home » RDBMS Server » Backup & Recovery » Problem with recovery of database (10g r2, XP SP3)
Problem with recovery of database [message #425041] Wed, 07 October 2009 04:45 Go to next message
pradeep_kumarp
Messages: 15
Registered: October 2009
Location: Mumbai
Junior Member
Hi,

I am Pradeep. I got 'oradata' folder from another office and I am performing a recovery from oradata. I have very little knowledge about oracle. I have followed the procedure below:

1. created a new sid using : oradim -new -sid spmatt
2. connected to sqlplus "/as sysdba"
3. SQL> startup nomount pfile= <pfile location>
4. SQL>@<location of control file>
5. SQL>recover database using backup controlfile;
6. I provided the 'REDO.LOG' files. (There are 3 of them- REDO01.LOG, REDO02.LOG, REDO03.LOG)

After providing all 3 log files one after the other, i get this message

'ORA-00310: archived log contains sequence 3; sequence 4 required'

The problem is i dont have any more redo logs to provide.

Previously when we recovered the same oradata at another place, only 3 redo logs were required and we could recover database properly. Now its asking for sequence 4 which i dont have.

Please help me out with this issue.

Pradeep
Re: Problem with recovery of database [message #425042 is a reply to message #425041] Wed, 07 October 2009 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do not describe what you have, show us: copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Problem with recovery of database [message #425049 is a reply to message #425042] Wed, 07 October 2009 05:28 Go to previous messageGo to next message
pradeep_kumarp
Messages: 15
Registered: October 2009
Location: Mumbai
Junior Member
im sorry for that..please find my session below:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\admin>oradim -new -sid spmatt
Instance created.

C:\Documents and Settings\admin>set oracle_sid=spmatt

C:\Documents and Settings\admin>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 7 14:20:12 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile=E:\oracle\product\10.2.0\admin\spmatt\pfile\initSPMAT
T.ora;
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 1296764 bytes
Variable Size 327157380 bytes
Database Buffers 33554432 bytes
Redo Buffers 7090176 bytes
SQL> @E:\oradata\SPMATT\controlfile.sql;

Control file created.

SQL> recover database using backup controlfile;
ORA-00279: change 453157767 generated at 09/22/2009 14:03:25 needed for thread
1
ORA-00289: suggestion :
E:\ORADATA\SPMATT\U_DUMP\SPMATT\ARCHIVELOG\2009_10_07\O1_MF_1_4_%U_.ARC
ORA-00280: change 453157767 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oradata\SPMATT\log\REDO01.LOG
ORA-00310: archived log contains sequence 1; sequence 4 required
ORA-00334: archived log: 'E:\ORADATA\SPMATT\LOG\REDO01.LOG'


SQL> recover database using backup controlfile;
ORA-00279: change 453157767 generated at 09/22/2009 14:03:25 needed for thread
1
ORA-00289: suggestion :
E:\ORADATA\SPMATT\U_DUMP\SPMATT\ARCHIVELOG\2009_10_07\O1_MF_1_4_%U_.ARC
ORA-00280: change 453157767 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oradata\SPMATT\log\REDO02.LOG
ORA-00310: archived log contains sequence 2; sequence 4 required
ORA-00334: archived log: 'E:\ORADATA\SPMATT\LOG\REDO02.LOG'


SQL> recover database using backup controlfile;
ORA-00279: change 453157767 generated at 09/22/2009 14:03:25 needed for thread
1
ORA-00289: suggestion :
E:\ORADATA\SPMATT\U_DUMP\SPMATT\ARCHIVELOG\2009_10_07\O1_MF_1_4_%U_.ARC
ORA-00280: change 453157767 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oradata\SPMATT\log\REDO02.LOG
ORA-00310: archived log contains sequence 3; sequence 4 required
ORA-00334: archived log: 'E:\ORADATA\SPMATT\LOG\REDO03.LOG'



Now i dont have anymore redo logs to provide. How do i get thru this.


Pradeep
Re: Problem with recovery of database [message #425058 is a reply to message #425049] Wed, 07 October 2009 06:07 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
E:\oradata\SPMATT\controlfile.sql


Post your controlfile statement.

How your taking backup from source database?

PS: Please use code tag for posting SQL Codes

[Updated on: Wed, 07 October 2009 06:10]

Report message to a moderator

Re: Problem with recovery of database [message #425064 is a reply to message #425041] Wed, 07 October 2009 06:33 Go to previous messageGo to next message
pradeep_kumarp
Messages: 15
Registered: October 2009
Location: Mumbai
Junior Member
My control file is as below:

Create controlfile reuse SET database "SPMATT"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'E:\oradata\spmatt\dbf\JCS01.DBF',
'E:\oradata\spmatt\dbf\M_INDEX01.DBF',
'E:\oradata\spmatt\dbf\MARIAN01.DBF',
'E:\oradata\spmatt\dbf\XDB01.DBF',
'E:\oradata\spmatt\dbf\SYSTEM01.DBF',
'E:\oradata\spmatt\dbf\UNDOTBS01.DBF',
'E:\oradata\spmatt\dbf\SYSAUX01.DBF',
'E:\oradata\spmatt\dbf\USERS01.DBF'
LOGFILE GROUP 1 ('E:\oradata\spmatt\log\REDO01.log') SIZE 51200K,
GROUP 2 ('E:\oradata\spmatt\log\REDO02.log') SIZE 51200K,
GROUP 3 ('E:\oradata\spmatt\log\REDO03.log') SIZE 51200K, RESETLOGS
;


We received the 'oradata' folder of the source database. now we are trying to restore it from oradata folder itself.

Thanks
Pradeep
Re: Problem with recovery of database [message #425068 is a reply to message #425049] Wed, 07 October 2009 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Now i dont have anymore redo logs to provide. How do i get thru this.

So CANCEL is the answer you have to give.

Regards
Michel
Re: Problem with recovery of database [message #425069 is a reply to message #425064] Wed, 07 October 2009 06:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
>>We received the 'oradata' folder of the source database. now we are trying to restore it from oradata folder itself


Received mean COPY/PASTE or Your taken any hot backup from your source server.

- Babu
Re: Problem with recovery of database [message #425076 is a reply to message #425064] Wed, 07 October 2009 07:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you have everything (including online redo logs) EXCEPT the controlfiles, then why not just recreate the controlfile and mount the database and then issue:

RECOVER DATABASE;
ALTER DATABASE OPEN;
Re: Problem with recovery of database [message #425081 is a reply to message #425076] Wed, 07 October 2009 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe because if you recreate the control file (with create controlfile) you have to use "using backup controlfile" clause and do an "until cancel" recovery.

Regards
Michel
Re: Problem with recovery of database [message #425084 is a reply to message #425081] Wed, 07 October 2009 07:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Michel Cadot wrote on Wed, 07 October 2009 08:26
Maybe because if you recreate the control file (with create controlfile) you have to use "using backup controlfile" clause and do an "until cancel" recovery.

Not at all. It's a "new" controlfile, it's not a backup. Therefore, you don't have to use "USING BACKUP CONTROLFILE" and you are not doing a PITR recovery.
Re: Problem with recovery of database [message #425085 is a reply to message #425084] Wed, 07 October 2009 07:38 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello Mr Ebrain,

Need to one clarification here. If the same recover doing to new operating system & trying to recover database using text based controlfile. (Having only datafile backup (from hot backup))

We may need to go with "USING BACKUP CONTROLFILE" I'm correct?

Thanks

Babu
Re: Problem with recovery of database [message #425087 is a reply to message #425085] Wed, 07 October 2009 07:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
No..."USING BACKUP CONTROLFILE" is not needed.
Re: Problem with recovery of database [message #425088 is a reply to message #425087] Wed, 07 October 2009 07:45 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Ok. Thanks for your answer. I need to check & then I'll confirm you.

Babu
Re: Problem with recovery of database [message #425190 is a reply to message #425041] Wed, 07 October 2009 23:53 Go to previous messageGo to next message
pradeep_kumarp
Messages: 15
Registered: October 2009
Location: Mumbai
Junior Member
as michel said, i tried using 'UNTIL CANCEL USING BACKUP CONTROLFILE' and it worked.

Thank you all for your support.

Pradeep
Re: Problem with recovery of database [message #425245 is a reply to message #425190] Thu, 08 October 2009 04:17 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello Mr Pradeep_kumarp,

Thanks for your feedback. Can you post your log details. I would like double check your log.

- Babu
Previous Topic: Issue with Oracle 8i recovery
Next Topic: Backup size
Goto Forum:
  


Current Time: Sun Dec 22 18:06:52 CST 2024