Re: EXP Parameters to take backup of 110 GB database

On Aug 6, 2:29 am, hjr.pyth..._at_gmail.com wrote:
> On Aug 6, 2:54 pm, Jitendra <jpsingh1..._at_gmail.com> wrote:
> > On Aug 3, 2:13 pm, Jitendra <jpsingh1..._at_gmail.com> wrote:
>
> > > Hi All,
>
> > > I have to take backup of 110GB Database. I have taken physical backup
> > > on tape, now I want to take Logical backup of whole database.
>
> > > What parameters should I use in EXP command (Using 9i Exp) ?
>
> > > Thanks and Regards,
> > > Jitendra
>
> > Hi All,
>
> > Thanks for your reply.
> > I have successfully exported the database without any warnings.
> > Dump file is 35 GB ......... :)
>
> For future reference, here's the parameters I use to take a full
> export of an 80GB 10g database (and because it might have to be used
> with 9i instances, we use exp not export datapump)
>
> exp sys/xxxx full=y file=expdb.dmp buffer=2000000000 compress=n
> log=exportdb.log direct=y
>
> Regards
> HJR
Howard,

Can you provide some clarification of what you wrote above? I have read in a couple books/articles that the SYS user should not be used to perform an export. If I recall correctly, it had something to do with SYS not being able to perform an object lock as regular users are able to do (memory is a bit fuzzy on the reason). One such comment can be found on page 2-11 (page 55 in the PDF) of "Administrator's Guide Release 2 (8.1.6) for Windows NT": "Note: To export an entire database, you must use the user name SYSTEM. Do not use INTERNAL or SYS." Is that no long true?

You also indicate that you use exp so that the export files from a 10g database can be imported into a 9i database. Do you use the 9i exp utility to permit this? Tom Kyte's books seem to indicate that this is the only way to import an export file created in a later release of Oracle.

I don't see CONSISTENT=Y in your export - isn't this required for a good export?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm "CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both."

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm "The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export. For direct path Exports, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file."

It could be that the notes that I posted above are out of date. Any clarification would be helpful.

To the OP:
See page 325 of "Expert One-on-One Oracle" regarding how to split an export file into multiple files. Parameters might be:   file = f1,f2,f3,f4,f5 filesize = 2000m

The above should create files f1.dmp, f2.dmp, f3.dmp, f4.dmp, f5.dmp, each of which is roughly 2GB in size. You would of course need to continue the file naming convention to reach the maximum export file size.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 07 2007 - 08:29:29 CDT