11gr2 cold cloning

Clone an Oracle Database using Cold Physical Backup

Database Name: orcl
Source Database side: 

1. Get the file path information using below query

SQL> Select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;

2. Parameter file backup

If orcl database running on spfile create pfile.

SQL> Create pfile=’/u01/backup/initorcl.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Taken the control file backup

SQL> alter database backup controlfile to trace as /u01/backup/control01.ora’;

4.Shutdown database

SQL> shut immediate

5.Copy all the data files/log files using os command & placed in /u01/backuppath.

$scp /u01/backup/*  oracle@192.168.187.132:/u01/backup/

6.Startup the database.

SQL> startup


Clone Database side: (Clone database)

Database Name: CloneNote: Before these steps to follow we need to install only oracle software in this server
1.Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.

$mkdir –p /u01/app/oracle/oradata/clone/

$mkdir –p /u01/app/oracle/flash_recovery_area/clone

$mkdir –p /u01/app/oracle/admin/clone/adump

2.Change the init.ora parameter like control file path, dbname, instance name etc...

Ex:

Create pfile for clone database :

$vi /u01/backup/initorcl.ora

orcl.__db_cache_size=109051904

orcl.__java_pool_size=12582912

orcl.__large_pool_size=4194304

orcl.__oracle_base='D:\oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=104857600

orcl.__sga_target=322961408

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=188743680

orcl.__streams_pool_size=0

*.audit_file_dest='D:\oracle\admin\orcl\adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='D:\ORACLE\ORADATA\ORCL\CONTROL01.CTL','D:\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='D:\oracle\flash_recovery_area'

*.diagnostic_dest='D:\oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.log_archive_dest=''

*.log_archive_dest_1='location=D:\archive\'

*.log_archive_format='orcl_%s_%t_%r.arc'

*.memory_target=425721856

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS2'

In the above pfile replace  the text  “orcl”  with  “clone “  and save it as bellow. Hence we will have the pfile  for clone database.

clone.__db_cache_size=109051904

clone.__java_pool_size=12582912

clone.__large_pool_size=4194304

clone.__oracle_base='D:\oracle'#ORACLE_BASE set from environment

clone.__pga_aggregate_target=104857600

clone.__sga_target=322961408

clone.__shared_io_pool_size=0

clone.__shared_pool_size=188743680

clone.__streams_pool_size=0

*.audit_file_dest='D:\oracle\admin\clone\adump'

*.audit_trail='db'

*.compatible='11.1.0.0.0'

*.control_files='D:\ORACLE\ORADATA\clone\CONTROL01.CTL','D:\ORACLE\FLASH_RECOVERY_AREA\CLONE\CONTROL02.CTL'

*.db_block_size=8192

*.db_domain=''

*.db_name='clone'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='D:\oracle\flash_recovery_area'

*.diagnostic_dest='D:\oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'

*.log_archive_dest=''

*.log_archive_dest_1='location=D:\archive\'

*.log_archive_format='clone_%s_%t_%r.arc'

*.memory_target=425721856

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS2'

$cp initorcl.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initclone.ora

3.Create the password file using orapwd utility.

$ orapwd file='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwclone.ora' password=oracle;

4.Startup the Database in NOMOUNT stage.

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/

$ export PATH=$PATH:$ORACLE_HOME/bin

$ export ORACLE_SID=clone

$sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 2 00:59:57 2013

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

Connected to an idle instance.

SQL> startup nomount

5.Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.

Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET' and change NORESETLOGS to RESETLOGS and ensure there is no blank lines in between these lines.

EX:

CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

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

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

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

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

  '/u01/app/oracle/oradata/orcl/users01.dbf'

CHARACTER SET WE8MSWIN1252;

placed the script in sql prompt. Now controlfile created.
 

SQL> @/u01/backup/control.ctl;

Control file created.

6.Now open the database.

SQL> alter database open resetlogs;

Note: Check the instance status

SQL>select open_mode from v$database;

**********************************************************************************