Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Exports > 2GB -- need scripts

RE: Exports > 2GB -- need scripts

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Mon, 20 Nov 2000 14:18:43 -0500
Message-Id: <10686.122530@fatcity.com> 





This is a multi-part message in MIME format.



--Boundary_(ID_PXRCiJvvFkDZCWX5H/tksw)


Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit



"split the export up by table, tablespace, or table related groups of tables
(related via FK's) and do individual exports of each"



That could prove difficult if you want a 'consistent' export.
  -----Original Message-----


  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Chuck
Hamilton


  Sent: Monday, November 20, 2000 1:10 PM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: Exports > 2GB -- need scripts




  If the datbase is 45g, I'd expect that the export will be over 2g even if
compressed. Even if I compress with gzip which yields a better compression
ration that compress, the best ration I get is about 4:1. He's going to need
to both compress and split. Something like this would work.



  mkfifo expdat.dmp


  compress < expdat.dmp | split -b 2048m - export_
  exp us/pw parfile=exp.par



  To import an individual table however, you need to search through the
entire export. That could take quite a long time. What I would recommend
doing is to split the export up by table, tablespace, or table related
groups of tables (related via FK's) and do individual exports of each. You
could do them in parallel to speed it up. That way the export is granular
enough that you can quickly recover an individual table without having to
scan through a single huge export file.



    Allan Davis Sahadeo <asahadeo_at_neal-and-massy.com> wrote:




    I was wondering, if the size of the compressed file did exceed 2 gigs
then


    we would indeed be in a bit of a predicament. Since an export does
compress


    fairly nicely, we may not encounter such a problem. But what if we
did...


    What would we do then ? Do we split our compressed files ? And if so how
do


    we ?


    Allan.





    Here are the scripts for export and import:



    For export:


    mknod t1.dmp p


    compress < t1.dmp> t10.dmp.Z &


    exp us/pw parfile=exp.dat



    For Import:


    /etc/mknod t1.dmp p


    dd of=t10.dmp if=t1.dmp &


    imp us/pw parfile=imp.dat



    Ramani


    DBA




    >>> 11/17/00 01:25PM >>>




    I have a 45GB Oracle 8.0.5 database on HPUX. I want to be abl! ! e
    to do nightly exports so that if any of the tables gets "accidentally"
    truncated or whatever, we can restore just the table and not
    have to restore the entire database.



    Does anybody have a script that uses the split(?) utility and
    compress so that the export isn't bigger than 2GB? What about
    importing? Can just one table be picked out of that large of
    an export if the export is split up and compressed? Please let
    me know your experience/advice on doing this.





    Sent using MailStart.com ( http://MailStart.Com/welcome.html )
    The FREE way to access your mailbox via any web browser, anywhere!



    --


    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --


    Author:


    INET: cemail_at_sprintmail.com



    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists




    To REMOVE yours! ! elf from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).



    --


    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --


    Author: Akhil Ramani


    INET: Akhil.Ramani_at_3cc.co.wayne.mi.us



    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists




    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for oth! ! er information (like subscribing).



    --


    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --


    Author: Allan Davis Sahadeo


    INET: asahadeo_at_neal-and-massy.com



    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists




    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).







--
  Do You Yahoo!?
  Yahoo! Calendar - Get organized for the holidays!

--Boundary_(ID_PXRCiJvvFkDZCWX5H/tksw)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D350141819-20112000><FONT face=3DArial color=3D#0000ff =
size=3D2>"<FONT=20
face=3D"Times New Roman" color=3D#000000 size=3D3>split the export up by =
table,=20
tablespace, or table related&nbsp;groups of&nbsp;tables (related via =
FK's) and=20
do individual exports of each"</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=3D350141819-20112000></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D350141819-20112000><FONT face=3DArial color=3D#0000ff =
size=3D2>That=20
could prove difficult if you want a 'consistent' =
export.</FONT></SPAN></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px =
solid">
  <DIV class=3DOutlookMessageHeader><FONT size=3D2>-----Original=20
  Message-----<BR><B>From:</B> root_at_fatcity.com =
[mailto:root_at_fatcity.com]<B>On=20
  Behalf Of</B> Chuck Hamilton<BR><B>Sent:</B> Monday, November 20, 2000 =
1:10=20
  PM<BR><B>To:</B> Multiple recipients of list =
ORACLE-L<BR><B>Subject:</B> Re:=20
  Exports &gt; 2GB -- need scripts<BR><BR></FONT></DIV>
  <P>If the datbase is 45g, I'd expect that the export will be over 2g =
even if=20
  compressed. Even if I compress with gzip which yields a better =
compression=20
  ration that compress, the best ration I get is about 4:1. He's going =
to need=20
  to both compress and split. Something like this would work.</P>
  <P>mkfifo expdat.dmp<BR>compress &lt; expdat.dmp | split -b 2048m -=20
  export_<BR>exp us/pw parfile=3Dexp.par</P>
  <P>To import an individual table however, you need to search through =
the=20
  entire export. That could take quite a long time. What I would =
recommend doing=20
  is to split the export up by table, tablespace, or table =
related&nbsp;groups=20
  of&nbsp;tables (related via FK's) and do individual exports of each. =
You could=20
  do them in parallel to speed it up. That way the export is granular =
enough=20
  that you can quickly recover an individual table without having to =
scan=20
  through a single huge export file.</P>
  <P>&nbsp; <B><I>Allan Davis Sahadeo=20
  &lt;asahadeo_at_neal-and-massy.com&gt;</I></B> wrote: <BR>
  <BLOCKQUOTE=20
  style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px =
solid">I=20
    was wondering, if the size of the compressed file did exceed 2 gigs=20
    then<BR>we would indeed be in a bit of a predicament. Since an =
export does=20
    compress<BR>fairly nicely, we may not encounter such a problem. But =
what if=20
    we did...<BR>What would we do then ? Do we split our compressed =
files ? And=20
    if so how do<BR>we ?<BR>Allan.<BR>----- Original Message =
-----<BR>To:=20
    "Multiple recipients of list ORACLE-L" =
<ORACLE-L_at_FATCITY.COM><BR>Sent:=20
    Friday, November 17, 2000 4:50 PM<BR><BR><BR>Here are the scripts =
for export=20
    and import:<BR><BR>For export:<BR>mknod t1.dmp p<BR>compress &lt; =
t1.dmp&gt;=20
    t10.dmp.Z &amp;<BR>exp us/pw parfile=3Dexp.dat<BR><BR>For=20
    Import:<BR>/etc/mknod t1.dmp p<BR>dd of=3Dt10.dmp if=3Dt1.dmp =
&amp;<BR>imp us/pw=20
    parfile=3Dimp.dat<BR><BR>Ramani<BR>DBA<BR><BR>&gt;&gt;&gt;=20
    <CEMAIL_at_SPRINTMAIL.COM>11/17/00 01:25PM &gt;&gt;&gt;<BR><BR>I have a =
45GB=20
    Oracle 8.0.5 database on HPUX. I want to be abl! ! e<BR>to do =
nightly=20
    exports so that if any of the tables gets =
"accidentally"<BR>truncated or=20
    whatever, we can restore just the table and not<BR>have to restore =
the=20
    entire database.<BR><BR>Does anybody have a script that uses the =
split(?)=20
    utility and<BR>compress so that the export isn't bigger than 2GB? =
What=20
    about<BR>importing? Can just one table be picked out of that large =
of<BR>an=20
    export if the export is split up and compressed? Please let<BR>me =
know your=20
    experience/advice on doing this.<BR><BR>-----<BR>Sent using =
MailStart.com (=20
    http://MailStart.Com/welcome.html )<BR>The FREE way to access your =
mailbox=20
    via any web browser, anywhere!<BR><BR>--<BR>Please see the official =
ORACLE-L=20
    FAQ: http://www.orafaq.com<BR>--<BR>Author:<BR>INET:=20
    cemail_at_sprintmail.com<BR><BR>Fat City Network Services -- (858) =
538-5051=20
    FAX: (858) 538-5051<BR>San Diego, California -- Public Internet =
access /=20
    Mailing=20
    =
Lists<BR>----------------------------------------------------------------=
----<BR>To=20
    REMOVE yours! ! elf from this mailing list, send an E-Mail =
message<BR>to:=20
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in<BR>the=20
    message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the =
name of=20
    mailing list you want to be removed from). You may<BR>also send the =
HELP=20
    command for other information (like =
subscribing).<BR><BR>--<BR>Please see=20
    the official ORACLE-L FAQ: http://www.orafaq.com<BR>--<BR>Author: =
Akhil=20
    Ramani<BR>INET: Akhil.Ramani_at_3cc.co.wayne.mi.us<BR><BR>Fat City =
Network=20
    Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, =
California --=20
    Public Internet access / Mailing=20
    =
Lists<BR>----------------------------------------------------------------=
----<BR>To=20
    REMOVE yourself from this mailing list, send an E-Mail =
message<BR>to:=20
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in<BR>the=20
    message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the =
name of=20
    mailing list you want to be removed from). You may<BR>also send the =
HELP=20
    command for oth! ! er information (like subscribing).<BR><BR>-- =
<BR>Please=20
    see the official ORACLE-L FAQ: http://www.orafaq.com<BR>-- =
<BR>Author: Allan=20
    Davis Sahadeo<BR>INET: asahadeo_at_neal-and-massy.com<BR><BR>Fat City =
Network=20
    Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, =
California --=20
    Public Internet access / Mailing=20
    =
Lists<BR>----------------------------------------------------------------=
----<BR>To=20
    REMOVE yourself from this mailing list, send an E-Mail =
message<BR>to:=20
    ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and =
in<BR>the=20
    message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the =
name of=20
    mailing list you want to be removed from). You may<BR>also send the =
HELP=20


Received on Mon Nov 20 2000 - 13:18:43 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US