Create a control file for Refresh

Steps to create Control File during System Refresh:

Creating control file is not difficult task but sometime small mistake can leads to major issues while recovering Database during System Refresh.
Below are the steps and measures should be taken while creating control file on Target system:

1. In system Refresh, once the Online/Offline backup of Source System completed, immediately.create a trace file from the control file on the Source system and move that Trace file to the Target System.
In one of the refresh we faced issues while recovering Database, it was asking for log which does not exist in the back<SID>.log this is due to someone added 6 Datafiles after backup completion and trace file created after 2 days.

On source system as ora<sid>:

sqlplus “/ as sysdba”

SQL> alter database backup controlfile to trace;

SQL> exit;

File <sid>_ora_nnnnn.trc is created in /oracle/<SID>/saptrace/diag/rdbms/<sid>/<SID>/trace.

Copy this file to CONTROL.SQL.

ora<sid> 1> cp -p <sid>_ora_nnnnn.trc CONTROL.SQL

2.One more precaution, mostly people use Vi editor to edit and prepare control file.
I will suggest rather emphasis on Notepad. copy all the contents of the trace file and paste it on the local notepad file.

Most of the time in VI editor, small space or accidentally some punctuation mark goes wrong and control file we prepared is not right.even some are not used to VI editor commands, so Notepad is the safe and easy option.

orasid>cat <sid>_ora_nnnnn.trc
select all and copy paste it in the Notepad.

3. On Target System, save number of Online Redolog files:
SQL> select group#, sequence#, archived, status, bytes from v$log;

GROUP# SEQUENCE# ARC STATUS BYTES

---------- ---------- --- ---------------- ----------

11 1041 YES INACTIVE 1073741824

12 1042 YES INACTIVE 1073741824

13 1043 YES INACTIVE 1073741824

14 1044 YES INACTIVE 1073741824

15 1045 YES INACTIVE 1073741824

16 1046 YES INACTIVE 1073741824

17 1047 NO CURRENT 1073741824

18 1040 YES INACTIVE 1073741824

8 rows selected.

We have taken screen-shots (before Refresh) of this Target Redolog group sequence and size because after refresh also we want to set it as it is not like source system Redolog Group Sequence and size which we will maintain in control file.

Calculate Size of one Redolog Group in MB:

1073741824/1024/1024=1024M

4. Open Controlfile Notepad to edit.

There will be 2 sets starting from Startup to UTF8.

Remove all lines after first occurrence of CHARACTER SET UTF8 ;

5. At the end of the tracefile you will find some comments about the TEMP files (used for PSAPTEMP). Save these entries in a TEMP.SQL in same location as CONTROL.SQL. Entries can later be used for setting up (after changing contents of TEMP.SQL) to setup TEMP files.

Example:

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/SID/sapdata11/temp_1/temp.data1'

SIZE 6192M REUSE AUTOEXTEND OFF;

6. Remove lines from top to STARTUP NOMOUNT

7. Change

CREATE CONTROLFILE REUSE DATABASE "<sourceSID>" NORESETLOGSARCHIVELOG

into

CREATE CONTROLFILE SET DATABASE "<targetSID>"RESETLOGS NOARCHIVELOG

8. Change all <sourceSID> into <targetSID> using Notepad command cntrl+h (Replace)

Replace SOURCE SID with TARGET SID (in CAPS letters)
select Match case option.

9. Check the value for MAXLOGFILES (best to set to 255

10. Change the number and size of the online redologs to the original value (see step 4.19).

Example: source system GROUP 11-20, target system GROUP 11-18

source system = SIZE 2048M, target system = SIZE 1024M

When deleting obsolete groups be aware to have no , (comma) after last GROUP

Change size of online redo log file using Replace option (cntrl+h)2048M with 1024M.

11. Cross check edited controlfile notepad once again whether all changes has been done or not.

12.select all content of controlfile notepad and copy.

go to target system path: /oracle/SID/saptrace/diag/rdbms/sid/SID/trace/

create CONTROL.SQL file using vi command.

orasid> vi CONTROL.SQL

press escape key then press key i

press keys Shift+insert (it will paste all the copied content of the controlfile saved in notepad to the CONTROL.SQL)

once done press escape then :wq! to save the file.

Edited Control file will look like below:

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "ED2" RESETLOGS FORCE LOGGING NOARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 508

MAXINSTANCES 50

MAXLOGHISTORY 19948

LOGFILE

GROUP 11 (

'/oracle/ED2/origlogC/log_g11m1.dbf',

'/oracle/ED2/mirrlogC/log_g11m2.dbf'