Oracle 18.4.0-XE: setPassword.sh fails for PDBADMIN

[goranpaues]

Describe the issue
Setting a new password for SYS and SYSTEM works, but fails for PDBADMIN:
[oracle@localhost ~]$ /home/oracle/setPassword.sh Not_Very_N1ce_Pass
The Oracle base remains unchanged with value /opt/oracle

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 4 17:52:00 2020
Version 18.4.0.0.0

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

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>
User altered.

SQL>
User altered.

SQL> 2 XEPDB1
*
ERROR at line 2:
ORA-02248: invalid option for ALTER SESSION

SQL> ALTER USER PDBADMIN IDENTIFIED BY "Not_Very_N1ce_Pass"
*
ERROR at line 1:
ORA-01918: user 'PDBADMIN' does not exist

SQL> Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production`

Environment (please complete the following information):

• Host OS: Windows 10
• Vagrant version: 2.2.10
• VirtualBox version: 6.1.12r139181
• Vagrant project: OracleDatabase/18.4.0-XE

Additional information

Looks like there is an extra pdb with a long name, causing the error when trying to find the PDB name:
[oracle@localhost ~]$ ls -dl $ORACLE_BASE/oradata/$ORACLE_SID/*/ | grep -v pdbseed | awk '{print $9}' | cut -d/ -f6
AE7E20660A3D5A64E055000000000001
XEPDB1

[PaulNeumann]

@goranpaues-tradedoubler I suspect something went wrong during VM provisioning. The installer RPM creates only a single PDB called XEPDB1, and the only directories under $ORACLE_BASE/oradata/$ORACLE_SID should be pdbseed and XEPDB1, so the setPassword.sh script should work correctly. (To double-check, I just built this VM on Windows 10, using the same versions of VirtualBox and Vagrant, and the script worked fine.)

A few questions to help figure out what happened:

• Do you have the output from vagrant up? If so, could you copy it to a gist and reply with the link? If not, could you try deleting the VM (vagrant destroy) and provisioning it again and saving the output? (An easy way to capture the output on Windows hosts is to run vagrant up | Tee-Object provision.log under PowerShell.)
• Did you change any of the files in the 18.4.0-XE directory or add any files to the userscripts directory before running vagrant up?
• Did you use environment variables or a .env.local file to configure the installation?
• Do you have any customizations in your "global" Vagrantfile (C:\Users\yourusername\.vagrant.d\Vagrantfile)?
• Is Hyper-V enabled on your host? Hyper-V can cause unpredictable behavior with VirtualBox VMs.

[goranpaues]

Hi @PaulNeumann , thank you for assisting!
I did a vagrant destroy and tried again, got the same error. But yes, I have added custom scripts to the userscripts directory. I tried to provision without my custom scripts, then it worked!
All my custom scripts starts with ALTER SESSION SET CONTAINER = XEPDB1. So I'm not sure why an extra PDB is created because of my scripts?

[PaulNeumann]

@goranpaues-tradedoubler I'm not sure either, but this is good, because it narrows down the problem.

Are all of the scripts in the userscripts directory SQL scripts, or are there shell scripts as well? Do the scripts contain confidential information? If not, could you copy them to a gist and reply with the link? If the scripts contain confidential information, or you'd prefer not to post them, could you describe what they're doing?

[goranpaues]

Its several megabytes of closed source DDL and stored procedure code. It will take too long to troubleshoot further and I think we can settle the issue with the fact that the error is caused by my own scripts somehow. Thank you for your help!

[PaulNeumann]

@goranpaues-tradedoubler I understand, and you're welcome!

This does point out an issue with the setPassword.sh script, because it doesn't work correctly if one or more PDBs are added. This affects not only the 18.4.0-XE project, but the 12.1.0.2, 12.2.0.1, 18.3.0, and 19.3.0 projects as well, because they all use the same setPassword.sh script. I'll try to come up with a generic solution and submit an enhancement PR for consideration.

[PaulNeumann]

@goranpaues-tradedoubler, @gvenzl I think I have a generic solution that will work.

To recap: The setPassword.sh script for the single-instance database projects (except for 11g XE) assumes that there is only a single PDB. It parses the directory names under $ORACLE_BASE/oradata/$ORACLE_SID to find the name of the PDB. This doesn't work correctly if one or more PDBs have been added.

Using catcon allows setPassword.sh to work correctly with either a single PDB or multiple PDBs. The script could look like:

#!/bin/bash
# LICENSE UPL 1.0
#
# Copyright (c) 1982-2018 Oracle and/or its affiliates. All rights reserved.
#
# Since: November, 2016
# Author: gerald.venzl@oracle.com
# Description: Sets the password for sys, system and pdbadmin
#
# DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
#

ORACLE_PWD=$1

sqlplus / as sysdba << EOF
      ALTER USER SYS IDENTIFIED BY "$ORACLE_PWD";
      ALTER USER SYSTEM IDENTIFIED BY "$ORACLE_PWD";
      exit;
EOF

echo 'Setting PDBADMIN password in PDB(s) using catcon'
log_dir=$(mktemp -d)

# shellcheck disable=SC2016
"$ORACLE_HOME"/perl/bin/perl "$ORACLE_HOME"/rdbms/admin/catcon.pl \
  -l "$log_dir" -C 'CDB$ROOT PDB$SEED' -f -b setPassword \
  -- --x"ALTER USER PDBADMIN IDENTIFIED BY \"$ORACLE_PWD\""

rm -rf "$log_dir"
echo 'Done setting PDBADMIN password in PDB(s)'

This sets the SYS and SYSTEM passwords, and sets the PDBADMIN password in all open PDBs where the PDBADMIN user exists. (It also removes the call to oraenv. This isn't needed, because the environment variables are already set in the oracle user's .bashrc.) I've tested this for the 12.1.0.2, 12.2.0.1, 18.3.0, 18.4.0-XE, and 19.3.0 projects.

@gvenzl, if you agree that this is a legitimate issue, and that this approach is reasonable, I'd be happy to submit a PR.

[gvenzl]

Hey @PaulNeumann,

Sorry for the delay in response, somehow your update slipped through back then.

Personally, I'm not a big fan of dependencies on other components such as Perl on catcon.
It makes, IMO, the overall solution more convoluted and the chances of something going wrong only broader (i.e. what if catcon fails somewhere, how to debug, etc.).

Instead, I think we can come up with a solution that retrieves all the open PDB names from v$pdbs and sets the passwords for each of them via dynamic SQL in a PL/SQL block.

That would keep the entire logic still self-contained and also still fairly simple to follow, read and debug in the future.

What do you think?

[PaulNeumann]

@gvenzl That makes sense to me. I'm not sure a single PL/SQL block will work, because EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER' doesn't work within a PDB, but I'll see what I can come up with.

[gvenzl]

Perhaps retrieve the list of PDBs into a file, loop over the values issuing the ALTER USER statement and then delete that file again, or something similar?

[PaulNeumann]

[Edited 05-Jun-2021 to include the completed, tested script.]
[Edited 04-Jul-2021 to set line size for SQL query output.]

@gvenzl Perhaps something like the following would work.

#!/bin/bash
# LICENSE UPL 1.0
#
# Copyright (c) 1982-2018 Oracle and/or its affiliates. All rights reserved.
#
# Since: November, 2016
# Author: gerald.venzl@oracle.com
# Description: Sets the password for sys, system and pdbadmin
#
# DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
#

ORACLE_PWD=$1

sqlplus / as sysdba << EOF
      ALTER USER SYS IDENTIFIED BY "$ORACLE_PWD";
      ALTER USER SYSTEM IDENTIFIED BY "$ORACLE_PWD";
EOF

echo 'Setting PDBADMIN password in open PDB(s)'

sqlplus -s / as sysdba << EOF > /tmp/set_pdbadmin_pw.sql
  SET HEADING OFF LINESIZE 120 PAGESIZE 0
  SELECT   'ALTER SESSION SET CONTAINER = ' || name || ';' || CHR(10)
        || 'SET SERVEROUTPUT ON' || CHR(10)
        || 'BEGIN' || CHR(10)
        || '  EXECUTE IMMEDIATE ''ALTER USER PDBADMIN IDENTIFIED BY "$ORACLE_PWD"'';' || CHR(10)
        || '  DBMS_OUTPUT.PUT_LINE (''Set PDBADMIN password in ' || name || ' PDB'');' || CHR(10)
        || 'EXCEPTION' || CHR(10)
        || '  WHEN OTHERS THEN' || CHR(10)
        || '    IF SQLCODE = -1918 THEN' || CHR(10)
        || '      DBMS_OUTPUT.PUT_LINE (''PDBADMIN user not found in ' || name || ' PDB'');' || CHR(10)
        || '    ELSE' || CHR(10)
        || '      RAISE;' || CHR(10)
        || '    END IF;' || CHR(10)
        || 'END;' || CHR(10)
        || '/'
  FROM     v\$pdbs
  WHERE    open_mode = 'READ WRITE'
  ORDER BY name;
EOF

sed -i -e 's|no rows selected|PROMPT No open PDBs found|' /tmp/set_pdbadmin_pw.sql

echo 'EXIT' | sqlplus -s / as sysdba @/tmp/set_pdbadmin_pw.sql

rm -f /tmp/set_pdbadmin_pw.sql

echo 'Done setting PDBADMIN password in open PDB(s)'

I've tested this for the 12.1.0.2, 12.2.0.1, 18.3.0, 18.4.0-XE, and 19.3.0 projects. What do you think?