Skip to main content
Delphix

Oracle SYSDBA Privilege Faults and Potential Causes (KBA1259)

 

Issue

The Delphix Engine is reporting faults along the lines of "Environment user ... cannot log on to databases in Oracle home ... as SYSDBA."

Troubleshooting

Ensure the Delphix Environment user can login to the Target database and invoke the SYSDBA privilege.

o To check this set the Environment to that of the database in question:
 

export ORACLE_SID=<enter oracle sid>
export ORACLE_HOME=<enter path to oracle home>
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus / as sysdba


If the above succeeds, it means the Delphix OS User and Target node are configured correctly and according to the requirements documented in the following documentation:


Requirements for Oracle Target Hosts and Databases
Requirements for Oracle Source Hosts and Databases

If this does not succeed there are several scenarios that could be causing this issue:

Scenario 1- If the above does not succeed and the Oracle error "ORA-01017 occurs at the SYSDBA login attempt", then further investigation needs to be performed looking at OS level group membership.


o After setting the OS environment for the Delphix OS User "Delphix", you experience Oracle errors attempting to connect AS SYSDBA:
 

[root@oelc9n1 ~]# su - Delphix
-bash-4.2$ cat /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
-MGMTDB:/u01/app/clusterware/12.1:N        # line added by Agent
db12c:/u01/app/oracle/12.1:N                    # line added by plb
dbhome112:/u01/app/oracle/product/11.2.0/dbhome_1:N
+ASM1:/u01/app/clusterware/12.1:N        # line added by Agent
c9cdb1:/u01/app/oracle/12.1:N        # line added by Agent


bash-4.2$ . oraenv
ORACLE_SID = [Delphix] ? db12c
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID Delphix.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/12.1

bash-4.2$ echo $ORACLE_SID
db12c
-bash-4.2$ echo $ORACLE_HOME
/u01/app/oracle/12.1
-bash-4.2$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/app/oracle/12.1/bin


o You may see an ORA-01017 when attempting to connect as SYSDBA
 

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 29 11:29:57 2016

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

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


o Check of the groups shows that the user is not a member of the OSDBA group (in this case "dba"):

While the group name "dba" is typically used, it is not always the case and you may need to determine what the OSDBA group actually is.


o As the user is not a member of the ODSBA group, identify the groups currently allocated to the Delphix OS user:
 

bash-4.2$ id
uid=1102(Delphix) gid=1010(oinstall) groups=1010(oinstall),1020(asmadmin),1021(asmdba)


o Compare these to the Oracle Home owner:
 

[oracle@oelc9n1 ~]$ id
uid=1101(oracle) gid=1010(oinstall) groups=1010(oinstall),989(vboxsf),1021(asmdba),1031(dba)


From this comparison, we can see that there are differences between the Delphix OS User "Delphix", and the user "oracle" - who owns the home.

o Check which group is the DBA group from Oracle's perspective:

  • As the ORACLE_HOME owner, in this case the OS user "oracle" set the environment to the home in question
oracle@oelc9n1 ~]$ . oraenv
ORACLE_SID = [oracle] ? db12c
The Oracle base has been set to /u01/app/oracle


[oracle@oelc9n1 bin]$ cd $ORACLE_HOME/rdbms/lib
[oracle@oelc9n1 lib]$ ls config.c
config.c
[oracle@oelc9n1 lib]$ strings config.c | egrep DBA
/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
#define SS_DBA_GRP "dba"

The line #define SS_DBA_GRP "dba" tells us the group needs to be the OS group "dba". Adding the user to this group will correct this problem.

o To add the Delphix OS user to the DBA group

As root

[root@oelc9n1 ~]# usermod -g oinstall -G dba,asmdba,asmadmin Delphix
[root@oelc9n1 ~]# exit


o Once you have added the Delphix OS user to the proper group, you can confirm this error no longer appears by establishing a new session as the Delphix OS user, checking the group and attempt another login as SYSDBA:
 

-bash-4.2$ su - Delphix
Password: 
Last login: Tue Nov 29 11:28:03 AEDT 2016 on pts/2
Last failed login: Tue Nov 29 11:38:08 AEDT 2016 on pts/2
There was 1 failed login attempt since the last successful login.
-bash-4.2$ id
uid=1106(Delphix)
 gid=1010(oinstall) 
groups=1010(oinstall),1020(asmadmin),1021(asmdba),1031(dba) 
context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$ . oraenv
ORACLE_SID = [Delphix] ? db12c
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID Delphix.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/12.1
-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 29 11:39:54 2016

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

Connected to an idle instance.

SQL> exit
Disconnected

 

Scenario 2- If this does not succeed and the Oracle error "ORA-12547: TNS:lost contact" when you try and validate the SYSDBA privileges for the Delphix OS user


o You experience Oracle errors attempting to connect AS SYSDBA:
 

[Delphix@oelc9n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 29 11:43:39 2016

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

ERROR:
ORA-12547: TNS:lost contact


o After checking on the error,  it appears that the shadow process that would normally be spawned at login has died:
 

[Delphix@oelc9n1 ~]$ oerr ora 12547
12547, 00000, "TNS:lost contact"
// *Cause: Partner has unexpectedly gone away, usually during process
// startup.
// *Action:Investigate partner application for abnormal termination. On an
// Interchange, this can happen if the machine is overloaded.


o The first place to start with this error is the permission set on the Oracle binary:
 

[oracle@oelc9n1 bin]$ ls -l oracle
-rwxrwxr-x. 1 oracle asmadmin 323649832 Sep 28 09:25 oracle


o While the permissions might look like Delphix can execute the Oracle binary OK, they are NOT correct as Oracle requires the setuid and setgid bits be set on the Oracle binary/executable
 

This change will need to be performed as the root user

[root@oelc9n1 ~]# . oraenv
ORACLE_SID = [root] ? db12c
The Oracle base has been set to /u01/app/oracle
[root@oelc9n1 ~]# cd $ORACLE_HOME/bin
[root@oelc9n1 bin]# chmod 6751 oracle
[root@oelc9n1 bin]# ls -l oracle
-rwsr-s--x. 1 oracle asmadmin 323649832 Sep 28 09:25 oracle


o The permissions are now set correctly as -rwsr-s--x

o Once the permissions set correctly the Delphix OS user should be able to get passed the ORA-12547 error:

[Delphix@oelc9n1 lib64]$ ls -la $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 323649832 Sep 28 09:25 /u01/app/oracle/12.1/bin/oracle

[Delphix@oelc9n1 lib64]$ id
uid=1102(Delphix)
 gid=1010(oinstall) 
groups=1010(oinstall),1020(asmadmin),1021(asmdba),1031(dba) 
context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[Delphix@oelc9n1]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 30 14:23:05 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> 

Scenario 3 - Check to ensure the Delphix OS User can invoke sqlplus successfully through a non-interactive shell login.

o The environment may have ORACLE related environment variables set that are impacting Delphix's ability to execute sqlplus or RMAN in the non-interactive session.

On AIX these environment variables can be found in /etc/environment.

On Linux it may be in the users local .bashrc.

For example the following environment variables may be in place:

# User specific aliases and functions
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_SID=delphix_sid
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
LIBPATH=$ORACLE_HOME/lib:$LIBPATH
TWO_TASK=delphix_sid
export PATH ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH LIBPATH TWO_TASK

Please Note it is not necessary to set any of these for the Delphix OS User as Delphix will set up the environment when and as required.

o The following command should execute cleanly and not report an error when executed as the Delphix OS user on the target host where ORACLE environment variables are in place.

ssh localhost sqlplus / as sysdba

If there is an issue with the non-interactive logins execution of sqlplus, for example where the TWO_TASK environment variable was set, then the following error may be reported.

[delphix]$ ssh localhost sqlplus / as sysdba
delphix@localhost's password:

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 17 19:24:43 2019

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

ERROR:
ORA-12541: TNS:no listener

Removing TWO_TASK from /etc/environment (in the AIX scenario) resolves the issue. 

On linux there may be environment variables set in .bashrc for the user that are impacting the sqlplus login.

When TWO_TASK is set this way it impacts the non-interactive linux login resulting in the following error:

[delphix]$ ssh localhost sqlplus / as sysdba
delphix@localhost's password: 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 18 11:41:41 2019

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Removing TWO_TASK from the .bashrc will allow the sqlplus execution to complete successfully. 

[delphix@oel7si1 ~]$ ssh localhost sqlplus / as sysdba
delphix@localhost's password: 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 18 11:42:34 2019

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

Connected to an idle instance.
SQL> 

Additional Information

Requirements for Oracle Target Hosts and Databases
Requirements for Oracle Source Hosts and Databases

If the requirements in the documents above have been met and troubleshooting steps above do NOT correct the errors reported by Delphix regarding SYSDBA logins not being allowed, please log a case with Delphix Support.