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

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000028204
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle (RSA G&L)
RSA Version/Condition: 7.x, 6.x, 5.x, 4.x, 3.6.X
RSA Platform:  Hardware and Software Appliance (with local database)
IssueThere are times during testing or during deployment when you want to remove all the data in the RSA G&L Application Oracle Database.  It is possible to clear out all the user data from an existing  RSA G&L Oracle Database installation using the methods below.

For a Local Database


For a hardware or software appliance using a local database, the $AVEKSA_WAR_DIR/WEB-INF/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 and Lifecycle V7.0.1 Database Setup and Management Guide for specifics on how to do this for a remote database.  For earlier releases, check the documentation available in the RSA Identity Governance and Lifecycle product space.
NOTE: If AFX is used, AFX data may need to be re-enabled after the database is completely refreshed. This article covers RSA G&L only.
ResolutionThis process can be used for a hardware or software appliance. 
Login as the Linux oracle user and do 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. The export of collector metadata is available from the Admin tab by selecting Import/Export > General. Workflow metadata is available from the Admin tab under Import/Export > Workflow.  Refer to the RSA Identity Governance and Lifecycle 7.0.1 Administrator's Guide 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 to the same version. You will need the metadata exports if you need to recreate collectors, workflows and other objects in the refreshed database. 
  3. Stop the RSA G&L services:

  • For version 3.X single node appliances run:
sudo service aveksa_agent stop
sudo service aveksa_server stop


  • For version 3.X clustered appliances:
    • Run the following on the active and non active nodes:
run AlterCluster.sh off

  • For version 4.X, 5.x, 6.x and 7.x appliances:
acm stop

or

service aveksa_server stop

  1. Stop and restart the database:
  • For version 3.X appliances:
/etc/init.d/dbora stop
/etc/init.d/dbora start

or 

service dbora restart

  • For version 4.X , 5.x, 6.x and 7.x appliances:
acm stopdb
acm startdb

  1. Change directories to /home/oracle/database:
$ cd /home/oracle/database

  1. Run the create schema script:
$ ./createSchema.sh


The createSchema.sh should take between 15 and 35 minutes to run depending on the class of machine.  The script drops and recreates the application user, avuser.   Note that the default password for this user will then be in play.  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.


The database is now in the same state as a new installation.

  1. Install any required patches, which may have been applied.
  2. Start the RSA Identity G&L services:
  • For version 3.X single node appliances run:
sudo service aveksa_server start
sudo service aveksa_agent start


  • For version 3.X clustered appliances:
    • Run the following on the active and non active nodes:
run AlterCluster.sh off

  • For version 4.X, 5.x, 6.x and 7.x appliances:
acm start

or 

service aveksa_server start

  1. Import metadata and run collections to collect and process all new fresh data.
A backup from a previous system or version could also be imported at this time. 
Notes
  • The metadata export in version 3.6.3 and below is limited to a maximum of 2 MB, in some installations multiple metadata export files may be required. 
  • 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:00   1%] executing Create Scripts/Drop_User.sql
[0:01:16   1%] executing Create Scripts/Drop_Reports_User.sql
[0:01:17   1%] executing Create Scripts/Drop_Public_User.sql
[0:01:17   1%] executing Create Scripts/Drop_ExportImport_Directory.sql
[0:01:17   1%] executing Create Scripts/Drop_Data_Directory.sql
Executing sys statements . . .

.
.
.
Executing public schema statements . . .
[0:04:43 100%] executing packages/As_Public_Schema_User.pks
[0:04:44 100%] executing packages/As_Public_Schema_User.pkb
[0:04:44 100%] executing Create Scripts/Create_Synonyms_Public_Schema.sql
The log is available at: /home/oracle/jboss-4.2.2.GA/server/default/deploy/aveksa.ear/aveksa.war/WEB-INF/database/log/create.log
$

After the execution of createSchema.sh has successfully completed, review of the aveksaServer.log will look like this when the JBoss server is started, just as if this were a brand new installation: 
10/26/2015 13:58:29.779 INFO  (main) [com.aveksa.server.runtime.AveksaSystem] ******************** Aveksa System Initialization Start ********************
10/26/2015 13:58:29.801 INFO  (main) [com.aveksa.migration.jdbctool.AveksaSystemCfg] Loaded Aveksa_System.cfg from AVEKSA_HOME directory:/home/oracle
10/26/2015 13:58:29.883 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java version:      1.6.0_45
10/26/2015 13:58:29.885 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java VM version:   20.45-b01
10/26/2015 13:58:29.887 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java heap init:    124M
10/26/2015 13:58:29.888 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java heap used:    192M
10/26/2015 13:58:29.889 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java heap max:     1820M
10/26/2015 13:58:29.890 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java PermGen init: 20M
10/26/2015 13:58:29.891 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java PermGen used: 57M
10/26/2015 13:58:29.891 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Java PermGen max:  512M
10/26/2015 13:58:29.893 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Memory options:    -Xmx2048m -XX:MaxPermSize=512M
10/26/2015 13:58:29.894 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Operating System:  Linux
10/26/2015 13:58:29.895 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Operating System Architecture:  amd64
10/26/2015 13:58:29.895 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Operating System Version:  2.6.18-308.el5
10/26/2015 13:58:29.896 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] JDBC driver:       11.2.0.2.0
10/26/2015 13:58:29.906 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Oracle version:    11.2.0.3
10/26/2015 13:58:29.911 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Aveksa Compliance Manager schema user:      AVUSER
10/26/2015 13:58:29.925 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Aveksa Compliance Manager reporting engine user:     AVDWUSER
10/26/2015 13:58:29.928 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Aveksa Compliance Manager public database schema user:      ACMDB
10/26/2015 13:58:29.945 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] Tablespace information
10/26/2015 13:58:29.947 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] DATA 256K Tablespace :DATA_256K
10/26/2015 13:58:29.947 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] DATA 1M Tablespace :DATA_1M
10/26/2015 13:58:29.948 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] DATA 25M Tablespace :DATA_25M
10/26/2015 13:58:29.949 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] DATA 50M Tablespace :DATA_50M
10/26/2015 13:58:29.950 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] INDEX 256K Tablespace :INDX_256K
10/26/2015 13:58:29.951 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] INDEX 1M Tablespace :INDX_1M
10/26/2015 13:58:29.952 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] INDEX 25M Tablespace :INDX_25M
10/26/2015 13:58:29.953 INFO  (main) [com.aveksa.migration.jdbctool.CheckDatabase] INDEX 50M Tablespace :INDX_50M
10/26/2015 13:58:30.008 INFO  (main) [com.aveksa.migration.jdbctool.AveksaSystemCfg] Loaded Aveksa_System.cfg from AVEKSA_HOME directory:/home/oracle
10/26/2015 13:58:30.022 INFO  (main) [SystemOut] executing run-once-avuser.sql
10/26/2015 13:58:30.869 INFO  (main) [SystemOut] executing updates/6.8/ACM-43507.sql
10/26/2015 13:58:30.909 INFO  (main) [SystemOut] executing updates/6.8/ACM-45157.sql
10/26/2015 13:58:31.674 INFO  (main) [SystemOut] executing Upgrade/Utils/Migration_Utils_Pkg.pks
10/26/2015 13:58:31.681 INFO  (main) [SystemOut] executing Upgrade/Utils/Migration_Utils_Pkg.pkb
10/26/2015 13:58:31.697 INFO  (main) [SystemOut] executing updates/6.8/ACM-51985.sql
10/26/2015 13:58:31.754 INFO  (main) [SystemOut] executing Create Scripts/Create_Views.sql
10/26/2015 13:58:35.933 INFO  (main) [SystemOut] executing Rebuild_Packages.sql
10/26/2015 13:58:35.934 INFO  (main) [SystemOut] executing ./packages/Common_Constants_Pkg.pks
10/26/2015 13:58:35.944 INFO  (main) [SystemOut] executing ./packages/Admin_Util_Constants_Pkg.pks
10/26/2015 13:58:35.950 INFO  (main) [SystemOut] executing ./packages/Ref_Resl_Constants_Pkg.pks
10/26/2015 13:58:35.957 INFO  (main) [SystemOut] executing ./packages/System_Settings.pkg
10/26/2015 13:58:35.966 INFO  (main) [SystemOut] executing ./StoredProcedures/AV_Compile_Invalid_Objects.sql
10/26/2015 13:58:35.991 INFO  (main) [SystemOut] executing ./StoredProcedures/getSequenceValue.sql
10/26/2015 13:58:36.000 INFO  (main) [SystemOut] executing ./packages/statistics/All_Statistics_Package_Headers.pks
10/26/2015 13:58:36.017 INFO  (main) [SystemOut] executing ./packages/statistics/Statistic_Control.pkb
10/26/2015 13:58:36.023 INFO  (main) [SystemOut] executing ./packages/statistics/Statistic_Operations.pkb
10/26/2015 13:58:36.031 INFO  (main) [SystemOut] executing ./packages/statistics/Statistic_Tables.pkb
.
.
.

Attachments

    Outcomes