Coskan’s Approach to Oracle

April 27, 2007

ORA-01031: insufficient privileges

Filed under: Tips — coskan @ 8:23 am

When you set SQLNET.AUTHENTICATION_SERVICES to NONE on SQLNET.ora file on windows you will get ORA-01031 when you try to connect with the strings belowfor SQL*Plus

C:\Documents and Settings\cgundogar>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:47:42 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:48:00 2007

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

idle> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


idle> connect “/ as sysdba”
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied –password is not accepted

Warning: You are no longer connected to ORACLE.

for RMAN

C:\Documents and Settings\cgundogar>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:12 2007

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

RMAN> connect target /RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN>

To solve this problem you can must connectby giving valid username username and password

for SQL*Plus

sys@XE> connect sys as sysdba
Enter password:
Connected.
sys@XE> connect sys/passwd as sysdba
Connected.
sys@XE> connect sys@passwd as sysdba
Enter password:
Connected.
sys@ORACOS> connect sys/passwd@oracos as sysdba –over listenerConnected.
Connected.


C:\Documents and Settings\cgundogar>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:08:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter password:Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

sys@XE>exit


C:\Documents and Settings\cgundogar>sqlplus sys/passwd as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:09 2007

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

Connected to:Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production


C:\Documents and Settings\cgundogar>sqlplus sys/passwd@oracos as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:15 2007

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

Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
sys@ORACOS> exit

for RMAN

C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target sys

target database Password:
connected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

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

RMAN> connect target sys/passwdconnected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:28 2007

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

target database Password:

connected to target database: XE (DBID=2496353564)RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys/passwd

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:38 2007

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

connected to target database: XE (DBID=2496353564)

Edit (03/07/2009) : Check also
Metalink Note ID:730067.1
Metalink Note ID:114384.1

About these ads

30 Comments »

  1. Hi

    very nice look to the blog. You will only be able to log on as sysdba with a password if you have set one up – using the orapwd utility. no passwordfile, no non os authenticated logons – which on windows means nts.

    Comment by Niall Litchfield — May 29, 2007 @ 2:46 pm

  2. Hi,

    When I do connect target sys/passwd

    I type any password, like passpwd, like senha, like anything.

    Why can I put any password for user sys?

    Comment by Tadeu — June 21, 2007 @ 6:57 pm

  3. The password file needs to be created using orapwd with the file name as ‘orapw$ORACLE_SID’.

    we need to export the oracle_sid, down the database, create the password file and bring up the database.

    once db is up, check the v$pwfile_users. For ‘SYS’ user, the value should be TRUE, TRUE.

    just say ‘rman target sys/xxx@targetsid’
    you should be able to login.

    I noticed, that even if the file name has lower case oracle_sid in the ORAPW file, the database wouldn’t pick up the value.
    HTH

    Comment by freebirds — August 28, 2007 @ 2:16 am

  4. Hi,

    It helped me a lot…
    thanks a lot…

    Comment by Ritesh — February 11, 2008 @ 2:12 pm

  5. I have changed the sqlnet.ora and removed ‘none’ and I was able to connect. However, I get another error “ora-01506: missing or illegal database name” when shutdown and startup the database.

    How can I sort this one out.

    Thanks in advance

    Comment by Munyendo — August 25, 2008 @ 12:49 pm

  6. Hi
    my ARCHIVELOG Mode in Configure Recovery Settings
    is Disable Please Help Me.

    Comment by shaho — September 25, 2008 @ 8:38 am

  7. Tamam da, ben OS autentication yapmak istiyorum!
    sen bana yapma demisin! bu cozum degildir ki!
    seni Oguz abiye sikayet edecem! :))

    Comment by Bekir Birden — December 17, 2008 @ 8:50 am

  8. Thanks for the solution, it solved my problem!!!!

    Comment by cepguill — January 21, 2009 @ 10:00 pm

  9. Just a heads up for everybody:

    There is a bug in SQLNET.AUTHENTICATION_SERVICES=NTS on Windows.

    If the machine name and the user name are the same, then “sqlplus / as sysdba” will fail with ORA-12638 “credential retrieval failed” even if you’ve done all the other setup correctly (i.e., the user is a member of the local “ora_dba” group).

    As my network guy explains it, when Oracle is querying Active Directory to authenticate the user, apparently it is not setting the appropriate flag to select just user accounts. Since the machine account is returned first(?) by Active Directory, the authentication fails.

    Workaround is to change either the machine name or the user name. This is now recognized as an Oracle “feature” (Tech Note 782419.1). Probably won’t be fixed until at least 11.2.

    Comment by Richard Holmes — May 1, 2009 @ 6:14 pm

  10. Thanks a lot Team..

    this really helped me sort out my issue..

    Thanks for ur valuable support

    Comment by Abdul Kalam Asath — June 29, 2009 @ 10:02 am

  11. I thought I know, how to deal with orapwd, did it many times in different environment, but now on Linux I can’t get over ORA-01031: insufficient privileges, when login as sysdba.

    I work on Oracle 10.2.0.1 on Linux.
    Ican login local without password, but I can’t login remotely as sysdba.

    I shutdown instance, recreate passwd liek this:

    $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwkcub.ora password=’password’ force=y

    – start instance
    – sqlplus ‘sys@KCUB as sysdba’

    Enter password:
    ERROR:
    ORA-01031: insufficient privileges

    could have something to do with group for user ‘oracle’ ? he is memebr of oinstall as first group and dba as 2nd group !

    thanks for any tip.

    Comment by laode — July 3, 2009 @ 3:59 pm

  12. Hi Laode

    What is SQLNET.AUTHENTICATION_SERVICES parameter setting ?

    It needs to be ALL

    Comment by coskan — July 3, 2009 @ 4:15 pm

    • Hi coskan,

      thanks for the tip, it looks like that the problem is in deed somewhere in the sql*net settings.

      before I posted my 1st question, there was no sqlnet.ora at all, so I created on with only this entry:

      NAMES.DIRECTORY_PATH=(TNSNAMES)

      Now I added your hint:

      SQLNET.AUTHENTICATION_SERVICES=(ALL)

      and I get a different error:

      ORA-12641: Authentication service failed to initialize

      I intend to setup only a basic instance to become able to run other Oracle-gui-tools from windows-PC, but this requires remote login as sysdba.

      laode

      Comment by laode — July 3, 2009 @ 6:49 pm

  13. i am creating an oracle DB manually on a Windows XP SP2 machine.
    I have created the required SID(service) using, oradim command. I have also created the initialization parameter file in the default location.
    $sqlplus /nolog
    SQL>startup nomount;
    ORA-01031: insufficient privileges

    I am getting this error :( . Please help

    Comment by nagashree — November 19, 2010 @ 11:11 am

    • sqlplus /nolog

      means you are not logged in

      sqlplus / as sysdba
      or
      sqlplus sys as sysdba

      means you are in logged in

      Comment by coskan gundogar — November 19, 2010 @ 11:14 am

  14. hai,

    i have written a procedure on scott/tiger but at the compilation time i got error as insufficient privileges plz tell about how to solve it.

    Comment by vijay — January 22, 2011 @ 3:18 pm

  15. Thanks.. (saol)

    Comment by Özgür Turan — January 16, 2013 @ 8:53 pm

  16. I absolutely love your website.. Excellent colors &
    theme. Did you build this web site yourself? Please reply back as I’m hoping to create my own personal site and would like to know where you got this from or exactly what the theme is called. Thank you!

    Comment by tropic skin care — August 4, 2013 @ 10:05 pm

  17. With havin so much content do yyou ver run into any problems of plagorism or
    copyright infringement? My blog has a lot of exclusive content I’ve either authored myself or outsourced but it looks like a lot
    of it is popping it up all over the internet without my permission.
    Do you know any methods to help protect against content from being stolen?
    I’d rreally appreciate it.

    Comment by Miu Mu 63ev — September 30, 2013 @ 10:24 pm

  18. Hi! Do you know if they make any plugins to protect against hackers?
    I’m kinda paranoid about losing everything I’ve worked hard on. Any tips?

    Comment by best Porn videos — August 7, 2014 @ 4:08 am

  19. You need to be a part of a context for one of the finest
    blogs on the web. I’m going to highloy recommen this web site!

    Comment by Adult Website suggestions — August 8, 2014 @ 5:25 am

  20. It’s a shame you don’t have a donate button! I’d most certainly donate to this superb blog!
    I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account.
    I look forward to fresh updates and will talk about this website with
    my Facebook group. Talk soon!

    Comment by free to do list app — August 13, 2014 @ 3:11 pm

  21. I blog quite often and I truly thank you for your information. This article has truly
    peaked my interest. I will bookmark your site and keep checking for new details about once a week.

    I subscribed to your RSS feed as well.

    Comment by nouvebelle cream — August 15, 2014 @ 12:01 am

  22. Infߋrmativе article, jսst wɦat I needed.

    Comment by safety trigger — August 28, 2014 @ 2:38 pm

  23. Really love all these steam showers

    Comment by seat-alterra.ru — September 5, 2014 @ 12:51 am

  24. Stateside Associates Arlington Virginia

    ORA-01031: insufficient privileges | Coskan’s Approach to Oracle

    Trackback by Stateside Associates Arlington Virginia — September 7, 2014 @ 4:29 pm

  25. article

    ORA-01031: insufficient privileges | Coskan’s Approach to Oracle

    Trackback by article — September 8, 2014 @ 11:34 pm

  26. You actually make it appar really easy together with your
    presentation but I find this matter to bbe really something that I belkeve I would by no
    means understand. It seems tooo complicated and extremely broad for me.
    I am having a look ahead on your next submit, I’ll
    attempt to get the hold off it!

    Comment by Glock slide plate custom — September 14, 2014 @ 6:09 am

  27. Hidradenitis Suppurativa Cure

    ORA-01031: insufficient privileges | Coskan’s Approach to Oracle

    Trackback by Hidradenitis Suppurativa Cure — September 16, 2014 @ 11:12 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers

%d bloggers like this: