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.
run
{
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' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
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
orclq1.__pga_aggregate_target=3187671040
orclq2.__pga_aggregate_target=3959422976
orclq1.__sga_target=5939134464
orclq2.__sga_target=5502926848
*.audit_file_dest='/u01/app/oracle/admin/orclq/adump'
*.audit_trail='db'
*.cluster_database=false #change after restore
*.compatible='12.1.0.2'
*.control_files='+FRA/orclq/controlfile/current.284.094896199'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+FRA'
*.db_domain=''
*.db_name='orclq'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=104857600000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclqXDB)'
orclq2.instance_number=2
orclq1.instance_number=1
1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ractest1-vip.localhost.com) (PORT=1521))))'
2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ractest2-vip.localhost.com)(PORT=1521))))'
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ractest2-vip.localhost.com)(PORT=1521))))'
orclq2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ractest2-vip.localhost.com)(PORT=1521))))'
orclq1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ractest1-vip.localhost.com)(PORT=1521))))'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_max_target=9122611200
*.memory_target=9122611200
*.open_cursors=300
*.processes=500
*.remote_listener='orclq.localhost.com:1535'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sessions=555
*.sga_max_size=9122611200
orclq2.thread=2
orclq1.thread=1
orclq1.undo_tablespace='UNDOTBS1'
orclq2.undo_tablespace='UNDOTBS2'
#db_file_name_convert=('+DATA/orclp','+DATA/orclq')
#log_file_name_convert=('+DATA/orclp/onlinelog','+DATA/orclq/onlinelog','+FRA/orclp/onlinelog','+FRA/orclq/onlinelog')
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
run
{
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 12.1.0.2.0 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
