Re: Block corruption while rebuilding the tablespace

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Tue, 15 Nov 2011 15:56:56 +0530
Message-ID: <CAJsOtB4TEAJ1HzBwhDWcq8HCWC9LGBSMzP=bgo9+Ca8+UB9KLg_at_mail.gmail.com>

hello anant,
were you able to query these tables when they were at original locations.. if this particular table is only having the problem then can you check with dbv and also can you check at os level.
similarly can check the new tablespace and its dbfile with dbv and with say fsck.

then you have the options of either moving, or export import, create the same tables in new tablespace or sqlldr.. etc..

thanks...subodh

On 15 November 2011 12:08, Shastry(DBA) <shastry17_at_gmail.com> wrote:

> Thanks Tim and Sidney for your inputs,
> I am sorry that i didnt provide the complete information like the OS, DB
> version and here it is:
>
> OS - Linux x86_64
> Linux testdb.6.18-128.1.14.el5 #1 SMP Mon Jun 1 15:52:58 EDT 2009 x86_64
> x86_64 x86_64 GNU/Linux
> DB - 11.2.0.1.2
>
> Regards,
> Anant
>
> On Mon, Nov 14, 2011 at 10:16 PM, Tim Gorman <tim_at_evdbt.com> wrote:
>
> > Anant,
> >
> > It would be helpful to provide OS, OS version, and database version, so
> > let's assume 10g or above...
> >
> > One thing you might consider doing is reading up on the package
> > DBMS_REPAIR, although despite the name, the package doesn't "repair"
> > corruption, it just "fences" the corrupted blocks so that the rest of
> > the object can be used. I've always likened this to taking your pet to
> > the veterinarian to get "fixed"; the pet returns with something missing
> > but is less troublesome. In this respect then, if you just want to
> > recover the rest of the table and give up any data in that corrupted
> > block, then consider the SKIP_CORRUPT_BLOCKS procedure in DBMS_REPAIR,
> > which may allow your ALTER TABLE ... MOVE command to succeed with the
> > move, but without the data from the corrupted block(s).
> >
> > If you want to try to recover the data which used to be in that block,
> > then you'll need to read up on RMAN "object point-in-time" recovery
> > techniques or, as has already been suggested, the BLOCKRECOVER
> capability.
> >
> > Good luck!
> >
> > -Tim
> >
> >
> >
> > On 11/13/2011 10:20 PM, Shastry(DBA) wrote:
> > > Hello Gurus,
> > > I am trying to rebuild the tablespace using the move and i could move
> all
> > > the objects execpt the below table which is erroring out with block
> > > corruption.
> > > Please suggest me on this to go ahead what best options are available
> to
> > > do. Shall i do export import for this table alone.
> > >
> > > Problem Description: SQL> alter table
> > XDSBIADM.CV_BOOKINGS_DETAILS_ALL_DATA
> > > move tablespace XDSBIADM_DATA_NEW parallel 8;
> > > alter table XDSBIADM.CV_BOOKINGS_DETAILS_ALL_DATA move tablespace
> > > XDSBIADM_DATA_NEW parallel 8
> > > *
> > > ERROR at line 1:
> > > ORA-12801: error signaled in parallel query server P007
> > > ORA-01578: ORACLE data block corrupted (file # 358, block # 267)
> > > ORA-01110: data file 358:
> > '/oracle/oradata/JPRDEV/fs28/xdsbiadm_data_22.dbf'
> > > ORA-26040: Data block was loaded using the NOLOGGING option
> > >
> > > Thanks,
> > > Anant
> > >
> > >
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> > >
> > >
> > >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
=============================================
TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
=============================================


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 15 2011 - 04:26:56 CST