Saturday, 30 November 2013

E-business Suite Data Only refresh without cloning 11i/R12

Data refreshing on e-business was queit challenging to do, our developers needed an instance with fresh data (ie: previous day data).

As rapid clone needs manual intervention we had to find a way to do it everyday with an automated way.

This is one of the methods used. This method assumes already a cloned instance is running for which only data has to be refreshed. The method requires the source database to be down for the database backup.

 
On the source server connect to sqlplus and issue "alter database backup controlfile to trace", This step has to be done whenever database undergoes some structural changes like adding datafile/logfile.

Shutdown the source database and make a backup of datafiles.

Move the generated trace file and backed up data files to the destination. 

Modify the generated trace file to to match the destination database


Clear everything up to "Set #2. RESETLOGS case"

Change "CREATE CONTROLFILE REUSE DATABASE "SOURCE" RESETLOGS  ARCHIVELOG" to "CREATE CONTROLFILE SET DATABASE "DEST" RESETLOGS"


Modify the data/log/temp files path to reflect correct location on the destination

After doing the modification the file looks like

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "VMERP" RESETLOGS
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 2921
LOGFILE
  GROUP 1 '/u03/vmproddata/redolog01.dbf'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u03/vmproddata/redolog02.dbf'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u03/vmproddata/redolog03.dbf'  SIZE 100M BLOCKSIZE 512,
 ----removed few lines here
-- STANDBY LOGFILE
DATAFILE
  '/u03/vmproddata/system01.dbf',
  '/u03/vmproddata/system02.dbf',
  '/u03/vmproddata/system03.dbf',
  '/u03/vmproddata/system04.dbf',
  '/u03/vmproddata/system05.dbf',
   ----removed few lines here
  '/u03/vmproddata/system06.dbf',
  '/u03/vmproddata/discoverer01.dbf',
  '/u03/vmproddata/APEXTS01.dbf'
CHARACTER SET US7ASCII
;
.........................few lines are removed for brevity

--RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/vmproddata/temp01.dbf'
     SIZE 2047M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/vmproddata/temp02.dbf'
     SIZE 2047M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
exit;

save the file as recreate_database.sql




On the destination remove  all copies of controlfiles

start sqlplus and run the controlfile recreate script

sqlplus / as sysdba @recreate_database.sql

set the environment variable on dbtier

start the listener on database tier

run autoconfig on dbtier

run autoconfig on appstier




I am not sure if its supported way, but it serves the purpose

1 comment:

  1. Hello,
    what about DB Name/SID you changed in create Controlfile command. Application Tier needs to be aware of that right?

    Regards!

    ReplyDelete