RE: Relocating LOB segment
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: Kevin Jernigan <kevin.jernigan@xxxxxxxxxx>, Brent Day <coloradodba@xxxxxxxxx>
- Date: Tue, 10 Feb 2015 16:48:10 +0000
Thank you to all who have provided their feedback. Here is a brief background
of why we are moving segments to different tablespaces and then I will tell you
the pushback that I am getting from Oracle.
In the older Oracle E-Business Suite databases, there were 300+ tablespaces to
store different objects. Oracle later released a consolidated TS model which is
comprised of around 12 tablespaces. As a precursor to upgrading one of our EBS
systems to a recent version of the applications, we are converting our database
to this consolidated TS model. Oracle provides a utility, called OATM (Oracle
Application TS Migration) to automate the process of migrating segments from
the old TS to the new TS. This utility is built around the “move” command.
When using Oracle’s OATM utility to move a 300GB table that has a LOB segment,
it is taking around 28-30 hours. When I used the DBMS_REDEF method, with a DOP
of 8, I was able to move the table in less than five hours.
As a standard process in our organization, we wanted to ask Oracle’s EBS group
if it is okay for us to use the DBMS_REDEF process instead of the move command
(that the OATM utility uses under the hood) just for this particular table. The
Oracle EBS Suite group is absolutely adamant that they would not certify this
process as they have not tested it internally.
From: Kevin Jernigan [mailto:kevin.jernigan@xxxxxxxxxx]
Sent: Friday, February 06, 2015 6:16 PM
To: Hameed, Amir; Brent Day
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Relocating LOB segment
If / when you upgrade to 12.1 (Oracle Database 12c), more parallelism on
SecureFiles LOBs is supported. Also, you can use the (new in 12c) "ONLINE"
keyword with the "ALTER TABLE MOVE" statement to move and/or compress and/or
convert data while the table remains fully online...KJ
--
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
+1-650-607-0392 (o)
+1-415-710-8828 (m)
On 2/6/15 3:48 AM, Hameed, Amir wrote:
Thanks Brent. The DB version is 11.2.0.4.
From: Brent Day [mailto:coloradodba@xxxxxxxxx]
Sent: Thursday, February 05, 2015 10:49 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Relocating LOB segment
Sorry I hit send to soon.
Without knowing your version it is hard to give you correct information but I
will give you a couple of thoughts that might help based on tools and methods I
have used.
If you are on 11.2 and use SecureFiles then you can export with parallel. I
have used this a few times but it does require the lob to already be stored as
secure files. See metalink doc 1467662.1 for details.
Another alternative is to use DBMS_REDEFINITION. If you haven't used this tool
before it is awesome. You can move the table behind the scenes while allowing
transactions to continue (captured via materialize views behind the scene).
Once the initial table create and data load is done you can sync the changes,
then build your indexes, keys, etc. and then run the finalize procedure.
Hope that helps.
Brent
On Thu, Feb 5, 2015 at 8:34 PM, Brent Day
<coloradodba@xxxxxxxxx<mailto:coloradodba@xxxxxxxxx>> wrote:
Since it is a LOB you can't parallelize it.
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel001.htm#CACGFHCC
You can typically use parallel DDL where you use regular DDL. There are,
however, some additional details to consider when designing your database. One
important restriction is that parallel DDL cannot be used on tables with object
or LOB columns.
Other related posts:
