8 Replies Latest reply: Jan 31, 2013 6:34 AM by EdStevens RSS

    Recover datafile

    950166
      Hi all,

      I am exporting my database 9.2.0.8 to be installed in 11g
      but i have encountered an error: ORA-01578: Oracle Data block corrupted (file # 435, block # 67616)
      so i stopped my export and try to recover the block, and i also try to recover the database
      but i encountered:

      ORA-00283: recovery session canceled due to errors
      ORA-00264: no recovery required

      How can i recover when there is no recovery required?

      by the way there is no RMAN (standard edition only)

      Thanks for your future replies,
        • 1. Re: Recover datafile
          Aman....
          sikakaito wrote:
          Hi all,

          I am exporting my database 9.2.0.8 to be installed in 11g
          but i have encountered an error: ORA-01578: Oracle Data block corrupted (file # 435, block # 67616)
          so i stopped my export and try to recover the block, and i also try to recover the database
          but i encountered:

          ORA-00283: recovery session canceled due to errors
          ORA-00264: no recovery required

          How can i recover when there is no recovery required?

          by the way there is no RMAN (standard edition only)
          Are you sure that the file is corrupted? What's the output with the DBVerify utility? And for the recovery, you need to be in the archive log mode and should have a proper backup. If you don't have RMAN, you won't be able to do the block level recovery.

          Aman....
          • 2. Re: Recover datafile
            Hemant K Chitale
            Recovery would be done by first restoring a backup of the datafile -- such that the backup was created before the corruption got introduced. So, you'd have to guesstimate when the corruption was introduced.

            You should first try to see if you can identify and fix the segment (table or index) and data in the corrupt block before attempting a datafile recovery.

            See Oracle Support note Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1] and log an SR with Oracle Support for assistance.


            Hemant K Chitale
            • 3. Re: Recover datafile
              950166
              yup,it is certainly corrupted.


              DBVERIFY - Verification complete

              Total Pages Examined : 153600
              Total Pages Processed (Data) : 150784
              Total Pages Failing (Data) : 0
              Total Pages Processed (Index): 0
              Total Pages Failing (Index): 0
              Total Pages Processed (Other): 1
              Total Pages Processed (Seg) : 0
              Total Pages Failing (Seg) : 0
              Total Pages Empty : 2559
              Total Pages Marked Corrupt   : 256
              Total Pages Influx : 0
              Highest block SCN : 190120347 (0.190120347)
              • 4. Re: Recover datafile
                950166
                Hi all,

                thanks for your replies.

                I have tested all our previous back up..and our backup had errors also..
                and
                I have tried restoring, the data file, same error ORA-00264: no recovery required.

                i'll be reading the note..

                thanks
                • 5. Re: Recover datafile
                  Hemant K Chitale
                  Total Pages Marked Corrupt : 256
                  Means that have already been marked corrupt in the past. These blocks are unused. Tables and Indexes would not be assigned to use these blocks.

                  Your Total Pages Failing counts are zero. That indicates that Oracle DBV doesn't find a corrupt Table or Index block.

                  Hemant K Chitale
                  • 6. Re: Recover datafile
                    950166
                    Hemant K Chitale wrote:
                    Total Pages Marked Corrupt : 256
                    Means that have already been marked corrupt in the past. These blocks are unused. Tables and Indexes would not be assigned to use these blocks.

                    Your Total Pages Failing counts are zero. That indicates that Oracle DBV doesn't find a corrupt Table or Index block.

                    Hemant K Chitale
                    Ah.. thank you for this info.
                    Sorry for having many questions. Does it mean that table is ok for export?
                    • 7. Re: Recover datafile
                      Hemant K Chitale
                      It would mean that the corrupted blocks will not be used for new rows in tables / indexes.

                      Technically, since there are no Failing Pages, your existing Table and Index segments do not have corruption. It is strange that you encountered corruption during the export. Is it possible that the table that failed in the export has since been rebuilt (causing it to be rebuilt over good blocks that are not Failing Pages) ?

                      Yes, you should retry the export.


                      Hemant K Chitale
                      • 8. Re: Recover datafile
                        EdStevens
                        sikakaito wrote:
                        Hi all,

                        I am exporting my database 9.2.0.8 to be installed in 11g
                        but i have encountered an error: ORA-01578: Oracle Data block corrupted (file # 435, block # 67616)
                        so i stopped my export and try to recover the block, and i also try to recover the database
                        but i encountered:

                        ORA-00283: recovery session canceled due to errors
                        ORA-00264: no recovery required

                        How can i recover when there is no recovery required?

                        by the way there is no RMAN (standard edition only)
                        What does standard edition have to do with it? rman comes with every edition of the Oracle rdbms. It is not a just an 'enterprise edition' feature. If you are not using rman, you need to correct that ASAP.
                        >
                        Thanks for your future replies,