14 September 2010

Step by Step Oracle Database Cloning

Oracle Database Clone
alter database backup controlfile to trace as '/oracle/back.trc';
create pfile='init<new database sid>.ora' from spfile;
alter database backup controlfile to trace as '/home/oracle/cr_<new sid>.sql'
/
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<your password>

STARTUP NOMOUNT
SET CONTROLFILE REUSE DATABASE "clone" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/product/10.2.0/oradata/clone/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/product/10.2.0/oradata/clone/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/product/10.2.0/oradata/clone/redo03.log'  SIZE 50M
DATAFILE
  '/clone/dbf/system01.dbf',
  '/clone/dbf/undotbs01.dbf',
  '/clone/dbf/sysaux01.dbf',
  '/clone/dbf/users01.dbf',
  '/clone/dbf/muthu1.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/product/10.2.0/oradata/or10/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Error  :
SQL> @/oracle/back.sql
CREATE CONTROLFILE REUSE SET DATABASE "clone" NORESETLOGS
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01223: RESETLOGS must be specified to set a new database name


ORA-01507: database not mounted


ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01507: database not mounted


ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/product/10.2.0/oradata/clone/temp01.dbf'
*
ERROR at line 1:
ORA-01109: database not open

SQL> select OPEN_RESETLOGS from v$database;

OPEN_RESETL
-----------
REQUIRED

SQL> alter database open resetlogs;

Database altered.

SQL> shut
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  738197504 bytes
Fixed Size                  1221612 bytes
Variable Size             205523988 bytes
Database Buffers          524288000 bytes
Redo Buffers                7163904 bytes
Database mounted.
Database opened.
SQL>
LISTNER.ORA Entry

CLONE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = training.muthu.com)(PORT = 1523))
  )

SID_LIST_CLONE =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = clone)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = clone)
    )
  )

TNSNAME.ora  Entry

CLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.235)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = clone)
    )
  )

No comments: