7 Replies Latest reply: May 3, 2012 3:43 AM by taha RSS

    orapwd and oracle password file

    taha
      Hi all,

      I have to allow remote connetion to an instance as sysdba. on remote server (SunOS, SQL*Plus: Release 10.2.0.1.0) and on oracle database server (SunOS, Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 )
      Oracle database server contains many databes instances.

      i tried these sql statments:

      SQL> show parameter password;

      NAME TYPE VALUE

      remote_login_passwordfile string EXCLUSIVE



      SQL> select * from v$pwfile_users;

      no rows selected

      i tried searching for this password file (i think it must be in $ORACLE_HOME/dbs directory) i found a weird one pwd$ORACLE_SID ( i thought is should be orapw$ORACLE_SID).
      I thought since there is a password file why not trying to grant sysdba privilège to some user ( i just want to see if the password file was used). so on oracle database server (as sysdba) i tried this :
      SQL> grant sysdba to XXX;
      ERROR at line 1:
      ORA-01994: GRANT failed: password file missing or disabled

      I have some question about this situation :

      1/ if something went wrong when using the orawpd utility, why are we able to (start up/ shut down) this instance whithout problems (well being on oracle server of course), does the password file authentification interfere only with remote authentification as sysdba ? (sorry i don't grasp well this mechanism since i am not a DBA administrator ...)

      2/ how can i do to solve this situation :)

      Thanks a lot
      BR
      Taha
        • 1. Re: orapwd and oracle password file
          Anthony.P
          Hello,

          You are able to startup and shutdown your database from lacalhost since your user belongs to the dba group as specified while the Oracle installation. And as far as I know, I think the SYS and SYSTEM users automatically owns the sysdba role.

          The file with the "pwd$ORACLE_SID" name means it is a password file created for a Windows installation. Since you are using Unix, you are right, your password file should be "orapwd$ORACLE_SID".
          Maybe you should try to re-create a password file with the orapwd command (orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password> entries=10)
          • 2. Re: orapwd and oracle password file
            taha
            Hi :)

            thanks for your reply.

            1/ Is it orapwd$ORACLE_SID or orapw$ORACLE_SID ?

            2/ How do i recreate this password file ? do i have first to set REMOTE_LOGIN_PASSWORDFILE parameter to 'NONE' then reset password file and finally reset REMOTE_LOGIN_PASSWORDFILE parameter to 'EXCLUSIVE' or can I directly change password file ?

            3/ this question is just to be sure ( it may be stupid to ask it but i prefer being sure) : there are some backups of this database ( a tar.gz files of the /data/ora/$ORACLE_SID), il i want to restore this backup i should not face problem because changing the password file just in case.

            thanks
            Taha
            • 3. Re: orapwd and oracle password file
              mBk77
              1 The file name =${ORACLE_HOME}/dbs/orapw${ORACLE_SID}

              2 You dont have to do any thing just run the orapwd utility and it will recreate the password file.
              then you can set the REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE OR SHARED

              3 The password file has got nothing to do with your datafiles, backups ..It is used to grant sysdba privlage to normal users,
              so there wont be any problem even if you loose the password file, you can still login using the OS authentication.

              If you are not part of the OS groups then you must and should have password file to connect as sysdba/sysoper...
              • 4. Re: orapwd and oracle password file
                Helios-GunesEROL
                Hi Taha;

                I suggest to read below links first:
                http://www.orafaq.com/wiki/Orapwd

                Than I suggest read:
                Creating and Maintaining a Password File
                http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba007.htm

                Regard
                Helios
                • 5. Re: orapwd and oracle password file
                  taha
                  since i had already REMOTE_LOGIN_PASSWORDFILE set to exclusive , I asked about steps of recreating the password file because i read this in some link :
                  http://ist.uwaterloo.ca/~baumbach/ORACLE10g/orapwd.html


                  "Removing the password file

                  If you determine that you no longer require a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE init.ora parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform database administration operations.

                  +Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED. If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamps and checksums will be wrong.+


                  :?

                  Thanks
                  Taha
                  • 6. Re: orapwd and oracle password file
                    Anthony.P
                    Yes, sorry, the filename is ora*pw*$ORACLE_SID (sorry for the typo).

                    About setting REMOTE_LOGIN_PASSWORDFILE to NONE, I've never read such a thing (yet... :p). I've just performed a test: I've removed the orapw file and created a new one, and my instance was able to find the new file. I've even created a new user with sysdba privileges. However, maybe I have a lack of experience, and setting REMOTE_LOGIN_PASSWORDFILE to 'none' while creating the new file is not that difficult.
                    • 7. Re: orapwd and oracle password file
                      taha
                      Hi,
                      well i tried this and it worked

                      1/ set the remote_login_passwordfile to none

                      alter SYSTEM SET remote_login_passwordfile=none scope=spfile; ( here you have to find if you are using spfile or pfile, in my case it was spfile)

                      2/ restart database to take changes in account
                      3/ delete (if it exists) the old password file.
                      4/ create the new password file : orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys_password entries=5 (i added 5 just to test it)
                      5/alter system set remote_login_passwordfile=exclusive scope=spfile;
                      6/ restart database to take changes in account
                      and now i can connect as a sysdba from a remote server.


                      BR
                      Taha

                      Edited by: taha on 3 mai 2012 10:43