Discussion:
Online partition move 12.2.0.1
(too old to reply)
samrat roy
2018-10-04 04:57:27 UTC
Permalink
Hi,
I am trying online sub partition move but it fails with ORA-00054 resource busy error. Isn’t 12.2.0.1 claims that online move is possible while writes are going on the same segment. Writes are from goldengate replicate. Once replicats are stopped obviously it works. But is the expectation of 12c online move is “read online” ?

-Samrat
Stefan Knecht
2018-10-04 06:35:14 UTC
Permalink
What is the exact script / statement you're running? Are you setting
ddl_lock_timeout? It might need to wait for any open transactions and may
time out?
Post by samrat roy
Hi,
I am trying online sub partition move but it fails with ORA-00054 resource
busy error. Isn’t 12.2.0.1 claims that online move is possible while writes
are going on the same segment. Writes are from goldengate replicate. Once
replicats are stopped obviously it works. But is the expectation of 12c
online move is “read online” ?
-Samrat
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/
samrat roy
2018-10-04 14:02:46 UTC
Permalink
In order to speed up slow insert current partition’s subpartitions which are in “cold” disks are moved to “hot” flash. goldengate is only client writing in this subpartition. The “resource busy” error comes only after good 3-4mins of running the statement. Typically it used to come right away if it’s a blocking operation and required X lock to start its proceedings. This might suggest it requires X lock towards the end for “merging” ? Or is it that in 12c there is a default value of ddl_lock_timeout which is 3-4 mins ? Isn’t ddl_lock_timeout is used for blocking/offline ddls, is it still required if its supposed to be online operation.

The statement:
Alter table testdba.testtable move subpartition testsubpart1 tablespace hot online parallel (degree 10);




On Oct 3, 2018, at 11:35 PM, Stefan Knecht <***@gmail.com<mailto:***@gmail.com>> wrote:

What is the exact script / statement you're running? Are you setting ddl_lock_timeout? It might need to wait for any open transactions and may time out?

On Thu, Oct 4, 2018 at 11:57 AM, samrat roy <***@hotmail.com<mailto:***@hotmail.com>> wrote:
Hi,
I am trying online sub partition move but it fails with ORA-00054 resource busy error. Isn’t 12.2.0.1 claims that online move is possible while writes are going on the same segment. Writes are from goldengate replicate. Once replicats are stopped obviously it works. But is the expectation of 12c online move is “read online” ?

-Samrat
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net<http://zztat.net/> | @zztat_oracle | fb.me/zztat<http://fb.me/zztat> | zztat.net/blog/<http://zztat.net/blog/>
Loading...