Uncategorized

RMAN Duplicate With Same Database Name and Same Directory Structure

July 3, 2018

Overview

 Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.

Environment Details

Target (Source) details
Database Name
prod
Hostname
Snsm-linux02
Ip Address
172.16.110.18
OS
Linux
Version
x86_64
Datafile Location
/data2/prod
Backup Location
/backup/rman_backup

Tns details:
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )


Destination (Auxiliary) details:
Database Name
Prod
Hostname
Nsm-linux01
Ip Address
172.16.110.16
OS
Linux
Version
x86_64
Datafile Location
/data2/prod
Backup Location
/backup/rman_backup

Tns details:
DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dup)
    )
  )

Pre-requisites

  • Oracle net configuration
  • Both target and destination server should have tns entries
  • Static listener registration on auxiliary site
  • Service should be register on aux listener
  • Password files from target database
  • Sqlnet.ora should have correct parameters
  • Target database should be running through spfile
  • Check connectivity between target and destination server

Steps on target (source) server

  • Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory
[oracle@Snsm-linux02 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
[oracle@Snsm-linux02 admin]$ vi tnsnames.ora

DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dup)
    )
  )

[oracle@Snsm-linux02 admin]$ tnsping dup

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:34:19

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = dup)))
OK (0 msec)
  •  Check database whether it is running in archive log mode.
SQL> select name, open_mode, log_mode from v$database;

NAME       OPEN_MODE            LOG_MODE
———- ——————– —————
PROD       READ WRITE           ARCHIVELOG
  • Check database is running from spfile.
SQL> select value from v$parameter where name=’spfile’;

VALUE
—————————————————————–
/data2/app/oracle/product/11.2.0/dbs/spfileprod.ora

or


SQL> show parameter pfile

NAME                                 TYPE                              VALUE
———————————— ——————————— ——————————
spfile                               string                            /data2/app/oracle/product/11.2
                                                                       .0/dbs/spfileprod.ora

  • Create pfile from spfile.
SQL> create pfile=’/backup/rman_backup/initprod.ora’ from spfile;

File created.

  • Create password file.
[oracle@Snsm-linux02 dbs]$ pwd
/data2/app/oracle/product/11.2.0/dbs
[oracle@Snsm-linux02 dbs]$orapwd file=orapwprod password=oracle

  • Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp
scp orapwprod oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/
scp /backup/rman_backup/initprod.ora oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/

Steps on destination server

  • Put static listener entry into listener.ora file in $ORACLE_HOME/network/admin directory.
[oracle@Nsm-linux01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin
[oracle@Nsm-linux01 admin]$

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dup)
      (ORACLE_HOME = /data2/app/oracle/product/11.2.0)
      (SID_NAME = prod)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Here global_name is nothing but service name and sid_name is instance name
Now reload the listener and check the services

[oracle@Nsm-linux01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:46:41

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
The command completed successfully

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))
Services Summary…
Service “dup” has 1 instance(s).
  Instance “prod”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0
         LOCAL SERVER
Service “prod” has 1 instance(s).
      “DEDICATED” established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
  • Put Tns entry for target (source) database in $ORACLE_HOME/network/admin directory
[oracle@Nsm-linux01 admin]$ vi tnsnames.ora
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )

[oracle@Nsm-linux01 admin]$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:49:28

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)

(SERVICE_NAME = prod)))
OK (10 msec)

  • Create the directory structure same as target(source) server.
[oracle@Nsm-linux01 admin]$ mkdir -p /data2/prod
[oracle@Nsm-linux01 admin]$ mkdir -p /backup/archive
  • Set the oracle Sid and start the instance in no mount stage.
[oracle@Nsm-linux01 admin]$ export ORACLE_SID=prod
[oracle@Nsm-linux01 admin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 12:54:37 2015

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

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size                  2212696 bytes
Variable Size             335547560 bytes
Database Buffers          805306368 bytes
Redo Buffers                9383936 bytes
SQL>
  • Now connect to Rman
[oracle@Nsm-linux01 admin]$ rman target sys/oracle@prod auxiliary sys/oracle@dup

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 1 12:56:39 2015

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

connected to target database: PROD (DBID=284539893)
connected to auxiliary database: PROD (not mounted)

RMAN>

  • Issue the command to duplicate the database

RMAN> DUPLICATE TARGET DATABASE TO prod FROM ACTIVE DATABASE NOFILENAMECHECK;

Starting Duplicate Db at 01-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

Following is the output.

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 =
”PROD” comment=
”Modified by RMAN duplicate” scope=spfile”;
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  ‘/data2/prod/control01.ctl’;
   restore clone controlfile to  ‘/data2/prod/control02.ctl’ from
‘/data2/prod/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 =  ”PROD” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

Starting backup at 01-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f tag=TAG20150401T124940 RECID=4 STAMP=875882981
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-APR-15

Starting restore at 01-APR-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-APR-15

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
“/data2/prod/system01.dbf”;
   set newname for datafile  2 to
“/data2/prod/sysaux01.dbf”;
   set newname for datafile  3 to
“/data2/prod/undotbs01.dbf”;
   set newname for datafile  4 to
“/data2/prod/users01.dbf”;
   set newname for datafile  5 to
“/data2/prod/example01.dbf”;
   backup as copy reuse
   datafile  1 auxiliary format
“/data2/prod/system01.dbf”   datafile
2 auxiliary format
“/data2/prod/sysaux01.dbf”   datafile
3 auxiliary format
“/data2/prod/undotbs01.dbf”   datafile
4 auxiliary format
“/data2/prod/users01.dbf”   datafile
5 auxiliary format
“/data2/prod/example01.dbf”   ;
   sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data2/prod/system01.dbf
output file name=/data2/prod/system01.dbf tag=TAG20150401T124955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/data2/prod/sysaux01.dbf
output file name=/data2/prod/sysaux01.dbf tag=TAG20150401T124955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/data2/prod/example01.dbf
output file name=/data2/prod/example01.dbf tag=TAG20150401T124955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/data2/prod/undotbs01.dbf
output file name=/data2/prod/undotbs01.dbf tag=TAG20150401T124955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/data2/prod/users01.dbf
output file name=/data2/prod/users01.dbf tag=TAG20150401T124955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-APR-15

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  “/backup/archive/1_116_872701561.dbf” auxiliary format
“/backup/archive/1_116_872701561.dbf”   ;
   catalog clone archivelog  “/backup/archive/1_116_872701561.dbf”;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 01-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=116 RECID=112 STAMP=875883160
output file name=/backup/archive/1_116_872701561.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 01-APR-15

cataloged archived log
archived log file name=/backup/archive/1_116_872701561.dbf RECID=112 STAMP=875883644

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=875883644 file name=/data2/prod/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=875883644 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=875883645 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=875883645 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=875883645 file name=/data2/prod/example01.dbf

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

executing command: SET until clause

Starting recover at 01-APR-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 116 is already on disk as file /backup/archive/1_116_872701561.dbf
archived log file name=/backup/archive/1_116_872701561.dbf thread=1 sequence=116
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-APR-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone “alter system set  db_name =
”PROD” comment=
”Reset to original value by RMAN” scope=spfile”;
   sql clone “alter system reset  db_unique_name scope=spfile”;
   shutdown clone immediate;
   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    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes

sql statement: alter system set  db_name =  ”PROD” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1152450560 bytes

Fixed Size                     2212696 bytes
Variable Size                335547560 bytes
Database Buffers             805306368 bytes
Redo Buffers                   9383936 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “PROD” RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP  1 ( ‘/data2/prod/redo01.log’ ) SIZE 50 M  REUSE,
  GROUP  2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M  REUSE,
  GROUP  3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M  REUSE
DATAFILE
  ‘/data2/prod/system01.dbf’
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
“/data2/prod/temp01.dbf”;
   switch clone tempfile all;
   catalog clone datafilecopy  “/data2/prod/sysaux01.dbf”,
“/data2/prod/undotbs01.dbf”,
“/data2/prod/users01.dbf”,
“/data2/prod/example01.dbf”;
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data2/prod/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/data2/prod/sysaux01.dbf RECID=1 STAMP=875883671
cataloged datafile copy
datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=875883671
cataloged datafile copy
datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=875883671
cataloged datafile copy
datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=875883672

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=875883671 file name=/data2/prod/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=875883671 file name=/data2/prod/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=875883671 file name=/data2/prod/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=875883672 file name=/data2/prod/example01.dbf

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

database opened
Finished Duplicate Db at 01-APR-15


 Issues which we have faced are as follows              

Everything has went well listener services were also worked fine, RMAN connectivity from auxiliary server also fine using net service name but  When issue the duplicate database command from RMAN> prompt it has thrown following error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:01:01
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:01:01
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server


Then we have searched for solutions, we get one MOS note 1144273.1

This note gives some idea like causes and solutions from that we have checked in our databases on destination server there are wrong parameters in sqlnet.ora then we have corrected the sqlnet.ora file as follows

[oracle@Nsm-linux01 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES = (NTS,NONE)
#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /data2/app/oracle


Now we have executed again, this time we get different error, check below.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:23:02
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:23:02
ORA-17628: Oracle error 19505 returned by remote Oracle server

After dig out the error we get to know that there is no directory structure is available on destination server to accommodate data files and control  Files, we have created the directory structure and execute the duplicate command again, this time has successful.
 Additionally there is one more MOS note for known issues while duplicating RMAN. 1366290.1

Conclusion

In the above article, we have learned that Active Duplication using Rman utility with same database name and same directory structure, and we have faced some errors and their work around.





BY
Name: Omer
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com


Avast logo
This email has been checked for viruses by Avast antivirus software.
www.avast.com

Tags: , , , , , , , , , ,