Create Duplicate Or Refresh the Database Using RMAN Backup on RAC

Create duplicate database or refresh the database from production backup for testing purpose is one of the regular activities for DBA. I will discuss and show how to create duplicate database using RMAN backup.

Scenario: Production database running on two nodes RAC and need to refresh the database for testing purpose on testing environment.

  • Production DB Name: orclp
  • Production Nodes: racprod1, racprod2
  • QA DB Name: orclq
  • Test DB Nodes: ractest1, ractest2

Steps to perform refresh or duplicate database:

Assumed, we have an database on testing environment with name orclq. But if you need to create database with new name (name is not exist) then don’t need to perform below some steps.

Step 1: Cold backup the current database (orclq) before dropping.

This is always good practices to have backup. requirement may change any time, management may ask you to roll back (go back old database). As a DBA, always commandeered to take backup even for lower environment if have option.

Shutdown the database and start with mount mode:

# On Testing Server:
$ srvctl stop database -d orclq
$ srvctl start database -d orclq -o mount
# Make sure that database is mount stage
$ srvctl stats res -t

Trigger the backup and monitor the logs. You may use below rman script to take backup: based on your system configuration, you may chose the number of channel.

allocate channel ch01 type disk format '/backup/backups/orclq/ch01_orclq_fullbackup.rman' ;
allocate channel ch02 type disk format '/backup/backups/orclq/ch02_orclq_fullbackup.rman' ;
allocate channel ch03 type disk format '/backup/backups/orclq/ch03_orclq_fullbackup.rman' ;
allocate channel ch04 type disk format '/backup/backups/orclq/ch04_orclq_fullbackup.rman' ;
release channel ch01;
release channel ch02;
release channel ch03;
release channel ch04;

Step 2: Cope the backup of production database server (racprod1) to testing database server (ractest1).

Note:::It may take longer time depend on network speed between data center and database size. so plan accordingly.

Assume database backup location on production is /backup/rman_backup/orclp and on test server path is /backup/rman_backup/orclq

Note::: If backup size is huge then use run as nohup command or cron job

# On Production Server:
$ cd /backup/rman_backup/orclp
$ scp /backup/rman_backup/orclp/* oracle@ractest1:/backup/rman_backup/orclq/fullbakcup

Step 3: Create a pfile from spfile for orclq

SQL> create pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora' from spfile; 

Step 4: Drop the test database after successful backedup (if in case need to restore old one)

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';
$ srvctl stop database –d orclq
SQL> startup mount exclusive restrict;
SQL> select instance_name,status,logins from v$Instance;
SQL> drop database;
# To remove the configuration from OCR on RAC
$ srvctl remove database -db orclq

Step 4: Create pfile for new DB (orclq) and change required parameters.

Create pfile from production DB – orclp and move to test db server.

Note:::If Step 4 is applicable for your requirement then you may use pfile which has been created on Step 3.

On production database server: (orclp)
SQL> create pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora' from spfile; 
$ scp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora oracle@ractest1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
orclq1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orclq2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.cluster_database=false #change after restore
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclqXDB)'

Step 5: Start database in one node with mount mode:

oracle@ractest1 $ export ORACLE_SID=orclq
oracle@ractest1 $ sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora';
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2226712 bytes
Variable Size             889193960 bytes
Database Buffers          620756992 bytes
Redo Buffers                7720960 bytes

SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora';
File created.

Step 6: Catalog the backup.

If you have multiple directories then include all path into catalog.

oracle@ractest1: $ rman target /
$ RMAN> Catalog start with backup/rman_backup/orclq/fullbackup/
$ RMAN> Catalog start with backup/rman_backup/orclq/archivelog_backukp

Step 7: Start Duplicate command:

rman auxiliary / log=/backup/rman_restore_orclq.log
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
allocate auxiliary channel c3 device type disk;
duplicate target database to orclq
backup location '/backup/rman_backup/orclq/fullbackup' nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
oracle@ractest1: $ . oraenv
ORACLE_SID = [orclq] ? oraclq1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@ractest1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release Production on Sun Dec 15 20:27:54 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orcl_pfile.ora';
ORACLE instance started.

Total System Global Area  297795584 bytes
Fixed Size                  2923632 bytes
Variable Size             239076240 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.
Database opened.

SQL> create spfile='+DATA/ORCLQ/PARAMETERFILE/spfileoraclq.ora' from  pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/pfile_orclq.ora';

Step 8: Add database information to OCR

oracle@ractest1: srvctl add database -dbname orclq -d orclq -o /u01/app/oracle/product/12.1.0/dbhome_1  -p +DATA/ORCLQ/PARAMETERFILE/spfileorclq.ora -r PRIMARY -a OCR,DATA,FRA -s open -t immediate -c RAC
oracle@ractest1: srvctl add instance -d orclq -i orclq1 -n ractest1
oracle@ractest1: srvctl add instance -d orclq -i orclq2 -n ractest2

Step 9: Validation:

oracle@ractest1: srvctl stop database -d orclq
oracle@ractest1: export ORACLE_SID=+ASM1
oracle@ractest1: crsctl stat res -t