Using RMAN DUPLICATE when the target system is behind a firewall

My production servers are on the other end of a network link where they can not make connections to any of the servers in my dev/test environment, but connections from dev/test to production are fine.  This is problematic:

  • OEM 12c (housed in dev/test environment) can't monitor production (needs 2-way agent<->OMS comms)
  • RMAN DUPLICATE doesn't work (requires connection from source to destination)
  • No way to push files from production to dev/test
  • And many others...
The point of this post is to talk about RMAN DUPLICATE in this situation.

As there's no outbound connectivity from prod->dev/test, I initially assumed this wouldn't be possible, however I've tried using SSH tunnels. and while it didn't work due to my previous blog post (RMAN-05531 during duplicate from active standby), the concept is fine.

Log in to the shell of the destination database, and follow the usual setup (DocID 452868.1) to create a duplicate from active database:
  • Create PFILE ($ORACLE_HOME/dbs/initNEWSID.ora) with DB_NAME and COMPATIBLE parameters
  • Add entries for source and duplicate in tnsnames.ora
  • Verify connection from duplicate to source with tnsping
  • Add entry for duplicate in listener.ora
  • Create password file for duplicate database ($ORACLE_HOME/dbs/orapwNEWSID)
  • Start duplicate DB in nomount mode
At this point, we need to allow for the source system to connect to the duplicate.  One way to do this is using SSH tunnelling.  With this we can set up a port on the source system which is tunnelled "through" the SSH connection back to the duplicate server on any port we specify.  So we can direct, say, port 1522 on the source server to port 1521 on the duplicate!

Start a new SSH connection to the duplicate, then SSH to the source server, tunnelling the ports as appropriate.  e.g.:
    ssh -C -R1522:localhost:1521 oracle@production.domain.local

The -R switch tells the remote (source) server that if it gets any connections on port 1522, to redirect them through the SSH tunnel, to port 1521 on localhost on the local (duplicate) server, where the SSH command was run.

Lastly, we set up the source server so it knows how to talk to the duplicate.
  • Edit tnsnames.ora, add a new entry for the duplicate DB with:
    • SID=NEWSID
    • port=1522 
    • host=localhost
  • Confirm the source can contact the duplicate using tnsping
That's it!  On the duplicate, run the RMAN DUPLICATE as normal, and everything should, theoretically, "just work".

Comments

Post a Comment

Popular posts from this blog

Data Guard with Transparent Application Failover (TAF)

One of the problems with Data Guard is that even after switching/failing over to the standby database, all clients need to be reconfigured to communicate with the new primary. The simplest way to achieve this (if using a centralised tnsnames.ora ), is to modify the file and  add the line " (FAILOVER=YES) ",  and either multiple ( DESCRIPTION=...) or (ADDRESS=...)  lines, one for each of the standby systems, in the order that the client should attempt connections . This tells the client to try each address in turn until one succeeds. Doing so provides basic failover functionality within Oracle Net, and will probably function correctly most of the time.  Though be aware - because both primary and standby can share the same instance name (e.g. the same SID, therefore the same connection string), there is potential for this simple scheme to go wrong. An example in an active data guard environment (where the standby is open read only): a temporary network issue causes all c
Read more

RMAN-05531 During RMAN Duplicate from Active Data Guard Standby

[oracle@target dbs]$ rman auxiliary sys@dup target sys@source [...] RMAN> duplicate target database to DUP from active database db_file_name_convert '/u2/data/PROD','/u2/data/DUP' LOG_FILE_NAME_CONVERT '/u2/data/PROD/onlinelog','/u2/data/DUP' nofilenamecheck; Starting Duplicate Db at 25-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=171 device type=DISK RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of Duplicate Db command at 02/25/2015 16:04:44 RMAN-05501: aborting duplication of target database RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy Using Oracle 11.2.0.3 (anything below 11.2.0.3.6), this is the error if an attempt is made to duplicate from an activ
Read more

Data pump - "ORA-39786: Number of columns does not match between export and import databases"

Today I was asked if it was possible to import data for a specific table from a data pump export file, which was taken from when a table had a different structure to what it has now. e.g. when the export was taken, there were 10 columns.  Now there are 11. I thought that was simple, and I was sure I'd heard of this being done before.  Simply import using the "CONTENT=DATA_ONLY" parameter.  Right? Apparently not: Centos-/home/oracle/demo> impdp DEMO_USER/yyyyyyy directory=INBOUND dumpfile=demo_data.dmp logfile=impdpdemo.log content=data_only tables=xxxxxxxx Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 08 July, 2014 14:32:20 Copyright (c) 2003, 2007, Oracle.  All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production Master table "DEMO_USER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "DEMO_USER"."SYS_IMPORT_TABLE_01":  xxxxxxxx/******** directory=INBOUND dumpfile
Read more