Don’t create a user from an Oracle script within a set container to pdb

For an 19C Oracle database that will be added in an Oracle Fail Safe cluster I had to install statspack with an Oracle script, which also creates the perfstat user. Because I didn’t want to create a local listener because Oracle Fail Safe has is own mechanism for this, I thought to make a shortcut and install statspack creating it in the pdb while changing the session from pdb to cdb.

This is how this went:

Microsoft Windows [Version 10.0.17763.1577]
(c) 2018 Microsoft Corporation. All rights reserved.


C:\Windows\system32>set oracle_sid=cdb1
C:\Windows\system32>sqlplus / as sysdba
SQL> alter session set container=pdb1;
SQL> show con_name;
PDB1

SQL>@?/rdbms/admin/spcreate
--some install stuff is asked, password for perfstat user and what tablespaces to user.

--after that, try to connect to perfstat:
C:\Windows\system32> sqlplus perfstat@pdb1

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 3 10:23:06 2020

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

So the password and user were correct, but there was no listener created and no tnsnames.ora to look at. So I created these, bounced the new listener and database and it registred with the listener. But still I got the same error. There was something really wrong with this user perfstat!

After that I used spdrop.sql to rinse and repeat, but this time with a listener connection to the pdb, to find out the script couldn’t drop the perfstat user:

drop user perfstat cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

After some digging on Oracle Support and the Internet I found this MOS note:

This gave me insight in two things: never create a user from an Oracle internal script with alter session set container to pdb, and to right this mistake to remove the perfstat user, use

alter session set "_oracle_script"=true

in your sql session before dropping the perfstat user. It is not recommended except when Oracle Support explicitely tells you to use this oracle_script parameter. But decided this was just a similar circumstance that started my trouble. So I performed this and dropped the perfstat user without a problem:

sqlplus sys@pdb1 as sysdba
alter session set "_oracle_script"=true;
drop user perfstat cascade;

After that I used the statspack script again but this time with a tns connection to the pdb. After that there were no problems with logging in with the perfstat user on the pdb.

Hope this might help someone.