Re: Using oracle dump (quickly!)

Dan Bolser wrote:

> Hi, and thanks everyone for the replies, I will start looking at the
> various material various people suggested.
>
> I really appreciate your willingness to help!
>
> As requested, here is the details which come with the dump... hopefully
> they should help answer some of the questions you have... (that I should
> know)...
>
> Here are 'schema' details (god knows how they are created)...
>
> http://www.ebi.ac.uk/msd-srv/docs/dbdoc/
>
> I include 'hardware requirements' below just to make you jelous... ;)
>
> NOTES---
>
> To download
> - ftp to 'ftp.ebi.ac.uk ', log in as 'xxxx', password
> 'xxxx'
>
> *(sorry, you need a licence to download).
>
> - cd pub/beta00/oracle_exports/full00.01
> - download all files
> - follow instructions in README
> - uncompress files (I used gzip)
> - install
>
> Some notes from the README file:
>
> MSDSD Full Release
> - Database version: beta00.01_2003-12-01
> - Data version: 01-2003_10_02
> - Import files suitable for any server platform and versions of Oracle
> 9.0.1.3 plus
>
> 1. Notes
> 1b. The importing of data into an Oracle database is suitable for any
> hardware server platform using Oracle 9.0.1.3 or later. We recommend
> 9.0.1.3, 9.0.1.4 or 9.2.0.4 as we have had problems with other versions.
>
> 1c. Minimum Recommendation
> 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)
> - 150 GB, as many disks as possible for Oracle *.dbf files
> (e.g. /u04/oradata/MSDSD)
> Available memory 1GB (i.e. your hardware should have at lease 2GB)
> 2 CPUs
>
> --END-NOTES
>
>
> README---
>
> 2. This release contains all the files required to import MSDSD into an
> existing database
> You need to:
> a. Create required tablespaces
> b. Set Oracle environment variables
> c. Create WHOUSE1 schema and assign privileges
> d. Create and populate the majority of the database tables
> e. Create and populate the ATOM and ATOM_DATA tables
> f. Create constraints and build indexes
>
> 2a. Create required tablespaces
>
> 2a. Create required tablespaces
> A total of 104 GB space will be needed for the WHOUSE1 tablespaces. Create
> the tablespaces below. Spread out the datafiles for each tablespace on as
> many disks
> as you can to distribute I/O load. It may take half a day plus to create
> all the tablespaces so this can be done whilst files are being downloaded.
> Tablespace Name Size (GB)
> WHOUSE1_ATOM 10.94
> WHOUSE1_ATOM_DATA 37.60
> WHOUSE1_ATOM_DATA_I 14.45
> WHOUSE1_ATOM_I 3.71
> WHOUSE1_COMP 3.13
> WHOUSE1_COMP_DATA 6.00
> WHOUSE1_DATA 22.56
> WHOUSE1_STRUCT_DATA 4.69
> TEMP 4.00
>
>
> Note a minimum of 4GB temporary tablespace is recommended for large sorts
> and a
> minimum of 4GB for rollback segments (say 8GB to be safe) for the imports.
>
> 2b. Set Oracle environment variables
> Make sure the environment variables below are set in the shell where the
> import
> scripts will be executed. Settings for our MSDSD database is given below:
> ORACLE_SID MSDSD
> ORACLE_BASE /u01/app/oracle
> ORACLE_HOME ${ORACLE_BASE}/product/9.0.1.3
> ORACLE_TERM vt100
> TNS_ADMIN ${ORACLE_BASE}/product/9.0.1.3/network/admin/
> ORACLE_PATH ${ORACLE_HOME}/bin:${ORACLE_HOME}/orainst
> LD_LIBRARY_PATH
> ${ORACLE_HOME}/lib/:/usr/lib/X11/lib:$ORACLE_HOME/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
>
>
> 2c. Create WHOUSE1 schema and assign privileges
> (1) Grant connect and resource roles to WHOUSE1 user
> (2) Grant unlimited tablespace priviledge to WHOUSE1 user
>
>
> 2d. Create and populate the majority of the database tables:
> The following instructions can be used on a machine other than the
> database server. This may be useful if you are short of disk space on the
> server machine or have been unable to download the files directly to
> it. The network overhead involved in doing this should be similar to that
> involved in transferring the files to the server machine in any case. To
> check whether this is possible, issue the command:
>
> $ORACLE_HOME/bin/tnsping <sid>
>
> where <sid> is the service identifier of the target database and see if
> the connection is successful.
>
> These instructions are Unix-specific. If you are using a Windows machine,
> they should still work if you have installed Cygwin
> (http://www.cygwin.com) and are working from a Cygwin shell prompt.
>
> Make sure that the Oracle environment has been set up correctly in the
> shell where you will be executing the import scripts, in particular that
> the ORACLE_HOME
> environment variable is set.
>
> Make sure that 'gzip' is in your path.
>
> (1) cd to your local directory corresponding to
> pub/beta00/oracle_exports/full00.01 on the ftp server, if you are not
> there already.
>
> - Create and populate the majority of the database tables:
>
> (a) cd other_tables
> (b) chmod u+x import_other_tabs.csh
> (c) Edit import_other_tabs.csh. Go to the following line:
>
> set inst=MSDSD
>
> and change "MSDSD" to the service identifier of your target
> database.
>
> (d) Run the import_other_tabs.csh script, capturing the output into a
> file as follows:
>
> ./import_other_tabs.csh |& tee import.out (Csh, tcsh)
> or
> ./import_other_tabs.csh 2>&1 | tee import.out (Bourne shell,
> bash, ksh)
> (enter password of the WHOUSE1 database user when prompted)
>
> (d) Return to the full00.01 directory:
>
> cd ..
>
> 2e. Create and populate the ATOM and ATOM_DATA tables:
>
> (a) $ORACLE_HOME/bin/sqlplus WHOUSE1@<sid> @create_atom_tabs.sql
>
> Substitute the service identifier of your database for <sid>
>
> (b) cd atom_table
> (c) chmod u+x import.csh
> (d) Edit import.csh. Go to the following line:
>
> set inst=MSDSD
>
> and change "MSDSD" to the service identifier of your target
> database.
>
> (e) Run the import.csh script, capturing the output into a file as
> follows:
>
> ./import.csh |& tee import.out (Csh, tcsh)
> or
> ./import.csh 2>&1 | tee import.out (Bourne shell, bash, ksh)
>
> (enter password of the WHOUSE1 database user when prompted)
>
> (f) cd ../atom_data_table
>
> (g) Repeat steps (c) - (e)
>
> (h) Return to the full00.01 directory:
>
> cd ..
>
> 2f. Create constraints and build indexes:
>
> (a) gzip -d whouse_constraints.dmp.gz
>
> (b) imp whouse1@<sid> file = whouse_constraints.dmp ignore = y log =
> whouse_constraints.log
>
>
> These steps will take a long time (hours), depending on your
> hardware: 2(d), 3(e), 4(b)
>
>
> ---END-README
>
> Hmmmm.... maby I have been really dumb, if I just follow the above
> (roughly) what else do I need to know?
>
> Sorry if I have been a big waste of time.... :|
>
> Dan.
>
>
>

Drink (lots!) of coffee/tea/mineral water/beer/<sid>? Substitute the beverage of your liking for <sid>.

Looks quite OK to me; problem is this does not look like a 'getting started with Oracle' database. It does place your "I want to get started in a day" in another perspective. Actually - it looks like a V7.0 database, with separate index and table tablespaces, that are put on the same mount point. So - where's your I/O spread there?

Bet there are initial segment allocations in the exports, that would hold all data in 1 segment.

Which would make it all sub-optimal for a 9.2 environment, but workable.

And... locate in what document steps 3 (e) and 4 (b) are documented, they take long, as does 2 (d)

-- 

Regards,
Frank van Bortel