1. Part One: Moving a large range partitioned table to a read only tablespace
2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN.
1. Part One: Moving a large range partitioned table to a read only tablespace
This post is about making historical data read only in a data warehouse. My goal is to convert some of the historical data in this data warehouse to read only and stop backing it up. This way I am planning to save space and improve daily backup times. I have a tablespace called EXAMPLE which has got a large partitioned table called PT and I would like to move the last 12 years data out of this table into a read only tablespace and stop backing up this old historical portion of the table.
Here is how the table data is:
select tablespace_name, table_name, partition_name, blocks, num_rows from user_tab_partitions
where table_name='PT'
order by partition_position
TABLE_NAME PARTITION_NAME BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
PT PART_95 748 100539
PT PART_96 748 100540
PT PART_97 748 100539
PT PART_98 748 100539
PT PART_99 748 100539
PT PART_00 748 100539
PT PART_01 748 100539
PT PART_02 748 100539
PT PART_03 748 100539
PT PART_04 748 100539
PT PART_05 748 100539
PT PART_06 748 100539
PT PART_07 748 100539
PT PART_MAX 0 0
14 rows selected.
This are the segments and the total bytes in the EXAMPLE Tablespace which belong to table PT:
SEGMENT_NAME MB
----------------------------------- --------------------
IDX_PT_X_UNIQUE 26 MB
PT 78.0625 MB
I want to move these segments, except for the current partition 2007, out of the EXAMPLE tablespace in a READ ONLY EXAMPLE_RO tablespace that I will create.I then used the following SQL to get the commands to move the partitions out of EXAMPLE to EXAMPLE_RO tablespace.
SELECT 'ALTER TABLE ' || TABLE_NAME || ' MOVE PARTITION ' || partition_name || ' TABLESPACE EXAMPLE_RO COMPRESS; '
FROM user_tab_partitions
WHERE TABLE_NAME = 'PT'
AND partition_name NOT IN('PART_MAX', 'PART_07')
ORDER BY partition_position DESC;
'ALTERTABLE'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE_ROCOMPRESS;'
-------------------------------------------------------------------------------------------------------------------------
ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;
First I have to create the read-only tablespace as normal
SQL> create tablespace example_ro datafile '/usr/local/oracle/test01/example_ro.dbf' size 100M reuse autoextend on;
Tablespace created.
Then move the data in:
(Note that I also compress this data for maximum saving on space)
ALTER TABLE PT MOVE PARTITION PART_06 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_05 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_04 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_03 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_02 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_01 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_00 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_99 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_98 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_97 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_96 TABLESPACE EXAMPLE_RO COMPRESS;
ALTER TABLE PT MOVE PARTITION PART_95 TABLESPACE EXAMPLE_RO COMPRESS;
Looking at the partitions and their tablespaces, it seems like I have got what I wanted.
TABLESPACE_NAME TABLE_NAME PARTITION_ BLOCKS NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO PT PART_95 748 100539
EXAMPLE_RO PT PART_96 748 100540
EXAMPLE_RO PT PART_97 748 100539
EXAMPLE_RO PT PART_98 748 100539
EXAMPLE_RO PT PART_99 748 100539
EXAMPLE_RO PT PART_00 748 100539
EXAMPLE_RO PT PART_01 748 100539
EXAMPLE_RO PT PART_02 748 100539
EXAMPLE_RO PT PART_03 748 100539
EXAMPLE_RO PT PART_04 748 100539
EXAMPLE_RO PT PART_05 748 100539
TABLESPACE_NAME TABLE_NAME PARTITION_ BLOCKS NUM_ROWS
------------------------------ ---------- ---------- ---------- ----------
EXAMPLE_RO PT PART_06 748 100539
EXAMPLE PT PART_07 748 100539
EXAMPLE PT PART_MAX 0 0
Next lets turn the EXAMPLE_RO tablespace read only.
SQL> alter tablespace example_ro read only;
Tablespace altered.
Good, now let's test our table PT see if it is accepting row insertions in partitions PART_2007 and PART_MAX and not in any other partition before that as is read only.
SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay')
*
ERROR at line 1:
ORA-01502: index 'OE.IDX_PT_X_UNIQUE' or partition of such index is in unusable
state
Oooops, we broke the index with the move partition command as the index is left behind, still in the EXAMPLE tablespace. See below
SQL> select index_name, tablespace_name from user_indexes where index_name='IDX_PT_X_UNIQUE';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IDX_PT_X_UNIQUE EXAMPLE
Let's rebuild this index to repoint it to the moved partitions.
SQL> alter index IDX_PT_X_UNIQUE rebuild;
Index altered.
And now we can do our test again
SQL> insert into pt values (111111111111, 2007, 'kubilay', 'kubilay', 'kubilay');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into pt values (1111111111110, 2005, 'kubilay', 'kubilay', 'kubilay')
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'
Success! What we have is the current portions of this partitioned table PT in a READ WRITE tablespace, and this will be backed up as default by RMAN and the rest of the historical data, which will never change again, in a READ ONLY tablespace EXAMPLE_RO which will only be backed up once at the beginning and never again.
2. Part Two: Backing up, Restoring and Recovering Read Only tablespaces with RMAN
If you ever use an RMAN script (no catalog) with the option SKIP READONLY in the BACKUP.. command you have to make sure you have to have backed up at least once your read only datafiles (tablespace). Otherwise you are doing nothing and in case you loose everything and want to restore the database to a New Host you will not have the read only datafile! You will get an error like this:
Starting recover at 24-FEB-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:16:52
RMAN-06094: datafile 6 must be restored
Realising this mistake, you decide to copy the read only file from somewhere (tape, an OS backup) into the location on the New Host. But you will find out bitterly that this still doesn't work and you will get an error like:
RMAN> recover database until sequence=48;
Starting recover at 24-FEB-08
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/24/2008 14:23:55
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 6 belongs to an orphan incarnation
ORA-01110: data file 6: '/usr/local/oracle/test01/example_ro.dbf'
This happens because RMAN wants the Read Only file that backed up itself! Where is it? It asks. Well this is bad, and you are in a bad situation now.
What you should have don is to take a backup of this read-only tablespace just after you have made it read-only, by modifying your backup script slightly only for once, then use the modified RMAN command below in your backup script. Or just don't SKIP READONLY when you take the backup after you make the tablespace read-only. Make sure it backs up!
RMAN> backup database force;
Once you have taken the backup with the option above RMAN WILL KEEP the backup of this read-only tablespace and will NEVER delete it, no matter what retention policy you have, unless obviously you delete it with OS commands yourself. RMAN Will always keep the backupset containing the read only tablespace.
At the full database restore point you will have to get RMAN to check for read-only files, as it doesn't restore them!? I was puzzled with this myself, yes but that is the case. A simple full restore database will not work! So you have to restore like below, by indicating RMAN to check to see if any read-only tablespaces exist and need to be restored.
RMAN> restore database check readonly;
Starting restore at 24-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /usr/local/oracle/test01/example_ro.dbf
channel ORA_DISK_1: reading from backup piece /u00/test01/databasefiles_TEST01_1qj9rmti_58_20080222
channel ORA_DISK_1: restored backup piece 1
...
At this point RMAN will find the read-only tablespace/datafiles in the backupset, and will restore them before proceeding with restoring the other files. If you do not use the check readonly option during a full database restore RMAN will not restore the file and you will keep on getting the error.
good post :-
ReplyDeleteWorth reading even if you donot work in dataware house environment
Great post,
ReplyDeleteI am looking at setting up something similar, this has answered all my questions.
thanks
dave
My problem is that we are backing up to a tape using Networker - and it has its own retention policy.
ReplyDeletethat means that it does not metter if RMAN will not delete the read only file from the catalog - the tape retention policy will keep the file only for the 28 days it was set up to.
Any way to overcome this problem ?
Anonymous
ReplyDeleteUse RMAN for Oracle and Networker for all else. Do you have to use Networker for your Oracle backups? Some google search on the topic says that others do have your problem have a look:http://newsgroups.derkeiler.com/Archive/Comp/comp.databases.oracle.server/2008-08/msg00272.html
Cheers
Kubilay,
ReplyDeleteIf I backup my read only tablespace seperately like this:
backup tablespace atms_ro_128m
tag='ATMS_RO_128M_NOV_2010';
will it be kept forever regardless of the retention policy? I would still be backing up the rest of my databse once per week full with the skip readonly clause and also with daily incrementals.
Do you see any downside to doing it this way?
Nicole
Nicole
ReplyDeleteWhich version you are on? Is worth checking the documentation on this, as this post is quite old, things might have changed with 11g R2 for example.
RMAN is a very convenient tool. As long as you have backed up the tablespace with RMAN and is registered as a read-only tablespace, RMAN will ignore retention policies for the tablespace and will keep it forever.
What happens in your case? Do you see the tablespace/datafiles around when the retention policy elapses?
I would suggest you check this, don't take things for granted, and the best test would be to do a restore/recover and see if you really get the read-only data recovered back.
Hope this helps.
Regards
Kubilay