Home » RDBMS Server » Backup & Recovery » Losta all the redo lgs files
Losta all the redo lgs files [message #195694] Sun, 01 October 2006 07:53 Go to next message
vsridba
Messages: 2
Registered: August 2006
Location: USA
Junior Member
Hi,

We have Oracle DB 9i and we lost all the redolog files.
Please help. We use RMAN to backup the databse.
Please let me what needs to be done.
I am new to this forum.

Thanks.
Re: Losta all the redo lgs files [message #195704 is a reply to message #195694] Sun, 01 October 2006 10:48 Go to previous messageGo to next message
email.sandeepnaik
Messages: 121
Registered: August 2006
Senior Member
Restore from backup and recover point in time and open resetlogs
Sandeep
Re: Losta all the redo lgs files [message #195705 is a reply to message #195694] Sun, 01 October 2006 11:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
ORL files are never backed up with any hot backup method.
YOu can try a recovery using RMAN until a last valid bakup.


Please be warned.
This is just a simulation that uses certain undocumented methods.
If you are dealing with a production database, talk to Oracle support.
DO not apply these without the consent of Oracle support.
Again this is just a simulation.

--
-- I am trying to reproduce the issue.
-- 
sys@9i > select member,status from v$logfile;

MEMBER                                                       STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/mutation/redo01.log                  STALE
/u01/app/oracle/oradata/mutation/redo02.log
/u01/app/oracle/oradata/mutation/redo03.log

sys@9i > !rm /u01/app/oracle/oradata/mutation/redo01.log

sys@9i > !rm /u01/app/oracle/oradata/mutation/redo02.log

sys@9i > !rm /u01/app/oracle/oradata/mutation/redo03.log

sys@9i > shutdown abort
ORACLE instance shut down.
sys@9i > startup
ORACLE instance started.

Total System Global Area  504858576 bytes
Fixed Size                   731088 bytes
Variable Size             167772160 bytes
Database Buffers          335544320 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/mutation/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

--
-- Look into the status of those ORL files
-- From above message we know that, redo02 was online by that time.
-- So let us fix the rest.

sys@9i > select member,status from v$logfile;

MEMBER                                                       STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/mutation/redo01.log                  STALE
/u01/app/oracle/oradata/mutation/redo02.log
/u01/app/oracle/oradata/mutation/redo03.log

sys@9i > alter database clear logfile '/u01/app/oracle/oradata/mutation/redo01.log';

Database altered.

sys@9i > alter database clear logfile '/u01/app/oracle/oradata/mutation/redo02.log';
alter database clear logfile '/u01/app/oracle/oradata/mutation/redo02.log'
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/mutation/redo02.log'


sys@9i > alter database clear logfile '/u01/app/oracle/oradata/mutation/redo03.log';

Database altered.

--
-- when prompted, enter CANCEL
--
sys@9i > recover database until cancel;
ORA-00279: change 686669 generated at 10/01/2006 11:49:14 needed for thread 1
ORA-00289: suggestion : /home/oracle/scr/ora/mrajendr_1_2.arc
ORA-00280: change 686669 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/mutation/system01.dbf'


ORA-01112: media recovery not started


sys@9i > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/mutation/system01.dbf'


sys@9i > shutdown abort
ORACLE instance shut down.
--
-- I used spfile to startup before.
-- i created a pfile from spfile
-- and added these entries
-- and use PFILE to open the database.
sys@9i > !tail -1 $ORACLE_HOME/dbs/initmutation.ora;
 _allow_resetlogs_corruption = true

sys@9i > startup open pfile='$ORACLE_HOME/dbs/initmutation.ora';
ORACLE instance started.

Total System Global Area  504858576 bytes
Fixed Size                   731088 bytes
Variable Size             167772160 bytes
Database Buffers          335544320 bytes
Redo Buffers                 811008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


sys@9i > ALTER database open resetlogs;

Database altered.

sys@9i > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
--
-- now startup using spfile.
--
sys@9i > startup
ORACLE instance started.

Total System Global Area  504858576 bytes
Fixed Size                   731088 bytes
Variable Size             167772160 bytes
Database Buffers          335544320 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.
sys@9i > select member,status from v$logfile;

MEMBER                                                       STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/mutation/redo01.log
/u01/app/oracle/oradata/mutation/redo02.log
/u01/app/oracle/oradata/mutation/redo03.log



sys@9i >  !ls -lrt /u01/app/oracle/oradata/mutation/redo*
-rw-r-----   1 oracle   dba      104858112 Oct  1 12:02 /u01/app/oracle/oradata/mutation/redo03.log
-rw-r-----   1 oracle   dba      104858112 Oct  1 12:02 /u01/app/oracle/oradata/mutation/redo01.log
-rw-r-----   1 oracle   dba      104858112 Oct  1 12:03 /u01/app/oracle/oradata/mutation/redo02.log


--
-- checkthe alert log for any errors.
-- dump the old pfile with undocumented parameters or rename it to something obscure.
-- switch the current log files multiple times.
-- Take a new RMAN backup along with archived log files.

[Updated on: Sun, 01 October 2006 11:10]

Report message to a moderator

Re: Losta all the redo lgs files [message #195713 is a reply to message #195705] Sun, 01 October 2006 12:04 Go to previous messageGo to next message
vsridba
Messages: 2
Registered: August 2006
Location: USA
Junior Member
Mahesh,


We lost all the redo.logs due disk crash.
I am assuming we lost commited and uncommitted data.
Is there anyway we can recreate redologs in separate location without having to restore/recover using RMAN.

Please bear with me if my questions are not clear some times..I have given this new assignment database DBA and solaris administration.

Thanks.
Re: Losta all the redo lgs files [message #195714 is a reply to message #195713] Sun, 01 October 2006 12:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to do some recovery, with or without RMAN.
I suggest, open a ticket with Oracle.
Re: Losta all the redo lgs files [message #195897 is a reply to message #195714] Tue, 03 October 2006 01:43 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

If you don't have any bkp for redo log file.
then simply apply
sql>startup
error
sql>recover database until cancel;
media recovery complete.
sql>alter database open resetlogs

through below cmd database create new redo log files.
and u lost ur uncommited data.

thanxx
Previous Topic: RMAN backup command
Next Topic: RMAN Question
Goto Forum:
  


Current Time: Sun Dec 04 18:48:44 CST 2016

Total time taken to generate the page: 0.14458 seconds