Thursday, April 14, 2011

Cloning through RMAN on same server...have a look!!




Cloning or duplicating a database means creating an exact copy of it. RMAN has made this activity a bit easier. Let's see how it is going to be done.

First put your target database in archive logmode…

C:\>rman target /
SQL> startup mount;
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             260048468 bytes
Database Buffers          163577856 bytes
Redo Buffers                6078464 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter system set log_archive_dest_1='location=c:\archive\';

System altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Now, take the full backup of it…

C:\>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 14 11:00:25 2011

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

connected to target database: TEST (DBID=2046510594)

RMAN> backup database plus archivelog;


Starting backup at 14-APR-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=748436386
input archived log thread=1 sequence=7 RECID=2 STAMP=748436405
input archived log thread=1 sequence=8 RECID=3 STAMP=748436411
input archived log thread=1 sequence=9 RECID=4 STAMP=748436436
channel ORA_DISK_1: starting piece 1 at 14-APR-11
channel ORA_DISK_1: finished piece 1 at 14-APR-11
piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2011_04_14\O1_MF_ANNNN
_TAG20110414T110040_6TF1N2DP_.BKP tag=TAG20110414T110040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 14-APR-11

Starting backup at 14-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
input datafile file number=00002 name=D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF
input datafile file number=00003 name=D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
input datafile file number=00004 name=D:\ORACLE\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 14-APR-11
channel ORA_DISK_1: finished piece 1 at 14-APR-11
piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2011_04_14\O1_MF_NNNDF
_TAG20110414T110048_6TF1NH1G_.BKP tag=TAG20110414T110048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:36
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-APR-11
channel ORA_DISK_1: finished piece 1 at 14-APR-11
piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2011_04_14\O1_MF_NCSNF
_TAG20110414T110048_6TF1XNVC_.BKP tag=TAG20110414T110048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
Finished backup at 14-APR-11

Starting backup at 14-APR-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=5 STAMP=748436777
channel ORA_DISK_1: starting piece 1 at 14-APR-11
channel ORA_DISK_1: finished piece 1 at 14-APR-11
piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2011_04_14\O1_MF_ANNNN
_TAG20110414T110621_6TF1YVF5_.BKP tag=TAG20110414T110621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 14-APR-11

RMAN> exit


Recovery Manager complete.

Create password file for duplicate instance…


C:\>orapwd file=D:\oracle\product\11.1.0\db_1\dbs\orarefer password=password

C:\>sqlplus sys/sys@test as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 14 11:14:25 2011

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


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

SQL> create pfile='c:\archive\initrefer.ora' from spfile;

File created.

Now, edit it...grey marked are the changes done into it.

refer.__db_cache_size=79691776
refer.__java_pool_size=12582912
refer.__large_pool_size=4194304
refer.__oracle_base='D:\oracle'#ORACLE_BASE set from environment
refer.__pga_aggregate_target=188743680
refer.__sga_target=243269632
refer.__shared_io_pool_size=0
refer.__shared_pool_size=142606336
refer.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\refer\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\oracle\oradata\refer\control01.ctl','D:\oracle\oradata\refer\control02.ctl','D:\oracle\oradata\refer\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='refer'
*.db_recovery_file_dest='D:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='D:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=referXDB)'
*.log_archive_dest_1='location=c:\archive\'
*.memory_target=428867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_FILE_NAME_CONVERT=('D:\oracle\oradata\test\','D:\oracle\oradata\refer\')
LOG_FILE_NAME_CONVERT=('D:\oracle\oradata\test\','D:\oracle\oradata\refer\')



SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Create the duplicate instance…

C:\>oradim -new -sid refer -syspwd refer -startmode m
Instance created.

Configure listener for duplicate database.

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 14-APR-2011 11:23
:35

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abc-117)(PORT=1521))
)
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abc-117)(PORT=1521))
)
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date                14-APR-2011 10:46:08
Uptime                    0 days 0 hr. 37 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\11.1.0\db_1\network\admin\listener.o
ra
Listener Log File         d:\oracle\diag\tnslsnr\abc-117\listener\alert\log.
xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=abc-117)(PORT=1521)))
Services Summary...
Service "refer" has 1 instance(s).
  Instance "refer", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
  Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit


Configure tnsnames entry for duplicate instance..

C:\>netca

Oracle Net Services Configuration:
Default local naming configuration complete.
    Created net service name: refer
Oracle Net Services configuration successful. The exit code is 0



C:\>set ORACLE_SID=refer

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 14 11:24:52 2011

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

Connected to an idle instance.

SQL> create spfile from pfile='C:\archive\initrefer.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             343934548 bytes
Database Buffers           79691776 bytes
Redo Buffers                6078464 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


C:\>rman target=sys/sys@test auxiliary=sys/refer@refer

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 14 11:26:28 2011

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

connected to target database: TEST (DBID=2046510594)
connected to auxiliary database: REFER (not mounted)

RMAN> duplicate target database to refer nofilenamecheck
2> ;

Starting Duplicate Db at 14-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

contents of Memory Script:
{
   set until scn  974360;
   set newname for datafile  1 to
 "D:\ORACLE\ORADATA\REFER\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORACLE\ORADATA\REFER\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\ORACLE\ORADATA\REFER\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\ORACLE\ORADATA\REFER\USERS01.DBF";
   restore
   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

Starting restore at 14-APR-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\REFER\SYST
EM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\REFER\SYSA
UX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\REFER\UNDO
TBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\REFER\USER
S01.DBF
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\FLASH_RECOVERY_AREA\
TEST\BACKUPSET\2011_04_14\O1_MF_NNNDF_TAG20110414T110048_6TF1NH1G_.BKP
channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\TEST\BACKUPSE
T\2011_04_14\O1_MF_NNNDF_TAG20110414T110048_6TF1NH1G_.BKP tag=TAG20110414T110048

channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:22:00
Finished restore at 14-APR-11
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REFER" RESETLOGS ARCHIVELO
G
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( 'D:\ORACLE\ORADATA\REFER\REDO01.LOG' ) SIZE 50 M  REUSE,
  GROUP  2 ( 'D:\ORACLE\ORADATA\REFER\REDO02.LOG' ) SIZE 50 M  REUSE,
  GROUP  3 ( 'D:\ORACLE\ORADATA\REFER\REDO03.LOG' ) SIZE 50 M  REUSE
 DATAFILE
  'D:\ORACLE\ORADATA\REFER\SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


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

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=748439405 file name=D:\ORACLE\ORADATA\REFER\SY
SAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=748439409 file name=D:\ORACLE\ORADATA\REFER\UN
DOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=748439413 file name=D:\ORACLE\ORADATA\REFER\US
ERS01.DBF

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

executing command: SET until clause

Starting recover at 14-APR-11
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file C:\ARCHIVE
\ARC00010_0748364180.001
archived log file name=C:\ARCHIVE\ARC00010_0748364180.001 thread=1 sequence=10
media recovery complete, elapsed time: 00:00:08
Finished recover at 14-APR-11

contents of Memory Script:
{
   shutdown clone immediate;
   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     431038464 bytes

Fixed Size                     1333676 bytes
Variable Size                343934548 bytes
Database Buffers              79691776 bytes
Redo Buffers                   6078464 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "REFER" RESETLOGS ARCHIVELO
G
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( 'D:\ORACLE\ORADATA\REFER\REDO01.LOG' ) SIZE 50 M  REUSE,
  GROUP  2 ( 'D:\ORACLE\ORADATA\REFER\REDO02.LOG' ) SIZE 50 M  REUSE,
  GROUP  3 ( 'D:\ORACLE\ORADATA\REFER\REDO03.LOG' ) SIZE 50 M  REUSE
 DATAFILE
  'D:\ORACLE\ORADATA\REFER\SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\ORACLE\ORADATA\REFER\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "D:\ORACLE\ORADATA\REFER\SYSAUX01.DBF";
   catalog clone datafilecopy  "D:\ORACLE\ORADATA\REFER\UNDOTBS01.DBF";
   catalog clone datafilecopy  "D:\ORACLE\ORADATA\REFER\USERS01.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\ORACLE\ORADATA\REFER\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\REFER\SYSAUX01.DBF RECID=1 STAMP=74843
9581

cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\REFER\UNDOTBS01.DBF RECID=2 STAMP=7484
39587

cataloged datafile copy
datafile copy file name=D:\ORACLE\ORADATA\REFER\USERS01.DBF RECID=3 STAMP=748439
596

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=748439581 file name=D:\ORACLE\ORADATA\REFER\SY
SAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=748439587 file name=D:\ORACLE\ORADATA\REFER\UN
DOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=748439596 file name=D:\ORACLE\ORADATA\REFER\US
ERS01.DBF

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

database opened
Finished Duplicate Db at 14-APR-11

RMAN> exit


Recovery Manager complete.

C:\>set ORACLE_SID=refer

C:\>sqlplus sys/refer@refer as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 14 11:57:02 2011

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


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

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
REFER     READ WRITE

You have successfully created clone database…

:-) Enjoy