Skip to content
Advertisements

create duplicate database using rman

You want to use RMAN to create a duplicate database on the same server by using RMAN backups.

My Source Database Name is MYTEST

Target (duplicate) Database name is MYDB 

Step 1: Configure Listener.ora and tnsnames.ora files

tnsnames.ora file

MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)

listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)

Step 2: create pfile for target database from source database

SQL> create pfile from spfile;

File created.

Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.

*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,’/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,’/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,’/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,’/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step 4: Create password file

[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5

Step 5: start your target database nomount stage

[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010

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

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
SQL> exit

Step 6: From source database

SQL> alter system switch logfile;

System altered.

SQL>

Step 7: Backup database and archivelog Source database

[oracle@localhost root]$ rlwrap rman target sys/azar@mytest

Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010

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

connected to target database: MYTEST (DBID=2419076664)

RMAN> backup database plus archivelog;

Starting backup at 25-MAY-10
current log archived

input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10

(skipped)

Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10

Step 8: Connect auxiliary database

RMAN> connect auxiliary sys/oracle@mydb;

connected to auxiliary database: MYDB (not mounted)

Step 9: Create duplicate database.

RMAN> duplicate target database to mydb;

Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn  470310;
set newname for datafile  1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile  2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile  3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile  4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.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

Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M  REUSE,
GROUP  2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M  REUSE,
GROUP  3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

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

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

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

executing command: SET until clause

Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10

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     285212672 bytes

Fixed Size                     1218992 bytes
Variable Size                 92276304 bytes
Database Buffers             188743680 bytes
Redo Buffers                   2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP  1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M  REUSE,
GROUP  2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M  REUSE,
GROUP  3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1

contents of Memory Script:
{
set newname for tempfile  1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy  “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593

cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf

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

database opened
Finished Duplicate Db at 25-MAY-10

RMAN>

Step 10: My duplicate database succesfully created.

Enter user-name: sys/oracle@mydb as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS
————
OPEN

SQL> select name from V$database;

NAME
———
MYDB

SQL>

Advertisements
23 Comments Post a comment
  1. Nice post.

    Good work

    bob

    May 25, 2010
    • Thanks Sir, You’re welcome 🙂

      Mohamed Azar

      May 25, 2010
  2. Congratulation! Well done.

    May 26, 2010
    • Thank you, You’re welcome 🙂

      Mohamed Azar

      May 26, 2010
  3. Abdullah Saleh #

    Good Job,

    Simple & Clear.

    August 4, 2010
    • Thank you, I had seen this video already. 🙂

      August 18, 2010
  4. rustam #

    Hi azardba,
    i tried to create clone database on the same host. I instaleld Oracle XE. Then followed ur article:
    1. configured tnsnames and listener.ora
    2. created new pfile from spfileXE.ora and renamed to initclone.ora and modified all values to clone DB.
    3. i created service in windows 7 but i cant start this services.
    i run cd oracle_home_path\bin\oradim -new -sid clone intpwd pass -startmode M.
    and during starting services Oracleserviceclone i got error:

    do you have any idea? what could i do?

    thanks and regards,
    rustam

    December 8, 2010
    • Can you run from command promt as a Administrator

      December 8, 2010
    • Right click cmd prompt, Run as Administator
      set ORACLE_SID=CLONE
      net start oracleserviceclone

      If any errors will show, paste it here

      December 8, 2010
  5. rustam #

    Yeah, i run as a Administrator

    >set ORACLE_SID=clone
    >net start oracleserviceclone
    The OracleServiceclone is starting.
    The OracleServiceclone could not be started.

    A service specific error occured: 183
    More help is available by typing NET HELPMSG 3547

    thanks and regards,
    Rustam

    December 8, 2010
    • NOt sure , I think It may cause Password file also, Can you create new service like this
      first delete Old service,

      Open cmd as a Administrator

      C:\Windows\system32>oradim -delete -sid clone
      Instance deleted.
      Create a New Service,

      C:\Windows\system32>oradim -new -sid clone -startmode auto -pfile C:\oracle\db_1\initclone.ora

      Note : Location – your duplicate DB pfile location or you can create without pfile But you can startup db using pfile.

      After you done it, let me know Whether its working or not.

      December 8, 2010
  6. Red #

    Mr. Azar when ever i tried to create duplicate database on remote host. i always got the same error No backup files to restore. Even i did i prerequisites and have all necessary backups.

    YOu plz help me in this regard.
    I will be really thankful to you.

    Regards.
    Red

    March 7, 2011
    • May you post Error result here?

      March 7, 2011
      • Red #

        I do this activity in office and I am posting you from my home. Errors occur in this sequence.
        ORA-19870
        ORA-19505
        ORA-27037
        RMAN-03002
        RMAN-03015
        RMAN-06026
        RMAN-06023
        I DO work on linux AS 4.0 and have oracle 10g DB.
        Is it OS level issue or RMAN uses some Protocol to access backup files from remote host.
        Plz help me in this regard.
        Thanks

        March 8, 2011
  7. Red #

    allocate channel C1a type disk connect ‘sys/oracle@etf-1’;
    configure controlfile autobackup on;
    backup as copy database tag ‘clonecopy’ format ‘+ETF_DG/%d/datafile_%f_clone.dbf’;
    }

    RUN {
    ALLOCATE AUXILIARY CHANNEL C4d DEVICE TYPE DISK;
    duplicate target database to ‘clone’
    pfile=’/oracle/mgmt/bin/initclone-1.ora’;
    }

    RMAN-03002: failure of Duplicate Db command at 06/17/2010 23:27:37
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-06026: some targets not found – aborting restore
    RMAN-06023: no backup or copy of datafile 5 found to restore
    RMAN-06023: no backup or copy of datafile 4 found to restore
    RMAN-06023: no backup or copy of datafile 3 found to restore
    RMAN-06023: no backup or copy of datafile 2 found to restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    March 8, 2011
  8. Ashwina #

    I had problem in step 3 to modify parameter file… and I created listener and tns entry but NO Listener error. help me in step 3…

    Thank you

    May 22, 2011
  9. Thank you, Welcome 🙂

    Mohamed Azar.

    October 18, 2010

Trackbacks & Pingbacks

  1. Other DBA Related « Center Point for Oracle DBA & Kuwait Info
  2. 2010 in review « DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: