Hummingbird, the document management system, uses an Oracle database 91 on Sun at this company. Performance of several functions were very bad. 40 seconds to give me a document in some cases.  Bad sql-statements all over the place.

Installed a Linux system with 10.2.0.4 on it, made an export on SUN of the user DOCSADMIN, moved it to the Linux machine, imported it and transformed the tnsnames on the DM-server of Hummingbird. Very straightforward.  It gave Hummingbird a performance-boost. The select statement of 40 sec went down to 2 seconds. Be aware: turn on the statistics on SYS ! This will be done automatically when you create a database with DBCA by the way.

More in detail:

Preparations:
– Check the tablespaces.

– Check the memory use

Migration:
– DM server down
– Make an export
– Copy the export to the Linux machine
– Create role DOCS_USERS
– Create users docsadm en docsuser (generated by Toad… ):

CREATE USER DOCSADM
IDENTIFIED BY VALUES ‘<values on the old db>’
DEFAULT TABLESPACE DOCSVAL
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
— 3 Roles for DOCSADM
GRANT CONNECT TO DOCSADM;
GRANT CTXAPP TO DOCSADM;
GRANT DBA TO DOCSADM;
ALTER USER DOCSADM DEFAULT ROLE ALL;
— 1 System Privilege for DOCSADM
GRANT UNLIMITED TABLESPACE TO DOCSADM;
— 13 Object Privileges for DOCSADM
GRANT SELECT ON SYS.AUDIT_ACTIONS TO DOCSADM;
GRANT SELECT ON SYS.DBA_DIRECTORIES TO DOCSADM;
GRANT SELECT ON SYS.DBA_EXTENTS TO DOCSADM;
GRANT SELECT ON SYS.DBA_INDEXES TO DOCSADM;
GRANT SELECT ON SYS.DBA_IND_COLUMNS TO DOCSADM;
GRANT SELECT ON SYS.DBA_OBJECTS TO DOCSADM;
GRANT SELECT ON SYS.DBA_SEGMENTS TO DOCSADM;
GRANT SELECT ON SYS.DBA_TABLES TO DOCSADM;
GRANT SELECT ON SYS.DBA_TEMP_FILES TO DOCSADM;
GRANT SELECT ON SYS.DBA_USERS TO DOCSADM;
GRANT SELECT ON SYS.V_$INSTANCE TO DOCSADM;
GRANT SELECT ON SYS.V_$LATCHNAME TO DOCSADM;
GRANT SELECT ON SYS.V_$PARAMETER TO DOCSADM;

CREATE USER DOCSUSER
IDENTIFIED BY VALUES ‘<values of the old server>’
DEFAULT TABLESPACE DOCSVAL
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
— 2 Roles for DOCSUSER
GRANT CONNECT TO DOCSUSER;
GRANT DOCS_USERS TO DOCSUSER;
ALTER USER DOCSUSER DEFAULT ROLE ALL;
— 4 Tablespace Quotas for DOCSUSER
ALTER USER DOCSUSER QUOTA UNLIMITED ON DOCSPROF;
ALTER USER DOCSUSER QUOTA UNLIMITED ON DOCSVAL;
ALTER USER DOCSUSER QUOTA UNLIMITED ON DOCSIND;
ALTER USER DOCSUSER QUOTA UNLIMITED ON DOCSTEMP;

Import de dump-file:

– export NLS_LANG=’AMERICAN_AMERICA.WE8ISO8859P1′
imp system/<passwd> file=/backup/oradata/backup/hbt/exports/export_hbt.dmp log=/backup/oradata/backup/hbt/exports/imp_hbt.log buffer=10000 fromuser=docsadm touser=docsadm

Edit the tnsnames.ora on the DM-server.

The Hummingbird – release used, is 5.1.0.5,  service release 6, maintenance 4. You need also to copy some files on the DM-server:

To use the English Library Generator:  Copy Library Generator (tblcomp.exe), and
the files DOCSOR32.DLL and DOCSDB32.DLL to the …\Hummingbird\DM
Server\Programs folder

Restart the DM-server.

zv7qrnb