datafile went offline.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: datafile went offline.

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    datafile went offline.

    Hi All,
    Due to some reason(not know yet), one of the datafile went offline and cannot be bought offile as the file header is corrupt and does not match with control file.
    Due to this two tables were effected. But we were able to access some of the records becuase they were part of other datafile and would fail when they try to access data in the offline datafile.

    One of the table had a CLOB column, so customers are able to see all other column data except the CLOB column data.

    So, when a records is saved it should save either completely or rollback completely. How can this CLOB column be missing.
    Even if this clob column was part of the offline datafile, it should not show the enitre record, how can other columns data be visible and not this CLOB column value.

    Can any one please explain. Our customers are bombarding us.

    We are using oracle 8.1.7.4 and solaris 5.8.

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    how do you know the clobs were in the broken datafile

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    This clob column is part of the tablespace contaning the offline datafile.

    Is it possible that this clob column value was not commitedand all other column value commited.

    regards
    anandkl.
    anandkl

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    Quote Originally Posted by anandkl
    Can any one please explain. Our customers are bombarding us.
    Here's why. Because you've enabled storage in row (that's the default). That's why your customers are able to see CLOB data for 'inline' segments that get stored in the table's tablespace (rather than the CLOB ts). If the CLOB segments were > 32K they would've gone to your LOB tablespace..which was offline and they would see errors.

    See below

    Code:
    SQL> c/log/lob
      1* create table t (id int, out_row clob) tablespace test1 lob (out_row) store as (tablespace test2 disable storage in row)
    SQL> /
    
    Table created.
    
    SQL> insert into t values (1,'BLAH');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from t;
    
            ID
    ----------
    OUT_ROW
    --------------------------------------------------------------------------------
             1
    BLAH
    
    
    SQL> alter tablespace test2 offline;
    
    Tablespace altered.
    
    SQL> select * from t;
    ERROR:
    ORA-00376: file 5 cannot be read at this time
    ORA-01110: data file 5: '/oradata/NETMSPRD/test02.dbf'
    
    SQL> alter tablespace test2 online;
    
    Tablespace altered.
    
    SQL> create table t2 (id int, out_row clob) tablespace test1 lob (out_row) store as (tablespace test2 enable storage in row)
      2  /
    
    Table created.
    
    SQL> insert into t2 values (1,'BLAH2');
    
    1 row created.
    
    SQL> commit;
    
    SQL> alter tablespace test2 offline;
    
    Tablespace altered.
    
    SQL> select * from t2;
    
            ID
    ----------
    OUT_ROW
    --------------------------------------------------------------------------------
             1
    BLAH2

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Thanks a lot Axr,
    It was really help full.

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Axr,
    Just one question, if the CLOB data is >32k and if we have mentioned "enable store in row", then would it still be in the same tablespace as the table was, or will it go to the LOB tablespace.

    Also, will this insert work for tables having clob columns.

    insert into new_Table select * from old_table;

    Here old_table has clob values.
    I am copying all data from old_table into new_table.

    Will there be any issue with copying clob column values.

    regards
    anandkl
    Last edited by anandkl; 10-07-2005 at 08:05 AM.
    anandkl

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Axr,
    One more thing, both table and lob segments are in the same tablespace.


    regards
    anandkl
    anandkl

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    You need to store CLOB completely out of the data tablespace.
    This will not only help performance but also recovery in case the datafile gets corrupted.

    Tamil

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Yes, i agree, But unfortunately it was done like this earlier.

    Selven can you please let me know if i can use

    insert into table_name as select * from old_table;

    to insert clob values from other table.

    regards
    anandkl
    anandkl

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Do you want insert only clob data into another table?

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width