drop table from readonly TS

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Darshak Shah
    #1

    drop table from readonly TS

    Hi,

    * Question background :

    My DB is in ArchiveLog mode.
    I have TS in ReadOnly mode.
    As i know,
    - Oracle allows to DROP table resides in ReadOnly TS (& other objects
    also) even though that TS is in ReadOnly mode.
    - Oracle will not recover ReadOnly TS during Oracle startup.

    I have backup of db files "after" making TS readOnly but "before"
    dropping tables from ReadOnly TS.
    That means...
    1st, made TS as ReadOnly
    2nd, took backup of ReadOnly TS
    3rd, dropped few tables from ReadOnly TS
    Assume now media failure occurred.

    * Question :
    After restore + recovery operation, those dropped table will be
    available or not ? WHY ?
    I am in doubt because
    1) there is chance of having those tables BACK after restore +
    recovery operation because restored files are before dropping tables.
    2) there is chance of NOT having those tables back after restore +
    recovery operation because Oracle doesn't perform recovery for
    ReadOnly TS.

    Which one is correct ???


    Thanks,
    Darshak
    Tags: None
    • sybrandb@yahoo.com
      #2
      Re: drop table from readonly TS

      darshaks@coreob jects.com (Darshak Shah) wrote in message news:<92054707. 0307310311.1138 114d@posting.go ogle.com>...
      Hi,
      >
      * Question background :
      >
      My DB is in ArchiveLog mode.
      I have TS in ReadOnly mode.
      As i know,
      - Oracle allows to DROP table resides in ReadOnly TS (& other objects
      also) even though that TS is in ReadOnly mode.
      - Oracle will not recover ReadOnly TS during Oracle startup.
      >
      I have backup of db files "after" making TS readOnly but "before"
      dropping tables from ReadOnly TS.
      That means...
      1st, made TS as ReadOnly
      2nd, took backup of ReadOnly TS
      3rd, dropped few tables from ReadOnly TS
      Assume now media failure occurred.
      >
      * Question :
      After restore + recovery operation, those dropped table will be
      available or not ? WHY ?
      I am in doubt because
      1) there is chance of having those tables BACK after restore +
      recovery operation because restored files are before dropping tables.
      2) there is chance of NOT having those tables back after restore +
      recovery operation because Oracle doesn't perform recovery for
      ReadOnly TS.
      >
      Which one is correct ???
      >
      >
      Thanks,
      Darshak
      Information about tables is maintained in the datadictionary in the
      system tablespace. The affected tables have been deleted from the
      dictionary.
      And NO you will not get them back EVER. Not recovering read only
      tablespaces has NOTHING to do with it.

      Sybrand Bakker
      Senior Oracle DBA

        Comment

        • Karsten Schmidt
          #3
          Re: drop table from readonly TS

          sybrandb@yahoo. com wrote in message news:<a1d154f4. 0307312334.2198 eb46@posting.go ogle.com>...
          darshaks@coreob jects.com (Darshak Shah) wrote in message news:<92054707. 0307310311.1138 114d@posting.go ogle.com>...
          Hi,

          * Question background :

          My DB is in ArchiveLog mode.
          I have TS in ReadOnly mode.
          As i know,
          - Oracle allows to DROP table resides in ReadOnly TS (& other objects
          also) even though that TS is in ReadOnly mode.
          - Oracle will not recover ReadOnly TS during Oracle startup.

          I have backup of db files "after" making TS readOnly but "before"
          dropping tables from ReadOnly TS.
          That means...
          1st, made TS as ReadOnly
          2nd, took backup of ReadOnly TS
          3rd, dropped few tables from ReadOnly TS
          Assume now media failure occurred.

          * Question :
          After restore + recovery operation, those dropped table will be
          available or not ? WHY ?
          I am in doubt because
          1) there is chance of having those tables BACK after restore +
          recovery operation because restored files are before dropping tables.
          2) there is chance of NOT having those tables back after restore +
          recovery operation because Oracle doesn't perform recovery for
          ReadOnly TS.

          Which one is correct ???


          Thanks,
          Darshak
          >
          Information about tables is maintained in the datadictionary in the
          system tablespace. The affected tables have been deleted from the
          dictionary.
          And NO you will not get them back EVER. Not recovering read only
          tablespaces has NOTHING to do with it.
          >
          Sybrand Bakker
          Senior Oracle DBA
          Hi,
          actually this is kind of interesting - I think.
          If this tablespace is local managed, Oracle would have to update the
          free space bitmaps in the 'read only' datafiles, i.e. they change.
          Thus a new backup would be required.

          I did nt't test this, but i doubt that it would be allowed to drop
          tables from read-only local managed tablespaces.

          Karsten

            Comment

            Previous template Next
            Working...