Series: Duplicating an Oracle Database – Part 1: Creating a New Instance on the Destination Server

When working with Oracle databases, there are times when you might need to duplicate a database to a different server for testing, backup, or development purposes. In this three-part series, we will walk through the process of creating a duplicate Oracle database. This first article focuses on setting up the necessary instance on the destination server, preparing the environment for the database duplication.

Step 1: Create a Parameter File (PFILE) on the Source Server

The first step is to create a PFILE from the SPFILE of the production database on the source machine.

SQL> create pfile from spfile;

This PFILE will be modified later for use on the destination server.

Step 2: Copy the PFILE to the Destination Server

Next, copy the PFILE you created on the source server to the destination server using scp:

cd /u01/app/oracle/product/19.0.0/db_1/dbs
scp initorcl.ora oracle@192.168.1.64:/u01/app/oracle/product/19.0.0/db_1/dbs

Step 3: Create Necessary Directories on the Destination Server

On the destination server, create the appropriate directories for the new instance.

mkdir -p /u01/app/oracle/admin/test/adump
mkdir -p /u01/app/oracle/oradata/test

These directories will be used to store the logs and data files of the new instance.

Step 4: Adjust the Parameter File (PFILE) on the Destination Server

You now need to edit the PFILE on the destination server to ensure that it reflects the new instance name and file locations. Adjust the following parameters as needed:

*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.db_block_size=8192 
*.compatible='19.0.0.0.0'
*.db_domain='' 
*.db_name='orcl' 
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 
*.db_recovery_file_dest_size=4385144832 
*.diagnostic_dest='/u01/app/oracle' 
*.memory_target=300M
*.open_cursors=300 
*.processes=300 
*.remote_login_passwordfile='EXCLUSIVE' 
*.undo_tablespace='UNDOTBS1' 
*.db_file_name_convert =('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/test/') 
*.log_file_name_convert =('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/test/')

Step 5: Copy the Password File to the Destination Server

On the source server, copy the password file to the destination server.

cd /u01/app/oracle/product/19.0.0/db_1/dbs
scp orapworcl oracle@192.168.1.64:/u01/app/oracle/product/19.0.0/db_1/dbs

Step 6: Start the Instance in NOMOUNT Mode on the Destination Server

Finally, start the Oracle instance on the destination server in NOMOUNT mode using the PFILE you just modified:

sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/db_1/dbs/initorcl.ora';

This sets up the environment for the database duplication, which we will cover in the next part of the series.

Conclusion

In this first part of the series, we have covered how to set up a new Oracle instance on a destination server, preparing it for the next steps of the database duplication process. Stay tuned for the second part, where we will perform the actual database duplication using RMAN.

---