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 🙂
Pickleball spielen 
#1 von Hans Forbrich am Februar 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 von Simar am Februar 4, 2010 - 01:51
Thanks for rectifying me and that too with a wonderful example.
Learnt a new feature………
Best Regards
#3 von Kevin am Februar 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 von Simar am Februar 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 von Kevin am Februar 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 von Hans Forbrich am Februar 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 von Uwe Hesse am Februar 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 von Uwe Hesse am Februar 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 von Hans Forbrich am Februar 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 von Kevin am Februar 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 von Uwe Hesse am Februar 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 von Kevin am Februar 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 von Kevin am Februar 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 von Kevin am Februar 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 von Hemant K Chitale am Februar 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 von Uwe Hesse am Februar 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 von Surachart Opun am Februar 5, 2010 - 10:48
great demo… for sharing a tablespace -)
#18 von David Aldridge am Februar 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 von Kevin am Februar 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 von Uwe Hesse am Februar 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 von Kevin am Februar 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 von iiotzov am Juni 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 von Celesta am 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 von Uwe Hesse am 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 🙂
#25 von nivas am November 6, 2014 - 20:57
Thanks for wonderful post…when trying to change source database tablespace to read write mode gives error. I want to modify the data again on source to share with 2nd instance, how to fix this error ?
ALTER TABLESPACE tbs_shared READ WRITE
*
ERROR at line 1:
ORA-01256: error in locking database file 29
ORA-01110: data file 29:
‚/oracle/dbdata/app001/shared_DB.dbf‘
#26 von Tanah Abang am Januar 7, 2015 - 19:29
It’s actually a cool and useful piece of info. I’m glad that you just shared
this useful info with us. Please keep us up to date like
this. Thank you for sharing.
#27 von Trish am März 1, 2017 - 17:38
Thanks for sharing this. Using this can we have the tablespace online on the original/live database and have it as read-only on the test database? Can the changes made on live readable on test? Thanks for your help