Read Only Tablespaces

[mike9]

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

[SANJAY_G]

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

[marist89]

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.

[jmodic]

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.

[julian]

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.

[mike9]

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

[jmodic]

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.

[abhaysk]

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?

[jmodic]

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

[DaPi]

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?