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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.