Skip to main content
Delphix

Oracle SYSDBA privilege faults and potential causes

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 Delphixos.
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 Delphixos
[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 - Delphixos
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(Delphixos)
 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 = [Delphixos] ? db12c
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID Delphixos.
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> 

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.

  • Was this article helpful?