OraFAQ Forum: Backup & Recovery » Problem with recovery of database

Home » RDBMS Server » Backup & Recovery » Problem with recovery of database (10g r2, XP SP3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with recovery of database [message #425041]

Wed, 07 October 2009 04:45

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

Report message to a moderator

Re: Problem with recovery of database [message #425042 is a reply to message #425041]

Wed, 07 October 2009 04:55

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Problem with recovery of database [message #425049 is a reply to message #425042]

Wed, 07 October 2009 05:28

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

Report message to a moderator

Re: Problem with recovery of database [message #425058 is a reply to message #425049]

Wed, 07 October 2009 06:07

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

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

Report message to a moderator

Re: Problem with recovery of database [message #425068 is a reply to message #425049]

Wed, 07 October 2009 06:35

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Problem with recovery of database [message #425069 is a reply to message #425064]

Wed, 07 October 2009 06:35

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

Report message to a moderator

Re: Problem with recovery of database [message #425076 is a reply to message #425064]

Wed, 07 October 2009 07:10

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;

Report message to a moderator

Re: Problem with recovery of database [message #425081 is a reply to message #425076]

Wed, 07 October 2009 07:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

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

Report message to a moderator

Re: Problem with recovery of database [message #425084 is a reply to message #425081]

Wed, 07 October 2009 07:34

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.

Report message to a moderator

Re: Problem with recovery of database [message #425085 is a reply to message #425084]

Wed, 07 October 2009 07:38

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

Report message to a moderator

Re: Problem with recovery of database [message #425087 is a reply to message #425085]

Wed, 07 October 2009 07:40

ebrian
Messages: 2794
Registered: April 2006

Senior Member

No..."USING BACKUP CONTROLFILE" is not needed.

Report message to a moderator

Re: Problem with recovery of database [message #425088 is a reply to message #425087]

Wed, 07 October 2009 07:45

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

Babu

Report message to a moderator

Re: Problem with recovery of database [message #425190 is a reply to message #425041]

Wed, 07 October 2009 23:53

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

Report message to a moderator

Re: Problem with recovery of database [message #425245 is a reply to message #425190]

Thu, 08 October 2009 04:17

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

Hello Mr Pradeep_kumarp,

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

- Babu

Report message to a moderator

Previous Topic:	Issue with Oracle 8i recovery
Next Topic:	Backup size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 08:24:53 CDT 2024