0

I ran below command and restarted docker container.

alter system set processes = 1 scope = spfile;

after this I am not able to login to DB at all.

root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020

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

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> 

I tried to resolve it with below command, tried login again, still getting same error

orapwd file=filename password=password entries=100

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file and manually assign processes=100 and back to processes=1 this didnt help.

can someone guide here please?

3
  • 1
    root user is not meant to do that. Use oracle user. Aug 5 '20 at 15:39
  • In spite of all the clear text, the spfile is a binary file. Manually editing it with a text editor will corrupt it.
    – EdStevens
    Aug 5 '20 at 16:56
  • Have you tried what's been suggested? It's starting to appear that you have abandoned the thread . . .
    – EdStevens
    Aug 6 '20 at 15:57
1

Based on your comments, I have to assume that you are new to Oracle Database.

alter system set processes = 1 ...

It takes lots of processes to run an Oracle Database.
I very rarely hear of anyone reducing this parameter - usually we're having to ramp it up to cope with increasing workload.

orapwd file=filename password=password entries=100

The Oracle Password File controls who can log into the database using Operating System authentication rather than a local, database password.
It should have as few entries in it as possible (I don't think I've ever exceeded five) and then only those that *administer" the database and are trusted to know what they're doing.

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file

No, no, no. Just ... No.

You should consider most, if not all, of the files used / managed by Oracle Database to be in a proprietary format that you absolutely must not try to "edit".

This is Oracle database, not Microsoft's "ODBC Text" Driver.

The PFile is one of the few a text files that you can edit but their use is exceptional these days - most database instances run from an SPFile - intended for situations like this where you have to "fix" things on the fly.

2
  • W - "The Oracle Password File controls who can log into the database using Operating System authentication rather than a local, database password." Don't you have that backward? The password file controls logging on as SYS when using a password - hence the name 'password file'. Local OS authentication is password-less and is controlled by who is a member of the OS group 'dba'.
    – EdStevens
    Aug 6 '20 at 15:46
  • @EdStevens: D'Oh! You're absolutely right. Thanks for the Catch.
    – Phill W.
    Aug 7 '20 at 10:35
0

You will need to restart your database from a custom pfile:

  1. copy the spfile to 'mypfile.ora'

  2. edit 'mypfile.ora' to delete all of the binary gobbeldygook, and correct your 'processes' parameter. (why were you messing with it in the first place?)

  3. stop the database. Since you cannot connect, due to the undersized processes parm, you will not be able to issue a shut down. You'll have to use the os 'kill' command to kill the pmon process, and wait for the rest of it to stop.

  4. start the database with your pfile:

    sqlplus / as sysdba SQL> startup pfile='mypfile.ora';

  5. create a new spfile

    SQL> create spfile from pfile='mypfile.ora'

  6. restart with the spfile

-- Edit -- And as mentioned by Balasz Papp, don't do any of that as root. Do it as the owner of the oracle software, typically 'oracle'.

-- Edit 2 -- Thinking some more, it would seem that if you have corrupted your spfile, then your database isn't even started at all. Perhaps you don't need to kill it as I described above, but simply connect to the idle instance -- 'sqlplus /nolog'.

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.