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
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
 |
This email has been checked for viruses by Avast antivirus software.
www.avast.com |
1