Prev: Tracing the given client id
Next: Please help to put 2 Linux/Oracle VMs into a H/A cluster
From: Bill on 2 Jul 2010 18:43
Hi there.

I'm using impdp to import a full database.

Everything seemed to go well, except that the sequences didn't seem to
match the old db.

There are numerous schema owners and lots of dependencies.

I was hoping to bring the database across in one go but I was
wondering if anyone can recommend the steps for this situation.

My old method with exp/imp was:

1. full exp.
2. imp rows=n and constraints=n (to build structure)
3. disable triggers
4. imp rows=y
5. imp indexes=y constraints=y

It worked but it took ages.

Any suggestions? I have looked at the Oracle 10g utilities
documentation but couldn't see anything specific to this.

Thanks, Ed.

From: Mark D Powell on 4 Jul 2010 10:48
On Jul 2, 6:43 pm, Bill <billshatne...(a)googlemail.com> wrote:
> Hi there.
>
> I'm using impdp to import a full database.
>
> Everything seemed to go well, except that the sequences didn't seem to
> match the old db.
>
> There are numerous schema owners and lots of dependencies.
>
> I was hoping to bring the database across in one go but I was
> wondering if anyone can recommend the steps for this situation.
>
> My old method with exp/imp was:
>
> 1. full exp.
> 2. imp rows=n and constraints=n (to build structure)
> 3. disable triggers
> 4. imp rows=y
> 5. imp indexes=y constraints=y
>
> It worked but it took ages.
>
> Any suggestions? I have looked at the Oracle 10g utilities
> documentation but couldn't see anything specific to this.
>
> Thanks, Ed.

Actually posting the sequence information for a specific sequence as
it appears in the old db and as it appears in the new db might allow
someone to provide more specific information. but If the sequences do
not exist in hte target db then the import task would create the
sequences in the target. The sequence values should more or less
'match' the source. I say more or less cause the values assigned will
skip the buffered values in the old database.

On the other hand if the sequences already exist in the target then
the import will not replace them and the values will reflect the
existing values rather than the values you need for the freshly
imported data. This can happen when you import a production extract
into test without removing all the target objects, sequences in this
case, from test first.

HTH -- Mark D Powell --

From: Mark D Powell on 4 Jul 2010 10:50
On Jul 4, 10:48 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Jul 2, 6:43 pm, Bill <billshatne...(a)googlemail.com> wrote:
>
>
>
>
>
> > Hi there.
>
> > I'm using impdp to import a full database.
>
> > Everything seemed to go well, except that the sequences didn't seem to
> > match the old db.
>
> > There are numerous schema owners and lots of dependencies.
>
> > I was hoping to bring the database across in one go but I was
> > wondering if anyone can recommend the steps for this situation.
>
> > My old method with exp/imp was:
>
> > 1. full exp.
> > 2. imp rows=n and constraints=n (to build structure)
> > 3. disable triggers
> > 4. imp rows=y
> > 5. imp indexes=y constraints=y
>
> > It worked but it took ages.
>
> > Any suggestions? I have looked at the Oracle 10g utilities
> > documentation but couldn't see anything specific to this.
>
> > Thanks, Ed.
>
> Actually posting the sequence information for a specific sequence as
> it appears in the old db and as it appears in the new db might allow
> someone to provide more specific information. but If the sequences do
> not exist in hte target db then the import task would create the
> sequences in the target.  The sequence values should more or less
> 'match' the source.  I say more or less cause the values assigned will
> skip the buffered values in the old database.
>
> On the other hand if the sequences already exist in the target then
> the import will not replace them and the values will reflect the
> existing values rather than the values you need for the freshly
> imported data.  This can happen when you import a production extract
> into test without removing all the target objects, sequences in this
> case, from test first.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

PS - If the objects do not exist just make the export file and perform
the import. Oracle will automatically handle the FK since it will
create the tables first, import the data, add the PK, build the
indexes, and bring the FK after the tables have been created.

-- Mark --
From: Bill on 4 Jul 2010 16:52
Thanks Mark, for replying.

What I did was used DBMS_METADATA to get all the sequence source.
Ran an import with triggers disabled and brought in the structure. No
constraints or indexes or stats.
Then ran the data in, then re-enabled triggers and brought in the
indexes and constraints.
Seemed to work fine, except the java classes are invalid.
That was all before I read your reply. Your method looks more
streamlined. I'll give it a go.

I think I'm right in saying that impdp deals with fk constraints
differently to import. Certainly, I couldn't figure out a single
import way that worked before for a full database or maybe I've just
been making it needlessly difficult.

Regards,
Ed.
 | 
Pages: 1
Prev: Tracing the given client id
Next: Please help to put 2 Linux/Oracle VMs into a H/A cluster