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 22.214.171.124.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:
|ORA-28014: Cannot Drop Administrative Users (Doc ID 1566042.1)
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.