Showing results for 
Search instead for 
Did you mean: 

Oracle Clone and drop tablespace in basically one move?

Go to solution
Honored Contributor

Oracle Clone and drop tablespace in basically one move?

Hey all, I've not done it - but I can't see why not....

Can I clone a database to another server, and leave behind a few tablespaces?

That is, when I make my new control file on the new server - and then make my controlfile - and files are missing...
Can I somehow get that controfile created, with the missing files with the DB just mounted (only), then tell the DB to drop the missing tablespaces, and then have the DG come up "lighter" sans the missing tablespaces.

What I'd like to be able to do - is clone to a test server and just not include certain tablespaces I don't/won't need on certain test servers. I would like to use this approach for leaving out some historical archive data.
AND, I don't want to have bring up my cloned systems with all files (tablespaces) and then drop them from the second server. This is because I still have to allocate the space to bring up the DB, just to drop those areas after I bring up the test instance.

Is this doable?
We are the people our parents warned us about --Jimmy Buffett
Eric Antunes
Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?

Hi TwoProc,

I don't think you can because after you ran the "alter database backup controlfile to trace", in the *.trc file you get and use to clone the destination database, there are only references to DATAFILES, NOT TABLESPACES:

GROUP 1 '/disc2/oradata//log1.dbf' SIZE 50M,
GROUP 2 '/disc2/oradata//log2.dbf' SIZE 50M,
GROUP 3 '/disc2/oradata//log3.dbf' SIZE 50M

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Patti Johnson
Respected Contributor

Re: Oracle Clone and drop tablespace in basically one move?

Sure it can be done. This is the method that can be used to recover just one table from a complete backup. (TSPITR - tablespace point in time recovery). Restore the system, temp, undo and any tablespaces that you require from your backup.
Then remove the files not being restored from your script before you create the new controlfile - then offline drop the datafiles (from mount mode) and then drop tablespaces and contents after open.


Volker Borowski
Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?


yes it is doable :-)

But the approach ist slightly diffrent and works only, if ALL missing files belong to a tablespace that is to be dropped afterwards. Otherwise there might be some relicts that catch you afterwards.

Here is an old problem of mine which I finaly solved in a related way:

Check out the word-attachment.

Only proceed if you fully understand what I have described there and NEVER try this in a production environment, but only with a clone-copy to play around with.

To sum it up the steps are:

- Copy/Restore everything you need plus system and undo/rollback tablespaces.

- Create complete (!) Controlfiles including all filenames from the source.

- Mount the target db.

- Set the "missing" files offline.
alter database datafile '.....' offline;

- Recover the database as needed if restored from online Backup (might be more tricky with rman with offline datafiles, never tried this way).

- OFFLINE DROP the "MISSING" datafiles.
alter database datafile '.....' offline drop;

- Open (resetlogs) the database.

- Force-Drop the tablespaces that are related to the missing datafiles including contents.

- Re-Create TEMP as needed

Test this procedure very carefully in non-critical environment.

Good luck
Volker Borowski
Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?

Just rechecked:

It might be possible, that you can not create a controlfile with all files named, when some files are missing, esp. if you like to rename the SID. If the SID is the same (as in my "bad" scenario), you can restore binary controlfiles and procede as described.

If you like to change the SID, you need to CREATE the controlfile with "SET SID" and that might not work with files missing. Exclude these files from the CREATE CONTROLFILE statement in this case. In this case you will get generated entrys in the controlfile AFTER the database has been opened. Those are named
or likewise.
"Offline drop" these files as described and the related tablespace after OPEN in this case.


Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?

Thanks all VERY much for your help.

The only complexity I've added that I didn't tell you (and really should have).

Is that I *do* change the database name, and therefore I *do* set the sid by creating a new controlfile on the way up. Which means that I BEGIN by restoring all of my files to a new location(except temp) - fixing my create controlfile script, and then create a new control file right then. So - I'm hoping to get this done, without having to create a production named instance first, then dropping what I don't want and then cloning from the new database. I'm trying to "one-step" this process, and that's why I'm asking. I've done this on an in-place database, but not through a clone to a different instance name.
Why a different instance name? I made it a policy long ago not to have test and development databases with production names hanging about, even though it greatly simplifies cloning. I'm convinced that the day I start doing this, I'll start having developers deleting from production that they believe is a test database, or running production things in test that shouldn't be , like a whole accrual in place, and then a week later accounting wants to know why their accruals seem to be missing. Heck, things like this have happened already (though rarely), and I don't have ANY resources named the same!

So, I know I could two-step through this - I just want to know if I can handle this whole thing in a single swipe, saving my team lots of time and disk space.

Thanks all for kindly giving me your responses, as always it is very much appreciated!
We are the people our parents warned us about --Jimmy Buffett
Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?

hi john,

I have a similar environment but do a "manual standby" using RMAN of my production database.

alter database rename global_name to;

In this way, i have a copy of the production database and apply logs regularly to the "standby". (RMAN: restore force archivelog from logseq followed by SQL: recover automatic from '' database using backup controlfile;)

With about 5GB of redologs generated for a specified interval, the aboive process takes only about 10 to 20 minutes (i know it is relative to the machine in the backend..)

In case, i have recover from any user error, i just have to open the database and get the desired information.

With Oracle 10g release2, this becomes more easier with the possibility to have standby databases which we can switch forward and backward with more facility.

I do not know how critical, your system is to your business but the more simple you keep the system, the more easy and fast it is recover in cases of failures...

You can also look into "Schema Level Streams Replication"

Otherwise, you can always use exp/imp to exclude tables with historical archive data...

all the best!

kind regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Honored Contributor

Re: Oracle Clone and drop tablespace in basically one move?

Hey Yogeeraj,

It's always great to get your sound advice (as Eric, Volker, and Patti) - you guys keep this forum going!

It's not for a standby database, it's just for cloning to a test area for people to develop on (destroy :-). I'm going to have stuff I don't need for certain types of development instances, and I'm just wanting to try my hand at leaving stuff behind when cloning. It would save space on the destination server and time cloning by just not worrying about copying a bunch of custom and archive data for a software team that fools only with say Fixed Asset modeling and reporting. In this example - right off, I know that custom quality team data and archives would never be needed, yet the data is extensive.

We are the people our parents warned us about --Jimmy Buffett