This discussion is archived
5 Replies Latest reply: Oct 8, 2012 11:28 AM by jgarry RSS

Part Copy of Production data to Development Environment

SahirHadkar Newbie
Currently Being Moderated
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 development.

Let me know if you need any more information?

Database: 11g - 11.2.0.2.0

Thanks a lot in advance!!
  • 1. Re: Part Copy of Production data to Development Environment
    John Stegeman Oracle ACE
    Currently Being Moderated
    You can use partition exchange.

    Exchange the partition you want to transport with an empty one (temporarily) - it now is a standalone table.

    Transport

    Exchange the partition back

    On the dev database, exchange the table in the transported tablespace with an empty partition on the table.

    edit: after fully reading your question - I see that the documentation told you to do just that :)
  • 2. Re: Part Copy of Production data to Development Environment
    SahirHadkar Newbie
    Currently Being Moderated
    Thanks a lot for the prompt reply.

    I had thought of exchange partition technique.

    For this method I have to perform exchange partition on Production database every time I want to transport the data.
    I really do not want to touch or tamper the production data every time I refresh Dev environment

    Also, on development environment, I need to rebuild the indexes again for the partitions which I have exchanged which may take time.

    I would be grateful if you come up with some alternate solution.

    Thanks a lot!
  • 3. Re: Part Copy of Production data to Development Environment
    John Stegeman Oracle ACE
    Currently Being Moderated
    expdp can export a single partition (TABLES=schema.table:partition
  • 4. Re: Part Copy of Production data to Development Environment
    Marco V. Expert
    Currently Being Moderated
    Have a look at expdp SAMPLE option
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL863
    *Purpose*
    Allows you to specify a percentage of the data rows to be sampled and unloaded from
    the source database.
    *Syntax and Description*
    SAMPLE=[[schema_name.]table_name:]sample_percent
    This parameter allows you to export subsets of data by specifying the percentage of
    data to be sampled and exported. The sample_percent indicates the probability that
    a row will be selected as part of the sample. It does not mean that the database will
    retrieve exactly that amount of rows from the table. The value you supply for
    sample_percent can be anywhere from .000001 up to, but not including, 100.
    The sample_percent can be applied to specific tables. In the following example,
    50% of the HR.EMPLOYEES table will be exported:
    SAMPLE="HR"."EMPLOYEES":50
    If you specify a schema, you must also specify a table. However, you can specify a
    table without specifying a schema; the current user will be assumed. If no table is
    specified, then the sample_percent value applies to the entire export job.
    Note that you can use this parameter with the Data Pump Import PCTSPACE
    transform, so that the size of storage allocations matches the sampled data subset. (See
    "TRANSFORM" on page 3-42.)
    *Restrictions*
    ■ The SAMPLE parameter is not valid for network exports.
    *Example*
    In the following example, the value 70 for SAMPLE is applied to the entire export job
    because no table name is specified.
    
    expdp hr DIRECTORY=dpump_dir1 DUMPFILE=sample.dmp SAMPLE=70
  • 5. Re: Part Copy of Production data to Development Environment
    jgarry Guru
    Currently Being Moderated
    I may not be understanding something, but can you do a point in time incomplete recovery for just the tablespaces you want? Then you'd have to do a resetlogs and probably some index work.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points