How to recover an Oracle 10.2 db from a cold backup on Win XP

I have an Oracle database on my local Windows XP machine that I use for development and testing.

Up to now, I have done regular exports of selected schemas for backup purposes. Even though I have managed to automate this over the years, I still find it a bit tedious. It would be much easier to simply do a cold backup while backing up the rest of my files. I just use XCOPY to an external harddrive which works just fine.

The reasons for wanting a cold backup are:
* I don't have Oracle running at all times.
* I don't use ARCHIVELOGS.
* This is a personal test system.
* When recovery is needed, I start with a fresh XP or at least with a fresh Oracle installation.
* Creating a new instance, re-creating and importing all schemas is tedious and requires lots of manual work and thinking (the latter is the hard bit...).

In order to use the export approach, I have to make sure to start up Oracle as part of my backup procedure. This also takes time. In order to use XCOPY, I simply shut down the Oracle services as the first task in my backup script.

The idea is that I shut down Oracle and xcopy all my Oracle datafiles:

as well as the spfile

There is lots of information on the net about backing up Oracle but it's mostly about 24/7 databases and all recommendations are to use hot backups. This is not needed in my scenario. I was not able to find simple consistent information how to recover a backup like the one mentioned above.

1. Do I need any other files to be able to restore?
2. Do I need to export a pfile or can I use the spfile?
3. How do I recover the database on a fresh installation of Oracle suing the files as above?
Who is Participating?
dbmullenConnect With a Mentor Commented:
you have everything..  only other thing I might do

alter database backup controlfile to trace as  'C:\oracle\product\10.2.0\oradata\backup_control_file.trc';

to recover from this cold.
shutdown database
delete C:\oracle\product\10.2.0\oradata\*
copy files back from external-location

Sharp2bAuthor Commented:
Thanks a lot.

I have only seen that you could write this to the trace file, but you show that I can specify the file name.

I think I'm pretty close now but I could use some clarifications:
Is it correct that the generated script (from ...backup controlfile...) will create new control files so these should not be copied back?

Since this is on WIndows, If I have a fresh Oracel install, I suppose I also need to use ORADIM (ORAPWD as well?) to create the service etc before I can connect to run the script?
Sharp2bAuthor Commented:
I have now done some tests on a fresh VM and I got no indication that I need to re-create the controlfiles from a backup controlfile which is stated in all articles I found on the subject.

This is the procedure I followed:
Shut down the original instance using the NET STOP command
I made an xcopy type of  backup of the data-, control-, and redo-log files.
I backed up SPFILEORCL.ORA and PWDORCL.ora from %ORACLE_HOME%\database.
I also copied tnsnames.ora, sqlnet.ora, and listener.ora

I installed Oracle without creating a database, making sure I applied the same patch set as in the original database.

I edited the backed up TNS files according to the new server name and IP address.

I then ran the attached batch file

I was a bit surprised to see that it actually worked. I can see no errors and the "new" database seem to be up running just fine.
mkdir C:\oracle\product\10.2.0\admin\ORCL\adump
mkdir C:\oracle\product\10.2.0\admin\ORCL\bdump
mkdir C:\oracle\product\10.2.0\admin\ORCL\cdump
mkdir C:\oracle\product\10.2.0\admin\ORCL\dpdump
mkdir C:\oracle\product\10.2.0\admin\ORCL\pfile
mkdir C:\oracle\product\10.2.0\admin\ORCL\udump
mkdir C:\oracle\product\10.2.0\oradata\ORCL
mkdir C:\oracle\product\10.2.0\db_1\cfgtoollogs\dbca\ORCL
mkdir C:\oracle\product\10.2.0\db_1\database
mkdir C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
xcopy X:\backup\ORCL\*.* C:\oracle\product\10.2.0\oradata\ORCL\
xcopy X:\backup\database\*.* C:\oracle\product\10.2.0\db_1\database\
xcopy X:\backup\TNS\*.* C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
:: Create the database service
:: Start/Create the listener
lsnrctl start

Open in new window

Sharp2bAuthor Commented:
Thanks, you put me on the track to get this resolved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.