THanks... So u saying my plan(cloning & changing db
name) can't work??? But I have never used transport
tablespace option before. Can u tell me how to do
that? And will it solve my problem??? I'm now going to
look & search at X-port tbs option myself.
--- "Ramamurthi, Radha" <[Email Address Removed] 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] To subscribe: send a blank email to
> oracledba-[Email Address Removed] Visit 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
=====
Ashish Saxena
Oracle DBA
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
Oracle LazyDBA home page