RE: Troubleshooting log shipping

From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Fri, 8 Jan 2016 08:15:19 -0500
Message-ID: <BLU181-W12151AAE7C2B24DBF9FFB5D8F60_at_phx.gbl>

Hi Nassyam,
I believe the configuration is correct, otherwise I wouldn't be receiving any archivelogs at the destination. The DB_unique_name is set to "SID_DR" in the data guard site and there is a corresponding TNS entry in the PROD site (with the same name) pointing to the data guard site.

The issue is I am receiving SOME logs but not ALL the archivelogs at the data guard site. As I pasted the output of the archivelogs in my earlier message, see the sequence numbers are all over the place.. This is from ASM:

      512  1860501   952576512   954204160  thread_1_seq_434986.5778.900455621
       512  1883168   964182016   965738496  thread_1_seq_434991.8962.900456295
       512  1850346   947377152   948961280  thread_1_seq_434997.9060.900456899
       512  1865067   954914304   956301312  thread_1_seq_435003.3202.900457539
       512   134582    68905984    70254592  thread_1_seq_435008.1471.900457605
       512  1848325   946342400   947912704  thread_1_seq_435009.3990.900458259
       512  1863147   953931264   955252736  thread_1_seq_435017.6484.900458793
       512  1834310   939166720   940572672  thread_1_seq_435023.8233.900459079
       512  1438234   736375808   738197504  thread_1_seq_435024.11250.900460605

When the logapply hits the point where the archivelog is missing it is copying from Prod, which is slowing the log apply significantly. From alert.log at data guard:
Media Recovery Waiting for thread 1 sequence 434987 (in transit)

Here is the output of the query you sent (taken from Prod):

        ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE
---------- --------- --------------- ---------- ----------------------- -------------------- ---------- ----------
ARCHIVED_SEQ#

---

         1 VALID     OPEN            ARCH       IDLE                    MAXIMUM PERFORMANCE           0     0
       355351

         2 VALID     MOUNTED-STANDBY LGWR       MANAGED                 MAXIMUM PERFORMANCE          14     3
       435651

         3 VALID     MOUNTED-STANDBY LGWR       MANAGED                 MAXIMUM PERFORMANCE          14     3
       353763

Thanks
-Upendra

From: nassyambasha_at_gmail.com
Date: Fri, 8 Jan 2016 15:08:10 +0530
Subject: Re: Troubleshooting log shipping To: nupendra_at_hotmail.com
CC: oracle-l_at_freelists.org

Hello Upendra,
If the configuration is perfect then the redo/archive should receive on remote destinations. If you see below, you have mentioned service=SID_DR, can you confirm you have used the Oracle net service which points to the related standby database? and what about db_unique_name also you have mentioned SID_DR, it should be the unique name but not SID, so it depends on configuration however what the db_unique_name,sid, service configured.

"service="SID_DR", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=15                                                 db_unique_name="SID_DR" net_timeout=30, valid_for=(all_logfiles,primary_role)"

Can you send output of this below query? Run them from primary. set line 120 pages 100col severity for a15col message for a70col timestamp for a20select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=3;select ds.dest_id id, ad.status, ds.database_mode db_mode, ad.archiver type, ds.recovery_mode, 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 adwhere ds.dest_id = ad.dest_idand ad.status != 'INACTIVE'order byds.dest_id;

Thanks.
On Fri, Jan 8, 2016 at 1:22 PM, Upendra nerilla <nupendra_at_hotmail.com> wrote:

Hello everyone -
Happy new year!

Oracle RAC 11.2.0.3
OEL 5.x

I have an environment in which the primary is shipping logs to a local standby and a remote dataguard database. What I am seeing is that, somehow not all the archivelogs are being shipped to the remote site. At the time of log apply these logs are being transmitted over.

Here is an example on how the redologs appear in the remote site:

      512  1860501   952576512   954204160  thread_1_seq_434986.5778.900455621
       512  1883168   964182016   965738496  thread_1_seq_434991.8962.900456295
       512  1850346   947377152   948961280  thread_1_seq_434997.9060.900456899
       512  1865067   954914304   956301312  thread_1_seq_435003.3202.900457539
       512   134582    68905984    70254592  thread_1_seq_435008.1471.900457605
       512  1848325   946342400   947912704  thread_1_seq_435009.3990.900458259
       512  1863147   953931264   955252736  thread_1_seq_435017.6484.900458793
       512  1834310   939166720   940572672  thread_1_seq_435023.8233.900459079
       512  1438234   736375808   738197504  thread_1_seq_435024.11250.900460605

Similar gaps for thread_2 as well..

Here is the configuration of the log_archive_dest_3 on primary for the remote site: service="SID_DR", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=15

                                                 db_unique_name="SID_DR" net_timeout=30, valid_for=(all_logfiles,primary_role)

I do not see any errors for the redo transport in the alert log on the primary site or on the remote DG site. Any thoughts on how should I troubleshoot this? Are there any known bugs around this?

Your help is appreciated.

Thanks
-Upendra

--

Nassyam Basha.
Oracle Database Consultant| Pythian

Oracle 11g Certified Master
Co-Author: Oracle Data Guard 11gR2
Co-founder of Oraworld-team

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 08 2016 - 14:15:19 CET