oops.. sorry about that.. still waking up.. :)
Here is the output from the second query:
SEVERITY ERROR_CODE timestamp MESSAGE
--------------- ---------- --------------------
----------------------------------------------------------------------
Error 16198 08-JAN-2016 07:23:37 WARN: ARC3: Terminating pid
18564 hung on an I/O operation
Error 16198 08-JAN-2016 07:23:39 ARC3: Error 16198 due to hung
I/O operation to LOG_ARCHIVE_DEST_3
I see the following alert.log entry in Prod:
Thu Jan 07 19:09:15 2016
LNS: Standby redo logfile selected for thread 1 sequence 435322 for destination
LOG_ARCHIVE_DEST_2
krsv_proc_kill: Killing 1 processes (Process by index)
krsv_proc_kill: Killing 1 processes (Process by index)
ARC1: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_3
ARC1: Detected ARCH process failure
ARC1: Detected ARCH process failure
ARC1: STARTING ARCH PROCESSES
Thanks
-Upendra
From: nassyambasha@xxxxxxxxx
Date: Fri, 8 Jan 2016 18:50:04 +0530
Subject: Re: Troubleshooting log shipping
To: nupendra@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
Hello Upendra, I understand. But you missed to provide the first query i.e.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;Thanks.
On Fri, Jan 8, 2016 at 6:45 PM, Upendra nerilla <nupendra@xxxxxxxxxxx> wrote:
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@xxxxxxxxx
Date: Fri, 8 Jan 2016 15:08:10 +0530
Subject: Re: Troubleshooting log shipping
To: nupendra@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx> 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
--
Nassyam Basha.
Oracle Database Consultant| Pythian
Oracle 11g Certified Master
Co-Author: Oracle Data Guard 11gR2
Co-founder of Oraworld-team