search cancel

Harvest Database Tables - Which Tables are actually needed to back up and which tables not needed to backup.

book

Article ID: 201462

calendar_today

Updated On:

Products

CA Harvest Software Change Manager CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

We are planning to decommission the Oracle Database for Harvest. But we would like to take a backup only the main tables. Which Tables are actually needed to back up and which tables not needed to backup.

Environment

Release : 13.0.4

Component : CA Harvest Software Change Manager

Resolution

For the projects where you will need to be able to check out past versions of files, the best bet is to set up an archive database and use the “hmvproj” utility to move those projects out of the main database.  Here’s where you can read more about that: https://techdocs.broadcom.com/us/en/ca-enterprise-software/business-management/harvest-scm/13-0/command-reference/get-started-with-ca-harvest-scm-commands/hmvproj-command-move-or-copy-projects.html

For the remaining projects, the fun begins when we start looking at primary and foreign key relationships to know which tables to drop first, second, third, etc.  Surgically removing tables from the database is therefore a challenge.  What might make more sense is to delete data from the fields that occupy the bulk of the space.  The main culprit will be the VERSIONDATA field in the HARVERSIONDATA table, and the VERSIONDATA field in the HARBIGFILEDATA table.  These are blob fields and when you check in a file, that’s where the guts of the file goes.  It’s by far the largest consumer of space in the database.

Looking at other things that can go,

  • If you are not interested in the audit log information, those tables can be dropped:
    • HARACTION
    • HARAUDITEVENT
    • HARAUDITEVENTDESCRIPTION
    • HARAUDITEVENTRESOURCEL1
    • HARAUDITEVENTRESOURCEL2
    • HARAUDITEVENTRESOURCEL3
    • HARAUDITEVENTRESOURCEL4
    • HAREXECUTABLEACTION
    • HARGLOBALAUDITPOLICY
    • HARRESOURCETYPE
    • HARRESOURCETYPECHILD
    • HARRESOURCETYPEDESCENDANT
  • The temp tables, of course, can be dropped:
    • ARUSRGRPLISTTEMP
    • HARCHILDRENTEMP
    • HARCHILDTEMP2
    • HARCHILDTEMP1
    • HARPARENTTEMP
    • HARPATHTEMP
    • HARAPPROVEACTIONVIEW_TEMP
    • HARAPPROVEHISTACTIONVIEW_TEMP
    • HARAPPROVEHISTVIEW_TEMP
    • HARFORMTEMP
    • HAROBJID3TEMP
    • HAROBJID2TEMP
    • HAROBJIDTEMP
    • HARUSRGRPTEMP
    • HARUSERTEMP
    • HARASSOCPKGTEMP
    • HARREPOSITORYTEMP
    • HARPKGGRPTEMP
    • HARPACKAGETEMP2
    • HARPACKAGETEMP
    • HARVERSIONDTEMP
    • HARBASELINEVIEWTEMP
    • HARCPMTEMP
    • HARCPMVERSIONTEMP
    • HARVERSIONTEMP
    • HARVIEWTEMP
    • TEMPAPPROVELIST
    • TEMPAPPROVEHIST
    • HARITEMIDTEMP
    • HARITEMTEMP
    • RECURSIVE
    • VCTEMP2
    • VCTEMP1
    • HARMOTETEMP
    • VCTEMPD2
    • VCTEMPD
    • HARENVACCESSTEMP
  • The table containing the BIRT report queries:
    • HARRPTSQL
  • The information in forms attached to packages might be of interest.  If not you can drop these tables:
    • HARASSOCPKG
    • HARCOMMENT
    • HARDEFECT
    • HARESD
    • HARFORM
    • HARFORMATTACHMENT
    • HARFORMHISTORY
    • HARFORMTEMPLATES
    • HARFORMTYPE
    • HARFORMTYPEACCESS
    • HARFORMTYPEDEFS
    • HARMR
    • HARONDEMANDCR
    • HARPAC
    • HARPROBLEMREPORT
    • HARQANDA
    • HARTESTINGINFO
    • (and any other tables carrying data typed into specific types of forms.  This query will generate that list for you:  SELECT FORMTABLENAME FROM HARFORMTYPE)
  • You need to keep the main tables for the project and state, but information about the processes and access permissions can be dropped
    • HARCHECKINPROC
    • HARCHECKOUTPROC
    • HARCONMRGPROC
    • HARCPMSTATE
    • HARCRPKGPROC
    • HARCRSENVMRGPROC
    • HARDELPKGPROC
    • HARDELVERSPROC
    • HARDEMOTEPROC
    • HARENVIRONMENTACCESS
    • HARINTMRGPROC
    • HARLINKEDPROCESS
    • HARLISTDIFFPROC
    • HARLISTVERSPROC
    • HARMOVEITEMPROC
    • HARREMOVEPATHPROC
    • HARMOVEPATHPROC
    • HARMOVEPKGPROC
    • HARNOTIFY
    • HARNOTIFYLIST
    • HARPROMOTEPROC
    • HARREMITEMPROC
    • HARRENAMEITEMPROC
    • HARRENAMEPATHPROC
    • HARSNAPVIEWPROC
    • HARSTATEACCESS
    • HARSTATEPROCESS
    • HARSTATEPROCESSACCESS
    • HARSWITCHPKGPROC
    • HARUDP
  • If you’ve been using the peer review feature you might want to keep those tables to retrieve comments, approvals and such.  If you’ve not been using this feature those tables will be empty.  Not taking up any space, but not really needed either.
    • HARPEERREVIEW
    • HARPEERREVIEWREVIEWER
    • HARPEERREVIEWCOMMENT
    • HARPEERREVIEWCOMMENTDATA
    • HARPEERREVIEWHISTORY
    • HARPEERREVIEWATTACHMENT
    • HARPEERREVIEWCOMMENTATTACHMENT
  • For repositories and items, you will need to keep the main ones but tables relating to access, file extensions, etc can be dropped:
    • HARFILEEXTENSION
    • HARITEMACCESS
    • HARREPOSITORYACCESS
  • And tables with general system information:
    • HARHARVEST
    • HAROBJECTSEQUENCEID
    • HARPACKAGENAMEGEN
    • HARPACKAGESTATUS
    • HARPASSWORDHISTORY
    • HARPASSWORDPOLICY
    • HARPKGSINCMEW
    • HARPMSTATUS
    • HARTABLEINFO
    • HARUSERCONTACT
    • HARUSERDATA
    • USCMMAPPER

Again, you’ll probably need to disable constraints in order to drop the tables so that you don’t have to worry about which table to drop first, second, third, etc.  And after this you’ll need to follow Oracle standard procedures for reclaiming the free space.