Setup Database Server

Owned by Checkmarx Bot (Unlicensed)
Last updated: Jan 21, 2020 by Jeff Duke
4 min read

Database Setup

VetView can be configured to work with Oracle 12c or newer.


Server Hardware and Operating System Information

VetView does not require any specific brand or model of server or Operating System. The VetView web server uses a standard JDBC connection to the database. We recommend at least 4GB of available RAM for oracle to operate. More memory may be required dependent on your user load and transaction volume.

  • 4GB RAM available for Oracle ( minimum )


Oracle Installation

VetView does not require an special installation, in our test environment we use 12c Release 2 for Windows x64. During the installation we use all the default settings, allocating 3-4 GB of RAM for use. This amount a memory may very based on your user load.

Example Setup:

    • Oracle Version - 12c Release 2 for Windows x64
    • 4GB of RAM available to the Database

Oracle Install Wizard Screen Shots ( Click to enlarge Images )

                    


Oracle Setup Summary Report


Once this is complete you should be able to log into the new database using the SYS account.

         

Oracle Setup for VetView

  • Define Tablespaces for VetView tables, indexes, and Working space. We will also want to create a tablespace for temporary UVIS data used during the migration.

Currently the setup and migration scripts use 4 default tablespaces

  • DATA_SMALL - Permanent tablespace for VetView data
  • DATA_INDEX - Permanent tablespace for VetView Indexes
  • UVIS_TEMP - Permanent tablespace for UVIS data use in migration
  • WORKSPACE - Temporary tablespace used for transactions
  • DATA_CHARGE_ADMIN - Permanent tablespace for VetView Hospital data

The VetView application does not have any restriction on the usage of tablespace and data management. Tablespaces can be managed and organized by local DBA for optimization of data access. 

Example Create Permanent Tablespace
CREATE TABLESPACE MASTER_DATA 
DATAFILE 'D:\APP\JDUKE\ORADATA\TEST\MASTER_DATA.DBF' 
SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/
Example Create Temporary Tablespace
CREATE TEMPORARY TABLESPACE WORKSPACE 
TEMPFILE 'D:\APP\JDUKE\ORADATA\TEST4\WORKSPACE.DBF' 
SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/


  • Create VetView user account

The VetView application uses a single oracle account to connect to the database. The example script provided grants the VetView account rights to create and update all the objects needed to create and update the database schema. In a production environment the VetView user will only need SELECT, UPDATE and DELETE on tables and sequences.

Example Create VetView Account
CREATE USER VETVIEW IDENTIFIED BY <password> DEFAULT TABLESPACE DATA_SMALL TEMPORARY TABLESPACE WORKSPACE PROFILE DEFAULT ACCOUNT UNLOCK ;
  -- 38 System Privileges for VETVIEW 
  GRANT CREATE ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ROLE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY VIEW TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY INDEX TO VETVIEW WITH ADMIN OPTION;
  GRANT DELETE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY INDEX TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY INDEX TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP TABLESPACE TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER TABLESPACE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE SESSION TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
  GRANT EXECUTE ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY PROCEDURE TO VETVIEW WITH ADMIN OPTION;
  GRANT GRANT ANY ROLE TO VETVIEW WITH ADMIN OPTION;
  GRANT FLASHBACK ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY TRIGGER TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE ANY VIEW TO VETVIEW WITH ADMIN OPTION;
  GRANT INSERT ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT LOCK ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY MATERIALIZED VIEW TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT BACKUP ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT DROP ANY ROLE TO VETVIEW WITH ADMIN OPTION;
  GRANT SELECT ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  GRANT MANAGE TABLESPACE TO VETVIEW WITH ADMIN OPTION;
  GRANT CREATE TABLESPACE TO VETVIEW WITH ADMIN OPTION;
  GRANT ALTER ANY ROLE TO VETVIEW WITH ADMIN OPTION;
  GRANT SELECT ANY SEQUENCE TO VETVIEW WITH ADMIN OPTION;
  GRANT UPDATE ANY TABLE TO VETVIEW WITH ADMIN OPTION;
  -- 2 Tablespace Quotas for VETVIEW 
  ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_SMALL;
  ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_INDEX;
  ALTER USER VETVIEW QUOTA UNLIMITED ON UVIS_TEMP;
  ALTER USER VETVIEW QUOTA UNLIMITED ON DATA_CHARGE_ADMIN;


  • Create a Database Link for migrating data directly from an UVIS database.

For migrating data from UVIS we have found the easiest way is to create a database link to a source UVIS database, and then importing data directly into the target VetView database.

Example Database Link
CREATE PUBLIC DATABASE LINK "UVIS.SOURCE.DATABASE"
 CONNECT TO UVIS
 IDENTIFIED BY <PWD>
 USING '(DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=1.0.0.1)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SID=UVISDB)
    )
  )';



Next Step

Create Database Schema