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?
