Skip to main content

DUPLICATE DATABASE USING RMAN





Duplicating a database using Recovery Manager  (RMAN)

NOTE: Here host name is same for both source & Target db's

 Following are the steps that we need to perform.

 Source Database :PRMY001

 Duplicate Database Name: DPRMY001

 --------------------------------------------------------------------------------------------------------------------------

 STEP 1: Create password for destination database.  In $ORACLE_HOME/dbs location like below.

 orapwd file=$ORACLE_HOME/dbs/orapwdprmy001 password=dprmy001 entries=3 force=y

 --------------------------------------------------------------------------------------------------------------------------

 STEP 2: Create necessary folders for destination duplicate database.

 [oracle@oradba dprmy001]$ ls    > List all files/Directories in  present DIR

 adump bdump cdump dpdump pfile scripts udump

 [oracle@oradba dprmy001]$ pwd  > Shows present working DIR


 /u01/app/oracle/admin/dprmy001

 --------------------------------------------------------------------------------------------------------------------------

 STEP 3: Create pfile for the destination database dprmy001. The below is an example for it. You can create it as per your location & requirements.

 *db_name=dprmy001
# note that the following two initialization parameters have equivalents
# on the DUPLICATE command itself
*DB_FILE_NAME_CONVERT=("/oradata/prmy001/","/oradata/dprmy001/")
*LOG_FILE_NAME_CONVERT=("/oradata/prmy001/","/oradata/dprmy001/")
*compatible='10.2.0.1.0'
*db_block_size=8192
*db_file_multiblock_read_count=16
*db_recovery_file_dest_size=2147483648
*job_queue_processes=10
*open_cursors=300
*pga_aggregate_target=94371840
*processes=150
*remote_login_passwordfile='EXCLUSIVE'

*sga_target=285212672

 --------------------------------------------------------------------------------------------------------------------------

 STEP 4: Create spfile from the pfile and open the destination database with nomount mode. Like below

 [oracle@oradba dbs]$ export ORACLE_SID=dprmy001

 [oracle@oradba dbs]$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 19:05:43 2011


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

 Connected to an idle instance.

 SQL> create spfile from pfile='/u01/app/oracle/product/10201/dbs/initdprmy001.ora';
File created.
SQL> exit
Disconnected

 NOTE: First Oracle search for the spfile in the default location, if the spfile is not available in that location, then oracle looks for the pfile.

[oracle@oradba dbs]$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 19:07:02 2011

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

 Connected to an idle instance.

 SQL> startup nomount
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 1218004 bytes
Variable Size 58722860 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes

SQL>

 --------------------------------------------------------------------------------------------------------------------------

 STEP 5: Check the source database – source database should be in open state if database is in archive

log mode.

 NOTE: If database is in noarchivelog mode it should be in MOUNT state.

 [oracle@oradba dprmy001]$ . oraenv

 ORACLE_SID = [dprmy001] ? prmy001

 [oracle@oradba dprmy001]$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 7 19:12:38 2011

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

 Connected to an idle instance.

 SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

 SQL> select name from v$database;

 NAME
---------
PRMY001

 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/prmy001/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

 SQL> show parameter spfile

 NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10201/dbs/spfileprmy001.ora

 NOTE: Here database is in archivelog mode.

 -------------------------------------------------------------------------------------------------------------------

 STEP 6: Add necessary TNS Entries for the Destination database dprmy001 and check the status of
Listener,if the listener not started start the listener.

# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/10201/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

 PRMY001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bhuvan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmy001)
)
)

 DPRMY001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bhuvan)(PORT = 1521))  > This one we have added
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dprmy001)
)
)

 ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bhuvan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)

)

 ------------------------------------------------------------------------------------------------------------

 LSNRCTL> start
Starting /u01/app/oracle/product/10201/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10201/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10201/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuvan)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 07-JUL-2011 19:18:21
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF

Listener Parameter File /u01/app/oracle/product/10201/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10201/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuvan)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dprmy001" has 1 instance(s).
Instance "dprmy001", status UNKNOWN, has 1 handler(s) for this service...
Service "prmy001" has 1 instance(s).
Instance "prmy001", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL>

 -----------------------------------------------------------------------------------------------------------

 STEP 7: Take the backup using RMAN for source database.

[oracle@oradba ~]$ rman target /

 Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 12 19:51:33 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

 connected to target database: PRMY001 (DBID=1750001398)

 RMAN> backup database plus archivelog;

 Starting backup at 12-JUL-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=755896381
input archive log thread=1 sequence=2 recid=2 stamp=756322842
input archive log thread=1 sequence=3 recid=3 stamp=756330704
channel ORA_DISK_1: starting piece 1 at 12-JUL-11
channel ORA_DISK_1: finished piece 1 at 12-JUL-11
piece
handle=/u01/app/oracle/flash_recovery_area/PRMY001/backupset/2011_07_12/o1_mf_annnn_TAG20
110712T195144_71rp3t6y_.bkp tag=TAG20110712T195144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 12-JUL-11
Starting backup at 12-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/prmy001/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/prmy001/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/prmy001/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/prmy001/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-11
channel ORA_DISK_1: finished piece 1 at 12-JUL-11
piece
handle=/u01/app/oracle/flash_recovery_area/PRMY001/backupset/2011_07_12/o1_mf_nnndf_TAG20
110712T195154_71rp432z_.bkp tag=TAG20110712T195154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:17
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-JUL-11
channel ORA_DISK_1: finished piece 1 at 12-JUL-11
piece
handle=/u01/app/oracle/flash_recovery_area/PRMY001/backupset/2011_07_12/o1_mf_ncsnf_TAG201
10712T195154_71rp6kn5_.bkp tag=TAG20110712T195154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 12-JUL-11
Starting backup at 12-JUL-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=4 stamp=756330794
channel ORA_DISK_1: starting piece 1 at 12-JUL-11
channel ORA_DISK_1: finished piece 1 at 12-JUL-11
piece
handle=/u01/app/oracle/flash_recovery_area/PRMY001/backupset/2011_07_12/o1_mf_annnn_TAG20
110712T195314_71rp6n19_.bkp tag=TAG20110712T195314 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 12-JUL-11

 --------------------------------------------------------------------------------------------------------------

 STEP 7: Now connect to RMAN and issue the below script.

 [oracle@oradba ~]$ rman target /

 RMAN> connect auxiliary sys/dprmy001@dprmy001

 RMAN> run
{
allocate auxiliary channel c1 type disk;
duplicate target database to dprmy001;
}
2> 3> 4> 5>
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=156 devtype=DISK

Starting Duplicate Db at 12-JUL-11

 -----------------------------------------------------
I have cut down the script execution results
-----------------------------------------------------

 database opened
Finished Duplicate Db at 12-JUL-11

RMAN>

 Duplicate database using RMAN is completed. Now you can connect to the duplicate database.
------------------------------------------------------------------------------------------------------------

Comments

Post a Comment

Popular posts from this blog

CHANGING DBID FOR ORACLE DATABASE 11G

C:\Users\computer>sqlplus SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 21:03:33 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select dbid from v$database;       DBID ---------- 2188161033 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options C:\Users\computer>nid DBNEWID: Release 11.1.0.7.0 - Production on Thu Sep 10 21:04:44 2013 Copyright (c) 1982, 2007, Oracle.  All rights reserved. Keyword     Description                    (Default) ---------------------------------------------------- TARGET ...
Post a Comment
Read more

Difference between AWR, ADDM and ASH reports

AWR: Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. Different Components that uses AWR are: Automatic Database Diagnostic Monitor Undo Advisor SQL Tuning Advisor Segment Advisor Different types of AWR Reports for different purposes: For Single Instance Environment: @$ORACLE_HOME/rdbms/admin/awrrpt.sql For Oracle RAC Environment : @$ORACLE_HOME/rdbms/admin/awrgrpt.sql For a particular SQL Statement : @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql For Comparing the reports : @$ORACLE_HOME/rdbms/admin/awrddrpt.sql ADDM: Automatic Database Diagnostic Monitoring Report analyzes the AWR data on a regular basis, to give you overview of the root cause of the problem which is affecting your database’s performance. It also provides suggestions or recommendations for rectifying any problem identified and lists the areas which are having no issues. ADDM recommends multiple solutions for the DBA to choose from which...
Post a Comment
Read more