I still don't understand it...
Juan Guascarancas Pena, September 12, 2002 - 1:13 pm UTC
Tom,
I'm still a little bit confused about this.
Does SCN get printed on the control file? When?
Lets see this scenario:
monday:
hot backup database and control file.
tuesday:
a lot of data is inserted on the database.
wednesday:
a crash involving both the control file and datafiles occur. The redo log and archived redo files are intact, though.
Please correct me, but I think things go like this:
1) We shutdown the database (perhaps with ABORT option?)
2) We restore the control file and datafiles from our backup
3) We start SVRMGRL and ask for a RECOVER DATABASE
4) Oracle will see what the SCN on the datafiles is and roll them forward up to the SCN on the redo log, using the archived redo and the online redo.
Questions:
Why should I reset the redo log files? What part does the control file play on the recover process? Why can't Oracle update the control file to reflect the SCN on the redo log?
I understand that when we make incomplete recovery, the logs must be reset otherwise the data that we didn't apply from the log to the datafile could be used on another future recovery, which is not what we want.
Another doubt regards the archived redo. When can I safely delete them?
Please help me with this!
Juan
September 12, 2002 - 4:20 pm UTC
does it get into the control file -- yes, when -- almost continously.
Your scenario is wrong, it would go more like this (assuming you did 1,2,3):
1) shutdown abort
2) restore control file and datafiles
3) do a recover database
4) database is recovered to the point in time of the backup -- cause that is what the control files say to do
You should
o restore control/data
o RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
o hit enter whilst we suggest what archives we think we need
o tell us about the online redo
o cancel the recovery.
the control file tells us how to recover (has the archive history). using a backup control file wipes out our "memory" if you will.
We cannot use the "scn" from the redo since
a) we have no idea about the redo -- the control files tell us about them, chicken and egg.
b) we have no idea where in the redo stream we were when we "died"
You need to read a document, from start to finish. this is totally important stuff.
</code>
http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76993/toc.htm <code>
query
atul, September 13, 2002 - 5:47 am UTC
Sir,
as we lost control file and only 3 datafiles out of 20..
as your method said restore control+datafiles,
We have to restore all 20 datafiles + controlfile as we are doing incomplete recover,
Is it correct?
Thanks.
atul
September 14, 2002 - 1:50 pm UTC
well, if you lost only one control file, you should have the other 2 right?
(this is perhaps the worst time to be learning how to recover isn't it? Its pretty frightening)....
Before you do ANYTHING, read the manuals -- get a good backup of what you got. Perhaps if you've never ever done this yourself (bad bad idea, you need to PRACTICE THIS in good times so the bad times aren't so bad) you should open a TAR and let them pilot you through the process.
Controlfile backed up with no resetlogs option...
Subhrajyoti Paul, September 13, 2002 - 9:05 am UTC
Tom,
There is an option to create a controlfile backup to trace file with NORESETLOGS option. Using this controlfile backup to recreate the controlfile and then recovering the database does not require the use of OPEN RESETLOGS. How is that done, if what I am saying is right?
September 14, 2002 - 2:03 pm UTC
When you do a complete recovery, you do not need to resetlogs. You need to reset logs when there is redo in the redo logs that could be applied --but wasn't. Most common with point in time recoveries.
Read the manuals but still...
Juan Guascarancas Pena, September 13, 2002 - 4:31 pm UTC
Hey Tom, I read the document you suggested from start to finish today (is not being a busy friday after all) but there I could only find WHEN to use resetlogs not WHY to use it.
Let me try again:
1)You said:
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
How will Oracle do it, if the old control file doesn't have the archived log info?
My guess (I may be very wrong) is that Oracle gets the SCN from the online logs, and perhaps the name of the archived logs from the data dictionary (I can find this info there, right?) and tries to find and apply the archived log contents to data files. Why doesn't Oracle then register this SCN and the list of archived logs that it has successfully applied to the control file, making it a happy new updated and current control file? Why do I need the resetlogs?
Tom, I now you are very busy, and perhaps I'm just being stupid and not seeing what is right in front of my nose with this resetlog stuff. I could very well print a piece of paper saying "always do a resetlog when using a backup control file or imcomplete recover", but I would very much prefer to understand the mechanics behind Oracle. So don't be mad at me, huh?
I'll try creating a database on monday, and deleting its control file, then I'll see how things go. I never run through this recovery procedure before, perhaps it's self explanatory, who knows?
Thanks in advance
Juan Pena, from Mexico
September 14, 2002 - 3:04 pm UTC
You need to resetlogs when doing a point in time recovery - when stuff is left in them that wasn't applied.
You need to do a resetlogs when you lose (or have damaged) the online redo logs (resetlogs will rebuild them). Eg: if you are in noarchivelog mode, you just backup your data and control files -- when you need to restore, restore the data and control files and open with resetlogs. you never backup online redo logs)
Oracle will look at the datafiles, see when they are current as of and will "suggest" what the archive log file names would be and try to apply them....
You have the correct idea tho -- do it, practice it, get into all kinds of situations. Learn what to do and you'll never be sorry you did!
Archivelog and noarchive
Ernest, November 13, 2002 - 10:29 am UTC
I think that a lot of people just don't understand the basic concept of Oracle process (For example) the asynchronous nature of the DBWR.
How it works/why it decide like that?/why tom recommend to put redo log on non-raid disk./why is it so powerful/why not made it as synchronous process/Why Oracle need so much ram over sql server 2000?
A lot of books just taught you how but forget to tell you why. Thus, a lot of questions being asked in the same area. If you still don't understand, I highly recommended you all to set up a test db and try all the deadly cases say lost of control file/lost of all db/lost of archive log/try with the redo log in archive mode and experience how fatal it is as quoted by tom..
Effects of Resetlogs on Standby Database
Mohammed Osman, May 28, 2003 - 5:38 am UTC
Lukcy me, this is the exact situation I am into.
I have been testing the Backup and Recovery process using Rman on Oracle Server 8.1.7. on Windows 2000 Server before implementing on Production database.
Created Primary Database & Standby Database (Managed Recovery Mode)
1) Incremental Level 0 Backup of Standby (Weekend), Daily Level 2, and alternate days Level 1 is taken with All Arhicve Logs and deleting 3 days logs using DELETE INPUT
2) Backup of Control File from Primary Database is taken.
During the Testing of Disaster Recovery Process I have taken into consideration of the following scenarios. Shutdown Immediate, and deleting the Datafiles & Controlfile.
1) Missing all Datafiles (Mounted the Database and successfully Recoverd)
2) Missing Control File and all Datafiles (Just the Redo Logs are intact). Recovered the Control File with Nomount Database. Recovered all the Datafiles with Mount Database successfully.
However when Alter Database Open is issued it does not Open, it prompts to Open database with RESETLOGS.
Once the database is opened with RESETLOGS. Backup from Standby database generates errors.
RMAN> resync catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 05/27/2003 02:24:36
RMAN-20011: target database incarnation is not current in recovery catalog
RMAN> list backup;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 05/27/2003 02:24:45
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog
Standby database does not synchronize with the primary once Incarnation of the Primary database is done.
It seems that I have to recreate the Standby Database again to take the Backup or is there any other alternative which I am not aware of.
Expecting your valuable advise.
With best regards
May 28, 2003 - 7:07 am UTC
if you resetlogs, that is one of the things that will "break" the standby -- yes.
so you backup and restore and reinstantiate the standby at that point.
Prompt Response
Mohammed Osman, May 28, 2003 - 7:54 am UTC
Thanks a lot to clarify my doubt and advise accordingly.
Appreciate your prompt response.
With best regards
resetlogs
Nagaraju, July 22, 2004 - 7:06 am UTC
Tom, please correct me if i am wrong.
In Oracle 8i database,
A)
can i say we need to open the database with resetlogs option, because old restored control file has a different log sequence number than the online redolog files. with resetlogs, we resynchronize the control file and redologs.
B)
if so, what cases, do we have to use this resetlogs option
1.after recovering the database with an old controlfile
2. after doing an incomplete recovery
please tell me anything else
July 22, 2004 - 7:31 am UTC
no, you can use the old binary control file to create a "create controlfile" statement that can use either resetlogs or noresetlogs.
you use resetlogs in general when doing incomplete recovery -- when the entire redo stream is not to be applied.
Hot backup
A reader, July 22, 2004 - 1:12 pm UTC
So if I have a complete hot backup (datafiles and controlfile), I can NEVER recover and open this database with a NORESETLOGS, right? Why is this?
Thanks
July 23, 2004 - 8:05 am UTC
Hot backup
A reader, July 23, 2004 - 9:20 am UTC
Um, no I cant. Look at the link you pointed me to.
6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:
ALTER DATABASE OPEN RESETLOGS;
Look at
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/performingreco.htm#22669 <code>
Questions:
1. Why do I need to resetlogs?
2. Also, earlier in this thread, you said that I can use my binary controlfile backup to CREATE CONTROLFILE. How can I do this? If I can do this, and since the controlfile is now current, can I now open NORESETLOGS?
Thanks
Hot backup
A reader, July 23, 2004 - 10:16 am UTC
Great, thanks.
1. Is this something new with 9iR2? In 8i, I am pretty sure that there was no way to open NORESETLOGS from a hot backup. If so, what changed architecturally between 8i and 9i in this regard?
2. I remember my Oracle univ instructor telling me that in 9iR2 (or was it 10g), even after a RESETLOGS, old backups are still usable! How in the world is this possible?
Can you explain the changes between 8i and 9iR2 regarding all this backup, recovery stuff?
Thanks
July 23, 2004 - 3:36 pm UTC
1) this has been true in prior releases. if you look at the backup controlfile to trace from 8i:
Instance name: ora817dev
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 29665, image: oracle@aria-dev (TNS V1-V3)
*** SESSION ID:(8.1531) 2004-07-23 15:38:22.483
*** 2004-07-23 15:38:22.483
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA817" NORESETLOGS NOARCHIVELOG
2) rman facilitates that, see the rman guide.
Look at the 9i new features chapter for rman (in rman docs) for whats new in backup and recovery in 9i.
Another question
A reader, July 23, 2004 - 12:26 pm UTC
From the 'backup controlfile to trace' file, I see the following
# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files.
# Use this only if the current version of all online logs are available
How can the current version of all online logs be available? I mean, live online redlogs are not supposed to be backed up, right? A typical hot backup backs up all the datafiles, archived redologs, controlfile, etc. Should online logs be backed up as part of this?
So, if my primary site goes down, I restore all the datafiles, controlfile and the online redologs?
Then I do
startup nomount
create controlfile ... NORESETLOGS ...
alter datase open NORESETLOGS;
I guess what I dont understand is how I can backup live online redologs and how can the information in them be recovered since it has not yet been written to the archived logs?
Thanks
July 23, 2004 - 4:41 pm UTC
if you do not have the online redo logs - then yes, you must open resetlogs -- think about it -- YOU'VE BROKEN the redo log chain?
question was:
So if I have a complete hot backup (datafiles and controlfile), I can NEVER
recover and open this database with a NORESETLOGS, right? Why is this?
If you have access to the online redo logs (just one of the two or three copies) you can open without resetlogs. break the redo chain -- don't have those online logs available -- that's a horse of a different color.
Another question
A reader, July 23, 2004 - 5:18 pm UTC
OK so I guess if I want to open noresetlogs to preverve my log sequence#, online redologs must be included in my hot backup.
But my question was really...I cant simply do a 'cp datafile backup_dir' because the datafile is actively being written to. When I copy it the blocks might be fractured or whatever. Thats why I have to do a BEGIN BACKUP so that Oracle does something special and that lets me recover this datafile.
Why doesnt the same thing apply to backing up online redologs? Why dont I need do a BEGIN BACKUP on them?
Thanks
July 23, 2004 - 5:36 pm UTC
NO -- that is not what this says at all.
If you want to open noresetlogs
you must be doing a complete recovery
which includes the CURRENT online redo logs.
You never never never backup online redo logs.
Online redologs
A reader, July 23, 2004 - 7:06 pm UTC
"If you want to open noresetlogs you must be doing a complete recovery which includes the CURRENT online redo logs. You never never never backup online redo logs"
I dont know where the disconnect is. What do you mean by current online redologs? The real current ones or the ones in on my backup tape?
Let me try to explain what I am not clear about.
I have a database that I manually backup. I put all the tablespaces in hot backup mode. I copy off all my datafiles to tape/disk whatever. I do a alter database backup controlfile to 'filename'. I copy off 'filename' to tape. I do NOT backup my online redologs.
Now taking just my backup tape, can I do a OPEN NORESETLOGS?
If the answer is NO, then that contradicts your earlier answer
<quote>
So if I have a complete hot backup (datafiles and controlfile), I can NEVER recover and open this database with a NORESETLOGS, right? Why is this?
Followup:
yes you can.
</quote>
July 23, 2004 - 8:17 pm UTC
the real live CURRENT ones.
No, you cannot take your backup tape and do that.
The answer DOES NOT contradict my previous answer for you have materially changed the question.
You have a complete hot backup -- datafiles and controlfiles. You can in fact open without resetlogs -- assuming the system you are restoring to has your current online redologs so you can in fact to a complete recovery -- resulting in no loss of data.
So yes, yes you can and yes, yes people do...
Online redologs
A reader, July 23, 2004 - 9:55 pm UTC
So if my database is humming along and suddenly I lose all the disks containing my datafiles and controlfiles. All I have left are the online redologs.
I tried finding this scenario in the User-Managed Backup guide but didnt find it.
So, in this case, I would restore all my datafiles from the last backup and
startup nomount
create controlfile ... NORESETLOGS
RECOVER DATABASE;
Provide all the archived redologs it asks for
alter database open NORESETLOGS;
Sounds correct? Thanks
July 24, 2004 - 11:15 am UTC
yup.
it is just as if "you lost all online controlfiles" -- variation on a theme.
create new control file vs use backup
Another reader, July 26, 2004 - 11:47 am UTC
Hi Tom--Just trying to make sure I understand the last two postings; so if the online logs are available, you can create a new control file and open NORESETLOGS even if your datafiles are not current (i.e. have been restored from back up)? Since all the redo is available (online and archived) we can do complete recovery, but how does the new control file know what redo to apply since it's just been created and the extent of it's redo "knowledge" covers only the online logs?
(I'm assuming the datafiles will need archived redo as well as online to bring them forward to the present since they're from a backup)Have I missed something?
Thanks.
July 26, 2004 - 11:58 am UTC
you would recover the database and it would apply the logs.
the log sequences and such are "well known" -- they are just numbers. It'll see it needs "change #432423432" and will request logs till it finds it.
How to go about in this situation
Yenyang, August 25, 2004 - 2:48 am UTC
I have a test scenario. If I accidently drop a table at 11:40 AM and I realized after couple of hours. Since I have previous day's backup and also archive log files till this time I can do an incomplete recovery. While doing incomplete recovery, instead of doing incomplete recovery till 11.40AM, I did till 11.30 AM. I opened the database with resetlogs and I checked for the table which I dropped. But it was not recovered. If I do an incomplete recovery again till 11.40 AM and while opening the database what should I give resetlogs or noresetlogs or it does not matter at all ?
August 25, 2004 - 7:38 am UTC
"But it was not recovered." ?!? what do you mean there?
To my previous question
YenYang, August 26, 2004 - 1:58 am UTC
Sorry I interchanged the timings. I have a test scenario. If I accidently drop a table at 11:30 AM and I realized after couple of hours. Since I have previous day's backup and also archive log files till this time I can do an incomplete recovery. While doing incomplete recovery, instead of doing incomplete recovery till 11.29AM (some time before 11.30AM) , I did till 11.40 AM. I opened the database with resetlogs and I checked for the table which was dropped. But it was not recovered because as per archive log even at 11.40 AM the table is dropped. If I do an incomplete recovery again till 11.29 AM and while opening the database what should I give resetlogs or noresetlogs or it does not matter at all ? After first incomplete recovery the log sequence numbers are set to 1.
My question is if I once do an incomplete recovery and open with resetlogs and I realize that I need to do an incomplete recovery because I gave the wrong time (in UNTIL TIME clause) what are the things I should take care ?
August 26, 2004 - 9:40 am UTC
incomplete recovery is going to want resetlogs.
that first recovery to 11:40 does not count, it is as if it did not happen since you are re-doing the recovery.
Recover Until Time
Anil Pant, August 28, 2004 - 4:52 am UTC
Here is an scenario. My database is running in Archive log mode.
(1) I've taken a cold back.
(2) I change my server date to next year (2005-08-28 : 14:30:00)
(3) I create some table.
(4) I then reset to current date (2004-08-28 : 14:30:00)
(5) Again I create some table
(6) Shutdown the database
(7) Copy all the datafiles from old back
(8) recover database until time '2004-08-28 : 14:30:00'
will the table created with server time as 2005-08-28 : 14:30:00 be restored? i tried but could not recover.
August 28, 2004 - 9:59 am UTC
the date/time is converted to an Oracle SCN using a mapping table. It is within +/- some amount of time (never exact).
Using scn based recovery is exact.
"i tried but could not recover" is very vague, yes, you can recover this table, no I cannot tell you why you didn't here because you provided insufficient data to analyze the problem.
Resetlog Scenarios
Deepak Haldiya, March 09, 2005 - 12:17 pm UTC
Hi Tom,
There are so many different scenarios for using resetlogs or noresetlogs. I am trying to compile them in one place. Please suggest if you think the following is true,
Recovery with Control Files
Complete Recovery
NoResetLogs
Incomplete Recovery
online redo logs available
ResetLogs
online redo logs NOT available
ResetLogs
Recovery with BackUp Control Files
Complete Recovery
ResetLogs
Incomplete Recovery
online redo logs available
Resetlogs
online redo logs not available
ResetLogs
Recovery with new control file (generated through alter database backup controlfile to trace)
Complete Recovery
NORESETLOGS
Incomplete Recovery
online redo logs available
Resetlogs
online redo logs not available
Resetlogs
Please suggest if the option for Resetlogs and No resetlogs is true in the above scenarios. Please advice any other scenarios which I have not touched.
Thanks
Deepak Haldiya
Deepak Haldiya, March 09, 2005 - 1:42 pm UTC
Hi Tom,
This is taken directly from User Managed Bachup & recovery manual,
Table 3–1 Scenarios When Control Files Are Lost
Status of Status of Response
Online Log Datafiles
----------------------------------------------------------------------------
1.Available Current If the online logs contain redo necessary for
recovery, then restore a backup control file
apply the logs during recovery. Hence, you must
specify the filename of the online logs
containing the changes in order to open the
database. After recovery, open RESETLOGS.
2.Unavailable Current If the online logs contain redo necessary for
recovery, then you must re-create the control
file. Because the logs are inaccessible, open
RESETLOGS.
3.Available Backup Restore a backup control file, perform complete
recovery, and then open RESETLOGS.
4.Unavailable Backup Restore a backup control file, perform incomplete
------------------------------------------------------------------------------
What can't we can use backup control file in Case 2, while case 4 allows it?
Thanks
March 09, 2005 - 3:08 pm UTC
please lets try to keep it to general comments on the prior posting.
because the control file is older than the datafiles in 2
ResetLogs
Deepak Haldiya, March 09, 2005 - 5:30 pm UTC
Hi Tom,
I did not receive your feedback on the prior posting:
-------------------------------------------------
There are so many different scenarios for using resetlogs or noresetlogs. I am
trying to compile them in one place. Please suggest if you think the following
is true,
Recovery with Control Files
Complete Recovery
NoResetLogs
Incomplete Recovery
online redo logs available
ResetLogs
online redo logs NOT available
ResetLogs
Recovery with BackUp Control Files
Complete Recovery
ResetLogs
Incomplete Recovery
online redo logs available
Resetlogs
online redo logs not available
ResetLogs
Recovery with new control file (generated through alter database backup
controlfile to trace)
Complete Recovery
NORESETLOGS
Incomplete Recovery
online redo logs available
Resetlogs
online redo logs not available
Resetlogs
Please suggest if the option for Resetlogs and No resetlogs is true in the above
scenarios. Please advice any other scenarios which I have not touched.
Thanks
Deepak Haldiya
March 09, 2005 - 6:39 pm UTC
I don't see them all, I don't respond to them all -- I get too many of them, if I sit down and am in a hurry without time to research when I don't know the answer off of the top of my head - I skip it rather than be possibly wrong.
I'm in a hurry right now.
controlfile, online log and resetlogs
Deepak Haldiya, March 09, 2005 - 8:50 pm UTC
on the question, What can't we can use backup control file in Case 2, while case 4 allows it?
you said: "because the control file is older than the datafiles in 2 "
Control file is older than datafiles in case1 too. I am still confused?
In case 2, since the online logs are not accessbile, it is understood we can only perform an incomplete recovery. So, why can't we use RECOVER DATABASE USING BACKUP CONTROLILE UNTIL CANCEL/TIME/CHANGE# and perform an recovery to a time in Archive logs and open the database with RESETLOGS.
Table 3–1 Scenarios When Control Files Are Lost
Status of Status of Response
Online Log Datafiles
----------------------------------------------------------------------------
1.Available Current If the online logs contain redo necessary for
recovery, then restore a backup control file
apply the logs during recovery. Hence, you must
specify the filename of the online logs
containing the changes in order to open the
database. After recovery, open RESETLOGS.
2.Unavailable Current If the online logs contain redo necessary for
recovery, then you must re-create the control
file. Because the logs are inaccessible, open
RESETLOGS.
3.Available Backup Restore a backup control file, perform complete
recovery, and then open RESETLOGS.
4.Unavailable Backup Restore a backup control file, perform incomplete
------------------------------------------------------------------------------
9iR2 RMAN recovery of Controlfile without resetlogs
Randall, June 24, 2005 - 9:28 am UTC
Tom,
Thanks for taking time.. We are reviewing our recovery methods with rman and I have a question about control file recovery.
We normally have three drive arrays for each database with a control file on each one. Our redo groups have two members with members on different drives and archive written to the third disk and to another server.
So each "Disk" has one control file, an x number of database files and ether a redo group member or archive files.
So when we test recovery from a lost array, I can make a copy of one of the other two control files to then use RMAN to:
STARTUP FORCE MOUNT;
-- FOR EACH MISSING FILE
SQL 'ALTER DATABASE DATAFILE 8 OFFLINE';
RESTORE DATAFILE 8;
RECOVER DATAFILE 8;
SQL 'ALTER DATABASE DATAFILE 8 ONLINE';
--
ALTER DATABASE OPEN;
All good..
But if I try to have RMAN recover the lost control file
STARTUP NOMOUNT
RESTORE CONTROLFILE;
I am forced to do a
ALTER DATABASE OPEN RESETLOGS;
Is there a way to have RMAN recover the missing control file without having to RESETLOGS?
Thanks...
June 24, 2005 - 12:40 pm UTC
if you restore the controlfile from rman, you are restoring an OLD COPY of it.
Randall, June 24, 2005 - 2:02 pm UTC
I guess this would be more of a product enhancement then..
With 9iR2 when you do a RESTORE DATABASE;
rman is already smart enough to know what data files do NOT need to be recovered. It shouldn't be too hard to say
"I know I still have at least one good control file" I can use that to recover the lost one instead of getting an OLD COPY that needs recovery.
And while were at it. Go ahead and rebuild the missing redo group members from the existing ones.
What do you think.. I haven't done much with 10g yet so maybe its a mute point.
As always your thoughts are most welcome...
June 24, 2005 - 6:43 pm UTC
well, it is more complex than a simple datafile check. rman would have to know what you "mean", you asked to restore a backed up controlfile, you'd need a new command like
Please either use a current good one or restore an old one over all of the others if no current good one can be found....
so the results of the command might be that you can open without resetlogs or not.
RAC -How to reset those logs even there is no recovery happened
kumar, December 12, 2005 - 1:25 pm UTC
Tom,
I want to simply do the resetlogs on the current running database, which has no problems and no recovery needed. This is RAC two node instances called db1 on node 1 and db2 on node2. We are taking full backups with RMAN no catalog options, and deleting archive logs after full RMAN backup. But some reason we want to reset those archive logs. How easy we can do this without having any trouble occur to this two node RAC. Yes we don't care even those previous backups obsolete
December 12, 2005 - 2:18 pm UTC
what is the reason. this sounds "nuts" to me, can you give me a real world use case where this makes sense, then I'll tell you.
RAC -How to reset those logs even there is no recovery happened
kumar, December 12, 2005 - 3:34 pm UTC
Tom,
Actually the same reason and same kind of answer I expressed to my customer, whom I provided this RAC. But now due to some reason they were getting simply on RMAN backup archive logs, that were deleted long back without backing them up through RMAN. Since then, though they have good backup, RMAN throws a tones of errors saying such archive log not found. I have also tried to run another RMAN full back on disk with simple default commands and I didn't give that delete archivelogs after backup in the list of commands, still it throws tones of such errors saying some old dated archive log files not found. So thought to have one time reset these logfiles so that RMAN may behave quiet instead throwing those errors.
December 12, 2005 - 4:20 pm UTC
what are the rman messages.
you can just startup mount, recover until cancel, CANCEL, then open resetlogs.
resetting logs
kumar, December 12, 2005 - 6:43 pm UTC
When I do this way, do I have to shutdown the both nodes databases? is there any exclusive threat in this one, like I need to first use such as archive log current etc such as. Do I have to do on both instances or one node instances is OK?.
Scenario
Log sys into node 1
Shutdown abort or immediate
startp mount
Recover until cancel , CANCEL
then alter database open resetlogs;
Still node 2 instance is down
now Log sys into node 2
startp mount
Recover until cancel , CANCEL
then alter database open resetlogs;
Is this way to do or you suggest any better way, I am kind of worried to do such due to this is RAC
December 12, 2005 - 7:29 pm UTC
the only way you'll be resetting logs is "offline".
like i said - WHAT is the message from rman and is it just "oh bother, there is that message" or is it "oh - it is preventing us from doing our job".
eg: is this even worth it.
Clarification
A reader, December 12, 2005 - 7:33 pm UTC
Tom,
Just for clarification, in the User-Managed Backup Manual
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/osrestore.htm#26899 <code>
it indicates that when the online redo logs ARE available and all control files are lost, then recovery should be followed by a RESETLOGS. It specifically states,
"You cannot mount and open the database until you make the control file accessible again. If you restore a backup control file, then you must open the database with the RESETLOGS option."
I agree with your statement above where you mentioned that a recovery in this scenario could be followed by a NORESETLOGS.
Can you comment?
Thanks.
December 12, 2005 - 7:44 pm UTC
"if you restore a backup control file...."
and what if you do not? what if you create controlfile instead?
A reader, December 12, 2005 - 10:33 pm UTC
Keeping all these permutations straight gets confusing. I'm a firm believer in your philosophy of practice, practice, practice B&R. And that's what I'm finally getting the opportunity to do.
In thread,
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3151992035532 <code>
your response to the question:
Q. When a database is in UNMOUNTED state is there a way to create a text
control file from a earlier binary control file available
was,
A. you would take the binary backup
restore it
startup MOUNT <<<=== gotta be mounted
alter database backup controlfile to trace noresetlogs;
this is the documented procedure to recover after the loss of all online,
current controlfiles with just a binary backup (shows why you don't really
want binary backups, you want "to trace" backups)
In addition, the Backup Manual indicates:
"If all control files have been lost or damaged by a permanent media failure, but all online redo logfiles remain intact, then you can recover the database after creating a new control file. Note that this procedure does not require you to open the database with the RESETLOGS option."
Therefore, why not avoid binary controlfile backups all together and just make backup of controlfiles to trace? In this case, if the online redo logs are INTACT, I don't see why we would want to restore a binary control file and subject ourselves to issuing a RESETLOGS when we could recreate the control file with CREATE CONTROLFILE NORESETLOGS and avoid having to issue a RESETLOGS?
Thanks for your time.
December 13, 2005 - 8:59 am UTC
... Therefore, why not avoid binary controlfile backups all together and just make
backup of controlfiles to trace? ....
the controlfile contains lots of other stuff that you will lose by recreating it that you might not want to lose (it can be used as your "recovery catalog").
A reader, December 13, 2005 - 9:14 am UTC
But that's really only benefical if RMAN is being implemented...right?
December 13, 2005 - 9:50 am UTC
no not really, your archive history and other stuff is in there.
backup controlfile to trace
jp, December 13, 2005 - 1:45 pm UTC
tom,
about the previous post, you said that we might need a controlfile to keep the archive history and other stuff, I did the following test..
Db 9.2 in archivelog, all archive files availables,
Backup from Nov 11th, since this date, I have created a new tablespace, but not included in the backup.
1)shutdwon database
2)delete all datafiles and controlfiles, except the datafile created after the backup.
3)restore all datafiles from a backup from Nov 11th,
4)From the same backup, I have a controlfile to trace file, from where I took the text to recreate the controlfile, I just added the datafile for the new datafile created after backup.
5)controlfile created successfully
6)recover database, at some point the recover was stopped, and request to rename the new datafile,
7)rename datafile successfully
8)recover database again , and finished the recover successfully
9) alter database open; complete recovery !!
my point is that even recreating a controlfile with a old backup controlfile to trace, we can do a complete recovery, there is no need for archive history.
can you think in a scenario where binary backup controlfile will be better than a backup controlfile to trace. I believe that a controlfile to trace will be always better than a binary controlfile backup
thanks
December 13, 2005 - 5:06 pm UTC
You *might* want it, you didn't, so you don't need to keep it.
Others might want what is in the control file, you were asking "why taking binary backup". One reason - you might want that information
of course you can recover without it (the binary one), you can always do a recovery and supply the location of the archives (if they are not in the default place).
Strange Recovery
Dost, December 22, 2005 - 11:30 am UTC
Tom,
I faced very strange situation so please help me to understand what happened?
1. I asked DCO to restore quarter end backup and they did . We are following shadow process which runs at 02:00 am PST.
Database restores completed.
startup mount
SQL> recover database until cancel
ORA-00279: change 7281316858513 generated at 10/02/2005 09:34:34 needed for thread 1
ORA-00289: suggestion : /REP/arch/archREP_0000051659
ORA-00280: change 7281316858513 for thread 1 is in sequence #51659
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: '/REP/data04/system01.dbf'
" Begin and End backup was based on following archivelogs
alert log :
alter tablespace HYPIND end backup
Completed: alter tablespace HYPIND end backup
Sat Oct 1 23:22:22 2005
Beginning log switch checkpoint up to RBA [0xc9a6.2.10], SCN: 0x069f.4ec5babc
Thread 1 advanced to log sequence 51622
Current log# 3 seq# 51622 mem# 0: /REP/loga/log_03a.dbf
Current log# 3 seq# 51622 mem# 1: /REP/logb/log_03b.dbf
Sat Oct 1 23:22:22 2005
ARC1: Evaluating archive log 2 thread 1 sequence 51621
ARC1: Beginning to archive log 2 thread 1 sequence 51621
Creating archive destination LOG_ARCHIVE_DEST_1: '/REP/arch/archREP_0000051621'
and for recovery it is asking
Sun Oct 2 15:31:15 2005
Beginning log switch checkpoint up to RBA [0xc9cc.2.10], SCN: 0x069f.50fb869c
Thread 1 advanced to log sequence 51660
Current log# 5 seq# 51660 mem# 0: /REP/loga/log_05a.dbf
Current log# 5 seq# 51660 mem# 1: /REP/logb/log_05b.dbf
Sun Oct 2 15:31:15 2005
ARC1: Evaluating archive log 4 thread 1 sequence 51659
ARC1: Beginning to archive log 4 thread 1 sequence 51659
Creating archive destination LOG_ARCHIVE_DEST_1: '/REP/arch/archREP_0000051659'
Why?
Anyways what I did
recover database until time '2005-10-02:09:30';
recover database until time '2005-10-02:00:24';
recover database
alter database open;
and it opened database succuessfully
and I found
-r--r--r-- 1 orarep dba 101067264 Dec 21 23:20 archREP_0000051659
Please please help me to udnerstand as I am not able to sleep.
December 22, 2005 - 12:18 pm UTC
"DCO"? you start with lots of "internal terms" - like shadown process and what not...
but I'm not following the rest of it pretty much either.
recover
dost, December 22, 2005 - 12:21 pm UTC
I am not sure what do you mean ..Ok lets talk technical
I did not have archive log archREP_0000051659 then how it got created
-r--r--r-- 1 orarep dba 101067264 Dec 21 23:20 archREP_0000051659
Appreciate if you see the alert log details and other mentioned stuff
December 22, 2005 - 12:31 pm UTC
you must have had it, if it got created?? and it exists.
I read the other stuff, I did not follow it. Insufficient data.
I see information about backing up some tablespace HYPIND
I see the database saying "this other tablespace - the most important one SYSTEM - NOT HYPIND, needs more recovery"
I don't see anything about system in the "backup" part of the alert. So one can only assume "that happened later"
I have no idea what a 'dco' is, what you mean by 'shadown process' and other terms you are using which seem to be 'specific to where you work'.
You've given me tiny snippets of some alert logs and ask to have a situation recreated from it. Sort of like trying to explain the entire contents of a conversation hearing just snippets of it.
dost, December 22, 2005 - 12:44 pm UTC
Wed Dec 21 22:58:26 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Wed Dec 21 22:59:25 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Dec 21 23:00:08 2005
ALTER DATABASE RECOVER database until cancel
Wed Dec 21 23:00:08 2005
Media Recovery Start
Starting datafile 1 recovery in thread 1 sequence 51659
Datafile 1: '/REP/data04/system01.dbf'
Starting datafile 2 recovery in thread 1 sequence 51659
Datafile 2: '/REP/data04/system02.dbf'
Starting datafile 3 recovery in thread 1 sequence 51659
Datafile 3: '/REP/data04/system03.dbf'
.
.
.
Datafile 721: '/REP/indx03/fpaind24.dbf'
Starting datafile 722 recovery in thread 1 sequence 51659
Datafile 722: '/REP/data03/fpadata40.dbf'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Wed Dec 21 23:03:25 2005
ALTER DATABASE RECOVER CANCEL
Wed Dec 21 23:03:29 2005
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Dec 21 23:03:29 2005
ALTER DATABASE RECOVER CANCEL
ORA-1112 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Dec 21 23:04:41 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Dec 21 23:09:57 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Dec 21 23:15:13 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Dec 21 23:17:05 2005
ALTER DATABASE RECOVER database until time '2005-10-02:09:30'
Wed Dec 21 23:17:05 2005
Media Recovery Start
Starting datafile 1 recovery in thread 1 sequence 51659
Datafile 1: '/REP/data04/system01.dbf'
Starting datafile 2 recovery in thread 1 sequence 51659
Datafile 2: '/REP/data04/system02.dbf'
Starting datafile 3 recovery in thread 1 sequence 51659
.
.
.
Datafile 721: '/REP/indx03/fpaind24.dbf'
Starting datafile 722 recovery in thread 1 sequence 51659
Datafile 722: '/REP/data03/fpadata40.dbf'
Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 51659 Reading mem 0
Mem# 0 errs 0: /REP/loga/log_04a.dbf
Mem# 1 errs 0: /REP/logb/log_04b.dbf
Media Recovery Complete
Completed: ALTER DATABASE RECOVER database
Wed Dec 21 23:20:01 2005
alter database open
Wed Dec 21 23:20:04 2005
Beginning crash recovery of 1 threads
Wed Dec 21 23:20:05 2005
Started first pass scan
Wed Dec 21 23:20:06 2005
Completed first pass scan
77351 redo blocks read, 0 data blocks need recovery
Wed Dec 21 23:20:06 2005
Started recovery at
Thread 1: logseq 51659, block 120046, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 4 Seq 51659 Reading mem 0
Mem# 0 errs 0: /REP/loga/log_04a.dbf
Mem# 1 errs 0: /REP/logb/log_04b.dbf
Wed Dec 21 23:20:07 2005
Completed redo application
Wed Dec 21 23:20:07 2005
Ended recovery at
Thread 1: logseq 51659, block 197397, scn 1695.1349521501
0 data blocks read, 0 data blocks written, 77351 redo blocks read
Crash recovery completed successfully
Wed Dec 21 23:20:08 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 51660
Thread 1 opened at log sequence 51660
Current log# 5 seq# 51660 mem# 0: /REP/loga/log_05a.dbf
Current log# 5 seq# 51660 mem# 1: /REP/logb/log_05b.dbf
Successful open of redo thread 1.
Wed Dec 21 23:20:09 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Dec 21 23:20:09 2005
SMON: enabling cache recovery
Wed Dec 21 23:20:09 2005
ARC0: Evaluating archive log 4 thread 1 sequence 51659
ARC0: Beginning to archive log 4 thread 1 sequence 51659
Creating archive destination LOG_ARCHIVE_DEST_1: '/REP/arch/archREP_0000051659'
Wed Dec 21 23:20:11 2005
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 23.
Dictionary check beginning
Wed Dec 21 23:20:12 2005
ARC0: Completed archiving log 4 thread 1 sequence 51659
Wed Dec 21 23:20:20 2005
Dictionary check complete
Wed Dec 21 23:20:20 2005
SMON: enabling tx recovery
Wed Dec 21 23:20:20 2005
Database Characterset is UTF8
Wed Dec 21 23:20:29 2005
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Dec 21 23:20:35 2005
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
This what I did
SQL> recover database until cancel
ORA-00279: change 7281316858513 generated at 10/02/2005 09:34:34 needed for ---- We have log till 0000051658
thread 1
ORA-00289: suggestion : /REP/arch/archREP_0000051659
ORA-00280: change 7281316858513 for thread 1 is in sequence #51659
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: '/REP/data04/system01.dbf'
recover database until time '2005-10-02:09:30';
recover database until time '2005-10-02:00:24';
recover database
alter database open
and how this archive log generated
-r--r--r-- 1 orarep dba 101067264 Dec 21 23:20 archREP_0000051659
all other files are of October time stamp
December 22, 2005 - 4:50 pm UTC
still not getting it - there is "the way" archives get created (arch creates them).
Wed Dec 21 23:20:09 2005
ARC0: Evaluating archive log 4 thread 1 sequence 51659
ARC0: Beginning to archive log 4 thread 1 sequence 51659
Creating archive destination LOG_ARCHIVE_DEST_1: '/REP/arch/archREP_0000051659'
Wed Dec 21 23:20:11 2005
the alert says it did this.
Not trying to be dense here, but I'm not sure what the issue is exactly.
Nice!
Matthew Lange, July 25, 2006 - 9:50 am UTC
As usual, "most useful" Oracle help!
Cheers! :-)
helpful
atul, August 27, 2006 - 7:03 pm UTC
Hi,
My scenario is,if i am creating a controlfile using script and in that script i am giving "resetlogs" option.
And if i removed one of the datafiles entry from that controlfile script.
Now if i create the controlfile with resetlogs options.
then we have one file entry missing in controlfile but datafile headers have that information,so database will create missing datafile.
Now in that scenarion previous backups wont be useful as we deed resetlogs.
so what are the options we left with?
Thanks,
Atul
August 27, 2006 - 9:17 pm UTC
what goals do you desire?
(only way to know your "options" is to understand what it is you are trying to do)
and - if this is "real life", not hypothetical - use support please, you can really mess stuff up big time otherwise.
clearing_current
Darin, September 21, 2007 - 1:04 pm UTC
G'day tom,
I am playing around in a test DB, but I do need to get it up and running so we can practice some upgrade scripts for a next app. release. Here's what I got....
I shutdown the DB to relocate some of the files (data files, redologs, etc.). The dbf file relocation worked fine. When I relocated the on-line redo log files, the OS file move was OK, and the alter database move file ...to...; worked fine (from startup mount state). Now when i attempted to alter database open I got an error saying:
ORA-00341: log 1 of thread 1, wrong log # in header
ORA-00312: online log 1 thread 1: 'C:\ORADATA\CCHSPRD5\REDO\REDO03.LOG'
I then proceeded as follows:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 290 52428800 1 YES INACTIVE 9.1850E+12 21-SEP-07
3 1 292 52428800 1 NO CURRENT 9.1850E+12 21-SEP-07
2 1 291 52428800 1 YES INACTIVE 9.1850E+12 21-SEP-07
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------- ---
3 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG NO
2 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO02.LOG NO
1 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO03.LOG NO
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00341: log 3 of thread 1, wrong log # in header
ORA-00312: online log 3 thread 1: 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG'
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance cchsprd5 (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG'
SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-00341: log 3 of thread 1, wrong log # 1 in header
ORA-00312: online log 3 thread 1: 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG'
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1293192 bytes
Variable Size 402654328 bytes
Database Buffers 1199570944 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-00449: background process 'DBW0' unexpectedly terminated with error 341
ORA-00341: log of thread , wrong log # in header
ORA-00341: log of thread , wrong log # in header
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1293192 bytes
Variable Size 402654328 bytes
Database Buffers 1199570944 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-00449: background process 'DBW0' unexpectedly terminated with error 341
ORA-00341: log of thread , wrong log # in header
ORA-00341: log of thread , wrong log # in header
ERROR:
ORA-03113: end-of-file on communication channel
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1293192 bytes
Variable Size 402654328 bytes
Database Buffers 1199570944 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup no mount
SP2-0714: invalid combination of STARTUP options
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1293192 bytes
Variable Size 402654328 bytes
Database Buffers 1199570944 bytes
Redo Buffers 7094272 bytes
SQL> alter database mount;
Database altered.
SQL> set pages 1000
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------- ---
3 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG NO
2 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO02.LOG NO
1 ONLINE C:\ORADATA\CCHSPRD5\REDO\REDO03.LOG NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 52428800 1 YES UNUSED 0 21-SEP-07
3 1 0 52428800 1 NO CLEARING_CURRENT 9.1850E+12 21-SEP-07
2 1 291 52428800 1 YES INACTIVE 9.1850E+12 21-SEP-07
SQL> alter database drop logfile member 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG';
alter database drop logfile member 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG'
*
ERROR at line 1:
ORA-00361: cannot remove last log member C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG
for group 3
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance cchsprd5 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: 'C:\ORADATA\CCHSPRD5\REDO\REDO01.LOG'
SQL>
------
Now since this is a test DB as I mentioned above I am not too greatly concerned over experimenting with it (as you can see I was trying above) - but I would greatly like to understand why
(1) the redo logs got out of sync in the firest place after simply moving the files as the documentation specifies.
(2) what is going on with the clearing_current status - and why I can't get rid of either the log group or the member
(3) i cannot force a logfile switch b/c the DB is not open - so what is my next step here? do I need to open with resetlogs?
I am not concerned over data-loss at this point either since I have just created the D via a metadata_only import from our production DB - but again, I need to understand what is truly going on here - if this WERE our production DB, I would be in a very serious panic mode right now!!!
Thanks as always.
Regards - Darin
September 26, 2007 - 1:12 pm UTC
you did not include how you "moved" redo log files (Oracle wise, precise command - not sure what you think you used...). You generally just create new, drop old to move them.
Or you use the control file create command to recreate them elsewhere.
so, new example LIMITED to just the problem at hand, from the very very beginning.
clearing_current continued
Darin, September 24, 2007 - 4:26 pm UTC
As a follow-up - on Monday morning I was feeling very daring so I decided to go ahead and try to open the DB with RESETLOGS just to get moving again with the set-up....
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
As you can see, all my bravery and valour was to no avail. Now I am VERY confused where to go with this - I assumed the RESETLOGS would work and was actually counting on it (especially since when I attempt to open the DB I get the following error:
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1293192 bytes
Variable Size 402654328 bytes
Database Buffers 1199570944 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-00449: background process 'DBW0' unexpectedly terminated with error 341
ORA-00341: log of thread , wrong log # in header
ORA-00341: log of thread , wrong log # in header
...and when I look up the error code ORA-00341....
ORA-00341: log string of thread string, wrong log # string in header
Cause: The internal information in an online log file does not match the control file.
Action: Restore correct file or reset logs.
Action: Restore correct file or
reset logs.I cannot "restore correct file" (since I apparently don't have it), and as shown above "resetlogs" failed!!!
Any insight is most appreciated.
September 26, 2007 - 8:36 pm UTC
see above, I have no idea how you "moved" redo log files, I want a full example
a reader
raman, October 10, 2007 - 2:35 am UTC
tom why recover after restore controlfile?
raman
A reader, April 03, 2009 - 6:06 am UTC
Hey tom
"recover database using backup of controlfile" command just picks up all the information from the current system.dbf file. and its scn is equal to system.dbf`s scn
right?
A reader, April 04, 2009 - 12:44 am UTC
if I recreate the controlfile, its checkpoint scn# is diffrent than datafiles and log files, therefore I issue :
"recover database using backup of controlfile"
so that the checkpoint scn# of the controlfile equals to checkpoint scn# of datafiles.Therefore I can open the db
Am I right tom?
A reader, April 06, 2009 - 4:48 am UTC
So
"recover database using backup of controlfile" command just ignores the current scn of the controlfile and
It checks the scn of datafiles and redologs.
Correct tom?
April 13, 2009 - 10:05 am UTC
forget about the scn (as I said)
when you use the backup controlfile, you are basically saying "I am doing an incomplete recovery - database, you WILL apply redo logs as long as I tell you to, when I'm out of redo that I want to apply, I'll tell you to STOP"
why open resetlogs takes so much time
A reader, March 08, 2010 - 6:41 pm UTC
why open resetlogs takes so much time to execute.
I know it has to change sequence in control file.
But still it takes so much time to execute.
What other updates it needs to do.
March 09, 2010 - 11:43 am UTC
it might have to .... reset the logs... (look at the logs)
No resetlogs option
Arvind, January 21, 2011 - 12:40 pm UTC
Tom,
When doing a clone we create a controlfile using the script generated with "alter database backup controlfile to trace" statement. And then use this controlfile to recover the database.
My question is --
If I have all the archive and redo data and I can copy it over to the target node (by simple scp) and apply it using 'recover database using backup controlfile until cancel'. Can I open my database with noresetlogs option??
Thanks,
January 24, 2011 - 7:18 am UTC
yes.