Create Oracle Control File for SAP System Refresh

Source System

1. Ensure the Oracle database is in OPEN mode and not in BACKUP mode. In the SQL level of the source system, execute below command.

SQL> alter database backup controlfile to trace;

2. Look for the generated control file in the trace location (location maybe different on different Oracle version). Copy the control file to the source system via FTP software (WinSCP, FTPzilla).

SQL> show parameter dump

3. Login to the target operating system and look for the control file which been transferred earlier. The control file need to be edit because it is containing the source system information. You can edit the control file using the VI editor or notepad. The control file will be like below after edited (example). Take the information from STARTUP NOMOUNT line till CHARACTER SET UTF8; line. Two things need to be change here is the SID and REUSE to SET. Save the file as control.sql in /oracle/SID/ (or anywhere you liked).

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS  ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 1024
MAXINSTANCES 2
MAXLOGHISTORY 12988
LOGFILE
GROUP 1 (
'/oracle/DEV/origlogA/log_g1m1.dbf',
'/oracle/DEV/mirrlogA/log_g1m2.dbf'
) SIZE 200000K BLOCKSIZE 512,
GROUP 2 (
'/oracle/DEV/origlogB/log_g2m1.dbf',
'/oracle/DEV/mirrlogB/log_g2m2.dbf'
) SIZE 200000K BLOCKSIZE 512,
GROUP 3 (
'/oracle/DEV/origlogA/log_g3m1.dbf',
'/oracle/DEV/mirrlogA/log_g3m2.dbf'
) SIZE 200000K BLOCKSIZE 512,
GROUP 4 (
'/oracle/DEV/origlogB/log_g4m1.dbf',
'/oracle/DEV/mirrlogB/log_g4m2.dbf'
) SIZE 200000K BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/DEV/sapdata1/system_1/system.data1',
'/oracle/DEV/sapdata1/undo_1/undo.data1',
'/oracle/DEV/sapdata2/undo_2/undo.data2',
'/oracle/DEV/sapdata1/sysaux_1/sysaux.data1',
'/oracle/DEV/sapdata2/sr3_1/sr3.data1',
'/oracle/DEV/sapdata1/sr3_2/sr3.data2',
'/oracle/DEV/sapdata2/sr3_3/sr3.data3',
'/oracle/DEV/sapdata1/sr3_4/sr3.data4',
'/oracle/DEV/sapdata2/sr3_5/sr3.data5',
'/oracle/DEV/sapdata1/sysaux_2/sysaux.data2'
CHARACTER SET UTF8
;

4. Another thing to be taken is the PSAPTEMP information. You can get the PSAPTEMP information from the control file. The PSAPTEMP will need to be add later.

SQL> select file_name from dba_temp_files where tablespace_name='PSAPTEMP';
FILE_NAME
--------------------------------------------------------------------------------
/oracle/DEV/sapdata1/temp_1/temp.data1
1 rows selected.

Target System

5. Login to the SQL level of the Oracle database. Start the Oracle as NOMOUNT mode.

SQL> startup nomount;

6. Execute the control.sql script (from the step 3).

SQL> @/oracle/SID/control.sql

7. Add the PSAPTEMP tablespace (as per step 4) and you are done!

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/DEV/sapdata1/temp_1/temp.data1'
SIZE 9524M REUSE AUTOEXTEND OFF;

You May Also Like

Leave a Reply?