ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [APEX_PUBLIC_USER] after noncdb_to_pdb.sql

I found a bug last week after the creation of a PDB from NON-CDB in an empty 12.2 CDB with the noncdb_to_pdb.sql script :

	[oracle@server ~]$ sqlplus / as sysdba
	SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 27 13:40:56 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	SYS@CDB> CREATE PLUGGABLE DATABASE PDB USING '/tmp/ORCL.xml' NOCOPY TEMPFILE REUSE;
	Pluggable database created.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	4 PDB MOUNTED
	SYS@CDB> col pdb_name for a15
	SYS@CDB> select pdb_name, status from dba_pdbs where pdb_name = 'PDB';
	PDB_NAME STATUS
	--------------- ----------
	PDB NEW
	SYS@CDB> alter session set container=PDB;
	Session altered.
	SYS@CDB> @?/rdbms/admin/noncdb_to_pdb.sql
	...

view raw kziaVrfyAcctStatInRootCbk.0 hosted with ❤ by GitHub

After a successful conversion from NON-CDB to PDB, here is the error when trying to connect in the PDB as APEX_PUBLIC_USER :

	[oracle@server ~]$ sqlplus APEX_PUBLIC_USER/*****@127.0.0.1:1521/PDB
	SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 28 08:34:05 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	ERROR:
	ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user],
	[APEX_PUBLIC_USER], [], [], [], [], [], [], [], [], [], []

view raw kziaVrfyAcctStatInRootCbk.1 hosted with ❤ by GitHub

	2017-04-28T08:34:05.210271-04:00
	Errors in file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_4284.trc (incident=14692) (PDBNAME=CDB$ROOT):
	ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [APEX_PUBLIC_USER], [], [], [], [], [], [], [], [], [], []
	Incident details in: /u01/app/oracle/diag/rdbms/cdb/CDB/incident/incdir_14692/CDB_ora_4284_i14692.trc
	Use ADRCI or Support Workbench to package the incident.
	See Note 411.1 at My Oracle Support for error and packaging details.
	2017-04-28T08:34:06.400212-04:00
	Dumping diagnostic data in directory=[cdmp_20170428085203], requested by (instance=1, osid=4284), summary=[incident=14692].

view raw kziaVrfyAcctStatInRootCbk.2 hosted with ❤ by GitHub

In the PDB, I looked at the APEX users more closely and found that APEX_PUBLIC_USER and APEX_050000 were defined as common users. Even if they are not available in the root container, see the result of the CDB_USERS view below :

	SYS@CDB> alter session set container = CDB$ROOT;
	Session altered.
	SYS@CDB> show con_name
	CON_NAME
	------------------------------
	CDB$ROOT
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	2 PDB$SEED READ ONLY NO
	4 PDB READ WRITE NO
	SYS@CDB> col username for a30
	SYS@CDB> select username, common, con_id from cdb_users where username like 'APEX%';
	USERNAME COMMON CON_ID
	------------------------------ ------ ----------
	APEX_LISTENER NO 4
	APEX_PUBLIC_USER YES 4
	APEX_REST_PUBLIC_USER NO 4
	APEX_050000 YES 4

view raw kziaVrfyAcctStatInRootCbk.3 hosted with ❤ by GitHub

So to make a long story short, the noncdb_to_pdb.sql did not "convert" the APEX_PUBLIC_USER and APEX_050000 schemas as local users but left them as common users. A service request was raised and until an official fix is available, here is a workaround that Oracle Support gave to bypass the issue :

	[oracle@server ~]$ sqlplus / as sysdba
	SQL*Plus: Release 12.2.0.1.0 Production on Wed May 3 12:44:46 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	SYS@CDB> alter session set container=PDB;
	Session altered.
	SYS@CDB> create table backup_user$ as select * from user$;
	Table created.
	SYS@CDB> select spare1 from user$ where name='APEX_PUBLIC_USER';
	SPARE1
	----------
	384
	SYS@CDB> select username, common from cdb_users where username='APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER YES
	SYS@CDB> update user$ set spare1=0 where name='APEX_PUBLIC_USER';
	1 row updated.
	SYS@CDB> select username, common from cdb_users where username='APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER NO
	SYS@CDB> commit;
	Commit complete.
	SYS@CDB> shutdown immediate;
	Pluggable Database closed.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	4 PDB MOUNTED
	SYS@CDB> startup
	Pluggable Database opened.
	SYS@CDB> show pdbs
	CON_ID CON_NAME OPEN MODE RESTRICTED
	---------- ------------------------------ ---------- ----------
	4 PDB READ WRITE NO
	SYS@CDB> select username, common from cdb_users where username = 'APEX_PUBLIC_USER';
	USERNAME COMMON
	---------------------------------------------------------------------------- ------
	APEX_PUBLIC_USER NO

view raw kziaVrfyAcctStatInRootCbk.4 hosted with ❤ by GitHub

After this UNSUPPORTED modification to the data dictionary of the PDB, we were able to connect as APEX_PUBLIC_USER as it should be (without ORA-00600) :

	[oracle@server ~]$ sqlplus APEX_PUBLIC_USER/*****@127.0.0.1:1521/PDB
	SQL*Plus: Release 12.2.0.1.0 Production on Wed May 3 12:54:35 2017
	Copyright (c) 1982, 2016, Oracle. All rights reserved.
	Last Successful login time: Thu Apr 27 2017 09:25:18 -04:00
	Connected to:
	Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
	APEX_PUBLIC_USER@127.0.0.1:1521/PDB> show con_id
	CON_ID
	------------------------------
	4

view raw kziaVrfyAcctStatInRootCbk.5 hosted with ❤ by GitHub

Oracle will release a patch to correct the problem to the noncdb_to_pdb.sql script. The script will convert the common APEX users to local users only if they are not already available on the root container (default 12.2):

Bug 25979661 : ORA-00600: [KZIAVRFYACCTSTATINROOTCBK: !USER] [APEX_PUBLIC_USER]
https://support.oracle.com/epmos/faces/BugDisplay?id=25979661

Once again the workaround provided here is NOT SUPPORTED by Oracle and was used only to diagnose the problem.