(快速複製) Fast Clone Oracle Database Instance to other machine

Run SQL Command on Source Instance, to generate the SQL statement in trace file which will used to create the Control File on new Instance.

(  How to find the Path of trace file? : user_dump_dest parameter of the init.ora  )

alter database backup controlfile to trace;

This statement will output to the newest trace file( <DB_Name>_ora_123.trc  )
Copy this part of SQL statement to a new file. (Eg. control.sql)

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "BBCABCD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    'F:\ABCD\ORACLE\BBCABCD\REDO01.LOG'
  ) SIZE 50M,
  GROUP 2 (
    'F:\ABCD\ORACLE\BBCABCD\REDO02.LOG'
  ) SIZE 50M,
  GROUP 3 (
    'F:\ABCD\ORACLE\BBCABCD\REDO03.LOG'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'F:\ABCD\ORACLE\BBCABCD\SYSTEM01.DBF',
  'F:\ABCD\ORACLE\BBCABCD\SYSAUX01.DBF',
  'F:\ABCD\ORACLE\BBCABCD\UNDOTBS01.DBF',
  'F:\ABCD\ORACLE\BBCABCD\USERS01.DBF',
  'F:\ABCD\ORACLE\BBCABCD\ABCD_DATA01.DBF',
  'F:\ABCD\ORACLE\BBCABCD\ABCD_DATA02.DBF',
  'F:\ABCD\ORACLE\BBCABCD\ABCD_IDX01.DBF'
CHARACTER SET ZHT16BIG5
;

Modify the second line as follow (control.sql) :

From:

CREATE CONTROLFILE REUSE DATABASE "OLD_Instance" NORESETLOGS

To:

CREATE CONTROLFILE SET DATABASE "NEW_Instance" RESETLOGS

And change the path to fit your new environment. (control.sql)

F:\ABCD\ORACLE\BBCABCD\ - > K:\ABCD\ORACLE\BBCABCD\

Stop the Source Instance and copy all database file to new server but control file:

2014-05-20  17:21     1,000,000,000 SYSAUX01.DBF
2014-05-20  17:10       700,000,000 SYSTEM01.DBF
2014-05-20  17:01        29,000,000 TEMP01.DBF
2014-05-20  17:10       700,000,000 UNDOTBS01.DBF
2014-05-20  17:10         5,000,000 USERS01.DBF
    .          .              .         .
    .          .              .         .
    .          .              .         .
    .          .              .         .

Create a new Instance on new Database By Pfile ( > Sample of Pfile < ):

oradim -new -sid new_instance -startmode auto -pfile 'c:\ACBD.ora'

Set sid and oracle home in command prompt (windows environment sample) :

C:\Documents and Settings\administrator>set oracle_home=C:\oracle\product\11.1.0\db_1
C:\Documents and Settings\administrator>set oracle_sid=BBCABCD
C:\Documents and Settings\administrator>sqlplus /nolog

Create Spfile :

create spfile from pfile='c:\ACBD.ora'

Create Control file :

SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 21 11:15:20 2014

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

SQL> @control.sql

PS. Your database may show following error if upgrade is required:

ORA-00704: bootstrap process failure
ORA-1092 signalled during: alter database open...

Alert.log :
ORA-39700: database must be
opened with UPGRADE option error.

Update the Instance if come from lower version :

startup upgrade;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catalog.sql;
@C:\oracle\product\11.1.0\db_1\rdbms\admin\catproc.sql;

 

Restart the database and place the database in archivelog mode. 

Done 😀