APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 37,356 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    May 2009
    M T W T F S S
    « Apr   Jun »
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Archives

  • Authors

Oracle Database 10g DB Refresh

Posted by appsinfo on May 9, 2009

Database Refresh

Often there will be requirement for Database refresh from production instance for testing of specific modules, upgrades, patches, or data migration.

Database referesh from production to UAT. (COLD)

1. Prepare Control file at production instance using

alter database backup controlfile to trace as ‘F:\ctlfile.sql’;

2. Edit the controle ctlfile.sql and delete all the lines except the structure mentioned below and copy this file to the target instance.

When you are using the control file from the production instance you need to use ‘REUSE SET DATABASE <new sid>’

CREATE CONTROLFILE reuse set DATABASE “UAT” RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 7260
LOGFILE
  GROUP 1 (
    ‘E:\oracle\uatdata\LOG01A.DBF’,
    ‘E:\oracle\uatdata\LOG01B.DBF’
  ) SIZE 10M,
  GROUP 2 (
    ‘E:\oracle\uatdata\LOG02A.DBF’,
    ‘E:\oracle\uatdata\LOG02B.DBF’
  ) SIZE 10M
DATAFILE
  ‘E:\oracle\uatdata\SYSTEM01.DBF’,
  ‘E:\oracle\uatdata\SYSTEM02.DBF’,
  ‘E:\oracle\uatdata\SYSTEM03.DBF’,
  ‘E:\oracle\uatdata\SYSTEM04.DBF’,
  ‘E:\oracle\uatdata\SYSTEM05.DBF’,
  ‘E:\oracle\uatdata\SYSTEM06.DBF’,
  ‘E:\oracle\uatdata\SYSTEM07.DBF’,
  ‘E:\oracle\uatdata\SYSTEM08.DBF’,
  ‘E:\oracle\uatdata\SYSTEM09.DBF’,
  ‘E:\oracle\uatdata\SYSTEM10.DBF’,
  ‘E:\oracle\uatdata\SYSTEM11.DBF’,
  ‘E:\oracle\uatdata\UNDO01.DBF’,
  ‘E:\oracle\uatdata\A_ARCHIVE01.DBF’,
  ‘E:\oracle\uatdata\A_INT01.DBF’,
  ‘E:\oracle\uatdata\A_MEDIA01.DBF’,
  ‘E:\oracle\uatdata\A_NOLOG01.DBF’,
  ‘E:\oracle\uatdata\A_QUEUE01.DBF’,
  ‘E:\oracle\uatdata\A_QUEUE02.DBF’,
  ‘E:\oracle\uatdata\A_REF01.DBF’,
  ‘E:\oracle\uatdata\A_REF02.DBF’,
  ‘E:\oracle\uatdata\A_SUMM01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA02.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA03.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND02.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND03.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND04.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND05.DBF’,
  ‘E:\oracle\uatdata\CTXD01.DBF’,
  ‘E:\oracle\uatdata\ODM.DBF’,
  ‘E:\oracle\uatdata\OLAP.DBF’,
  ‘E:\oracle\uatdata\OWAD01.DBF’,
  ‘E:\oracle\uatdata\PORTAL01.DBF’,
  ‘E:\oracle\uatdata\SYSAUX01.DBF’,
  ‘E:\oracle\uatdata\USER_DATA.ORA’,
  ‘E:\oracle\uatdata\USER_IDX.ORA’,
  ‘E:\oracle\uatdata\APPS_TS_TOOLS.ORA’,
  ‘E:\oracle\uatdata\CUSTOM01.DBF’
CHARACTER SET AR8MSWIN1256
;

3. Delete the datafiles from the UAT instance.

4. Copy all the dbfs from the production instance.

5. Connect in nomount phase

     sqlplus “/as sysdba”

     startup nomount

6. Run the ctlfile.sql   @E:\ctlfile.sql

7. reuse the temp file

 SQL> alter tablespace TEMP add tempfile ‘E:\oracle\uatdata\temp01.dbf’ size 520 m reuse autoextend off;

 Tablespace altered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: