Khurram Siddiqui

I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Thursday, January 31, 2008

How to clone database within same server 

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl

SQL> select * from tab
  2  /

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TEST1                          TABLE
TEST                           TABLE
TEST2                          TABLE

7 rows selected.

SQL> create table primary_table as select * from all_objects
  2  .
SQL> /

Table created.

SQL> 

==================================================================
Backup production database with archivelog 
==================================================================

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@orcl

connected to target database: ORCL (DBID=1171886541)


RMAN> backup database plus archivelog delete input
2> ;


Starting backup at 29-JAN-08
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=25 recid=82 stamp=645234594
input archive log thread=1 sequence=26 recid=83 stamp=645234661
input archive log thread=1 sequence=27 recid=84 stamp=645234727
input archive log thread=1 sequence=28 recid=85 stamp=645274841
input archive log thread=1 sequence=29 recid=86 stamp=645274906
input archive log thread=1 sequence=30 recid=87 stamp=645284174
input archive log thread=1 sequence=31 recid=88 stamp=645284257
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133739_3SXSGO8V_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_25_3SW8YJTC_.ARC recid=82 stamp=645234594
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_26_3SW90NPY_.ARC recid=83 stamp=645234661
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_27_3SW92Q8S_.ARC recid=84 stamp=645234727
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_28_3SXJ857Y_.ARC recid=85 stamp=645274841
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_29_3SXJB85Y_.ARC recid=86 stamp=645274906
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_30_3SXSCX8F_.ARC recid=87 stamp=645284174
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_31_3SXSGKNL_.ARC recid=88 stamp=645284257
Finished backup at 29-JAN-08

Starting backup at 29-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NCSNF_TAG20080129T133749_3SXSJPO0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 29-JAN-08

Starting backup at 29-JAN-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=89 stamp=645284335
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXSJZ1Y_.ARC recid=89 stamp=645284335
Finished backup at 29-JAN-08

Create duplicate database hirarchy with DBCA as same file structure with production database hirarchy


===================================
Add Listener in listener.ora file
===================================

# listener.ora Network Configuration File: C:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = %ORACLE_HOME%)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1)
      (ORACLE_HOME = %oracle_home%)
      (SID_NAME = ORCL1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
  )

=========================================================
Add tnames string in tnsnames.ora for duplicate database
=========================================================

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

=============================================================================================================
Edit the pfile of duplicate database ORCL1 and make some changes and after all changes just save not save as
=============================================================================================================

DB_FILE_NAME_CONVERT=("C:\oracle\product\10.1.0\oradata\orcl\","C:\oracle\product\10.1.0\oradata\orcl1\orcl1")
LOG_FILE_NAME_CONVERT=("c:\oracle\product\10.1.0\oradata\orcl","C:\oracle\product\10.1.0\oradata\orcl1\orcl1")

=============================================================================================================
Connect to duplicate database ORCL1 and create spfile from edited pfile
=============================================================================================================

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile='C:\oracle\product\10.1.0\admin\orcl1\pfile\init.ora.0292008145243'
  2  /

File created.

SQL> startup nomount

ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl1


=============================================================================================================
Connect to target production database ORCL and mount the database
=============================================================================================================

SQL> conn sys/sys@orcl as sysdba
Connected.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl


=============================================================================================================
Go to dos prompt 
=============================================================================================================


C:\>set oracle_sid=orcl

C:\>rman target / auxiliary sys/sys@orcl1

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1171886541)
connected to auxiliary database: orcl1 (not mounted)

RMAN> run
2> {
3> allocate auxiliary channel aux1 device type disk;
4> duplicate target database to 'orcl1';
5> }

using target database controlfile instead of recovery catalog
allocated channel: aux1
channel aux1: sid=160 devtype=DISK

Starting Duplicate Db at 29-JAN-08

contents of Memory Script:
{
   set until scn  1104818;
   set newname for datafile  1 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF";
   set newname for datafile  2 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF";
   set newname for datafile  3 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF";
   set newname for datafile  4 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF";
   set newname for datafile  5 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 29-JAN-08

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF
channel aux1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP tag=TAG20080129T133749
channel aux1: restore complete
Finished restore at 29-JAN-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M  REUSE,
  GROUP  2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M  REUSE,
  GROUP  3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M  REUSE
 DATAFILE
  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF

contents of Memory Script:
{
   set until scn  1104818;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 29-JAN-08

starting media recovery

channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=32
channel aux1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP tag=TAG20080129T133855
channel aux1: restore complete
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC thread=1 sequence=32
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC recid=1 stamp=645290824
media recovery complete
Finished recover at 29-JAN-08

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M  REUSE,
  GROUP  2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M  REUSE,
  GROUP  3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M  REUSE
 DATAFILE
  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF";
   switch clone datafile all;
}
executing Memory Script

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF recid=1 stamp=645290915

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF recid=2 stamp=645290916

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF recid=3 stamp=645290916

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF recid=4 stamp=645290916

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=645290915 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 29-JAN-08

RMAN>

SQL> conn scott/tiger@orcl1
Connected.
SQL> select * from tab
  2  /

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TEST1                          TABLE
TEST                           TABLE
TEST2                          TABLE
PRIMARY_TABLE                  TABLE

8 rows selected.

SQL>
Posted by at
Subscribe to: Post Comments (Atom)

Followers

About Me

My photo
Melbourne, Victoria, Australia