README 2005/05/04 1. MSDSD Full Release Database version: beta00.05_2005-03-10 Data version: 13-2005_03_10 Import files suitable for any server platform and versions of Oracle 9.2.0.5 and 9.2.0.6 Contact melford@ebi.ac.uk, Last modified May 2005 2. Documentation Documentation for the structure of MSDSD at the table level is available online at: http://www.ebi.ac.uk/msd/dbdoc/ 3. Installaion Of MSDSD Installation involves the creation of a schema (WHOUSE1) in an existing database and the importing of data into it. The directory where this README file is located and its subdirectories contain scripts and data files that are used to create the full WHOUSE1 schema that stores the MSDSD data. To install MSDSD download all files and directories in this directory (approx 28GB) to your local client or server computer and follow instrtuctions below. To access the MSDSD installation area of the current MSDSD release you need to: ftp ftp.ebi.ac.uk login: msdftp password: enter usual password cd ~msdftp cd pub/beta00/oracle_exports/full00.05 follow instructions in README file Note: use "ls -l" or "dir" and not just "ls" to see the contents of every directory. You should have received the password on the ftp area at the time you have received acknowledgement of the MSDSD licence approval. If you have forgotten the password or you want to start using MSDSD immediatelly and for any other request of question contact msdhelp@ebi.ac.uk 4. Incremental Updates We supply regular incremental updates to the data in MSDSD on our ftp site. To access this: ftp ftp.ebi.ac.uk login: msdftp password: enter usual password cd ~msdftp cd pub/update_scripts follow instructions in README file 5. Notes 5a. The data and scripts supplied with this release of MSDSD is suitable for any hardware server platform using Oracle 9.2.0.5 or 9.2.0.6. The import may work with Oracle 10g but this has not been tested and is not supported as yet. Later on this year we will have a version of MSDSD out for MySQL. 5b. Minimum Requirements Disk storage 1 x 5 GB disk for Oracle software (e.g. /u01/app/oracle/admin/MSDSD) 2 x 5 GB disks for log files and control files (e.g. /u02/oradata/MSDSD, /u03/oradata/MSDSD) 300 GB on as many disks as possible for Oracle datafiles Available memory of 1GB (your hardware should have 2GB) 2 CPUs Oracle 9.2.0.5 or Oracle 9.2.6 Characterset of database must be UTF8 5c. Installing MSDSD Over A Network The whole of the installation of MSDSD can be executed on a computer other than the database server, as long as you have an Oracle client ins tallation available and your environment is configured to log onto the target database. This may be useful if you are short of disk space on the server or are unable to download files directly to it. 5d. Operating System And Environment The instructions provided in this README file are UNIX-specific. If you are following them on a Windows machine, they should still work if you have installed Cygwin (http://www.cygwin.com) and are working from a Cygwin shell prompt. 6. Installation Steps The installation process involves the following steps and could easily take a couple of days. Time estimates for a 4 CPU SUN server with data files spread across 2 RAID disks and log files on single internal disks given. a. Set Oracle environment (quick) b. Create WHOUSE1 tablespaces (5 hrs) c. Create WHOUSE1 schema and assign privileges (quick) d. Create tables for WHOUSE1 schema (quick) e. Import data into ATOM and ATOM_DATA tables using SQL*Loader (5 hrs) f. Import data into the other WHOUSE1 tables (8 hrs) g. Create constraints and build indexes for WHOUSE1 tables (16 hrs) 6a. Set Oracle Environment (quick) Make sure the environment variables below are set in the shell where the import scripts will be executed. Settings for our MSDSD database are given below: ORACLE_SID MSDSD ORACLE_BASE /u01/app/oracle ORACLE_HOME ${ORACLE_BASE}/product/9.2.0 ORACLE_TERM vt100 TNS_ADMIN ${ORACLE_BASE}/product/9.2.0/network/admin/ ORACLE_PATH ${ORACLE_HOME}/bin:${ORACLE_HOME}/orainst LD_LIBRARY_PATH ${ORACLE_HOME}/lib/:/usr/lib/X11/lib:$ORACLE_HOME/ctx/lib PATH /usr/bin:/usr/sbin:/usr/openwin/bin:/usr/ccs/bin:/usr/local/bin:${ORACLE_PATH} NLS_LANG american_america.utf8 NLS_DATE_FORMAT 'DD-MON-YYYY HH24:MI:SS' In addition to the above: The characterset for the database where MSDSD will be installed MUST BE UTF8. Make sure that 'gzip' is in your path. Version for SUN OS 5.8 included with this release. 6b. Create WHOUSE1 Tablespaces (5 hrs) In this step the tablespaces used to store the tables and indexes of the whouse1 schema are created. The table below gives a list of tablespaces to be created. Tablespace Size (GB) Data (GB) % Full whouse1_atom 20 14.0 70 whouse1_atom_i 9 6.0 67 whouse1_atom_data 76 53.3 70 whouse1_atom_data_i 18 12.7 70 whouse1_comp 8 5.1 64 whouse1_comp_data 14 9.4 67 whouse1_data 96 68.9 72 whouse_struct_data 15 10.3 69 temp 16 undo 24 A script create_tablespaces.sql is provided which you can edit and execute to create the whouse1_* tablespaces. Spread out datafiles for each tablespace on as many disks as you can to distribute I/O load. I use 4096M as the maximum size of my datafiles because I find that size convenient to manage for backups and file transfers. A minimum of 16GB temporary tablespace is recommended for large sorts and a minimum of 24GB for rollback segments during imports. A total of 256 GB will be required for the WHOUSE1 tablespaces and a further 40 GB for undo and temporary tablespaces. This allows 30% free space which will accommodate several incremental data updates in future. 6c. Create WHOUSE1 Schema And Assign Privileges (quick) 1. Create user WHOUSE1 default tablespace whouse1_data 2. Grant connect and resource roles to WHOUSE1 3. Grant unlimited tablespace priviledge to WHOUSE1 4. Create role WHOUSE1_SELECT 5. Grant WHOUSE1_SELECT to WHOUSE1 with admin option; The script create_whouse1_user.sql can be edited and used to execute the above steps in 6c. 2d. Create Tables For WHOUSE1 Schema (quick) Execute the following in a directory that contains whouse1_tables.dmp: imp userid=whouse1@msdsd file=whouse1_tables.dmp log=whouse1_tables.log fromuser=whouse1 touser=whouse1 This will create all the tables in the WHOUSE1 schema. 6e. Import data into ATOM and ATOM_DATA tables using SQL*Loader (5 hrs) cd to the atom_data_table directory ./import.csh |& tee import.out cd to the atom_table directory ./import.csh |& tee import.out 6f. Import Data Into The Other WHOUSE1 Tables (8 hrs) If you have problems uncompressing with gunzip on Solaris, use the gzip executable included in the download set and set the environment variable GZIPEXE to the full path to this executable: setenv GZIPEXE /path/to/my/gzip # Csh or tcsh GZIPEXE=/path/to/my/gzip; export GZIPEXE # Bourne shell, Ksh or bash cd to your local directory corresponding to: pub/beta00/oracle_exports/full00.05 on the ftp server cd other_tables chmod u+x import_other_tables.csh Edit import_other_tables.csh. Go to the following line: set inst=MSDSD change "MSDSD" to the service identifier of your target database Run the import_other_tables.csh script: ./import_other_tables.csh |& tee import.out (Csh, tcsh) or ./import_other_tables.csh 2>&1 | tee import.out (Bourne shell, bash, ksh) (enter password of the WHOUSE1 database user when prompted) IMPORTANT - IMPORTANT!!! This script should finish with the message: Imports ran OK. If you do not see this message, something has gone wrong. DO NOT CONTINUE WITH THE INSTALLATION. Places to check for error messages on what has gone wrong: The output of this script The import log files (there should be one for each table, called .log Look in the most recently-created one first Things that might have gone wrong: Insufficient tablespace available Using a broken build of gzip that cannot handle large files Contact us if you get stuck. The script creates a file .done for each successful import, or a file
.err where the import failed. If any of the imports fail, fix the problem, do: /bin/rm -f *.err in the other_tables directory and log on to the database and truncate the table. Run the mport_other_tables.csh script again as before: ./import_other_tables.csh |& tee import.out (Csh, tcsh) or ./import_other_tables.csh 2>&1 | tee import.out (Bourne shell, bash, ksh) The import will continue with the table that had problems. If you want to do the complete import again, execute the following first: /bin/rm -f *.done *.err Return to the full00.05 directory: cd .. 6f. Create Constraints And Build Indexes For WHOUSE1 Tables (16 hrs) Note Limit records per block for tables that will have bitmap indexes. This step is optional, but will help prevent certain indexes from occuping an excessive amount of disk space as the contents of their tables is updated. sqlplus whouse1@msdsd @min_rec.sql (If you have an account on metalink.oracle.com, see document 103490.1 for more information on this.) imp whouse1@msdsd file=whouse1_constraints.dmp ignore=y log=whouse1_constraints.log statistics=safe \ fromuser=whouse1 touser=whouse1