creating new database from old database cold backup.

I am writing below steps that will create a new database ICPORA that will be a complete replica of old database ICPORA.

Scenario: I have new database ROCK and old database ICPORA, I have cold backup of ICPORA, and Now I want recreate ICPORA at ROCK database. I have taken backup of both database.

1. Copy all datafiles, controlfiles, redologs, archivelogs, init files from ICPORA to new database ROCK (oradata). Delete all files from ROCK database after backup and paste ICPORA files.

2. Check init.ora file and modify/specify the correct paths for ArchiveLog, Controlfiles and other files.

3. If you have new database with the old DB name then go to step 5 otherwise step 4. In my situation I have new database with name ROCK, so I need ICPORA service/instance to operate, for that I need to create new service ICPORA.

4. create new service with old database name with ORADIM utility.

H:\>oradim -new -sid icpora -intpwd icpora -startmode m -pfile 'D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA'

Instance created.

5.
H:\>set oracle_sid=icpora

H:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 18 15:19:02 2009
Copyright (c) 1982

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> startup mount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\DATAFILES\SYSTEM01.DBF'

Note: The above error is encountered because the contents/information stored in the control file is different from the current situation. (i.e paths of files). We need to recreate the controlfile, the below command will generate a script for recreating a controlfile in udump folder.

SQL> alter database backup controlfile to trace;
Database altered.

6. Copy the important contents of script generated in udump folder and modify with new paths and execute it. Note execute in NOMOUNT Mode.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount pfile='D:\oracle\product\10.2.0\oradata\icpora\INITicpora.ORA';
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 1253124 bytes
Variable Size 486539516 bytes
Database Buffers 578813952 bytes
Redo Buffers 7135232 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ICPORA" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 18688
7 LOGFILE
8 GROUP 1 'D:\oracle\product\10.2.0\oradata\icpora\REDO01.LOG' SIZE 100M,
9 GROUP 2 'D:\oracle\product\10.2.0\oradata\icpora\REDO02.LOG' SIZE 100M,
10 GROUP 3 'D:\oracle\product\10.2.0\oradata\icpora\REDO03.LOG' SIZE 100M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM01.DBF',
14 'D:\oracle\product\10.2.0\oradata\icpora\SYSTEM02.DBF',
15 'D:\oracle\product\10.2.0\oradata\icpora\SYSAUX01.DBF',
16 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO01.DBF',
17 'D:\oracle\product\10.2.0\oradata\icpora\ABAMCO02.DBF',
18 'D:\oracle\product\10.2.0\oradata\icpora\EDP01.DBF',
19 'D:\oracle\product\10.2.0\oradata\icpora\UNDOTBS2.DBF',
20 'D:\oracle\product\10.2.0\oradata\icpora\USERS01.DBF',
21 'D:\oracle\product\10.2.0\oradata\icpora\AXIS_ALERT01.DBF',
22 'D:\oracle\product\10.2.0\oradata\icpora\RISKMETER01.DBF'
23 CHARACTER SET WE8MSWIN1252
24 ;

Control file created.

SQL> RECOVER DATABASE
Media recovery complete.
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;
Database altered.