IMPORT - how many passes?

[Database Administrator]

I'm trying to import all the schemas* from our production database into
a new instance (which will eventualy beome the new production database
once I get it all to work).

On the new installation I create the database, create the tablespaces,
create the users/schemas giving them quotas on the tablespaces they
need. So far so good.

Next I want to run import - how many passes is typical for this? I
understand I can't do it in one pass, beause I'll be trying to grant
rights to objects that don't exist yet, and apply foreign keys to tables
where the parent data may not exist yet.

It seems like I need three passes, but I keep reading that a two-pass
import is the usual way to go. Here's what I think I need:

1) rows=n, constraints=n, grants=n, indexes=n
2) rows=y, constraints=n, grants=y, indexes=n, ignore=y
3) rows=n, constraints=y, show=y, grants=n, indexes=n, ignore=y

Step three should build a text file that can be used to create the
constraints and indexes. Is this the way to do it, or am I barking up
the wrong tree?

Or alternatively, anybody got a standard set of parfiles for doing this
sort of thing?


details:
Current production Oracle 8.1.6 on winNT
New database Oracle 9.2 on Win2k
The export file is from a full export, size just under 2 Gig.


--
//-Walt
//
//


*all the schemas == all our application schemas
- not system, mdsys, ctxsys, etc.

[Database Administrator]

Why not do export with FULL=Y and import with FULL=Y? This is only one
pass and gets everything.

HTH,
Brian

Walt wrote:
>
> I'm trying to import all the schemas* from our production database into
> a new instance (which will eventualy beome the new production database
> once I get it all to work).
>
> On the new installation I create the database, create the tablespaces,
> create the users/schemas giving them quotas on the tablespaces they
> need. So far so good.
>
> Next I want to run import - how many passes is typical for this? I
> understand I can't do it in one pass, beause I'll be trying to grant
> rights to objects that don't exist yet, and apply foreign keys to tables
> where the parent data may not exist yet.
>
> It seems like I need three passes, but I keep reading that a two-pass
> import is the usual way to go. Here's what I think I need:
>
> 1) rows=n, constraints=n, grants=n, indexes=n
> 2) rows=y, constraints=n, grants=y, indexes=n, ignore=y
> 3) rows=n, constraints=y, show=y, grants=n, indexes=n, ignore=y
>
> Step three should build a text file that can be used to create the
> constraints and indexes. Is this the way to do it, or am I barking up
> the wrong tree?
>
> Or alternatively, anybody got a standard set of parfiles for doing this
> sort of thing?
>
> details:
> Current production Oracle 8.1.6 on winNT
> New database Oracle 9.2 on Win2k
> The export file is from a full export, size just under 2 Gig.
>
> --
> //-Walt
> //
> //
>
> *all the schemas == all our application schemas
> - not system, mdsys, ctxsys, etc.

--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Database Administrator]

Why not do export with FULL=Y and import with FULL=Y? This is only one
pass and gets everything.

HTH,
Brian

Walt wrote:
>
> I'm trying to import all the schemas* from our production database into
> a new instance (which will eventualy beome the new production database
> once I get it all to work).
>
> On the new installation I create the database, create the tablespaces,
> create the users/schemas giving them quotas on the tablespaces they
> need. So far so good.
>
> Next I want to run import - how many passes is typical for this? I
> understand I can't do it in one pass, beause I'll be trying to grant
> rights to objects that don't exist yet, and apply foreign keys to tables
> where the parent data may not exist yet.
>
> It seems like I need three passes, but I keep reading that a two-pass
> import is the usual way to go. Here's what I think I need:
>
> 1) rows=n, constraints=n, grants=n, indexes=n
> 2) rows=y, constraints=n, grants=y, indexes=n, ignore=y
> 3) rows=n, constraints=y, show=y, grants=n, indexes=n, ignore=y
>
> Step three should build a text file that can be used to create the
> constraints and indexes. Is this the way to do it, or am I barking up
> the wrong tree?
>
> Or alternatively, anybody got a standard set of parfiles for doing this
> sort of thing?
>
> details:
> Current production Oracle 8.1.6 on winNT
> New database Oracle 9.2 on Win2k
> The export file is from a full export, size just under 2 Gig.
>
> --
> //-Walt
> //
> //
>
> *all the schemas == all our application schemas
> - not system, mdsys, ctxsys, etc.

--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Database Administrator]

Brian Peasland wrote:
>
> Why not do export with FULL=Y and import with FULL=Y? This is only one
> pass and gets everything.

Several reasons, the principal one being that it won't work.


--
//-Walt
//
//

[Database Administrator]

Brian Peasland wrote:
>
> Why not do export with FULL=Y and import with FULL=Y? This is only one
> pass and gets everything.

Several reasons, the principal one being that it won't work.


--
//-Walt
//
//

[Database Administrator]

> > Why not do export with FULL=Y and import with FULL=Y? This is only one
> > pass and gets everything.
>
> Several reasons, the principal one being that it won't work.

Can you elaborate *why* it won't work? I've done many, many refreshes of
a development or test database with production data and doing a FULL
export and a FULL import has always worked for me. Why won't it work for
you?

Cheers,
Brian

--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Database Administrator]

> > Why not do export with FULL=Y and import with FULL=Y? This is only one
> > pass and gets everything.
>
> Several reasons, the principal one being that it won't work.

Can you elaborate *why* it won't work? I've done many, many refreshes of
a development or test database with production data and doing a FULL
export and a FULL import has always worked for me. Why won't it work for
you?

Cheers,
Brian

--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

[Database Administrator]

Brian Peasland wrote:
>
> > > Why not do export with FULL=Y and import with FULL=Y? This is only one
> > > pass and gets everything.
> >
> > Several reasons, the principal one being that it won't work.
>
> Can you elaborate *why* it won't work? I've done many, many refreshes of
> a development or test database with production data and doing a FULL
> export and a FULL import has always worked for me. Why won't it work for
> you?


Well, for one, I'm moving the data from 8.1.6 to 9.2. Wouldn't a full
import overwrite the data in the system and ctxsys tables (and some
others)?

Also, wouldn't I get a boatload of errors as foreign keys fail to find
their parent records, grant statements fail to find the referenced
objects, views fail to find their underlying tables, etc.

I can see a full import working if it's done just the right order, but I
don't see how to guarantee it.

In any case, I did try a full import at first, but everytime I tried it
the import process would hang. Others here suggested importing just the
application data, so that's the path that I'm on now. If there's an
easy way to just import the whole thing at once I'd be more than happy
to use it.

--
//-Walt
//
//

[Database Administrator]

Brian Peasland wrote:
>
> > > Why not do export with FULL=Y and import with FULL=Y? This is only one
> > > pass and gets everything.
> >
> > Several reasons, the principal one being that it won't work.
>
> Can you elaborate *why* it won't work? I've done many, many refreshes of
> a development or test database with production data and doing a FULL
> export and a FULL import has always worked for me. Why won't it work for
> you?


Well, for one, I'm moving the data from 8.1.6 to 9.2. Wouldn't a full
import overwrite the data in the system and ctxsys tables (and some
others)?

Also, wouldn't I get a boatload of errors as foreign keys fail to find
their parent records, grant statements fail to find the referenced
objects, views fail to find their underlying tables, etc.

I can see a full import working if it's done just the right order, but I
don't see how to guarantee it.

In any case, I did try a full import at first, but everytime I tried it
the import process would hang. Others here suggested importing just the
application data, so that's the path that I'm on now. If there's an
easy way to just import the whole thing at once I'd be more than happy
to use it.

--
//-Walt
//
//

[Database Administrator]

> Well, for one, I'm moving the data from 8.1.6 to 9.2. Wouldn't a full
> import overwrite the data in the system and ctxsys tables (and some
> others)?

Hopefully, you don't have anything but the bare minimum owned by SYSTEM.
As for CTXSYS, if you are using ConText, then don't you want that
updated as well once you pull over all of your ConText stuff?

> Also, wouldn't I get a boatload of errors as foreign keys fail to find
> their parent records, grant statements fail to find the referenced
> objects, views fail to find their underlying tables, etc.

This is only true if you leave the development objects there. But you
are refreshing everything from production right? So why not drop all of
the development objects and then import.

> I can see a full import working if it's done just the right order, but I
> don't see how to guarantee it.

The import process guarantees this. It doesn't enable any constraints
until after everything has been imported. Views don't get created until
the end. So the tables are there when it gets time for views to be
created. Want to see exactly the steps that import will take? Run import
with SHOW=Y and LOG=some_log_file. The contents of some_log_file will
show the DDL statements, in the order that import will take.

The only time you should have a problem with this is if you leave tables
and constraints there. But why not drop the tables? You don't want the
data that is in there anyway because you will be refreshing it from
production via your dump file. In fact, you can even do the DROP USER
command because the import file contains the DDL to create all the
users/grants/etc. When I do refreshes from production I do exactly this.
I don't want a developer leaving data that they have "logically
corrupted" in the natural course of their development. The whole point
of refreshing from production is to get to a point that has no
corruptions like this so that you can do development on it without
affecting production.

HTH,
Brian


--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"