Server-1 - Source
Server-2 - Destination
Step - 1 - Put the source database in backup mode.
Step - 2 - Copy all the datafiles,redologfiles,parameter files and
archived log files from server 1 to server 2.
Step - 3 - At the source datbase sql prompt execute the following
command sqlplus>backup controlfile to trace.
(Output will be generated at udump,this will be henceforth
called create_db.sql)
Step - 4 - Copy this file to server-2
Step - 5 - Edit the create_db.sql :
5.1 So as to represent new locations.
5.2 Replace "Reuse" with "set" (CREATE CONTROLFILE set DATABASE)
5.3 Update the SID of Database.
Step - 6 - Restore the initialisation parameter file to
$ORACLE_HOME/dbs
Step - 7 - Edit the file with new db name and file locations.
Step - 8 - Set ORACLE_SID with new database name
Step - 9 - Connect as "sysdba"
[oracle]/ > sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 27 20:47:17 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
Step - 10
SQL> @create_db.sql
ORACLE instance started.
Total System Global Area 813694976 bytes
Fixed Size 2033504 bytes
Variable Size 528488608 bytes
Database Buffers 268435456 bytes
Redo Buffers 14737408 bytes
Control file created.
It is important to specify CANCEL when you are asked for the last log.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4
Specify log: {
{Specify the location of the log file here...if the log required is present in archivelog files,else press type cancel)
CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> Shu immediate
SQL>Startup
Add temp file and Database is ready to use.
Useful Links:
http://www.blogger.com/www.dba-oracle.com/oracle_tips_db_copy.htm
http://www.blogger.com/www.dba-oracle.com/topica/super_fast_database_cloning.htm
=================================================================================================
[oracle] > vi create_db.sql
STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/pre/RESTORE_ACTIVITY/redo1a.log',
'/pre/RESTORE_ACTIVITY/redo1b.log'
) SIZE 1000M,
GROUP 2 (
'/pre/RESTORE_ACTIVITY/redo2a.log',
'/pre/RESTORE_ACTIVITY/redo2b.log'
) SIZE 1000M,
GROUP 3 (
'/pre/RESTORE_ACTIVITY/redo3a.log',
'/pre/RESTORE_ACTIVITY/redo3b.log'
) SIZE 1000M
-- STANDBY LOGFILE
DATAFILE
'/pre/RESTORE_ACTIVITY/system01.dbf',
'/pre/RESTORE_ACTIVITY/undo_ts01.dbf',
'/pre/RESTORE_ACTIVITY/sysaux01.dbf',
'/pre/RESTORE_ACTIVITY/index01.dbf',
'/pre/RESTORE_ACTIVITY/data01.dbf',
CHARACTER SET UTF8;
