recreate new control file.

Hi,

Iam using oracle 9i and win2000 server in my PC. I have taken backup control file to trace and then deleted all my control files,then edited control file (removed reuse in create control file command) and saved in f: drive,then i did following process..

c:>sqlplus
connect /as sysdba
connected to ideal instance
SQL>@f:\control
sp2-0042 : unknown command " DATAFILE " rest of line ignored.
sp2-0734 : unknown command beginning " 'f:\oracle..." rest of line ignored
sp2-0734 : unknown command beginning " 'f:\oracle..." rest of line ignored
sp2-0734 : unknown command beginning " 'f:\oracle..." rest of line ignored
sp2-0734 : unknown command beginning " 'CHARACTER... " rest of line ignored
1 create controlfile dtabase "TEST" norestetlogs
2 maxlogfiiles 50
3 maxlogmemebers 5
4 maxdatafiles 100
5 max instances 1
6 maxloghistory 113
7 LOGFILE
8 GROUP 1 'f:\oracle\oradata\TEST\REDO01.log' size 100m
9 GROUP 2 'f:\oracle\oradata\TEST\REDO02.log' size 100m
10* GROUP 3 'f:\oracle\oradata\TEST\REDO03.log' size 100m
SQL>

This is the first time for me to create new control file.
pls help to proceed further.

Please upload your trace file which is created in "udump" directory.

I will create a sql out of it and upload it back. Use that and it will solver your problem. I see a lot of spelling mistakes in your script.

ALso, if you can upload the .sql script you are using for controlfile creation

I have typed it here with spelling mistake.but actually the file is taken from alter ..backup trace command and i have removed commented lines and (reuse)in that file and remaining are correct.
Why it shows this error?

Your script should start with :

create controlfile set database "TEST" ....
....

Why don't you upload your script which your are executing.

so where is the script?

Here is the trace file.

CREATE CONTROLFILE DATABASE "TEST" NORESETLOGS
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ' f:\oracle\oradata\test\redo01.log ' SIZE 100M,
GROUP 2 ' f:\oracle\oradata\test\redo02.log ' SIZE 100M,
GROUP 3 ' f:\oracle\oradata\test\redo03.log ' SIZE 100M

DATAFILE
' f:\oracle\oradata\test\system01.dbf',
' f:\oracle\oradata\test\undotbs.dbf',
' f:\oracle\oradata\test\drsys01.dbf',
' f:\oracle\oradata\test\example01.dbf',
' f:\oracle\oradata\test\indx01.dbf',
' f:\oracle\oradata\test\odm01.dbf',
' f:\oracle\oradata\test\tools01.dbf',
' f:\oracle\oradata\test\users01.dbf',
' f:\oracle\oradata\test\xdb01.dbf',
' f:\oracle\oradata\test\.dbf',
' f:\oracle\oradata\test\mca01.dbf'
CHARACTER SET WE8ISO8859P1
;

So i have missed set word.ok.. why it shows other error.

remove the blank line - you cant have one

I have copied the above file in sql prompt and added "SET" and executed it prompts me to change Noresetlogs to "RESETLOGS" , after changing it executed and results as 'Control file Created';
then i opened my database and everything is fine.

then deleted all control files & tried to repeate the same process in 'Command mode'
c:\sqlplus
connect /as sysdba
connected to the ideal instance.
SQL>@f:\control
sp2-0042 : unknown command " DATAFILE " rest of line ignored
then the same error repeats.(which i posted in first thread.)

The same content in f:\control is used previously in SQL (copy&Paste).


Why does it run smoothly if i use sql prompt(using copy & paste) and not in CMD mode.

when you specify the SET option,
you are instructing oracle to create the database with a new database name, so the redo logs will always be RESET.
Either create the database with no SET option and open the db with NORESETLOGS option, or create a new controlfile with new DBname with SET option and open the database with RESETLOGS option.

as what noted from other posters, remove the blank(space) before or after single quote(')

rey..same file works in sql prompt.But not in cmd.

rey..if blank space is the actual problem it should not work when i copied and ran that script. but it works properly.but if i show the path to access that file it throws err.So what i suspect, if we give the path it will throw error
(in command mode).

you cant have blank lines in the script, that it the problem you were facing when it is saying unknown command DATAFILE

ok.. davey. i will try that.

ok.., Dave it is fine..,

Can any one give me actual algorithm about conrol file creation.i.e., When we run that script it should create one control file.But it creates three files.Does it refer pfile before running that create control file script.

whatever you have in your parameter file