Thanks Anil but my problem is that how do I change the
names of the production schemas on the newly cloned
test database to the test schema names???????

After cloning, on the test database, this is what I
want: Rename proddta to testdta and prodctl to
testctl.

Ashish
--- "Dongare, Anil" <anil.[Email Address Removed]
> Perfect plan. But u don't need step 7. i.e. Recover
> DB by applying logfiles.
> B'coz u r shutting down production DB and then
> copying the datafiles. just
> like cold backup.
>
> -anil
>
> > -----Original Message-----
> > From: Ashish Saxena [SMTP:[Email Address Removed] > Sent: Thursday, October 31, 2002 4:29 PM
> > To: LazyDBA.com Discussion
> > 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 LazyDBA home page