1 2 3 Previous Next 31 Replies Latest reply: Jul 2, 2012 9:05 PM by 849425 Go to original post RSS
      • 15. Re: logfile member shows in database but not on physical disk, not match
        CKPT
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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