Re: EXP Parameters to take backup of 110 GB database

On Aug 3, 1:14 pm, sybra..._at_hccnet.nl wrote:
> On Fri, 03 Aug 2007 02:13:02 -0700, 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
>
> I would exercise my TSPITR procedures. Exporting a 110 Gb database is
> just ridiculous. You are not going to IMP it ever, do you?
> How would you plan to deal with the redolog.
> TSPITR doesn't generate redolog.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I do 50G (times more than one) all the time, not even the latest or greatest hardware. I think it is perfectly reasonable for what I'm using it for. Perfectly doable in reasonable timeframes. exp takes 40 minutes, RMAN takes 4 hours. imp takes 4 hours, I've only done RMAN recovery on slower hardware recently, but IIRC it was about twice the save last time I tried save/restore on the same hardware, long ago. As far as redo - let's just say if you are not going to recover, you don't need to archive. For major system-down upgrades that do things like expand and transform columns in the largest tables, it often makes sense to turn off archivelog mode and do whatever, then turn it back on at the end and then RMAN backup. Backing up stages within such an upgrade don't need transactional recovery, you just restore and start the stage over again.

The determinant is, are there many things in the tablespace that don't actually need to be recovered? When a lot of the stuff is ETL or test schema reloads or strange vendor-specific things, no, you don't need to recover transactions. Logical backups are also very handy for getting certain (not necessarily small) amounts of data from the past.

Could my tablespaces be laid out differently to decrease recovery time for specific recoveries? Sure. But, no need. Just to "use RMAN" is not a legitimate requirement. Restoring specific tables is a legitimate requirement. Restoring a schema is a legitimate requirement. TSPITR seems only to be done to show that it could be done, the granularity of a recovery doesn't match anything until you know what you need, and there are cross-app references.

I mentioned Connor McDonald's list of ways to speed up exp/imp in another thread, for my particular requirements I: Use schema level exports with DIRECT=Y compress=N recordlength=65535 Use exp with buffer=20000000 full=y rows=n compress=n Sometimes use the QUERY parameter if appropriate. Sometimes use transportable tablespaces if appropriate. Sometimes use user mode imp with ignore=y commit=y buffer=20000000 statistics=recalculate if appropriate.

YMMV. jg

--
@home.com is bogus.
http://www.dba-oracle.com/images/cruise_menu.jpg