Re: Move 8i instances into 9R2 schemas

Marc Eggenberger wrote:

> Hi.
>
> I have the following situation:
>
> 3 instances of Oracle 8i (8.1.7.0) on Windows 2000 Server SP4 for 3
> applications. The schemas of the 3 instances are identical except for
> the data. They are used for an application for 3 different groups.
>
> Now I want to move them into one 9R2 (9.2.0.6 on Windows 2000 Advanced
> Server SP4) instance into different schemas.
>
> The database is very simple. Some tables, and some foreign constraints.
> Nothing else, No procedures, no java, no triggers.
>
> What I tried is create the new instance and all the schemas.
>
> It looks like this:
>
> Old_Instance1:
> schema1
> schema2
> schema3
>
> Old_Instance2:
> schema1
> schema2
> schema3
>
> Old_Instance3:
> schema1
> schema2
> schema3
>
>
> New_Instance:
> group1_schema1
> group1_schema2
> group1_schema3
> group2_schema1
> group2_schema2
> group2_schema3
> group3_schema1
> group3_schema2
> group3_schema3
>
> I then used exp on the old server to expert the schemas on Old_instance1
> and imp and the same server (I read that I always should use the lower
> version of two different instances when exp/imp) to the new instance.
>
> The data is ok but on the old instance user schema3 hat grants on
> schema1, same with schema2 on schema1.
>
> These grants failed with the import. I used the following cmdlines:
>
> exp sys/xxx_at_old_instace1 file=e:\export\old_instance1.dmp log=e:\export
> \old_instance1.log owner=(schema1, schema2, schema3)
>
> then I issued the following imp cmds:
>
> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:
> \export\imp.log fromuser=schema1 touser=group1_schema1
>
> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:
> \export\imp.log fromuser=schema2 touser=group1_schema2
>
> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:
> \export\imp.log fromuser=schema3 touser=group1_schema3
>
>
> but as I said the grants fail because imp tries grants to schema2 on the
> new instance but this does not exist.
>
> How can I solve this? Am I doing it completely wrong? What should I do
> instead?
>
>
> Thanks for any hints.

Do you have the luxury of fixing the design? I can't see any reason why you don't have one new schema. On its face this looks like a nightmare of a design.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---