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

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG
Post a Comment
Read more

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes Database Buffers          436207616 bytes Redo Buffers          
2 comments
Read more