In this post I will explain how to refresh test dev database from production backup. For “oracle refresh test database from production” we will prefer from valid production rman backup.
How to refresh test dev database?
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 restore/clone database using rman to restore the production database.
SERVER1—PROD
SERVER2—TEST
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;
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; }
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;
To change DB name, follow the below steps: –
To change the database name in addition to the DBID, specify the DBNAME parameter. This example
changes the name to test_db:
Ensure that the target database is mounted but not open, and that it was shut down consistently prior
to mounting. For example:
Before executing the nid command, ensure that redo log files and Temp files renamed accordingly.
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
$nid TARGET=SYS/<password> DBNAME=test_db
SQL>ALTER DATABASE OPEN RESETLOGS;
After that modify the parameter file as db_name.
Thanks for your information sharing,your site helps to many people.
Oracle Apps DBA Training in Hyderabad
Thanks for sharing this post. Your post is really very helpful its students.
SQL server dba Online Training
Very useful post.
Really good article.
Thanks its helpfull
I am new to Oracle and hard to find any proper document to do refresh from prod to test with proper steps. Same with this one.
Some of your lines doesnt match like below. where did you get nsndb?
–Take backup of PROD with archivelog,controlfile and spfile. (why not put script to do all backup)
–
ON SERVER2:
–Create pfile for database nsndb.
For more details on RMAN Backup . Pls check another post https://sajidoracledba.blogspot.com/2017/09/how-to-automate-schedule-shell-script-for-rman-backup-in-oracle-database-through-crontab.html.
RUN
{
sql 'alter system archive log current';
BACKUP AS compressed backupset filesperset 8 DATABASE FORMAT '$destnation/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 FORMAT '$destnation/ARCBAK_%d_%p_%T_%s.rbkp' TAG ARCHIVE_BKP;
BACKUP AS compressed backupset CURRENT CONTROLFILE FORMAT '$destnation/CNT_%d_%p_%T_%s.rbkp' TAG CONTROL_FILE;
CROSSCHECK COPY;
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE ARCHIVELOG UNTIL TIME 'sysdate-4' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
}
Include in run block for spfile.
Backup spfile FORMAT '/Backup01/SPFILE_%d_%p_%T_%s.rbkp';