Troubleshooting of SQL plus logon failures caused by su switching in Oracle

Troubleshooting of SQL plus logon failures caused by su switching in Oracle

Problem description:
The Oracle database in the production environment suddenly fails to log on, and the oracle database in the rlwrap production environment suddenly fails to log on. rlwrap sqlplus "/as sysdba" reports the following error: rlwrap
[Oracle @ localhost root] $ rlwrap sqlplus "/as sysdba"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.
Rlwrap: Cannot execute sqlplus: Permission denied
[Oracle @ localhost root] $ sqlplus "/as sysdba"
Bash: sqlplus: command not found
[Oracle @ localhost root] $

1. oracle logon Error
[Oracle @ localhost root] $ rlwrap sqlplus "/as sysdba"
Error 6 initializing SQL * Plus
SP2-0667: Message file sp1 <lang>. msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[Oracle @ localhost root] $
[Oracle @ localhost root] $

2. Check the ORACLE_HOME variable, which is null.
[Oracle @ localhost root] $ echo $ ORACLE_HOME

[Oracle @ localhost root] $

3. Set the ORACLE_HOME variable in/etc/profile.
[Root @ localhost ~] # Vim/etc/profile
Export ORACLE_HOME =/opt/oracle/app/oracle/product/11.2.0/dbhome_1

[Root @ localhost ~] # Source/etc/profile
[Root @ localhost ~] # Su oracle
[Oracle @ localhost root] $
[Oracle @ localhost root] $ echo $ ORACLE_HOME
/Oracle/app/oracle/product/11.2.0/dbhome_1/
[Oracle @ localhost root] $

4. the ORACLE_HOME environment variable has a value. Log On With sqlplus and try again.
[Oracle @ localhost root] $ rlwrap sqlplus "/as sysdba"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.
Rlwrap: Cannot execute sqlplus: Permission denied
[Oracle @ localhost root] $

5. Perform a soft connection to sqlplus.
[Oracle @ localhost root] $ exit
Exit
[Root @ localhost ~] # Ln-s $ ORACLE_HOME/bin/sqlplus/usr/bin
[Root @ localhost ~] # Su oracle
[Oracle @ localhost root] $ sqlplus "/as sysdba"

SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 16 10:19:39 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

ERROR:
ORA-12162: TNS: net service name is incorrectly specified

Enter user-name:
ERROR:
ORA-12162: TNS: net service name is incorrectly specified

Enter user-name:
ERROR:
ORA-12162: TNS: net service name is incorrectly specified

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL * Plus
[Oracle @ localhost root] $
[Oracle @ localhost root] $ echo $ ORACLE_SID

[Oracle @ localhost root] $
There is no ORACLE_SID in the system environment variable.

6. Set ORACLE_SID
[Oracle @ localhost root] $ exit
Exit
[Root @ localhost ~] # Vim/etc/profile
Export ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1/
Export ORACLE_SID = pxxerxxs
[Root @ localhost ~] # Source/etc/profile

[Oracle @ localhost root] $ exit
Exit
[Root @ localhost ~] # Vim/etc/profile
[Root @ localhost ~] # Source/etc/profile
[Root @ localhost ~] # Su oracle
[Oracle @ localhost root] $ rlwrap sqlplus "/as sysdba"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.
SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 16 10:25:33 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected to an idle instance.

SQL>
SQL>
SQL>

7. Go to oracle user to view system variables
[Oracle @ localhost root] $ cat ~ /. Bash_profile
#. Bash_profile

# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

# Added for oracle
Export ORACLE_BASE =/oracle/app/oracle
Export ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1
Export ORACLE_SID = pxxerxxs
Export TNS_ADMIN = $ ORACLE_HOME/network/admin

Export PATH = $ PATH: $ ORACLE_HOME/bin
[Oracle @ localhost root] $
There is a value in the configuration. Where is the problem?

8. There are differences in su
If you do not add-, you just need to switch the user and do not replace the user's configuration. After adding-, you can try sqlplus again.
[Oracle @ localhost ~] $ Exit
Logout
[Root @ localhost ~] # Su oracle
[Oracle @ localhost root] $ echo $ ORACLE_BASE

[Oracle @ localhost root] $ exit
Exit
[Root @ localhost ~] # Su-oracle
[Oracle @ localhost ~] $ Echo $ ORACLE_BASE
/Oracle/app/oracle
[Oracle @ localhost ~] $

9. Verify the su-oracle
[Oracle @ localhost ~] $ Rlwrap sqlplus "/as sysdba"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.

SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 16 10:36:51 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>

10. OK. An error is returned when you try to log on with a non-sys account.
[Oracle @ localhost ~] $ Rlwrap sqlplus "plas_prd/plrd_1628 @ pxxerxxs as sysdba"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.

SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 16 16:53:11 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

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

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

Enter user-name:
[Oracle @ localhost ~] $

11. check whether tnsping pxxerxxs is successful.
[Oracle @ localhost ~] $ Tnsping pxxerxxs

TNS Ping Utility for Linux: Version 11.2.0.1.0-Production on 16-JAN-2015 16:53:27

Copyright (c) 1997,200 9, Oracle. All rights reserved.

Used parameter files:
/Oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet. ora

TNS-03505: failure to resolve name
[Oracle @ localhost ~] $
Tnsping failed. The error message "pxxerxxs" is invalid.

12. Go to tnsnames. ora.
[Oracle @ localhost admin] $ more/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames. ora
You can also more $ ORACLE_HOME/network/admin/tnsnames. ora
Find
MPMD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.110.107) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = pxxerxxs)
)
)

Log on with a non-sys user
[Oracle @ localhost ~] $ Rlwrap sqlplus "pxxerxxsk/pa141215 @ MPMD2"
Rlwrap: warning: your $ TERM is 'xtermin' but rlwrap couldn't find it in the terminfo database. Specific CT some problems.

SQL * Plus: Release 11.2.0.1.0 Production on Fri Jan 16 20:30:30 2015

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>

OK. The logon is successful.

Conclusion,
(1) in linux, when su is switched to an oracle user, the addition of "-" will change the configuration of the oracle user separately. If "-" is not added, the oracle user's separate configuration will not be used.
(2) When using sqlplus to log on, use a non-sys user to log on. @ is followed by $ ORACLE_HOME/network/admin/tnsnames. the prefix MPMD2 defined by ora, instead of oracle_sid, tnsnames. the MPMD2 prefix definition in ora is as follows:
MPMD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.110.101) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = pxxerxxs)
)
)

Oracle Study Notes: sqlplus User Logon

Sqlplus ORA-01017 when logging on to Oracle: invalid username/password; logon denied Error

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian