Oracle 9i Restore Backup on different host

As a DBA this task we perform so many times.

In the following cases.

1. Create CLONE database

2. Create same copy as PRODUCTION database

3. TEST Production Database Backup

4. Update TEST & UAT database with Production Database

To complete above fours point we require Production Database Backup, In am using Oracle 9ir1 backup for this demostration.

How to Take Oracle 9i Online User Managed Backup

The following steps will required to complete above task.

1. Create Oracle Services

2. Create Password file

3. Edit following parameter location

1. bdump 2. udump 3. cdump 4. log_archive_dest 5. controlfile location

4. connect with SYSDBA user and create spfile from backup pfile

then startup database in nomount mode

5. Restore controlfile from backup and mount the database

6. If source and target database file directory structure is different then rename all datafile to new target location.

7. after rename all datafile & redolog files location restore all archivelog files from backup and open database with UNTIL CANCEL option.

(In this demo target or source db location is same)

8. open database with RESETLOGS.

C:\Ora9ihome\BIN>oradim -new -sid ORA9I

C:\Ora9ihome\BIN>orapwd file=C:\Ora9ihome\database\pwdORA9I.ora password=oracle entries=5

C:\Ora9ihome\BIN>set oracle_sid=ora9i

C:\Ora9ihome\BIN>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Sat Aug 2 17:21:38 2008
(c) Copyright 2001 Oracle Corporation.

All rights reserved.

SQL> conn sys as sysdba

Enter password:

Connected to an idle instance.

SQL> create spfile from pfile='e:\ora9i\backup\init.ora';
File created.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 118255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

SQL> host copy e:\ora9i\backup\*.ctl c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\CONTROL01.CTL

e:\ora9i\backup\CONTROL02.CTL

e:\ora9i\backup\CONTROL03.CTL

3 file(s) copied.

SQL> alter database mount;
Database altered.

SQL> host copy e:\ora9i\backup\*.dbf c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\INDX01.DBF

e:\ora9i\backup\SYSTEM01.DBF

e:\ora9i\backup\TOOLS01.DBF

e:\ora9i\backup\UNDOTBS01.DBF

e:\ora9i\backup\USERS01.DBF

5 file(s) copied.

SQL> set autorecovery on

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;
Database altered.