Create a Duplicate (Auxiliary) Oracle database on same host with RMAN

Create a Duplicate (Auxiliary) Oracle database on same host with RMAN

orcl database up and running


main database name = orcl
Auxiliary database name= dupli


export ORACLE_SID=dupli
first create passpword file

go to following location
cd $ORACLE_HOME/dbs/

then create password file
orapwd file=orapwdupli password=manager entries=5 force=y




Location of the file would be

cd $ORACLE_HOME/network/admin/

then create listener.ora file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /opt/app/oracle/product/10.2/db)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = dupli)
(ORACLE_HOME = /opt/app/oracle/product/10.2/db)
(SID_NAME = dupli)
)
)




then create tnsnames.ora file

Location of the file would be

cd $ORACLE_HOME/network/admin/


orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

dupli =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dupli)
)
)




create init ora file for dupli instance

Create Directories for the duplicate Database as given in init ora file

====
[apt-amd-02:oracle:dupli]$ cat init_dupli.txt

dupli.__db_cache_size=297795584
dupli.__java_pool_size=4194304
dupli.__large_pool_size=4194304
dupli.__shared_pool_size=130023424
dupli.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/dupli/adump'
*.background_dump_dest='/opt/app/oracle/admin/dupli/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/app/oracle/oradata/dupli/control01.ctl','/opt/app/oracle/oradata/dupli/control02.ctl','/opt/app/oracle/oradata/dupli/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/dupli/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dupli'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=440401920
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/dupli/udump'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.remote_login_passwordfile=EXCLUSIVE
*.db_file_name_convert='/opt/app/oracle/oradata/orcl/orcl' '/opt/app/oracle/oradata/dupli'
*.log_file_name_convert='/opt/app/oracle/oradata/orcl/orcl' '/opt/app/oracle/oradata/dupli'
*.instance_name='dupli'
*.DB_UNIQUE_NAME='dupli'
*.remote_login_passwordfile=EXCLUSIVE

================================
init ora file for main database


[apt-amd-02:oracle:orcl]$ cat init_orcl.txt
orcl.__db_cache_size=297795584
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=130023424
orcl.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'
*.background_dump_dest='/opt/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/app/oracle/oradata/orcl/orcl/control01.ctl','/opt/app/oracle/oradata/orcl/orcl/control02.ctl','/opt/app/oracle/oradata/orcl/orcl/control03.ctl'
*.core_dump_dest='/opt/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=440401920
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/app/oracle/admin/orcl/udump'
*.log_archive_dest_1='LOCATION=/opt/app/oracle/archive/ MANDATORY'
*.log_archive_dest_2='service=orcl lgwr'
*.FAL_SERVER='standby'
*.FAL_CLIENT='orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.remote_login_passwordfile=EXCLUSIVE

======

take main database backup

through RMAN

script is given below

[apt-amd-02:oracle:orcl]$ cat rman.sh

rman nocatalog target / <<-EOF
configure retention policy to recovery window of 3 days;
configure backup optimization on;
configure controlfile autobackup on;
configure default device type to disk;
configure device type disk parallelism 1 backup type to compressed backupset;
configure datafile backup copies for device type disk to 1;
configure maxsetsize to unlimited;
configure snapshot controlfile name to '/opt/app/oracle/oradata/rmanback/snapshot_controlfile';
show all;

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format '/opt/app/oracle/oradata/rmanback/datafile_%s_%p.bak'
tag 'datafile_daily';
}

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup archivelog all
delete all input
format '/opt/app/oracle/oradata/rmanback/archivelog_%s_%p.bak'
tag 'archivelog_daily';
}

run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup format '/opt/app/oracle/oradata/rmanback/controlfile_%s.bak' current controlfile;
}
crosscheck backup;
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
delete noprompt expired backup of archivelog all;
delete noprompt obsolete recovery window of 3 days;
quit
EOF


run RMAN backup script . backup will be store in /opt/app/oracle/oradata/rmanback/

[apt-amd-02:oracle:orcl]$ sh rman.sh


========
now start duplicate database ...

[apt-amd-02:oracle:dupli]$

Duplicate the Database
Now you are ready to duplicate the database orcl to dpuli.
export ORACLE_SID=dupli
sqlplus sys/manager as sysdba
startup force nomount pfile='/home/oracle/init_dupli.txt';


=====
export ORACLE_SID=orcl

[apt-amd-02:oracle:ORCL]$

sqlplus / as sysdba

run this query to idetified the files

set feed off
set pagesize 10000
column name format a40 heading "Datafile"
column file# format 99 heading "File-ID"
column member format a40 heading "Logfile"
column group# format 99 heading "Group-Nr"


select name, file# from v$dbfile;

select member, group# from v$logfile;


/opt/app/oracle/oradata/orcl/orcl/redo01.log 1
/opt/app/oracle/oradata/orcl/orcl/redo02.log 2
/opt/app/oracle/oradata/orcl/orcl/redo03.log 3

/opt/app/oracle/oradata/orcl/orcl/system01.dbf 1
/opt/app/oracle/oradata/orcl/orcl/undotbs01.dbf 2
/opt/app/oracle/oradata/orcl/orcl/sysaux01.dbf 3
/opt/app/oracle/oradata/orcl/orcl/users01.dbf 4




then create following script


RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/opt/app/oracle/oradata/dupli/dupli_sys1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/opt/app/oracle/oradata/dupli/dupli_undo1.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/opt/app/oracle/oradata/dupli/dupli_users1.dbf';
DUPLICATE TARGET DATABASE TO dupli
PFILE = '/home/oracle/init_dupli.txt'
NOFILENAMECHECK
LOGFILE GROUP 1 ('/opt/app/oracle/oradata/dupli/dupli_log1A.rdo') SIZE 50M REUSE ,
GROUP 2 ('/opt/app/oracle/oradata/dupli/dupli_log2A.rdo') SIZE 50M REUSE,
GROUP 3 ('/opt/app/oracle/oradata/dupli/dupli_log3A.rdo') SIZE 50M REUSE ; }


===========
Now connect RMAN

[apt-amd-02:oracle:orcl]$ rman TARGET / AUXILIARY sys/manager@dupli

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Oct 15 14:13:24 2009

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

connected to target database: ORCL (DBID=1226728274)
connected to auxiliary database: DUPLI (not mounted)

run this script

RMAN> RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/opt/app/oracle/oradata/dupli/dupli_sys1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/opt/app/oracle/oradata/dupli/dupli_undo1.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/opt/app/oracle/oradata/dupli/dupli_users1.dbf';
DUPLICATE TARGET DATABASE TO dupli
PFILE = '/home/oracle/init_dupli.txt'
NOFILENAMECHECK
LOGFILE GROUP 1 ('/opt/app/oracle/oradata/dupli/dupli_log1A.rdo') SIZE 50M REUSE ,
GROUP 2 ('/opt/app/oracle/oradata/dupli/dupli_log2A.rdo') SIZE 50M REUSE,
GROUP 3 ('/opt/app/oracle/oradata/dupli/dupli_log3A.rdo') SIZE 50M REUSE ; }2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 15-OCT-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:
{
set until scn 871634;
set newname for datafile 1 to
"/opt/app/oracle/oradata/dupli/dupli_sys1.dbf";
set newname for datafile 2 to
"/opt/app/oracle/oradata/dupli/dupli_undo1.dbf";
set newname for datafile 3 to
"/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf";
set newname for datafile 4 to
"/opt/app/oracle/oradata/dupli/dupli_users1.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 15-OCT-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/app/oracle/oradata/dupli/dupli_sys1.dbf
restoring datafile 00002 to /opt/app/oracle/oradata/dupli/dupli_undo1.dbf
restoring datafile 00003 to /opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf
restoring datafile 00004 to /opt/app/oracle/oradata/dupli/dupli_users1.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/oradata/rmanback/datafile_1_1.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/opt/app/oracle/oradata/rmanback/datafile_1_1.bak tag=DATAFILE_DAILY
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 15-OCT-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPLI" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/opt/app/oracle/oradata/dupli/dupli_log1A.rdo' ) SIZE 50 M REUSE,
GROUP 2 ( '/opt/app/oracle/oradata/dupli/dupli_log2A.rdo' ) SIZE 50 M REUSE,
GROUP 3 ( '/opt/app/oracle/oradata/dupli/dupli_log3A.rdo' ) SIZE 50 M REUSE
DATAFILE
'/opt/app/oracle/oradata/dupli/dupli_sys1.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=700323282 filename=/opt/app/oracle/oradata/dupli/dupli_undo1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=700323282 filename=/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=700323282 filename=/opt/app/oracle/oradata/dupli/dupli_users1.dbf

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

executing command: SET until clause

Starting recover at 15-OCT-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

starting media recovery

archive log thread 1 sequence 43 is already on disk as file /opt/app/oracle/archive/1_43_698947538.dbf
archive log filename=/opt/app/oracle/archive/1_43_698947538.dbf thread=1 sequence=43
media recovery complete, elapsed time: 00:00:03
Finished recover at 15-OCT-09

contents of Memory Script:
{
shutdown clone;
startup clone nomount pfile= '/home/oracle/init_dupli.txt';
}
executing Memory Script


database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 440401920 bytes

Fixed Size 2021408 bytes
Variable Size 130025440 bytes
Database Buffers 306184192 bytes
Redo Buffers 2170880 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPLI" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/opt/app/oracle/oradata/dupli/dupli_log1A.rdo' ) SIZE 50 M REUSE,
GROUP 2 ( '/opt/app/oracle/oradata/dupli/dupli_log2A.rdo' ) SIZE 50 M REUSE,
GROUP 3 ( '/opt/app/oracle/oradata/dupli/dupli_log3A.rdo' ) SIZE 50 M REUSE
DATAFILE
'/opt/app/oracle/oradata/dupli/dupli_sys1.dbf'
CHARACTER SET WE8ISO8859P1


contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/app/oracle/oradata/dupli/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/opt/app/oracle/oradata/dupli/dupli_undo1.dbf";
catalog clone datafilecopy "/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf";
catalog clone datafilecopy "/opt/app/oracle/oradata/dupli/dupli_users1.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /opt/app/oracle/oradata/dupli/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/opt/app/oracle/oradata/dupli/dupli_undo1.dbf recid=1 stamp=700325657

cataloged datafile copy
datafile copy filename=/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf recid=2 stamp=700325658

cataloged datafile copy
datafile copy filename=/opt/app/oracle/oradata/dupli/dupli_users1.dbf recid=3 stamp=700325658

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=700325657 filename=/opt/app/oracle/oradata/dupli/dupli_undo1.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=700325658 filename=/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=700325658 filename=/opt/app/oracle/oradata/dupli/dupli_users1.dbf

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

database opened
Finished Duplicate Db at 15-OCT-09


===============
Now Duplicate database ready with the RESETLOGS option.





SQL> !echo $ORACLE_HOME
/opt/app/oracle/product/10.2/db

SQL> !echo $ORACLE_SID
dupli

SQL> select NAME from v$database;

NAME
---------
DUPLI


SQL> select FILE_NAME from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/dupli/dupli_sys1.dbf
/opt/app/oracle/oradata/dupli/dupli_undo1.dbf
/opt/app/oracle/oradata/dupli/dupli_sysaux1.dbf
/opt/app/oracle/oradata/dupli/dupli_users1.dbf





you can comment the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameter in the init_dupli.txt file and restart the database.