This discussion is archived
1 2 3 4 Previous Next 57 Replies Latest reply: Oct 17, 2013 3:43 AM by BluShadow Go to original post RSS
  • 30. Re: Standby Gaps after changing sys password
    yxes2013 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    Superrr problem solved ?

  • 35. Re: Standby Gaps after changing sys password
    Anar Godjaev Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    Please send again result

     

    select name, value from v$dataguard_Stats;

  • 40. Re: Standby Gaps after changing sys password
    yxes2013 Newbie
    Currently Being Moderated

    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
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    Hi Asif,

     

    hmmm maybe, but still I not sure ..

     

    Thank you

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points