Oracle SYSDBA Privilege Faults and Potential Causes (KBA1259)
KBA
KBA#1259Issue
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 connectAS 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 theOSDBA
group actually is.
If the user is not a member of theODSBA
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>
Related Articles
- 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