Prev: Senior Cognos Consultant Detroit, MI
Next: Hyperion Financial Management (HFM) , Jersey city, NJ
From: gs on 5 May 2010 10:59
I'm moving several 9iR2 databases onto new hardware with 11GR2 64bit.

One of them has only a couple of users, roles etc. so I pre-create all
the tablespaces, users, etc. then import with 11G imp utility using
fromuser=user1,user2 etc. in the .imp parfile.

Another has an application front end that creates a database user for
all the application users, and assigns certain roles etc. So I have
dozens of users on this database, along with various roles as well. I'm
wondering what the best way to approach this one is, so I'm thinking:

Do I do a import full=y, and let the import create all the users roles
etc. ? *I already pre-created all the application tablespaces when I
created the empty database with DBCA

Do I generate the ddl for all the users and roles, run it on the new db
first, then import with fromuser=user1,user2..? (doable, but is quite
time consuming in this case)

Do I import full=y for trial run, then when ready to import final
production data, drop users that have data with cascade option, recreate
these users and import their schemas only?

Keep in mind that I am doing several "rehearsals" on this, importing
from full exp files on the current production database, for users to
test the app against and also to debug my import commands,scripts etc.
so this will be a one step(batch) process when I am ready to do the
final import.

I'm asking all of this because I'm wondering if its just ok to do full=y
when upgrading, (I usually don't)

I'm assuming all the system etc. tables, views etc. in the 11G are just
left alone when ignore=y parameter is used and only other user data is
imported.
From: joel garry on 5 May 2010 13:25
On May 5, 7:59 am, gs <g...(a)gs.com> wrote:
> I'm moving several 9iR2 databases onto new hardware with 11GR2 64bit.
>
> One of them has only a couple of users, roles etc. so I pre-create all
> the tablespaces, users, etc. then import with 11G imp utility using
> fromuser=user1,user2 etc. in the .imp parfile.
>
> Another has an application front end that creates a database user for
> all the application users, and assigns certain roles etc. So I have
> dozens of users on this database, along with various roles as well. I'm
> wondering what the best way to approach this one is, so I'm thinking:
>
> Do I do a import full=y, and let the import create all the users roles
> etc. ? *I already pre-created all the application tablespaces when I
> created the empty database with DBCA
>
> Do I generate the ddl for all the users and roles, run it on the new db
> first, then import with fromuser=user1,user2..? (doable, but is quite
> time consuming in this case)
>
> Do I import full=y for trial run, then when ready to import final
> production data, drop users that have data with cascade option, recreate
> these users and import their schemas only?
>
> Keep in mind that I am doing several "rehearsals" on this, importing
> from full exp files on the current production database, for users to
> test the app against and also to debug my import commands,scripts etc.
> so this will be a one step(batch) process when I am ready to do the
> final import.
>
> I'm asking all of this because I'm wondering if its just ok to do full=y
> when upgrading, (I usually don't)
>
> I'm assuming all the system etc. tables, views etc. in the 11G are just
> left alone when ignore=y parameter is used and only other user data is
> imported.

Do the roles. Think about what has changed between versions, and
consider if the application does things right. In my experience, they
don't. At least CYA from management about letting the app do it, if
that's what they want. That's one of those things where there is a
variance between the business and technical viewpoints.

Haven't tried 11G myself.

jg
--
@home.com is bogus.
http://dilbert.com/dyn/tiny/File/Lost%20Phone%201%20.jpg
From: Sybrand Bakker on 6 May 2010 02:11
On Wed, 05 May 2010 14:59:06 GMT, gs <gs(a)gs.com> wrote:

>I'm assuming all the system etc. tables, views etc. in the 11G are just
>left alone when ignore=y parameter is used and only other user data is
>imported.

ignore=y just means a when a CREATE TABLE statement fails because the
table already exists, the associated INSERT statements are executed.

SYSTEM is treated as an ordinary user by imp.

SYS is never exported nor imported.

-------
Sybrand Bakker
Senior Oracle DBA
From: gs on 6 May 2010 13:22
Sybrand Bakker wrote:
> On Wed, 05 May 2010 14:59:06 GMT, gs <gs(a)gs.com> wrote:
>
>> I'm assuming all the system etc. tables, views etc. in the 11G are just
>> left alone when ignore=y parameter is used and only other user data is
>> imported.
>
> ignore=y just means a when a CREATE TABLE statement fails because the
> table already exists, the associated INSERT statements are executed.
>
> SYSTEM is treated as an ordinary user by imp.
>
> SYS is never exported nor imported.
>
> -------
> Sybrand Bakker
> Senior Oracle DBA

since this is still in "rehearsal mode", I tried importing full export
file in using 11G "import from export files" GUI option, which basically
looks like an imp front end. Anyway, full imp just failed, at first I
tried having it read the dmp file so I could pick what I wanted to
import (I wanted to see what it looked like, more than anything) but
after that churned away for over two hours reading the export file with
no results I killed it and just tried the full import, which failed.

So, I did it again picking the schemas I wanted, all went ok save for
some invalid views that would not compile. The reason they would not
compile is that the public synonyms for one of the user schemas did not
import for some reason, a quick create script taken from the production
database fixed this, but I thought they would have come across with the
import.

I also noticed that all the users accounts that have no tables were
created, even though I did not specify them. I thought I would have to
create them all manually (via create script) and I'm wondering if they
were brought over before the full import hit whatever it did when it
failed.
 | 
Pages: 1
Prev: Senior Cognos Consultant Detroit, MI
Next: Hyperion Financial Management (HFM) , Jersey city, NJ