Harvest - how to migrate the Oracle database

Products

• CA Harvest Software Change Manager

Issue/Introduction

What is the correct procedure to move my Harvest database to a new Oracle database server?

Environment

Release : 13.0.3 and higher

Component : CA Harvest Software Change Manager

Resolution

In this example we will assume that the same Oracle Client installation on the broker machine will be used to connect to the Harvest database in its new location.

1. On the broker machine shut down all scm processes

Linux Example (logged in as the user that owns the SCM software folder and processes):

         bkrd -shutdown
         pgrep rtserver | xargs kill
         ps -ef | grep 'hserver\|bkrd\|rtserver'

Windows Example (from command prompt window):

         bkrd -shutdown=all
         (or you can go to Control Panel -> Administrative Tools -> Services and stop the CA SCM Broker Service)
         rtserver -stop_all
         (or you can go to Task Manager and kill the rtserver.exe process from there)

2. On the old Oracle server cd to Oracle’s dpdump folder and dump the Harvest database schema to a dump file

Example:

         expdp <user-name>/<password> SCHEMAS=harvest DUMPFILE=harvest.dmp LOGFILE=harvest-exp.log

(On Linux you should be logged in as the “oracle” user; user-name should be Oracle system level user for example "system")

3. On the new Oracle server cd to Oracle’s dpdump folder and copy the dump file from old Oracle server’s dpdump folder

Linux Example (logged in as the “oracle” user):

         $ cd /app/oracle/oradata/cl/admin/orcl/dpdump/
         $ sftp <old oracle server>
         @<old oracle server>'s password:
         Connected to <old oracle server>.
         sftp> cd /app/oracle/admin/orcl/dpdump/
         sftp> get harvest.dmp
         sftp> quit

Windows Example:

Use an sftp utility such as WinSCP or FileZilla, or Windows shared network drives to copy the file.

4. On the new Oracle server copy crtblspc.sql and creatusr.sql scripts from broker machine’s scm/Database folder.  We will need these scripts to set up the Oracle schema (userid) and tablespace set for the Harvest database.

Linux Example (logged in as the “oracle” user):

         $ cd /app/oracle/oradata/cl/admin/orcl/dpdump/
         $ sftp <old scm server>
         @<old scm server>'s password:
         Connected to <old scm server>.
         sftp> cd /opt/CA/scm/Database/
         sftp> get crtblspc.sql
         sftp> get creatusr.sql
         sftp> quit

Windows Example:

Use an sftp utility such as WinSCP or FileZilla, or Windows shared network drives to copy the files.

5. On new Oracle server run scripts to create tablespaces and user

Example:

         sqlplus <user-name>/<password> @crtblspc.sql HARVESTMETA HARVESTBLOB HARVESTINDEX HarvestMeta.ora
         HarvestBlob.ora HarvestIndex.ora 50 50 50 crtblspc.log

(The sizes of the tablespaces mentioned here (50 50 50) represents 50 MB which is the default for all the table spaces HARVESTMETA HARVESTBLOB HARVESTINDEX. It may be the case that the AUTO EXTEND of the table spaces could be ON or OFF as configured by the DBA. You should be aware so that you can anticipate future space requirements when used with harvest. This point could be considered and assessed by the harvest admin and the DBA.)

         sqlplus <user-name>/<password> @creatusr.sql harvest harvest HARVESTMETA HARVESTBLOB HARVESTINDEX TEMP UNDOTBS1 creatusr_harvest.log
         sqlplus <user-name>/<password> @creatusr.sql harrep harvest HARVESTMETA HARVESTBLOB HARVESTINDEX TEMP UNDOTBS1 creatusr_harrep.log

(On Linux you should be logged in as the “oracle” user; user-name should be Oracle system level user)

6. On new Oracle server import the dump file

Example:

         impdp <user-name>/<password> SCHEMAS=harvest DUMPFILE=harvest.dmp LOGFILE=harvest-imp.log

(On Linux you should be logged in as the “oracle” user; user-name should be SCM system administrator (default is harvest))

7. On the broker machine cd to Oracle home’s network/admin folder and update tnsnames.ora to create a new TNS Service Name paragraph with connection details for new Harvest database location

(On Linux you should be logged in as the “oracle” user)

8. On the broker machine run hdbsetup "CO" option to configure ODBC data source for new Harvest database TNS Service Name

(On Linux you should be logged in as the user that owns the SCM software folder and processes)

9. Restart broker

Linux Example (logged in as the user that owns the SCM software folder and processes):

         ./bkrd
         ps -ef | grep 'bkrd\|hserver\|rtserver'
         ./hgetusg -b $HOSTNAME -prompt -cu ; cat hgetusg.log

Windows Example (from command prompt window):

         bkrd

(or you can go to Control Panel -> Administrative Tools -> Services and start the CA SCM Broker Service)

         tasklist | findstr "bkrd hserver rtserver"

(or you can go to Task Manager and check for bkrd.exe, hserver.exe and rtserver.exe processes)

         hgetusg -b %COMPUTERNAME% -prompt -cu & type hgetusg.log

(or you can try to connect with Workbench)