Clone DB on New Host with HotBkp

Source Database: Windows XP/Oracle 10gr1
Target Database: Windows 2003/Oracle 10gr1

Source Database:

1. Take Controlfile Backup

SQL> alter session set tracefile_identifier=my_bkp_control_file;

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --check udump folder ($ORACLE_HOME/admin/udump)
SQL> --filename :: _ora_xxx_my_bkp_control_file.trc

2. Take datafiles backup
SQL> alter database begin backup;

SQL> --copy all *.dbf files to BACKUP folder
Note: except TEMP tbs

SQL> alter database end backup;

SQL> alter system switch logfile;

System altered.
Note: Must Switch logfile after end backup command

3. Create init.ora from spfile

SQL> create pfile='c:\tmp\init.ora' from spfile;

Target Database:
1. Change Below parameters location in init.ora file
bdump,udump,cdump
log_archive_dest_n
control_files

2. Restore datafile and controlfile and init.ora backup to target database

3. Set ORACLE_SID=[sid]
C:\>set oracle_sid=orcl

4. Create Oracle Services through ORADIM utility
C:\>oradim -new -sid orcl -startmode auto
Instance created.

5. Create Password File
C:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5

5. startup instance with nomount through init.ora file.
C:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Sep 2 16:35:10 2007

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile='d:\orcl\admin\pfile\init.ora';
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90437052 bytes
Database Buffers 192937984 bytes
Redo Buffers 1048576 bytes

6. Create controlfile from trace file
Note: Create Controlfile with RESETLOGS Options

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 'd:\ORCL\oradata\REDO01.LOG' SIZE 10M,
9 GROUP 2 'd:\ORCL\oradata\REDO02.LOG' SIZE 10M,
10 GROUP 3 'd:\ORCL\oradata\REDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'd:\ORCL\oradata\SYSTEM01.DBF',
14 'd:\ORCL\oradata\UNDOTBS01.DBF',
15 'd:\ORCL\oradata\SYSAUX01.DBF',
16 'd:\ORCL\oradata\USERS01.DBF'
17 CHARACTER SET WE8MSWIN1252;

Control file created.

7. apply neccessary archivelog file
SQL> recover database using backup controlfile;
ORA-00279: change 529461 generated at 09/03/2007 12:54:37 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_29_632229757.ARC
ORA-00280: change 529461 for thread 1 is in sequence #29


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 529600 generated at 09/03/2007 12:59:51 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_30_632229757.ARC
ORA-00280: change 529600 for thread 1 is in sequence #30
ORA-00278: log file 'D:\ORCL\ARC\LOG_1_29_632229757.ARC' no longer needed for
this recovery


ORA-00308: cannot open archived log 'D:\ORCL\ARC\LOG_1_30_632229757.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

8. Perform Incomplete Recovery with UNTIL CANCEL
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 529600 generated at 09/03/2007 12:59:51 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_30_632229757.ARC
ORA-00280: change 529600 for thread 1 is in sequence #30


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

9. Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.

Note: All archivelog files needed after database backup for recovery.