[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-14
4
Medium Priority
?
748 Views
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:
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?
0
Comment
Question by:Sharp2b
  • 3
4 Comments
 
LVL 10

Accepted Solution

by:
dbmullen earned 2000 total points
ID: 22960764
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
 

Author Comment

by:Sharp2b
ID: 22974926
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
 

Author Comment

by:Sharp2b
ID: 22979716
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
 

Author Closing Comment

by:Sharp2b
ID: 31516724
Thanks, you put me on the track to get this resolved.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
The main intent of this article is to make you aware of ‘Exchange fail to mount’ error, its effects, causes, and solution.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

834 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