Search this Blog

Thursday, September 26, 2019

ORA-01017 on CONNECT / AS SYSDBA after changing OS user/group

From an existing database server the database Instance name and the OS-user and -group running the database had to change, as the database itself went from Development environment to Test environment.
The database was called "STID01", its OS-user "stid01" with the group "dbad01"

Everything was running just fine before the change.
It was intended to change the database name to "STIT01" and its OS-user to "stit01", with the OS-group set to "dbat01".
So a very simple change.

We brought down the database and the UNIX admins changed the OS-username, its OS-group and renamed file system mount points also, as they also contain the Dev environment name.

When I got the machine back, I ran a command to find all files on the system that belonged to the old user and changed their ownership to the new username and group.
After that I relinked the Oracle binaries, using the "$ORACLE_HOME/bin/relink all" command.

Finally I updated the /etc/oratab, renamed and edited the initSTID01.ora file to match the new configuration and I also created a new password file.

Now trying to connect to the database, it generated an ORA-01017 error on connect as "/ as sysdba":

[stit01@vtl1g152 oraInventory]$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Sep 26 14:50:57 2019

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

ORA-01017: invalid username/password; logon denied

Connecting as user SYS with its password, entered in the password file did work.
So I started working on renaming the Instance and datafile locations using a generated control file script:

SQL>  alter database backup controlfile to trace as '/tmp/crctrlfile.sql';

Afer renaming the database using the generated script the database was opened with the RESETLOGS option.

Now I updated /etc/oratab, /etc/oraInst.loc and the .bash_profile file and reconnected.

Connecting with "/ as sysdba" still generated the same  ORA-01017 however

Remembering a similar issue on IBM AIX, I went browsing the Internet.
Looking up things on Google, the main line of thinking in the results was based on the change of the OS-group. The user running the database, or wanting to connect as sysdba without a password should be member of a privileged dba-group set during the installation.

During the installation of this ORACLE_HOME this group was understandably set to "dbad01".
Now that we are part of group "dbat01" this OS authentication fails.

This brought me into looking into the installation and setup of the ORACLE_HOME.
From ORACLE_HOME I ran a "find" command for all files containing the OS-group "dbad01".
Two files came up as suspicious:


Especially the first one got my attention as a line in this file looked like:

Changing this file, setting the VALUE to "dbat01", I ran a "relink all" again.
Still no luck, again an ORA-01017 when trying to connect.

So, back to editing the next file.
Editing the file $ORACLE_HOME/rdbms/lib/config.c showed a whole list of variables set to "dbad01". Looking at the location, this file is used for recompiling the library files.

So we "relink all" again....and.... :

 sqlplus / as sysdba

SQL*Plus: Release Production on Thu Sep 26 16:08:01 2019

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

Connected to an idle instance.


Yes, we fixed it. This confirms that the OS-group is the variable that controls password less authentication at SYSDBA level.

Now this post reads more or less like a "try or die" post, but in fact it wasn't.
All changes before I went looking for the group-definition in the ORACLE_HOME are basic, well understandable changes that have to be done, when the OS-user and OS-group change on an Oracle database server

For the remainder, I had seen and done this before on IBM AIX, but not yet on Linux. 
The Post is therefore written following me searching for the right file.

No comments:

Post a Comment