Operating System - HP-UX
1748081 Members
5284 Online
108758 Solutions
New Discussion юеВ

one confusion about oracle

 
SOLVED
Go to solution
SeaMark_1
Frequent Advisor

one confusion about oracle

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" ??
He must cherish the hope that one day he will be amply rewarded.
18 REPLIES 18
Eric Antunes
Honored Contributor

Re: one confusion about oracle

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
Each and every day is a good day to learn.
Steven E. Protter
Exalted Contributor

Re: one confusion about oracle

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

Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Eric Antunes
Honored Contributor

Re: one confusion about oracle

In my previous response I meant to wrote:

"...and retry all the recover operation as you want until 2004-12-27:08:00."
Each and every day is a good day to learn.
Indira Aramandla
Honored Contributor

Re: one confusion about oracle

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: one confusion about oracle

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: one confusion about oracle

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
SeaMark_1
Frequent Advisor

Re: one confusion about oracle

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.

He must cherish the hope that one day he will be amply rewarded.
SeaMark_1
Frequent Advisor

Re: one confusion about oracle

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.
He must cherish the hope that one day he will be amply rewarded.
Darren Etheridge_2
Super Advisor

Re: one confusion about oracle

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)?