Archiver Process cache TNS Entries for Data Guard Broker

 When you setup Standby DB or Data Guard Broker, you may see this error
ORA-16737: the redo transport service for standby

Dataguard Broker trace logs shows
 ORA-12154: TNS:could not resolve the connect identifier specified
 ORA-16737: the redo transport service for standby database "testdg" has an error
2018-02-20 01:05:59.926 00001000    56049214 Operation HEALTH_CHECK canceled during phase 1, error = ORA-16778

However tnsping testdg is fine
sqlplus sys/***@testdg as sysdba    ---- is also fine

Actions:

• Set dgmgrl tracelevel to support
• Remove and Add DB into DG broker configuration with different connect identifiers(no luck)
• Modify tnsnames.ora entry for scan and vip(no luck)
• Stop and Start DG broker (no luck)
• Modify log_archive_dest_3 not to use TNS. (no luck)

alter system set log_archive_dest_3='SERVICE="(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test-adc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = WWWABCUDG.US.ORACLE.COM)))" ASYNC VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES ) DB_UNIQUE_NAME=WWWABCDG';

Reason:

 After trying all kinds of ways, we find it's related to archiver process which will not read the NEW entries from tnsnames.ora file. The new DB entries were unknown to archiver. That's why it keeps reporting  ORA-12154

Solution:

•  Bounce the DB to refresh archiver processes which needs outages

•  Kill the archiver processes one by one. Use "alter system switch logfile" to spawn them back

Above 2 methods to force archiver processes to read tnsnames.ora and refresh entries

 

 

About "SQL*Net break/reset to client" Event of Oracle DB

Users reported that his application was very slow when it exec a  "insert"  sql again oracle DB in the cloud

User has limited skills to turn on trace from apps

Actions:

• Get AWR report from Cloud EM express
• Use sqlt to get more detailed sql report
•  Ask user to set sql trace or turn on 10046 event 
• Tkprof the trace file 

Reason:

 The top event from the insert sql is"SQL*Net break/reset to client". CPU IO are OK, no contention found. It appears to be network related. However other sql were running well. When you check oracle doc of this event, it could be application related. Specially when pl/sql loop is involved. It would generate quite many such events on db side. 

Solution:

   Ask user to involve developers to tune the sql ,check how they handle error exception and check apps logs

How To Use Incremental Backup to Fix DataGuard Gap

Top Steps

• Get SCN of Primary and Standby
• Backup incremental from scn ******** database;
• alter database create standby controlfile as '/u02/backup/***_standby.ctl';
• Copy backup and controlfiles to Standby Host
• Startup nomount
• RESTORE STANDBY CONTROLFILE FROM '/u03/backup/****_standby.ctl';
• Alter database mount
• Alter system set standby_file_management=manual;
• Change datafile location (example commands)
  alter database rename file '/u10/oradata/test/users01.dbf' to '+DATA/test/DATAFILE/USERS.623.831847799'; 
• Change temp file location(example commands)
  alter database rename file '/u10/oradata/test/temp01.dbf' to '+DATA/test/tempfile/TEMP.849.824255731';    
• Change temp file location(example commands)
  alter database rename file '/u10/oradata/test/temp01.dbf' to '+DATA_test/lglholdu/tempfile/TEMP.849.824255731';    
• Change redo log location  (example commands)
• alter database clear logfile group 6;
• alter database drop logfile group 6;
• alter database add logfile thread 1 group 6;
• select group#,member from v$logfile;
• select group#, thread#, sequence#, status from v$log;
• Catalog start with '/u03/backup';
• Recover database;
• Start MRP
• Switch logfile in Primary to make  current redo log group of standby switch as well
• Drop and recreate last current redo logs

A Simple Way To Find Which process Is Taking Big Swap

Run top then press 'O' (capital letter o) followed by 'p' then 'enter'. Now processes should be sorted by their swap usage.

How To Debug DataGuard broker dgmgrl ORA-16664

After you setup dg broker in your enviroment, all TNS are correct, ports are open, sqlplus sys as sysdba is fine. password file are identical. You still see below error on DRC logs

ORA-16664: unable to receive the result from a database

Reason:

   It is possible dg broker ignore the changes you made on tnsnames.ora. It stores its own connect string when you set up dg broker at the beginning

Solution:

   Turn on trace to support .
   dgmgrl>EDIT CONFIGURATION set property tracelevel=support;
   dgmgrl>EDIT INSTANCE "COMMPRDA1" ON DATABASE COMMPRDA set property LogArchiveTrace=8191;

   Find out exact connect string it is using via drc logs. Test the string on sqlplus

   In my case, the special listener we created before for dataguard which confuses dg broker to create its own connect string. Fix it via update your listener.ora

  Or staticconnectidentifier
  dgmgrl>edit instance 'CONTNTA1' on database contntadc set property staticconnectidentifier='TNS string';

 Turn off trace.
dgmgrl>EDIT CONFIGURATION set property tracelevel=user;
dgmgrl>EDIT INSTANCE "COMMPRDA1" ON DATABASE COMMPRDA set property LogArchiveTrace=0;