Sunday, March 30, 2014

Oracle Adaptive Access Manager(OAAM) 11g DataBase purging


In this post we will learn how to configure and run OAAM Database purging scripts.

Note: OAAM purge scripts are recommended to run using SQLPLUS command line. Avoid using SQL developer. I have faced issues while creating and running the purge scripts.

Step 1: Locate OAAM database purging scripts "" under OAAM Middleware folder.

Step 2: Extract ZIP file to desired location.

Step 3: Login as SYS or SYSDBA into OAAM database and run below scripts

                GRANT create any procedure TO <schema_username>;
               GRANT create any table TO <schema_username>;
               GRANT create any index TO <schema_username>;
               GRANT create procedure TO <schema_username>;
               GRANT execute any procedure TO <schema_username>;

Step 4: Now login as OAAM schema owner and run "create_purge_proc.sql" script under oracle_db folder. When prompted enter Index Table Space & Data Table Space as <SchemaPrefix>_BRSADATA & <SchemaPrefix>_BRSAINDX.

Step 5: Below table has list of purge scripts 

Type of DataCorresponding Script
Login, Device Dataexec_sp_purge_tracker_data.sql
Rules, Policy Log Dataexec_sp_purge_rule_log.sql
Transactions, Entities Dataexec_sp_purge_txn_log.sql
Autolearning Dataexec_sp_purge_workflow_data.sql
Profile Dataexec_sp_purge_profile_data.sql
Cases related Dataexec_sp_purge_case_data.sql
Monitor Dataexec_v_monitor_purge_proc.sql

Step 6: Set the p_days1 and p_archived(either to "Y" or "N") parameters using a text editor when you run the scripts.

Parameter details:
  1. p_days represents parameter to purge data which is more than specified number of days.
  2. p_archived has default value "Y" which says archive the data. Change it to "N" to purge the data.
Step 7: Login to database as OAAM schema owner and run one of the script from the above table as per the requirement.

-- Siva Pokuri.

No comments:

Post a Comment