ORACLE9i INSTALLATION AND GUS ARCHITECTURE DOCUMENT

AT Center for Tropical and Emerging Global Diseases

Chetna Warade
chetna@uga.edu
waradec@yahoo.com

To install Genomics Unified Schema following the are the softwares/packages required:

  1. Oracle 9i for a Database server
  2. Perl 5 for user interaction with the Database server
  3. Java 2 Platforms for WWW user interaction with the Database server
  4. Ant build tool

First lets take a step by look at the installation process.

CHAPTER 1


ORACLE 9i


  1. Download the Oracle9iServer Release 2(9.2.0.1.0) for UNIX Systems from the website http://otn.oracle.com/software/products/oracle9i/content.html
    1. Download the Complete Files 674014720 bytes lnx_920_disk1.cpio 603018752 bytes lnx_920_disk2.cpio 460834304 bytes lnx_920_disk3.cpio
    2. Directions to extract the files
      • Run "gunzip <filename>" on all the files. Eg. lnx_920_disk1.cpio.gz
      • Extract the cpio archives with the command "cpio -idmv < <filename>" Eg. cpio -idmv <lnx_920_disk1.cpio
      Important Note: Some browsers will uncompress the files but leave the 
      extension the same (gz) when downloading.  If the above steps do not work 
      for you, try skipping step 1 and go directly to step 2 without changing 
      the filename. Eg. "cpio -idmv <Linux9i_Disk1.cpio.gz"

c. Create a user id in the oracle website. This user id is very useful for

debugging purposes. With this user id one can use the online documentation.

2) Installation of following modules from Oracle9i Enterprise Edition:

        Oracle9i Database 
        - Data Warehouse

        Oracle9i Client 
        - Administrator
        - Runtime

        Oracle9i Management and Integration
        - Oracle Management Server 
        - Oracle Internet Directory (failed installation)  

3) Preinstallation steps:

Setup Tasks to Perform as root User

Setup Tasks to Perform as oracle User


VARIABLE DESCRIPTION
  DISPLAY          The name, server number and screen number where
                   of the system where Oracle Universal Installer
                   display its GUI
  PATH             Shell's search path for executables

4) Installation

Oracle Universal Installer

If this is the first time any Oracle9i product has been installed on the current system, the File Locations window appears. Specify the base directory where you want to install the Oracle software. i.e /home/oracle/OraHome1

The File Locations window appears. Do not change the text in the Source field. The Source field specifies the location of the installation files.

     You must install Oracle9i products into a new Oracle home directory. You 
     cannot install Oracle9i products into a directory that contains older 
     versions of the software.
     After selecting Oracle9i database from the Available Products window, the 
     Installation Types window appears.

     - Select Enterprise, Standard, then click Next.

     Oracle9i Enterprise Edition Installation
    
         1. Select the appropriate database and click Next.

            If you select...        Then Oracle Universal Installer... 
            General Purpose         Installs a preconfigured database 
                                    optimized for general purpose usage.
            Transaction Processing  Installs a preconfigured database optimized
                                    for transaction processing. 
            Data Warehouse          Installs a preconfigured database optimized
                                    for data warehousing and OLAP. 
            Customized              Allows you to create a customized database.
                                    This option takes longer than the 
                                    pre-configured options. 
            Software Only           Installs software only and does not run any
                                    configuration tools. 
       
          NOTE - I selected Data Warehouse option. GUS is a Dataware House

     - Click Next.
     - The Database Identification window appears.
     - Enter the Global Database Name and System Identifier (SID) in the 
       appropriate fields: i.e GUS (TGUS)

     - Click Next. The Database File Location window appears.
     - In the Directory for Database Files field, enter the directory location
       of the database file, i.e /db_storage.
     - Click Next. The Database Character Set window appears
       Choose the database character set that you want to use from the 
       available options i.e default
     - Click Next. The Summary window appears.
     - Review the information to ensure that you have enough disk space and 
       click Install.

       The Install window appears and displays a progress meter. The Installer 
       goes through the install and relinking phases, so the meter adjusts for 
       each phase completion.

       NOTE - It takes a while for completion.

     - Run the root.sh script when prompted.

       The Installer creates the root.sh script in the Oracle home directory and       prompts you to run the script when it finishes installing Oracle products.      Log in as the root user and run the script. The root.sh script sets the 
       necessary file permissions for Oracle products and performs other 
       root-related configuration activities. To run the root.sh script use the        following commands:

       # cd $ORACLE_HOME
       # ./root.sh

       When the root.sh script runs successfully, return to the Oracle Universal       Installer, and click OK in the Alert window.
      Click Exit to exit the Oracle Universal Installer, or click Next Install 
      to install additional products. Selecting Next Install returns you to the       Oracle Universal Installer File Locations window.

5. Post Installation

Configuration Tasks to Perform as the root User

-Automating Database Startup and Shutdown The dbstart and dbshut scripts are located in the $ORACLE_HOME/bin directory

Perform the following tasks to set up the dbstart and dbshut scripts so that they are called at system startup. This process must be completed for every new database that you want to configure for automated startup and shutdown.

  1. Edit the /etc/oratab Database entries in the oratab file appears in the following format:

ORACLE_SID:ORACLE_HOME:{Y|N}

       In the preceding command, Y or N specifies whether you want the dbstart 
       and dbshut scripts to start up and shut down the database. For each 
       database that you want to start up, find the ORACLE_SID entry identified        by the sid in the first field. Change the last field for each to Y.

2. In the /etc/rc.d/init.d directory, create a dbora script.

3. Create symbolic links to the dbora script in the appropriate run-level

        script directories, as follows:
        /etc/rc.d/rc0.d/K10dbora
        /etc/rc.d/rc3.d/S30dbora
        /etc/rc.d/rc4.d/S30dbora
        /etc/rc.d/rc5.d/S30dbora

Configuration Tasks to Perform as the oracle User

umask 0022

        EPC_DISABLED=F
        export EPC_DISABLED

        ORACLE_BASE=/db_storage/Oracle
        export ORACLE_BASE

        ORACLE_HOME=/home/oracle/OraHome1
        export ORACLE_HOME

        CLASSPATH=$ORACLE_HOME/JRE/lib:$ORACLE_HOME/product/jlib
        export CLASSPATH

        LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export LD_LIBRARY_PATH

        ORACLE_SID=GUS
        export ORACLE_SID

        TNS_ADMIN=$ORACLE_HOME/network/admin
        export TNS_ADMIN

        DISPLAY=mango.ctegd.uga.edu:0.0
        export DISPLAY

        HOME=/home/oracle
        export HOME

        PATH=:$ORACLE_HOME/bin:/usr/local/netscape:/usr/local/Acrobat5/bin/:$PATH:$HOME/bin

        export PATH

        unset USERNAME

Post-Installation for Installed Oracle Products

Basic configuration of Oracle Net Services is done by Oracle Net Configuration Assistant when it is started by Oracle Universal Installer during installation.

Verify and complete your initial configuration with the following steps:

  1. Log in as root and reserve a port for the Oracle Net listener by making the following entry in the /etc/services file of each Oracle Net Services node on the network:
       listener_name 1521/tcp         #Oracle Net listener
       i.e LISTENER 1521/tcp

      In the preceding command, 1521 is the default port number. If you chose a       different port when you configured the Oracle Net listener, specify that 
      port in the /etc/services file.

   2.  Check the status of the listener following the installation by using the        following command:

       $ lsnrctl status listener_name
       i.e $ /home/oracle/OraHome1/bin/lsnrctl status LISTENER

      The listener_name  field is required if the listener has a name other than      the default listener.

      If the listener is not running, start it by using the following command:

      $ lsnrctl start listener_name
       i.e $ /home/oracle/OraHome1/bin/lsnrctl start LISTENER

3. Install and configure Oracle client software on a remote system, if

necessary, then start SQL*Plus to test the connection to the server.

       $ sqlplus username/password@net_service_name
       i.e sqlplus scott/tiger@gus

       If you can successfully connect to the server with SQL*Plus, you have 
       established network connectivity over TCP/IP.

5. Using Oracle Net Configuration Assistant After installation is complete, a more detailed configuration can be accomplished using the Oracle Net Configuration Assistant by using the following command in the $ORACLE_HOME/bin directory:

$ netca

Oracle Net Configuration Assistant configures the Oracle client/server network environment. It modifies the configuration files located in the default $ORACLE_HOME/network/admin directory. Review the Oracle Net Configuration Assistant procedure for your product installation choice.

Oracle9i Database Enterprise Edition and Standard Edition Installation

For Enterprise and Standard installations, the Oracle Net Configuration Assistant performs the following task:

      Configures the Oracle Net server environment by configuring the following files:
            listener.ora: Oracle Net Services configures a listener with the name and protocol address you select. Oracle Net Services also configures a protocol address and static service information for external procedures.

            sqlnet.ora: Oracle Net Services configures the server's network domain as the default domain, which is the same as the network domain of your system. The domain is automatically appended to any unqualified net service name given in the connect string. The sqlnet.ora file also configures the naming methods the server uses to resolve a name to connect descriptor.

            tnsnames.ora: Oracle Net Services creates a net service name entry to use for external procedure connections.

6. Using Database Configuration Assistant Start Database Configuration Assistant by using the dbca command located in the $ORACLE_HOME/bin directory:

$ dbca

Database Configuration Assistant enables you to copy an Oracle9i preconfigured database, or create a fully customized database to match your selected environment and database configuration. It starts automatically after Oracle9i software has been installed during Oracle9i installation.

When installing Oracle9i software using any database configuration option other than the Custom and Software Only, the Oracle Universal Installer prompts for a global database name and System Identifier (SID). After Oracle9i installation is completed, the Database Configuration Assistant uses this information to create the database. In addition, the Database Configuration Assistant automatically configures the static service information for the Oracle9i database in the listener.ora file.

In our case we need Data Warehouse option: If you select this, the Database Configuration Assistant creates a database that is fully enabled for data warehousing applications. The OLAP option, consisting of support for analytic workspaces and the OLAP catalog metadata repository (CWMLite), is included in the database.

7. Using Oracle Enterprise Manager Configuration Assistant Oracle Enterprise Manager Configuration Assistant is used to configure the local Oracle Management Server. You can configure the local Oracle Management Server by creating, upgrading or deleting an Oracle Enterprise Manager repository.

Start OEM by
$ oemapp console

8. Installation logs are generated in the directory /home/oracle/oraInventory/logs

Logs can be viewed to preview what options were selected during installation.

9. Whenever a new database is created system file called as initYourDataBaseName.ora is created in the directory $ORACLE_HOME/dbs.

Alternative:
CREATING A SERVER PARAMETER FILE

The server parameter file must initially be created from a traditional text initialization parameter file. It must be created prior to its use in the STARTUP command. The CREATE SPFILE statement is used to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

The following example creates a server parameter file from initialization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

Another example, below, illustrates creating a server parameter file and supplying a name.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM PFILE='/u01/oracle/dbs/test_init.ora';

The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.

Oracle recommends that you allow the database server to default the name and location of the server parameter file. This will ease administration of your database. For example, the STARTUP command assumes this default location to read the parameter file.

When the server parameter file is created from the initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All other comments are ignored.

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to recreate the same server parameter file that is currently being used by the instance.

Note:

When you use the Database Configuration Assistant (DBCA) to create a database, it can automatically create a server parameter file for you.

10. Starting up the database using the oracle user login. Login as oracle user.

$sqlplus "/ as sysdba"         Because this is considered as  the most secure
                               oracle login no password is asked.

SQL> startup

STARTUP USAGE

Starts an Oracle instance with several options, including mounting, and opening a database.

STARTUP options | migrate_options

where options has the following syntax:

[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] | [ OPEN [open_options] [dbname] ] |
NOMOUNT ]

where open_options has the following syntax:

READ {ONLY | WRITE [RECOVER]} | RECOVER

and where migrate_options has the following syntax:

[PFILE=filename] MIGRATE [QUIET]

BUGS
Whenever oracle user logs in, should set the ORACLE_SID env variable $ ORACLE_SID=GUS
$ export ORACLE_SID
$ sqlplus /nolog

CHAPTER 2


GUS 3.0 SCHEMA INSTALLATION


GUS 3.0 source code/binaries (doesn't matter as PERL is used) are available at http://cvsweb.sanger.ac.uk/ under project name GUS. Follow the cvs instructions on the web site.

There are two domains in GUS 3.0 a project domain and a central place for gus installation. The GUS build system (ANT based) allows users to install latest copies of the source at one central location (also accessible for all users on the system). More information is available http://www.gusdb.org/documentation/install-setup.html

SCHEMA INSTALLATION:

In order to install the GUS 3.0 schema in the database create a new database in Oracle using instructions for Oracle 9i in chapter 1 or use an existing Oracle database. Use the oracle user account "sys" for creating the GUS 3.0 schema and users. Run the script create-db.sh after modifying the sys password (depending on what passwd you have selected for your database) in it. The script create-db.sh creates five schemas/users Core,SRes, DoTS, TESS and RAD3 with passwords password1, password2, password3, password4 and password5 respectively for simplicity. It is recommended that the database administrator should change the passwords for security reasons.

CHAPTER 3


JAVA 2 PLATFORMS


Following are the Java Platforms required for GUS user/www interface development.

  1. Java 2 Standard Edition - J2SDK 1.4.1_01
  2. Java 2 Enterprise Edition - J2SDKEE 1.3.1
  3. Java Web Services Developer Pack - JWSDP 1_0_01

JAVA 2 STANDARD EDITION - J2SDK 1.4.1_01

     Download Java 2 SDK, Standard Edition 1.4.1_01
     -----------------------------------------------------------------

        Go to web page http://java.sun.com/j2se/1.4.1/download.html

        Click on the "Download" link in the row that contains Linux self-
        extracting file and column SDK. 

        Read the license agreement and if you agree with it Click on Accept.

        Click on the link "Download j2sdk-1_4_1_01-linux-i586.bin"
    
        Save this file to your disk.
    
        Refer to the link for installation help at (if required)
        http://java.sun.com/j2se/1.4.1/install-linux.html 

        1) Check the download file size.
        2) Copy j2sdk-1_4_1_01-linux-i586.bin to the /usr/local directory 
           to provide java environment system-wide.
        3) Run j2sdk-1_4_1_01-linux-i586.bin
 
        Unbundling the software automatically creates a directory called 
        j2sdk1.4.1_01. Note that if you choose to install the Java 2 SDK into 
        system-wide location such as /usr/local, you must first become root to 
        gain the necessary permissions. If you do not have root access, simply 
        install the Java 2 SDK into your home directory, or a subdirectory that         you have permission to write to.

JAVA 2 ENTERPRISE EDITION - J2SDKEE 1.3.1

     Download Java 2 SDK, Enterprise Edition 1.3.1
     -----------------------------------------------------------------
        Go to web page http://java.sun.com/j2ee/download.html

        Under the section "1.3.1 FCS Release January 31, 2002" click the 
        link "Software & Documentation" or http://java.sun.com/j2ee/sdk_1.3/

        Scroll down the web page and select a platform (Linux) and click 
        continue.

        Read the license agreement and if you agree with it Click on Accept.

        Select a mode of tranfer either HTTP or FTP.
  
        Save this file to your disk.
    
        Refer to the link for installation help at (if required)
        http://java.sun.com/j2ee/sdk_1.3/install.html#linux_soft        

        1) Check the download file size.
 
        2) cd /usr/local

        Unpacking the bundle automatically creates a subdirectory in this 
        directory called j2sdkee1.3.1.

        3) The download bundle for the software is in the 
        j2sdkee-1_3_1-linux.tar.gz file. To uncompress and unpack the download 
        bundle and run this command:

          tar xvzf j2sdkee-1_3_1-linux.tar.gz

        The j2sdkee1.3.1 directory is created and the software is installed 
        into it.

        4)  Set the environment variables.
        Before running the J2EE SDK, you must set these environment variables 
        in your .profile or .bash_profile file of your login:

        J2EE_HOME - the directory where you've installed this release.
        JAVA_HOME - the directory where the Java 2 SDK Standard Edition is 
                    installed.
        PATH      - include the bin directory beneath the directory where you've                    installed this release.

        If you need help setting these variables, paste the link
        http://java.sun.com/j2ee/sdk_1.3/install.html#env-var. 
        You need to logout and login again for these changes to take effect.

JAVA WEB SERVICES DEVELOPER PACK - JWSDP 1_0_01

     Download Java Web Services Developer Pack 1.0_01
     -----------------------------------------------------------------

        Go to web page http://java.sun.com/webservices/downloads/webservicespack.html

        Scroll down the web page and select platform as UNIX because there is no        specific differences in Linux/UNIX version of JWSDP and click continue. 

Read the license agreement and if you agree with it Click on Accept.

Select a mode of tranfer either HTTP or FTP.

Save this file to your disk.

        Refer to the link for installation help at (if required)
        http://java.sun.com/webservices/downloads/install-unix.html

        1) Check the download file size

            If you saved the self-installing executable to disk without running             it from the download page at the Java Software website, check to see            that you have the complete file:

                example: jwsdp-1_0_01-unix.sh  31,732,238 bytes 

        2) Run the Java WSDP installer

        $ /bin/sh jwsdp-1_0_01-unix.sh

        Installed Directory Tree

        Verify that the Java WSDP has the directory structure shown below.

        * jwsdp-1_0_01
                   * LICENSE
                   * bin
                   * common
                   * conf
                   * config
                   * docs
                   * images
                   * lib
                   * logs
                   * samples
                   * server
                   * services
                   * shared
                   * temp
                   * tools
                   * unist
                   * webapps
                   * work
                   * xsl

3) Update the PATH variable

You can run the Java WSDP without setting the PATH variable, or you can optionally set it as a convenience.

        4) Set the PATH variable if you want to be able to conveniently run the            Java WSDP executables (startup.sh, shutdown.sh, ant, etc.) from any 
           directory without having to type the full path of the command. If you           don't set the PATH variable, you need to specifythe full path to the            executable every time you run it, such as:

        $ /home/myuser/jwsdp-1_0_01/bin/startup.sh

           It's useful to set the PATH permanently so it will persist after 
           rebooting. To set the PATH permanently, add the full path of the bin            directory in the Java WSDP installation directory to the beginning of           your PATH variable. Typically this full path looks something like 
           /home/myuser/jwsdp-1_0_01/bin.

ANT JAVA BASED BUILD TOOL

Download Ant build tool version 1.5.1 for building (compilation) and installation of source code and binaries on the machine from http://ant.apache.org/bindownload.cgi.

Note: GUS 3.0 is tested with Ant 1.5.1 version and is used as the build tool at CBIL and CTEGD.

SETTING ENVIRONMENT/LOGIN FOR GUS 3.0

A sample .bash_profile file for using Oracle9i, GUS schema, Java and Perl in entirety is:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then

. ~/.bashrc
fi

# User specific environment and startup programs

export ORACLE_HOME=/home/oracle/OraHome1

export J2EE_HOME=/usr/local/j2sdkee1.3.1

export JAVA_HOME=/usr/local/j2sdk1.4.1_01

export JWSDP_HOME=/usr/local/jwsdp-1_0_01

export TOMCAT_PATH=:/usr/local/jwsdp-1_0_01/common/lib/servlet.jar:/usr/local/jwsdp-1_0_01/docs/tutorial/examples/cb/jaxrpc/build/client:/usr/local/jwsdp-1_0_01/docs/tutorial/examples/cb/jaxrpc/build/server:/usr/local/jwsdp-1_0_01/docs/tutorial/examples/cb/jaxrpc/build/service-class:/usr/local/jwsdp-1_0_01/docs/tutorial/examples/cb/jaxrpc/build/registry

export CLASSPATH=$JAVA_HOME/lib/tools.jar:$JAVA_HOME/jre/lib/rt.jar:$TOMCAT_PATH

export PATH=:/usr/local/netscape:/usr/local/Acrobat5/bin/:$JAVA_HOME/bin:$JWSDP_HOME/bin:$ORACLE_HOME/bin:$PATH:$HOME/bin

export GUS_HOME=/home/gusdev/gus.2.0.1/source-code/gusdev-src-sep-10-2002 export LD_LIBRARY_PATH=/home/oracle/OraHome1/lib export PERL5LIB=$GUS_HOME/perl/lib:$GUS_HOME/lib export CATALINA_HOME=/usr/local/jwsdp-1_0_01 export GUS_CFG=$GUS_HOME
export DBI_DSN="dbi:Oracle:host=mango.ctegd.uga.edu;sid=GUS" export ORACLE_SID=GUS

NOTE

1) Setting up the env variable CLASSPATH is very important otherwise it may cause problems in using java compilers and java tools like run-time linker/debugger.

2) Refer http://www.gusdb.org/documentation/install-setup.html for setting environment variables for GUS 3.0

JAVA SERVLET INTERFACE INSTALLATION INSTRUCTIONS: GENERAL

-Download the external Java libraries mentioned in the prerequisites. -Edit $GUS_HOME/www/install/installServletNew.pl; the section labeled "EDIT THIS SECTION AS NEEDED" contains a number of variables that must be set to tell the script where to find and place a number of files.

-Set the ORACLE_HOME and LD_LIBRARY_PATH environment variables as described in the prerequisites; both in the environment of the user that will run Tomcat and also in the Apache httpd.conf file (with 'SetEnv' directives) -cd into the $GUS_HOME/www/install directory and run installServletNew.pl -Edit the installed web.xml file as neeeded -Start the servlet engine
-Check the following logs for any errors: /var/log/httpd/error_log (i.e., httpd error log) $CATALINA_HOME/logs/*
servlet log file (location depends on what is specified in web.xml)

JAVA SERVLET INTERFACE INSTALLATION INSTRUCTIONS: GUS 3.0

In addition to following the steps the section JAVA SERVLET INTERFACE INSTALLATION INSTRUCTIONS: GENERAL you need to download tag libraries (binary) from http://www.apache.org/dist/jakarta/taglibs/standard/binaries/ for GUS 3.0

CHAPTER 4


LEARNING GUS 3.0


GUS 3.0 source code/binaries (doesn't matter as PERL is used) are available at http://cvsweb.sanger.ac.uk/ under project name GUS. Follow the cvs instructions on the web site.

PERL MODULES:

Following are the PERL modules needed for GUS 3.0 and are available at http://cpan.org:

  1. DBI 1.28 The Perl Database Interface
  2. DBD Oracle 1.12 An Oracle 7 and Oracle 8 interface for Perl 5 but also works for Oracle 9i
  3. PerlTools 1.2.0a A Java package built using OROMatcher(TM) that makes Perl regular expressions extremely easy to use by taking the responsibility of compiling and matching out of the programmer's hands.
  4. GD 1.32 GD.pm -- A perl5 interface to Thomas Boutell's gd library. This is a autoloadable interface module for libgd, a popular library for creating and manipulating PNG files. With this library you can create PNG images on the fly or modify existing files. This version of GD REQUIRES libgd 1.8.3 or higher.

    Note: The latest GD 2.07 is available but requires libgd 2.0.12 or higher located in /usr/lib directory (default linux installation - may vary depending on system configuration)

  5. XML::Simple An easy API to read/write XML (esp config files). XML::Simple requires a module capable of parsing XML - either XML::SAX or XML::Parser must be installed (if you're running ActivePerl, you'll already have XML::Parser installed).
  6. Tie::IxHash Implements ordered hashes
  7. Parse::Yapp Used by GUS's Genbank parser

INITIAL DATA SETUP FOR GUS 3.0

Background:
Suppose in a bioinformatics laboratory there are different projects undertaken and there are several groups working on projects. A GUS/database user belongs to a group (e.g. to the bioinformatics lab) and can work on several projects at the same time. Accordingly then information goes to table Core.ProjectInfo, Core.GroupInfo and Core.UserInfo (read further for more information).

Creating GUS 3.0 users:
There are two types of users in GUS system: an Oracle/database user and the GUS 3.0 user. The Oracle/DB user account allows user to login into Oracle database using command line utility like SQLPlus and perform various database related operations. A GUS user account is created using both Oracle user account and the machine/system/operating system user account. This two level user account helps maintain tight security and controlled information flow within the organization and GUS as well (GUS/database could be synonyms in this case).

Following are steps required for GUS Administrator/DBA to start GUS 3.0 from scratch (could also create am .sql script): INSERT INTO CORE.AlgorithmParamKeyType VALUES(0,'string',SYSDATE,1,1,1,1,1,1,1,1,1,1); INSERT INTO CORE.AlgorithmParamKeyType VALUES(1,'float',SYSDATE,1,1,1,1,1,1,1,1,1,1); INSERT INTO CORE.AlgorithmParamKeyType VALUES(2,'int',SYSDATE,1,1,1,1,1,1,1,1,1,1); INSERT INTO CORE.AlgorithmParamKeyType VALUES(3,'ref',SYSDATE,1,1,1,1,1,1,1,1,1,1); INSERT INTO CORE.AlgorithmParamKeyType VALUES(4,'boolean',SYSDATE,1,1,1,1,1,1,1,1,1,1); INSERT INTO CORE.AlgorithmParamKeyType VALUES(5,'date',SYSDATE,1,1,1,1,1,1,1,1,1,1);

NOTE: It is imperative that the DBA will set the read/write permission according to the security and row_user_id, row_group_id and row_project_id depending on custom data.

Following are steps required for GUS Administrator/DBA to create GUS 3.0 users:

  1. Grant permissions (insert, select, delete, update and references) using grantPermissions.sql to the new Oracle user account from CORE,SRES,TESS,DOTS and RAD3 depending upon project requirements. e.g. grantPermissions.pl --db-sid=GUS --db-host=mango.ctegd.uga.edu --permissions=select,insert,delete,update,references --grantees=luchtan,gus30 --owner=dots --login=dots Enter password at the prompt
  2. Set up the GUS config file: $ cp $PROJECT_HOME/install/gus.properties.sample $GUS_CONFIG_FILE Edit the config file to reflect your personal information; this file will be read by the various GUS applications and plugins to determine such things as: which Oracle server to connect to; which Oracle logins to use for read-only and/or read-write access to the GUS database; your GUS username, default group, and default project. You may have multiple GUS config files, but will need to change your GUS_CONFIG_FILE environment variable whenever you wish to switch between them.
  3. Adding project, group and user information in the database/GUs 3.0 The DBA should add project information in Core.ProjectInfo table, group information in Core.GroupInfo, machine information in Core.Machine and GUS user information Core.Userinfo. Following are the dependencies: Core.ProjectInfo.Project_id equal to Core.UserInfo.Row_Project_id Core.GroupInfo.Group_id equal to Core.UserInfo.Row_Group_id
  4. Matching GUS config file with the database Make userName of .gus.properties equal to login of CORE.USERINFO group of .gus.properties equal to name of CORE.GROUPINFO project of .gus.properties equal to name of CORE.PROJECTINFO

GUS PERL OBJECT LAYER:

Before starting to use the perl object layer all core plugins need to be registered in the database, do $ga +meta +create

Help can be viewed by e.g.
ga GUS::Common::Plugin::LoadTaxon --help

Debugging can be done using --debug, --verbose or --veryVerbose options and not using --commit option. By default --commit is set as off.

  1. Taxonomy
  2. Download taxonomy file taxdump.tar.gz from ftp://ftp.ncbi.nih.gov/pub/taxonomy/ b)ga +create GUS::Common::Plugin::LoadTaxon --commit c)ga GUS::Common::Plugin::LoadTaxon --gencode=/file-path/gencode.dmp --names=/file-path/names.dmp --nodes=/file-path/nodes.dmp --commit

NOTE: use all the three options of ga GUS::Common::Plugin::LoadTaxon --gencode, --names and --nodes at the same time/together

2) GenBank

  1. Download nucleotide data from GenBank http://www.ncbi.nlm.nih.gov/Entrez/ and for example say typed query is "trypanosoma cruzi", set limit without EST and then save the files. b)ga +create GUS::Common::Plugin::GBParser --commit
  2. For loading GenBank data just needs one entry about the source database (e.g. GenBank) in sres.externaldatabase and sres.externaldatabaserelease. For the sequences which come from other databases GBParser automatically fills both the sres.external database and sres.externaldatabaserelease tables.

e.g. Sres.ExternalDatabase

insert into sres.externaldatabase values('GenBank (nrdb)','genbank (nrdb)', SYSD ATE,1,1,1,1,1,0,6,3,2,1);

Sres.ExternalDatabaseRelease
insert into sres.externaldatabaserelease values(135,1,15-APR-03,'Tcruzi', SYSDATE,1,1,1,1,1,0,6,3,2,1);

and Dots.SequenceType (create a .sql script)

insert into Dots.sequencetype values (1,'DNA',null,null,1,1,'DNA', 'DNA,unkown standedness',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (2,'RNA',null,null,1,1,'RNA', 'RNA,unkown standedness',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (3,'DNA',null,'ds',2,1,'ds-DNA', 'double stranded DNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (4,'DNA',null,'ss',2,1,'ss-DNA', 'single stranded DNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (5,'DNA',null,'ss',2,2,'ss-RNA', 'single stranded RNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (6,'RNA',null,'ds',2,2,'ds-RNA', 'single stranded RNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (7,'RNA','mRNA','ss',3,5,'mRNA', 'mRNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (8,'RNA','est','ss',3,5,'EST', 'EST - could be mRNA, rRNA...',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (9,'RNA','tRNA','ss',3,5,'tRNA', 'tRNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (10,'RNA','rRNA','ss',3,5,'rRNA', 'rRNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (11,'unknown',null,null,1,11,'unknown', 'unkown',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (12,'RNA','predicted_mRNA','ss',2,1,'predicted_mRNA', 'mRNA sequence predicted by an algorithm from genomic DNA',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (13,'virtual',null,null,1,null,'virtual', 'virtual nuclieic acid sequence',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (14,'DNA','GSS','ds',3,3,'GSS', 'Genome Survey Sequence',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Dots.sequencetype values (15,'DNA','oligonucleotide','ss',3,4,'oligonucleotide', 'synthetic single stranded oligonucleotide ',SYSDATE,1,1,1,1,1,0,6,3,2,1);

3) GOOntology

  1. Download the three gene ontology files located at ftp://ftp.geneontology.org/pub/go/ontology/ They are named process.ontology, component.ontology, and function.ontology.
  2. ga +create GUS::Common::Plugin::LoadGoOntology --commit
  3. Use --createRelease option when running the plugin to automatically generate an External Database Release Id in SRes.ExternalDatabaseRelease for these releases of the ontology branches. Add entries in SRes.ExternalDatabase with names "GO Function", "GO Component", and "GO Process".

e.g. SRes.ExternalDatabase

##These id values 92,93,94 are used because they were the next in order.. insert into Sres.Externaldatabase values(92,'GO Function','go function',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Sres.Externaldatabase values(93,'GO Component','go component',SYSDATE,1,1,1,1,1,0,6,3,2,1); insert into Sres.Externaldatabase values(94,'GO Process','go process',SYSDATE,1,1,1,1,1,0,6,3,2,1);

SRes.GORelationshiptype

insert into sres.GORelationshiptype values(1, 'isa',SYSDATE, 1,1,1,1,1,0,6,3,2,1); insert into sres.GORelationshiptype values(2, 'partof',SYSDATE, 1,1,1,1,1,0,6,3,2,1);

NOTE: Since we loaded GO data from scratch we encountered problems in ancestor_go_term_id in table SRes.GOTerm. Login as SReS in the database and modify as following:

alter table goterm modify (ancestor_go_term_id number(10) null);

NOTE: 05/22/2003

We had comment off line 500 to overcome an error but I guess in future this willgo away as GUS is constantly evolving.

(line 500 of LoadGoOntology.pm):

my $ontologyGoTerm = GUS::Model::SRes::GOTerm->new({ go_id => $rootGoId,
external_database_release_id => $extDbRelId, source_id => $rootEntry->getId(),
name => $rootEntry->getName(), #just the name definition => $rootEntry->getName(),
minimum_level => 0,
maximum_level => 0,
number_of_levels => 1,
ancestor_go_term_id => $tempAncestorId, <=== COMMENT THIS LINE } );

4) TIGR XML data

Under Construction