Refresh TEST DEV Database from Production RMAN backup

In this post I will explain how to refresh test dev database from production RMAN backup. To “refresh database from rman backup” we will prefer from valid production rman backup. oracle database refresh from production to test involves restoring the database backup from the production environment to the test/development environment. This process should be executed carefully to avoid data loss or disruption in the production environment.

How to refresh test dev database?

Before you start oracle database refresh from production to development using rman, several preparations are crucial for oracle database refresh steps:

Ensure you have a recent and valid RMAN backup of the production database. This backup should include all necessary database files and control files.

Identify the specific backup set or image copy that you’ll be using to restore the test/dev database. Make sure you have enough storage space available in the test/dev environment to accommodate the restored database files.

Here we have taken valid RMAN backup of PROD and want to restore the backup on TEST/DEV Instance as below. We will ensure the backup of data file, spfile, archive log and control file using rman.

For 12c: Multitenant -Backup and Recovery of Container Database (CDB) and Pluggable Database (PDB) you may check, click here.

Let’s take two servers as below to refresh database using rman to restore the production database.

1. SERVER1—PROD
2. SERVER2—TES
   
   

ON SERVER1:

DB Name: PROD

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data03/PROD/datafiles/system01.dbf
/data03/PROD/datafiles/system02.dbf
/data03/PROD/datafiles/system03.dbf
/data03/PROD/datafiles/system04.dbf
/data03/PROD/datafiles/system05.dbf
/data04/PROD/datafiles/ctxd01.dbf
/data04/PROD/datafiles/owad01.dbf
/data04/PROD/datafiles/a_queue02.dbf
/data04/PROD/datafiles/odm.dbf
/data04/PROD/datafiles/olap.dbf
/data04/PROD/datafiles/sysaux01.dbf
/data04/PROD/datafiles/apps_ts_tools01.dbf
/data04/PROD/datafiles/system122.dbf
/data04/PROD/datafiles/a_txn_data04.dbf
/data04/PROD/datafiles/a_txn_ind06.dbf
/data04/PROD/datafiles/a_ref03.dbf
/data04/PROD/datafiles/a_int02.dbf
/data04/PROD/datafiles/sysaux02.dbf
/data04/PROD/datafiles/olap01.dbf
/data04/PROD/datafiles/a_txn_data05.dbf
/data04/PROD/datafiles/a_txn_data06.dbf
/data04/PROD/datafiles/a_txn_ind07.dbf

–>Initiate backup of PROD with archivelog, controlfile and spfile.

===> For more details related to RMAN Backup scripts go through.

let’s see ‘how to refresh database in sql server using query’.

RUN
{
    sql 'alter system archive log current';
    BACKUP AS compressed backupset filesperset 8 DATABASE FORMAT '/Backup01/DB_%d_%p_%T_%s.rbkp' TAG DAILY_HOT_BACKUP;


    sql 'alter system archive log current';
    BACKUP AS compressed backupset ARCHIVELOG ALL NOT BACKED UP 1 TIMES  ORMAT '/Backup01/ARCBAK_%d_%p_%T_%s.rbkp' TAG       ARCHIVE_BKP;


   BACKUP AS compressed backupset CURRENT CONTROLFILE FORMAT '/Backup01/CNT_%d_%p_%T_%s.rbkp' TAG CONTROL_FILE.


   Backup spfile FORMAT '/Backup01/SPFILE_%d_%p_%T_%s.rbkp';


};

–Copy all backupsets from SERVER1(Location: /data1/bak/nsndb/) to SERVER2(Location:/u01/backup/nsndb/)

ON SERVER2:

–Create pfile for database TEST.


DB Name: TEST

$export ORACLE_SID=TEST

$rman target /


RMAN>startup nomount;

RMAN>restore controlfile from '/u01/backup/nsndb/backup_db_c-300166359-20120316-00';

RMAN>alter database mount;

Since backup location on SERVER2 is different from SERVER1, so we have to use catalog command to update new backup location in controlfile.

Use below command for all backuppieces:

RMAN> CATALOG START WITH '/my/backup/location';

Since datafile location on SERVER2 is different from SERVER1, so we have to update controlfile for same with SET NEWNAME clause:  Now we are going to restore database on another server.

Now restore database backup on SERVER2:-

RMAN>
run
{
set newname for datafile '/data02/oradata/nsndb/system01.dbf' to '/u01/oradata/nsnbak/system01.dbf';
set newname for datafile '/data02/oradata/nsndb/undotbs01.dbf' to '/u01/oradata/nsnbak/undotbs1.dbf';
set newname for datafile '/data02/oradata/nsndb/sysaux01.dbf' to '/u01/oradata/nsnbak/sysaux01.dbf';
set newname for datafile '/data02/oradata/nsndb/users01.dbf' to '/u01/oradata/nsnbak/users01.dbf';
restore database;
switch datafile all;
}
RMAN>exit;

To restore Oracle database using duplicate command, just check another post here.

–Also update redo log file in controlfile on SERVER2:

$sqlplus "/as sysdba"
SQL>alter database rename file '/data02/oradata/nsndb/redo07.log' to '/u01/oradata/nsnbak/redo07.log';
SQL>alter database rename file '/data02/oradata/nsndb/redo08.log' to '/u01/oradata/nsnbak/redo08.log';
SQL>alter database rename file '/data02/oradata/nsndb/redo09.log' to '/u01/oradata/nsnbak/redo09.log';
SQL>exit;
$rman target /
RMAN>
run
{
recover database;
alter database open resetlogs; }
RMAN>exit;
$sqlplus "/as sysdba"


SQL>alter database tempfile '/u01/home/oracle/PROD/tempfiles/temp01_PROD.dbf' drop including datafiles;


sql>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/nsnbak/temp01.dbf' size 200m reuse/size 500M;


sql>select name from v$tempfile;