Oracle 11g: Part Copy of Production data to Development Environment

oracle

I have a Production database with the current size involving two principale schemas:

SQL> SELECT SUM(bytes)/1024/1024/1025 "Size in GB" FROM dba_segments where owner in ('SCHEMA1','SCHEMA2');

Size in GB
----------
201.927804

Out of these the mentioned tables and their indexes are taking up following space (175.326 GB):

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12');

Size in GB
----------
72.0917968

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  (
  3  select index_name
  4  from dba_indexes
  5  where owner IN ('SCHEMA1','SCHEMA2')
  6  and table_name in ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12'));

Size in GB
----------
103.234375

SQL> select (72.0917968+103.234375) from dual;

(72.0917968+103.234375)
-----------------------
            175.3261718

Thus these 12 tables along with the indexes takes about 88% of the total space in the 2 schemas.

All 12 tables mentioned are subpartitioned based on a partition and subpartition key.

We have 7 development databases which needs to be refreshed on demand with the production database.

Currently, the refresh is done using RMAN, but going forward as the production database size increases we are anticipating space problems on development databases.

The Solution which I tried:

The 12 tables are partitioned in such a way that the data in one partition is functionally independent of other partition.

I created new tablespaces and moved partitions to these new tablespaces based on their business functionality.
Thus a single table had some partitions on one tablespace and rest of the partitions on the other tablespace.

The idea was to transport the necessary tablespces (and not the entire database) across production to development database.

Later on my DBA told me that for a tablespace to be transportable across databases they should be "self-contained".

And one of the violations for self contained tablespace according to oracle documentation is:

"A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables."

Thus my design failed.

Kindly note that the 12 big tables which I mentioned above contains data which is actually not needed for my unit testing on development database.
Is it possible to perform RMAN copy without including the data from these 12 tables?

Or could you think of any other solution so that I can do "part" refresh of production database to developement.

Let me know if you need any more information?

Database: Oracle 11g – 11.2.0.2.0

Thanks a lot in advance!!

Best Answer

Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup) After the clone, the skipped tablespaces have datafiles with status RECOVER.

see RMAN DUPLICATE DATABASE: Options

In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.

It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.