Oracle database conventional HOT Refresh

Hot Refresh Process (ACTUAL):



In this note we often refer Source as Production server and Target as Test Server


1. Take Hot Backup of the datafiles on the Production(Source)database -- To know how to get Hot Backups refer my post: How to take Hot Backups without RMAN?


2. Copy over the datafiles using SCP or SFTP.

On Production database : Prepare the Control file script:
SQL> alter database backup controlfile to trace as '.sql’
Copy the control file script to the destination server.


4. After the applications are shutdown, bring down the database on Test(Target).
Sqlplus ‘/ as sysdba’
Shutdown immediate;
Make a back of the existing datafiles if needed(Based on disk space)


Please be sure of the space on server,
5. No need to create a new init.ora file the old one will do; Delete the old Controlfiles(get the location of control files from initSID.ora)

7. Edit the Controlfile script that was copied from production – make sure you do necessary changes regarding the location of datafiles and redo logs(marked BOLD below are the ones that needs to be changed, change REUSE to SET and set SID to TEST, change NORESETLOGS to RESETLOGS and Change it to NOARCHIVELOG for time being)
After you edit the script it should look something as below:

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 9983
LOGFILE
GROUP 11 '/home/oradata/TEST/redo/redo11.rdo' SIZE 1000M,
GROUP 12 '/home/oradata/TEST/redo/redo12.rdo' SIZE 1000M,
GROUP 13 '/home/oradata/TEST/redo/redo13.rdo' SIZE 1000M,
GROUP 14 '/home/oradata/TEST/redo/redo14.rdo' SIZE 1000M
DATAFILE
'/home/oradata/TEST/data/system01.dbf',
'/home/oradata/TEST/data/undotbs01.dbf',
'/home/oradata/TEST/data/cwmlite01.dbf',
'/home/oradata/TEST/data/drsys01.dbf',
'/home/oradata/TEST/data/example01.dbf',
'/home/oradata/TEST/data/indx01.dbf',
'/home/oradata/TEST/data/odm01.dbf',
'/home/oradata/TEST/data/tools01.dbf',
'/home/oradata/TEST/data/data_ts07.dbf',
'/home/oradata/TEST/data/xdb01.dbf',
'/home/oradata/TEST/data/data_ts01.dbf',
'/home/oradata/TEST/data/index_ts01.dbf',
'/home/oradata/TEST/data/index_ts02.dbf',
'/home/oradata/TEST/data/xdb02.dbf',
'/home/oradata/TEST/data/system02.dbf',
'/home/oradata/TEST/data/data_ts02.dbf',
'/home/oradata/TEST/data/sbill_data01.dbf',
'/home/oradata/TEST/data/data_ts03.dbf'
CHARACTER SET WE8ISO8859P1
;
8. Start the database in nomount state, run the Controlfile script you just created.

9. Control file gets created.

10. The datafiles will not be consistent at this point and hence copy over the archive logs from Production server and plce them accordingly on the test server. The location to put the fileson test server would be in the initTEST.ora with the parameter log_archive_dest

11. Based on the time you need to recover the database, choose the archive logs that you need to copy over and place them in the appropriate archiving directory on Test server
You can refer the timing(Archive log sequence number) from the alert log of production database.

12. There are various ways to recover database it can be of DBA’s choice and the need of data as well. I have chosen this to work out:

Once the archive logs are copied, run
SQL> recover database using backup controlfile until time ‘2009-04-21:22:59:04'
Make sure you have copied appropriate log sequence number till the specified time.

13. Once media recovery gets completed, you can see: Media Recovery Complete
message on SQL prompt.
Then run -alter database open resetlogs.

14. After database is open, it’s up to the DBA’s choice to put back the database in ARCHIVELOG mode or NOARCHIVELOG mode.

15. Add Temp Files to the TEMP Tablespaces(If you already have temporay tablespace):
alter tablespace "TEMP_TBS" add tempfile '/home/oradata/TEST/data/temp_02.dbf' size 4000M reuse autoextend off;
Or you can create a new tablespace based on your requirements.
Move on to POST REFRESH Tasks if there are any.