Please help with full database export unix script.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Please help with full database export unix script.

  1. #1
    Join Date
    Jul 2002
    Posts
    13

    Please help with full database export unix script.

    Hi gurus,

    My Unix 2.5.1 server has a file size limitation of 2Gbytes, due to the 32 bit file system. I have a large database that needs exporting, so I have split it into schema level. Problem is, there is a schema with a table in there that produce exp dmp larger than 2Gbytes, so either way I am strapped by this file size limit.

    I have tried using background compress while exporting the file, and it works. I am able to send the dmp file straight to a compress pipe, and the final output is a *.Z file. The following is the script that I used:

    mknod /tmp/exp_pipe p
    compress < /tmp/exp_pipe > export.dmp.Z &
    exp file=/tmp/exp_pipe userid=system/manager full=y etc. etc.

    My question is as follows:
    I split the large database into three seperate exp jobs, and I use the above background compress method on all three segments. The following is the script:

    mknod /tmp/exp_pipe p
    compress < /tmp/exp_pipe > export1.dmp.Z &
    exp file=/tmp/exp_pipe userid=system/manager owner=A full=y etc.
    compress < /tmp/exp_pipe > export2.dmp.Z &
    exp file=/tmp/exp_pipe userid=system/manager owner=B,C full=y etc.
    compress < /tmp/exp_pipe > export3.dmp.Z &
    exp file=/tmp/exp_pipe userid=system/manager owner=D,E,F full=y etc.

    When I run this script, it seems to be working, but when I go to the tmp directory to look at the *.dmp.Z files as they come out, I notice that ALL THREE export*.dmp.Z files are growing simultaneously. Take a look at the following ls -lta results:

    #ls -lta
    -rw-r--r-- 1 oracle dba 23240704 Jul 21 11:06 export1.dmp.Z
    -rw-r--r-- 1 oracle dba 23666688 Jul 21 11:06 export2.dmp.Z
    -rw-r--r-- 1 oracle dba 23109632 Jul 21 11:06 export3.dmp.Z
    #ls -lta
    -rw-r--r-- 1 oracle dba 44072960 Jul 21 11:15 export1.dmp.Z
    -rw-r--r-- 1 oracle dba 43769856 Jul 21 11:15 export2.dmp.Z
    -rw-r--r-- 1 oracle dba 42811392 Jul 21 11:15 export3.dmp.Z
    #ls -lta
    -rw-r--r-- 1 oracle dba 47097739 Jul 21 11:17 export1.dmp.Z
    -rw-r--r-- 1 oracle dba 46726417 Jul 21 11:17 export2.dmp.Z
    -rw-r--r-- 1 oracle dba 45692955 Jul 21 11:17 export3.dmp.Z
    #

    Why are all three files growing simultaneously? Isn't Unix scripts supposed to step through one line at a time? I expected to see only the export1.dmp.Z file created and growing, then when it is completed, the next file should be created etc. I am worried that the three export processes are actually not running correctly and that maybe the dmp files are corrupted and unusable.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can export the data to multiple files. Use parameter FILESIZE to limit the maximum size. Specify the file names in FILE parameter or it will prompt you to enter filename.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Jul 2002
    Posts
    13
    I suppose I need to KISS. Heh... Thanks.

  4. #4
    Join Date
    Jul 2002
    Posts
    13
    Oh dear, I just found out that the FILESIZE parameter only works for 8i and above. Unfortunately I am using 7.3.3, so I guess I can't use such a clean method afterall...

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by wobbie
    I suppose I need to KISS. Heh... Thanks.
    No.. You need a HIT.

    Originally posted by wobbie
    owner=A full=y
    owner=B,C full=y
    FULL=y... FULL=y then owner =ABCD..?????


    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Jul 2002
    Posts
    13
    Sorry, that was a typo. It should be owner=A,B etc, without the full=y.

    Please get off your high horse kthxbye.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hay

    Relax.... "kthxbye" looks like an Oracle base table!!

    Create 3 separate pipe and do the export

    mknod /tmp/exp_pipe1 p
    mknod /tmp/exp_pipe2 p
    mknod /tmp/exp_pipe3 p
    compress < /tmp/exp_pipe1 > export1.dmp.Z &
    exp file=/tmp/exp_pipe1 userid=system/manager owner=A etc.
    compress < /tmp/exp_pipe2 > export2.dmp.Z &
    exp file=/tmp/exp_pipe2 userid=system/manager owner=B,C etc.
    compress < /tmp/exp_pipe3 > export3.dmp.Z &
    exp file=/tmp/exp_pipe3 userid=system/manager owner=D,E,F etc.


    Regards,
    Thomas
    Last edited by Thomasps; 07-21-2003 at 03:43 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Jul 2002
    Posts
    13
    Ahhh many thanks Tomas. I shall try that.

    BTW, what did you mean by "You need a HIT"? Is HIT an abbreviation for something? I took it to mean "knock on the head".

    My apologies for giving you the cold shoulder.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width