Step 1: Take Database of System and Tenant Database from Production Environment

Step 2: Stop DWP Catalog (./dwpcontroller stop ) in lower environment

Step 3: Restore the System and tenant database to Lower Environment

Database reference:

System Database: dwp_db_app or DWPCSaas on the latest versions
Tenant Database: dwp_db_tenant or DWPCTenant on the latest versions

Step 4: Before starting server, clean up the entries from below forms
             select * from servgrp_applic;
             select * from servgrp_board;
             select * from servgrp_config;
             select * from servgrp_ftslic;
             select * from servgrp_resources;
             select * from servgrp_userlic;
             select * from AR_System_Server_Group_Operati;
             select * from AR_System_Service_Failover_Ran;
             select * from AR_System_Service_Failover_Whi;


Step 5:           Update the AR_System_tenant Table in the System database ( dwp_db_app or DWPCSaas) with appropriate database user name, password, and database name. 

Note:

Regarding the table AR_System_tenant, this is a view , if you do not see it you should see the view with that name based on T table , otherwise search for view name in arschema , select viewName from arschema where name like 'AR System Tenant'
 

Step 6:        Check the ar.conf on the /opt/bmc/digitalworkplace/conf directory for the below parameters

               Db-name: dwp_db_app
               Db-user: dwp_admin_app
               Db-Server-Port: 1433
               Db-Type: sqlserver
               Db-Character-Set: UTF-16
               Db-Host-Name: xxxxxxx
               Db-password: vxxxxxx


Step 7:          Start the DWP Catalog Server ( ./dwpcontroller start )

Step 8:          Configure the Operation Ranking

Step 9:         Enable FTS and perform FTS reindex

Step 10:      At this point, you should have all the data from DWP Catalog Production database to Lower Environment
                   Note: Make sure to disable below parameter if you do not want the email to be sent out from lower environment

                  Location : /opt/bmc/digitalworkplace/sb/env/set_script_variables.sh

                 export SMTP_ADDRESS=xxxxxxx
                 export SMTP_PORT=25
                 export SMTP_USER_NAME=xxxxxxxxxxx
                 export SMTP_PASSWORD=xxxxxxx

NOTE: If above steps does not help to resolve the issue. you can run below steps to update  and re-compile some views in Tenant DB.(dwp_db_tenant or DWPCTenant)

Using DB Admin user, login to the target Tenant DB (dwp_db_tenant or DWPCTenant)
Run this query to check if there are views that are referencing source System DB

SELECT count(*)
FROM sys.sql_modules WHERE definition LIKE '%<SOURCE  SYSTEM DB>%'
If result from previous step shows more than 0 rows, generate sql script to update the views by running this sql command. 
Replace "SOURCE SAAS SYSTEM DB" and "TARGET SYSTEM DB"

SELECT
REPLACE(
        REPLACE(definition,'CREATE VIEW','ALTER VIEW'),
        '<SOURCE SYSTEM DB>','<TARGET SYSTEM DB>'
        )  + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules WHERE definition LIKE '%<SOURCE SYSTEM DB>%'
Run the sql scripts generated from previous step to update the database views.

There are views that have the database name hardcoded. They may need to be updated

** In case you have Oracle DB, please use following script to generate the statement to update the DB user:
SELECT 'CREATE OR REPLACE VIEW '||VIEW_NAME||' AS '||REPLACE(REPLACE(TEXT_VC, 'OLD_TENANT_DB_USER', 'NEW_TENANT_DB_USER'), 'OLD_SYSTEM_DB_USER', 'NEW_SYSTEM_DB_USER')  AS SQL_TO_RUN
FROM ALL_VIEWS
where TEXT_VC like '%DWPCTenantAdmin%'
ORDER BY VIEW_NAME;
**Run the sql scripts generated from previous step to update the database views.

If you are still unable to get this working, check the below


There was one customer who has performed all above steps still they were unable to connect to the target db. 
After doing below steps only they were able to connect to the target DB.


=======
(dwp_db_app or DWPCSaas)

--[New_DB].[dbo].[AR_System_Tenant]

update [New_DB].[dbo].[AR_System_Tenant] set Database_Name='<new name>', DB_Admin_Name='<new user>', Virtual_Hostname='<new hostname/LB>'

Password must be copied from old App DB

update [New_DB].[dbo].[AR_System_Tenant] set DB_Admin_Password=(select DB_Admin_Password from [OLD_DB].[dbo].[AR_System_Tenant])
====

Password must be copied from Old App DB

update [New_DB].[dbo].[AR_System_Configuration_Comp59] set Setting_Value=(select Setting_Value from [OLD_DB].[dbo].[AR_System_Configuration_Comp59] where Component_Name='<new_hostname>'AND Setting_Name='DB-password') where Component_Name='<new_hostname>'AND Setting_Name='DB-password'

update [New_DB].[dbo].[AR_System_Configuration_Comp59] set Setting_Value=(select Setting_Value from [OLD_DB].[dbo].[AR_System_Configuration_Comp59] where Component_Name='<new_hostname>'AND Setting_Name='DB-password') where Component_Name='<new_hostname>'AND Setting_Name='DB-password'
====