Post Contents

Oracle 19c Duplicate a Database

Oracle 19c Duplicate a Database

Duplicating a database in Oracle is an essential task for database administrators. It enables efficient Database Duplication and Database Cloning, which are crucial for various purposes such as testing, development, and disaster recovery. This guide will focus on physical duplication techniques, including Duplicate Database backupset, duplication through Oracle Net, duplication without connecting to the target, and using backups.

 

Understanding Database Duplication

Database Duplication involves creating a physical copy of an existing database within the same or a different environment. This process is essential for Database Cloning, which allows DBAs to create multiple instances of a database environment for different uses without affecting the original database.

Benefits of Database Cloning

  1. Testing and Development: Cloning a database provides a separate environment for testing and development without impacting the production environment.
  2. Disaster Recovery: Duplicates of critical databases ensure quick data restoration in case of failures.
  3. Load Balancing: Distributing workloads across multiple databases enhances performance and resource management.

 

Duplicate Database Backupset

Considerations

  1. The clause FOR STANDBY duplicates the database with the same DBID.
  2. The auxiliary instance is the database instance associated with Duplicate Database.
  3. Every duplication technique requires the connection of the source instance to the auxiliary instance.
  4. Pull (restore) is based on backupsets.
  5. Push is based on image copies.

Duplicating Database – Method “Pull”

Considerations:

  1. Based on backupsets.
  2. The restore operation is executed from the auxiliary instance.
  3. Requires TNS connections on the target and auxiliary.
  4. The Pull method, available from 12c, pulls the datafiles as backupsets (skips unused blocks) from the source database, making it faster than the Push method (older versions) which uses Image Copies.

📢 You might also like: Oracle 19c Upgrading and Transporting CDBs and PDBs (Category: Oracle Database Admin)

Steps to Duplicate Database using Backupset

  1. Copy the password file to the other server (or rename if local):
scp $ORACLE_HOME/dbs/orapwsource oracle@remote_server:/home/oracle/duplicate
mv orapwsource orapwclone
  1. Create a pfile from the spfile on the source:
SQL> create pfile='/home/oracle/initsource.ora' from spfile;
scp /home/oracle/initsource.ora oracle@remote_server:/home/oracle/duplicate
  1. Create directories on the target:
mkdir -p /home/oracle/duplicate/datafile
mkdir -p /home/oracle/duplicate/adump
mkdir -p /home/oracle/duplicate/redo
mkdir -p /home/oracle/duplicate/controlfile
mkdir -p /home/oracle/duplicate/FRA
mkdir -p /home/oracle/duplicate/cdump
  1. Edit the pfile:
vi /home/oracle/duplicate/initsource.ora

*.audit_file_dest='/home/oracle/duplicate/adump'
*.db_file_name_convert='/u01/oradata/datafile','/home/oracle/duplicate/datafile'
*.log_file_name_convert='/u01/oradata/redo','/home/oracle/duplicate/redo'
*.control_files='/home/oracle/duplicate/controlfile/control01.ctl','/home/oracle/duplicate/controlfile/control02.ctl'
*.db_name='clone'
*.db_recovery_file_dest='/home/oracle/duplicate/FRA'
*.core_dump_dest='/home/oracle/duplicate/cdump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clone)'
  1. Set environment variables for the CLONE database:
vi /etc/oratab --> CLONE:/u01/app/oracle/product/12.1.0/dbhome_1:N
. oraenv (CLONE)
  1. Start the CLONE instance in nomount:
startup nomount pfile='/home/oracle/duplicate/initsource.ora';
  1. Configure TNS and Listener on both servers:
cd $ORACLE_HOME/network/admin

vi listener.ora
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remote_server)(PORT = 1521))
  )

vi tnsnames.ora
SOURCE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = source_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = source_service)
    )
  )

CLONE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remote_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clone_service)
    )
  )
  1. Connect to the auxiliary and target instance from the auxiliary database:
rman target sys/password@source_db auxiliary sys/password@clone_db
  1. Run the Duplicate command:
RMAN> DUPLICATE TARGET DATABASE TO clone FROM ACTIVE DATABASE
      USING BACKUPSET
      SECTION SIZE 1G
      USING COMPRESSED BACKUPSET;

or

RMAN> DUPLICATE TARGET DATABASE TO clone;

 

Duplicate Database through Oracle Net

Considerations

  1. With the clause FOR STANDBY, the database is duplicated with the same DBID.
  2. The database instance associated with Duplicate Database is called an auxiliary instance.
  3. Every duplication technique requires the connection of the source instance to the auxiliary instance.
  4. Pull (restore) is based on backupsets.
  5. Push is based on image copies.

 

Duplicating Active Database – Method “Push”

Considerations:

  1. Does not need or use existing backups for the operation.
  2. Image copies are created by the source DB and transmitted directly via Oracle Net when used FROM ACTIVE DATABASE.
  3. Datafile, controlfile, spfile, and archive log copies are copied from the source to the auxiliary instance.
  4. This method of Active Database Duplication is referred to as “Push”.
  5. NOFILENAMECHECK: If you want the duplicate filenames to be the same as the target filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK.

Steps to Duplicate Database through Oracle Net

  1. Copy the password file to the other server (or rename if local):
scp $ORACLE_HOME/dbs/orapwsource oracle@remote_server:/home/oracle/duplicate
mv orapwsource orapwclone
  1. Create a pfile from the spfile on the source:
SQL> create pfile='/home/oracle/initsource.ora' from spfile;
scp /home/oracle/initsource.ora oracle@remote_server:/home/oracle/duplicate
  1. Create directories on the target:
mkdir -p /home/oracle/duplicate/datafile
mkdir -p /home/oracle/duplicate/adump
mkdir -p /home/oracle/duplicate/redo
mkdir -p /home/oracle/duplicate/controlfile
mkdir -p /home/oracle/duplicate/FRA
mkdir -p /home/oracle/duplicate/cdump
  1. Edit the pfile:
vi /home/oracle/duplicate/initsource.ora

*.audit_file_dest='/home/oracle/duplicate/adump'
*.db_file_name_convert='/u01/oradata/datafile','/home/oracle/duplicate/datafile'
*.log_file_name_convert='/u01/oradata/redo','/home/oracle/duplicate/redo'
*.control_files='/home/oracle/duplicate/controlfile/control01.ctl','/home/oracle/duplicate/controlfile/control02.ctl'
*.db_name='clone'
*.db_recovery_file_dest='/home/oracle/duplicate/FRA'
*.core_dump_dest='/home/oracle/duplicate/cdump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clone)'
  1. Set environment variables for the CLONE database:
vi /etc/oratab --> CLONE:/u01/app/oracle/product/12.1.0/dbhome_1:N
. oraenv (CLONE)
  1. Start the CLONE instance in nomount:
startup nomount pfile='/home/oracle/duplicate/initsource.ora';
  1. Configure TNS and Listener on both servers:
cd $ORACLE_HOME/network/admin

vi listener.ora
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remote_server)(PORT = 1521))
  )

vi tnsnames.ora
SOURCE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = source_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = source_service)
    )
  )

CLONE_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remote_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clone_service)
    )
  )
  1. Connect to the auxiliary and target instance from the auxiliary database:
rman target sys/password@source_db auxiliary sys/password@clone_db
  1. Run the Duplicate command:
RMAN> DUPLICATE TARGET DATABASE TO clone FROM ACTIVE DATABASE;

 

Duplicate Database without Connecting to Target

Steps

  1. Create a backup on the source server, including archivelogs:
run
{
  allocate channel ch1 device type disk format '/path/to/rman/db_%U.bkp';
  backup database plus archivelog;
  copy current controlfile to '/path/to/rman/control.ctl';
}
  1. Create directories on the auxiliary server:
mkdir -p /path/to/rman
mkdir -p /path/to/duplicate/datafile
mkdir -p /path/to/duplicate/adump
mkdir -p /path/to/duplicate/redo
mkdir -p /path/to/duplicate/controlfile
mkdir -p /path/to/duplicate/FRA
mkdir -p /path/to/duplicate/cdump
  1. Copy backup files to the auxiliary server:
scp /path/to/rman/* oracle@auxiliary_server:/path/to/rman
  1. Create a parameter file on the auxiliary server:
vi /path/to/rman/initclone.ora

*.audit_file_dest='/path/to/duplicate/adump'
*.db_file_name_convert='/u01/oradata/datafile','/path/to/duplicate/datafile'
*.log_file_name_convert='/u01/oradata/redo','/path/to/duplicate/redo'
*.control_files='/path/to/duplicate/controlfile/control01.ctl','/path/to/duplicate/controlfile/control02.ctl'
*.db_name='clone'
*.db_recovery_file_dest='/path/to/duplicate/FRA'
*.core_dump_dest='/path/to/duplicate/cdump'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clone)'
  1. Start the instance:
. oraenv (CLONE)
startup nomount pfile='/path/to/rman/initclone.ora';
  1. Connect to the auxiliary instance with RMAN and start cloning the database:
rman auxiliary sys/password

RMAN> run {
  allocate auxiliary channel aux1 device type disk;
  duplicate database to 'clone'
    backup location '/path/to/rman'
    noopen;
}

 

Verifying the Duplication

After completing the Duplicate Database or Clone Database, it is essential to verify that the new database is operational and consistent. Use the following queries to check the status:

Check Database Status

SELECT NAME, OPEN_MODE FROM V$DATABASE WHERE NAME = 'CLONE';

Validate Data Integrity

Perform a quick validation to ensure that the data within the new database is consistent and intact.

DBMS_UTILITY.DB_VERIFY(start_block => 1, end_block => DBMS_UTILITY.MAX_BLOCK);

 

Managing Duplicated and Cloned Databases

Effective management of duplicated and cloned databases involves regular monitoring, maintenance, and applying necessary updates. Oracle Enterprise Manager (OEM) monitors the performance and status of the databases.

Monitoring Databases

Use Oracle Enterprise Manager to monitor the resource usage, performance metrics, and overall health of the databases.

Applying Updates and Patches

Keep the duplicated and cloned databases up to date with the latest patches and updates to maintain security and performance.

 

Conclusion

Duplicating a database in Oracle 19c is a powerful feature that enhances the flexibility and manageability of database environments. By following the steps outlined in this guide, DBAs can efficiently perform Duplicate Database and Database Copy, ensuring that their databases are robust, scalable, and prepared for various scenarios.

Start leveraging Database Cloning in your Oracle environment today to enhance your database management capabilities and ensure your data remains protected and easily recoverable.

 

See more on Oracle’s website!

Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top