Part Copy of Production data to Development Environment
Hi,
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!!
