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;

Comments

Post a Comment

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Image
With increasing need to improve performance or reduce costs or vanishing support for operating systems/databases it becomes inevitable to migrate to a better OS or DB. We feel take a quick look at the primary ingredients of the tools for OS/DB migration handled by R3SETUP. Tools - R3load, R3ldctl and R3szchk R3ldctl creates STRucture files, TemPLate files by reading the technical settings for table/indexes in SAP data dictionary. R3szchk calculates the database size object sizes (EXTent files) for the target system based on the STR files created by R3ldctl. The EXT and STR files can be later split into smaller groups. The most crucial tool R3load is responsible for unloading and loading data from the database. R3load uses the files created by R3ldctl and R3szchk and dumps the data into compressed binary format as .001, .002 .. files. It uses cmd files to achieve the task. If the cmd files are not present, it creates on its own. Files Command files*.cmd  are created i
Read more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

If you find a table to be available on the database, but not active in ABAP dictionary, try calling SE14 as DDIC user, enter the table name and choose Table > Reconstruct, Execute. If this method fails follow these steps: Call transaction code SE37 and execute the function module  DD_TABL_ACTM with single test On the next screen, do not change any other field except the following and set them as shown below: MODE: 12 NTAB_PUTSTATE: N TABNAME: <enter the correct table name> Click on Execute The export parameter ACT_RESULT should show 0. To verify if the inconsistency is fixed, call transaction SE14 > Extras > Database Object > Check and  SE14 > Extras > Runtime object > Check.
Read more

301 Redirect Using SAP Web Dispatcher

301 redirects are typically used to redirect accesses that include www to non-www or vice-versa. Example, when you call http://blogger.com, the call is redirected to www.blogger.com. An end-user may prefer to call a URL in any manner. You must ensure that all these calls are redirected to relevant URLs with content. SAP web dispatcher can be configured to send a redirect (301) to the client. To enable redirection add icm/HTTP/redirect_<xx> to the web dispatcher's profile file. The parameter uses the following syntax: icm/HTTP/redirect_<xx> = PREFIX=<URL prefix>[, FROM=<pattern for URL>, FROMPROT=<incoming protocol>, FOR=<pattern for host name:port>,TO=<new URL prefix>, PROT=<protocol>, HOST=<host>, PORT=<port number/name>] Let's say you want to redirect all the HTTP requests on mysource.com on 80 port to mytarget.com on HTTPS 443 port icm/HTTP/redirect_0 = PREFIX=/, FROM=*, FROMPROT=http, FOR=mysource.com:80,
Read more