Sharing Tablespaces between Databases With Transportable Tablespaces

Sharing tablespaces between databases for data refresh process  has been there for long time using transportable tablespace.  Oracle officially supports this process  from 2010. See metalink document 90926.1  for details.

Following  are the steps involved using EMC BCV Snap/Clone and Transport tablespace

Note: EMC BCV Snap/Clone is optional and required only if the live and archive database reside on different host.  BCV snap is primarily used to clone the live database and  present the data to different host.

1. Use EMC to make  copy of live database from different host.

2. Use  transport tablespace to transport required tablespace to  archive database.  The process is same as regular transport tablespace, only difference being that you are avoiding the time consuming copy process. Instead the read-only files are shared between 2 database.

3.  Use custom scripts to refresh the data

4.  Drop the tablespace from archive database  after data refresh  (Without the INCLUDING DATAFILES OPTION);

5.  Repeat  steps 1-4 for daily refresh

Requirements

1. Tablespaces  that are transported must be READ-ONLY

2.  Databases that must be shared the tablespace must reside on same host

3.  Enabling and disabling of constraints may be required.

Advertisements

Tagged: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: