Transportable Tablespace PDB Migration and Local Undo

Photo by Steve Johnson on Unsplash

Sometimes it is necessary to warn you about known pitfalls to avoid frustration. In this particular case I decided not to blog about it simply because I thought this won’t happen to too many other people. Well, yesterday my good friend Philippe Fierens dropped me a message about an issue he ran into with a Transportable Tablespace PDB Migration and Local Undo. And I immediately knew what caused him trouble – and I regret that I didn’t blog about it (sorry Philippe!). We’ve seen the same problem with a large ExaCC migration project earlier this year.

What happens?

You take a non-CDB and plan to migrate it into a PDB with Transportable Tablespaces. You created a CDB in Oracle 12.2.0.1, provisioned a PDB – and then you migrate. Of course you are using Local Undo as this first of all is the default in Oracle 12.2.0.1, and second the requirement for many features such as Hot Cloning. Everything works well.

What fails?

It looks very good until you attempt to clone this PDB. The cloning works fine but accessing the data in your cloned PDB leads to a lot of suspicious errors, for instance:

select 'x' from COMMON.CXY_SERVER_JOB_QUEUE where rownum=1
                       *
ERROR at line 1:
ORA-00600: internal error code, arguments: [4000], [56], [1], [1], [], [], [], [], [], [], [], []


select 'x' from COMMON.CXY_SERVER_QUEUE_JN where rownum=1
                       *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktuisc:xid], [9], [27], [12937620], [2020], [], [], [], [], [], [], []
 

select 'x' from XYZ_ADAPTERS.XYZ_AR_JDBC_EVENTS where rownum=1
                             *
ERROR at line 1:
ORA-00600: internal error code, arguments: [4097], [29], [11], [13963839], [24], [0], [], [], [], [], [], []

Philippe has seen similar patterns in his project.

The problems comes up due to a missing piece when the tablespaces get plugged into the PDB. This is connected with local undo. Everything seems to work ok until you clone this PDB now.

Where is this fixed?

This does happen only with Oracle 12.2.0.1. It is fixed with Oracle 18c and 19c. But there is no possibility to get a backport for Oracle 12.2.0.1 due to complexity.

How do you workaround it?

The workaround is simple but not nice. You need to create a (I call it) proxy CDB with SHARED UNDO. In this CDB you will provision your PDB first, then migrate your database with Transportable Tablespaces into it. Once you completed this task, you’ll unplug this PDB and plug it into your desired target CDB. This one has of course LOCAL UNDO as intended. From now on, all cloning operations will work fine.

If you have single tenants only, then you could also create the CDB with SHARED UNDO, and once the transportable import has been finished you can convert it to LOCAL UNDO:

shutdown immediate
startup upgrade
alter database local undo on;
alter database open;

Further Information

• Philippe Fierens’ Blog
• MOS Note: 2499535.1 – ORA-00600 [4000] While Doing Selects on Cloned PDB

–Mike