13 Replies Latest reply on Dec 27, 2013 12:50 PM by Eduardo Legatti

    Database not able to startup

    user5261744

      hi, dear all

       

      i could not be able to startup my database, the following information was extracted from alert_orcl.log

       

      Thu Dec 26 13:40:55 2013

      ORACLE V9.2.0.1.0 - Production vsnsta=0

      vsnsql=12 vsnxtr=3

      Windows 2000 Version 5.2 Service Pack 2, CPU type 586

      Thu Dec 26 13:40:55 2013

      Starting ORACLE instance (normal)

      Thu Dec 26 13:40:55 2013

      Running with 1 strand for Non-Enterprise Edition

      LICENSE_MAX_SESSION = 0

      LICENSE_SESSIONS_WARNING = 0

      SCN scheme 2

      Using log_archive_dest parameter default value

      Running with 1 strand for Non-Enterprise Edition

      LICENSE_MAX_USERS = 0

      SYS auditing is disabled

      Starting up ORACLE RDBMS Version: 9.2.0.1.0.

      System parameters with non-default values:

        processes                = 150

        timed_statistics         = TRUE

        shared_pool_size         = 50331648

        large_pool_size          = 8388608

        java_pool_size           = 33554432

        control_files            = D:\ora9i\oradata\ORCL\CONTROL01.CTL, D:\ora9i\oradata\ORCL\CONTROL02.CTL, D:\ora9i\oradata\ORCL\CONTROL03.CTL

        db_block_size            = 8192

        db_cache_size            = 25165824

        compatible               = 9.2.0.0.0

        db_file_multiblock_read_count= 16

        fast_start_mttr_target   = 0

        undo_management          = AUTO

        undo_tablespace          = UNDOTBS1

        undo_retention           = 10800

        remote_login_passwordfile= EXCLUSIVE

        db_domain                =

        instance_name            = ORCL

        dispatchers              = (PROTOCOL=TCP) (SERVICE=ORCLXDB)

        job_queue_processes      = 10

        hash_join_enabled        = TRUE

        background_dump_dest     = D:\ora9i\admin\ORCL\bdump

        user_dump_dest           = D:\ora9i\admin\ORCL\udump

        core_dump_dest           = D:\ora9i\admin\ORCL\cdump

        sort_area_size           = 524288

        db_name                  = ORCL

        open_cursors             = 300

        star_transformation_enabled= FALSE

        query_rewrite_enabled    = FALSE

        pga_aggregate_target     = 25165824

        aq_tm_processes          = 1

      PMON started with pid=2

      DBW0 started with pid=3

      LGWR started with pid=4

      CKPT started with pid=5

      SMON started with pid=6

      RECO started with pid=7

      CJQ0 started with pid=8

      QMN0 started with pid=9

      Thu Dec 26 13:40:58 2013

      starting up 1 shared server(s) ...

      starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

      Oracle Data Guard is not available in this edition of Oracle.

      Thu Dec 26 13:40:59 2013

      alter database mount exclusive

      Thu Dec 26 13:41:03 2013

      Successful mount of redo thread 1, with mount id 1363032811.

      Thu Dec 26 13:41:03 2013

      Database mounted in Exclusive Mode.

      Completed: alter database mount exclusive

      Thu Dec 26 13:41:03 2013

      alter database open

      Thu Dec 26 13:41:04 2013

      Beginning crash recovery of 1 threads

      Thu Dec 26 13:41:04 2013

      Started recovery at

      Thread 1: logseq 385, block 3, scn 0.0

      Recovery of Online Redo Log: Thread 1 Group 3 Seq 385 Reading mem 0

        Mem# 0 errs 0: D:\ORA9I\ORADATA\ORCL\REDO03.LOG

      Thu Dec 26 13:41:06 2013

      Ended recovery at

      Thread 1: logseq 385, block 410, scn 0.110917628

      63 data blocks read, 63 data blocks written, 407 redo blocks read

      Crash recovery completed successfully

      Thu Dec 26 13:41:06 2013

      Thread 1 advanced to log sequence 386

      Thread 1 opened at log sequence 386

        Current log# 1 seq# 386 mem# 0: D:\ORA9I\ORADATA\ORCL\REDO01.LOG

      Successful open of redo thread 1.

      Thu Dec 26 13:41:07 2013

      SMON: enabling cache recovery

      Thu Dec 26 13:41:08 2013

      Undo Segment 1 Onlined

      Undo Segment 2 Onlined

      Undo Segment 3 Onlined

      Undo Segment 4 Onlined

      Undo Segment 5 Onlined

      Undo Segment 6 Onlined

      Undo Segment 7 Onlined

      Undo Segment 8 Onlined

      Undo Segment 9 Onlined

      Undo Segment 10 Onlined

      Successfully onlined Undo Tablespace 1.

      Thu Dec 26 13:41:08 2013

      SMON: enabling tx recovery

      Thu Dec 26 13:41:08 2013

      Database Characterset is WE8MSWIN1252

      Thu Dec 26 13:41:32 2013

      Errors in file d:\ora9i\admin\orcl\udump\orcl_ora_3128.trc:

      ORA-01115: IO error reading block from file 1 (block # 39273)

      ORA-01110: data file 1: 'D:\ORA9I\ORADATA\ORCL\SYSTEM01.DBF'

      ORA-27091: skgfqio: unable to queue I/O

      ORA-27070: skgfdisp: async read/write failed

      OSD-04006: ReadFile() failure, unable to read from file

      O/S-Error: (OS 23) Data error (cyclic redundancy check).

       

       

      Thu Dec 26 13:41:32 2013

      Error 1115 happened during db open, shutting down database

      USER: terminating instance due to error 1115

      Thu Dec 26 13:41:32 2013

      Errors in file d:\ora9i\admin\orcl\bdump\orcl_smon_3584.trc:

      ORA-00604: error occurred at recursive SQL level 1

      ORA-01115: IO error reading block from file 1 (block # 39273)

      ORA-01110: data file 1: 'D:\ORA9I\ORADATA\ORCL\SYSTEM01.DBF'

      ORA-27091: skgfqio: unable to queue I/O

      ORA-27070: skgfdisp: async read/write failed

      OSD-04006: ReadFile() failure, unable to read from file

      O/S-Error: (OS 23) Data error (cyclic redundancy check).

       

       

      Thu Dec 26 13:41:40 2013

      Errors in file d:\ora9i\admin\orcl\bdump\orcl_pmon_1012.trc:

      ORA-01115: IO error reading block from file  (block # )

       

       

      Instance terminated by USER, pid = 3128

      ORA-1092 signalled during: alter database open...

       

      thanks.

        • 1. Re: Database not able to startup
          observer_83

          What was the failure here? Please state what change was done before bringing up the DB.

          • 2. Re: Database not able to startup
            Aman....

            Errors in file d:\ora9i\admin\orcl\udump\orcl_ora_3128.trc:

            ORA-01115: IO error reading block from file 1 (block # 39273)

            ORA-01110: data file 1: 'D:\ORA9I\ORADATA\ORCL\SYSTEM01.DBF'

            ORA-27091: skgfqio: unable to queue I/O

            ORA-27070: skgfdisp: async read/write failed

            OSD-04006: ReadFile() failure, unable to read from file

            O/S-Error: (OS 23) Data error (cyclic redundancy check).

            So what's the status of this file and also, do you have a valid backup of this db and whether archive log mode is enabled or not?

             

            Aman....

            • 3. Re: Database not able to startup
              TSharma-Oracle

              Firstly, Your database version is currently not being supported.

              Secondly, Looks like your hard drive has developed one or more bad sectors,have your system admin checked your storage before recovering the database.

              • 4. Re: Database not able to startup
                Eduardo Legatti

                Hi,

                 

                ORA-01115: IO error reading block from file 1 (block # 39273)

                ORA-01110: data file 1: 'D:\ORA9I\ORADATA\ORCL\SYSTEM01.DBF'

                 

                It seems that SYSTEM01.DBF data file gets corrupted. This data file pertains to the SYSTEM tablespace, Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created.  The SYSTEM tablespace always contains the data dictionary tables for the entire database. You need to restore and recover your database from a backup. If you don't have a physical backup, maybe you can try to restore a logical backup (dump) of your schemas into another database using the imp utility.

                 

                Now, the questions are: Do you have a physical backup? Do you have a logical backup (dump) of your database schemas?

                 

                Cheers

                 

                Legatti

                • 5. Re: Database not able to startup
                  Aman....

                  observer_83 wrote:

                   

                  What was the failure here? Please state what change was done before bringing up the DB.

                  Well, this is the error reported by OP,

                  Errors in file d:\ora9i\admin\orcl\udump\orcl_ora_3128.trc:

                  ORA-01115: IO error reading block from file 1 (block # 39273)

                  ORA-01110: data file 1: 'D:\ORA9I\ORADATA\ORCL\SYSTEM01.DBF'

                  ORA-27091: skgfqio: unable to queue I/O

                  ORA-27070: skgfdisp: async read/write failed

                  OSD-04006: ReadFile() failure, unable to read from file

                  O/S-Error: (OS 23) Data error (cyclic redundancy check).

                   

                  Aman....

                  • 6. Re: Database not able to startup
                    Shivendra Narain Nirala

                    Hi

                     

                    From the alert log file,archival started and starting arch processes are not present , clearly indicates that your database in noarchive log mode . Restore your whole db from old backup .

                     

                    Regards

                     

                    Shivendra Narain Nirala

                    • 7. Re: Database not able to startup
                      jgarry

                      I concur with TSharma, do a chkdsk first.   If you do manage to get the file fixed, immediately do a backup, it's a bad sign when disk drives start complaining.

                      • 8. Re: Database not able to startup
                        Hemant K Chitale

                        >Beginning crash recovery of 1 threads

                        Indicates that the database had NOT been cleanly shutdown prior to this startup attempt.  Was there a disk error ?  Did the database crash ?  Was the disk error corrected / rectified ?  Was the storage checked for errors ?

                         

                         

                        Hemant K Chitale


                        • 9. Re: Database not able to startup
                          user5261744

                          Hi, Legatti

                           

                          I do not have any physical backup, i have logical dump file. However, the logical dump file only contain user pre-defined tables, therefore, i don't think it help, right?

                           

                          Thanks from Jerry

                          • 10. Re: Database not able to startup
                            Hemant K Chitale

                            Before you even begin to restore / recover / reimport your data get your storage checked and corrected.  The OS has reported CRC errors. These may still recur until and unless the storage is checked and bad blocks are marked corrupt so that the OS and Oracle do not reuse the bad blocks.

                             

                             

                            Hemant K Chitale


                            • 11. Re: Database not able to startup
                              sb92075

                              >I do not have any physical backup,

                              above indicates that the data that was held in the DB was valued at zero.

                              Accidents happens & you should plan for recovery after one occurs.

                              • 12. Re: Database not able to startup
                                user5261744

                                Hi, Hemant

                                 

                                thank you very much for your advise.

                                 

                                from Jerry

                                • 13. Re: Database not able to startup
                                  Eduardo Legatti

                                  Hi,

                                   

                                  I do not have any physical backup, i have logical dump file. However, the logical dump file only contain user pre-defined tables, therefore, i don't think it help, right?

                                   

                                  Big mistake... You need to create backup/recovery plan. Think about that! What do you mean by "pre-defined tables"? You need the most recent dump that contains the schemas that is important for you. Then you can try to import this dump in another database,

                                   

                                  Cheers

                                   

                                  Legatti