Utility Archives

Make sure that Oracle Identity Manager is not running and is not available for off-line utilities.

Make sure that Oracle Identity Manager database has no transaction against it until the UPA table is partitioned.

Query the UPA table to get the minimum and maximum calendar year for the audit data. Following queries can help you get the minimum and maximum year. The maximum year should be the current calendar year.

This helps in deciding the partitions for each calendar year starting from minimum year.

Create a new partition table.

Assuming 2005 as minimum year and 2011 as running or current calendar year, the following decisions are to be made before creating a newly partition table:

• How many years of old audit data you want to keep? If it is important to keep only three years of audit data, then you have to create newly partitioned table starting from year 2008. The data older than 2008 will get cleaned up when the original UPA table gets dropped.

• After deciding the years of old data to keep, the next question is how and where the old data should be kept? Do you want to keep all the old data partitions in the active UPA table, or create backup of the old partitions and then drop the old partitions? Oracle recommends moving the old partitions into tapes and then purging them from the UPA table. As stated earlier, you must keep the latest and running calendar year partition untouched.

The following sample assumes that you want to keep three years of audit data in UPA table and current calendar year is 2011:

Create another non-partitioned table with similar structure as the UPA table, by running the following statement:

Here, TBS_NAME is the name of the same tablespace as of partition, which is to be exchanged.

This table is temporary in nature. The purpose of this table is to facilitate the loading of audit data to a newly partitioned UPA table.

Load the latest audit data into the non-partitioned UPA table, as shown:

Swap the data into the partitioned table by using the ALTER TABLE command, as shown:

Drop the upa_non_part table, as shown:

While exchanging partitions, the data dictionary is updated instead of writing data physically. Therefore, it is necessary to drop and re-create the temporary non-partitioned UPA_NON_PART table in the same tablesapce associated to the partition to be exchanged.

Rename the original non-partitioned UPA table to UPA_OLD, as shown:

Rename the newly partitioned UPA_PART table to UPA:

Manage the constraints for the new UPA table. To do so:

1. Rename the constraint from old UPA table to some other name, as shown:

   ALTER TABLE UPA_old RENAME CONSTRAINT PK_UPA TO PK_UPA_old; ALTER INDEX IDX_UPA_EFF_FROM_DT RENAME TO IDX_UPA_EFF_FROM_DT_old; ALTER INDEX IDX_UPA_EFF_TO_DT RENAME TO IDX_UPA_EFF_TO_DT_old; ALTER INDEX IDX_UPA_USR_KEY RENAME TO IDX_UPA_USR_KEY_old; ALTER INDEX PK_UPA RENAME TO PK_UPA_OLD;

2. Create the necessary indexes and primary key constraint on the newly partitioned UPA table. Make sure to add storage characteristics, such as tablespace and size. To do so, run the following SQL query:

   SQL>create index IDX_UPA_EFF_FROM_DT on UPA (EFF_FROM_DATE) Local; SQL>create index IDX_UPA_EFF_TO_DT on UPA (EFF_TO_DATE) Local; SQL>create index IDX_UPA_USR_KEY on UPA (USR_KEY) Local; SQL>ALTER TABLE UPA add constraint PK_UPA primary key (UPA_KEY) using index;

   Note:

   The global non-partitioned index is created to support the primary key. Global index becomes unusable every time a partition is touched. You must rebuild the index when required.

Run the statistics collection for the UPA table, as shown:

Start Oracle Identity Manager. The database is ready to be opened for transactions. Test and make sure that applications are running as expected.

Bring current year data in UPA_2011_PART1 to have all data and maintain consistency for current year. To do so, run the following SQL queries in sequence:

Here, TBS_NAME is the same tablespace name as of the partition, which is to be exchanged.

If required, bring previous year's data into the newly partitioned UPA table. To do so:

1. Run the following SQL queries in sequence:

   SQL> CREATE TABLE upa_non_part Tablespace TBS_NAME AS SELECT * FROM upa WHERE 1=2;

   Here, TBS_NAME is the same tablespace as of the partition, which is to be exchanged.

   ............. ............. SQL> Alter Table UPA_NON_PART add constraint PK_UPA_NON_PART primary key (UPA_KEY) using index; ............. ............. SQL> Insert into upa_non_part select * from upa_old where eff_to_date >= to_date('01/01/YEAR', 'mm/dd/yyyy') and eff_to_date < to_date('01/01/<YEAR+1>', 'mm/dd/yyyy');

   Here, YEAR is the year for which you want to bring the data into newly parititoned UPA table.

   ............. ............. SQL>COMMIT; ............. ............. SQL> Alter table upa exchange partition UPA_<year> with table upa_non_part with validation Update global indexes;

2. Rebuild indexes if they are unusable. The Following SQL query shows the indexes that are unusable:

   SQL> Select index_name, partition_name, tablespace_name, status from user_ind_partitions;

3. Drop the table upa_non_part, as shown:

   SQL> Drop table upa_non_part;

All partition operations against UPA table are done and all the data is brought into. Run the statistics collection for the UPA table, as shown:

Drop the UPA_OLD table if it is not required. You can create a backup of this table before dropping.