recover database command in batch file 1

[kasparov]

Hello

I'm working in Windows Server 2003 with Oracle 10. I'm trying to recreate our test database every night by using the database files from the live system. I copy the files over & use a text copy of the control files to create the database. My problem is that I need to run this:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

It works fine on the command line but I don't know how to code this in a batch file - in particular how do I get the system to recognise a reply of "cancel"? Whatever I try SQLPLUS thinks my reply is directed at the SQL prompt.

Any advice appreciated.
Chris

 

[kasparov]

It's OK - I hadn't tried enough variations on the syntax. This works:

RECOVER DATABASE using backup controlfile until cancel;
cancel

was pretty simple really ...

 

[KenCunningham]

But have a star for posting the solution anyway. Too often these threads are left hanging.

I want to be good, is that not enough?

 

[karluk]

Don't you need at least one blank line before the cancel? Otherwise Oracle won't apply any archive logs and your database will be left in an inconsistent state?

 

[kasparov]

I don't seem to need a blank line before the cancel - this is the code I'm using:

RECOVER DATABASE using backup controlfile until cancel;
cancel

alter database open resetlogs;
exit;

I put the blank line in after the cancel just to make it a bit easier to read. It's only on a test database so I've not tested it to the nth degree but our users seem to think it's OK. Might the syntax be different on Unix or other versions?

Chris

PS - Thanks for the star Ken - my first one! <Thinks - perhaps I could get lots of stars by posting questions & answering them straightaway >

 

[karluk]

The reason I asked is that if you did this type of recovery interactively, you would be prompted for input every time the recovery wanted to apply an archive log. Your choices would be to accept the proposed archive log file name and location by hitting enter, change the file name and/or location by entering a different value, or typing "cancel" to halt the recovery.

So what your batch file appears to be doing is to cancel the recovery without applying any archive logs at all. I would expect this to fail if you are recovering from an online backup, since an online backup has database files in varying states - some were written to more recently than others.

But you report that the database opens without error, so I would infer that your backup is an offline backup, where the datafiles are already in a consistent state. Either that or I am misinterpreting what the batch file is doing.

If I'm right about what's happening, you don't appear to need the Oracle recovery step at all. It doesn't seem to be doing anything. You could delete the "recover database ..." and "cancel" lines and proceed immediately to "alter database open resetlogs;

 

[kasparov]

Hi karluk - You're right, I'm recovering from an offline backup but I'm creating a new database (on our test server from the live server) with a different instance name, etc so I need to create new control files (they're not included in the files copied from the live system). That's why I'm doing the RECOVER (& I use a permanent text copy of the control file).

At least I think it's why I use RECOVER (it's what I was advised to do). If there are better ways to do this I'm happy to get further advice!

Thanks, Chris

 

[karluk]

I would definitely experiment with deleting the "recover database ..." and "cancel" lines from your batch file. It's easy enough to put them back again if you find that they are really necessary, but I honestly don't see what they accomplish.