Read Only Tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Read Only Tablespaces

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    Read Only Tablespaces

    Hi,

    I'm working on a DWH project using 9iR2.

    My manager get concerned about the long time that a full hot backup of the hole DWH (2.4 TB) take.

    All our Tables containing the transaction data are monthly range partitioned. For each month we got a specific tablespace on which we store all partitions of a same month. All this transaction data and related indexes together take about 2 TB.

    My proposition to reduce the time and resources when doing a full backup was to set all tablespaces containing transaction data, without the one containing the data of the current month, to read only and so to reduce the amount of data which have to be backuped to 1/5. Until know we did not use the Read Only option for Tablespaces, so nobody in our team has a lot of experience with this feature.

    So my question is: are there any other advantages using Read Only Tablespaces for Tables/Partitions which will never be inserted deleted/updated again? Are the some major disadvantages/bugs? When I, for example, analyze (dbms_stats) a range partitioned table, does the data of the partitions on the read only tbs also get analyzed even if they already got analyzed once since the tbs has been set to read only?

    Thanks for any feedback
    Maurice Mike
    Last edited by mike9; 04-04-2004 at 08:16 AM.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Mike,
    Though I have not used read only tablespaces much, but I do not foresee any issue and it will definitely save you backup time. Below metalink url is a good read for readonly tablespaces.
    http://metalink.oracle.com/metalink/...T&p_id=33402.1
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I use readonly tablespaces in a similar manner. I backup my database using the skip readonly option with RMAN on a daily basis. I then backup the readonly tablespaces once a month (due to tape retention policies).

    There's a couple of things to keep in mind with readonly tablespaces. First, you are right, once the partitions in the readonly tablespaces have been analyzed, they never have to be analyzed again. The data never changes, so the statistics never change.

    Second, a DDL operation can still affect a partition in a readonly tablespace. For example, if you have partition P_20030101 in a readonly tablespace, you can still drop that partition even though it is in a readonly tablespace.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You would also better to berform a full table scan of each table in that tablespace before you put it to readonly mode. In particular those tables that are modified in "many records changed in one DML operation" mode.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    And something else: RMAN does not restore read-only datafiles when performing full DB restore.

    You need to run the command:

    RESTORE DATABASE CHECK READONLY;

    in order to restore the read-only datafiles.

    And yet something more:

    If your retention policy is redundancy, RMAN only skips backups of offline or read-only datafiles when there are n + 1 backups, where n is an integer specified in CONFIGURE RETENTION POLICY TO REDUNDANCY n.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    Hi everybody,

    Thanks a lot for your usefull feedback. With all your input I shouldn't have to much problem to convince my boss to use this feature and so I also know what our DBA has to change on the backup/recovery side.



    Jurij,
    Could you please explain me a bit better which is the goal of your select. For your information the monthy range partitions are daily loaded (INSERT APPEND) with about 1M rows a day.

    Thanks
    Mike

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike9
    Jurij,
    Could you please explain me a bit better which is the goal of your select. For your information the monthy range partitions are daily loaded (INSERT APPEND) with about 1M rows a day.
    It's because when mass-DML changes are taking place, oracle does not bother with "delayed block cleanout" operation. In other words, although after the operation is complete (transaction is commited or rolled back), all the blocks that have been changed are consistent, but there is one bit in block header that still indicates that the block is "dirty". Now when you visit such blocks for the first time after those changes have taken place, the "block cleanout" will be performe automaticaly (which imposes some small overhead), but this new information will now be stored in block header (block will be marked as clean) so that next time this overhead will not occur.

    But if you put such tablespace in readonly mode before this block cleanout operation takes place, the overhead of checking if the block is actualy clean although it is marked as dirty will reapear every time you tuch that block because block can not be marked as clean when it is in readonly tablespace. This means that all queries on such blocks will be substantialy slower than they would be if the blocks were cleared before the tablespace is put in readonly mode.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    But if you put such tablespace in readonly mode before this block cleanout operation takes place, the overhead of checking if the block is actualy clean although it is marked as dirty will reapear every time you tuch that block because block can not be marked as clean when it is in readonly tablespace. This means that all queries on such blocks will be substantialy slower than they would be if the blocks were cleared before the tablespace is put in readonly mode.
    This was useful peice of info, but can we know the courtesy?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    This is no big-secret behavior, I know I have found it described in many articles or other publication. You shouldn't have too much trouble find it described somewhere on the net. I just did a litle search on metalink and here is one of the documents where this is described:
    metalink Doc Id: Note:33402.1
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The recommendation is there in the Admin manual. http://download-west.oracle.com/docs...paces.htm#6884 Though it does not give as good an explanation as Jurij.

    But would select count(*) be enough? e.g. if it could be done with an Index Scan and NOT touch the table?

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