Creating the Database User and Tablespaces

It is important that the database is created using the AL32UTF16/UTF8 character set. This step assumes that Oracle is already operational. It is recommended that an Oracle DBA carry out this step. To run the required scripts and database import, you must be signed in to the target computer as the oracle user. You must also have access to the Oracle system user.

The first script will create four tablespaces required by ExtraView. When you run the script, you will be prompted for location of the data files. You can spread these out if you wish, or keep them in one directory (for example /oracle/oradata/ev). The script supplied allocates four tablespaces. If the size of your installation suggests you need differently sized tablespaces, this script can be modified. Please consult with ExtraView if you require help for this stage.

The second script will create the extraview user account within Oracle. Note that you must supply a password. Please ensure you keep a record of the password for future access. You will also need this in the installation process when you configure the Apache Tomcat application server.

Note: The time taken for the system to create and format the tablespaces is dependent on the size you select.

Oracle 11g Configuration Requirement

There is a default setting in Oracle 11g, that must be changed before you import the ExtraView database.

ALTER system SET deferred_segment_creation=false;

For information on this setting, please refer to http://www.dbsnaps.com/oracle/oracle-deferred-segement-creation/

Required Scripts

create_tablespace.sql

CREATE TABLESPACE “EXTRAVIEW”
LOGGING
DATAFILE ‘&extraviewTsLocation/extraview01.dbf’ SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE TABLESPACE “EXTRAVIEW_IDX”
LOGGING
DATAFILE ‘&extraviewTsLocation/extraview_idx01.dbf’ SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE TABLESPACE “EXTRAVIEW_SESSION”
LOGGING
DATAFILE ‘&extraviewTsLocation/extraview_session01.dbf’ SIZE 300M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE TABLESPACE “EXTRAVIEW_LOB”
LOGGING
DATAFILE ‘&extraviewTsLocation/extraview_lob01.dbf’ SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

create_user.sql

CREATE USER extraview PROFILE “DEFAULT”
IDENTIFIED BY &&PASSWORD
DEFAULT TABLESPACE “EXTRAVIEW” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;
GRANT “CONNECT” TO extraview;
GRANT “RESOURCE” TO extraview;
GRANT CREATE TABLE to extraview;
GRANT CREATE VIEW to extraview;
GRANT ALTER SESSION to extraview;
exit

For Linux Installations

cd
sqlplus system/password @create_tablespace.sql
sqlplus system/password @create_user.sql

For Windows Installations

Open a command prompt Navigate to the database directory

sqlplus system/password @create_tablespace.sql
sqlplus system/password @create_user.sql

Import the ExtraView Database into Oracle

Your contact at ExtraView will have provided you with a database export file, either containing a system that is designed for your company’s business processes, or a standard ExtraView system. Place that file in the same directory as the two files above.

Without signing off from the computer, perform this next step. This will import the ExtraView schema and initial data into Oracle from the installation directory.

Note: if the fromuser was not specified in an email from ExtraView, it will generally be the same as the filename of the provided dmp file. For example, a file named “evt701.dmp” would have a fromuser of “evt701”

imp system/password file=<your company>.dmp fromuser= touser=extraview commit=y

Oracle Database Maintenance

The Oracle database requires minimal maintenance, and most routine maintenance can be scheduled on your server, using cron or the equivalent with a Windows-based operating system.

Oracle internally manages recovery of space from deleted records within your database. For efficiency, however, Oracle indexes may need to be rebuilt if there is a very intense use of updates and deletes. With most usage of ExtraView, this is not the case and there is no need to rebuild indexes on a routine basis.

If you are using nightly exports for your backup, there should be no transaction logs to worry about. If you are using hot backups, then you should have a cron job that deletes the old archive log files after they are backed up.

For the best performance, one task that should to be done on a regular basis is to analyze the objects in the database. These are statistics used by the Oracle query optimizer when it builds query plans. It is recommend that you do this once per week, via a cron job, and whenever large amounts of data have been loaded using evimport, or the web-based import tool, etc. Following is an example script that can be adapted and used for this purpose. Note that you have to edit the file oracle.env and analyzeExtraView.sh and substitute the appropriate directory paths and passwords.

File analyzeExtraView.sh

#!/bin/bsh
# source in the env file
ENV=/u01/oracle/admin/prod01/dba/oracle.env
if [ -f "" ]; then
  .
else
  exit 1
fi
SCRIPT=/analyzeExtraView.sql
LOG=/analyzeExtraView.txt  
cd
if [ -f "" ]; then
  sqlplus @
else
  exit 1
fi  
mail -s "Analyze schemas for " <  
rm -f

File analyzeExtraView.sql

SET SERVEROUTPUT ON SIZE 5000
set echo off
set linesize 400
set term on
set feedback off
set head off
set pages 0
set verify off
spool analyzeExtraView.txt
exec DBMS_OUTPUT.PUT_LINE('Analyzing objects ' || to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss'));
exec dbms_utility.analyze_schema(USER, 'COMPUTE');
exec DBMS_OUTPUT.PUT_LINE('Done analyzing objects ' || to_char(sysdate, 'dd-MON-yyyy hh24:mi:ss')); spool off
exit

Entry for the crontab table

#MI HH DOM MOY DOW
13 01 * * * /u01/oracle/admin/prod01/dba/analyzeExtraView.sh

Entries in the oracle.env file

#!/bin/bsh  
# Oracle Environment
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/product/9.2
export ORACLE_SID=ev
export ORACLE_TERM=xterm
export NLS_LANG=American_America.UTF8;
export ORA_NLS33=/ocommon/nls/admin/data
export LD_LIBRARY_PATH=/lib:/lib:/usr/lib:/usr/local/lib  
# Set shell search paths export PATH=:/bin::/bin  
# admin directories
export UDUMP=/admin//udump
export BDUMP=/admin//bdump
export ARCH=/u02/oracle/arch/
export BIN=/admin//bin
export DBA=/admin//dba  
# misc
export SYSTEM_AUTH=system/XXX
export EXTRAVIEW_AUTH=extraview/XXX
export TODAY=$(date +%d-%b-%y)
export NOTIFY="valid email address"
export BACKUP_DIR=/u03/oracle/backup
export LOG=/tmp/log.txt

Finally, make sure you have Oracle’s cursors configured to an adequate number. In a typical database installation, please configure at least 1,000, but if you are likely to support hundreds of concurrent users, consider a higher number. This is an inexpensive resource and there is little downside to configuring a large number of cursors.