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

Posted on 2008-11-14
Last Modified: 2013-12-19
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?
Question by:Sharp2b
    LVL 10

    Accepted Solution

    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


    Author Comment

    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?

    Author Comment

    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
    oradim -NEW -SID ORCL -STARTMODE auto -SRVCSTART demand
    :: Start/Create the listener
    lsnrctl start

    Open in new window


    Author Closing Comment

    Thanks, you put me on the track to get this resolved.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    One of the frustrating downsides to using third party RAID hardware is the frequent lack of native driver support in the standard OS.  During install, Windows prompts for third party storage drivers from CD or USB so it is straightforward, but it ca…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
    This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now