Transportable Tablespaces TTS

Sharing Read-only Tablespaces Between Different Databases with TTS

Come back to visit occasionally as new posts about TTS.  I put quite a few TTS scenarios in my book, this is one of them.

There is an interesting MOS article that mentions a scenario to share a single tablespace between two databases – both databases would be using the tablespace in read-only mode.  How do you get two different databases to recognize the same datafile? The article doesn’t let you know the answer…but I will.  Using Transportable Tablespaces (TTS)

How to Share Tablespace Between Different Databases on Same Machine [ID 90926.1]

Assumptions:

Both databases using the same block size and the same Oracle Version on the same server.

Sharing the USERS tablespace between two databases on a single server – source ORACLE_SID (DB1), target ORACLE_SID (DB2).

Steps to Accomplish:

1. Made sure there were no tablespaces of the same name in NEW, renamed existing USERS tablespace to USERS1.

2. That still left the same username in the DB2 database – SCOTT.

Created a new user named SCOTT2.in the DB2 database.

3.  In the source database DB1, made the USERS tablespace read only. Made sure it was transportable.

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘USERS’,TRUE);

SELECT * FROM transport_set_violations;

alter tablespace users read only;

4. Exported Users as a transport set using Datapump.

Since this was on the same server, I kept the staging directory the same location as the dump location.

5.  Import the transport set into database DB2, using the REMAP_SCHEMA to make SCOTT2 the owner of the imported objects.

Now you have two sets of the same data in two databases, different schema names in both. I changed the schema name because it already existed in both databases.

If there is no identical schema name in the second database then you won’t have to remap to a new schema name.

Cool!  Cool beans!

Other possible test cases for this type of TTS currently working on:

Small differences in versions – 11.1.0.6 with a 11.1.0.7 . I would see issues related to differences in major versions.

Problems with working with dual read-only tablespaces – how to add data, performance, contention, compression, etc.

What would happen if you tried to make one of them read/write?

Advertisements

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