Test your Oracle Partition Archive/Restore Strategies:

Test your Oracle Partition Archive/Restore Strategies:

Are we testing our Oracle Partitioned Tables Restore strategies?
Your Partition Archives are no good if they cannot be restored when needed and if your SLA’s require you to restore the partition if needed by the customer.

Below are some quick steps you can follow to test your Oracle Partitioning Archive and Restore Strategy.

Step1

The first thing to do is create a discrete table that is identical to the partition we want to remove. It must have the same columns, data types, constraints and indexes, and exist in the same tablespace as the partition. The index must also exist in the corresponding index tablespace.

create table PNAME_2004Q1 tablespace PNAME_2004Q1NEW

Step2

The new table in Step1contains no rows, and the index is empty. When we EXCHANGE a partition with a discrete table, we literally exchange data segments between the two. That's why they must match in structure and location. No data is actually moved, only internal pointers are changed.

ALTER TABLE TABLE_NAME EXCHANGE PARTITION PNAME_2004Q1
WITH TABLE PNAME_2004Q1 INCLUDING INDEXES;

Check the indexes at this step – you will see that indexes of only the partition you exchanged got UNUSABLE (the partition you exchanged is the partition you want to drop)
Please Note: Both Global Index and Local Indexes for that partition may be unusable.
A Global Index rebuild will affect the entire table and a Local index rebuild will affect only the Local partition.

Step3:

Count the rows in the new table and in the partitioned table before and after the EXCHANGE to convince yourself that the data did actually "move", check the size of the data segments, and the status of the indexes.

SELECT COUNT(*) FROM PARTITION TABLE_NAME(PNAME_2004Q1);
SELECT COUNT(*) FROM PNAME_2004Q1;

Step4:

We no longer need the old partition as it is now empty, so we can drop it.

ALTER TABLE TABLE_NAME DROP PARTITION PNAME_2004Q1;

If the goal was to simply transport this partition without removing it, we could exchange the table back into the partition after transporting the tablespace.

To get ready make the tablespaces we are transporting (data and index) READ ONLY to ensure there are no active transactions, and that none start during transport. The rest of the partitions are unaffected.

ALTER TABLESPACE PNAME_2004Q1NEW READ ONLY;

Use the DBMS_TTS utility to verify the tablespace set is self contained. That is, there are no objects within the set that reference (foreign key, constraint indexes, etc) any object outside of the set. The set can be one or more tablespaces that will be transported together.

EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK(‘PNAME_2004Q1NEW’, TRUE);
SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;

exp \'sys/sysdba as sysdba\' file= PNAME_2004Q1.dmp transport_tablespace=y tablespaces=PNAME_2004Q1NEW log=PNAME _2004Q1.log
Make sure the unix team backs up the dump file

Drop table PNAME_2004Q1 ;
Alter tablespace PNAME_2004Q1NEW offline;
Drop tablespace PNAME_2004Q1NEW including contents;


Restore the Archived Partition

Step1:

Import the dump file into the target system. Again this is fast because we only import data definitions; the data already exists in the named datafiles. imp USERID='xxxxxx' TRANSPORT_TABLESPACE=y DATAFILES='xxxxxxxxxxxxxxxxx' TABLESPACES= PNAME_2004Q1NEW
FILE= PNAME_2004Q1.dmp

Step2:

By default the new tablespaces will be READ ONLY. In order to create the table partition and EXCHANGE it with the existing discrete table we will have to make them READ WRITE.

ALTER TABLESPACE PNAME_2004Q1NEW READ WRITE;

Step3:

We now have a partitioned table with one empty partition, and our discrete table with data that was transported. To complete the process, simply EXCHANGE data segments between the empty partition and discrete table. Then, count the number of rows, check the size of data segments and status of indexes again.

ALTER TABLE TABLE_NAME EXCHANGE PARTITION PNAME_2004Q1
WITH TABLE PNAME_2004Q1 INCLUDING INDEXES;

After the exchange check the indexes again – you may to rebuild the Local Index after this step if the indexes are unusable.

When you are convinced the exchange has occurred properly and the partitioned table is functional, drop the now empty discrete table and you are done for this year.

DROP TABLE PNAME_2004Q1 PURGE;