How to upgrade Agile Manager 2.40 without Database Administrator user

  • Document ID:KM02377594
  • Creation Date:01-Jun-2016
  • Modified Date:01-Jun-2016
    • Micro Focus Products:
      agile manager 2.40
      agile manager 2.50

Summary

This document purpose is to describe the procedure for upgrading Agile Manager (AGM)without having Database Administrator (DB) user permissions.

Question

This document purpose is to describe the procedure for upgrading AGM without having DBA user permissions. In this document you will find the installation  plan, limitations, prerequisites, and  procedures.

Answer

Terms

  • Restrict environment - production environment.
  • Non strict environment - staging environment.
  • Admin scheme - the schema used for administration of the product usually called AGM_SITEADMIN_DB
  • Site schema - the schema that actually hold the project data , usually called T1_SA_MAIN_DB
     
     

Plan highlights

  • Backup production schema's (2 schemes).
  • Restore Schema's on staging environment
  • Install AGM 2.5 on Staging and upgrade it.
  • Backup staging 2.5 schems
  • Restore schemes on production.
  • Rpm install agm 2.5 on production.
  • Connect Production agm install to restored schemas.
 

Prerequisites

  1. Knowledge how to backup and restore oracle schemas, (using impdp and expdp)
  2. Have a staging environment which is separated from production ,separated AGM server and separated database server. (can be installed on laptop with oracle express)
  3. have dba access to the staging environment database.
  4. create db user agm_admin on both Databases (staging and production), make sure the same user name and password are created on both systems.
    In staging the user needs to have dba permissions or admin user permissions (agm_admin) as described in the AGM installation guide.
    In the production environment the user can be as limited as this set of permissions.
Create agm_admin user and role
-- create agm_admin user and role.
drop user agm_admin cascade;
drop role agm_admin_role;
create user agm_admin identified by agm_admin;
create role agm_admin_role;
 
grant CTXAPP to agm_admin;
grant CREATE SESSION to agm_admin_role;
grant CREATE TABLE to agm_admin_role;
grant CREATE VIEW to agm_admin_role;
grant CREATE TRIGGER to agm_admin_role;
grant CREATE SEQUENCE to agm_admin_role;
grant SELECT ON SYS.DBA_REGISTRY to agm_admin_role;
grant SELECT ON SYS.DBA_ROLES to agm_admin_role;
 

 
 

The Procedure

 Copy from production to Staging
  1. On production shut down AGM server , run  <agm>/wrapper/HPALM stop.
  2. On production Backup the Site and System admin schemas - having the output of 2 oracle DMP file.
  3. On production - backup repository folder in tar file.
  4. On Staging - Restore DB schemes
  5. On Staging Install AGM RPM 2.5 clean installation , where no existing agm directory exist. (rpm -iAGM_ONPREM<version>.rpm)
  6. On Staging - edit table named PROJECTS on restored admin schema,
    • replace production db details name with staging db details in columns DBSERVER_NAME  and DB_CONNSTR_FORMAT.
  7. On Staging - extract backed up repository tar, exactly at the same location as in production. (default is /opt/agm/repository)
  8. On Staging - copy file /opt/hp/agm/conf/qcConfigFile.properties from production , and edit it.
    • change dbConnectionString=jdbc\:mercury\:oracle\://<STAGING_DB_SERVER_HOST>\:<STAGING_DB_SERVER_PORT>;sid\=<DB_SID>
    • change dbPort=<STAGING_DB_SERVER_PORT>
    • change dbSchemaName=<restored_admin schema> the default for example dbSchemaName=agm_siteadmin_db
    • change dbServerName=<STAGING_DB_SERVER_HOST>
    • change oracleSid=<DB_SID>
  9. On Staging run the configuration wizard <agm dir>./run_config.sh
    1. when asked for DB info provide the Staging environment DB info, provide the agm_admin user created before as the db user, its important he will also be used in production so don't use system manager user.
    2. When asked for DB schema provide the admin schema that was restored on Staging environment.
    3. when asked for upgrading /connect to existing - choose Upgrade production(option 3)
  10. On Staging start AGM server and verify its working correctly after upgrade, service HPALM start
  11. Open AGM user interface and check everything is working both admin and site

Copying from Staging to production
  1. On Staging shut down AGM server , run  <agm>/wrapper/HPALM stop.
  2. On Staging Backup the Site and System admin schemas - having the output of 2 oracle DMP file.
  3. On Staging - backup repository folder in tar file.
  4. On Production shut down server ,  , run  <agm>/wrapper/HPALM stop.
    1. On Production- Restore DB schemes, you need to rename/drop old schemes to avoid schema name collisions.
  5. On Production install new AGM version by running rpm -U AGM-ONPREM<VERSION>rpm
  6. On Production- edit table named PROJECTS on restored admin schema,
    1. replace staging db server name with production server name in columns DBSERVER_NAME  and DB_CONNSTR_FORMAT
  7. On Production - extract backed up staging repository tar, exactly at the same location as before. (default is /opt/agm/repository)
  8. On Production edit qcConfigFile.properties
    1. change SaDbAction=connectToExisting
    2. change dbConnectionString=jdbc\:mercury\:oracle\://<DB_SERVER_HOST>\:<DB_SERVER_PORT>;sid\=<DB_SID>
    3. change dbPort=<DB_SERVER_PORT>
    4. change dbSchemaName=<restored admin schema> the default for example dbSchemaName=agm_siteadmin_db_2_50
    5. change dbServerName=<DB_SERVER_HOST>
    6. change oracleSid=<DB_SID>
  9. edit <agm>/run_config.sh
    1. in the line containing #SKIP VALIDATIONS remove the remark (#) sign
    2. change to SKIP_VALIDATIONS="-wDbSettingsValidator -wSaSchemaValidator"
  10. Run the configuration wizard <agm dir>./run_config.sh -nonInteractive -c /opt/hp/agm/conf/qcConfigFile.properties
  11. On production admin schema , edit table named DBSERVERS - remove the entry pointing to staging database server, this table should have 1 record in the table pointing to the production database server.  Please commit the change.
  12. On production admin schema , edit table named APPSERVERS- remove the entry pointing to staging server, this table should have 1 record in the table pointing to the production server. Please commit the change.
  13. Run the server by running <agm dir>wrapper/HPALM start
  14. Monitor <agm dir>/log/wrapper.log to see errors.
 
Export and import the Oracle Schemas 

This is not a complete guide how to do that just a quick reference example.
Open Sqlplus or any other tool with dba permissions.
  1. First a logical directory needs to be created , execute the following change the physical directory as necessary.
    1. create or replace directory AGM_DUMP_DIR as 'E:\agm_dmp\';
  2. export schema - open command line in a machine where oracle is installed and run the following.
    1. expdp system/manager@localhost schemas=<SCHEMA TO EXPORT> directory=AGM_DUMP_DIR dumpfile=<SCHEMA_NAME>.dmp logfile=<SCHEMA_NAME>.log
  3. Import schema - place the DMP file in the directory which you mapped logically on target DB server (like above create directory command)
  4. Open command line in the target db machine where oracle is installed and run the following:
    1. impdp system/manager@localhost directory=AGM_DUMP_DIR dumpfile=<SCHEMA_NAME>.dmp full=y logfile=<SCHEMA_NAME>.log