Thursday, July 21, 2011

Sharing the Same Read Only Tablespace between Multiple Databases

The Theory:
Separate databases can share the same read only datafiles on disk. The datafiles must be accessible by all databases.

The Method:
This will be implemented using transportable tablespaces.

Note: The Shared tablespace must remain read-only in all databases mounting it.

The Way:
-The Shared read only Tablespace is already belongs to a database (ORCL).

-Make the tablespace read only:
SQL> ALTER TABLESPACE USERS READ ONLY;

-Export tablespace metadata using expdp utility:

export ORACLE_SID=ORCL
expdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_tablespaces=USERS logfile=ts_export.log

-Import tablespace metadata to the destination database using impdp utility:

export ORACLE_SID=FOO
impdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_datafiles=/ORA11G/ORADATA/ORCL/USERS01.DBF logfile=ts_import.log

*I suppose that directory "data_pump_dir" have the same path on both databases (ORCL &FOO).

Note: Dropping the read only tablespace in some databases will not modify the datafiles for the tablespace.Thus, the drop operation does not corrupt the tablespace,
Sure you will not use -including contents and datafiles- clause to delete the shared tablespace on one database or this will delete the tablespace datafiles on the Filesystem.


Warning:
Switching the read-only shared tablespace to read-write mode will lead to database corruption if it's mounted in more than one database.

No comments: