Shulou(Shulou.com)06/01 Report--

Oracle installation, especially in Linux/Unix environments, is a challenge for every beginner. There are many operation steps, various configuration projects are complex and many details, as long as there is an error in one step, it may bring problems to the subsequent installation or operation. Sometimes some problems are more confusing, and it takes some experience and knowledge to solve them.

This article mainly introduces a problem caused by case inconsistency during SID installation and the solution strategy.

1. Environmental introduction

The author received feedback from colleagues that a database that had been installed failed to log in remotely using sys users and reported the wrong user name and password. But there is no problem logging in with other users.

Since it is a newly installed database, check the various environment variables and data first. The current background process is indeed running normally, and the environment variables are also normal.

[oracle@TEST-NE-TESTDB ~] $ps-ef | grep pmon

Oracle 13259 1 0 Jul13? 00:00:07 ora_pmon_TESTDB

Oracle 16439 16414 0 08:31 pts/1 00:00:00 grep pmon

[oracle@TEST-NE-TESTDB ~] $env | grep ORA

ORACLE_SID=TESTDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

In the author's previous article, a process was recommended for connection failures, which is progressively progressive in the order of "from near to far". To put it simply: first look at the internal connection of the server, and then from the listener, the network to the specific connection client.

There is no problem with anonymous sys login on the server, and so is the average user (take scott as an example).

SQL > conn / as sysdba

Connected.

SQL > conn scott/tiger

Connected.

SQL > select open_mode from v$database

OPEN_MODE

-

READ WRITE

SQL > show parameter service

NAME TYPE VALUE

-

Service_names string testdb

The state of the listener is also normal.

[oracle@TEST-NE-TESTDB ~] $lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0-Production on 14-JUL-2017 08:35:54

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC1521)

STATUS of the LISTENER

-

(for reasons of space, there are omissions. )

Services Summary...

Service "TESTDB" has 1 instance (s).

Instance "TESTDB", status READY, has 1 handler (s) for this service...

Service "testdbXDB" has 1 instance (s).

Instance "TESTDB", status READY, has 1 handler (s) for this service...

The command completed successfully

[oracle@TEST-NE-TESTDB ~] $

However, when using pl/sql developer or sqlplus to connect remotely, the sys account is not successful, and the scott account is successful.

2. Problem analysis.

The point of failure of this problem should not be at the client-to-server network level, because after all, there are users who may log in. And one suspicious point is the error message: when sys connects, the error message is not a failure to connect, but an error in the user name and password.

So what's the effect of pushing back to the server side and using a listener to connect on the server side?

[oracle@TEST-NE-TESTDB admin] $sqlplus / nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 14 08:45:06 2017

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

SQL > conn sys/oracle@testdb as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

SQL > conn scott/tiger@testdb

Connected.

SQL >

The error prompt is the same, indicating that a validation error was caused after passing the listener. So, what's the difference for Oracle with or without a listener? After the listener, regardless of whether it is a server local client or not, Oracle will be treated as a "remote connection" for remote connection processing.

So, what's the difference between sys and scott users? Is the password file Password File. The normal user authentication information is inside the database, while the one with sysdba permission is in a separate password file. So I guess there's something wrong with the password file.

However, in the $ORACLE_HOME/dbs directory, there are suspected parameter files and password files.

[oracle@TEST-NE-TESTDB admin] $cd $ORACLE_HOME/dbs

[oracle@TEST-NE-TESTDB dbs] $ls-l

Total 32

-rw-rw----. 1 oracle oinstall 1544 Jul 13 20:25 hc_testdb.dat

-rw-rw----. 1 oracle oinstall 1544 Jul 13 20:27 hc_TESTDB.dat

-rw-rw----. 1 oracle oinstall 1544 Jul 13 18:28 hc_TESTNETESTDB.dat

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r-. 1 oracle oinstall 2112 Jul 13 20:15 initTESTDB.ora

-rw-r-. 1 oracle oinstall 24 Jul 13 20:27 lkTESTDB

-rw-r-. 1 oracle oinstall 1536 Jul 13 18:32 orapwtestdb

-rw-r-. 1 oracle oinstall 3584 Jul 13 19:16 spfiletestdb.ora

However, it looks strange, especially parameter files. The current parameter file finds that SPfile is not used at all, but is started directly with Pfile.

SQL > conn / as sysdba

Connected.

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string

At this point, the author can guess that the reason lies in case. During the colleague's installation of the database, the environment variables do not match the case of the sid information entered on the installation file. The uppercase TESTDB is entered in the environment variable, while the Oracle instance generates the instance file corresponding to lowercase testdb.

3. Solve the problem

Once we understand the cause of the problem, we will have a direction to solve it. This problem should be solved from two aspects: parameter file and password file. The first step is to rebuild the parameter file.

SQL > create spfile from pfile

File created.

SQL > quit

A new uppercase spfileTESTDB.ora file is generated in the corresponding directory.

[oracle@TEST-NE-TESTDB dbs] $ls-l

Total 36

-rw-rw----. 1 oracle oinstall 1544 Jul 13 20:25 hc_testdb.dat

(for reasons of space, there are omissions. )

-rw-r-. 1 oracle oinstall 1536 Jul 13 18:32 orapwtestdb

-rw-r-. 1 oracle oinstall 3584 Jul 13 19:16 spfiletestdb.ora

-rw-r-. 1 oracle oinstall 2560 Jul 14 08:48 spfileTESTDB.ora

Password file reconstruction.

[oracle@TEST-NE-TESTDB dbs] $orapwd file=orapwTESTDB password=oracle entries=5 force=Y

[oracle@TEST-NE-TESTDB dbs] $ls-l | grep orapw

-rw-r-. 1 oracle oinstall 1536 Jul 13 18:32 orapwtestdb

-rw-r-. 1 oracle oinstall 2048 Jul 14 08:56 orapwTESTDB

Restart the database.

SQL > conn / as sysdba

Connected.

SQL > startup force

ORACLE instance started.

Total System Global Area 4993982464 bytes

Fixed Size 2261808 bytes

Variable Size 1073745104 bytes

Database Buffers 3909091328 bytes

Redo Buffers 8884224 bytes

Database mounted.

Database opened.

The retest fault disappears.

SQL > select * from v$pwfile_users

USERNAME SYSDB SYSOP SYSAS

SYS TRUE TRUE FALSE

SQL > conn sys/oracle@testdb as sysdba

Connected.

SQL > show user

USER is "SYS"

4. Conclusion

Note: in fact, there is another way to deal with this case, which is to modify the environment variable ORACLE_SID along with the lowercase characteristics of the example, so that when you restart the database, the original environment files can play a role.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.