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;

A simple command to check which process takes OS memory in linux

ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS