Hi Ashish:
Have you looked at the transport tablespace option? This will allow you to
copy the data physically through the files but you will be importing the
meta data and so you get the option to do a fromuser/touser type import so
you can transfer ownership of objects.
Radha
-----Original Message-----
From: Ashish Saxena
To: LazyDBA.com Discussion
Sent: 10/31/02 9:28 AM
Subject: How to rename a schema without dropping it?
Dear fellow DBAs,
I have a monthly export import process that refreshes
a test database(no archive mode) with production
database(archive log mode). Production database size
is 45GB & data is 32GB. So really 32GB of data gets
imported to the test database(on same box)There are
only 2 application schemas that are refreshed as they
constitute 99% of data.
My issue is that inspite of fine tuning the import
process (data 1st,nologging in index creation,noarch
mode and all that good stuff), it takes around 36
hours to import 32 GB data & that comsumes my whole
weekend. I'm striving to reduce this time.
Following is my plan. Its a cloning cum db name change
process by re-creating controlfile. Please advise &
correct me if this is not possible.
1. On prod db, bkp controlfile to trace.
2. Shutdown production database and test database &
delete all control,redo & datafiles of test database.
3. Copy all prod datafiles to where I want the test db
files to reside. Startup the production database for
normal use.
4. Edit the controlfile bkp taken in step1 with the
redolog file, datafile locations specified in step3.
Also change the database name in the bkp ctl
file(create controlfile set database "test")
5. Change oracle sid to "test".
6. With startup nomount mode, run the edited
controlfile created in step 4.
7. Recover the database by applying online redologs(if
required)
8. Open the dtaabase with resetlogs.
This clones the production database to test database &
will take less than 2 hours.
But my problem is that the prod schemas
prodctl,proddta) retain their names in the test
database & cusotmer doesn't want that. AFTER CLONING
PROD DATABASE & CHANGING DB NAME, I WANT TO CHANGE THE
PROD SCHEMAS IN TEST DATABASE FROM prodctl,proddta to
testctl,testdta. Is there a way to do this????
Please advise if someone has done this before or has a
better plan to reduce the data refresh times.
Thanks in advance!
Ashish
=====
Ashish Saxena
Oracle DBA
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page