Re: Export Import Large Database - 9i

On Tue, Jan 26, 2010 at 8:24 PM, Mudhalvan Moovarkku < moovarkku.mudhalvan_at_axa-direct.co.jp> wrote:

> Tim/Wannabe,
>
>
>
> Whatever Tim said is perfect.
>
>
>
> I would like to add one more tip between 1 and 2.
>
>
>
> 1. Before Start importing on the Target Database Disable Logging at
> All segment level (Table, Table space)
>
>
>
> Don’t forget to enable the Logging once after you complete the import. It
> is good to do it with the scripts.
>
>
>
>
>
> Regards
>
> Mudhalvan M.M
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Tim Gorman
> *Sent:* Sunday, January 17, 2010 1:32 AM
> *To:* oracledbawannabe_at_yahoo.com
> *Cc:* Oracle-L_at_freelists.org
> *Subject:* Re: Export Import Large Database - 9i
>
>
>
> Wannabe,
>
> It gets pretty involved, but the basic algorithm for exporting/importing a
> large database is:
>
> 1. EXP FULL=Y ROWS=N from the source-db then IMP FULL=Y ROWS=N
> INDEXES=N CONSTRAINTS=N into the target-db
> - The idea is to lay down the tablespaces and table definitions in
> the target-db. Depending on similarities (or the lack thereof) between the
> two dbs, you may or may not be able to create tablespaces using IMP, but the
> goal is to import tables DDL and the DDL for other objects (but not indexes
> and constraints) using IMP
> - Don't forget to disable all DML triggers created in target-db
> before going on to the next step...
> 2. Run as many concurrent exp/imp streams as you can between the
> source-db and target-db to copy the table data across
> - Goal is to keep as many streams running concurrently as possible.
> For smaller tables, one exp/imp stream per table. For larger tables, one
> exp/imp stream per partition or one exp/imp stream per section of the table
> specified using EXP QUERY=
> 3. Create indexes and constraints using previously-obtained EXP FULL=Y
> ROWS=N in step #1
> - Usually I just extract the DDL using IMP INDEXFILES= and create
> SQL*Plus scripts for this, so I can break the task up into many concurrently
> running jobs for speed...
>
> That is just the general idea; as you can imagine, there are a lot of
> details - some specific to your environment, some generic to the task.
> You're going to need to do a lot of UNIX shell-scripting, as well as the
> techniques of SQL-generating-SQL, not to mention some dynamite editing
> skills. If you don't have those skills, find them or learn them.
>
> I've attached a UNIX shell script I first wrote back in the 1990s for doing
> this kind of thing. It is called "expimp.sh" and it is intended to perform
> a single stream of EXP->IMP (i.e. an entire schema, or a table, or a
> partition) by means of a UNIX FIFO or "pipe" in between. Doing this with a
> "pipe" is usually orders of magnitude faster than performing
> EXP->dmpfile->copy-file->IMP. No warranties implied or offered - caveat
> emptor...
>
> Hope this helps!
>
> Tim Gorman
>
> consultant -> Evergreen Database Technologies, Inc.
>
> postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
>
> website => http://www.EvDBT.com/
>
> email => Tim_at_EvDBT.com
>
> mobile => +1-303-885-4526
>
> fax => +1-303-484-3608
>
> Lost Data? => http://www.ora600.be for DUDE
>
>
>
> "Technical skill is mastery of complexity, while creativity is mastery of simplicity" - E. Christoper Zeeman, 1977
>
>
>
> Oracle Dba Wannabe wrote:
>
> Hi,
>
> Was wondering if anyone could share their experience of having to export
> import VLDB's in a 9i environment. For certains reason the database that I
> need to migrate cannot be upgraded - which would open up a lot more options.
> I'm hoping people out there could help with their personal experiences, if
> any, with respect to what they found helped the best during this process.
> I'd appreciate any input, thanks
>
> Thanks
>
>
>

-- 

Thanks & Regards,
Taral Desai
Stephen Leacock<http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html>
- "I detest life-insurance agents: they always argue that I shall some
day
die, which is not so."

--
http://www.freelists.org/webpage/oracle-l