Max Oracle Export Size

[Derek Baxter]

I know there was a couple other postings regarding this but it was not exactly my problem. I am using oracle 8.0.4 on a hp-ux 11. One of our databases export files have reached 2gig and cancels itself at that point. Does anyone have any suggestions that may help.
Thanks in advance

[Patrick Wallek]

Is the filesystem you are exporting to enabled for largefiles?

# fsadm /filesystem_name

If it is not largefiles enabled, you will need to do that.

If you have online JFS you can do:

# fsadm -o largefiles /filesystem_name

You will also need to check /etc/fstab and make sure that that filesystem has the largefiles mount option there so it gets mounted correctly next time you reboot.

[Steven E. Protter]

The problem is not oracle. Its the filesystem.

You need to enable the filesystem for largefiles.

If you have onlineJFS you can upgrade the filesystem via the fsadm command.

If like me, you don't have these powers yet, you need to back up the fs and then use newfs to recreate the fs.

umount /fs

newfs -F vxfs -o largefiles /dev/vg01/rfs

mount /fs

Now its up and clean and ready for largefiles mount.

For 11i you don't need the largefiles parameter in the /etc/fstab file.

SEP

[Derek Baxter]

I spoke with our Unix Administrator. We checked the system with fstab and it showed the following for this directory
/dev/vg107/lvol107 /u107 vxfs rw,suid,largefiles,delaylog,datainlog 0 2

So i believe taht Largefiles are enabled.

[Patrick Wallek]

Ask your unix admin to do:

# fsadm /u107

What does it return?

What happes if you try to create a 3GB file in that directory?

# prealloc testfile 3000000000

That will create a file called testfile of 3,000,000,000 size or approx. 3 GB.

[Bill Douglass]

Check the ulimit (ulimit -a) for user oracle.

Also, you can unmount a filesystem, fsck it, and set the largefiles bit using the standard version of fsadm (see fsadm_vxfs man page).

[Derek Baxter]

by running fsadm /u107 i get the following:
fsadm: /etc/default/fs is used for determining the file system type
nomultifsets
largefiles
by running prealloc testfile 3000000000 i create a 3gig file in /u107 with no error.
and by running ulimit -a for oracle it shows
file(blocks) ulimited

It looks to me like it is capable of making files that are bigger.

[Patrick Wallek]

OK, your filesystem can support largefiles.

It appears that it's time to dig into Oracle.

Unfortunately I'm not an Oracle person.

[Tim Sanko]

I should be able to help.

There is a cron patch that you will have to install if your export is being initiated by cron.

The ulimit for all ksh processes maxes at 2 GB/file.

if you echo your ulimit through the shells and it still fails it's most probable cause is the cron ulimit.

You can get it at the itrc search for the latest cron cumulative. you'll be all right.

[Brian Crabtree]

The way that we do it is to export to a pipe, and we normally compress the pipe as well. The following is a quick represenation.

mknod exp_pipe p
compress < exp_pipe > expdat.dmp.Z &
exp userid= file=exp_pipe full=y

This will export the database to a compressed file called "expdat.dmp.Z". To import it, you can uncompress it and import normally, or do the following:

mknod imp_pipe p
uncompress < expdat.dmp.Z > imp_pipe
imp userid= file=imp_pipe full=y

Hope this helps,

Thanks,

Brian

[Yogeeraj_1]

hi,

You can also try another solution if you are running 8i or later:

E.g.
$ORACLE_HOME/bin/exp $ACC_PASS filesize=1024M file=\($DMP_PATH1/yddbexp"$dt"FULLa.dmp, $DMP_PATH1/yddbexp"$dt"FULLb.dmp, $DMP_PATH1/yddbexp"$dt"FULLc.dmp, $DMP_PATH1/yddbexp"$dt"FULLd.dmp, $DMP_PATH1/yddbexp"$dt"FULLe.dmp\) buffer=409600 log=$LOG_PATH/yddbexp"$dt"FULL.log full=Y grants=Y rows=Y compress=N direct=n

Hope this helps too!

Best Regards
Yogeeraj

[Indira Aramandla]

Hi,
The 32bit issue affects Oracle in a number of ways. In order to use large files you need to have:
1. An operating system that supports 2Gb+ files or raw devices
2. An operating system which has an API to support I/O on 2Gb+ files
3. A version of Oracle which uses this API

At the time of writing most versions of export use the default file open API when creating an export file. This means that on many platforms it is impossible to export a file of 2Gb or larger to a file system file.

There are several options available to overcome 2Gb file limits with export such as:-

-It is generally possible to write an export > 2Gb to a raw device. Obviously the raw device has to be large enough to fit the entire export into it.

- By exporting to a named pipe (on Unix) one can compress, zip or split up the output.

- One can export to tape (on most platforms)

- Oracle8i allows you to write an export to multiple export files rather than to one large export file.

To Exporting >2Gb on Unix"
Calculating the size of an export file.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
% mknod /tmp/exp_pipe p
% dd if=/tmp/exp_pipe of=/dev/null bs=1024 &
% exp file=/tmp/exp_pipe

This will return the number of of 1K blocks the export file will be in

the following format : +0 records in
+0 records out

Hope tis helps

[Indira Aramandla]

The 32bit issue affects Oracle in a number of ways. In order to use large files you need to have:
1. An operating system that supports 2Gb+ files or raw devices
2. An operating system which has an API to support I/O on 2Gb+ files
3. A version of Oracle which uses this API

At the time of writing most versions of export use the default file open API when creating an export file. This means that on many platforms it is impossible to export a file of 2Gb or larger to a file system file.

There are several options available to overcome 2Gb file limits with export such as:-

-It is generally possible to write an export > 2Gb to a raw device. Obviously the raw device has to be large enough to fit the entire export into it.

- By exporting to a named pipe (on Unix) one can compress, zip or split up the output.

- One can export to tape (on most platforms)

- Oracle8i allows you to write an export to multiple export files rather than to one large export file.

To Exporting >2Gb on Unix"
Calculating the size of an export file.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
% mknod /tmp/exp_pipe p
% dd if=/tmp/exp_pipe of=/dev/null bs=1024 &
% exp file=/tmp/exp_pipe

This will return the number of of 1K blocks the export file will be in

the following format : +0 records in
+0 records out

I hope this helps

[Tim Sanko]

I assume that you are compressing it to .Z or .gz. If it is running out of cron you could be generating a file limited by cron. I went through that after putting in a previous cron patch.

Tim

[Derek Baxter]

We installed the cron patch. We are able to make files from cron that are 3 gig, but the export still failed. I will try to see if i can get it to work with the pipes.
Again thanks for all the help everyone.