Duplicating an Oracle database is an extremely useful feature for development, verification, and testing purposes. In this article, we will practically and thoroughly explain the procedure for duplicating a target database from the source database’s backups using RMAN (Recovery Manager). This method uses the net service name approach via tnsnames.ora.
■ Target Audience
- Oracle beginners
- DB Administrators looking to build a test environment
- Those who want to understand the big picture of
duplicate database
■ Benefits of RMAN Duplication
Database duplication via RMAN is highly effective in the following scenarios:
- Building development and testing environments
- Troubleshooting and reproducing bugs for verification
- Preparing environments for education and training
- Providing production data after masking sensitive parts
Using the duplicate command allows you to create a complete clone—including data files, control files, and archive logs—under a different SID.
■ Environment Configuration
[Source Database]
- SID:
v19
[Target (Destination) Database]
- SID:
orcl
■ Source Side: Taking a Backup
First, take the backup necessary for duplication. Ensure that ARCHIVELOG mode is enabled.
$ export ORACLE_SID=v19
$ rman target /
RMAN> backup database plus archivelog;
This command takes a full backup and includes the archive logs, preparing the resources required for the duplication process.
■ Target Side: Environment Preparation Steps
1. Create the Initialization Parameter File (PFILE)
$ vi /var/tmp/init.ora
DB_NAME=orcl
2. Set Environment Variables
$ export ORACLE_SID=orcl
3. Create a Password File
$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10 format=12
4. Static Configuration of listener.ora
$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.50 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)))
5. Start the Listener
$ lsnrctl start
6. Set tnsnames.ora (Define net service name)
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)))
7. Start the Instance (nomount)
$ sqlplus / as sysdba
SQL> startup nomount pfile=/var/tmp/init.ora
Terminal Execution Log:
[oracle@v19single ~]$ vi /var/tmp/init.ora
[oracle@v19single ~]$ cat /var/tmp/init.ora
DB_NAME=orcl
[oracle@v19single ~]$ export ORACLE_SID=orcl
[oracle@v19single ~]$ echo $ORACLE_SID
orcl
[oracle@v19single ~]$ vi $ORACLE_HOME/network/admin/listener.ora
[oracle@v19single ~]$ lsnrctl start
...
The command completed successfully.
[oracle@v19single ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=10 format=12
[oracle@v19single ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/var/tmp/init.ora
ORACLE instance started.
...
SQL> exit
■ Executing Duplication with RMAN
1. Start RMAN and Connect to Source DB
$ export ORACLE_SID=v19
$ rman target /
2. Connect to Target DB (Auxiliary) using Net Service Name
RMAN> connect auxiliary sys/oracle@ORCL
3. Execute the duplicate Command
RMAN> duplicate target database to orcl
spfile
parameter_value_convert 'V19','ORCL'
set db_file_name_convert='/u01/app/oracle/oradata/V19','/u01/app/oracle/oradata/ORCL'
set log_file_name_convert='/u01/app/oracle/oradata/V19','/u01/app/oracle/oradata/ORCL'
set control_files='/u01/app/oracle/oradata/ORCL/control01.ctl'
nofilenamecheck;
💡 nofilenamecheck is an option that forces execution even if the source and target file paths appear to overlap.
RMAN Execution Log Summary:
RMAN> duplicate target database to orcl
...
Starting Duplicate Db at 25-03-30
...
contents of Memory Script:
{
restore clone spfile to ...
shutdown clone immediate;
startup clone nomount;
}
...
Starting restore at 25-03-30
channel ORA_AUX_DISK_1: restoring control file
...
Starting restore at 25-03-30
channel ORA_AUX_DISK_1: restoring datafile ...
...
Starting recover at 25-03-30
Media recovery complete, elapsed time: 00:00:00
...
database opened
Finished Duplicate Db at 25-03-30
■ Main Flow of the Duplication Process (Diagram)
- Source DB (v19) connects to RMAN as
target. - RMAN connects to Target DB (orcl) as
auxiliary. - SPFILE Restore: RMAN restores the SPFILE and adjusts initial parameters.
- Nomount Startup: Target instance starts in NOMOUNT.
- Control File Restore/Mount: Control files are restored and the database is mounted.
- Data File Restore: Physical data files are restored to target locations.
- Recovery: Archive logs are applied to synchronize data.
- Open Resetlogs: Database is opened with
RESETLOGS.
■ Verification After Duplication
$ sqlplus / as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
------------- -------
orcl OPEN
■ Summary
RMAN’s duplicate feature is a powerful tool capable of creating a complete copy of a database under a different SID based on backups. The steps introduced here represent the basic pattern for a minimal configuration, but this can be applied to using a recovery catalog or creating standby databases in production environments.
By using net service names (tnsnames.ora), duplicating across networks or deploying to different hosts becomes significantly easier.
[reference]
Duplicating Databases
コメント