How to restore the control files

[castlerock]

Hi!
I am practicing some recovery scenarios in oracle 9i on WIN 2000

I am restoring the datafiles from tape drive backup to different disk,and renaming the datafile

>alter datatbase rename file d:\a to e:\a

and to change the datafiles location the control file

>alter database backup controlfile to trace;

using the latest trace file from the user_dump destination ...i am not able to recreate the control files

The content of the control file:
--------------------------------

STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "oracle_db" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\oracle_db\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\oracle_db\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\oracle_db\REDO03.LOG' SIZE 100M
# STANDBY LOGFILE
DATAFILE
'C:\ORACLE\ORADATA\oracle_db\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\oracle_db\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\oracle_db\DRSYS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\oracle_db\INDX01.DBF',
'C:\ORACLE\ORADATA\oracle_db\TOOLS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;
When i try to run this i am getting error!

> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC

ERROR:

SQL> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC
ORA-01078: failure in processing system parameters
LRM-00111: no closing quote for value 'c:'
'
SP2-0734: unknown command beginning "STANDBY LO..." - rest of line ignored.
CREATE CONTROLFILE REUSE DATABASE "ORACLE_DB" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SP2-0734: unknown command beginning "Recovery i..." - rest of line ignored.
SP2-0734: unknown command beginning "or if the ..." - rest of line ignored.
ORA-03114: not connected to ORACLE


SP2-0734: unknown command beginning "Database c..." - rest of line ignored.
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


SP2-0734: unknown command beginning "Files in n..." - rest of line ignored.
SP2-0734: unknown command beginning "TO 'C:\ORA..." - rest of line ignored.
SP2-0734: unknown command beginning "Commands t..." - rest of line ignored.
SP2-0734: unknown command beginning "Online tem..." - rest of line ignored.
SP2-0734: unknown command beginning "Other temp..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
ALTER DATABASE RENAME FILE 'MISSING00009'
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


Can anyone help me ?


Many Thanks,

[stecal]

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;
When i try to run this i am getting error!

> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC

ERROR:

SQL> @C:\oracle\admin\oracle_db\udump\ORA01420.TRC
ORA-01078: failure in processing system parameters
LRM-00111: no closing quote for value 'c:'


Doesnt't that pretty much tell you what the problem is?

[xyz2000]

It is unclear what the sequence in your step.

First, after you restored database from tape to disk, database is down at that time, how can you rename datafile using alter datatbase rename file d:\a to e:\a and alter database backup controlfile to trace;

# is not a common in sqlplus. # is a common in server manager which is desupported in oracle 9i. You have to delete the line start with # .


If you want to recreate control file, database have to be in nomount mode. You should shutdown database cleanly ( shutdown )first and run

STARTUP NOMOUNT;

feedback should be:

ORACLE instance started.


Nake sure oracle instance is up.
ERROR at line 1:
ORA-03113: end-of-file on communication channel

your instnace may not running right.

After database is in nomount mode, you can run:

CREATE CONTROLFILE REUSE DATABASE "oracle_db" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\oracle_db\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\oracle_db\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\oracle_db\REDO03.LOG' SIZE 100M
DATAFILE
'C:\ORACLE\ORADATA\oracle_db\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\oracle_db\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\oracle_db\DRSYS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\oracle_db\INDX01.DBF',
'C:\ORACLE\ORADATA\oracle_db\TOOLS01.DBF',
'C:\ORACLE\ORADATA\oracle_db\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;

You should see feedback :
Control file created.

Then you can run
SQL> RECOVER DATABASE

If you only shutdown your database cleanly and run startup nomount and create controlfile script, then there is no need to recover database. you will get following error:

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

You can ignore this and go to next step.

ALTER DATABASE OPEN;

You should get feedback:

Database altered.

In your case, you get
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-03114: not connected to ORACLE

This means your oracle instance is not up at all.(startup nomount failed. Reason: I think you did not shutdwon your database first.)


Then you can run

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\oracle_db\TEMP01.DBF' REUSE;


...................


I think you should run this step by step. Do not run it in one script.