one confusion about oracle

[SeaMark_1]

In fact, I have recovered the database at '2004-12-27:10:00' first time and 'alter database open resetlogs' . But from then to now, other reason need me do this again.


This time, I restore all files into exact directorys(datafiles,control files and online/offline redo logs) and the backup time is '2004-12-27:03:00:00'. So, I want to appply archieved logs until '2004-12-27:08:00:00' and met this error.

SQL> recover database until time '2004-12-27:08:00:00' using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/R3D/sapdata1/system_1/system.data1'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/R3D/sapdata1/system_1/system.data1'


I notice, '2004-12-27:08:00:00' is before '2004-12-27:10:00:00' and how does oracle know when I open it with "resetlogs" ??

[Eric Antunes]

Hi SeaMark,

Oracle archiving can only process recovery requests from the past to the future, not the opposite. As you said you've already done a "alter database open resetlogs" at 2004-12-27:10:00, so you can't do any recovery before this date and time with this open database.

But you can put all the backup files (datafiles, redologs and control) again in there place ready to recover (erasing the present database) and retry all the recover operation as you want until 2004-12-27:10:00.

Best Regards,

Eric Antunes

[Steven E. Protter]

Seems the control files disagree with what state this file should be in:

'/oracle/R3D/sapdata1/system_1/system.data1'

When you restored, you restored all control files? When you backed up was it hot or cold, rman or other methodology?

SEP

[Eric Antunes]

In my previous response I meant to wrote:

"...and retry all the recover operation as you want until 2004-12-27:08:00."

[Indira Aramandla]

Hi SeaMark,

From you post I understand that

You have a database backup as of at 03:00 hours on 27-12-2004.

You recovered the database and applied the logs till 10:00 hours on 27-12-2004 and opened the database with resetlogs option.

Next time you wanted to recover the database as of 08:00 hours on 27-12-2004.

I agree with Eric that archive logs can be applied to recover an instance to a time in the future.

Now if you wanted to recover the database to 08:00 hours then you will have to shutdown the database that you opened earlier, and restore the datafiles, control files and redologs again into their respective directories and then issue the command

SQL> Recover database using backup contorlfile until time â 2004-12-27:08:00â ;

You should be able to recover it and open the database. May be earlier you had the control files and datafiles belonging to different times.

I hope this helps
In

[Yogeeraj_1]

hi,

seems like a problem with your control files.

i would recommend that you use RMAN for backup and restore. The management will be much easier.


Please refer to metalink note: 73974.1 - RMAN: Restoring an RMAN backup to another node.

regards
Yogeeraj

[Sanjay Kumar Suri]

I agree with Indira. Check that all the datafiles, online/offline redo log file and control files are restored properly before starting the recovery process.

Also check the value of control_files in the parameter file.

sks

[SeaMark_1]

Thanks for all responses.
I guess I mislead you in some way.

I describe the situation in details.

1) I have cool backup at 2004.12.27 3:00.
That afternoon, I restore all files and done an incomplete recovery until 12.27 10:00 and open database with "resetlogs".

2) At 2004.12.19, another recovery must be done. I still restored those files(backuped at 2004.12.27 3:00) and want to do more time just as last time, only differece is that time-point is 8:00 not 10:00. but the error occured.

3) I use backup method provided by SAP.

I try more time and get it over.

a) restored all files.
b) recover database
c) recover database until '2004-12-27:08:00:00' using backup controlfile
d) alter database open resetlogs

All the best.

[SeaMark_1]

one mistake.

I want to say

2) At 2004.12.29, another recovery must be done. I still restored those files(backuped at 2004.12.27 3:00) and want to do more time just as last time, only differece is that time-point is 8:00 not 10:00. but the error occured.

[Darren Etheridge_2]

I'm kinda new to Oracle 9.2.1 and was wondering... Is there a disadvantage to doing a COLD backup of a database.. The restore seems seemless (just restore the files and that's it)?

[Yogeeraj_1]

Darren,

COLD backup has the biggest disadvantage - DOWNTIME!

I still wonder why people still do COLD backup, when with HOT backup, we have uptime and recovery procedures are not too complicated and we can recover till the very last transaction...

regards
Yogeeraj

[Darren Etheridge_2]

Since our operataion is from 9-5 and downtime isn't an factor is there a disadvantage?

[Eric Antunes]

Seamark,

Your 2) subject is the same as in previous post.

If it worked at the first time for 10:00, it must work with 08:00 since the cold backup is from 03:00! You must have done something different the time...

Darren,

Here I desagree with Yogeeraj... I use cold backup since the very begining and had recover only once: I loosed 2 hours of work but that wasn't critical for my organization because those 2 hours of data will never cover the money and time will needed to spend to garantee them with archive logs!

So, you have to evaluate what are the essencial needs for your organization:

- Restore time;

- Maximum data you can loose (in hours);

- Downtime you can have;

- Database volatility (changing speed);

- Disk space available for archive logs and money available to buy more if they are needed, ...

Best Regards,

Eric Antunes

[SeaMark_1]

hi,Eric

Thanks you for reply.

Really, another difference thing ignored is when I recover Oracle to 8:00 in the second time, I don't do "recover database" before "recover database using backup controlfile" and got that error. So, I do again following my last post, which is just like my recovery to 10:00 in the first time.

So, I have some confusion.

I believe doing "recover database" before "recover database using backup controlfile" seems to be insignificant.

Do you think?

---------------

About doing cold backup, you listed many factors concerned with and I take my side on you.

But you may neglect another important factor --- person.

I don't support COLD BACKUP.
In the beginning, I ONLINE backuped Oracle but an awful boss can destroy everything.
:-)
He made an opinionated decision that bakcup Oracle must be like backup AS/400(he is very skillful about AS/400.). I am new clerk and can't persuade him, which results in current situation directly.

Badly ~~~

[Sanjay Kumar Suri]

Situations Requiring Incomplete Recovery

1. Complete recovery fails because an archived log is lost.
2. All unarchived redo log files and a datafile are lost.
3. User error
- An important table was dropped.
- Invalid data was committed in a table.
4. Current control file is lost and a backup control file must be used to open the database.

One need to give the following command only once for the recovery.

recover [automatic] database until time 'yyyy-mm-dd:hh:mm:ss' using backup controlfile.

A combination of Online/offline (cold) backups are used depending on the crticality of the database and 24x7 operations where database shutdown is not possible.

sks

[SeaMark_1]

I want to know one key-point.

If I own all files(data files and control files) and roll Oracle forward, I use "recover database " or "recover database using backup cotrolfile" ??

I found do "recover database until cancel", trace file tell me "Media recovery is not necessary" and do nothing.

And with the same files I do "recover database using backup controfile until cancel ", the database really roll forward.

Why? Who can tell me?

Thanks.

[Sanjay Kumar Suri · Accepted solution]

In case of complete recovery, one should always use "recover database" and this process
1. No need to open the database with resetlogs.
2. New incarnation of database does not happen.
3. A complete backup is not mandatory.

In case of incomplete recovery, one should always use "recover database until or using backup controlfile" and this process
1. Always need to open the database with resetlogs.
2. New incarnation of database happen.
3. A complete backup is mandatory (preferably cold).

The message "Media recovery is not necessary" in case of "recover database" comes beacause control file, database files and redo log files are all in sync and no recovery is required.

Whereas in case of "recover database until cancel using backup controfile", Oracle tries to simulate an incomplete recovery scenario (even though backup file is matches that of other files).

sks

[Eric Antunes]

Hi SeaMark,

In you specific situation, you must do:

- Restore all datafiles;
- Do not restore controlfiles and redolog files;
- Mount database (startup mount);
- recover database until time '2004-12-27:08:00:00';
- alter database resetlogs;

Best Regards,

Eric Antunes

[SeaMark_1]

Thanks everyone !!
I have found my way.