This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Jul 2, 2012 7:05 PM by 849425 Go to original post RSS
  • 15. Re: logfile member shows in database but not on physical disk, not match
    CKPT Guru
    Currently Being Moderated
    846422 wrote:
    How do I judge?

    I checked alert log, the logfile errors are all gone and I compared os asm logfiles with db logfiles , they match.

    However there has been none any entry in alert log on standby side since I brought them to recovery mode.
    That's fine. Why you always missing something?
    I requested to query thy SQL and to post, have you done a the steps. Then where is my output.
    Don't expect help when there is insufficient information. Read carefully and post complete what we requested. Hope you understood why am asking... Read previous post

    Or

    Query that or use below link to execute on primary & standby then post in coded format
    http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
  • 16. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    I don;t know why this time standby not work. I will update this thread tomorrow when I log back into work.
    Thanks, CKTP.
  • 17. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    ok, just got time to run the query on primary:

    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE SWITCHOVER_STATUS
    ---------- ------------------------------ -------------------- ---------- -------------------- --------------------
    PRD PRD MAXIMUM PERFORMANCE PRIMARY READ WRITE NOT ALLOWED
    SQL> FROM
    (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,
    (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;
    col severity for a15
    col message for a70
    col timestamp for a20
    select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
    select ds.dest_id id
    , ad.status
    , ds.database_mode db_mode
    , ad.archiver type
    , ds.recovery_mode

    THREAD# MAX(SEQUENCE#)
    ---------- --------------
    1 899
    2 529
    SQL> 2 3 4 5 , ds.protection_mode
    , ds.standby_logfile_count "SRLs"
    , ds.standby_logfile_active active
    , ds.archived_seq#
    from v$archive_dest_status ds
    , v$archive_dest ad
    where ds.dest_id = ad.dest_id
    and ad.status != 'INACTIVE'
    order by
    ds.dest_id;
    column FILE_TYPE format a20
    col name format a60
    select name
    , floor(space_limit / 1024 / 1024) "Size MB"
    , ceil(space_used / 1024 / 1024) "Used MB"
    from v$recovery_file_dest
    order by name;
    spool off
    /


    Thread Last Sequence Received Last Sequence Applied Difference
    ---------- ---------------------- --------------------- ----------
    1 899 899 0
    2 529 529 0
    SQL> SQL> SQL> SQL>

    SEVERITY ERROR_CODE timestamp MESSAGE
    --------------- ---------- -------------------- ----------------------------------------------------------------------

    Error 16047 02-JUL-2012 21:50:13 PING[ARC2]: Heartbeat failed to connect to standby 'prds'. Error i
    s 16047.

    Error 16047 02-JUL-2012 21:51:14 PING[ARC2]: Heartbeat failed to connect to standby 'prds'. Error i
    s 16047.

    Error 16047 02-JUL-2012 21:52:15 PING[ARC2]: Heartbeat failed to connect to standby 'prds'. Error i
    s 16047.

    SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    --- --------- --------------- ---- -------------------- -------------------- ---- ------ -------------
    1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 900
    2 DISABLED UNKNOWN LGWR IDLE MAXIMUM PERFORMANCE 0 0 0
    SQL> SQL> SQL> 2 3 4 5
    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    +PRD_FRA                                                        737000      18655
    SQL> SQL>
    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    +PRD_FRA                                                        737000      18655
    SQL> SQL>

    Edited by: 846422 on Jul 2, 2012 3:03 PM
  • 18. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    Standy output:

    NAME DISPLAY_VALUE
    ------------------------------ ------------------------------
    db_file_name_convert PRD_DAT, PRD_DAT
    db_name PRD
    db_unique_name PRDS
    dg_broker_config_file1 +PRD_DAT
    dg_broker_config_file2 +PRD_DAT
    dg_broker_start TRUE
    fal_client
    fal_server PRD
    local_listener (DESCRIPTION=(ADDRESS_LIST=(AD
    DRESS=(PROTOCOL=TCP)(HOST=oracle07s)(PORT=1521))))
    log_archive_config nodg_config
    log_archive_dest_2
    log_archive_dest_state_2 enable
    log_archive_max_processes 4
    log_file_name_convert PRD_DAT, PRD_DAT
    remote_login_passwordfile EXCLUSIVE
    standby_archive_dest ?/dbs/arch
    standby_file_management AUTO
    SQL> SQL> SQL>
    NAME DB_UNIQUE_NAME PROTECTION_MODE DATABASE_R OPEN_MODE
    ---------- ------------------------------ --------------- ---------- --------------------
    PRD PRDS MAXIMUM PERFORM PHYSICAL S MOUNTED
    ANCE TANDBY

    SQL> select process, status,thread#,sequence# from v$managed_standby;
    col name for a30
    select * from v$dataguard_stats;
    SQL>
    PROCESS STATUS THREAD# SEQUENCE#
    --------- ------------ ---------- ----------
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    ARCH CONNECTED 0 0
    MRP0 WAIT_FOR_LOG 1 480
    SQL> SQL> select * from v$archive_gap;
    col name format a60
    select name
    , floor(space_limit / 1024 / 1024) "Size MB"
    , ceil(space_used / 1024 / 1024) "Used MB"
    from v$recovery_file_dest
    order by name;
    spool off


    NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
    ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
    transport lag day(2) to second(0) interval 07/02/2012 21:57:59
    apply lag day(2) to second(0) interval 07/02/2012 21:57:59
    apply finish time day(2) to second(3) interval 07/02/2012 21:57:59
    estimated startup time 24 second 07/02/2012 21:57:59
    SQL> SQL> SQL> 2 3 4 5
    NAME Size MB Used MB
    ------------------------------------------------------------ ---------- ----------
    +PRD_FRA                                                        716735        736                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 19. Re: logfile member shows in database but not on physical disk, not match
    mseberg Guru
    Currently Being Moderated
    Hello;

    Error 16047 02-JUL-2012 21:52:15 PING[ARC2]: Heartbeat failed to connect to standby 'prds'. Error i
    s 16047.

    You have a common error. So on your Primary I;m think log_archive_dest_n and the DB_UNIQUE_NAME fro the Standby don't match.

    The DB_UNIQUE_NAME specified for the destination does not match the DB_UNIQUE_NAME at the destination.

    Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n parameter defined for the destination matches the DB_UNIQUE_NAME parameter defined at the destination.

    Correct, cancel apply, restart Mount on corrected spfile, check results.

    Best Regards

    mseberg
  • 20. Re: logfile member shows in database but not on physical disk, not match
    CKPT Guru
    Currently Being Moderated
    Error 16047 02-JUL-2012 21:52:15 PING[ARC2]: Heartbeat failed to connect to standby 'prds'. Error is 16047.
    ORA-16047:
         DGID mismatch between destination setting and standby
    Cause:      The DB_UNIQUE_NAME specified for the destination does not match the DB_UNIQUE_NAME at the destination.
    Action:      Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n parameter defined for the destination matches the DB_UNIQUE_NAME parameter defined at the destination.
    >


    you posted only information from Primary what about standby?
    You should put this script in shell and run from OS, then you will get in better format. & You missed some of the output.

    Still...........

    Your LOG_ARCHIVE_CONFIG settings are wrong.
    it should be as

    LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRD,PRDS)'
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(<primary_db_unique_name,<standby_db_unique_name>)'

    Once you perform above settings, then
    1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 900
    2 DISABLED UNKNOWN LGWR IDLE MAXIMUM PERFORMANCE 0 0 0
    SQL> alter system set log_archive_dest_state_2='defer';
    SQL> alter system set log_archive_dest_state_2='enable';

    & then try to perform log switches then check for errors from below query

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


    NOTE:- read all the contents i have posted & post here all what i have requested. Then only it is possible to look, Hope you understood.
  • 21. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    SQL> show parameter db_unique

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_unique_name string PRD
    SQL> show parameter db_name

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_name string prd
    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, ALL_R
    OLES)
    DB_UNIQUE_NAME=prd
    log_archive_dest_10 string
    log_archive_dest_11 string
    log_archive_dest_12 string
    log_archive_dest_13 string
    log_archive_dest_14 string

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_15 string
    log_archive_dest_16 string
    log_archive_dest_17 string
    log_archive_dest_18 string
    log_archive_dest_19 string
    log_archive_dest_2 string service="prds", LGWR ASYNC
    NOAFFIRM delay=0 optional com
    pression=disable max_failure=0
    max_connections=1 reopen=300
    db_unique_name="prds" net_
    timeout=30, valid_for=(all_log

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    files,primary_role)


    SQL> show parameter log_archive_config

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string dg_config=(PRD,prds)


    I think I configured all right. primary db name is prd, and standby is prds. Are case matter?

    Where did I configured wrong?
  • 22. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    db_unique_name for prod is prd, for standby is prds.

    I think somehow the instance name is upcase, should I put those in upcase or lowercase? how to check?
  • 23. Re: logfile member shows in database but not on physical disk, not match
    CKPT Guru
    Currently Being Moderated
    log_archive_dest_2 string service="prds", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="prds" net_timeout=30, valid_for=(all_logfiles,primary_role)
    Its not necessary to put Service name & DB_UNIQUE_NAME in double quotes, set again properly(trial)
    Again & Again am asking you to read Again & Again... But all the time you are missing.. Why happening like that?

    READ AGAIN!!!!!!

    SQL> alter system set log_archive_dest_state_2='defer';
    SQL> alter system set log_archive_dest_state_2='enable';

    & then try to perform log switches then check for errors from below query

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

    NOTE:- read all the contents i have posted & post here all what i have requested. Then only it is possible to look, Hope you understood.
  • 24. Re: logfile member shows in database but not on physical disk, not match
    mseberg Guru
    Currently Being Moderated
    Hello again;

    What I saying is in your Primary INIT you probably have a setting like :
    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
    And in your Standby INIT you should have
    DB_UNIQUE_NAME
    ORA-16047 is a mismatch between destination setting and standby. Yes, make sure they are the same case too. LOG_ARCHIVE_CONFIG if set can cause this too.

    I will echo CKPT's comment. But I want to say this as professionally as possible. Read the replies carefully. CKPT is an outstanding troubleshooter, but it seems you miss some of the questions asked of you. Please focus carefully on them, because what we are giving you is our time. I know both CKPT and myself will ask a lot of questions, but please understand the mission is to solve ASAP.

    Best Regards

    mseberg
  • 25. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    I changed log_archive_dest_2 to:
    log_archive_dest_2 string SERVICE=prds ASYNC LGWR VA
    LID_FOR=(ONLINE_LOGFILES,PRIMA
    RY_ROLE) DB_UNIQUE_NAME=pr
    ds


    on the primary.

    set to defer and then enabled that log_archive_dest_2, and still get same error.
  • 26. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    I followed cktp 's instruction and it seems still has same error.

    Here is db_unique name on standby side:

    SQL> show parameter db_unique_name

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_unique_name string PRDS
  • 27. Re: logfile member shows in database but not on physical disk, not match
    mseberg Guru
    Currently Being Moderated
    Are you using LOG_ARCHIVE_CONFIG?

    If yes can you post both primary and standby settings?

    Would probably go lowercase all the way around.

    Best Regards

    mseberg
  • 28. Re: logfile member shows in database but not on physical disk, not match
    849425 Newbie
    Currently Being Moderated
    This might be the problem. On standby:

    SQL> show parameter log_archive_config

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string nodg_config


    On primary:
    SQL> show parameter log_archive_config

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config string dg_config=(PRD,prds)
    SQL>
  • 29. Re: logfile member shows in database but not on physical disk, not match
    mseberg Guru
    Currently Being Moderated
    Ah Ha ( sorry )

    Mismatched case, you found it!!

    log_archive_config string dg_config=(PRD,prds)

    but db_unique_name is PRDS
     db_unique_name string PRDS 
    This will cause the error!!

    Cause:

    Case mis-match between db_unique_name and log_archive_config

    Setting on one database and not the other might cause this error too.

    Best Regards

    mseberg

    Edited by: mseberg on Jul 2, 2012 6:15 PM

    Example

    http://syedrehanmehdi.blogspot.com/2012_06_01_archive.html

    Edited by: mseberg on Jul 2, 2012 6:54 PM

Legend

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