Re: ORA-00313 error on deleted logs

From: Karsten Farrell <kfarrell_at_medimpact.com>
Date: Wed, 23 Oct 2002 20:27:00 GMT
Message-ID: <oCDt9.22$zP1.4292634@newssvr13.news.prodigy.com>

Don wrote:
> I am an Oracle newbie and accidently deleted the redo logs (and
> emptied the recyle bin). Oracle 9.2 on Windows XP. When trying to
> start the database in Enterprise Manager, I get the following error:
>
> ORA-00313: open field for memebers of log group 3 of thread1
> ORA-00312: online log 3 thread1:'C:\ORACLE\ORADATA\TEST\REDO03.LOG
>
> I can go into the Redo Log Groups container and add more groups, but I
> don't seem to be able to switch log files without the db running.
>
> Is there a way to start the database in Enterprise Manager without
> this log?
>
> Is there a way that I can transfer the Procedures out of the current
> database?
>
> Thanks for any and all help.
>
> Don

You might be able to get away with the following procedure (or something similar) after you've backed up your existing datafiles (just in case).

SQLPLUS /NOLOG
CONNECT / AS SYSDBA
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG LOGFILE

   GROUP 1 'c:\oracle\oradata\test\redo01.log' size 1m,
   GROUP 2 'c:\oracle\oradata\test\redo02.log' size 1m,
   GROUP 3 'c:\oracle\oradata\test\redo03.log' size 1m

DATAFILE
   'c:\oracle\oradata\test\system01.dbf',    ...[all your data files]...
CHARACTER SET WE8ISO8859P1; Modify the filenames and directories to suit your particular situation.

Startup the database in 'NOMOUNT' so you only read the init.ora and establish the SGA. This command will create a brand new control file. The 'RESETLOGS' will create brand new redo log files (any old redo or archives are useless once this is done).

NOTE: You won't be able to recover any transactions that were lost when you deleted the redo logs. Received on Wed Oct 23 2002 - 15:27:00 CDT