Problem with in-time Recovery

[Former Member]

Hi!

I need your help. Yesterday we had to extend the Data Partition of our BW-System.

The procedure was faulty and we lost all Data (DB-Files)

Now I'm trying to recover the Database-Files and that doesn't work as I wish or expected.

The last full backup was made at the weekend, we have all offline-RedoLogs.

I want to make a in-time-Recovery using that last full Backup in combination with the Offline-RedoLogs.

• Restore DB-Files

• Rename Online Redo Logs

• make sure all control-Files are the correct ones (the files during the backup)

• followed by following commands:

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2059088 bytes
Variable Size            4227859632 bytes
Database Buffers           50331648 bytes
Redo Buffers               14716928 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 5557774378 generated at 08/19/2011 18:47:22 needed for thread
1
ORA-00289: suggestion : D:\ORACLE\BTS\ORAARCH\BTSARCHARC72733_0635875898.001
ORA-00280: change 5557774378 for thread 1 is in sequence #172733


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 5558029969 generated at 08/19/2011 22:47:21 needed for thread
1
ORA-00289: suggestion : D:\ORACLE\BTS\ORAARCH\BTSARCHARC72734_0635875898.001
ORA-00280: change 5558029969 for thread 1 is in sequence #172734
ORA-00278: log file 'D:\ORACLE\BTS\ORAARCH\BTSARCHARC72733_0635875898.001' no
longer needed for this recovery


ORA-00279: change 5558055569 generated at 08/23/2011 01:51:28 needed for thread
1
ORA-00289: suggestion : D:\ORACLE\BTS\ORAARCH\BTSARCHARC72735_0635875898.001
ORA-00280: change 5558055569 for thread 1 is in sequence #172735
ORA-00278: log file 'D:\ORACLE\BTS\ORAARCH\BTSARCHARC72734_0635875898.001' no
longer needed for this recovery


ORA-00365: the specified log is not the correct next log
ORA-00334: archived log: 'D:\ORACLE\BTS\ORAARCH\BTSARCHARC72735_0635875898.001'


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: 'E:\ORACLE\BTS\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

I have no idea why this error occures.

br

Alex

[lbreddemann]

Hi there!

You may want to check whether a "end backup" command is missing here!

Also: why don't you use the BRTOOLS for the whole process?

Would be a lot easier, especially when you're not that familiar with user managed backup/recovery on Oracle.

Also: you don't want to perform a point in time recovery here!

You definitively want to perform a full recovery up to the very last committed transaction, since you don't want to loose any data.

regards,

Lars

[Former Member]

Hi Lars,

thx for your reply as well.

Perhaps you (or someone else ) can tell me if my strategy is right.

Situation:

• I have a full offline backup from last weekend (shutdown of ORACLE and SAP and after that backup with Backup Exec)

• I have all Offline-RedoLogs beginning with the Offline Backup until the time I stopped the System to extend the Partition

• The last consistent Version of our Online-Redologs and Control-Files (there was no change) are from the backup as well

That was the plan - but didn't work until now - I have tried a lot of different ways:

• Restore of all DB-Files

• Apply REdoLogs

THX again,

Alex

[Former Member]

Hi Alex,

>> ORA-00365: the specified log is not the correct next log

>> ORA-00334: archived log: 'D:\ORACLE\BTS\ORAARCH\BTSARCHARC72735_0635875898.001'

There is something wrong with the offline redolog file. At this stage, you can apply online redolog files, as below;

recover database using backup controlfile until cancel;

ORA-00279: change 29656155255 generated at 08/23/2011 12:20:18 needed for
thread 1
ORA-00289: suggestion : D:\ORACLE\XYZ\ORAARCH\XYZARCH1_33128_683122102.DBF
ORA-00280: change 29656155255 for thread 1 is in sequence #33128


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

D:\ORACLE\XYZ\mirrlogA\LOG_G11M2.DBF <-- Enter the full path of your online redolog file

ORA-00279: change 29656247402 generated at 08/23/2011 13:20:13 needed for
thread 1
ORA-00289: suggestion : D:\ORACLE\XYZ\ORAARCH\XYZARCH1_33129_683122102.DBF
ORA-00280: change 29656247402 for thread 1 is in sequence #33129
ORA-00278: log file 'D:\ORACLE\XYZ\mirrlogA\LOG_G11M2.DBF' no longer needed for
this recovery

After the system applied the online redolog file, type "cancel" and stop the recovery process.

Please note that, you can execute this statement for each redolog file under mirrlogA. Oracle will skip online redolog file, if the sequence number does not contained in it. So, you can continue to the process by the next online redolog, in sequence.

At the end of the process, you should be able to execute the statement, below and open the database;

alter database open resetlogs;
shutdown;
startup

Best regards,

Orkun Gedik

[Former Member]

Thx for the quick answer.

hmm - now a perhaps stupid question - shouldn't be the whole content from the Online-redoLogfiles in the Offline Redologs?

Because the last consistent version of the online RedoLogs are from the same time the OfflineBackup is and I want to apply OfflineRedologs which were created after that backup!

Allover there are about 300 Offline RedoLogs I want to apply...the files shown asre only the first three of them...

Perhaps I'm acting in a wrong way (please have a look at my first posting again)

br

Alex

[Former Member]

Hi Alex,

>> shouldn't be the whole content from the Online-redoLogfiles in the Offline Redologs?

When the online redolog segment is filled up and switch to the next group, Oracle creates an offline redolog file. Let's come to your question; as I understand from your first statements that you couldn't shutdown the database respectively. At this stage, online redolog is not filled up and flush its content to the offline redolog file, respectively. Under this circumstance, online redolog file contains latest data.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

as far as I see it, in that case my Offline Redologs should contain all data I need.

Because data not written to Offline-Redologs before backup will be written to Offline Redologs when the system is back online again.

Please correct me if I'm wrong!

br

Alex

[Former Member]

Hi Alex,

>> as far as I see it, in that case my Offline Redologs should contain all data I need.

>> Because data not written to Offline-Redologs before backup will be written to Offline Redologs when the system is back online again.

Did I say something different??? As I told you previously that it depends the online redolog is full or not. You should have the data in your offline redolog file, after the offline. But, you may not have the latest data in the offline redolog files, at the faulty time. That's exactly what I mean. Not after offline.

Best regards,

Orkun Gedik

[Former Member]

Hi Alex,

>> ORA-00365: the specified log is not the correct next log

>> ORA-00334: archived log: 'D:\ORACLE\BTS\ORAARCH\BTSARCHARC72735_0635875898.001'

The root cause it looking the problem, above. In order to be safe side, I would like to ask that are you able to access this file? can you restore it again?

Best regards,

Orkun Gedik

[Former Member]

We already did that - with the same result

We now decided to bring the System back online without implementing the RedoLogs.

Thanks for your help!

br

Alex