Database cloning manual steps

Old database : soauat

New database as cloned: SOACLONE (clone of “soauat” database)

Step-1: Backup control file of existing database to which we are going take clone.

SQL>alter database backup controlfile to trace;

Step-2: Check alert logfile to check which trace file has been created after control file backup.

Alert Log file : /u01/app/diag/rdbms/soauat/soauat/trace/alert_soauat.log

We can see output of alert log file  as control file has been traced to below file,

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/app/diag/rdbms/soauat/soauat/trace/soauat_ora_10668.trc

Step-3: Change contents of that trace file as below contents and save it as sql file ,

CREATE CONTROLFILE REUSE SET DATABASE "SOACLONE" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 584

LOGFILE

  GROUP 1 '/u01/app/oradata/soaclone/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oradata/soaclone/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oradata/soaclone/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oradata/soaclone/system01.dbf',

  '/u01/app/oradata/soaclone/sysaux01.dbf',

  '/u01/app/oradata/soaclone/undotbs01.dbf',

  '/u01/app/oradata/soaclone/users01.dbf',

  '/u01/app/oradata/soaclone/example01.dbf',

  '/u01/app/oradata/soaclone/DEV_iassdpm.dbf',

  '/u01/app/oradata/soaclone/DEV_mds.dbf',

  '/u01/app/oradata/soaclone/DEV_soainfra.dbf',

  '/u01/app/oradata/soaclone/DEV_orabam.dbf',

  '/u01/app/oradata/soaclone/DEV_soainfra01.dbf'

CHARACTER SET AL32UTF8

And saved the file as “db-ctrl-file-create.sql”

Step-4

Shutdown soauat database and take a cold backup of all files  to appropriate location as mentioned in newly created control file creation sql script(db-ctrl-file-create.sql).

Step-5

Take a copy of initialization parameter file and named it as init<SID>.ora like we have here initSOACLONE.ora

And change the contents as below with new database name,

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=estuate

db_name=soaclone

###########################################

# File Configuration

###########################################

control_files=("/u01/app/oradata/soaclone/control01.ctl", "/u01/app/oradata/soaclone/control02.ctl")

###########################################

# Miscellaneous

###########################################

compatible=11.2.0.0.0

diagnostic_dest=/u01/app

memory_target=1107296256

###########################################

# Processes and Sessions

###########################################

processes=150

###########################################

# Security and Auditing

###########################################

audit_file_dest=/u01/app/admin/soaclone/adump

audit_trail=db

remote_login_passwordfile=EXCLUSIVE

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=soacloneXDB)"

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_tablespace=UNDOTBS1

new created  pfilename : initSOACLONE.ora

Step -6

[oracle@soauat132 bin]$ export ORACLE_SID=SOACLONE

[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 9 08:22:26 2014

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1104814080 bytes

Fixed Size                  1335980 bytes

Variable Size             671092052 bytes

Database Buffers          419430400 bytes

Redo Buffers               12955648 bytes

SQL>

SQL>  @/u01/app/db-ctrl-file-create.sql

Control file created.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     0

Next log sequence to archive   0

Current log sequence           0

SQL> alter database archivelog;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select name from v$database;

NAME

---------

SOACLONE

Step-7
Give entry in oratab file for new SID  because when we source . oraenv it reads SID info from oratab file,
oratab file location : /etc/oratab

SOACLONE:/u01/app/oracle/product/11.2.0/dbhome_1:N

Now if any other terminal we are trying to connect we can source environment as shown below,

[oracle@soauat132 bin]$ . oraenv
ORACLE_SID = [oracle] ? SOACLONE
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app
[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 10 04:32:40 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status,name from v$instance,v$database;

STATUS       NAME
------------ ---------
OPEN         SOACLONE

SQL>

Step-8
For netservice give entry in tnsnames.ora file,

file location:$TNS_ADMIN/tnsnames.ora

give below entry:
==============
SOACLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = soauat132.estuate)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SOACLONE)
    )
  )
===============
And check with listener status whether it is handling this new service name.

SQL> !lsnrctl status soauat

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-SEP-2014 06:09:39

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=soauat132.estuate)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     soauat
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                10-SEP-2014 06:07:18
Uptime                    0 days 0 hr. 2 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/diag/tnslsnr/soauat132/soauat/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=soauat132.estuate)(PORT=1521)))
Services Summary...
Service "SOAUAT.estuate" has 1 instance(s).
  Instance "soauat", status READY, has 1 handler(s) for this service...
Service "soaclone.estuate" has 1 instance(s).
  Instance "SOACLONE", status READY, has 1 handler(s) for this service...
Service "soacloneXDB.estuate" has 1 instance(s).
  Instance "SOACLONE", status READY, has 1 handler(s) for this service...
Service "soauatXDB.estuate" has 1 instance(s).
  Instance "soauat", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>

===========================Hope it will help you=====================