1 2 Previous Next 15 Replies Latest reply: Jan 16, 2014 9:24 AM by mseberg RSS

    ORA-010131 Insufficent Privileges on dest_id 2

    User516490-OC

      Hi Gurus,

       

      I'm trying to set physical standby for 11.2.0.3 RHEL 6.1 RAC Primary and RAC standby (3 nodes) using ASM  on the same cluster,i used RMAN active database for duplication and added the physical standby as cluster resource.

       

      The logs are not getting shipped if the standby is started using srvctl and looking at .v$archive_dest on primary for dest_id=2 shows error "ORA-010131 Insufficent Privileges" ,i copied password file from Primary database to all the standby instances and made sure it is rename appropriately.

       

      I tried many attempts with restarts of the standby database tried changing the the state of redo transport destinatrion.

       

      The logs will start shipping sometimes from all the instances if i manually start up the standby instances using sqlplus and v$archive_dest on primary for dest_id=2 shows all valid for all the primary instances.

       

      And also,i see on the standby alert log that its clearing primary standby redo log groups,is this normal or is it going to have redo corruption on the primary?

       

       

      Primary site: pr237

       

      standby site:pr237s1

       

       

       

      Primary db dataguard parameters:

       

       

       

      SQL> show parameter log_archive_dest

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      log_archive_dest                     string

      log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_

                                                       DEST VALID_FOR=(ALL_LOGFILES,A

                                                       LL_ROLES) db_unique_name=pr237

       

      log_archive_dest_2                   string      service=PR237S1 ASYNC reopen=1

                                                       5 NET_TIMEOUT=15 valid_for=(on

                                                       line_logfiles,primary_role) db

                                                       _unique_name=pr237s1

       

      SQL> show parameter log_archive_dest_state_2

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      log_archive_dest_state_2             string      enable

      log_archive_dest_state_20            string      enable

      log_archive_dest_state_21            string      enable

       

       

      below tns entries on all the nodes of the cluster (share prmy and stdby same Oracle home)

       

      Standby

       

      PR237S1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = lnx-crs-scan.vmx.org)(PORT = 1527))

          )

          (CONNECT_DATA =

            (SERVICE_NAME =PR237S1)

            (UR=A)

          )

        )

       

       

       

      Primary:

       

      PR237 =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = lnx-crs-scan.vmx.org)(PORT = 1527))

          (LOAD_BALANCE = yes)

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = PR237)

            (FAILOVER_MODE =

              (TYPE = SELECT)

              (METHOD = BASIC)

              (RETRIES = 180)

              (DELAY = 5)

            )

          )

        )

       

      Listener entry on node1 :

       

      SID_LIST_LISTENER =

          (SID_LIST =

            (SID_DESC =

            (GLOBAL_DBNAME = pr237_DGMGRL)

            (SID_NAME = pr237N1)

            (ORACLE_HOME = /lnx13adm/oracle/product/11.2.0.3/db)

            (ENVS="TNS_ADMIN=/lnx13adm/oracle/product/11.2.0.3/db/network/admin")

         )

       

            (SID_DESC =

            (GLOBAL_DBNAME = PR237)

            (SID_NAME = PR237N1)

            (ORACLE_HOME = /lnx13adm/oracle/product/11.2.0.3/db)

            (ENVS="TNS_ADMIN=/lnx13adm/oracle/product/11.2.0.3/db/network/admin")

         )

            (SID_DESC =

            (GLOBAL_DBNAME = pr237s1_DGMGRL)

            (SID_NAME = PR237S1N1)

            (ORACLE_HOME = /lnx13adm/oracle/product/11.2.0.3/db)

            (ENVS="TNS_ADMIN=/lnx13adm/oracle/product/11.2.0.3/db/network/admin")

         )

            (SID_DESC =

            (GLOBAL_DBNAME = PR237S1)

            (SID_NAME = PR237S1N1)

            (ORACLE_HOME = /lnx13adm/oracle/product/11.2.0.3/db)

            (ENVS="TNS_ADMIN=/lnx13adm/oracle/product/11.2.0.3/db/network/admin")

         )

       

       

       

       

       

      from primary site:

       

      SQL> select dest_name,dest_id,status ,inst_id,error from gv$archive_dest where dest_id=2 order by 4;

       

      DEST_NAME

      --------------------------------------------------------------------------------

         DEST_ID STATUS       INST_ID

      ---------- --------- ----------

      ERROR

      -----------------------------------------------------------------

      LOG_ARCHIVE_DEST_2

               2 ERROR              1

      ORA-01031: insufficient privileges

       

      LOG_ARCHIVE_DEST_2

               2 ERROR              2

      ORA-01031: insufficient privileges

       

      DEST_NAME

      --------------------------------------------------------------------------------

         DEST_ID STATUS       INST_ID

      ---------- --------- ----------

      ERROR

      -----------------------------------------------------------------

       

      LOG_ARCHIVE_DEST_2

               2 ERROR              3

      ORA-01031: insufficient privileges

       

       

       

      Primary inst3 alert log:

       

       

       

      Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_lgwr_68963.trc:

      ORA-00314: log 35 of thread 3, expected sequence# 1948 doesn't match 0

      ORA-00312: online log 35 thread 3: '+lnx13_DGA_DTA01/pr237/redo35a'

      Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_lgwr_68963.trc:

      ORA-00321: log 35 of thread 3, cannot update log file header

      ORA-00312: online log 35 thread 3: '+lnx13_DGA_DTA01/pr237/redo35a'

      Thread 3 advanced to log sequence 1949 (LGWR switch)

        Current log# 36 seq# 1949 mem# 0: +lnx13_DGA_DTA01/pr237/redo36a

        Current log# 36 seq# 1949 mem# 1: +lnx13_DGA_RDO01/pr237/redo36b

      Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_arc2_70823.trc:

      ORA-00313: open failed for members of log group 35 of thread 3

      Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_arc2_70823.trc:

      ORA-00313: open failed for members of log group 35 of thread 3

      ******************************************************************

      LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

      ******************************************************************

      Archived Log entry 7792 added for thread 3 sequence 1948 ID 0x237db0f4 dest 1:

      Error 1031 received logging on to the standby

      2014-01-14 16:05:15.100000 -05:00

      Error 1031 received logging on to the standby

      Error 1031 for archive log file 36 to 'pr237S1'

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_nsa2_5792.trc:

      ORA-01031: insufficient privileges

      FAL[server, ARC3]: Error 1031 creating remote archivelog file 'pr237S1'

      FAL[server, ARC3]: FAL archive failed, see trace file.

      ARCH: FAL archive failed. Archiver continuing

      ORACLE Instance PR237N3 - Archival Error. Archiver continuing.

      2014-01-14 16:06:29.588000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:07:29.882000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:08:30.837000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:09:31.037000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:10:31.212000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:11:31.549000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

      2014-01-14 16:12:32.003000 -05:00

      Error 1031 received logging on to the standby

      PING[ARC1]: Heartbeat failed to connect to standby 'pr237S1'. Error is 1031.

       

       

       

      file name conversion parameters on standby site:

      {code}

      db_file_name_convert           string+LNX13_DGA_DTA01/PR237, +LNX13
                                                 _DGA_DTA01/PR237S1
      log_file_name_convert          string+LNX13_DGA_RDO01/PR237, +LNX13
                                                 _DGA_RDO01/PR237S1

       

       

       

      Standby alert log from inst1

       

      Its trying to clear redo logs of primary database.Is this normal or due to any configuration issues.

      Clearing online redo logfile 32 complete

      Clearing online redo logfile 33 +LNX13_DGA_DTA01/pr237/redo33a.log

      Clearing online log 33 of thread 3 sequence number 1937

      2014-01-14 16:04:15.839000 -05:00

      Clearing online redo logfile 33 complete

      Clearing online redo logfile 34 +LNX13_DGA_DTA01/pr237/redo34a.log

      Clearing online log 34 of thread 3 sequence number 1934

      2014-01-14 16:04:16.925000 -05:00

      Clearing online redo logfile 34 complete

      Clearing online redo logfile 35 +LNX13_DGA_DTA01/pr237/redo35a

      Clearing online log 35 of thread 3 sequence number 1938

      2014-01-14 16:04:18.677000 -05:00

      Clearing online redo logfile 35 complete

      Clearing online redo logfile 36 +LNX13_DGA_DTA01/pr237/redo36a

      Clearing online log 36 of thread 3 sequence number 1939

      2014-01-14 16:04:20.269000 -05:00

      Clearing online redo logfile 36 complete

      Clearing online redo logfile 37 +LNX13_DGA_DTA01/pr237/redo37a

      Clearing online log 37 of thread 3 sequence number 1940

      2014-01-14 16:04:21.816000 -05:00

      Clearing online redo logfile 37 complete

      Clearing online redo logfile 38 +LNX13_DGA_DTA01/pr237/redo38a

      Clearing online log 38 of thread 3 sequence number 0

      2014-01-14 16:04:23.112000 -05:00

      Clearing online redo logfile 38 complete

      Clearing online redo logfile 39 +LNX13_DGA_DTA01/pr237/redo39a

      Clearing online log 39 of thread 3 sequence number 0

      2014-01-14 16:04:24.160000 -05:00

      Clearing online redo logfile 39 complete

      Clearing online redo logfile 40 +LNX13_DGA_DTA01/pr237/redo40a

      Clearing online log 40 of thread 3 sequence number 1933

      2014-01-14 16:04:25.718000 -05:00

      Clearing online redo logfile 40 complete

      Recovery of Online Redo Log: Thread 2 Group 43 Seq 2509 Reading mem 0

        Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_43.673.836775269

      Recovery of Online Redo Log: Thread 1 Group 1 Seq 2852 Reading mem 0

        Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_1.936.836775209

      Recovery of Online Redo Log: Thread 3 Group 53 Seq 1940 Reading mem 0

        Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_53.914.836775279

       

       

       

      Please let me know if there is anything that i need to look into for the "ORA-01031" i recreated the password files many times on standby but still no luck.

       

      Thanks

        • 1. Re: ORA-010131 Insufficent Privileges on dest_id 2
          Sunny kichloo

          Have you verified the permission of directories in which archives are generated???

          • 2. Re: ORA-010131 Insufficent Privileges on dest_id 2
            User516490-OC


            Thanks,the archives are going to ASM diskgroup "+LNX13_DGA_ARC01" .

            • 3. Re: ORA-010131 Insufficent Privileges on dest_id 2
              mseberg
              Until you can do the following:

               

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

               

              and

               

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

               

              You will not be able to ship redo.
              I would recopy and rename the password file and then restart the Standby on it again.
              Your listener.ora file is odd. Why are the global_dbname and sid_name the way they are?
              Best Regards
              mseberg
              • 4. Re: ORA-010131 Insufficent Privileges on dest_id 2
                User516490-OC

                I can nonnect from primary to standby using the SYS/PASWORD@STANDBY ,i copied the password mutiple times and rename them and restarted the standby but no luck.

                 

                connection to standby:

                 

                [oracle@lnx13-node3 ~]$ sqlplus sys@pr237s1 as sysdba

                SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 15 12:07:58 2014

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

                Enter password:

                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
                and Data Mining options

                SQL> select open_mode,database_role from v$database;

                OPEN_MODE            DATABASE_ROLE
                -------------------- ----------------
                MOUNTED              PHYSICAL STANDBY

                SQL>

                • 5. Re: ORA-010131 Insufficent Privileges on dest_id 2
                  mseberg

                  Can you post the password file name before and after along with the SID's you are starting the databases on?

                   

                  Best Regards

                   

                  mseberg

                  • 6. Re: ORA-010131 Insufficent Privileges on dest_id 2
                    User516490-OC

                    Here are the passwordfile names for standby.

                     

                    orapwPR237S1N1

                    orapwPR237S1N2

                    orapwPR237S1N3

                     

                    Standby instance names are:

                     

                    PR237S1N1

                    PR237S1N2

                    PR237S1N3

                     

                    Thanks and Regards

                    • 7. Re: ORA-010131 Insufficent Privileges on dest_id 2
                      mseberg

                      Those look correct.

                       

                      I have seen setups where somehow the SYSDBA role was messed up, but that;s highly unlikely.

                       

                      If you have not reviewed this I would:

                       

                      Troubleshooting Data Guard Log Transport Services (Doc ID 1604963.1)

                       

                      Best Regards

                       

                      mseberg

                      • 8. Re: ORA-010131 Insufficent Privileges on dest_id 2
                        User516490-OC


                        Thanks,the other question i had was about standby clearing the primary database redo log groups.Are these errors/warnings normal in maximum performance mode with SRL?

                         

                        Standby alert log from inst3

                         

                        Its trying to clear redo logs of primary database.Is this normal or due to any configuration issues.

                        Clearing online redo logfile 32 complete

                        Clearing online redo logfile 33 +LNX13_DGA_DTA01/pr237/redo33a.log

                        Clearing online log 33 of thread 3 sequence number 1937

                        2014-01-14 16:04:15.839000 -05:00

                        Clearing online redo logfile 33 complete

                        Clearing online redo logfile 34 +LNX13_DGA_DTA01/pr237/redo34a.log

                        Clearing online log 34 of thread 3 sequence number 1934

                        2014-01-14 16:04:16.925000 -05:00

                        Clearing online redo logfile 34 complete

                        Clearing online redo logfile 35 +LNX13_DGA_DTA01/pr237/redo35a

                        Clearing online log 35 of thread 3 sequence number 1938

                        2014-01-14 16:04:18.677000 -05:00

                        Clearing online redo logfile 35 complete

                        Clearing online redo logfile 36 +LNX13_DGA_DTA01/pr237/redo36a

                        Clearing online log 36 of thread 3 sequence number 1939

                        2014-01-14 16:04:20.269000 -05:00

                        Clearing online redo logfile 36 complete

                        Clearing online redo logfile 37 +LNX13_DGA_DTA01/pr237/redo37a

                        Clearing online log 37 of thread 3 sequence number 1940

                        2014-01-14 16:04:21.816000 -05:00

                        Clearing online redo logfile 37 complete

                        Clearing online redo logfile 38 +LNX13_DGA_DTA01/pr237/redo38a

                        Clearing online log 38 of thread 3 sequence number 0

                        2014-01-14 16:04:23.112000 -05:00

                        Clearing online redo logfile 38 complete

                        Clearing online redo logfile 39 +LNX13_DGA_DTA01/pr237/redo39a

                        Clearing online log 39 of thread 3 sequence number 0

                        2014-01-14 16:04:24.160000 -05:00

                        Clearing online redo logfile 39 complete

                        Clearing online redo logfile 40 +LNX13_DGA_DTA01/pr237/redo40a

                        Clearing online log 40 of thread 3 sequence number 1933

                        2014-01-14 16:04:25.718000 -05:00

                        Clearing online redo logfile 40 complete

                        Recovery of Online Redo Log: Thread 2 Group 43 Seq 2509 Reading mem 0

                          Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_43.673.836775269

                        Recovery of Online Redo Log: Thread 1 Group 1 Seq 2852 Reading mem 0

                          Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_1.936.836775209

                        Recovery of Online Redo Log: Thread 3 Group 53 Seq 1940 Reading mem 0

                          Mem# 0: +LNX13_DGA_ARC01/pr237s1/onlinelog/group_53.914.836775279

                         

                         

                        and  lot of redo log errors on primary alert log too,is there an impact to primary due to this redo errors on primary?

                         

                         

                        Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_lgwr_68963.trc:

                        ORA-00314: log 35 of thread 3, expected sequence# 1948 doesn't match 0

                        ORA-00312: online log 35 thread 3: '+lnx13_DGA_DTA01/pr237/redo35a'

                        Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_lgwr_68963.trc:

                        ORA-00321: log 35 of thread 3, cannot update log file header

                        ORA-00312: online log 35 thread 3: '+lnx13_DGA_DTA01/pr237/redo35a'

                        Thread 3 advanced to log sequence 1949 (LGWR switch)

                          Current log# 36 seq# 1949 mem# 0: +lnx13_DGA_DTA01/pr237/redo36a

                          Current log# 36 seq# 1949 mem# 1: +lnx13_DGA_RDO01/pr237/redo36b

                        Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_arc2_70823.trc:

                        ORA-00313: open failed for members of log group 35 of thread 3

                        Errors in file /lnx13adm/oracle/diag/rdbms/pr237/PR237N3/trace/PR237N3_arc2_70823.trc:

                        ORA-00313: open failed for members of log group 35 of thread 3

                        • 9. Re: ORA-010131 Insufficent Privileges on dest_id 2
                          mseberg

                          I don't see any impact on your because Oracle is clearing the redo.

                           

                          Generally redo is cleared because it has a corrupt entry.

                           

                          If the errors only occur over a short period of time there's no need to worry. But to be safe take a look at this:

                           

                          REDO or ARCHIVE Logfile Numbers Out Of Sync ORA-312 ORA-314 (Doc ID 1108550.1)

                           

                          Best Regards

                           

                          mseberg

                          • 10. Re: ORA-010131 Insufficent Privileges on dest_id 2
                            User516490-OC


                            Thanks .I removed the standby database as cluster resource and added back and then i start seeing the below errors now after i restarted standby with srvctl

                             

                            srvctl add database -d PR237S1 -o /lnx13adm/oracle/product/11.2.0.3/db -c RAC -p '+LNX13_DGA_DTA01/PR237s1/spfilepr237s1.ora' -r physical_standby -s 'READ ONLY' -t IMMEDIATE

                             

                            Primary :

                             

                            SQL> select dest_name,dest_id,status ,inst_id,error from gv$archive_dest where dest_id=2 order by 4;


                            DEST_NAME
                            --------------------------------------------------------------------------------
                               DEST_ID STATUS       INST_ID
                            ---------- --------- ----------
                            ERROR
                            -----------------------------------------------------------------
                            LOG_ARCHIVE_DEST_2
                                     2 ERROR              1
                            ORA-16058: standby database instance is not mounted

                            LOG_ARCHIVE_DEST_2
                                     2 ERROR              2
                            ORA-16058: standby database instance is not mounted

                            DEST_NAME
                            --------------------------------------------------------------------------------
                               DEST_ID STATUS       INST_ID
                            ---------- --------- ----------
                            ERROR
                            -----------------------------------------------------------------

                            LOG_ARCHIVE_DEST_2
                                     2 ERROR              3
                            ORA-16058: standby database instance is not mounted

                             

                             

                            standby:

                             

                            SQL> select inst_id,open_mode from gv$database;

                               INST_ID OPEN_MODE
                            ---------- --------------------
                                     3 READ ONLY WITH APPLY
                                     2 READ ONLY WITH APPLY
                                     1 READ ONLY WITH APPLY

                            • 11. Re: ORA-010131 Insufficent Privileges on dest_id 2
                              mseberg

                              Hello again;

                               

                              Would expect the second query to show something like this:

                               

                              SQL> select inst_id,open_mode from gv$database;

                               

                                INST_ID OPEN_MODE

                              ---------- --------------------

                                1 MOUNTED

                               

                              Did you do this:

                               

                              srvctl start database -d PR237S1 -o mount

                               

                               

                               

                              alter database recover managed standby database disconnect;

                               

                              Best Regards

                               

                              mseberg

                              • 12. Re: ORA-010131 Insufficent Privileges on dest_id 2
                                User516490-OC


                                no,what the query is showing is expected since it  is ADG and start option of srvctl is configured as read only.But the ouput of first query which is showing error "ORA-16058" is puzzling.

                                 

                                I looked at the documents (ML notes) and i seem to have done everything right but i'm not able to pass "ORA-01031: insufficient privileges" and logs are not getting shipped due to it.

                                 

                                If i start all the instances manually and mount and start the apply instead of opening i don't get this error.

                                 

                                Does the GLOBAL_DBNAME that i have in my listener.ora causing this what you were refereing to as weird.Is SID case sensitive in listener.ora and i believe have it all in upper case as ORACLE_SID?

                                 

                                Thanks

                                • 13. Re: ORA-010131 Insufficent Privileges on dest_id 2
                                  mseberg

                                  They are case sensitive. But what jumped out at me was your SID name and GLOBAL name were quite a bit different.

                                   

                                  My setup the global and sid are very close:

                                   

                                  (SID_DESC =

                                    (global_dbname = PRIMARY_DGMGRL.your_host.com)

                                    (ORACLE_HOME = /u01/app/oracle/product/11.2.0)

                                    (sid_name = PRIMARY)

                                   

                                  Best Regards

                                   

                                  mseberg

                                  • 14. Re: ORA-010131 Insufficent Privileges on dest_id 2
                                    User516490-OC

                                    Disabling system triggers at the standby site resolved the issues.Thanks for all the help

                                    1 2 Previous Next