How to remove all user data stored in the RSA Identity Governance and Lifecycle application database

[#1]

Article Number

000028204

Applies To

RSA Product Set: Identity Governance & Lifecycle 
RSA Version/Condition: 6.x, 7.x

 

Issue

There are times during testing or during deployment when you want to remove all the data in the RSA Identity Governance & Lifecycle Application Oracle Database.  It is possible to clear out all the user data from an existing RSA Identity Governance & Lifecycle Oracle Database installation using the methods below.

For a Local Database

For a hardware or software appliance using a local database, the $AVEKSA_HOME/database/createSchema.sh script may be run which essentially erases all existing data. For that reason, it is recommended that a database backup is created before following this process.

For a Remote Database

Remote database environments such as Enterprise Software users using WebSphere or WebLogic, or software appliances using a remote database have a slightly different process.  The createSchema.sh script cannot be used when the database is remote. For that scenario, the Oracle application user, AVUSER, has to be manually dropped and recreated. Refer to the RSA Identity Governance & Lifecycle Database Setup and Management Guide for your RSA Identity Governance & Lifecycle version for specifics on how to do this for a remote database. 

NOTE: If AFX is used, AFX data may need to be re-enabled after the database is completely refreshed. This RSA Knowledge Base Article covers RSA Identity Governance & Lifecycle only.

Resolution

This process can be used for a hardware or software appliance that has a local database. 

Login as the linux oracle user and perform the following tasks:

1. Take a backup of the database (a database export).  

It is good policy to make sure that a backup of the existing database is created in the event it needs to be restored at some future point in time.

2. Export metadata for collectors, workflows etc., i. e., anything that will be reused in the new fresh or clean database. Workflow metadata may be exported by going to Admin > Import/Export > Workflow tab > Export(all).) All other metadata may be exported by going to Admin > Import/Export > General tab > Export. Refer to the RSA Identity Governance & Lifecycle Administrator's Guide for your RSA Identity Governance & Lifecycle version or to the on-line help for more information on performing the metadata export. Keep in mind that metadata imports are only supported from the same version and patch level to the same version and patch level. You will need the metadata exports if you need to recreate collectors, workflows and other objects in the refreshed database. 

3. Stop the RSA Identity Governance & Lifecycle application:

$ acm stop

or

$ service aveksa_server stop

4. Stop and restart the database:

$ acm stopdb
$ acm startdb

or

$ service aveksa_server stopdb
$ service aveksa_server startdb

5. Change directories to /home/oracle/database ($AVEKSA_HOME/database):

$ cd /home/oracle/database

6. Run the create schema script:

$ ./createSchema.sh

The createSchema.sh script should take between 15 and 35 minutes to run depending on the class of machine. The script drops and recreates the application use, AVUSER. Note that the default password for this user will be restored  If this password had been changed, it will need to be updated again manually by logging into the database as sysdba and updating the password.

After this script completes, the database will be in the same state as a new installation.

7. Install any required patches, which may have been applied.

8. Start the RSA Identity Governance & Lifecycle application:

$ acm start

or 

$ service aveksa_server start

9. Import metadata and run collections to collect and process all new fresh data.

Note: A backup from a previous system or version could also be imported at this time. 

Notes

As a reminder,  since running this script resets the database to be the same as a fresh, brand new installation, the default AveksaAdmin password is reset to the new installation default.

When the createSchema.sh script is run, the output on the terminal session will look similar to this:
 

$ ./createSchema.sh

Executing sys statements . . .

[0:00:02   1%] executing Create Scripts/Drop_User.sql
[0:02:40   1%] executing Create Scripts/Drop_Reports_User.sql
[0:02:43   1%] executing Create Scripts/Drop_Public_User.sql
[0:02:45   1%] executing Create Scripts/Drop_ExportImport_Directory.sql
[0:02:45   1%] executing Create Scripts/Drop_Data_Directory.sql
[0:02:45   1%] executing Create Scripts/Drop_Reports_Directory.sql
[0:02:45   1%] executing Create Scripts/Create_TableSpaces_ASM.sql
[0:03:17   1%] executing Create Scripts/Create_ExportImport_Directory.sql
[0:03:17   1%] executing Create Scripts/Create_User_Profile.sql
[0:03:17   1%] executing Create Scripts/Create_System_Profile.sql
[0:03:17   1%] executing Create Scripts/Create_User.sql
[0:03:18   2%] executing Create Scripts/Create_User_Grants.sql
[0:03:18   2%] executing Create Scripts/Create_Reports_User.sql
[0:03:18   2%] executing Create Scripts/Create_Public_User.sql
[0:03:18   2%] executing migration/loadDatabaseUpdates.sql

.
.
.

Executing public schema statements . . . 

[0:08:43 100%] executing packages/As_Public_Schema_User.pks 
[0:08:43 100%] executing packages/As_Public_Schema_User.pkb 
[0:08:43 100%] executing Create Scripts/Create_Synonyms_Public_Schema.sql 

The log is available at: /home/oracle/database/log/create.log