Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: EXP Parameters to take backup of 110 GB database

Re: EXP Parameters to take backup of 110 GB database

From: joel garry <>
Date: Tue, 07 Aug 2007 12:01:41 -0700
Message-ID: <>

On Aug 7, 6:29 am, Charles Hooper <> wrote:
> On Aug 6, 2:29 am, wrote:
> > On Aug 6, 2:54 pm, Jitendra <> wrote:
> > > On Aug 3, 2:13 pm, Jitendra <> 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?

It's one way around VPD and label security, if you don't grant someone exempt access policy privilege. Mentioned in the Utilities manual. The things sys does don't need transactional integrity in general (though there are things that sys owns that might require it).

> 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?
> "CONSISTENT=y is unsupported for exports that are performed when you
> are connected as user SYS or you are using AS SYSDBA, or both."

Only if you are doing stuff in the db. (For my situation, I kill off user processes before the exp, unless arrangements are made not to. I'm not using sys though.) This is one of those YMMV things. For me, the killing has a side benefit of allowing a much smaller undo ts, which makes physical backups quicker. I'll probably be going 7/24 soon, so that will all change.

I think the current advice might be "Don't use sys unless you have some specific reason to. VPD isn't a good reason. Grant privileges to a special account if you need stuff sys has." If you import sys objects into another database, you better know what you are doing.


-- is bogus.
A good blog is never nearly good enough, but a lousy blog is a joy
Apologies and a Happy Birthday to Garrison Keillor
Received on Tue Aug 07 2007 - 14:01:41 CDT

Original text of this message