RMAN Duplicate To Another Server With Different SID

Cloning MYDBPROD → MYDBTEST with RMAN DUPLICATE

Sometimes we need a quick, up‑to‑date copy of production for troubleshooting or functional testing. The fastest route is RMAN duplicate from active database, which streams the data files over the network—no backup & restore cycle required.

Environment snapshot
oracle1 → production server (SID MYDBPROD)
oracle2 → test server (SID MYDBTEST)
Database names: MYDBP (prod) → MYDBT (test)
OS: Oracle Linux 8
SELinux Permissive, firewalld disabled.
Only the Oracle binaries are installed on the test host—no database.

1  Configure the listener on the test server

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME    = MYDBTEST)
    )
  )

Start (or reload) it:

lsnrctl start listener

2  Add tnsnames.ora entries on prod

# ── MYDBPROD ───────────────────────────
MYDBPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBPROD)
    )
  )

# ── MYDBTEST ───────────────────────────
MYDBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBTEST)
    )
  )

Verify connectivity:

tnsping MYDBTEST

3  Ship pfile and password file to the test host

-- on oracle1 (prod)
CREATE PFILE='/tmp/pfile_mydbp.ora' FROM SPFILE;
EXIT;

scp /tmp/pfile_mydbp.ora oracle@oracle2:$ORACLE_HOME/dbs
scp $ORACLE_HOME/dbs/orapwMYDBPROD \
    oracle@oracle2:$ORACLE_HOME/dbs/orapwMYDBTEST

# (or create a fresh file if none exists)
orapwd file=$ORACLE_HOME/dbs/orapwMYDBTEST force=y

4  Edit the copied pfile on oracle2

Change MYDBP → MYDBT, adjust paths, and add the name‑conversion rules:

MYDBT.__oracle_base = '/u01/app/oracle'
...
*.control_files         = '/u02/oradatatest/MYDBTEST/control01.ctl','/u02/oradatatest/MYDBTEST/control02.ctl'
*.db_name               = 'MYDBT'
*.db_unique_name        = 'MYDBTEST'
...
*.db_file_name_convert  = '/u02/oradata/MYDBPROD','/u02/oradatatest/MYDBTEST'
*.log_file_name_convert = '/u02/oradata/MYDBPROD','/u02/oradatatest/MYDBTEST'

5  Create the required directories

mkdir -p /u01/app/oracle/admin/MYDBTEST/adump
mkdir -p /u02/oradatatest/MYDBTEST

6  Start the auxiliary instance in NOMOUNT

export ORACLE_SID=MYDBTEST
sqlplus / as sysdba
STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/pfile_mydbt.ora';
CREATE SPFILE FROM PFILE='$ORACLE_HOME/dbs/pfile_mydbt.ora';
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;

7  Kick off the duplicate from the production host

rman target sys auxiliary sys@MYDBTEST

Provide the same SYS password for both prompts, then at the RMAN> prompt run:

DUPLICATE TARGET DATABASE TO 'MYDBT'
  FROM ACTIVE DATABASE
  NOFILENAMECHECK;

RMAN streams the control file, datafiles, and redo, adjusts the DBID, and finally opens the new test database.

© 2025 Your‑Name‑or‑Company. Feel free to share or adapt with attribution.

Comments

Post a Comment

Popular posts from this blog

Oracle 19c Dataguard Installation with DG Broker

Oracle 19c Data Guard – Standby Creation, Switchover & Failover Oracle 19c Physical Standby Creation & Management (RMAN Duplicate + Data Guard Broker) This article walks through building a physical standby using RMAN  DUPLICATE FROM ACTIVE DATABASE , then managing switchover / failover with the Data Guard Broker. The commands were tested on Oracle Linux 8 with Oracle Database 19c . Assumptions Two hosts ( oracle1.localdomain & oracle2.localdomain ) with Oracle 19c installed. Primary instance runs on oracle1 ; standby will be created on oracle2 . Listener port 1521 is reachable in both directions (check firewalls). Primary DB name MYDBPROD ; standby unique name MYDBDG . 1 – Primary Server Preparation 1.1 Switch to ARCHIVELOG & Force Logging -- Check mode SELECT log_mode FROM v$database; -- If NOARCHIVELOG: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCH...
Read more

Error when Installing Some Postgresql Packages (Perl IPC-Run)

Resolving PostgreSQL 17 Dependency Error on Oracle Linux 8 Fixing perl(IPC::Run) Dependency Error When Installing PostgreSQL 17 on Oracle Linux 8 Background While preparing a fresh Oracle Linux 8 server for a PostgreSQL 17 deployment, an attempt to install all PostgreSQL packages in one go failed with an unsatisfied dependency error: Problem 1: cannot install the best candidate for the job - nothing provides perl(IPC::Run) needed by postgresql17-devel-17.4-1PGDG.rhel8.x86_64 ... Root Cause The perl(IPC::Run) module (along with several tool‑chain libraries) resides in the CodeReady Builder repository, which is disabled by default on Oracle Linux 8. Because postgresql17-devel and postgresql17-test depend on that module, dnf / yum cannot resolve the full dependency tree unless the repository is enabled. CodeReady Builder hosts developer‑oriented packages—compilers, debuggers, Perl/Python modules—that ma...
Read more

Creating Jobs With Different Users via pg_cron in Azure Postgresql Flexible Server

PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases In this tutorial, I’ll walk you through the process of scheduling a Materialized View refresh using the pg_cron extension in PostgreSQL. Specifically, we’ll cover how to execute a scheduled SQL command in a different database using cron.schedule_in_database . Step 1: Enable the pg_cron Extension in Azure Portal On Azure PostgreSQL Flexible Server, the pg_cron extension is managed directly through the Azure portal. Follow these steps: Go to the Azure portal and navigate to your PostgreSQL Flexible Server instance. Under the Server Parameters section, locate the azure.extensions parameter. Add PG_CRON to the list of allowed extensions (as shown in the screenshot). Once enabled, the extension is automatically installed in the default `postgres` database . If you need to schedule jobs in o...
Read more