Re: ORA-00313 error on deleted logs

Karsten Farrell <kfarrell_at_medimpact.com> wrote in message news:<oCDt9.22$zP1.4292634_at_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.

Karsten,

Thanks for the help! I followed your procedure and was able to find the stored procedures that I was after. I had spent about a week transferring a database from MS SQL Server, so I was really interested in getting the stored procedures.

Anyway, thanks again! Received on Thu Oct 24 2002 - 11:06:19 CDT