Re: ADG lag after upgrading to 12.1

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Mon, 18 Mar 2019 08:18:09 -0500
Message-ID: <7e5f7310252bf506bfe5beb559c903c2_at_society.servebeer.com>

On 2019/03/17 14:58, Neil Chandler wrote:

> A lag of 20-30 seconds seems very high on the same server, with no network latency. What resource contention do you have on the server? Any CPU starvation? Slow disk?

I'm thankfully swimming in CPU -- 4 core POWER7, SMT4, hanging around 20% utilization with peaks below 60%, except when parallel RMAN incremental backups hit their daily run. Disk is XIV SAN, where I don't come near my pre-live tests of 4-5GBs with a 50/50 read/write split. Basically, no hardware performance issues -- I've been spoiled there... :)

But since this hasn't changed for 5 years, it seems highly likely to be something related to the DB upgrade.

> Any chance you could provide the config?
> Show configuration verbose
> Show database verbose "dbname"

I don't run the broker for various reasons, including issues I had at setup 5 years ago with EM12c BP1, IIRC. But maybe these 2 queries run from the Primary will give you what you're looking for:

SELECT

    vd.database_role,
    vd.force_logging,
    vd.flashback_on,
    vd.log_mode,
    vd.open_mode,
    vd.guard_status,
    vd.protection_mode,
    vd.switchover_status,

    vad.dest_id
FROM
    v$database vd, v$archive_dest vad
WHERE
    vad.target = 'STANDBY';

 		DATABASE_ROLE
 		FORCE_LOGGING
 		FLASHBACK_ON
 		LOG_MODE
 		OPEN_MODE
 		GUARD_STATUS
 		PROTECTION_MODE
 		SWITCHOVER_STATUS
 		DEST_ID

 		PRIMARY
 		YES
 		NO
 		ARCHIVELOG
 		READ WRITE
 		NONE
 		MAXIMUM PERFORMANCE
 		TO STANDBY

2

SELECT     ad.dest_id,ad.status,ad.target,ad.archiver,ad.process,ad.register,ad.transmit_mode,gap_status FROM v$archive_dest ad JOIN v$archive_dest_status ads ON ad.dest_id = ads.dest_id
WHERE ad.dest_id = 2;

 		DEST_ID
 		STATUS
 		TARGET
 		ARCHIVER
 		PROCESS
 		REGISTER
 		TRANSMIT_MODE
 		GAP_STATUS

2 		VALID
 		STANDBY
 		LGWR
 		LGWR
 		YES
 		ASYNCHRONOUS
 		NO GAP

> Do you have figures for the amount of redo produced at 11.2 and 12.1 ?

Hmmm, that sounds like a metric I should be actively monitoring, but am not. This might be in my EM repository, but a quick look at V$ARCHIVED_LOG shows no big difference after the upgrade, with an average of about 64GB/day.

Thoughts?

Thanks!
Rich

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 18 2019 - 14:18:09 CET