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:
C:\oracle\product\10.2.0\oradata\ORCL\CONTROL01.CTL
C:\oracle\product\10.2.0\oradata\ORCL\CONTROL02.CTL
C:\oracle\product\10.2.0\oradata\ORCL\CONTROL03.CTL
C:\oracle\product\10.2.0\oradata\ORCL\REDO01.LOG
C:\oracle\product\10.2.0\oradata\ORCL\REDO02.LOG
C:\oracle\product\10.2.0\oradata\ORCL\REDO03.LOG
C:\oracle\product\10.2.0\oradata\ORCL\SYSAUX01.DBF
C:\oracle\product\10.2.0\oradata\ORCL\SYSTEM01.DBF
C:\oracle\product\10.2.0\oradata\ORCL\TEMP01.DBF
C:\oracle\product\10.2.0\oradata\ORCL\UNDOTBS01.DBF
C:\oracle\product\10.2.0\oradata\ORCL\USERS01.DBF

as well as the spfile
C:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA

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.

Questions:
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?
Sharp2bAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbmullenCommented:
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
startup



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
0
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
 
set ORACLE_SID=ORCL
 
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
oradim -NEW -SID ORCL -STARTMODE auto -SRVCSTART demand
 
:: Start/Create the listener
lsnrctl start

Open in new window

0
Sharp2bAuthor Commented:
Thanks, you put me on the track to get this resolved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.