Oracle SYSDBA Privilege Faults and Potential Causes (KBA1259)

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

KBA

KBA#1259

Issue

The Delphix Engine is reporting faults like those in the following examples:

Environment user ... cannot log on to databases in Oracle home ... as SYSDBA.

Connection to database "CDOMLOSRC1D6PDB1" with OS credentials failed.

Remote environment validation failed for environment

Troubleshooting

Ensure the Delphix Environment user can log in to the Target database and invoke theSYSDBA privilege.

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 Hosts and Databases

If this does not succeed there are several scenarios that could be causing this issue that are addressed below.

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.

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

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

Check if 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.
If 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)

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.

Check which group is the DBA group for Oracle:
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.

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

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

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

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.

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

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

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

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.

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

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>

Scenario 4

Oracle sets the ORACLE_BASE environment variable using an entry held in $ORACLE_HOME/install/orabasetab. If for some reason this points to a path that is incorrect, does not exist or is not accessbile then it will impact setting the environment for running sqlplus. Utilities like oraenv utilise the orabase command and the orabasetab file. The orabasetab file is normally set during the install of the ORACLE_HOME and is provided by the DBA to utilities like the Oracle Universal Installer (OUI).

Executing $ORACLE_HOME/bin/orabase and examining the ORACLE_BASE environment variable afterwards allows you to determine what Oracle is setting. This needs to be compared to the the actual required path for this host or cluster. If it is not correct then orabasetab will need to be altered each of the affected homes and cluster nodes (if the environment is an Oracle cluster).

As reference please see MOS Document "orabase command returns no value instead of ORACLE_BASE value (Doc ID 2225573.1).

The following is an example of ORA-12547 appearing as a result of ORACLE_BASE being returned incorrectly. This usually occurs if the environment/ORACLE_HOME is cloned and has not been installed using regular Oracle tools.

After setting up the environment using oraenv, ORACLE_BASE is being set by the orabase binary.

[oracle@oelc5n1 ~]$ $ORACLE_HOME/bin/orabase
/u01/app/oracle2
[oracle@oelc5n1 ~]$ . oraenv
ORACLE_SID = [cdb2] ? 
The Oracle base remains unchanged with value /u01/app/oracle2

In this scenario ORACLE_BASE is set to /u01/app/oracle2.

[oracle@oelc5n1 ~]$ env | grep ORACLE_BASE
ORACLE_BASE=/u01/app/oracle2

Attempting to run sqlplus with an invalid ORACLE_BASE setting throws the error ORA-12547.

[oracle@oelc5n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 17 18:01:44 2021
Version 19.3.0.0.0

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

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: ^C

This path in this case is not accessible as it does not even exist.

[oracle@oelc5n1 ~]$ ls -l /u01/app/oracle2
ls: cannot access /u01/app/oracle2: No such file or directory

The orabasetab file that holds this location is incorrect.

[oracle@oelc5n1 ~]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle/19.3:/u01/app/oracle2:OraDB19Home1:N:

To correct the ORA-12457 error alter the file and set the true ORACLE_BASE path for this host/cluster.

[oracle@oelc5n1 ~]$ cat $ORACLE_HOME/install/orabasetab
#orabasetab file is used to track Oracle Home associated with Oracle Base
/u01/app/oracle/19.3:/u01/app/oracle:OraDB19Home1:N:

The orabase command now sets the correct path.

[oracle@oelc5n1 ~]$ $ORACLE_HOME/bin/orabase
/u01/app/oracle

The environment still has the old value and needs to be reset.

[oracle@oelc5n1 ~]$ set | grep ORACLE_BASE
OLD_ORACLE_BASE=/u01/app/oracle2
ORACLE_BASE=/u01/app/oracle2

Running oraenv again will configure ORACLE_BASE with the new value.

[oracle@oelc5n1 ~]$ . oraenv
ORACLE_SID = [cdb2] ? 
The Oracle base has been changed from /u01/app/oracle2 to /u01/app/oracle

ORACLE_BASE is now set correctly.

[oracle@oelc5n1 ~]$ set | grep ORACLE_BASE
OLD_ORACLE_BASE=/u01/app/oracle2
ORACLE_BASE=/u01/app/oracle

SQLPlus now functions normally.

[oracle@oelc5n1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 17 18:02:45 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Requirements for Oracle Target Hosts and Databases
Requirements for Oracle Source Hosts and Databases
For additional information on troubleshooting the ORA-12547 error, see the following Oracle Knowledge Articles and blog posts below:
Oracle Document: Troubleshooting ORA-12547 TNS: Lost Contact (Doc ID 555565.1)
Oracle Document: Local SQL*Plus Connection and DBCA Fails With: ORA-12547: TNS:Lost Contact (Doc ID 422173.1)
Oracle Document: ORA-12547: TNS:lost Contact (Doc ID 2317235.1)
ORA-12547: TNS: lost contact error in Oracle Linux 7.x

KBA