Using oracle dump (quickly!)

From: Dan Bolser <dmb_at_mrc-dunn.cam.ac.uk>
Date: Mon, 23 Feb 2004 20:09:46 +0000
Message-ID: <Pine.LNX.4.21.0402231957100.29951-100000@mail.mrc-dunn.cam.ac.uk>

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:

Create required tablespaces
Set Oracle environment variables
Create WHOUSE1 schema and assign privileges
Create and populate the majority of the database tables
Create and populate the ATOM and ATOM_DATA tables
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

                                                                                
          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. Received on Mon Feb 23 2004 - 14:09:46 CST