Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Recovery Question

Re: Recovery Question

From: Howard J. Rogers <>
Date: Mon, 15 Jul 2002 14:29:52 +1000
Message-ID: <agtjar$2gm$>

"Brian Li" <> wrote in message
> I'm a newbie using Oracle 9i.
> I have some questions in the followings, please kindly answer the
> Question 1
> If I lost a control file (no backup),
> how can I startup a database?
> how can I recover a control file?

Lose anything without a backup, and you are asking for trouble. The short answer is that without some form of backup of the controlfile, you can't open your database, nor recover the control file (you can only 'recover' that which you are first able to restore from backup).

However, the rather longer answer is that there is a SQL command, called 'create controlfile' which can be issued in the NOMOUNT stage, which will re-construct a missing controlfile. The syntax is, however, tricky: it demands to know exactly where all your datafiles and online redo logs are, and gets even worse when you have tempfiles and read-only tablespaces. I once spent 6 days emailing someone through manually issuing that command, and it wasn't pretty.

There is no excuse, however, for being in that situation. The command 'alter database backup controlfile to trace;' causes a tracefile to be produced which contains the complete (and accurate) 'create controlfile' syntax needed for your particular system. It takes all of 3 seconds to execute on a bad day.

The even longer answer is that if you've multiplexed your controlfiles, you can lose all bar one of them at any time, and still get the database open by simply copying the remaining good copy into the 'holes' left vacant by the missing ones.

> Question 2
> If I lost all archive log,
> how can I recover the database?

You do a 'recover database until cancel' and get it to prompt you for the first archive it wants, and immediately type in the word 'cancel', followed by 'alter database open resetlogs'. It's called a (very) incomplete recovery.

> Question 3
> If I lost all redo log,
> how can I recover the database?

Same answer as above, with the slight difference that you let Oracle apply all the redo it can find from the archives, and when it bombs out because it can't find the online redo logs, you *then* halt the process with the 'cancel' directive. It's called an incomplete recovery.

> Question 4
> If I lost all control file, redo log and archive log,
> how can I recover the database?

Is this some sort of homework assignment? If all you've got are your datafiles, and no backup of controlfiles, you then re-create the controlfiles using the 'create controlfile' command. You get the database into the mount state. You ask to 'recover database until cancel', you then immediately say 'cancel', and a set of new online redo logs is created for you. You then go and become a petrol pump attendant, because it is evident that (if you've got yourself into this state) you don't know the first thing about DBAing.

> Question 5
> About Offline Backup Procedure,
> shutdown normal;
> copy all datafile to backup folder
> copy all control file to backup folder
> copy all redo logs to backup folder
> copy all archive logs to backup folder
> Is it a correct procedure?

No it's not. You're copying archive logs. That means you must be in archivelog mode. In archivelog mode, you do not, ever, copy online redo logs, and it matters not the slightest whether you are taking hot or cold backups. You just don't do it.

> Why I have to need to backup redo log only?

Don't know what that question means. You aren't "only" backing up the "redo logs", as your own question states. But in any case, see above: you mustn't, ever, backup the current redo log when in archivelog mode.

> Why NOT I have to need to backup archive log?

What? Your own question above shows you backing up "all archive logs to backup folder".

> Question 6
> Why NOT I have to backup init.ora?

You ought to, but it isn't strictly essential, since the alert log includes all the relevant parameters every time you start up.

> How can I recover init.ora if I lost it?

Use the alert log.

And since you said right at the start that you're using 9i, you ought to backup your spfile, too, and if that's lost, recovery is: recover an init.ora, and use the 'create spfile from pfile' command.

> Question 7
> What is the their difference?
This is getting ridiculous. Tell you what: I'll answer all these questions in one go: do some reading about backup and recovery, or go on a backup and recovery training course. These are utterly basic commands without which you can't be a DBA.

> When use them?
> sometimes call ALTER DATABASE OPEN;?
> Question 8
> What is the their difference?
> recover database;
> recover database until cancel;
> recover database using backup controlfile;
> recover database until cancel using backup controlfile;
> recover database using backup controlfile until cancel;
> What is using backup controlfile? where/what backup controlfile it is
> what until cancel means?
> Please tell me more details.
> The above question make me confusing during recovery the database.
> Thanks a lot! ^^
> Brian

Brian: if this is a homework assignment, I've given you more than enough to be going on with. If it's just general interest, I've also given you a good start, but you really need to show some gumption of your own. Read the documentation, read the umpteen books and papers on the subject that are scattered throughout bookstores and websites around the globe.

Backup and recovery is a very, very big subject, and a critical one too. You can't get all you need to know by asking these sorts of questions here, and if I were to answer them thoroughly, I'd be here till Christmas.

You need to read up on Complete Recoveries, Incomplete recoveries, startup, shutdown, archiving, hot backups, cold backups, and consistency of datafiles.

For starters.
HJR Received on Sun Jul 14 2002 - 23:29:52 CDT

Original text of this message