Copy From Live into Test - Keeping Test Users

[philthee]

Hi There, i want to take a copy of my Production database into my Test database on Sun Solaris 5.10 Oracle 9.2.0.8
There are hundreds of users in LIVE but only a few in TEST. I don't want the LIVE users to exist in the TEST DATABASE.

The main schema owner in LIVE is called CFLIVE whereas the main schema owner in TEST is called CFTEST.

If i get a restore of a cold backup of LIVE database onto TEST server and start that up as TEST i have all the live users and all the tables owned by ZZLIVE.

Can i export all the TEST users before i overwrite TEST and then drop LIVE users, import old TEST users/roles?
How do i change ZZLIVE tables etc to be owned by ZZTEST? Do i copy all ZZLIVE stuff into ZZTEST schema then drop ZZLIVE?
How would i change all synonyms that point to ZZLIVE?
Is there anything else i should be aware of?

Any advice greatly appreciated.

Thanks
Phil

[PAVB]

How big is your database?

[gandolf989]

You can't rename a user, but you can create synonyms between users. So you could restore a backup, renaming the database in the process, then run a script that will create the test users, with the right synonyms and grants, and let test users log into those accounts. You might also be able to use the database flashback and the logs from the previous day to restore the test database to what it was before you started testing and then apply all of the new archive logs from prod, and rerun the above script to set it up for test again.

[Mr.Hanky]

Fromuser=zzlive
Touser=zztest

3guysfromtx=zztop

[gandolf989]

Fromuser=zzlive
Touser=zztest

3guysfromtx=zztop

Depending on the size of the database, an export might take significantly longer than just restoring a database and running a script. So why would an import be faster, better, easier to manage?

[Mr.Hanky]

Depending on the size of the database, an export might take significantly longer than just restoring a database and running a script. So why would an import be faster, better, easier to manage?

Am i misreading the original post?
There is no mention of size or speed, he asks how to move objects from one user to another.
My read on this is that it would be easier to export/import one schema rather then the whole mess and do a cold backup/restore, he said he did not want all the users no?


In any case taking an export of TEST prior to this is a great idea, backups are always a good thing.

[philthee]

Thanks for the replies.

The database is ~22GB

I like the simplicity of Mr Hankys export/import. If the only difference is the ZZLIVE ZZTEST schema then thats all i need to do. All the users will remain the same and i don't need to worry about dropping live users and recreating test users with all the same passwords etc.
I will find out if the only difference will be within these schemas to be sure. Thanks all

[PAVB]

The database is ~22GB

I agree. In a database of that size, having to move just one schema I would go with the exp/imp strategy.

Something like...
1- export cflive schema on production
2- script cftest account on test
3- drop user cftest on test
4- create cftest user on test
5- imp fromuser=cflive touser=cftest on test
...would do it.

[Mr.Hanky]

Now that I think about it you will need all the tablespaces created in your test environment or the FROM---TO will bomb.
This can be a nightmare depending on how many you have to create.
I recently used the REMAP_TABLESPACE option in datapump and that worked fantastic.

[PAVB]

Now that I think about it you will need all the tablespaces created in your test environment or the FROM---TO will bomb.

...or, you can pre-create objects in whatever tablespace you want and include ignore=yes option in import