Oracle 7.3.3 to 8i - exporting info

[Timothy Czarnik]

Hey all,

Some background: I'm looking for some info for our DBAs. We are currently running Oracle 7.3.3 on HP-UX 10.20. We are looking to move to Oracle 8i on HP-UX 11i (on a different server). The current Oracle database is about 30 Gb. I personally know very little about Oracle, but I'm told that you can perform a full export in 7.3.3 and use that to import into 8i to move the database. However, the DBAs said that with the 2 GB file size limitation in HP-UX 10.20, this is not an option.

Basically, I'm looking for a way to move a 30 GB Oracle 7.3.3 database running on an HP-UX 10.20 K class to Oracle 8i runing on an HP-UX 11i rp7400.

I don't really need specifics just yet, just some 10,000 foot ideas on how to best go about doing this that I can run by the DBAs.

Thanks in advance!

Tim

[A. Clay Stephenson]

Okay Tim: The 10,000 ft. level is 'named pipes'. Perhaps you would like me to descend to 5,000 ft. The idea is that you set up a named pipe, e.g. mknod /tmp/mypipe p. To do the export, first make sure you have a filesystem with largefiles enabled. Next setup a cat process to read from the named pipe and write to your actual export file.

e.g. cat < /tmp/mypipe > /u01/myfile.exp

You then direct the Oracle export to /tmp/mypipe and your are done.

The 8i import can normally handle large files but if not, you can do the inverse operation for the import as well.

Regards, Clay

[James R. Ferguson]

Hi Tim:

You can have filess up to 128GB on 10.20 if you are running JFS version 3:

http://docs.hp.com/hpux/onlinedocs/5971-2383/5971-2383.html

However, if that is an issue for you, you can export into a pipe (see man 'mknod' or man 'mkfifo') and using 'split' divide your Oracle export into multiple files.

Regards!

...JRF...

[James R. Ferguson]

Hi (again) Timothy:

I thought I'd remembered an earlier post describing the actual implementation of the export using a pipe with 'split'. Here it is:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xcc4668c57f64d4118fee0090279cd0f9,00.html

Regards!

...JRF...

[Tom Danzig]

If largefiles is enabled on the mount you're dumping to, the 2GB file limit is a non-issue.

To save additinal space, you can compress the export on the flt with:

/etc/mknod /tmp/tmp_pipe p
nohup compress < /tmp/tmp_pipe >export_file_name.dmp.Z &

Food for thought ...

[Tom Danzig]

By the way, the import's going to take a while !!

[Carlos Fernandez Riera]

Or you can export to tape...

Or you can avoid export and create a data base link and use several ' create as select'... ( you dba will understand).

But, of course, 10.20 can manage files greater than 2GB, if you create a filesystem with largefiles option.

[Yogeeraj_1 · Accepted solution]

hi,

just sharing something my guru (tom kyte) explained a few months back. Hope this helps.
Best Regards
Yogeeraj
PS. Get your DBAs to buy his last book: One-on-One Expert Oracle.
---------------------------------------------

You would export to a device that does not support seeking such
as a tape (not recommended, really slow) or a pipe.

Why not using compression? it'll considerably cut down on the
size?

I myself use both compression AND split to make my export be in
many managable sized file (500meg is my chosen size). You could
just use split and not compress if you want.

Basically, you would create a pipe in the OS via:

$ mknod somefilename p

and then export to that pipe. you would set up another process
in the background that 'eats' the contents of this pipe and puts
it somewhere. I use split, you could use 'cat' to just put it
into another file (if cat supports files >2 gig -- thats the
problem here, most utilities do not, you need to use a special
file io api to 2 gig file support).

Here is a script you can use as a template. Yes, it uses
compression but you can take that out. Its here to show you one
method of doing this.


------------------------------
#!/bin/csh -vx

setenv UID /
setenv FN exp.`date +%j_%Y`.dmp
setenv PIPE /tmp/exp_tmp_ora8i.dmp

setenv MAXSIZE 500m
setenv EXPORT_WHAT "full=y COMPRESS=n"

echo $FN

cd /nfs/atc-netapp1/expbkup_ora8i
ls -l

rm expbkup.log export.test exp.*.dmp* $PIPE
mknod $PIPE p

date > expbkup.log
( gzip < $PIPE ) | split -b $MAXSIZE - $FN. &

# uncomment this to just SPLIT the file, not compress and split
#split -b $MAXSIZE $PIPE $FN. &

exp userid=$UID buffer=20000000 file=$PIPE $EXPORT_WHAT >>&
expbkup.log
date >> expbkup.log


date > export.test
cat `echo $FN.* | sort` | gunzip > $PIPE &

# uncomment this to just SPLIT the file, not compress and split
#cat `echo $FN.* | sort` > $PIPE &

imp userid=sys/o8isgr8 file=$PIPE show=y full=y >>& export.test
date >> export.test

tail expbkup.log
tail export.test

ls -l
rm -f $PIPE
--------------------------------------------------

This also always does an 'integrity' check of the export right
after it is done with an import show=y, that shows how to use
these split files with import.
--------------------------------------------------

!!!
cat `echo $FN.* | sort` | gunzip > $PIPE &


sorts the filenames, sends them to cat, which give them to gunzip in the right
order.

[Timothy Czarnik]

Hey all,

Thanks for all the responses! I got what I wanted, and that is the general consensus is that we should export using named pipes in some form or another.

I can't tell you how much I appreciate all the help! Thanks again!

Tim