Friday, 12 July 2013

ORA-00333: redo log read error block 41017 count 7680

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00333: redo log read error block 41017 count 7680


Solution:
             http://kr.forums.oracle.com/forums/thread.jspa?threadID=1081145&start=15&tstart=0

Database not starting,REDO log file error?

This question is Not Answered.
698504Journeyer
698504 Jun 3, 2010 5:59 AM
My DB was working fine uptill last evening, when i restarted it for some DB task it showed me an error ..

ORA-00333: redo log read error block 57346 count 8192

I studied the alert log file & related trace file .... but could not conclude what to do ?? Can any one suggest me what could be the solution...

ORACLE 10g R2
Windows 2003 Server
Database in NOARCHIVELOG mode.
I don't have the recent backup.The one i have is 20 days old.


Alert Log file contents : -
Wed Jun 02 20:22:19 2010
Aborting crash recovery due to error 333
Wed Jun 02 20:22:19 2010
Errors in file e:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_140.trc:
ORA-00333: redo log read error block 57346 count 8192

ORA-333 signalled during: ALTER DATABASE OPEN...
Wed Jun 02 20:37:15 2010
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Jun 02 20:43:53 2010
Shutting down instance: further logons disabled
Wed Jun 02 20:43:53 2010
Stopping background process CJQ0
Wed Jun 02 20:43:54 2010
Stopping background process MMNL
Wed Jun 02 20:43:55 2010
Stopping background process MMON
Wed Jun 02 20:43:56 2010
Shutting down instance (immediate)
License high water mark = 3
Wed Jun 02 20:43:56 2010
Stopping Job queue slave processes
Wed Jun 02 20:43:56 2010
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Jun 02 20:43:58 2010
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Wed Jun 02 20:43:58 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Trace file to read ora10g_ora_140.trc:-
Dump file e:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_140.trc
Wed Jun 02 20:22:18 2010
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 1
CPU                 : 2 - type 586, 1 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:3527M/4094M, Ph+PgF:4386M/5973M, VA:800M/2047M
Instance name: ora10g

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 140, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:() 2010-06-02 20:22:18.406
*** SESSION ID:(159.3) 2010-06-02 20:22:18.406
Successfully allocated 2 recovery slaves
Using 545 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 14261, block 2, scn 86974094
  cache-low rba: logseq 14260, block 4023
    on-disk rba: logseq 14262, block 3758, scn 86978396
  start recovery at logseq 14261, block 2, scn 86974094
ORA-00333: redo log read error block 57346 count 8192
ORA-00312: online log 2 thread 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO02.LOG'
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00333: redo log read error block 57346 count 8192
ORA-00312: online log 2 thread 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO02.LOG'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00333: redo log read error block 57346 count 8192
*** 2010-06-02 20:43:58.812
*** 2010-06-02 20:43:58.812 60680 kcrr.c
ARCH: Archival disabled due to shutdown: 1089
*** 2010-06-02 20:43:59.812 60680 kcrr.c
ARCH: Archival disabled due to shutdown: 1089
  • 2212 Views
  • Re: Database not starting,REDO log file error?
    RajeshLathwalGuru
    RajeshLathwal Jun 3, 2010 6:03 AM (in response to 698504)
    Looks like your redo log file is corrupted ..

    So you will have to clear it ..

    Please read
    http://youngcow.net/doc/oracle10g/backup.102/b14191/recoscen008.htm

    http://download.oracle.com/docs/cd/B13789_01/server.101/b10739/onlineredo.htm#i1006568

    Regards
    Rajesh
  • Re: Database not starting,REDO log file error?
    63967Expert
    63967 Jun 3, 2010 6:04 AM (in response to 698504)
    Probably a good time to open up a ticket with Oracle support on this one. This is not a time to try things to see if they work. You do not want to do something wrong and make recovery impossible.
  • Re: Database not starting,REDO log file error?
    ChinarGuru
    Chinar Jun 3, 2010 6:07 AM (in response to 698504)
    Hi, Try following and post result there.First backup database then.
    sqlplus "/as sysdba"
alter database clear unarchived  logfile  group 2 ;
alter database open;
  • Re: Database not starting,REDO log file error?
    HemantKChitaleOracle ACE
    HemantKChitale Jun 3, 2010 6:14 AM (in response to 698504)
    Error message "O/S-Error: (OS 2) The system cannot find the file specified." seems to say "MISSING !" rather than "CORRUPTED"


    Hemant K Chitale
    • Re: Database not starting,REDO log file error?
      698504Journeyer
      698504 Jun 3, 2010 6:17 AM (in response to HemantKChitale)
      The file is present & i checked it by myself.
      • Re: Database not starting,REDO log file error?
        ChinarGuru
        Chinar Jun 3, 2010 6:18 AM (in response to 698504)
        Did you try
        sqlplus "/as sysdba"
alter database clear unarchived  logfile  group 2 ;
alter database open;
        • Re: Database not starting,REDO log file error?
          698504Journeyer
          698504 Jun 3, 2010 6:55 AM (in response to Chinar)
          in reply to the command you told .. actually i tried them before also , but after you said u tried it again ...& here us the result...



          1* alter database clear unarchived logfile group 2
          SQL> /
          alter database clear unarchived logfile group 2
          *
          ERROR at line 1:
          ORA-01624: log 2 needed for crash recovery of instance ora10g (thread 1)
          ORA-00312: online log 2 thread 1:
          'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO02.LOG'


          1* alter database clear unarchived logfile group 3
          SQL> /

          Database altered.

          SQL> alter database open;
          alter database open
          *
          ERROR at line 1:
          ORA-00333: redo log read error block 57346 count 8192
          • Re: Database not starting,REDO log file error?
            ChinarGuru
            Chinar Jun 3, 2010 7:00 AM (in response to 698504)
            try this and post result there
            sqlplus "/as sysdba"
recover database using backup controlfile until cancel;

then apply 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO01.LOG' then

alter database open resetlogs;
            • Re: Database not starting,REDO log file error?
              698504Journeyer
              698504 Jun 3, 2010 7:15 AM (in response to Chinar)
              SQL> recover database using backup controlfile until cancel;
ORA-00279: change 86978180 generated at 06/02/2010 17:54:42 needed for thread 1
ORA-00289: suggestion :
E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORA10G\ARCHIVELOG\2010_06_03\O1_MF_
1_14262_%U_.ARC
ORA-00280: change 86978180 for thread 1 is in sequence #14262


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO01.LOG'
ORA-00310: archived log contains sequence 14261; sequence 14262 required
ORA-00334: archived log: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\REDO01.LOG'


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: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF'


SQL> 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: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.DBF'
              what next ???
  • Re: Database not starting,REDO log file error?
    698504Journeyer
    698504 Jun 3, 2010 7:18 AM (in response to 698504)
    Should i use the following command :-

    ALTER DATABASE DROP LOGFILE MEMBER '<path>\REDO02.log';

    and then ..

    ALTER DATABASE ADD LOGFILE MEMBER '<path>/redo02b.log'
    TO GROUP 2;

    what effect will be here ?? will the database start ?? is their any risk to loose database in it .... ???
    • Re: Database not starting,REDO log file error?
      Aman....Oracle ACE
      Aman.... Jun 3, 2010 7:29 AM (in response to 698504)
      Please don't just try things out. You were suggested already to raise an SR given that there was an error in the redo block of yours. That was the best thing to do ( still is) . With random things tried out, you may very well lose the entire database as well.

      HTH
      Aman....
      • Re: Database not starting,REDO log file error?
        698504Journeyer
        698504 Jun 3, 2010 7:33 AM (in response to Aman....)
        okay Aman.... Thanks ....
      • Re: Database not starting,REDO log file error?
        ChinarGuru
        Chinar Jun 3, 2010 7:59 AM (in response to Aman....)
        Hi,Aman this(following) may is not good way for opening database,but OP can try and open own database:
        @ DBA4 you will see from following example how you can open your database.In your online log files was corrupted,therefore you have to do following step by step then you database will open.
        SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance p (thread 1)
ORA-00312: online log 2 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\REDO02.LOG'


SQL> alter database backup controlfile to trace as 'D:\oracle\product\10.2.0\ora
data\P\c.txt';

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  125829120 bytes
Fixed Size                  1288100 bytes
Variable Size              75499612 bytes
Database Buffers           41943040 bytes
Redo Buffers                7098368 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "P" RESETLOGS   ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\REDO01.LOG'  SIZE 50M,
  9    GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\REDO02.LOG'  SIZE 50M,
 10    GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\REDO03.LOG'  SIZE 50M
 11  DATAFILE
 12    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\SYSTEM01.DBF',
 13    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\UNDOTBS01.DBF',
 14    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\SYSAUX01.DBF',
 15    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\USERS01.DBF',
 16    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\TS_TEST.ORA',
 17    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\TEST.ORA',
 18    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\TEST1.ORA'
 19  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> 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: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\SYSTEM01.DBF'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2032832 generated at 06/03/2010 12:42:29 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\P\ARCHIVELOG\2010_06_03\O1_MF_1_87_

%U_.ARC
ORA-00280: change 2032832 for thread 1 is in sequence #87


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: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\SYSTEM01.DBF'


ORA-01112: media recovery not started

then add *_allow_resetlogs_corruption=true* in pfile then startup database with pfile as

SQL> startup mount pfile=D:\oracle\product\10.2.0\admin\P\pfile\init.ora.pr
ORACLE instance started.

Total System Global Area  125829120 bytes
Fixed Size                  1288100 bytes
Variable Size              62916700 bytes
Database Buffers           54525952 bytes
Redo Buffers                7098368 bytes
Database mounted.
SQL> recover;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2032832 generated at 06/03/2010 12:42:29 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\P\ARCHIVELOG\2010_06_03\O1_MF_1_87_

%U_.ARC
ORA-00280: change 2032832 for thread 1 is in sequence #87


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: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\P\SYSTEM01.DBF'


ORA-01112: media recovery not started


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>
        finally remove allowresetlogs_corruption=true parameter from pfile.
  • Re: Database not starting,REDO log file error?
    698504Journeyer
    698504 Jun 4, 2010 5:16 AM (in response to 698504)
    Hello,
    Well all the point discussed in this forum are a great help to me. I am very thankful to all the professionals for giving their time & knowledge.

    I would like to add that i managed to take a cold backup of my DB in such a situation. Except the REDO02.log file all the files were copied from the system to the backup folder.

    Now my next step is I have installed windows server 2003 & oracle 10r r2 on a different server  can i recover the the database with the help of cold back up ?????

    how to recover database from cold backup ?  Previously i also fired a command "Alter database backup controlfile to trace" So now i have the controlfile backup in trace format, but i am not sure how to use it.

    Yes even i agree that hidden parameters should not be used, so i kept it as a last option.

    Thanks in advance

    Regards,
    DBA4
    • Re: Database not starting,REDO log file error?
      152149Explorer
      152149 Jun 4, 2010 6:15 AM (in response to 698504)
      You can only use the cold backup if it was taken before you had the problem. You need a consistent set of data files and if you took the backup after the problem occurred (even with the database shut down) you won't have a consistent backup.

      Your best bet is to talk to Oracle support. Failing that restore from the most recent cold back taken befor eth eproblem occurred and re-enter the data. With cold backups you always lose data - it's just a question of how much. Tools like RMAN make taking hot backups very easy so you should use that.
  • Re: Database not starting,REDO log file error?
    698504Journeyer
    698504 Jun 4, 2010 11:25 AM (in response to 698504)
    Thanks to all the professionals ...
Posted by at

2 comments:

  1. Unknown30 October 2013 at 10:23

    I am getting an error "shared memory realm doesn't exist" what is the solution for this error?

    ReplyDelete
  2. Amaranath30 October 2013 at 23:07

    Might be the DB is not up(not open). Check the DB status in v$instance view.

    ReplyDelete

Subscribe to: Post Comments (Atom)