1 2 3 4 Previous Next 57 Replies Latest reply: Oct 17, 2013 5:43 AM by BluShadow Go to original post RSS
      • 30. Re: Standby Gaps after changing sys password
        yxes2013

        Hi ckpt,

         

        once you copied the password file you can also check the view for reference, v$pwfile_users

        Later you can perform the connectivity from primary to stndby using TNS service.. and vice versa.

        From Primary: sqlplus sys/****@standby as sysdba

        From Standby: sqlplus sys/****@primary as sysdba

         

        After that perform couple of log switches on primary and check the errors in alert log or using below query relate to authentication

        select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

         

         

        I can connect both from and to Primary & Standby :

         

        But I still have errors

        SQL> select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

         

         

        SEVERITY      ERROR_CODE timestamp

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

        MESSAGE

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

        Error               1031 17-OCT-2013 13:49:44

        PING[ARC0]: Heartbeat failed to connect to standby 'PRODDR'. Error is 1031.

         

         

        Error               1031 17-OCT-2013 13:50:45

        PING[ARC0]: Heartbeat failed to connect to standby 'PRODDR'. Error is 1031.

         

         

        Error               1031 17-OCT-2013 13:51:46

        PING[ARC0]: Heartbeat failed to connect to standby 'PRODDR'. Error is 1031.

         

         

         

         

        SEVERITY      ERROR_CODE timestamp

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

        MESSAGE

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

        Error               1031 17-OCT-2013 13:52:49

        PING[ARC0]: Heartbeat failed to connect to standby 'PRODDR'. Error is 1031.

         

         

         

         

        I thought this is just a simple process to do, I did not know I would get errors which can not identify the cause

        • 31. Re: Standby Gaps after changing sys password
          Anar Godjaev

          My Friend,

           

          This problem can be solved in two ways:

           

          1) bash-3.2$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX1 Standby_ip:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX (In primary)

          2) Maybe you should do it in Secondary database:  orapwd file='..\dbs\orapwxxx.ora password=XXXXX entries=5 force=y (Secondary DB)

           

          Please Follow step 2 :

           

          Maybe you should do it in Secondary database:  orapwd file='..\dbs\orapwxxx.ora password=XXXXX entries=5 force=y (Secondary DB)


          Now provide the output of:  (Primary)


          SELECT status, error

          FROM V$ARCHIVE_DEST_STATUS

          WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

           

          Thank you



          • 32. Re: Standby Gaps after changing sys password
            BluShadow

            yxes,

             

            Just clarify for us... what DBA training and experience do you have?

            The things you are trying to do are the sort of DBA tasks that I would expect a properly trained Oracle DBA to be doing, and one who has practiced and tried these things on test databases and installations many times, as well as being mentored by an experienced DBA, before even going anywhere near a live database.

            These are not the sort of things that an inexperienced DBA should be attempting on a production database, and certainly not something that should be relying on anonymous volunteers of a public forum to sort out.

            I hope, for your company's sake, that the references to PROD in your environment are not a real live production database instance.

            • 33. Re: Standby Gaps after changing sys password
              yxes2013

              Hi Asif

               

              Please issue another 2 to 3 log switches and check the log file.

               

              Now provide the output of:

              SELECT status, error

              FROM V$ARCHIVE_DEST_STATUS

              WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

               

               

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

               

               

              SQL> alter system switch logfile;

               

               

              System altered.

               

               

              SQL> /

               

               

              System altered.

               

               

              SQL> /

               

               

              System altered.

               

               

              SQL> SELECT status, error

              FROM V$ARCHIVE_DEST_STATUS

              WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';  2    3

               

               

              STATUS    ERROR

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

              VALID

              VALID

               

               

              What action did resolved the issue???

               

              I can not understand what had happened.

               

              Thanks

               

              • 34. Re: Standby Gaps after changing sys password
                Anar Godjaev

                Superrr problem solved ?

                • 35. Re: Standby Gaps after changing sys password
                  Anar Godjaev

                  can you plase cheeck thuis query in standby   :

                   

                  select name, value from v$dataguard_Stats;

                   

                  and paste result

                  • 36. Re: Standby Gaps after changing sys password
                    Asif Muhammad

                    Hi,

                     

                    I believe this:

                    1) SQL> alter system set log_archive_dest_state_2='defer';

                    2) SQL> alter system set log_archive_dest_state_2='enable';

                    3) Now perform couple of log swtiches using command:

                         alter system switch logfile.

                     

                    which consequently proved success in your alert logfile.

                     

                    Anways brother, Just to add on and better assist you. Before doing anything on PROD it is BEST and MUST DO approach to test these things on a test enviornment before meddling with the LIVE enviornment so that you get a hang of it and know what you are actually trying to do in the PROD.

                     

                    Thanks &

                    Best Regards,

                    • 37. Re: Standby Gaps after changing sys password
                      Anar Godjaev

                      Dear Asif,

                       

                      I say it again:   This problem can be solved in two ways:

                       

                      1) bash-3.2$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX1 Standby_ip:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX (In primary)

                      2) Maybe you should do it in Secondary database:  orapwd file='..\dbs\orapwxxx.ora password=XXXXX entries=5 force=y (Secondary DB)



                      In my opinion:  it is not solved this problem .


                      1) SQL> alter system set log_archive_dest_state_2='defer';

                      2) SQL> alter system set log_archive_dest_state_2='enable';


                      problem can be solved in such a way:


                      bash-3.2$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX1 Standby_ip:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX (In primary)


                       

                       

                      Thank you


                       


                      • 38. Re: Standby Gaps after changing sys password
                        yxes2013

                        Hi all,

                         

                        Why still I have gaps? or are these gaps?

                         

                         

                        SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

                          2  FROM

                          3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

                          4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

                        WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;  5

                         

                         

                            Thread Last Sequence Received Last Sequence Applied Difference

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

                                 1                   2642                  2594         48

                         




                        select name, value from v$dataguard_Stats;


                        NAME                      VALUE

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

                        transport lag             +00 00:00:00

                        apply lag                 +02 02:02:04

                        apply finish time         +00 00:02:40.000

                        estimated startup time    13



                        Thanks....

                        • 39. Re: Standby Gaps after changing sys password
                          Anar Godjaev

                          Please send again result

                           

                          select name, value from v$dataguard_Stats;

                          • 40. Re: Standby Gaps after changing sys password
                            yxes2013

                            Hi Anar,

                             

                            I have done this successfully :

                             

                            problem can be solved in such a way:

                            bash-3.2$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX1 Standby_ip:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwXXXXX (In primary)

                             

                            The proof is: I can sqlplus sys/password@PRODDR as sysdba   and vice-versa

                             

                            So what else  do I have to check?

                             

                            Thanks


                            • 41. Re: Standby Gaps after changing sys password
                              Mahir M. Quluzade

                              1. Check Apply is MRP process running ?

                               

                              select process from v$managed_standby where process like 'MR%';

                               

                              2. Please check Archived logs is exists in primary side.

                               

                              RMAN> restore archivelog from logseq 2595 until  logseq 2642;

                               

                              3. Please check transport service is running

                               

                              4. Check  transported and applied archived log.

                               

                              select max(sequence#) from v$archived_log; -- primary

                               

                              select max(sequence#)  from v$archived_log; standby

                              select max(sequence#)  from v$archived_log where pplied = 'YES';

                               

                               

                              Regards

                              Mahir M. Quluzade

                              • 42. Re: Standby Gaps after changing sys password
                                Asif Muhammad

                                Hi Anar,

                                 

                                I am quiet sure that you and Mahir guided him the correct path asking him to copy the password file to the standby site, and the INSUFFICIENT PRIVILEGE shown on the data dictionary holds the point true.

                                 

                                And  the archive_log_dest_2 is able to push in archive logs to the standby using the authentication supported by the password file, but  the OP mentioned issue existed even after couple of log switches after the password file being transferred from the Primary site to the standby site. That is the reason I requested to do a manual archive_log_dest_state_2 DEFER and ENABLE and recheck with the archive log switch from Primary Site.

                                 

                                 

                                Thanks &

                                Best Regards,

                                • 43. Re: Standby Gaps after changing sys password
                                  Anar Godjaev

                                  HI yxes,

                                   

                                  Super , Thank you

                                   

                                  Finally : Please send again result

                                   

                                  select name, value from v$dataguard_Stats;


                                  Thank you

                                  • 44. Re: Standby Gaps after changing sys password
                                    Anar Godjaev

                                    Hi Asif,

                                     

                                    hmmm maybe, but still I not sure ..

                                     

                                    Thank you