1 2 Previous Next 29 Replies Latest reply: Sep 10, 2013 4:53 AM by vk82 RSS

    ORA-01031: insufficient privileges During Physical Standby DB creation

    vk82

      Hi All,

       

      My DB Version: 11.2.0

      OS Version:RHEL5

      pri server: 192.168.72.130

      standby server:192.168.72.132

       

      i am trying to ping from primary server to standby server

       

      On Primary Server:

      [oracle@192.168.72.130]$ lsnrctl stop LISTENER

      [oracle@192.168.72.130]$ lsnrctl start LISTENER

      [oracle@192.168.72.130]$ tnsping pri

      success

      [oracle@192.168.72.130]$ tnsping stand

      success

      [oracle@192.168.72.130]$ sqlplus sys/xxxxx@pri as sysdba

      success

      [oracle@192.168.72.130]$ sqlplus sys/xxxxx@stand as sysdba


      at this pint i am getting error as : ORA-01031:insufficient privileges

       

      On Standby Server:

      [oracle@dgaskmsby01]$ lsnrctl stop LISTENER

      [oracle@dgaskmsby01]$ lsnrctl start LISTENER

      [oracle@dgaskmsby01]$ tnsping pri

      success

      [oracle@dgaskmsby01]$ tnsping stand

      success

      [oracle@dgaskmsby01]$ sqlplus sys/xxxxx@pri as sysdba

      success

      [oracle@dgaskmsby01]$ sqlplus sys/xxxxx@stand as sysdba


      and at this pint i am getting error as : ORA-01031:insufficient privileges


      tnsnames.ora on both server


      [oracle@dgaskmsby01 admin]$ cat tnsnames.ora

      pri=

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.130)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SID=pri)

          )

        )

       

      stand =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.132)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SID=stand)

          )

        )


      listener.ora file on pri server:

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.130)(PORT = 1521))

          )

        )

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

            (SID_NAME = pri)

          )

        )

      listener.ora file on standby server:


      LISTENER_SBY =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

          )

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.72.132)(PORT = 1521))

          )

        )

       

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

            (SID_NAME = stand)

          )

        )

      So kindly help me to sort out this problem as i already googled and try to apply the sol given there but remain unsuccessful.

        • 1. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
          sb92075

          try copying the external password file from the primary to the standby

          • 2. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
            vk82

            Already copy the pwd file from pri server to standby server

            • 3. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
              jgarry

              If you can actually get logged in to the oracle owner on the server, with the proper environment set up, you should be able to just sqlplus / as sysdba

              without the @ sign.

              Perhaps you started listener on standby, rather than LISTENER_STBY

               

              Are there particular instructions you are following?

              • 4. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                vk82

                I am able to log in to the db when issuing

                 

                 

                export ORACLE_SID=pri

                sqlplus

                 

                username : sys as sysdba

                password:- oracle

                 

                SQL>

                 

                 

                export ORACLE_SID=stand

                 

                sqlplus

                username:- sys as sysdba

                password?- oracle

                 

                SQL>

                 

                but not when using @ from pri machine to connect to db on standby machine

                • 5. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                  mseberg

                  Hello;

                   

                  Try this test:


                  sqlplus sys/password@Standby as sysdba (from the primary system)

                   

                  and

                   

                  sqlplus sys/password@primary as sysdba (from the standby system)


                  Also keep in mind that you must not only move a copy of the password file from the Primary to the Standby, but you must rename it and shutdown the standby database and restart it using the new password file.


                  After this run this SQL on the standby:



                  SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;


                  Best Regards


                  mseberg


                  • 6. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                    vk82

                    Thanks for your reply

                     

                    but i already tried this as well

                     


                    sqlplus sys/password@Standby as sysdba (from the primary system)

                    getting

                    ORA-01031:insufficient privileges

                     

                    But when running

                     

                    sqlplus sys/password@primary as sysdba (from the standby system)


                    Success



                    • 7. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                      mseberg

                      OK.

                       

                      So you have confirmed the issue. This:

                       

                      sqlplus sys/password@Standby as sysdba (from the primary system)

                      getting

                      ORA-01031:insufficient privileges


                      Means you can not transport redo.


                      Not only does Data Guard require a password file, but it also expects SYS to be granted the SYSDBA roles. Data Guard looks for the password file in $ORACLE/dbs. If you are 100 percent certain your Standby password file is OK then you should check the SYS user on the standby.

                       

                      Make sure the parameter "remote_login_passwordfile" is set to SHARED or EXCLUSIVE on both side.

                       

                      Best Regards

                       

                      mseberg

                      • 8. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                        1012457

                        Take a backup of password file in both primary and standby database server.

                         

                        Now create a new password file with this below statement:

                         

                        $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle force=Y


                        and copy the password file to standby database and then check the status.

                        • 9. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                          Pradeepcmst

                          Hi vk82 ,

                          may i know what command you used to create a password file? kindly send us the full syntax you used to create the password file.

                           

                           

                           

                           

                           

                          Regards,

                          Pradeep. V

                          • 10. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                            vk82

                            Thanks for all your response

                             

                            I copied the pwd file from pri site and change the name of pwd file to standby pwd name

                             

                            I am thinking that proble may be of Oracle Data Guard requires that you configure a static listener.

                             

                            As my listner .ora on PRI SITE files look like:

                            ISTENER_PRI =

                              (DESCRIPTION_LIST =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))

                                )

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmpri01.localdomain)(PORT = 1525))

                                )

                              )

                             

                            SID_LIST_LISTENER_PRI =

                              (SID_LIST =

                                (SID_DESC =

                                  (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)

                                  (SID_NAME = orcl)

                                )

                              )


                            tnsnmaes.ora file on PRI SITE looks like:


                            cat tnsnames.ora

                            orcl =

                              (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmpri01.localdomain)(PORT = 1525))

                                (CONNECT_DATA =

                                  (SERVER = DEDICATED)

                                  (SID=orcl)

                                )

                              )

                             

                            sbyorcl =

                              (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmsby01.localdomain)(PORT = 1525))

                                (CONNECT_DATA =

                                  (SERVER = DEDICATED)

                                  (SID=sbyorcl)

                                )

                              )



                            ON STANDBY SITE:


                            Listener.ora file:


                            LISTENER_SBY =

                              (DESCRIPTION_LIST =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))

                                )

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmsby01.localdomain)(PORT = 1525))

                                )

                              )

                             

                            SID_LIST_LISTENER_SBY =

                              (SID_LIST =

                                (SID_DESC =

                                  (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)

                                  (SID_NAME = sbyorcl)

                                )

                              )


                            tnsnames.ora file:


                            orcl =

                              (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmpri01.localdomain)(PORT = 1525))

                                (CONNECT_DATA =

                                  (SERVER = DEDICATED)

                                  (SID=orcl)

                                )

                              )

                             

                            sbyorcl =

                              (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = dgaskmsby01.localdomain)(PORT = 1525))

                                (CONNECT_DATA =

                                  (SERVER = DEDICATED)

                                  (SID=sbyorcl)

                                )

                              )



                            after that what i did is:


                            Verify connectivity

                             

                            On Primary Server:

                            [oracle@dgaskmpri01]$ lsnrctl stop LISTENER_PRI

                            [oracle@dgaskmpri01]$ lsnrctl start LISTENER_PRI

                            [oracle@dgaskmpri01]$ tnsping orcl

                            [oracle@dgaskmpri01]$ tnsping sbyorcl

                            [oracle@dgaskmpri01]$ sqlplus sys/xxxxx@orcl

                            [oracle@dgaskmpri01]$ sqlplus sys/xxxxx@sbyorcl

                             

                            On Standby Server:

                            [oracle@dgaskmsby01]$ lsnrctl stop LISTENER_SBY

                            [oracle@dgaskmsby01]$ lsnrctl start LISTENER_SBY

                            [oracle@dgaskmsby01]$ tnsping orcl

                            [oracle@dgaskmsby01]$ tnsping sbyorcl

                            [oracle@dgaskmsby01]$ sqlplus sys/xxxxx@orcl

                            [oracle@dgaskmsby01]$ sqlplus sys/xxxxx@sbyorcl


                            getting problem at this point





                            • 11. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                              Pradeepcmst

                              HI,

                              On stanby database are you able to connect to database using the following command:

                              " sqlplus / as sysdba ". --> Kindly check this.

                              and also send me the syntax for password file creation you used...

                              • 12. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                                vk82

                                While running below command on standby machine

                                " sqlplus / as sysdba ".

                                 

                                getting error:

                                 

                                ERROR:

                                ORA-01031:insufficient Privileges

                                 

                                Command for recreating pwd file:

                                 

                                orapwd file=$ORACLE_HOME/dbs/orapwsbyorcl force=y password=oracle entries-5

                                 

                                 

                                Ok this problem is sorted out as i add in sqlnet.ora file

                                 

                                SQLNET.AUTHENTICATION_SERVICES=(ALL)

                                 

                                i am able to connect to db when issuing:

                                " sqlplus / as sysdba "

                                 

                                But when trying to connect using sqlplus sys/oracle@sbyorcl as sysdba

                                 

                                getting error as:

                                ORA-12154:TNS:could not resolve the connect identifier specified

                                • 13. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                                  mseberg

                                  Hello;

                                   

                                  Is it possible the TNSNAMES enty on the standby has some white space in around the entry for sbyorcl?

                                   

                                  Works even without password file

                                   

                                  ORACLE_SID=sbyorcl

                                  /home/oracle:sbyorcl >sqlplus "/ as sysdba"

                                   

                                  SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 9 07:19:12 2013

                                   

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

                                   

                                  Connected to an idle instance.

                                   

                                  SQL>

                                   

                                  Same results here

                                   

                                   

                                  /home/oracle:sbyorcl >sqlplus sys/oracle@sbyorcl as sysdba

                                   

                                  SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 9 07:47:57 2013

                                   

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

                                   

                                  ERROR:

                                  ORA-12154: TNS:could not resolve the connect identifier specified

                                   

                                   

                                  Enter user-name:

                                   

                                   

                                   

                                   

                                   

                                  Best Regards

                                   

                                  mseberg

                                  • 14. Re: ORA-01031: insufficient privileges During Physical Standby DB creation
                                    vk82

                                    Thanks for your reply

                                     

                                    I checked all files for space issue and corrected them as well, but still getting the same

                                     

                                    ORA-01031 Error

                                    1 2 Previous Next