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;

--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 Production on Thu Dec 3 10:23:06 2020

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

Enter password:
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.

Gerelateerd bericht

Geef een reactie

%d bloggers liken dit: