1

I've researched the web but I could not found exactly the same situation than mine so I'm posting this message.

I'm connecting via Remote Desktop Connection to a remote server (Win2k3 Server). This server has installed (select * from v$version):

  • Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
  • PL/SQL Release 10.2.0.5.0 - Production
  • CORE 10.2.0.5.0 Production
  • TNS for 32-bit Windows: Version 10.2.0.5.0 - Production
  • NLSRTL Version 10.2.0.5.0 - Production

Running both of these sentences returns error:

c:\> sqlplus
SQL> conn / as sysdba
Insufficient privileges
SQL> connect system/****
Account locked

The remote computer is where the db is installed, so I'm not connecting to a remote db. The windows user account belongs to Administrator and Ora_DBA Windows groups.

Db has only one user available (the one used by the application we develope), but it hasn't enough privileges to alter SYSTEM account and unlock it.

I have no idea what else can I do, because I have no way to connect to de db as an administrator. I hope I have provided enough data for my question, if I missed anything please ask.

EDIT: I've checked again Windows user membership: Administrators, ora_dba, Users and "Remote Workplace Users" (hope I've translated last one right). PWD%ORACLE_SID%.ora file does not exist (suppose this is right).

EDIT2: I'm connecting to local db. I've found 3 installed SQLPLUS: one from Oracle Forms 6i (yeah, I know it's really old version), another one from Oracle 10 client, and the last one from Oracle 10 DB. I did not know about looking for ORACLE_HOME in HKLM/SOFTWARE/ORACLE, as I've read in a comment. In registry, all entries I've found point to Oracle Forms folder. I tried running sqlplus with full path, the sqlplus belonging to DB installation, and finally I've connected conn / as sysdba and I have run unlock account sentence, so problem is solved as my goal was to unlock system account.

  • Normally there is also a sys user available. Do you know that password? – Marco Jan 16 '18 at 12:28
  • 1
    @Marco, using as sysdba is equivalent to user SYS – Wernfried Domscheit Jan 16 '18 at 14:17
  • @Marco the command "connect sys as sysdba" ends with Insufficient Privileges error, and introducing sys as username when prompted from sqlplus (not in one command line, but running sqlplus command alone) it gives an error and prompts me for another username. – EAmez Jan 16 '18 at 15:32
4

The windows user account belongs to Administrator and Ora_DBA Windows groups.

Double check this, because you get the above error, when you are not a member of the ora_dba group.

Check %ORACLE_HOME%/network/admin/sqlnet.ora for the following line:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

If it is set to NONE, that disables logging in with / as sysdba, but would result in a ORA-01017: invalid username/password; logon denied error.

Finally, SYS password is stored outside the database, in a file, on Windows it is %ORACLE_HOME%/database/PWD%ORACLE_SID%.ora. You can recreate it (give new SYS password) as:

orapwd file=%ORACLE_HOME%/database/PWD%ORACLE_SID%.ora force=y

After that, you can should be able to log in with the given password as:

connect sys/password as sysdba

  • ORACLE_HOME is not defined and I'm not sure where should it point out because I see there's installed Oracle DB and Oracle client. – EAmez Jan 16 '18 at 15:38
  • Following your directions I've realised that I have things in too many places. In the server there's sqlplus80 installed (we have a legacy app in Oracle Forms 6i). Also there's the DB folder (c:\oracle\product\10.2.0\db_1) and the Client folder (c:\oracle\product\10.2.0\db_1). Using sqlplus from "db_1" folder I can connect as SYSDBA, and now system user account is unlocked. I think other versions of sqlplus are not reliable. I'll review them but I think problem is solved. Thanks. – EAmez Jan 16 '18 at 16:01
  • 2
    It's not that "other versions of sqlplus are not reliable" but not knowing "who's on first" when it comes to locating the relevant config files. I'm not sure how you determined that ORACLE_HOME is not defined. In windows you have to check the registry (HKLM\SOFTWARE\ORACLE) as well as the system settings. You should also note (fwiw) that Forms 6i and db 10.2 are both long out of support. – EdStevens Jan 16 '18 at 22:47
  • I've written "Client folder" wrong: it was meatn to be c:\oracle\product\10.2.0\client_1. I was looking for ORACLE_HOME in system and local variables. Under HKLM\SOFTWARE\ORACLE I see several folders that contain ORACLE_HOME or DEFAULT_HOME defined, and the ones I've checked point to ...c:\orant. I'll take this in mind for the future, I didn't know about this. Also... yes I know both versions are out of support, but there's nothing I can do about this. It's quite a problem because I know almost nothig about forms 6i and I can't do anything to evolve to another tech or version. – EAmez Jan 18 '18 at 15:04
0
  • OS : Linux
  • Oracle : Version 11.2.0.1.0 - Production
  • Error :

    SQL> connect sys as sysdba;
    Enter password:
    ERROR:
    ORA-01031: insufficient privileges
    

And I used the instructions of balazs-papp but being a Linux environment the command syntax is altered, but the process is the same:

cd  $ORACLE_HOME/dbs
ls -la
ls -lrt *orapw*
orapwd file=orapwEE force=y

It works !!!

0

Change parameter REMOTE_LOGIN_PASSWORDFILE=exclusive using pfile or spfile. create again spfile which contains above entry shutdown oracle using windows services execute orapwd file=%ORACLE_HOME%/database/PWD%ORACLE_SID%.ora force=y using CMD.. restart your sql session and login /as sysdba and restart db.

I think your REMOTE_LOGIN_PASSWORDFILE=none or doesn't set properly. Due to this reason you are hitting this issue.

  • 1
    remote_login_passwordfile is used for remote logins. OP was trying to log in locally. – Balazs Papp Jan 18 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.