Sharing READ ONLY Tablespaces between Databases

I came across an OTN Thread today, where the question was raised, whether it is possible to use the same READ ONLY Tablespace in multiple Databases. At first glance, I thought that this should of course be possible, though the answers where somewhat discouraging. So I have done a quick test to prove it:

SQL> host mkdir /home/oracle/shared

SQL> create tablespace shared datafile '/home/oracle/shared/shared01.dbf' size 10m;

Tablespace created.

SQL> grant create session,create table to shared identified by shared;

Grant succeeded.

SQL> alter user shared quota unlimited on shared;

User altered.

SQL> create table shared.t tablespace shared as select 'Test' as word from dual;

Table created.

SQL> alter tablespace shared read only;

Tablespace altered.

The tablespace is now ready to get “transported”. We pretend that the datafile is placed on a SAN, accessible from the other Database. I have already created the directory dpdir in the original database, pointing to the directory /home/oracle/dpdir

$ expdp transport_tablespaces=shared directory=DPDIR dumpfile=shared.dmp
Export: Release 10.2.0.1.0 - Production on Wednesday, 03 February, 2010 18:23:11
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":
/******** AS SYSDBA transport_tablespaces=shared directory=DPDIR dumpfile=shared.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
 /home/oracle/dpdir/shared.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 18:23:43

Now I can plug in the tablespace into another database – I have setup one called tst for that purpose already:

$ export ORACLE_SID=tst
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 18:24:38 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create directory DPDIR as '/home/oracle/dpdir';
Directory created.
SQL> grant create session,create table to shared identified by shared;
Grant succeeded.

All that is now left to do is to import the description with DataPump into the tst Database:

$ impdp transport_datafiles=/home/oracle/shared/shared01.dbf
  directory=DPDIR dumpfile=shared.dmp
Import: Release 10.2.0.1.0 - Production on Wednesday, 03 February, 2010 18:33:13

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  
/******** AS SYSDBA
transport_datafiles=/home/oracle/shared/shared01.dbf
  directory=DPDIR dumpfile=shared.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:33:19

Now I am going to test whether the table is also accessible in the second Database:

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 3 18:34:21 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from shared.t;

WORD
----
Test

It is. So I am sharing now one tablespace between two completely different Databases :-)

About these ads
  1. #1 by Hans Forbrich on February 3, 2010 - 23:44

    Nice demo.

    I always a pleasure reading your responses in the forums, since they are always well thought out and supported by proof.

  2. #2 by Simar on February 4, 2010 - 01:51

    Thanks for rectifying me and that too with a wonderful example.

    Learnt a new feature………

    Best Regards

  3. #3 by Kevin on February 4, 2010 - 02:46

    Okay, I’m seeing some major issues with this technique:

    1. Just because the tablespace is marked ‘READ-ONLY’ doesn’t mean the tablespace NEVER get’s written too. Things like tablespace status, last SCN, high-water marks, segment status, etc. ALL get written on occasion to the tablespace file, even when READ-ONLY.

    2. According to the std Oracle documentation:

    “You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE…ADD or ALTER TABLE…MODIFY, but you will not be able to utilize the new description until the tablespace is made read/write.”

    All of these could easily cause bugs, issues, or otherwise mess with the state of the tablespace and/or other dictionary entries stored on one of the xx systems accesing the datafile.

    Have you actually thoroughly tested this? I couldn’t possibly believe that Oracle would state that this really works…

  4. #4 by Simar on February 4, 2010 - 03:02

    Kevin

    He is showing with example that it works. Except one environment, just drop the tablespace in all environments before update. Update in that environment and then again share with other environments.

    Should be easy, I hope you will definitely test this before using in your systems.

    Regards

  5. #5 by Kevin on February 4, 2010 - 03:17

    No, he’s showing that he can read the table. That’s all. What he’s not showing is how after testing with various operations both systems still show an accurate state of the tablespace. Because you have two data-dictionaries reading the same tablespace file, there’s a major potential for data corruption to occur as one dictionary on one system may reference entities which may or may not exist in the tablespace, due to changes from ANOTHER system accessing the file.

    Read-Only tablespace, does NOT mean read-only datafile. The data file, can, and may change, thus resulting in one of the system’s dictionary being out-of-sync with the datafile.

    Hence why RAC is even required. During any tablespace operation, data is sent to each RAC node to update internal buffer entries and other ‘live’ objects when a change occurs. In addition, in RAC not only is the tablespace/datafiles shared, but also the dictionary, allowing for dictionary entries to be shared.

    The best method would be to simply use a MATERIALIZED VIEW with manual refresh so that you have a copy. Sharing a tablepace like this is just a reciepe for disaster….

  6. #6 by Hans Forbrich on February 4, 2010 - 06:00

    As far as I can tell, ‘Oracle’ does not state that this will work. It certainly violates the basic tenents for tablespaces going back to Oracle 6.

    The challenge provided by the original question was ‘*can* tablespaces be shared between test and dev environments to save disk space’.

    Uwe has correctly indicated that you ‘can’. I put this in the same category as having two databases and instances on the same server with the same SID – as long as you use a different ORACLE_HOME, you ‘can’.

    However, ‘should you do this?’ is another valid question.

    Now it is time to understand the risks. And you do highlight some of the dangers.

    Since you raise this, Kevin, perhaps you could identify some of the circumstances under which data files will change in a read/only tablespace. (If they can change then one may wonder why Oracle provides an option to allow RMAN to skip R/O tablespaces.)

  7. #7 by Uwe Hesse on February 4, 2010 - 07:08

    Hans,
    thank you for your feedback – I have noticed you as a very competent person by your postings on OTN for quite a while already!
    Simar,
    you’re welcome :-) It was an interesting exercise to do.
    Kevin,
    regarding the little discussion that you raised:
    I frankly do not see so many risks with this setup.
    “Things like tablespace status, last SCN, high-water marks, segment status, etc. ALL get written on occasion to the tablespace file, even when READ-ONLY.” I doubt that. To my knowledge, this is not the case. A datafile from a READ ONLY tablespace gets no writes whatsoever, which is the reason why you an even place it on an optical disk. On the other hand, I did not “thoroughly” test this, of course. I just proved that it can be done (in about 30 minutes, including think time :-) ). My statements do not necessarily reflect the position of Oracle Corp. – I think that should be clear from my disclaimer above.

  8. #8 by Uwe Hesse on February 4, 2010 - 08:31

    Well how does the song go “It’s all been done before…” Just checked the documentation about that topic – a highly recommendable thing to do :-)

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1319

    Still I enjoyed that discussion, and I hope you guys also!

  9. #9 by Hans Forbrich on February 4, 2010 - 14:16

    Thanks for posting that doc reference.

    I verified that this capability is documented all the way back to the 8i DBA manual at http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#922 .

    (And I am covered with considerable embarrassment as I am often the one to quote the docs.)

  10. #10 by Kevin on February 4, 2010 - 18:03

    Uwe, thanks for looking into this further. I’m still skeptical, but I’m willing to sit down and test it further. I’ve been told otherwise from Oracle, and it seems even the documentation contradicts itself (tablespaces section states R/O tablespaces can still be written too, trans tablespace section says R/O doesn’t).

    My biggest concern would be simply the hidden meta-data on the file that Oracle doesn’t necessarily explain. Those data structures are part of the physical file format and aren’t generally documented other than internally. Since Oracle hasn’t stated that the entire file can be read-only, just the tablespace (which may contain multiple files), that left open the possibility that the meta-data could change, yet still keep the data-segments intact.

    We have a large tablespace we use here that we keep read-only. I’m going to do some MD5/SHA-1 hashes on it to see if anything in the file changes after a period of time.

    I may also post a support ticket from Oracle for clarification from their support team. Something like this could be a powerful option if it really worked, in a production grade level.

  11. #11 by Uwe Hesse on February 4, 2010 - 18:19

    Kevin,
    because it is possible to place the datafiles of a READ ONLY Tablespace on an optical disk since the introduction of Transportable Tablespaces in 8i, I think it should be quite sure that these files cannot change. Look at

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tspaces006.htm#ADMIN11381

    However, I would be interested in your observations and the feedback from our support, if you would be so kind to share it.

  12. #12 by Kevin on February 4, 2010 - 18:58

    Right, which is the confusing part. Since your still able to add a datafile, drop a table, index, or blob, in addition to any number of *other* functions on a read-only tablespace… I’m assuming those operations fail when it’s on a read-only device? I’d hate to see the dictionary updated with an invalid operation…

  13. #13 by Kevin on February 4, 2010 - 20:18

    After reviewing some of the support docs, most references are of the following:

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

    Most of the Oracle documentation deals with the tablespace on a single machine. How this would be any different than over a shared mount (either via SAN, Clustered File system, or Filer based [CIFS, NFS]) is unknown.

    In regards to Han’s question, I’ve gathered the following:

    1. DROP operations can still occur on a read-only tablespace
    2. Tablespace modify operations (add datafile, modify datafile) still work

    There’s likely others that still work, but I haven’t gone through any of the comprehensively.

    It’s possible this may work with regards to a major caveat:

    Only operations that work on the ‘dictionary’ entries for the tablespace will function correctly. I’m assuming (since I don’t have a good test environment at the moment) that the drop operation succeeds on DB1, but DB2 still sees the objects and it’s data.

    This could however, cause *major* complications if, at any point you actually need to update the shared-RO tablespace. It would be an absolute necessity for it to be first removed from all other shared instances (except one), then updated, then re-transported to ensure that dictionary entries, segment listings, and other dictionary information regarding the tablespace is accurate.

    We’ll see what Oracle has to say on the matter…

  14. #14 by Kevin on February 4, 2010 - 21:03

    I have also confirmed that the following updates to the datafiles happens:

    1. Datafile header update after RMAN backup operations
    2. Datafile header update after tablespace configuration change
    3. Recover database operations
    4. Alter database reset logs operations
    5. Purge operations on dropped objects within a tablespace using the Recyclebin

    In these cases either one of two things happens:

    A. The change is reflected in the dictionary, but the appropriate change to the datafiles isn’t made. A message is sent to the alert log stating as such. It simply ignores the fact that the data-file write doesn’t complete (such as is the case if the datafiles are on read-only media).

    B. It ignores the fact that the tablespace is read-only and writes to the datafile regardless.

    Still waiting on clarification from Oracle on some of these matters…

  15. #15 by Hemant K Chitale on February 5, 2010 - 06:48

    Interesting ! I did not know of this.
    I wonder how many sites have actually implemented sharing a tablespace between two databases without actually copying the datafile(s).

    Hemant

  16. #16 by Uwe Hesse on February 5, 2010 - 08:27

    Kevin,
    thank you for keeping us informed about your investigations.

    Hemant,
    I wasn’t aware of it either – seems to be one of the many widely unknown or forgotten Oracle Database features :-)
    I am tempted to initiate a little survey about who actually uses this…

  17. #17 by Surachart Opun on February 5, 2010 - 10:48

    great demo… for sharing a tablespace -)

  18. #18 by David Aldridge on February 8, 2010 - 08:07

    This is very interesting, and it’s something I looked into a couple of years ago when faced with sourcing bulk data from Siebel for data warehouse feeds. The source database was a read-only copy of production made by some kind of SAN mirror splitting magic and was essentially recreated every day. As soon as this system was available in the early hours of the morning multiple other systems “pounced” on it to extract as much as possible as soon as possible — something of a feeding frenzy, quite frankly. It would have made a lot of sense for the relevant tablespaces to be shared between the destination databases.

    Alas the discussion never got further than cautious agreement that it was theoretical possible.

    I certainly share some of Kevin’s concerns, but in the right context I think that the risks are manageable.

  19. #19 by Kevin on February 12, 2010 - 13:38

    FYI:

    Official word from Oracle. They state that it works, but does have the problems issued that I outlined above. Meaning, certain operations still can write to the data-file regardless of it’s read-only state.

    This is why they don’t generally expound on the feature as being a major feature.

    To work around these issues, it is recommended when doing any of the following: Resetlogs, RMAN Recovery Operations, DROP w/purge – that the read-only tablespace be taken OFFLINE on the system in which these operations are occurring. This will prevent the system from making any changes to the datafile.

    All other operations, such as drop table, drop index, etc. function purely on the dictionary of the target system, and as such, only affect that environment alone. If and when you *do* need to update the tablespace, the tablespace will need to be re-transported to the other environments, and the objects possibly dropped again.

    In addition, if an object is dropped on the *primary* source of the data, it will need to be dropped on all other systems reading the datafile or else you will have objects pointing to invalid entries in the data file if the tablespace is transported again to the environments.

    So, in the end, it does work, but has a number of particulars which need to be taken into consideration.

  20. #20 by Uwe Hesse on February 12, 2010 - 13:59

    Kevin,
    thank you for sharing this information! I wasn’t aware that datafiles belonging to a READ ONLY tablespace get writes at all. But it should be possible to avoid these writes by placing the datafile on a WORM-device, thereby circumventing most of the issues that you have pointed out, shouldn’t it?

  21. #21 by Kevin on February 12, 2010 - 18:15

    According to Oracle, on WORM devices, a message is printed to the alert log stating that the write was attempted but failed. However, it shouldn’t fail the process in it’s entirety (unless of course, the R/O tablespace was the objective of the restore).

    I can’t explain to you why exactly, it tries to update the files. It seems that it is entirely optional (which begs the question, why they even bother attempting), but none-the-less that is what it does.

    I’m glad something like this was paid a little attention by your blog. It was a feature I’ve been meaning to investigate for years, but never got around to doing. Looks like it will work, with a few gotchas, but could be quite handy in certain situations.

  22. #22 by iiotzov on June 12, 2014 - 17:40

    Using the documented Transportable Tablespaces method to share Read-Only tablespasces requires that the RO tablespaces to be shared must be self-contained, which is a significant restriction.

    Read-Only tablespace can be shared directly, i.e. without the TTS part, but those methods are not supported by Oracle – http://iiotzov.files.wordpress.com/2012/09/iotzov_sharerotablespaces_presentation_final.ppsx

  23. #23 by Celesta on September 24, 2014 - 08:52

    I every time spent my half an hour to read this web site’s content every day along with a
    mug of coffee.

  24. #24 by Uwe Hesse on September 24, 2014 - 18:50

    Celesta, that is one of the nicest comments about a technical blog that I ever saw – thank you! And keep on doing that :-)

  1. Log Buffer #177: a Carnival of the Vanities for DBAs | The Pythian Blog
  2. Blogroll Report 29/01/2009 – 05/02/2010 « Coskan’s Approach to Oracle
  3. [A] การตรวจเช็คบนฐานข้อมูล(oracle) ตอนที่ 5 | Oracle in Thai | Oracle in Thai | Oracle User Group in Thailand |

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

Follow

Get every new post delivered to your Inbox.

Join 2,777 other followers

%d bloggers like this: