7 Replies Latest reply on Oct 29, 2013 7:43 AM by JohnWatson

    unable to open the database, getting error ORA-01110

    Rizwan Shaukat

      SQL> SELECT STATUS FROM V$INSTANCE;

       

       

      STATUS

      ------------

      MOUNTED

       

       

      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;

      ALTER DATABASE OPEN RESETLOGS

      *

      ERROR at line 1:

      ORA-01110: data file 32: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ERPDB\CINVD04.DBF'

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

      ORA-27070: async read/write failed

      OSD-04016: Error queuing an asynchronous I/O request.

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

       

       

      i have issue the command, "RECOVER DATABASE USING BACKUP CONTROLFILE" but no gain.

       

      Pl suggest, what should i do

       

      Regards,

        • 1. Re: unable to open the database, getting error ORA-01110
          Aman....

          RizwanShaukat wrote:

           

          SQL> SELECT STATUS FROM V$INSTANCE;

           

           

          STATUS

          ------------

          MOUNTED

           

           

          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;

          ALTER DATABASE OPEN RESETLOGS

          *

          ERROR at line 1:

          ORA-01110: data file 32: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ERPDB\CINVD04.DBF'

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

          ORA-27070: async read/write failed

          OSD-04016: Error queuing an asynchronous I/O request.

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

           

           

          i have issue the command, "RECOVER DATABASE USING BACKUP CONTROLFILE" but no gain.

           

          Pl suggest, what should i do

           

          Regards,

          Why "recover using backup controlfile" ? Looks like the disk is having the issue.

           

          Error:  "ORA-27070: skgfdisp: async read/write failed"
            Cause: aioread/aiowrite system call returned error. Additional information indicates starting block number of I/O.

           

          Aman....

          1 person found this helpful
          • 2. Re: unable to open the database, getting error ORA-01110
            saurabh

            check for the existance of the file on the disk.

             

            Also check the os error log file for any harddisk related error is mentioned.

             

            Also post the last few lines of alert log.

             

            Also let us know how did you restore the file.

             


            1 person found this helpful
            • 3. Re: unable to open the database, getting error ORA-01110
              Rizwan Shaukat

              Alert log :

                db_name                  = erpdb

                open_cursors             = 300

                pga_aggregate_target     = 403701760

              PMON started with pid=2, OS id=21108

              PSP0 started with pid=3, OS id=21112

              MMAN started with pid=4, OS id=21120

              DBW0 started with pid=5, OS id=21136

              LGWR started with pid=6, OS id=20668

              CKPT started with pid=7, OS id=20664

              SMON started with pid=8, OS id=21140

              RECO started with pid=9, OS id=21144

              CJQ0 started with pid=10, OS id=21148

              MMON started with pid=11, OS id=21152

              MMNL started with pid=12, OS id=21160

              Tue Oct 29 09:47:13 2013

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

              starting up 1 shared server(s) ...

              Tue Oct 29 09:47:14 2013

              ALTER DATABASE   MOUNT

              Tue Oct 29 09:47:18 2013

              Setting recovery target incarnation to 3

              Tue Oct 29 09:47:18 2013

              Successful mount of redo thread 1, with mount id 4274192802

              Tue Oct 29 09:47:18 2013

              Database mounted in Exclusive Mode

              Completed: ALTER DATABASE   MOUNT

              Tue Oct 29 09:47:19 2013

              ALTER DATABASE OPEN

              ORA-1589 signalled during: ALTER DATABASE OPEN...

              Tue Oct 29 09:47:48 2013

              alter database open resetlogs

              Tue Oct 29 09:47:50 2013

              RESETLOGS is being done without consistancy checks. This may result

              in a corrupted database. The database should be recreated.

              ORA-1110 signalled during: alter database open resetlogs...

              Tue Oct 29 09:53:53 2013

               

              Even viewer:

              The device, \Device\Harddisk0\DR0, has a bad block.

               

               

              For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

               

              File exist on disk

               

              Pl suggest remedy

              • 4. Re: unable to open the database, getting error ORA-01110
                Rizwan Shaukat

                I read many post on forums, they suggest that you need to perform recover using controlfile, that's why i try to recover database.

                • 5. Re: unable to open the database, getting error ORA-01110
                  saurabh

                  you harddisk has a bad block in it. The issue might be because of that bad block. so what you can do is restore the database on any other server or change that harddisk. Or you can ask you system administrator for help.

                  • 6. Re: unable to open the database, getting error ORA-01110
                    TSharma-Oracle

                    This is the problem.

                    O/S-Error: (OS 23) Data error (cyclic redundancy check). and The device, \Device\Harddisk0\DR0, has a bad block.

                     

                     

                    That error is most likely the result of your hard drive having hardware related problems. A CRC error indicates that the hard drive was unable to retrieve the data stored at those locations. It has nothing to do with the Oracle database or its configuration. Ask you system admin or your hardware vendor.

                    • 7. Re: unable to open the database, getting error ORA-01110
                      JohnWatson

                      Just follow the standard procedure that every DBA knows. Take the damaged file off line:

                       

                      alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ERPDB\CINVD04.DBF' offline;

                       

                      Then open the database. Then restore and recover the file.