1 2 Previous Next 17 Replies Latest reply on Apr 9, 2013 9:48 AM by 1001932

    redo logs issue...

    Efarah-Oracle
      Hi Gurus,
      I have a development database (study purpose) on which I run some apex applications.
      it's 11g and runs in a regular PC with Windows Vista Home Edition (service pack 2).
      because it's just for study I never bothered to backup it. (I know, I'm an idiot!).
      few days ago, my PC crashed and there were some disk issues, already fixed.

      the actual status of the database is:
      it mounts but doesn't open.

      SQL> select open_mode from v$database;

      OPEN_MODE
      --------------------
      MOUNTED

      SQL> select * from v$log;

      GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
      ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
      1 1 1 52428800 512 1 NO CURRENT 12285781 09-APR-12 2.8147E+14
      3 1 0 52428800 512 1 YES UNUSED 0 0
      2 1 0 52428800 512 1 YES UNUSED 0 0

      SQL> select * from v$logfile;

      GROUP# STATUS TYPE MEMBER IS_
      ---------- ------- ------- -------------------------------------------------------------------------------- ---
      3 ONLINE M:\SOFTWARES\ORACLE\ORADATA\ORAEDU\REDO03.LOG NO
      2 ONLINE M:\SOFTWARES\ORACLE\ORADATA\ORAEDU\REDO02.LOG NO
      1 ONLINE M:\SOFTWARES\ORACLE\ORADATA\ORAEDU\REDO01.LOG NO


      when I try to open, it returns:
      SQL> alter database open;
      alter database open
      *
      ERROR at line 1:
      ORA-03113: end-of-file on communication channel
      Process ID: 300
      Session ID: 5 Serial number: 7

      I have gone through some of the posts in the forum with similar issues and have tried a few steps... (such as "recover database until cancel" and "open resetlogs") but apparently there's no success on that.

      listener is up an running (tnsping against the instance works fine).

      in case someone can help, 3 questions below.

      1) what's the recommended approach here?
      I was wondering, before I mess up (MORE) with my data, if I could create new redo logs / groups and expect this to work ?

      2) without any backup should I throw my "study apps" away or I still have some options to revover them?

      other question is: 3) in case this DB is gone. Is there ANY way to get my data (DDL and data) as well as my apex code?
      I have created all my apps in different tablespaces.. .but have never done this process of moving them into another DB... that's the reason for asking...
        • 1. Re: redo logs issue...
          Efarah-Oracle
          ops.... hit send before the most important...

          thanks in advance ...
          • 2. Re: redo logs issue...
            mBk77
            post the last few lines of your alert log file.
            • 3. Re: redo logs issue...
              Efarah-Oracle
              Hi mBk77,
              I have tried some more things... latest status is still "mount but doesnt open".
              but I did a "recover database" and tried to open in read mode only. then I've got a ora600 (which is not good).

              Mon Apr 09 22:34:50 2012
              ALTER DATABASE RECOVER database
              Media Recovery Start
              started logmerger process
              Mon Apr 09 22:34:51 2012
              Media Recovery failed with error 264
              Recovery Slave PR00 previously exited with exception 283
              ORA-283 signalled during: ALTER DATABASE RECOVER database ...
              Mon Apr 09 22:40:24 2012
              alter database open read only
              AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
              Errors in file m:\softwares\oracle\diag\rdbms\oraedu\oraedu\trace\oraedu_ora_6408.trc:
              ORA-16005: database requires recovery
              ORA-16005 signalled during: alter database open read only...
              Mon Apr 09 22:41:01 2012
              ALTER DATABASE RECOVER database
              Media Recovery Start
              started logmerger process
              Mon Apr 09 22:41:01 2012
              Media Recovery failed with error 264
              Recovery Slave PR00 previously exited with exception 283
              ORA-283 signalled during: ALTER DATABASE RECOVER database ...
              alter database open read only
              Errors in file m:\softwares\oracle\diag\rdbms\oraedu\oraedu\trace\oraedu_ora_6408.trc:
              ORA-16005: database requires recovery
              ORA-16005 signalled during: alter database open read only...
              Mon Apr 09 22:41:22 2012
              alter database open noresetlogs
              Beginning crash recovery of 1 threads
              parallel recovery started with 3 processes
              Started redo scan
              Completed redo scan
              read 26 KB redo, 0 data blocks need recovery
              Started redo application at
              Thread 1: logseq 3, block 3
              Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
              Mem# 0: M:\SOFTWARES\ORACLE\ORADATA\ORAEDU\REDO03.LOG
              Completed redo application of 0.00MB
              Completed crash recovery at
              Thread 1: logseq 3, block 55, scn 12345990
              0 data blocks read, 0 data blocks written, 26 redo k-bytes read
              Mon Apr 09 22:41:25 2012
              Thread 1 advanced to log sequence 4 (thread open)
              Thread 1 opened at log sequence 4
              Current log# 1 seq# 4 mem# 0: M:\SOFTWARES\ORACLE\ORADATA\ORAEDU\REDO01.LOG
              Successful open of redo thread 1
              MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
              Mon Apr 09 22:41:25 2012
              SMON: enabling cache recovery
              Successfully onlined Undo Tablespace 2.
              Dictionary check beginning
              Dictionary check complete
              Verifying file header compatibility for 11g tablespace encryption..
              Verifying 11g file header compatibility for tablespace encryption completed
              SMON: enabling tx recovery
              Errors in file m:\softwares\oracle\diag\rdbms\oraedu\oraedu\trace\oraedu_smon_6160.trc (incident=158668):
              ORA-00600: internal error code, arguments: [4194], [], [
              Incident details in: m:\softwares\oracle\diag\rdbms\oraedu\oraedu\incident\incdir_158668\oraedu_smon_6160_i158668.trc
              Database Characterset is WE8MSWIN1252
              No Resource Manager plan active
              Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x3ECF3343] [PC:0x7411343, 07411343]
              Mon Apr 09 22:41:28 2012
              Errors in file m:\softwares\oracle\diag\rdbms\oraedu\oraedu\cdump\oraeducore.log
              ORA-07445: caught exception [ACCESS_VIOLATION] at [07411343] [0x07411343]
              Mon Apr 09 22:41:29 2012
              Trace dumping is performing id=[cdmp_20120409224129]
              Doing block recovery for file 3 block 808
              Mon Apr 09 22:41:30 2012
              PMON (ospid: 4008): terminating the instance due to error 397
              Instance terminated by PMON, pid = 4008

              hope it helps,

              thanks
              • 4. Re: redo logs issue...
                baskar.l
                Hi,

                Its better to raise a service request with Oracle support for the Ora-600 and Ora-07445 error reported in the alert log.

                Thanks,
                Baskar.l
                • 5. Re: redo logs issue...
                  Tubby
                  Advice that won't help you with your current predicament, but maybe help avoid future ones.

                  If this is only for study purposes i'd recommend utilizing virtualization ... something like Oracles' VirtualBox. I'm not aware of any Oracle databases being supported on Windows Vista, so you'd want to pick an OS (virtualized here) that is certified and supported.

                  Not that you have a support contract, but you're much more likely to find help when you hit issues ... and hopefully avoid issues altogether. Another bonus with virtualization is that you can take a snapshot of the virtual machine whenever you want, and restore that if you totally mess up the environment.

                  Oracle also offers a plethora of prebuilt VM's (for VirtualBox) which are quite useful.

                  http://www.oracle.com/technetwork/community/developer-vm/index.html

                  Cheers,
                  • 6. Re: redo logs issue...
                    817202
                    Hi efarah,


                    Have you checked the alert log file what are the message there
                    • 7. Re: redo logs issue...
                      Dominique L.
                      Hi,

                      Just a tip

                      At startup, when I have a crash at recovery, I try first to increase the LOG_BUFFER parameter.
                      Example :
                      show parameter log_buffer
                      NAME TYPE VALUE
                      ------------------------------------ ----------- ------------------------------
                      log_buffer integer 2097152
                      alter system set log_buffer=4097152 scope=spfile;

                      System altered.

                      And restart the instance...
                      • 8. Re: redo logs issue...
                        EdStevens
                        926834 wrote:
                        Hi,

                        Just a tip

                        At startup, when I have a crash at recovery, I try first to increase the LOG_BUFFER parameter.
                        Example :
                        show parameter log_buffer
                        NAME TYPE VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_buffer integer 2097152
                        alter system set log_buffer=4097152 scope=spfile;

                        System altered.

                        And restart the instance...
                        That kind of advice is like me saying "whenever my car won't start I put gas in the tank." Yeah, ok. But suppose the reason it didn't start was because the batter was dead.
                        • 9. Re: redo logs issue...
                          Uwehesse-Oracle
                          Nice analogy :-)

                          In this particular case, we have not (only) a crash recovery - where increasing the log buffer is not completely absurd, although not necessary - but files have been damaged.
                          The system is not able to fix that itself like with an instance/crash recovery.

                          This "tip" will not solve the OPs problem therefore.

                          Kind regards
                          Uwe Hesse

                          "Don't believe it, test it!"
                          http://uhesse.com
                          • 10. Re: redo logs issue...
                            user097815
                            why dont you re-create the control file and open database in resetlogs...." I think " thats your only option here, as you dont have a good backup....you might loose some data but as long as your datafile are intact you sould be good....

                            something like below

                            http://myracle.wordpress.com/2008/01/11/recover-database-without-control-files-and-redo-log-files/
                            1 person found this helpful
                            • 11. Re: redo logs issue...
                              Efarah-Oracle
                              yes Tubby. I'm aware of VirtualBox. I had this plan already. maybe was waiting for something to crash to do it.
                              thanks anyway.
                              • 12. Re: redo logs issue...
                                Mark Malakanov (user11181920)
                                Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x3ECF3343] [PC:0x7411343, 07411343]
                                ORA-07445: caught exception [ACCESS_VIOLATION] at [07411343] [0x07411343]
                                ACCESS_VIOLATION. Check if your Oracle service account has access to all files: oracle binaries, control, log and data files ..
                                Trace dumping is performing id=[cdmp_20120409224129]
                                Doing block recovery for file 3 block 808
                                select * from v$datafile where file#=3;

                                In my case file# is SYSAUX tablespace. You can try to take its files offline temporarily.
                                If you can open DB after that, export your data ASAP.
                                Then drop this DB and create a new one and load your data into it.
                                • 13. Re: redo logs issue...
                                  Efarah-Oracle
                                  Hi Gurus,
                                  I was able to OPEN the database after following a few steps...

                                  1) I've generated a new script for my control files, using
                                  ALTER DATABASE BACKUP CONTROLFILE TO TRACE
                                  2) then, I realized my undo tablespace was corrupted, which made me to create a new one.
                                  create UNDO tablespace UNDOTBS2 datafile ''.\ORADATA\ORAEDU\undotbs02.dbf' size 1024m REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 1024M;
                                  ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2';

                                  3) then, my temporary tablespace was also corrupted, which made me create a new one, as well
                                  ALTER TABLESPACE myg_temp ADD TEMPFILE 'M:\softwares\oracle\oradata\oraEdu\MYG_TEMP2' SIZE 150M;

                                  point now is that even with the database OPENED, I can query and even open my application through APEX. though, if I try to export (using EXPDP, since it has some blob columns) it returns INDEX errors.

                                  C:\Dump_data>expdp

                                  Export: Release 11.2.0.1.0 - Production on Sun Apr 29 01:05:59 2012

                                  Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                                  Username: myg
                                  Password:

                                  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                                  ORA-31626: job does not exist
                                  ORA-31633: unable to create master table "MYG.SYS_EXPORT_SCHEMA_05"
                                  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
                                  ORA-06512: at "SYS.KUPV$FT", line 1020
                                  ORA-08102: index key not found, obj# 39, file 1, block 61019 (2)

                                  if I try to use DBV it returns sy:
                                  DBV-00100: Specified FILE (.\ORADATA\ORAEDU\SYSTEM01.DBF) not accessible

                                  I've researched on metalink and it says to use DBMS_REPAIR, which I use and get a
                                  ERROR at line 1:
                                  ORA-08102: index key not found, obj# 39, file 1, block 61019 (2)
                                  ORA-06512: at "SYS.DBMS_SQL", line 1053
                                  ORA-06512: at "SYS.DBMS_REPAIR", line 245
                                  ORA-06512: at line 2

                                  any idea?

                                  since it doesn't seem to be related to the original post, should I open it as a new thread?
                                  1 2 Previous Next