Skip to Main Content
  • Questions

  • System tablespace migration from dictionary to locally managed in 12c.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jacob.

Asked: October 06, 2016 - 6:54 pm UTC

Last updated: October 08, 2016 - 1:25 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Tom,

I am an Oracle DBA who has inherited a large production landscape which has been in continuous operation since 1999 and Oracle 8.

Back in the days of 10g, the previous DBA performed a migration of all the tablespaces from dictionary to locally managed using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL, except for the SYSTEM tablespaces.

Earlier this year I completed the upgrade of the databases in this landscape from 11.2.0.3 to 12.1.0.2 however the SYSTEM tablespaces remain dictionary managed. At the moment we are implementing Oracle Advanced Compression, which will include a downtime for dropping old tablespaces and renaming the new compressed ones to maintain consistency. I have proposed that we migrate the SYSTEM tablespaces to locally managed during the same downtime, however I have been asked to provide additional justification beyond upgrade performance and bringing us up to current Oracle best practices.

Assuming I get the go-ahead, I've been reading arguments on both sides for using a drop and recreate strategy with transportable tablespaces versus doing a full exp/imp. Most articles out there on this topic are many years old, and I am also curious if changes since then may lead to different recommendations.

Questions:

1. What are the specific performance or stability benefits that would justify the additional work and downtime to migrate SYSTEM from dictionary managed to locally managed?

2. Assuming you would not recommend EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(‘SYSTEM’); for such a migration would you use a full exp/imp or a drop and recreate with transportable tablespaces and what are the benefits and downsides of each?

Thank you for your time,

Jacob Miller
Oracle DBA

and Connor said...

1) The bottleneck on FET$/UET$ is perhaps justification enough. Nothing worse than having an *unknown* completion time for simple operations like dropping tables or partitions etc. But for me the biggest thing is support - if you have a problem and contact Support - there will 100,000 customers with a LMT system, and 7 customers without it... I know which camp I'd rather be in :-)

2) I wrote this post many many many years ago :-) but I think its still valid

http://www.oracledba.co.uk/tips/dbms_space_admin.htm

Unless using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL is going to give you incredible small extents (see the blog post), I can't see a reason why you would not use this option. exp/imp is a sledgehammer approach with lots of risk.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

System tablespace specifically though.

Jacob Miller, October 08, 2016 - 12:18 am UTC

Connor,

Thanks so much for your reply, it is greatly appreciated, but I have a little bit of followup.

I understand the FET$/UET$ bottleneck conceptually at least, but the counterargument I'm receiving from the previous DBA is that all the tablespaces in these databases are already locally managed, except SYSTEM. He's claiming that since the addition of SYSAUX, the SYSTEM tablespace is essentially static except during an upgrade so there wouldn't be enough benefit to justify the migration.

Which processes could I point out to an Oracle 8/9 era DBA and say, "Whenever the database does X, Y, and Z, we're running into the FET$/UET$ bottleneck in the SYSTEM tablespace." ?

With regards to the second question: in some of the older posts, people argued against using DBMS_SPACE_ADMIN because the extent size remains in 'USER' mode and thus the benefits are not fully realized. Is this no longer a relevant objection?

I would be happy to take exp/imp and transportable tablespaces off the table, as I agree it is a sledgehammer and not something I was looking forward to.

Thanks again for your time,

Jacob Miller
Connor McDonald
October 08, 2016 - 1:25 am UTC

I think that you cant have a read-only standby unless all the tablespaces are LMT. Its been so long since I've tinkered with DMT, so I cant be 100% sure on that.

"because the extent size remains in 'USER' mode and thus the benefits are not fully realized" is fiction.

If there is so much resistance to it, then just schedule it for your next major upgrade.

Most battles in IT are political not technical :-)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.