Setup Database Server
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 )
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.
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 /
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.
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.
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)
)
)';