create Control file in my Quality server

[Former Member]

Dear Team,

I am doing Coloning in my quality server . I have done full system restore from my Full Offline backup.

I am ushing HP Ux.

quality:oraqas 40> nohup brrestore -c force -b bdzvcstv.aft -m full &

Now it is sucessfully restore.

Now How to create Control file in my Quality server.

kallissa

[Former Member · Accepted solution]

hi kalissa,

you can use sql plus command on your sqlplus :

>CREATE CONTROLFILE SET DATABASE u201C<DATABASE_NAME>u201D RESETLOGS ARCHIVELOG

to create new controlfile.

ardhian

http://ardhian.kioslinux.com

http://sapbasis.wordpress.com

[Former Member · Accepted solution]

Dear Ardhin,

I am login in my Source System sapprd1

oraprd>sqlplus

SQL>alter database backup controlfile to trace ;

I think it will generate on trace file.

then what to do.

[Former Member · Accepted solution]

hello Kallissa

As a result of this command, a trace file will be created under /oracle/<SSID>/saptace/usertrace directory. File can be determined by sorting according to modification/creation dates (UNIX: # ls u2013ltr). Most current file must be cheched.

You have to edit this file to be able to use for CONTROLFILE creation on target system.

Rename file name as CONTROL.SQL

Open file to edit using VI (UNIX) or NOTEPAD (WIN).

Remove all lines before "STARTUP MOUNT" line. Delete all commented "#" lines. Also remove all lines after CHARACTER SET WE8DEC;" line.

Change all Source SID's to Target SID via following commands.

UNIX (VI): :%s/<SSID>/<TSID>/g

WIN (NOTEPAD): CTRL+H

Change the line

CREATE CONTROLFILE REUSE DATABASE u2018SSIDu2019 NORESETLOGS ARCHIVELOG;

as follow

CREATE CONTROLFILE REUSE SET DATABASE u2018TSIDu2019 RESETLOGS ARCHIVELOG;

After CONTROL.SQL script preparation, following commands must be run to create

CONTROLFILE of target system:

1. su u2013 ora<targetsid>

2. slqplus

sqlplus> connect internal

sqlplus> @/<path_to_file>/control.sql

You have to guarantee the successful completion of this command as follow:

sqlplus> @control.sql

Statement processed.

Hope it helps

Rohit

[former_member204746 · Accepted solution]

kalissa,

there are good documentation on the internet about this process. I provided 2 links earlier in this topic.

all quetion you might have about control files are answered in these guides.

so, please read them before asking more questions. This forum was not created to spoonfeed people.

thanks.

[Former Member · Accepted solution]

Thanks Rohit for your reply.

one file prd_ora_348.trc is generated in /oracle/SID/saptrace/usertrace . Now I move this file in my targer system and rename controlprd.sql.

Now I open This file

quality :/oracle/QAS#cat controlprd.sql

cREATE CONTROLFILE SET DATABASE "QAS" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 3

MAXDATAFILES 500

MAXINSTANCES 50

MAXLOGHISTORY 11680

LOGFILE

GROUP 1 (

'/oracle/QAS/origlogA/log_g11m1.dbf',

'/oracle/QAS/mirrlogA/log_g11m2.dbf'

) SIZE 50M,

GROUP 2 (

'/oracle/QAS/origlogB/log_g12m1.dbf',

'/oracle/QAS/mirrlogB/log_g12m2.dbf'

) SIZE 50M,

GROUP 3 (

'/oracle/QAS/origlogA/log_g13m1.dbf',

'/oracle/QAS/mirrlogA/log_g13m2.dbf'

) SIZE 50M,

GROUP 4 (

'/oracle/QAS/origlogB/log_g14m1.dbf',

'/oracle/QAS/mirrlogB/log_g14m2.dbf'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/oracle/QAS/sapdata1/system_1/system.data1',

'/oracle/QAS/sapdata3/undo_1/undo.data1',

'/oracle/QAS/sapdata1/sysaux_1/sysaux.data1',

'/oracle/QAS/sapdata1/sr3_1/sr3.data1',

'/oracle/QAS/sapdata1/sr3_2/sr3.data2',

'/oracle/QAS/sapdata1/sr3_3/sr3.data3',

'/oracle/QAS/sapdata1/sr3_4/sr3.data4',

'/oracle/QAS/sapdata1/sr3_5/sr3.data5',

'/oracle/QAS/sapdata2/sr3_6/sr3.data6',

'/oracle/QAS/sapdata2/sr3_7/sr3.data7',

'/oracle/QAS/sapdata2/sr3_8/sr3.data8',

'/oracle/QAS/sapdata2/sr3_9/sr3.data9',

'/oracle/QAS/sapdata2/sr3_10/sr3.data10',

'/oracle/QAS/sapdata3/sr3_11/sr3.data11',

'/oracle/QAS/sapdata3/sr3_12/sr3.data12',

'/oracle/QAS/sapdata3/sr3_13/sr3.data13',

'/oracle/QAS/sapdata3/sr3_14/sr3.data14',

'/oracle/QAS/sapdata3/sr3_15/sr3.data15',

'/oracle/QAS/sapdata4/sr3_16/sr3.data16',

'/oracle/QAS/sapdata4/sr3_17/sr3.data17',

'/oracle/QAS/sapdata4/sr3_18/sr3.data18',

'/oracle/QAS/sapdata4/sr3_19/sr3.data19',

'/oracle/QAS/sapdata4/sr3_20/sr3.data20',

'/oracle/QAS/sapdata1/sr3700_1/sr3700.data1',

'/oracle/QAS/sapdata1/sr3700_2/sr3700.data2',

'/oracle/QAS/sapdata1/sr3700_3/sr3700.data3',

'/oracle/QAS/sapdata1/sr3700_4/sr3700.data4',

'/oracle/QAS/sapdata2/sr3700_5/sr3700.data5',

'/oracle/QAS/sapdata2/sr3700_6/sr3700.data6',

'/oracle/QAS/sapdata2/sr3700_7/sr3700.data7',

'/oracle/QAS/sapdata2/sr3700_8/sr3700.data8',

'/oracle/QAS/sapdata3/sr3700_9/sr3700.data9',

'/oracle/QAS/sapdata3/sr3700_10/sr3700.data10',

'/oracle/QAS/sapdata3/sr3700_11/sr3700.data11',

'/oracle/QAS/sapdata3/sr3700_12/sr3700.data12',

'/oracle/QAS/sapdata4/sr3700_13/sr3700.data13',

'/oracle/QAS/sapdata4/sr3700_14/sr3700.data14',

'/oracle/QAS/sapdata4/sr3700_15/sr3700.data15',

'/oracle/QAS/sapdata4/sr3700_16/sr3700.data16',

'/oracle/QAS/sapdata1/sr3usr_1/sr3usr.data1',

'/oracle/QAS/sapdata4/sr3700_17/sr3700.data17',

'/oracle/QAS/sapdata4/sr3700_18/sr3700.data18',

'/oracle/QAS/sapdata1/system_2/system.data2',

'/oracle/QAS/sapdata4/sr3700_19/sr3700.data19',

'/oracle/QAS/sapdata1/sysaux_2/sysaux.data2',

'/oracle/QAS/sapdata4/sr3700_20/sr3700.data20',

'/oracle/QAS/sapdata4/sr3700_21/sr3700.data21',

'/oracle/QAS/sapdata5/sr3700_22/sr3700.data22',

'/oracle/QAS/sapdata5/sr3700_23/sr3700.data23',

'/oracle/QAS/sapdata5/sr3700_24/sr3700.data24',

'/oracle/QAS/sapdata5/sr3700_25/sr3700.data25',

'/oracle/QAS/sapdata5/sr3_21/sr3.data21',

'/oracle/QAS/sapdata5/sr3_22/sr3.data22',

'/oracle/QAS/sapdata5/sr3700_26/sr3700.data26',

'/oracle/QAS/sapdata5/sr3700_27/sr3700.data27',

'/oracle/QAS/sapdata5/sr3700_28/sr3700.data28',

'/oracle/QAS/sapdata5/sr3_23/sr3.data23',

'/oracle/QAS/sapdata5/sr3_24/sr3.data24',

'/oracle/QAS/sapdata5/sr3_25/sr3.data25',

'/oracle/QAS/sapdata5/sr3_26/sr3.data26',

'/oracle/QAS/sapdata5/sr3_27/sr3.data27',

'/oracle/QAS/sapdata5/sr3_28/sr3.data28',

'/oracle/QAS/sapdata5/sr3_29/sr3.data29',

'/oracle/QAS/sapdata5/sr3_30/sr3.data30'

CHARACTER SET UTF8

;

Copy above this file in notepad and rename all PRD to QAS.

Now where I put this notepad file

In witch Location should I Put this control.sql file in target system.

Thanks

[Former Member · Accepted solution]

Hello Kalissa

Ensure you have created control file script as follows:

Remove all lines before "STARTUP MOUNT" line. Delete all commented "#" lines. Also

remove all lines after CHARACTER SET WE8DEC;" line.

Change all Source SID's to Target SID via following commands.

Change the line

CREATE CONTROLFILE REUSE DATABASE u2018SSIDu2019 NORESETLOGS ARCHIVELOG;

as follow

CREATE CONTROLFILE REUSE SET DATABASE u2018TSIDu2019 RESETLOGS ARCHIVELOG;

Now that control.sql script has been created,we will have to create the control file of target system:

Logon to the target system with orasid and do the follwoing by connecting to sqlplus:

1. sqlplus

sqlplus> connect internal

sqlplus>@/<path_to_file>/control.sql

You have to guarantee the successful completion of this command as follow:

sqlplus> @control.sql

Statement processed.

Now try opening the database.

Rohit

[Former Member · Accepted solution]

Before creating new control file

I have checked old control file in my system

/oracle/QAS/sapdata1/system_1/cntrl

/oracle/QAS/saparch/cntrl

/oracle/QAS/origlogA/cntrl

Should I Removed all these Control file or Copy some other location.??

WhenI Run this scrept.

SQL>@control.sql

Is this scrept create new control file in all give previous location.??

kallis

[Former Member · Accepted solution]

Hi Kallissa

yes, remove the control files in all below mentioned 3 folders

/oracle/QAS/sapdata1/system_1/cntrl

/oracle/QAS/saparch/cntrl

/oracle/QAS/origlogA/cntrl

and then run the script. It will create new control file

thanks

Bhudev

[Former Member · Accepted solution]

Thanks for your nice response.

kallis

[Former Member]

Hi

As as part of pre-work we delete all the content in the mentioned directories

rm -r /oracle/<sid>/sapdata/

rm /oracle/<sid>/saparch/*.dbf

rm /oracle/<sid>/dbs/cntrl<sid>.dbf

rm u2013r /oracle/<sid>/mirrlog?/*

rm u2013r /oracle/<sid>/origlog?/*

rm u2013r /oracle/<sid>/sapreorg

mkdir /oracle/<sid>/sapdata1/cntrl

mkdir /oracle/<sid>/sapdata2/cntrl

As of now you are at the phase of creating control file just ignore all and create control file

sql>@control.trc

Regards

Uday

[former_member204746]

go get the latest homogeneous copy guide from http://service.sap.com/systemcopy

or go at http://www.basisconsultant.com/index.php?option=com_remository&Itemid=185&func=fileinfo&id=10 for a user-based documentation.

[Former Member]

Hello Kallissa,

Please refer to following link for creating the control file:

http://www.dba-oracle.com/oracle_tips_db_copy.htm

Let me know of any issues

Rohit