Skip to Main Content
  • Questions
  • Shutdown abort prior to an offline backup.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Doug.

Asked: November 05, 2001 - 11:03 am UTC

Last updated: May 29, 2013 - 5:11 pm UTC

Version: All

Viewed 1000+ times

You Asked

An old chestnut that I've never had answered adequately. Oracle typically suggest a clean shutdown (normal or immediate) prior to an offline backup to guarantee that it's 'clean' or 'good'.

Given the fact that Oracle's perfectly capable of performing instance recovery in the event of a server or software failure, what's the difference between restoring a backup that was taken after a shutdown abort and opening it and the server power failing during the middle of the day and then restarting the database?

To summarise - I have confidence in oracle's ability to restore a backup and recover from a shutdown abort using the contents of online redo log files, why do Oracle say I shouldn't do this?

tia,

Doug

and we said...

Because you should never backup your online redo logs and if you do a shutdown abort in NOarchivelog mode you NEED the online redo logs.

In archivelog mode, it doesn't matter (but in archivelog mode, you shouldn't be shutting down to take a backup at all! I would not ever recommend shutting down to take a backup)

In noarchivelog mode, if you are backing up your online redo (bad habit though, the only thing that this could do is cause confusion later when you switch this database into archivelog mode -- when backing up ONLINE redo logs can be deadly) you can backup an aborted instance. But.... If you care so little about the data that you are running in noarchivelog mode (a mode that is assured to have data loss someday) maybe you don't even need to backup ;) (no media recovery, not suitable for the real world!)



Rating

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mmmm ....

Doug Burns, November 05, 2001 - 1:34 pm UTC

... I hear what you're saying and don't really disagree with any of it on a technical level. I still think that Oracle documentation spends a lot of time telling people 'Make sure you have a good offline backup prior to this operation' (or similar - I don't have a photographic memory) without really having a logical reason to insist on the shutdown normal or immediate.

I still think that backing up online redo logs might be a reasonable idea - what about a situation when you want to restore some tapes and have an operator restart a database without it needing any kind of recovery? I know you could script it, but ...

Oh, and I've worked at a few sites that would *prefer* to go back to last night's position, with data loss, because it's a more logical position for their business (e.g. tieing it in to external data feeds that can be re-submitted from a known point in time). I know what you're saying, but I think there might be real world variations.

Thanks for your comments, though. Great book, too ...

Tom Kyte
November 05, 2001 - 2:49 pm UTC

The only time -- repeat the only time -- you can backup the online redo logs is in NOARCHIVELOG mode, it is fatal in archivelog mode, the only thing that could happen in archivelog mode is the operator restores these old logs -- wiping out the REAL current online redo logs and making it so you lose data!

The only thing the operator would need to do is open the database with resetlogs -- the logs would come back by themselves.


redo logs

Naveed Amin Khan, November 05, 2001 - 2:08 pm UTC


I agree with what you said about redo logs 100%. I think it only makes sense to treat redo logs for what their real purpose in life is.

What Doug really asked...

Andre Whittick Nasser, November 05, 2001 - 2:23 pm UTC

What I believe Doug is really asking is what is the difference between shutting down abort or having an instance crash ("dirty") and shutting down normal/immediate/transactional ("clean") before taking an off-line, or cold, backup.

The answer is that when you shut down your database in a dirty way, the datafile headers are not syncronized and during startup Oracle will complain about this "inconsistency". Redo log files will be applied to bring your database uo to a consistent state. Remember that only the current redo log file will be used, since a log switch ocurred before begining to fill it up, generating a checkpoint (DBWR flushing to the datafiles).

If you did a clean backup, your datafiles are ok, and the database will come up just fine.

If you backup your database with its redo log files, don't restore them, since information in the redo files of the crashed database is essential to restoring the system. Archive logs and then the last redo file will be applied to make datafiles consistent.

Backing up online redo...

jan van mourik, November 05, 2001 - 2:44 pm UTC

Some shops just insist on doing a full system cold backup every week (or month or whatever). And they demand the database(s) to be shut down because they will back up everything, including the Oracle directories. In that case, if you were to restore *everything*, would a shutdown abort be a problem? I would think not for the reasons stated by Doug above.
That said, for the Oracle specific backups, would you always skip the online redo log files? This to avoid accidentaly restoring them?

jan

Tom Kyte
November 05, 2001 - 3:41 pm UTC

If you are in ARCHIVELOG MODE, backing up online redo log files is fatal, a mistake, don't do it.

If you are in ARCHIVELOG MODE, backup after a shutdown <anything you want>.

If you are in NOARCHIVELOG MODE, if you backup after a shutdown abort, backup the redo log files (but this is strongly discouraged as it can lead to confusion). Otherwise do not backup the redo log files.

If you have the opportunity to shutdown abort, you have the same opportunity to startup and shutdown clean. Just do it, its so easy, why quibble about it? Then the rule becomes simply "never backup online redo log". There, done, very easy.

(i would always skip backing them up just in case I accidently was to restore them, destroying the REAL online redo logs with important stuff).

A simplification

Doug, November 05, 2001 - 4:14 pm UTC

First time I've used the system, apologies if I'm not responding through the right method.

Anyway, just to try to wrap this up a bit (because I *know* what I was asking ;-) ), I'm fully aware of all the stuff that you're saying about backups Tom and it's the way I do it too, but it's not *really* what I was asking. Maybe it's too abstract but, is there any difference in the 'validity' of an full offline backup taken after a shutdown abort or an immediate if the online logs are part of the backup?

As for why you would want to do this - let's just say that in my stupidity I *do* want to do offline backups (and let's face it, lots of people used to, even when the architecture was fundamentally the same as it is now). The fastest way of guaranteeing the database comes down is to do a shutdown abort and backup the redo logs. Should I ever need to do it, then I pay the rollback price on the recovery, not at the start of the backup job. I know you wouldn't recommend it, but I'm sure you can still say whether it works or not ...

But, honestly, I know all the stuff you were saying, I was just trying to say that a shutdown abort shouldn't invalidate a full offline backup - correct?

(This has turned into *just* the sort of pointless, academic discussion I try to avoid!)

Tom Kyte
November 05, 2001 - 6:06 pm UTC

I'll make it as short and simple as I can. There are two cases:

ARCHIVELOG MODE:
go ahead and backup the shutdown aborted instance. DO NOT BACKUP ONLINE REDO
logs. That is a HUGE mistake in archivelog mode.

NOARCHIVELOG MODE:
go ahead and backup the shutdown aborted instance IF AND ONLY IF you also
backup the online redo logs. You'll NEED them to perform instance recovery
if you ever restore these files.

short and sweet. Thats it. Yes, you can backup the shutdown aborted instance.

Reader

Reader, November 05, 2001 - 8:04 pm UTC


For one thing, I think, shutdown abort may lose data
Ex: TX commits. LGWR writing to redo log. You abort
the process. Data is lost. Will there be redo log
corruption ? Possibly?

When Oracle recovers itself from failure, the
Buffer Cache is in tact. If we do a shutdown
abort, and do a offline backup, the Buffer cache
is refreshed and will it be still safe to
do shutdown abort, in this case.

Tom Kyte
November 05, 2001 - 8:25 pm UTC

No, its just like an instance failure.

Say you pull the plug. If we could not recover that from, we cannot recover from a shutdown abort. We are built to be tolerant of this type of failure.

When Oracle recovers from failure - -the buffer cache does not exist (we had a failure, we went away).

Redo log files ....

Andre Whittick Nasser, November 06, 2001 - 6:46 am UTC

Dear colleagues...

As Tom says, NO MUSS, NO FUSS...

You may even back up the redo log files but, look at the following recovery scenarios:

1) In NOARCHIVELOG MODE -

a) Instance failure (shutdown abort, power outage) - Oracle may, and possibly will, automatically apply the CURRENT redo log file (only!). It has all the consistency information (the differences between the commited transactions and what is in the datafiles), because in the last log switch the data files had been updated because of DBWR's flushing. Oracle checks to see if datafiles are consistent with redo files by "looking" at the datafile headers and comparing their LSN (log sequence number) against that of the redo files. LSN's are incremented at every log switch. Just startup the instance !

b) Media failure (curruption, deletion of datafiles) - If are you very lucky (real darn lucky!) the information in your redo files were not overwritten (there were not enough log switches to make a full "wrap" around the groups) since your last backup was taken . Restore your backup files. In this case, the "differences" mentioned above are in the redo files and will be applied. Notice the similarity between this and archive log mode, only there are no archives, you can only recover from a very short time in the past, that is, since when there is information generated after your backup in the redo logs.

2 - In ARCHIVELOG MODE -

a) Instance failure - Same as 1.a) Archive log files not necessary, only your CURRENT redo log.

b) Media failure - The "differences" between your backup and the current redo logs were saved as archive log files. You must have all archives since your last backup. Just apply them.

I EMPHISIZE: THERE IS PROBLEM BACKING UP YOUR REDO LOG FILES (OK, YOU DON'T NEED TO). JUST DO NOT OVERWRITE THE ONES OF THE CRASHED DATABASE BECAUSE THEY CONTAIN DATA FOR RECOVERY, AS EXPLAINED ABOVE !

Note: Oracle has this "contract" with you: all commited changes are in the redo log files. Thet may not be in the data files yet, because of the DBWR's "asynchronous nature".




A case to back up the online redo logs in archive log mode

Doug, June 27, 2002 - 10:11 pm UTC

Hey Tom - there is a case where you might want the online redo logs backed up in archive log mode. You and I actually discussed it once in a thread that didn't make the public cut. If I am to take a hot backup and use it to do a restore on another box (like a QA environment) and RENAME the instance and potentiallly even roll it forward with a "recover database using backup controlfile until cancel", I have to resetlogs when re-creating the controlfile from trace such as CREATE CONTROLFILE SET DATABASE "NEWNAME" RESETLOGS NOARCHIVELOG - The online logs MUST be there even though since they were taken during a hot backup they may not be consistent. But.. If I take any old logfiles Oracle will complain that the logs are from a prior point in time. I can't RENAME a database without a resetlogs, and I can't resetlogs unless there are logs there. I do this hot restore all the time - you actually helped me to figure out how to do it.

Tom Kyte
June 28, 2002 - 6:47 am UTC

That is not really backing up -- that is restoration. The online logs would be there (if you could get them) in a restore to the same machine (if not, you would have lost some work).

I don't consider that "backup" at all personally. It is a special case of a restore. The case would be "we have to restore to another machine due to total loss of production machine. We have access to the disks so we can get the online redo logs for the damaged database and fully recover".

That your ultimate goal was to rename the database is just a quirk of your environment. We could have just restored, recovered, opened, closed and then renamed as well (the open resetlogs would have given us logfiles the database would "like")

Beating a dead horse

Tom Dyess, August 06, 2002 - 6:17 pm UTC

This is a very good thread, but I have a scenerio I need input on. In my cold backup scripts, I do the following --

1. alter system checkpoint
2. kill all sessions (select statement spooled for all users other than current and oracle processes)
3. shutdown abort
4. startup restrict
5. shutdown immediate
6. backup datafiles

There is a shutdown abort, but since I forced a checkpoint, killed all sessions and startup restrict after the shutdown abort, is this absolutely safe, or are there still issues with the shutdown abort?

Tom Kyte
August 07, 2002 - 10:23 am UTC

I would just do

#3, 4, 5, 6 and skip #1 and 2.

If you want a "consistent" backup, you need to do #4 and #5.

Actually -- I would not do a cold backup under any circumstances but hey -- thats me.

Since you mention "script" you are probably doing this un-attended -- baaaaadddd idea. read
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2876092892845 <code>



SHUTDOWN ABORT and loss of cached sequence numbers

Jarda Smucr, August 20, 2002 - 6:08 am UTC

Hi:

Regarding to possible losses after a shutdown abort -
could you pls give your comments on the following?

</code> http://ixora.com.au/scripts/library.htm <code>
Oracle Advanced Performance Tuning Scripts
Library Cache Scripts

unload_sequences.sql

This script unloads all cached sequence numbers from the
library cache, by temporarily marking them as NOCACHE.
This script is intended to be used prior to shutdown in
single-instance Oracle, to prevent the loss of cached
sequence numbers, should a SHUTDOWN ABORT become
necessary.

-----------------------------------------------------------
--
-- Script: unload_sequences.sql
-- Purpose: to unload cached sequence numbers from the
-- shared pool
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-----------------------------------------------------------
@save_sqlplus_settings

set pagesize 0
set termout off
set echo off

spool unload_sequences.tmp
prompt set echo on
select
'alter sequence ' || sequence_owner || '.' ||
sequence_name || ' nocache;'
from
sys.dba_sequences
where
cache_size > 0
/
select
'alter sequence ' || sequence_owner || '.' ||
sequence_name || ' cache ' ||
cache_size || ';'
from
sys.dba_sequences
where
cache_size > 0
/
spool off

@restore_sqlplus_settings
@unload_sequences.tmp

set termout off
host rm -f unload_sequences.tmp -- for Unix
host del unload_sequences.tmp -- for others

@restore_sqlplus_settings
--

Many thanks for your time.

Jarda

Tom Kyte
August 20, 2002 - 7:49 am UTC

My 2cents on it:

There are three things you can count on:

a) death
b) taxes
c) your sequences will have gaps


Now, consider when you do a shutdown abort (or when it is done for you by something). Do you really have time to do this "unloading"? (no). If you did -- what is to stop another session from reloading them? (nothing). Will to assure you that your sequences have no gaps? (no)

Me -- I really *do not care at all* if one day my sequence gives me 55 and the next restart gives me 100 or 1000 or 10000000 or 1000000000000. A sequence is exactly:

a) a unique number
b) with gaps
c) to be used as a surrogate key
d) with gaps
e) did I mention -- it'll have gaps?


Could I apply archive log files on an offline backup?

Michael, January 07, 2003 - 3:40 am UTC

If I get an offline backup only and I get all archive log files after the offline backupl. Can I restore the database back to the current status?

Tom Kyte
January 07, 2003 - 6:33 am UTC

if and only if the database was in archivelog mode.


the "type" of backup is not meaningful here - only the state of the database -- archivelog mode = point in time recovery, including complete recovery is possible.

noarchivelog mode = what you backed up is what you get. nothing more, nothing less.

for Michael from HK

A reader, January 07, 2003 - 9:40 am UTC

don't open the database
mount it and then perform recovery

Avoid shutdown abort and get some peace of mind with 2 more lines of code.

Moorthy Rekapalli, January 07, 2003 - 11:09 am UTC

Hi,

If you have to do an offline backup for whatever reason, please do the following. You will get some peace of mind.

${ORACLE_HOME}/bin/sqlplus <<EOF
connect / as sysdba
shutdown abort
startup open restrict
shutdown normal
EOF

Now, kickoff your offline_backup_routine.

In my view, the very reason somebody does a "shutdown abort" is to bring down an active instance instantaneously. After that, why not open the database in restrict mode and then shut it down normally.

Opening the database in restrict mode will do two things:

1) Oracle will do crash recovery (if needed) during startup
2) restrict option will prevent a normal end user to get in during that brief period of time - before doing shutdown normal again.

Also, before invoking this sequence, I usually shutdown listener for that instance.

That way, to prevent shutdown normal to happen, somebody should have access to DB server (to make an ipc connection), restricted session privilege and should get in during that brief period of time. Other than DBAs, nobody should have restricted session privilege. So, we are safe there also.

For these reasons, I always suggest to my customers to do this before they take any offline backup so that the database is always shutdown with normal option. When it comes to corporate data, I would prefer peace of mind than tinkering with the fine technical details.

Thanks,
Moorthy Rekapalli.




Re: Could I apply archive log files on an offline backup?

Michael, January 08, 2003 - 4:43 am UTC

After I "startup mount" the database, I typed "recover database until cancel;".
It just prompted "meadium recovered", no any archive log files required and nothing different from the offline backup.
What and how should I do?

for Michael from HK

A reader, January 08, 2003 - 7:45 am UTC

sqlplus "/ as sysdba"
startup mount
recover database using backup controlfile until cancel;


For A reader

Michael, January 10, 2003 - 4:46 am UTC

How about I don't have a backup control file?

Tom Kyte
January 10, 2003 - 7:30 am UTC

how about you create one then via the create control file statement.

Re: followup

Michael, January 14, 2003 - 8:55 pm UTC

I have made an backup control file to recover,
unluckliy, it failed and core dump.

Tom Kyte
January 15, 2003 - 8:01 am UTC

what failed, what core dumped.

Table monitoring requires clean shutdown

Neil, March 06, 2003 - 12:25 pm UTC

Another reason for not shutting down with abort is if
you are using monitoring on tables (and indexes for 9i).
Using shutdown abort, the mon_mods$ table doesn't get updated,
and potentially, you could lose 3 hours worth of
changes (15 minutes in 9i), which could affect your statistics
if using gather stale option.

checking Restore

A reader, August 06, 2003 - 3:43 am UTC

Hi Tom,
Is there any way to judge from the alert files that restore has been performed,a restore from cold backup only, not applying archive logs, like the a cold back of day before was taken and put in the location of the current datafiles and the database was opened or mounted.If not the alert file does any other file store this information.
we do come to know if the database has been shutdown and started but can we come to know if the database is taken a few days behind without quering the data, I know its possible to query the data and tell that but can we use the alert file, as at times the alert file is the only old information,resource available.

Thanks










Tom Kyte
August 06, 2003 - 8:09 am UTC

you should be able to look at the log sequences

...
Wed Aug 6 08:09:17 2003
Thread 1 opened at log sequence 9865
Current log# 7 seq# 9865 mem# 0: /usr/oracle/ora920/OraHome1/oradata/ora920/redo7.log
Successful open of redo thread 1.
Wed Aug 6 08:09:17 2003
.......

if they were going up and then went back down after a startup, that would be an indication.

Offline back up

junior, January 09, 2004 - 12:22 pm UTC

Tom:

I used to do a off line back up in Oracle8/Windows NT by shutting down database and then made copy of those datafiles. However, by accidently, in Oracle 9i/Windows XP environment, I copied all those databfiles without shutting down the database. I restored those files and it works fine. The database is able to be opened. Why? Is it because Windows XP or Oracle 9i version or others? I believe database will not be opened in Oracle 8/NT if I did not shut down database first then make a copy of files.

The database is in Nonarchive Mode. Thanks.



Tom Kyte
January 09, 2004 - 2:21 pm UTC

because you "got very very lucky", "very very lucky"

don't ever even think about relying on that.

Wow....that is lucky....

Mark J. Bobak, January 10, 2004 - 11:45 am UTC

I didn't think that was even possible......

apply archive log files on an offline backup

Jerry, October 14, 2004 - 9:30 pm UTC

Could you please expand on the "apply archive log file on an offline backup" topic?

If that's doable, then why oracle offers stand-by. What's the difference?

Why use backupcontrolfile? How about changed database structure, e.g. new datafiles added later?

Thank you!

Tom Kyte
October 15, 2004 - 11:04 am UTC

standby gets the redo off of production ASAP.
backups are usually done hours after the fact.
standbys are so you don't lose data, even if the disks holding your archives are burnt to a crisp (cause the redo is already on another system, in another location, far away)

standby is for failover -- near real time.
backups take hours to restore typically.

if you don't have the current control file, what else will you use besides a backup of it? you would have to coach the recovery process through these file additions if you didn't backup your control file after doing so already (eg: structural changes, like no-logging operations, should be an indicator that you need to "do something else" to cover yourself in the event of a failure before the next backup)

very clear!

A reader, October 15, 2004 - 11:55 pm UTC

Thanks Tom. It's clear and to the point.

fuzzy backup set

A reader, October 20, 2004 - 3:53 pm UTC

[Quote from your comment above:]

ARCHIVELOG MODE:
go ahead and backup the shutdown aborted instance. DO NOT BACKUP ONLINE REDO logs. That is a HUGE mistake in archivelog mode.
....................
Yes, you can backup the shutdown aborted instance.

....................[end quote]

If I remember correctly, attempting to recover a fuzzy backupset will succeed only if complete recovery is attempted. If I have all the archived logs after the shutdown abort, I still can not do point-in-time recover, but can only recover to the very latest point. Is that correct?

The reason I am asking is that I can have a replica of the database without putting the database into the hot backup mode through BCVs. It's essentially a fuzzy backup set. How useful is it? Can I roll forward with it?

Thank you!

Tom Kyte
October 20, 2004 - 5:12 pm UTC

complete or cancel based -- either or, it doesn't care.

you don't have to fully recover , you can certainly do cancel based recovery with "fuzzy files"

How about time-based recovery?

A reader, October 21, 2004 - 10:59 am UTC

I am fairly new to Oracle and still is not quite clear on the reasoning. I can understand why cancel-based recovery works, i.e., at the log switch time, there is a checkpoint and all the files are in-sync. But can I recover using time-based or SCN-based recovery based on a fuzzy backup set? If I can, then there is really no difference if I put the database in the hot backup mode at time of backup as long as the database is in archivelog mode. Right?

Thanks!

Tom Kyte
October 21, 2004 - 2:51 pm UTC

yes -- you can recover that way with fuzzy.


YES there is a huge difference if you do a hot backup using OS tools and haven't put the database in backup mode.

if you use RMAN (integrated backup tool) -- no "backup mode" needed. If you use anything else -- you MUST put the database files into backup mode before touching them to do a hot backup.

read:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:271815712711 <code>



Final clarification

A reader, October 22, 2004 - 12:24 pm UTC

Thanks Tom for the explanation That's very helpful for understanding hot backup.

To clarify the point for backup of shutdown aborted database, as long as the database is in archivelog mode and I have all the archived logs afterwards, it should not matter. I can do complete recovery or any type of incomplete recovery (SCN, timed-based or cancel-based), right?

Tom Kyte
October 22, 2004 - 6:15 pm UTC

correct.

confused

A reader, October 28, 2004 - 3:42 pm UTC

I got the following response from one person in oracle:

"When Oracle restarts, and sees a disk image that represents a crashed database, it considers what is on disk "VALID". What is on disk is NOT A RESTORED BACKUP, but an OPENED database. So, Oracle performs its crash recovery magic to make the database USABLE again. But in the process of doing so, it will discard uncommitted transactions (as if there is a rollback requested). The controlfile will be radically different in state from the restored controlfile when you are doing a database hot backup file restore and recovery with archived logs. In fact, Oracle will not allow the saved archived logs to be applied, because those archived logs do not BELONG to this crashed recovered OPEN database. The control file noted SCN sequence numbers are different based on the restored controlfile and backups compared to the crashed recovered OPEN database. "

Therefore, the conclusion is that PIT recovery is not supported. Could you please help clarify?

Tom Kyte
October 28, 2004 - 7:39 pm UTC

I'm confused too -- what are you talking about?

no context here for me, what point are you trying to make????

more update

A reader, October 29, 2004 - 12:35 am UTC

Sorry about the unclear post. I was trying to figure out if backup of crashed db is a valid backup option. For production database, storage software can split a replica(mirror) off. At the time of split, it can hold the IO, therefore there will be no data block head/tail out of sync during the split. I can mount the replica on another host and backup the database (without the crash recovery, just mount the filesystem). The backup is in essence a crash dump of the db. Based on the previous thread above, if I can backup all the archivelogs from the production db afterwards, I can do restore/recovery to any time. But based on the above last post, I can only do crash recovery and can't do point-in-time restore.

Could you please help me understand the mechanism here? Is the replica a valid backup option?

Thank you!

Tom Kyte
October 29, 2004 - 8:18 am UTC

<quote>
I was trying to figure out if backup of crashed
db is a valid backup option.
</quote>

yes it is

but -- you would in your case put the tablespaces into backup mode, split, take them out and then you can backup the split partition.

search otn, there are actually many white papers on doing just this -- people have been doing it for years

how about not in hot backup mode

A reader, October 29, 2004 - 11:38 am UTC

I fully understand that one option is to put the tablespaces in the hot backup mode prior to the replica split and avoid all the problems. However, for the other approach, not putting database in the hot backup, just hold off IO for a short period of time and create sort of crash dump, there are quite some different opinions out there as to whether it's a valid database backup. From the database concept perspective, I'd like to know if oracle can roll forward based on such backups.

Thank you!

Tom Kyte
October 29, 2004 - 11:59 am UTC

we do have an alter to suspend IO for such a thing but why not do it "better"?

yes, it'd be valid for a restore.

Getting problem during putting database in archive mode

Shan, January 09, 2005 - 1:14 am UTC

Hi Tom,

I am facing very critical scenario is.
One database which is in noarchive mode migrated to archive log mode with a different name. I recreated controlfile with noarc mode then took database open. when i was trying to shutdown immediate it was in hung state so i killed pmon process. again i startup the database and trying to shutdown immediate but cud nt get success after that i did shutdown abort. if i am trying to put this database in arhive mode its asking for recovery.

what to do .

Thanks


Tom Kyte
January 09, 2005 - 11:43 am UTC

geez, i have no idea what you did -- but please contact support and walk through what you need to do.

My immediate reaction would not have been "kill pmon".

It may be as easy as "recover database", shutdown, startup mount, alter database archivelog and open.

what does "cud" which is " Food brought back up into the mouth by an animal from its first stomach to be chewed again" have to do with the database. And it sounds like you are on Unix (killed pmon) but you reference 'nt'. very confusing.



Redo Log File setup

j, February 19, 2005 - 1:16 pm UTC

Dear Tom,
What could be a better setup for redo log files,
1) 10 groups, one member each group or
2) 5 groups, two members each group.
Which setup would have less impact in the perfomance, and why.
thanks


Tom Kyte
February 19, 2005 - 3:49 pm UTC

the first one is bad, no redundancy.

the second is good, redundancy.


forget performance, think "data protection".




offline backup in restrict mode

A reader, April 01, 2005 - 3:35 am UTC

Hi

I have seen offline backup scripts which does this

shutdown immediate
startup restrict
backup datafiles

is this supported??!!

Tom Kyte
April 01, 2005 - 8:29 am UTC

well, as long as there is a bunch of alter tablespaces in there to begin backup or you are using rman

and you are in archive log mode...

need more info to say for sure.

it´s noarchive log

A reader, April 01, 2005 - 3:53 pm UTC

Hi

The database is in noarchive log, the strange thing is the guys is telling me they got this script from Veritas

Tom Kyte
April 01, 2005 - 4:31 pm UTC

it is not a complete script. hard to comment.

but if they do that in noarchive log mode, you have a bunch of bits in a backup and nothing more. nothing restorable anyway.

Clarification

A Reader, May 04, 2006 - 1:52 pm UTC

Tom,

Can you please explain the difference between below two points:

1. recover database until cancel;

2. recover database until cancel using backup controlfile;

If you want , pls give some example to I can understand it easily.

Thanks in Advance.



Tom Kyte
May 04, 2006 - 5:04 pm UTC

#1 uses current control file
#2 uses a backup of the control file you took

don't see how to give 'an example', if you have the current control files, you would use them, if you lost them all - you would use a backup controlfile and tell us (so we don't stop at the point when the backup controlfile was taken - we keep going)

Need more detailed information...

Sri, August 27, 2007 - 6:31 pm UTC

Tom,

First of all thanks for the great support!

You have adviced when a cold backup is taken (and not adviced to take a cold backup!) to -

1. shutdown abort
2. startup restrict
3. shutdown immediate
4. backup the files.

The question is -

Oracle opens the database once the roll-forward phase of the instance recovery is completed, now does it wait for the rollback phase to complete or does it simply shutdown since the next command is shutdown immediate?

I would believe it to wait for the completion of the rollback phase, but then how much of a difference does this approach really make in comparision to -

1. shutdown immediate
2. backup files.

As even shutdown immediate essentially rolls back the uncommitted data before shutting down the database.

Thanks,
Sri
Tom Kyte
September 04, 2007 - 12:35 pm UTC

where did I advise you to shutdown abort?

I said it could be done, never said "do it"


shutdown immediate is all you need, do not do 1 and 2.

you do a shutdown immediate in either case, the results are the same - consistent files on disk.

Difference between shutdown abort and pulling the plug out

Bob, May 19, 2013 - 10:37 pm UTC

Hi Tom,

Is there any subtle difference between shutdown abort and
Pulling the plug out?

Thanks,
Bob
Tom Kyte
May 21, 2013 - 2:43 pm UTC

not too much from the Oracle perspective - they both necessitate instance recovery.

I guess pulling the plug could be a little more severe - as it requires the OS to "instance recovery" itself - fix up file systems and stuff like that. You are relying on two things to be able to fully recover now - not just the database.

Chuck Jolley, May 21, 2013 - 3:56 pm UTC

Pulling the plug out and then deleting something before restarting the database is how I test backup and recovery.
Or if I need to demonstrate to some manager why we are on Oracle and not something else. ;)

What's the difference between pulling the plug out and shutdown abort

Bob, May 26, 2013 - 3:35 am UTC

Hi Tom,

Thanks for your prompt response! So for example in OEL Linux,
which files at the o/s level can we expect to get cleaned up? Would it be the "bootstrap" code, .rc files(?). I know it does it all automatically behind the scenes but it would be good to know what it does at the file system level and where there would be potential for failures!

Which files would be involved in Solaris?


Thanks in advance :-)
Tom Kyte
May 29, 2013 - 5:11 pm UTC

it would be any open file, all files. The OS 'recovers' the file system.