RE: Transportable Tablespaces and Deferred segment creation "bug"
Date: Mon, 26 Nov 2012 10:16:35 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C764F1FA3_at_JAXMSG01.crowley.com>
Ahem :),
Speaking of oracle homes, I am still experimenting with naming conventions and the out-of-line patching. So looking for input.
I decided lately to spell out the oracle version, (and I had no reason for numbering dbhome_1, _2).
Like.
/orasoft/app/oracle/product/11.2.0.3.0/dbhome
/orasoft/app/oracle/product/11.2.0.3.3/dbhome
etc..
The original reason was to upgrade and patch databases one at a time, say 11.2.0.1.0 to 11.2.0.3.0, and 11.2.0.3.0 to 11.2.0.3.3. One result is upgrading through a version, say to 112033 leaves an empty oracle homes like 10g, 112010, or 112030, which can be cleaned up, or left in place for later upgrading of other databases.
Now, if I get another PSU, I think I have to install the base 112030 in a new home say, 11.2.0.3.5, and apply the PSU 112035?
So the jury is still out on whether or not this is an optimal idea or not. Any comments from the list? Any other naming conventions that the listers like over others?
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
Sent: Monday, November 26, 2012 5:55 AM
To: oracle-l_at_freelists.org
Subject: Transportable Tablespaces and Deferred segment creation "bug"
Morning all,
Oracle versions are 11.2.0.3 Enterprise and Standard on Linux x86-64. May affect others.
I'm fighting/discussing with Oracle Support at the moment on a potential bug in the use of Transportable Tablespaces exporting from an Enterprise Edition database and importing into a Standard Edition.
I need to do this because I (yes, me!) inadvertently assigned the wrong Oracle Home while creating a 6.5 Tb database and built it with Enterprise rather than Standard edition (it was a naming convention that is, ahem, useless, that caused this error).
Anyway, Enterprise comes with deferred_segment_creation defaulted to true, so creating tables and/or indexes doesn't create a segment until the first row is added to the table.
Standard Edition also comes with the default set to true, but Standard Edition ignores the parameter and all segments get allocated on initial creation of the table/index.
Doing a Transportable Tablespace export works fine, doing the import results in a number of IMP-00017 errors caused by ORA-01647 "Tablespace is read only, cannot allocate space in it" errors. It's not the most helpful error message in the world, all the tablespaces being imported are read only because they don't actually exists until the import if done - so I couldn't make them read write even if I wanted to!
All the tables throwing this error on the import are empty in the source (Enterprise) database and don't have a segment allocated for them in DBA_SEGMENTS due to the default setting.
I've written it up here if anyone suffers something similar.
There is nothing in Oracle's docs to say that this will happen, even the engineer on the call at MOS was unable to find any. I've requested that this be logged as a bug, but I don't hold out much hope!
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 26 2012 - 16:16:35 CET