RE: Lost ofall redo logs

From: Bill Buchan <wbuchan_at_uk.intasys.com>
Date: Thu, 19 Sep 2002 03:08:48 -0800
Message-ID: <F001.004D35B4.20020919030848@fatcity.com>

If you lost the redo logs *after* you shutdown the database, you can do the following to re-create them:

As DBA: startup mount;
alter database backup controlfile to trace;
select * from v$controlfile;
shutdown
Delete the controlfiles identified by (3) as these need to be re-created.
Edit the tracefile created by (2) to look something like the following:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS NOARCHIVELOG

     MAXLOGFILES 6
     MAXLOGMEMBERS 2
     MAXDATAFILES 64
     MAXINSTANCES 1
     MAXLOGHISTORY 226

LOGFILE

   GROUP 1 '/usr4/oradata/SID/redoSID01a.log'  SIZE 50M,
   GROUP 2 '/usr4/oradata/SID/redoSID02a.log'  SIZE 50M,
   GROUP 3 '/usr4/oradata/SID/redoSID03a.log'  SIZE 50M,
   GROUP 4 '/usr4/oradata/SID/redoSID04a.log'  SIZE 50M

DATAFILE

   '/usr1/oradata/SID/system_01.dbf',
   '/usr2/oradata/SID/users_01.dbf',
   '/usr3/oradata/SID/users_indexes_01.dbf',
   '/usr3/oradata/SID/rollback_01.dbf'

CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/usr1/oradata/SID/temp_01.dbf' REUSE;

ie.

	i.	Remove leading comments and lines beginning with #
	ii.	In the CREATE CONTROLFILE line change REUSE to SET
		and NORESETLOGS to RESETLOGS
	iii.	Get rid of RECOVER DATABASE
	iv.	Change ALTER DATABASE OPEN to ALTER DATABASE OPEN RESETLOGS
	(v.	Ignore ALTER TABLESPACE stuff if you are not using LMT
		temporary tablespaces.)

7. Now go into SQL*Plus as DBA and run the script, which should build new
	controlfiles, re-create the log files and open the database.

Remember to back up everything you *do* have before starting. This will not work if you lost the redo logs before you shutdown the database.

Bill.
- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, September 18, 2002 5:30 PM

> Hi people...
>
> What to do if I have LOST ALL REDO LOGS and i don´t have any backup?
>
> Thanks in advance!
>
> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
> Gilberto Gampert Universidade de Passo Fundo
> Administrador de Banco de Dados Passo Fundo - RS - Bra5il
> gampert@upf.br http://www.upf.br
> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gilberto Gampert
> INET: gampert_at_upf.br

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Sep 19 2002 - 06:08:48 CDT