skip to main content

SAP NetWeaver Newbie

SAP Oracle Database Refresh - Control file creation

SAP refresh requires database to be restored and recovered on the target system.

The control file includes: The database name. Names and locations of associated datafiles and redo log files


One of the most important steps in Oracle DB restore/recovery is the control file creation on the target system as the file locations and SID of the database change. Here are the steps to create control file:

Generate the control file trace on the source system:
1. Ensure that the source DB is on open or mounted mode by running the following command
select open_mode from v$database;
The output should be MOUNTED or READ WRITE
2. Write the control file to trace by running the following command
alter database backup controlfile to trace;
3. Find out where the trace is written by running the following
show parameter dump;
The location is most likely /oracle/<SID>/saptrace/diag/rdbms/<sid>/<SID>/trace for SAP-Oracle database. Check the latest trace file.
4. Open the file and copy the section resembling the following as a new file ex: createcontrolfile.sql by removing all lines above STARTUP NOMOUNT and changing REUSE to SET (that's because SID is changing) and replace prod SID with QA SID
CREATE CONTROLFILE SET DATABASE "<SID>" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 1000
    MAXINSTANCES 50
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 (
    '/oracle/<SID>/origlogA/log_g11m1.dbf',
    '/oracle/<SID>/mirrlogA/log_g11m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/<SID>/origlogB/log_g12m1.dbf',
    '/oracle/<SID>/mirrlogB/log_g12m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/<SID>/origlogA/log_g13m1.dbf',
    '/oracle/<SID>/mirrlogA/log_g13m2.dbf'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 4 (
    '/oracle/<SID>/origlogB/log_g14m1.dbf',
    '/oracle/<SID>/mirrlogB/log_g14m2.dbf'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/<SID>/sapdata1/system_1/system.data1',
  '/oracle/<SID>/sapdata1/sysaux_1/sysaux.data1',
  '/oracle/<SID>/sapdata3/undo_1/undo.data1',
  '/oracle/<SID>/sapdata1/sr3_1/sr3.data1',
  '/oracle/<SID>/sapdata1/sr3_2/sr3.data2',
  '/oracle/<SID>/sapdata1/sr3_3/sr3.data3',
  '/oracle/<SID>/sapdata1/sr3_4/sr3.data4',
  '/oracle/<SID>/sapdata1/sr3_5/sr3.data5',
  '/oracle/<SID>/sapdata2/sr3_6/sr3.data6',
  '/oracle/<SID>/sapdata2/sr3_7/sr3.data7',
  '/oracle/<SID>/sapdata2/sr3_8/sr3.data8',
  '/oracle/<SID>/sapdata2/sr3_9/sr3.data9',
  '/oracle/<SID>/sapdata2/sr3_10/sr3.data10',
  '/oracle/<SID>/sapdata3/sr3_11/sr3.data11',
  '/oracle/<SID>/sapdata3/sr3_12/sr3.data12',
  '/oracle/<SID>/sapdata3/sr3_13/sr3.data13',
  '/oracle/<SID>/sapdata3/sr3_14/sr3.data14',
  '/oracle/<SID>/sapdata3/sr3_15/sr3.data15',
  '/oracle/<SID>/sapdata4/sr3_16/sr3.data16',
  '/oracle/<SID>/sapdata4/sr3_17/sr3.data17',
  '/oracle/<SID>/sapdata4/sr3_18/sr3.data18',
  '/oracle/<SID>/sapdata4/sr3_19/sr3.data19',
  '/oracle/<SID>/sapdata4/sr3_20/sr3.data20',
  '/oracle/<SID>/sapdata1/sr3700_1/sr3700.data1',
  '/oracle/<SID>/sapdata1/sr3700_2/sr3700.data2',
  '/oracle/<SID>/sapdata1/sr3700_3/sr3700.data3',
  '/oracle/<SID>/sapdata1/sr3700_4/sr3700.data4',
  '/oracle/<SID>/sapdata2/sr3700_5/sr3700.data5',
  '/oracle/<SID>/sapdata2/sr3700_6/sr3700.data6',
  '/oracle/<SID>/sapdata2/sr3700_7/sr3700.data7',
  '/oracle/<SID>/sapdata2/sr3700_8/sr3700.data8',
  '/oracle/<SID>/sapdata3/sr3700_9/sr3700.data9',
  '/oracle/<SID>/sapdata3/sr3700_10/sr3700.data10',
  '/oracle/<SID>/sapdata3/sr3700_11/sr3700.data11',
 '/oracle/<SID>/sapdata3/sr3700_12/sr3700.data12',
  '/oracle/<SID>/sapdata4/sr3700_13/sr3700.data13',
  '/oracle/<SID>/sapdata4/sr3700_14/sr3700.data14',
  '/oracle/<SID>/sapdata4/sr3700_15/sr3700.data15',
  '/oracle/<SID>/sapdata4/sr3700_16/sr3700.data16',
  '/oracle/<SID>/sapdata1/sr3usr_1/sr3usr.data1'
CHARACTER SET UTF8
;
5. Adjust the SID and datafile locations as per the target system.

Run control file on the target system after datafiles are restored:
1. Start the database on NOMOUNT mode
startup nomount;
2. Run the createcontrolfile.sql file created in step 4 on source system
@createcontrolfile.sql
3. Check the status of the database to ensure that it is in MOUNTED state by running the SQL command select status from v$instance
4. Recover database using one the options (until a specific time or until all redo logs in oraarch are applied)
recover database using backup controlfile until time '2013-08-17:11:56:00';
recover database until cancel using backup controlfile;
You will get the following prompt:
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Choose AUTO
Once the redo logs are applied, you will get the prompt again, this time choose CANCEL
5. Open the database
alter database open resetlogs;
6. Create the temporary tablespace. The trace file created in the source system carries the command to recreate temporary tablespace. The command will resemble the following syntax:
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/<SID>/sapdata3/temp_1/temp.data1' SIZE 4000M REUSE AUTOEXTEND ON NEXT 20971520  MAXSIZE 10000M;

2 comments:

  1. It's to the point and very useful for quick reference.
    Thank you,
    Ananda

    ReplyDelete

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.