Restoring Oracle database on different Windows Platforms

Purpose of this post is to answer the question of restoring oracle database on different windows platform. Three oracle databases have been created on Windows 2000 Advanced Server (Oracle SID=Win2000),Windows 2003 Enterprise Server (Oracle SID=Win2003) and Windows XP (Oracle SID=WinXP). On all the boxes Oracle 10gR2 have been installed.

Steps of restoring the database from Windows 2000 (Source Server) to Windows 2003 (Destination Server) are enumerated as follows...

1) Take the cold backup of the database present in source server. Also backup spfile.

2) Shift these files to the destination server. In source server the database files are present in c:\oracle\product\10.2.0\oradata\Win2000. In this experiment, these files will be restored in in D:\oracle\product\10.2.0\oradata\Win2000. So renaming of files is required here.

3) Create the default folders bdump,cdump,adump,dpdump and pfile in d:\oracle\product\10.2.0\admin\Win2000 folder.

4) Create the windows service for oracle. Specifying startmode as manual, will not start the instance.

D:\Documents and Settings\Administrator>oradim -new -sid Win2000 -startmode manual

5) CONTROL_FILES parameter is not modifiable through ALTER SYSTEM SET command. Create pfile from this spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.
SQL> create pfile='d:\oracle\product\10.2.0\db_1\database\initWin2000.ora' from spf
ile;

File created.

6) Change the values of CONTROL_FILES,DB_RECOVERY_FILE_DEST,BACKGROUND_DUMP_DEST and other location specific parameters, pointing to D drive.

7) Again connect to SQL and create the spfile.

D:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:20:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

8) Execute startup command (instance will come throught spfile) in nomount stage and check
the value of CONTROL_FILES parameter.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 104858280 bytes
Database Buffers 180355072 bytes
Redo Buffers 2945024 bytes
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\ORACLE\PRODUCT\10.2.0\ORADA
TA\WIN2000\CONTROL01.CTL, D:\ORAC
LE\PRODUCT\10.2.0\ORADATA\WIN2000
\CONTROL02.CTL, D:\ORACLE\PROD
UCT\10.2.0\ORADATA\WIN2000\CONTRO
L03.CTL

9) Mount the database.

SQL> alter database mount;

Database altered.

10) Rename the datafiles and online redo log files. One example shown below. If restoring in the same location then this step can be skipped.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF


SQL> ed
Wrote file afiedt.buf

1 alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF' to
3* 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF'
SQL> /

Database altered.



SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database rename file
2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG'
3 to
4 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG';

Database altered.


11) Double check the new file names/locations and start the database.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\EXAMPLE01.DBF

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO03.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\Win2000\REDO01.LOG

SQL> alter database open;

Database altered.

12) Take the hot/cold backup of the database.

In the same fashion, database from Windows 2000 box have been restored to Windows XP box. In addition to that following restore combinations have been tried.

Database from Windows XP box to Windows 2003 box.
Database from Windows XP box to Windows 2000 box.
Database from Windows 2003 box to Windows XP box.
Database from Windows 2003 box to Windows 2000 box.

In all the above combinations successful restoration of database has been achieved.

Conclusion:- Thus among 2000, XP and 2003 Windows, databases can be restored. Experiments have been done for oracle 10gR2 version. This will help to prepare test machines regardless of specified Windows Platforms.