Solved

ORA-00368,00312,00353 error after a power blackout

Posted on 2004-10-13
8
2,375 Views
Last Modified: 2009-12-16
Hello
i have an oracle 9iR2 on a Windows 2000 Server..this is a system test...i have no backups of this machine..and the oracle was started in NOARCHIVELOG mode..
yesterday we've got a power blackout so the server goes down suddenly.
On the power up oracle was no longer available..with the errors in the Title
ORA-00368, 00312, 00353 concearning the corruption of the redolog.
I've tryed many recovery solution, but with no good results, because for every solution i've tryed, i needed the backup or the archivelogs..
What can i do?
i'm desperated
I know that there is an undocumented parameter to set on init.ota (i suppose it's the one located in c:\oracle\admin\instancename\pfile). The parameter is
_allow_resetlogs_corruption=TRUE ..but it's not working..i'm still getting those nasty errors..
Any tips on how and where to set this parameterto makes the oracle working?
Thank you in advance
0
Comment
Question by:acivita
  • 4
  • 3
8 Comments
 
LVL 9

Expert Comment

by:konektor
Comment Utility
try :
startup resetlogs
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
I think that resetlog will not work, because Oracle needs the
undo logs for the crash recovery.
In this case you have 2 options:

1) Check the alert.log to identify the corrupted log file and init<sid>.ora to
    identify the undo files and groups
2) Rename this file and copy over a good member from the same group
   -or-
   Drop the file and allow the database to use the other members

0
 

Author Comment

by:acivita
Comment Utility
well
first of all thank you..
i have no alert.log file on my system...
the corrupted file shoulde be this:C:\oracle\oradata\instancename\REDO03.log
so..u are suggesting me for example to copy over the redo01? rename it in redo03? is this right?
or simply delete the redo03?
i've tryed to delete redo03 but got this error:

ORA-00313: apertura non riuscita per membri del gruppo log 3 del percorso 1 (impossible to open for some members of the log group3 ..)
ORA-00312: log in linea 3 percorso 1: 'C:\ORACLE\ORADATA\TER\REDO03.LOG'
ORA-27041: impossibile aprire file (translate -- impossible to open the file)
OSD-04002: impossibile aprire il file
O/S-Error: (OS 2) Impossibile trovare il file specificato. (impossible to find the specified file)

thx
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You have to investigate if the redologs are multiplexed (have more then one member in every group).
By runnig DB it is easy: SELECT * FROM V$LOG;
But your DB isn't running. So look in the init.ora for multiplexing or investigate the directory where the logs reside.

If the redo logs are not multiplexed may be you have to try to do incomplete recovery:

SHUTDOWN IMMEDIATE
STARTUP MOUNT
RECOVER DATABASE UNTIL CANCEL;
CANCEL;
ALTER DATABASE OPEN RESETLOGS;

If you succeed to do this kind of recovery then for future accidents multiplex the control and redologs files.
This will allow you to use undamaged members of the groups.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:acivita
Comment Utility
This is all..i've tryed the recover but nothing...
as last resort what i can do...reinstall oracle and make backups i think :(

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        182  104857600          1 NO  INACTIVE
     25382084 12-OTT-04

         2          1        183  104857600          1 NO  ACTIVE
     25513123 12-OTT-04

         3          1        184  104857600          1 NO  CURRENT
     25644255 12-OTT-04

SQL> startup mount
Istanza ORACLE avviata.

Total System Global Area  294722480 bytes
Fixed Size                   453552 bytes
Variable Size             134217728 bytes
Database Buffers          159383552 bytes
Redo Buffers                 667648 bytes
Database montato.
SQL> recover database until cancel;
ORA-00279: modifica 25703897 generata alle 10/12/2004 12:09:01 necessaria per
il thread 1
ORA-00289: suggerimento : C:\ORACLE\ORA92\RDBMS\ARC00184.001
ORA-00280: modifica 25703897 per thread 1  nella sequenza #184


Specificare log: {<RET>=suggerito | nome file | AUTO | CANCEL}
CANCEL;
ORA-00308: impossibile aprire log archiviato 'CANCEL;'
ORA-27041: impossibile aprire file
OSD-04002: impossibile aprire il file
O/S-Error: (OS 2) Impossibile trovare il file specificato.


Specificare log: {<RET>=suggerito | nome file | AUTO | CANCEL}
cancel
ORA-01547: avvertenza: RECOVER riuscito ma OPEN RESETLOGS otterrebbe un errore
ORA-01194: necessarie pi riparazioni in file 1 per ottenere coerenza
ORA-01110: file di dati 1: 'C:\ORACLE\ORADATA\TER\SYSTEM01.DBF'


ORA-01112: recupero di dischi non iniziato


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERRORE alla riga 1:
ORA-01194: necessarie pi riparazioni in file 1 per ottenere coerenza
ORA-01110: file di dati 1: 'C:\ORACLE\ORADATA\TER\SYSTEM01.DBF'
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
If you reinstall Oracle you will lose all data in the tables.

Read this:
(B) Redo logs
    ---------
In normal cases, we would not have backups of online redo log files. But the  
inactive logfile changes could already have been checkpointed on the datafiles
and even archive log files may be available.
 
SQL> startup mount
     Oracle Instance Started
     Database mounted
     ORA-00313: open failed for members of log group 1 of thread 1
     ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
     ORA-27041: unable to open file
     OSD-04002: unable to open file
     O/S-Error: (OS 2) The system cannot find the file specified.
 
** Verify if the lost redolog file is Current or not.
     SQL> select * from v$log;
     SQL> select * from v$logfile;  
 
     --> If the lost redo log is an Inactive logfile, you can clear the logfile:
 
     SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
 
     Alternatively, you can drop the logfile if you have at least two other    
     logfiles:
     SQL> alter database drop logfile group 1;
 
     
     --> If the logfile is the Current logfile, then do the following:
     SQL> recover database until cancel;
         
     Type Cancel when prompted
 
     SQL>alter database open resetlogs;
 
     
     The 'recover database until cancel' command can fail with the following  
     errors:
     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: '/ORACLE/ORADATA/H817/SYSTEM01.DBF'
 
     In this case , restore an old backup of the database files and apply the
     archive logs to perform incomplete recovery.
     --> restore old backup
     SQL> startup mount
     SQL> recover database until cancel using backup controlfile;
     SQL> alter database open resetlogs;
 
 
If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would have restore from an old backup and start the database.
 
 
Note that all redo log maintenance operations are done in the database mount state
0
 

Author Comment

by:acivita
Comment Utility
I've solved it in that way..
added in my .ora startup file located in C:\oracle\ora92\sysman\ifiles this options:
LOG_BLOCK_CHECKSUM=FALSE
_allow_resetlogs_corruption=TRUE

now i'm making an export of the database
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
Comment Utility
Great!

After the full Export multiplex the redolog files:
1. Create a directories 'multiplexed' and 'multiplexed1'
2.Run as SYS:

ALTER DATABASE ADD LOGFILE MEMBER 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED\REDO01_b.LOG’ TO GROUP 1;

ALTER DATABASE ADD LOGFILE MEMBER ‘E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED\REDO02_b.LOG’ TO GROUP 2;

ALTER DATABASE ADD LOGFILE MEMBER ‘E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED\REDO03_b.LOG’ TO GROUP 3;


ALTER DATABASE ADD LOGFILE MEMBER 'E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED1\REDO01_c.LOG’ TO GROUP 1;

ALTER DATABASE ADD LOGFILE MEMBER ‘E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED1\REDO02_c.LOG’ TO GROUP 2;

ALTER DATABASE ADD LOGFILE MEMBER ‘E:\ORACLE_DB_MAIN\ORADATA\MAIN\MULTIPLEXED1\REDO03_c.LOG’ TO GROUP 3;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now