[Oracle] online backup of file 1 needs more recovery to be consistent

[Former Member]

Hi guys,

I have this kind of situation - I've got Oracle 11.2.0.4 database and backup created with DB13 (probably it is online backup). I want to restore system to point of creation this backup, so I've run brrestore, which copied data files to right places, then I try to run database and I got message:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Ok, so I'm trying to run alter database open resetlogs:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'E:\ORACLE\TAT\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

I tried also do an recovery with cancel:

RECOVER DATABASE using backup controlfile until cancel;

Then I hit cancel after first file (because I dont want to restore data after point of creation backup). Unfortunately, I receive this kind of message:

SQL>  RECOVER DATABASE using backup controlfile until cancel;

ORA-00279: change 931170155 generated at 06/14/2015 01:42:51 needed for thread

1

ORA-00289: suggestion : E:\ORACLE\TAT\ORAARCH\TATARCH1_457_877457716.DBF

ORA-00280: change 931170155 for thread 1 is in sequence #457

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

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'E:\ORACLE\TAT\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

ORA-01112: media recovery not started

Here I'm stucked. I tried creating new controlfile and also without any result.

Please help.

[stefan_koehler · Accepted solution]

Hi Martin,

> I want to restore system to point of creation this backup, so I've run brrestore, which copied data files to right places, then I try to run database and I got message

You need to recover the database at least to the time-point after the "online" backup has finished. This is how it works. So the earliest possible point of time with this backup is the point after the "alter database end backup" (assuming you are not using RMAN) command was issued.

> Then I hit cancel after first file (because I dont want to restore data after point of creation backup).

You need to recover to a point after the online backup has finished. Check your backup logs and recover to that archive log file.

> After adding _ALLOW_RESETLOGS_CORRUPTION = TRUE to init.ora file I've received:

... and now you can restore your whole database again and start from the scratch.

Regards

Stefan

[Former Member · Accepted solution]

Thanks. Oracle is very weird, unfriendly and ..... database. If I'm doing FULL backup, that means, that I should be able to recover to that point, in which backup STARTS, not ends. This way it works on any other databases.

I dont understand why Oracle is such a popular database, and probably I wont never.

In addition what I wrote, I can add that I've finally run RECOVER DATABASE using backup controlfile until cancel; and proceed with "Auto" options. All logs was marked as non required, and database state is the last state (just before fighting).

If someone has any idea to fix that and revert to previous settings, answer will be very appriciated.

[stefan_koehler · Accepted solution]

Hi Martin,

> I dont understand why Oracle is such a popular database, and probably I wont never.

> If someone has any idea to fix that and revert to previous settings, answer will be very appreciated.

Sorry you can not revert. You googled, found some hidden unsupported parameter, set it and knowingly corrupted your previously restored database (ORA-00600). Just restore your database once again and recover it.

> If I'm doing FULL backup, that means, that I should be able to recover to that point, in which backup STARTS, not ends.

Well ok, but just use the right tool to restore/recover the database and you would not even notice that behavior. Tool brrecover instead of brrestore.

Regards

Stefan

[fidel_vales · Accepted solution]

Martin Kovalski wrote:

Thanks. Oracle is very weird, unfriendly and ..... database. If I'm doing FULL backup, that means, that I should be able to recover to that point, in which backup STARTS, not ends. This way it works on any other databases.

Hi,

I cannot talk about other DBs (I stop doing Informix ages ago) but you do not specify what kind of backup you are doing.

If you are doing an offline backup, by definition it is "consistent", you restore it and you can open it without recovering.

If you are doing an online backup, by definition is "not consistent", you restore it and you cannot open it. You must apply redo logs to make it consistent.

It is explained in the "Oracle Database Concepts" guide

[Former Member · Accepted solution]

Thanks for the explanation and link. I'm always doing online backup, as I cannot make downtimes on SAP servers.

I will start making backups by expdp.... For me full backup is always complete, it doesnt matter if its online or offline..

[fidel_vales · Accepted solution]

Hi,

expdp is not a backup tool, you will be in a worse situation if you use it.

It seems that you do not understand properly how are backups, consistency, complete, full and those things (or I'm totally mistaken on how Oracle works, which might be also possible).

Running EXPDP online will have also an inconsistent DB (unless you do the export "consistently" which would take forever in a busy system) You will be able to open that "backup" and you will have a nightmare finding missing data. But you will not know that you are missing data until a user ask you where is such and such data entered, committed and confirmed.

A FULL backup is a backup of everything, complete, all files. And you are doing that. But the backup also have to be CONSISTENT.

But it seems that you do not grasp the difference between ONLINE and OFFLINE.

If you take an online backup it will not be consistent by itself. The changes to the files being done while the backup is running (lets say 1 hour) are recorded on the redo log files. You will need the backup plus the redo log files. Therefore, you can only recover the backup until the time AFTER the backup is finished as it is the only point in time you have a consistent database (meaning you have all the changes that have been done and you can open the database)

one of the "good" things of oracle is that is designed so you do not lose data and it does that job pretty well (apart of some bugs that will appear from time to time)

[Former Member · Accepted solution]

Thanks - thats a pretty clear explanation.

About expdp it was some kind of joke

I'm not an DB expert - I just dont like Oracle at all for being so complicated at simple processes - like all that tablespaces, backups and buffers. Doing anything is big pain, on the opposite we have DB2 or MSSQL, where all this stuff is automatic or even does not exist.

Probably I need to learn more and more to be a good Oraadmin.

Discussion is going in wrong way - sorry for that

Maybe one of you guys could provide name and author of good book with Oracle basics?

[fidel_vales · Accepted solution]

Hi,

Sorry, I did not got the joke (I've seen it done "seriously")

Yes, Oracle is "complicated", but it gives you loooooots of options (and some times too many)

The oracle documentation is quite good (now), I'd recommend you starting with the "concept" guide, the one I linked before. You can start with the Oracle 11g, probably what you will currently see in the SAP world
Depending where you want to go you can use the "2 Day DBA" (more orientated to the enterprise manager) or "Administrator Guide" if is administration what you are interested. "Backup and Recovery Users Guide" is also "essential" for administrators. "Performance Tuning Guide" alos.

But understanding the "architecture" (concepts) is the key.

If you want a book I strongly recommend "Expert Oracle Architecture" from Tom Kyte.

There are tons of good books. but also bad ones. I cannot tell you which ones because I could be "sued"

[Former Member · Accepted solution]

Thanks a lot! I really appreciate your answers

[Former Member]

After adding _ALLOW_RESETLOGS_CORRUPTION = TRUE to init.ora file I've received:

SQL> ALTER DATABASE OPEN RESETLOGS ;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [931170165], [0],

[931173352], [8896577], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [931170164], [0],

[931173352], [8896577], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [931170162], [0],

[931173352], [8896577], [], [], [], [], [], []

Process ID: 14204

Session ID: 76 Serial number: 5

I dont belive, that backup is wrong... It will be VERY stupid, that if I done FULL backup (even online) and I cannot restore to point of this backup...