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

Comments

  1. Nitin29 May 2015 at 17:22

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

    Regards!

    ReplyDelete

Post a Comment

Popular posts from this blog

Error processing request - Contact your application administrator apex 20.1

Image
After upgrading 11.2.0.4 to 19c with apex 20.1 accessing apex_admin/apex pages was throwing “Error processing request” Contact your application administrator. There were three invalid objects for APEX_200100 WWV_FLOW_SESSION_RAS WWV_FLOW_WIZARD_API WWV_FLOW_DATA_LOADER Tried compiling these objects but compile was completing with PL/SQL: ORA-00942: table or view does not exist To resolve the issue following rant needs to be given then compile (ran both grant and compile as sysdba) grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_200100; grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_200100; alter package APEX_200100.WWV_FLOW_SESSION_RAS compile body; alter package APEX_200100.WWV_FLOW_WIZARD_API compile body; alter package APEX_200100.WWV_FLOW_DATA_LOADER compile body; Returned 0 invalid object for APEX_200100  select * from dba_objects where status = 'INVALID' and owner = 'APEX_200100' ; Now both admin and apex pages ar
Read more

APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter (R12 12.1.3)

Image
APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter After upgrading oracle application 11i(11.5.10.2) to R12(12.1.3) running concurrent program request caused this issue When concurrent program is run for a concurrent request after upgrading to R12 12.1.3 the lov, which is attached to a parameter, gives APP-FND-01436: “List of Values cannot find any values for you to choose”. When the same request is run from oracle applications 11i 11.5.10.2 the same value set is working fine. The value set is based on a table. If you go and test the value set query from the value set definition screen it returns complete successful message yet if you go to the parameter of the concurrent request program form where the particular value set is used it gives you the above error. After searching google and oracle support I found metalink note 1400963.1, EBS R12.1: Parameter for Concurrent Request Using Table Based LOV G
Read more

WEB ADI - issues encountered during initial access

Image
On accessing create document link under Desktop Integrator responsibility browser was showing following error Set BNE UIX Physical Directory profile value to proper directory at site level Location = $COMMON_TOP/html/cabo/  (11i) Location = $COMMON_TOP/webapps/oacore/html/cabo/  (R12) As this was a cloned instance the value of this profile setting was pointing to a wrong location. set as BNE UIX Physical Directory /u03/oracle/prodcomn/html/cabo/ Access to functions are controlled by function security so to access and work with web adi the user should have proper access. You should have access to particular integrator to access and work with it Find out responsibility which is created to work with web adi check the main menu for this responsibility. Modify the responsibility or Menu if default is not fitting to your needs. For instance Desktop Integration Menu has create document, Define Lay out, Define Mapping, Manage Documents, Setup options Note : you must add
Read more