| Losta all the redo lgs files [message #195694] |
Sun, 01 October 2006 07:53  |
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 #195705 is a reply to message #195694] |
Sun, 01 October 2006 11:09  |
 |
Mahesh Rajendran
Messages: 10707
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 |
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 #195897 is a reply to message #195714] |
Tue, 03 October 2006 01:43 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
