Search

Friday, June 24, 2016

Creating duplicate database Using RMAN in Oracle Database 11gR2

Duplicate a Database Using RMAN in Oracle Database 11g Release 2
-- Following Active Database Duplication method

Introduction:

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions. First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.

Purpose of Database Duplication: 
A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:
  • Test backup and recovery procedures
  • Test an upgrade to a new release of Oracle Database
  • Test the effect of applications on database performance
  • Create a standby database
  • Generate reports
Techniques for Duplicating a Database:
RMAN supports two basic types of duplication: active database duplication and backup-based duplication.

A connection to both is required for active database duplication.

Figure-1 shows the decision tree for the two duplication techniques.









Active Database Duplication:
In active database duplication, RMAN connects as TARGET to the source database instance and as AUXILIARY to the auxiliary instance. RMAN copies the live source database over the network to the auxiliary instance, thereby creating the duplicate database. No backups of the source database are required. Figure -2 illustrates active database duplication.
















             Figure-2 Active Database Duplication

Steps of Database Duplication (Active method)

A) Pre-Configuration Steps:

1) Find Source and Target database 
Assume host name of Source database is "DB1" and target database is "DB2". Both Severs are stand-alone type.
Assume source database name is PRODDB and target database name is REPDB.
2) Find connectivity between both hosts
use ping with IP from both server. Ping status should OK.
3) Verify OS and database versions
Assume OS version RHEL 6.3 and Oracle Software version 11.2.0.3
4) Verify Source database database size and more than space should available in target database.
5) Check Archive log is enabled in source database.


B) Configuration steps:

-- in proposed duplicate database side
1) configure listener.
Check listener should up and running in target side.
2) configure tnsname.ora for itself and source also
Check tnsping from both databses. Both side status should OK.
3) configure initREPDB.ora ( Duplicate Specific parameters)

db_name='REPDB'
DB_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'
LOG_FILE_NAME_CONVERT='/u01/EHISDC/ORADATA/PRODDB/','/u10/ORADATA/REPDB/'

See sample pfile:


















Note: Remove db_unique_name option, if your target database listener is blocking.

4) create a password file in source database and move it to target database side and rename it database SID name.
e.g.,
-- Source side
DB1] $ cd $ORACLE_HOME/dbs
DB1] $ orapwd force=y file=orapwPRODDB password=system
$ scp orapwPRODDB oracle@DB2:/$ORACLE_HOME/dbs
-- Traget side
DB2] $ cd $ORACLE_HOME/dbs
$ mv orapwPRODDB orapwREPDB
Now check connectivity using sqlplus in source database using password. It should connect
DB1] $ sqlplus /nolog
sql> connect sys/system@REPDB as sysdba
connected.


C) Create Duplicate database:

1) When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.

$ export ORACLE_SID=$PRODDB
$ rman target=sys/system auxiliary=sys/system@REPDB
OR
$ rman target /
rman> connect auxiliary sys/system@REPDB;


2) Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.

RMAN>
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database to REPDB from active database;
}


Note: Automatically database will be created and open it r/w mode also.

-- Issues may be faced

-- Issue-1:

DBGSQL:     TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end;
DBGSQL:        sqlcode = 6550
DBGSQL:         B :fhdbi = 32767
released channel: stby1
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/21/2014 19:52:49
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Solution:1: like this
pfile :
*.db_name=prod1
*.db_unique_name=prod2


-- Issue-2:
RMAN-04006:  ORA-28547:

RMAN-04006: error from auxiliary database: ORA-28547: connection to server failed, probable Oracle Net admin error

Solution:2:

1) re-create password file for target / duplicate database properly
2) recheck tnsnames.ora entry
3) Check the target database/ duplcate database listener status
4) While connecting auxiliary database, check auxiliary is connecting or not.

-- Issue-3:

Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2015 17:37:34
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01102: cannot mount database in EXCLUSIVE mode

OR

Solution-1: 

If any oracle process is running with your duplicate database name, then kill them.

e.g.

$ ps -ef|grep $ORACLE_SID
$ kill -9 <pid>

Solution-2:

Add below parameter in standby parameter.

*.remote_login_passwordfile='EXCLUSIVE'

Solution-3:

Comment below entry in standby parameter file if you have kept/ add.

.db_unique_name='primary_dbname'

-- Issue - 4:

Oracle instance shut down
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 16:54:33
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ch1 channel at 04/29/2015 16:54:24
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Solution:
1) re-create password file in target db and test connection from source db.
2) Check the listener status. No blocked service should be there: 
e.g.
Services Summary...
Service "PROD1" has 2 instance(s).
  Instance "PROD1", status UNKNOWN, has 1 handler(s) for this service...
  Instance "PROD1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD2" has 2 instance(s).
  Instance "PROD2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "PROD2", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
Verify your listener:
sample listener.ora

LISTENER_UAT =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = uat-bkp)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = UAT)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)

    )
  )

ADR_BASE_LISTENER_UAT = /u01/app/oracle
Note: Most of cases issue will come when listener.ora is not configured properly.
3) while you are connecting from source database via rman to target database, check which target db is connecting:
sample:
$ rman target=sys/system auxiliary=sys/system@UAT;

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 29 18:45:29 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=1278375623)
connected to auxiliary database: UAT (not mounted)
4) Check source db is archivelog mode is on or not.

The the rman log / message while creating duplicate database:


.....
.....
duplicate target database to UAT from active database;
}
2> 3> 4> 5> 
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=964 device type=DISK

 allocated channel: stby1
channel stby1: SID=541 device type=DISK

 Starting Duplicate Db at 29-APR-15

 contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

 sql statement: create spfile from memory

 contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

 Oracle instance shut down

 connected to auxiliary database (not started)

 Fixed Size                     2237008 bytes
Variable Size               1577061808 bytes
Database Buffers            8455716864 bytes
Redo Buffers                  19767296 bytes
allocated channel: stby1
channel stby1: SID=272 device type=DISK

 contents of Memory Script:
{
   sql clone "alter system set  db_name = 
 ''PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''UAT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/ORADATA/UAT/control01.ctl';
   restore clone controlfile to  '/u01/ORADATA/UAT/control02.ctl' from 
 '/u01/ORADATA/UAT/control01.ctl';
   alter clone database mount;
}
executing Memory Script

 sql statement: alter system set  db_name =  ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile

 sql statement: alter system set  db_unique_name =  ''UAT'' comment= ''Modified by RMAN duplicate'' scope=spfile

 Oracle instance shut down

 ...
...
 72 auxiliary format 
 "/u01/ORADATA/UAT/L2SUPPORT_TBLSPC01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 ...
...
Starting backup at 29-APR-15
channel ch1: starting datafile copy
input datafile file number=00033 name=/u01/ORADATA/PROD/HR01.dbf

 ...
...
datafile 70 switched to datafile copy
input datafile copy RECID=69 STAMP=878325378 file name=/u01/ORADATA/UAT/CRM_INDEX01.dbf
datafile 71 switched to datafile copy
input datafile copy RECID=70 STAMP=878325378 file name=/u01/ORADATA/UAT/LAB01.dbf
datafile 72 switched to datafile copy
input datafile copy RECID=71 STAMP=878325378 file name=/u01/ORADATA/UAT/SUPPORT_TBLSPC01.dbf

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

 database opened
Finished Duplicate Db at 29-APR-15
released channel: ch1
released channel: stby1

 RMAN>


Issue -5 : When data block corruption will come during duplicate database creation:

I found below message like this and terminated the session:
.....
.....
.....
datafile 71 switched to datafile copy
input datafile copy RECID=70 STAMP=878329989 file name=/u01/ORADATA/UAT/HR02.dbf
datafile 72 switched to datafile copy
input datafile copy RECID=71 STAMP=878329989 file name=/u01/ORADATA/UATAHLL/SUPPORT_TBLSPC01.dbf

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure. 
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 04/29/2015 20:33:48
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 86663)
ORA-01110: data file 1: '/u01/ORADATA/UAT/system01.dbf'
Process ID: 25656
Session ID: 805 Serial number: 5
$
The above message is clearly telling there is issue with disk and corrupted block in source database.

Action to be taken:
1) Verify source database block corruption issues.
2) Validate some backups for corrupted blocks in source and then transfer to target database and again validate in target database side and restore.
3) The last option is to take a full export dump and restore it.

Note: Try to replace the exiting storage.
Posted by at