Tag: authentication

How to Upgrade Oracle Database with Username and Password

Normally, you upgrade an Oracle Database with native operating system authentication. The upgrade tooling connects to the database as / as sysdba. However, in some organizations, this is not allowed for security reasons. Every connection must be with username and password.

How can you upgrade an Oracle Database using username and password?

Set-Up

First, I’ll disable connections using native operating system authentication. I do that in sqlnet.ora.

$ env | grep TNS
TNS_ADMIN=/u01/app/oracle/product/19/network/admin
$ cat $TNS_ADMIN/sqlnet.ora
sqlnet.authentication_services=(none)

Let me check that it is disabled:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 09:55:05 2024
Version 19.21.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied

Upgrade Without Operating System Authentication

  1. I ensure that my AutoUpgrade is the latest version, or at least version 24.7.241021:
$ java -jar autoupgrade.jar -version
build.version 24.7.241021
build.date 2024/10/21 11:16:20 -0400
build.hash babf5a631
build.hash_date 2024/10/18 18:36:27 -0400
build.supported_target_versions 12.2,18,19,21,23
build.type production
build.label (HEAD, tag: v24.7, origin/stable_devel, stable_devel)
build.MOS_NOTE 2485457.1
build.MOS_LINK https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
  1. This is my AutoUpgrade config file. There’s nothing special in it, except for global.keystore which tells AutoUpgrade where to store its keystore. AutoUpgrade uses the keystore to keep your password safe until it is needed.
$ cat UPGR.cfg
global.autoupg_log_dir=/home/oracle/logs/autoupgrade-UPGR
global.keystore=/home/oracle/autoupgrade-keystore
upg1.source_home=/u01/app/oracle/product/19
upg1.target_home=/u01/app/oracle/product/23
upg1.sid=CDB19
upg1.timezone_upg=NO
  1. Now, I’m starting AutoUpgrade in -load_password mode. This is the first time I use it, so AutoUpgrade prompts for a password that it can use to encrypt the keystore:
$ java -jar autoupgrade.jar -config UPGR.cfg -load_password
Processing config file ...

Starting AutoUpgrade Password Loader - Type help for available options
Creating new AutoUpgrade keystore - Password required
Enter password:
Enter password again:
AutoUpgrade keystore was successfully created
  1. To enter the username and password for my database, I change the group to PWD:’
PWD> group PWD
Group [PWD] is already active
  1. Then, I can enter my username and password for my database (CDB19). AutoUpgrade validates the credentials, so you better make sure they work at this point:
PWD> add CDB19 -user SYS
Enter your secret/Password:
Re-enter your secret/Password:
Database SID: cdb19              User: SYS
  1. I save the keystore and convert it into an auto-login keystore, so I don’t have to enter the keystore password every time I use AutoUpgrade:
PWD> save
Convert the AutoUpgrade keystore to auto-login [YES|NO] ? yes
  1. Exit the keystore.
PWD> exit

AutoUpgrade Password Loader finished - Exiting AutoUpgrade
  1. Finally, I start the upgrade by starting AutoUpgrade in deploy mode:
java -jar autoupgrade.jar -config UPGR.cfg -mode deploy

That’s it!

Now, I can upgrade my Oracle Database without relying on native operating system authentication, using username and password instead.

SQLNET.ORA

Just a few words about sqlnet.ora and how to set up native operating system authentication.

  • Your operating system user (for instance, oracle) must be added to the appropriate groups in your operating system.
  • On UNIX/Linux, you allow native operating system authentication by authentication_services=beq in sqlnet.ora, or you can omit the parameter completely.
  • On Windows, you allow native operating system authentication by authentication_services=nts in sqlnet.ora. If you omit the parameter, then it won’t work.

Happy Upgrading!