Introduction
When migrating Oracle databases, achieving zero data loss while minimizing downtime is critical. This article demonstrates how to use RMAN's DUPLICATE FROM ACTIVE DATABASE feature with the NOOPEN clause to achieve zero data loss migration while keeping the source database online.
The Challenge
Traditional RMAN active database duplication opens the duplicate database immediately after completion, which means:
- Any transactions committed after duplication starts may be lost
- For large databases (TB+), duplication can take hours
- Data loss window = time from duplication start to completion
Question: Can we recover all transactions up to a planned cutover time, even if duplication takes hours?
Answer: Yes, using the NOOPEN clause.
The Solution: NOOPEN Clause
The key is to prevent RMAN from opening the duplicate database immediately. Instead, keep it in MOUNT mode and open it at your planned cutover time.
How It Works
- Duplication Phase (source stays online): RMAN duplicates datafiles and applies archive logs up to duplication completion
- Mount Mode: Database remains in MOUNT mode, not yet opened
- Cutover: Stop application, force log switch
- Roll Forward: Catalog and apply all remaining archive logs generated during duplication
- Open with RESETLOGS: Open database to complete recovery
- Result: Zero data loss
Implementation
Prerequisites
- Source database in ARCHIVELOG mode
- Network connectivity between source and target
- Sufficient disk space on target server
- Password file with matching SYS password on both servers
Step 1: Prepare Auxiliary Instance
#!/bin/bash
# prepare_auxiliary.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=dupdb
export PATH=$ORACLE_HOME/bin:$PATH
# Create directories
mkdir -p /u02/oradata/dupdb
mkdir -p /u02/fra/dupdb
mkdir -p /u01/app/oracle/admin/dupdb/adump
# Create password file (match source password)
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwdupdb password=<YOUR_PASSWORD> force=yes
# Create init parameter file
cat > $ORACLE_HOME/dbs/initdupdb.ora <<'EOF'
db_name=dupdb
db_unique_name=dupdb
control_files='/u02/oradata/dupdb/control01.ctl','/u02/oradata/dupdb/control02.ctl'
db_block_size=8192
sga_target=16G
pga_aggregate_target=8G
processes=1000
db_recovery_file_dest=/u02/fra/dupdb
db_recovery_file_dest_size=500G
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
EOF
# Start auxiliary instance
sqlplus / as sysdba <<SQL
startup nomount pfile='$ORACLE_HOME/dbs/initdupdb.ora'
exit
SQL
Step 2: Configure TNS (if on different servers)
On both source and target servers, add TNS entries:
# tnsnames.ora
SOURCEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = source-server)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = sourcedb))
)
DUPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = target-server)(PORT = 1521))
(CONNECT_DATA = (SID = dupdb))
)
On target server, add static listener entry for auxiliary instance:
# listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dupdb)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = dupdb)
)
)
Reload listener: lsnrctl reload
Step 3: Execute Duplication with NOOPEN
#!/bin/bash
# rman_duplicate_noopen.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=sourcedb # Run from source server
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE="/backup/rman_duplicate_$(date +%Y%m%d_%H%M%S).log"
echo "Starting duplication at: $(date)"
echo "Log file: $LOG_FILE"
$ORACLE_HOME/bin/rman msglog=$LOG_FILE <<'EOF'
connect target sys/<PASSWORD>@sourcedb
connect auxiliary sys/<PASSWORD>@dupdb
run {
allocate channel c1 device type disk connect 'sys/<PASSWORD>@sourcedb';
allocate channel c2 device type disk connect 'sys/<PASSWORD>@sourcedb';
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
duplicate target database to dupdb from active database
spfile
set db_unique_name='dupdb'
set db_name='dupdb'
set control_files='/u02/oradata/dupdb/control01.ctl','/u02/oradata/dupdb/control02.ctl'
set db_recovery_file_dest='/u02/fra/dupdb'
set db_recovery_file_dest_size='500G'
set diagnostic_dest='/u01/app/oracle'
set db_file_name_convert='/u01/oradata/sourcedb','/u02/oradata/dupdb'
set log_file_name_convert='/u01/oradata/sourcedb','/u02/oradata/dupdb'
nofilenamecheck
noopen; # KEY: Keep database in MOUNT mode
}
exit
EOF
echo "Duplication completed at: $(date)"
echo "Database is in MOUNT mode, ready for cutover"
Important: For same-server duplication, add nofilenamecheck. For different servers, omit it.
Step 4: Cutover Process
At your planned cutover time:
#!/bin/bash
# cutover.sh
# 1. Stop application
echo "Stopping application..."
# <your application stop commands>
# 2. Force log switch on source to generate final archive logs
export ORACLE_SID=sourcedb
sqlplus / as sysdba <<EOF
alter system switch logfile;
alter system archive log current;
exit
EOF
echo "Waiting for archive logs to be written..."
sleep 5
# 3. On target server: Apply remaining archive logs
export ORACLE_SID=dupdb
# Option A: If archive logs are accessible via NFS or shared storage
rman target / <<EOF
catalog start with '/mnt/source_archive/';
recover database;
exit
EOF
# Option B: If archive logs were copied to target
rman target / <<EOF
catalog start with '/target/archive/';
recover database;
exit
EOF
# 4. Open duplicate database with RESETLOGS
sqlplus / as sysdba <<EOF
alter database open resetlogs;
exit
EOF
# 5. Open PDBs
sqlplus / as sysdba <<EOF
alter pluggable database all open;
exit
EOF
echo "Cutover completed at: $(date)"
Key Steps Explained:
- Force log switch: Ensures all transactions are in archive logs
- Catalog archive logs: Makes RMAN aware of new archive logs from source
- Recover database: Applies all archive logs up to the latest available
- Open resetlogs: Creates new incarnation and completes recovery
Step 5: Verification
-- On duplicate database
ALTER SESSION SET CONTAINER = pdb1;
SELECT COUNT(*) FROM critical_table;
-- Compare with source
-- Counts should match exactly
Key Considerations
Archive Log Management
For large databases with long duplication times:
- Archive log retention: Ensure sufficient space for archive logs during duplication
- Archive log destination: Must be accessible from duplicate database
- Monitoring: Watch archive log generation rate
-- Check archive log space usage
SELECT SUM(blocks * block_size)/1024/1024/1024 AS gb_used
FROM v$archived_log
WHERE first_time > SYSDATE - 1;
Network Bandwidth
For different servers:
- Bandwidth requirement: ~100-500 MB/s for efficient transfer
- Compression: Consider using
SECTION SIZEfor parallel transfer - Monitoring: Track network utilization during duplication
Downtime Estimation
Actual downtime = Time to:
- Stop application (seconds)
- Force log switch (seconds)
- Roll forward: Copy, catalog and apply remaining archive logs (60 - 300 seconds)
- Open database with RESETLOGS (1-3 minutes for TB databases)
- Verify data (1-2 minutes)
Typical downtime: 3-10 minutes regardless of database size
Data Loss Risk
Zero data loss IF:
- Archive logs are retained and accessible
- No archive log gaps
- Duplicate database can access source archive log location
Verify before cutover:
-- Check for archive log gaps
SELECT thread#, sequence#
FROM v$archived_log
WHERE deleted = 'NO'
ORDER BY thread#, sequence#;
Different Server Considerations
Network Configuration
- Firewall rules: Open port 1521 between servers
- DNS/hosts: Ensure hostname resolution
- TNS connectivity: Test with
tnspingbefore duplication
Archive Log Access
Option 1: NFS Mount (Recommended)
# On target server, mount source archive log location
mount source-server:/archive /mnt/source_archive
# In RMAN, Oracle will automatically find logs
Option 2: Copy Archive Logs
# After duplication, before opening
scp source-server:/archive/* /target/archive/
# Then open database
Option 3: Standby Redo Logs (Advanced) Configure standby redo logs for real-time log shipping during duplication.
File Path Mapping
Ensure correct path conversion:
-- Check source paths
SELECT name FROM v$datafile;
SELECT member FROM v$logfile;
-- Use in duplicate command
set db_file_name_convert='/source/path','/target/path'
set log_file_name_convert='/source/path','/target/path'
Finding Recovery SCN
After duplication completes, verify the recovery point:
-- Checkpoint SCN (recovery point)
SELECT checkpoint_change#,
TO_CHAR(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') as checkpoint_time
FROM v$database;
-- Datafile checkpoint SCN
SELECT file#, name, checkpoint_change#,
TO_CHAR(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') as checkpoint_time
FROM v$datafile
ORDER BY file#;
-- Convert SCN to timestamp
SELECT current_scn,
TO_CHAR(scn_to_timestamp(current_scn), 'YYYY-MM-DD HH24:MI:SS.FF6') as scn_timestamp
FROM v$database;
Monitoring Script
#!/bin/bash
# monitor_duplication.sh
while true; do
echo "=== $(date) ==="
# Check RMAN progress
tail -20 $LOG_FILE | grep -E 'channel|datafile|archive'
# Check archive log generation
sqlplus -s / as sysdba <<EOF
SELECT COUNT(*) as "Archive logs last hour"
FROM v\$archived_log
WHERE first_time > SYSDATE - 1/24;
exit
EOF
sleep 300 # Check every 5 minutes
done
Troubleshooting
Issue: ORA-01017 during duplication
Cause: Password file mismatch
Solution: Recreate password file on auxiliary with same password as source
Issue: Archive logs not found during OPEN RESETLOGS
Cause: Archive log location not accessible
Solution:
- Verify archive log destination parameter
- Check file permissions
- For different servers, ensure NFS mount or copy logs
Issue: Database opens but PDB not recovered
Cause: PDB archive logs not applied
Solution:
ALTER PLUGGABLE DATABASE pdb1 OPEN;
-- If fails, check alert log for specific archive log needed
Comparison with Alternatives
| Method | Downtime | Data Loss | Complexity | Cost |
|---|---|---|---|---|
| DUPLICATE + NOOPEN | 3-10 min | 0 | Low | EE/SE2 |
| Data Guard Switchover | <1 min | 0 | Medium | EE |
| Export/Import | Hours | NA | Low | EE/SE2 |
| Transportable Tablespaces | 10-30 min | NA | Medium | EE |
| GoldenGate | <1 min | 0 | High | OGG |
Conclusion
RMAN's DUPLICATE FROM ACTIVE DATABASE with NOOPEN provides a simple, effective solution for zero data loss migrations:
✓ Zero data loss - All committed transactions recovered
✓ Minimal downtime - 2-5 minutes regardless of database size
✓ Source stays online - No impact during duplication
✓ No additional licensing - Uses standard RMAN features
✓ Works across servers - With proper network configuration
The key is the NOOPEN clause, which keeps the database in MOUNT mode until your planned cutover time, allowing Oracle to automatically apply all remaining archive logs when you open it.
References
- Oracle Database Backup and Recovery User's Guide
- MOS Note 452868.1: How to Duplicate a Database Using RMAN
- MOS Note 1526162.1: RMAN DUPLICATE FROM ACTIVE DATABASE
Tested on Oracle Database 19c and 26ai. Procedure applies to Oracle 11g and above.
