Oracle Database Administration Training Series – Day 9
Database Refresh and Cloning
Database refresh and cloning are essential tasks for database administrators. They involve creating a
copy of an Oracle database, often for non-production purposes like development, testing, or
reporting.
In this session, we’ll focus on the concept of refreshing a non-production database and explore
RMAN cloning methods in detail.
1. What is Database Refresh and Cloning?
Database Refresh:
• A process of overwriting an existing non-production database (e.g., testing, development)
with the latest data from a production or another source database.
• Ensures the non-production database has up-to-date data for accurate testing,
troubleshooting, or other purposes.
Database Cloning:
• Involves creating an exact replica of a source database.
• Can be performed for both production and non-production purposes.
2. Why Refresh and Clone a Database?
1. Development and Testing:
o Developers often require current data to test new features or resolve issues.
2. Performance Tuning:
o A clone of the production database can be used to simulate and address
performance issues without affecting live operations.
3. Backup Validation:
o Cloning verifies the reliability of backups.
4. Reporting:
o Analysts may use a clone of the database to run intensive queries without impacting
production performance.
3. Refreshing a Non-Production Database
Steps to Refresh a Non-Production Database
Scenario: Refreshing a test database with data from the production database.
1
1. Backup the Source (Production) Database:
o Use RMAN to take a consistent backup of the production database:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
2. Transfer Backup Files to the Target Server:
o Copy the backup sets and archive logs to the server where the non-production
database resides using tools like SCP, FTP, or Oracle's DBMS_FILE_TRANSFER.
3. Prepare the Target Environment:
o Ensure the non-production database instance is stopped.
o Delete or archive the existing database files.
4. Restore the Database Using RMAN:
o Start RMAN and connect to the target database in NOMOUNT mode:
RMAN> CONNECT TARGET /;
RMAN> STARTUP NOMOUNT;
o Restore the control file:
RMAN> RESTORE CONTROLFILE FROM '/path/to/controlfile.bkp';
RMAN> ALTER DATABASE MOUNT;
o Restore the database:
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
5. Open the Database:
o Open the database in RESETLOGS mode:
RMAN> ALTER DATABASE OPEN RESETLOGS;
6. Post-Refresh Tasks:
o Update database-specific parameters (e.g., db_name, db_unique_name).
o Update application connections to point to the refreshed database.
4. RMAN Cloning Methods
Oracle Recovery Manager (RMAN) supports multiple cloning techniques. These methods help create
a copy of a database efficiently.
a. RMAN Duplicate Command
The RMAN DUPLICATE command creates a duplicate (clone) of the source database. It can clone a
database on the same server or a different server.
2
Steps for RMAN Duplicate:
1. Prepare the Target Environment:
o Install the Oracle software on the target server.
o Configure the necessary parameter files (e.g., init.ora) and listener.
2. Backup the Source Database:
o Take an RMAN backup of the source database.
3. Transfer Backup Files to the Target Server:
o Move the backup files to the target server.
4. Connect to RMAN:
o Connect to the source database, auxiliary database (target), and optional RMAN
catalog:
RMAN> CONNECT TARGET sys@prod_db;
RMAN> CONNECT AUXILIARY sys@test_db;
5. Run the Duplicate Command:
o Use the following command to clone the database:
RMAN> DUPLICATE TARGET DATABASE TO test_db;
o This duplicates the source database to the target environment.
6. Open the Cloned Database:
o The DUPLICATE command automatically performs recovery and opens the cloned
database.
b. Active Database Duplication
Active duplication eliminates the need for a backup. The source database sends its data files directly
to the target database using an RMAN channel.
Steps for Active Duplication:
1. Prepare the Target Environment:
o Install Oracle software on the target server.
o Create an NOMOUNT instance for the auxiliary database.
2. Establish Network Connectivity:
o Ensure the source and target databases can communicate using Oracle Net.
3. Run the RMAN Duplicate Command:
o Connect to the source and auxiliary databases and run:
3
RMAN> CONNECT TARGET sys@prod_db;
RMAN> CONNECT AUXILIARY sys@test_db;
RMAN> DUPLICATE TARGET DATABASE TO test_db FROM ACTIVE DATABASE;
o This copies the data files directly from the source to the target.
c. Cloning with Storage-Level Snapshots
Many organizations use storage-level snapshots for cloning databases. While this method does not
directly involve RMAN, it is an effective alternative for environments using storage systems like
NetApp, Dell EMC, or Oracle ZFS.
5. Real-World Example: MODI Airline Ticketing System
In an MODI airline ticketing system, database refresh and cloning are frequently used for testing and
development:
1. Refreshing the Testing Database:
o Developers may need a refreshed copy of the production database to test new
booking features or pricing algorithms.
o The testing database is overwritten with the latest production data using the RMAN
refresh process.
2. Cloning for Reporting:
o A cloned database is set up to run intensive analytical queries on passenger travel
patterns without affecting the live booking database.
3. Backup Validation:
o A clone of the production database is created to validate the integrity of backups by
performing test restores.
6. Best Practices for Database Refresh and Cloning
• Plan the Refresh Window:
o Perform refreshes or clones during low-usage periods to minimize disruptions.
• Mask Sensitive Data:
o Ensure sensitive data (e.g., customer details) is masked or anonymized in non-
production environments.
• Use FRA for Backup Files:
o Keep backup files in the Fast Recovery Area (FRA) for quick access during cloning or
refresh operations.
4
• Automate the Process:
o Use scripts to automate repetitive steps for faster and consistent refreshes.
• Test the Cloning Process:
o Regularly test the cloning process to ensure backups are usable and procedures are
up-to-date.
Summary
Database refresh and cloning are critical activities for maintaining an efficient and reliable database
environment. RMAN offers powerful tools for both tasks, allowing DBAs to create consistent and up-
to-date database copies. By understanding and implementing these processes effectively, you can
support development, testing, and reporting needs while ensuring the integrity and availability of
your data.
