GGS_Heartbit enable trigger on replicator when using DBOPTIONS SUPPRESSTRIGGER

Below procedure on DB level on replication side can make trigger fires on below two table which is important  for Golden Gate monitoring when we have disable trigger on DB side


DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('GGS_ADMIN','GGS_HEARTBEAT','FALSE')
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('GGS_ADMIN','GGS_HEARTBEAT_HISTORY','FALSE')

Useful one liner awk, perl, sed

Very useful handy 1 liners needed to be successful, good Administrator. Its very handy to troubleshoot issue Smartly.

Sed (stream editor): Sed id a stream editor is used to perform basic text transformations on an input stream (a file or input from a pipeline).

AWK (Aho, Weinberger, and Kernighan):  is a programming language designed for text processing and typically used as a data extraction and reporting tool. It is a standard feature of most Unix-like operating system

Perl (Practical Extraction and Report Language):

Perl is a powerful text processing facility provider that makes report processing easier. 


Below few one liner we can use in day to day Linux shell programming and some string management:


1) find and Replace in file
perl -pi -e 's/first/second/g' file1 file2 file3

2) find and replace only on line matching word "single"
perl -pi -e 's/first/second/g if /single/' file

perl -ne 'print if $a{$_}++' file

3) Print the N records after some pattern:

awk 'c&&c--;/pattern/{c=N}' file


4) Kill all process matching pattern

kill -9 $(ps -ef|grep search_string|grep -v grep|awk -F " " '{ print $2 }')

kill -9 `ps -ef |awk ' /search_string/ {print $2} '`

5) delete ALL blank lines from a file (same as "grep '.' ")
 awk NF
 awk '/./'

6) remove duplicate, nonconsecutive lines
 awk '!a[$0]++'                     # most concise script
 awk '!($0 in a){a[$0];print}'      # fast

7) print the line immediately after a regexp, but not the line
 # containing the regexp
 sed -n '/regexp/{n;p;}'

7) print 1 line of context before and after regexp, with line number
 # indicating where the regexp occurred (similar to "grep -A1 -B1")
 sed -n -e '/regexp/{=;x;1!p;g;$!N;p;D;}' -e h


8) print section of file based on line numbers 
 sed -n '18,121p'               # way 1
 sed '18,121!d'                 # way 2

9) awk condition Check
YESTERDAY=$(date --date "1 days ago" +%d%m%y)
DATAFILE=visitor_log_$YESTERDAY.txt
#!/usr/bin/expect --
#cat $DATAFILE > VISITOR_LOG_BIG
cat DILIP_log.bad >> ALL_DILIP_log.bad

awk -F '|' '{if((length($1)==12)&&($1>910000000000)&&($1<=919999999999)) print $1,"|",$2,"|",$9}' $DATAFILE | sort | uniq >  VISITOR_LOG_BIG

SQL Wise CPU Usage

SQL Wise CPU Usage

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn
where   se.STATISTIC# = sn.STATISTIC#
and   NAME like '%CPU used by this session%'
and   se.SID = ss.SID
and   ss.status='ACTIVE'
and   ss.username is not null
order by VALUE desc;

col type for a10
select * from (
select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,
audit_actions aud
where SQL_ID is not NULL
and ash.sql_opcode=aud.action
and ash.sample_time > sysdate - &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
/

AWR Reports

Oracle support Various AWR report based on requirement one can use

1)  AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id
SQL> @?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

2) RAC Related awr Report

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

SQL> @?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)
SQL> @?/rdbms/admin/awrgdrpt.sql -- AWR Global Diff Report (RAC)
 
Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql -- Server Performance RAC report
SQL> @?/rdbms/admin/awrsqrpt.sql -- Standard SQL statement Report
SQL> @?/rdbms/admin/awrddrpt.sql -- Period diff on current instance
SQL> @?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)

Advance level SQL tuning one need to look into tkprof etc.

Tuning SQL not running as expected.

Scenario: 

Many times we see complains from application team, as till yesterday query execution was perfectly right and suddenly they started observing lag in execution or and some cases not even giving any result. When DBA check, mostly he see plan change and that is due many reasons. Below are few checks DBA can try to find why sql plan got changed. 

Though to Oracle Optimizer, you can just give hint if its a small query and you understand the execution flow as well as you know the data. But for many large queries its not possible to fix with hint, you have to live with Oracle Optimizer to decide the SQL plan. 

You can also forcefully map any plan to particular SQL ID, but that option need to consider carefully.

Why Plan got changed? or Why SQL is slow check few option mentioned below.

1) Check for any stale statistics.
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

2) Check any invalid index/Partition

col TABLE_NAME for a30
SYS@orcl>  select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');

no rows selected

SYS@orcl>  select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';

no rows selected

3) Check free memory shared pool area. Check too much hard parsing.

SYS@orcl> select * from (select SQL_ID,PARSING_SCHEMA_NAME, count(1) from v$sql group by SQL_ID,PARSING_SCHEMA_NAME order by  3 desc,2) where rownum<=10;

SQL_ID        PARSING_SCHEMA_NAME                     COUNT(1)
------------- ------------------------------ -----------------
9p6bq1v54k13j SYS                                            4
f8pavn1bvsj7t SYS                                            3
bgjhtnqhr5u9h SYS                                            3
ga9j9xk5cy9s0 SYS                                            3
05sghzkq6r6yv SYS                                            3
53saa2zkr6wc3 SYS                                            3
32bhha21dkv0v SYS                                            3
87gaftwrm2h68 SYS                                            3
b1wc53ddd6h3p SYS                                            3
asvzxj61dc5vs SYS                                            3

10 rows selected.

5) Wait/ Blocking analysis.

-- Display blocked session and their blocking session details.
SELECT sid, serial#, blocking_session_status, blocking_session
FROM   v$session
WHERE  blocking_session IS NOT NULL;


-- Display the resource or event the session is waiting for more than 1 minutes
SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM   v$session
where (seconds_in_wait/1000000) > 60
ORDER BY sid;

select sid,seq#,event,state,SECONDS_IN_WAIT from v$session_wait where SECONDS_IN_WAIT > 60;


--Monitor Top Waiting Event Using Active Session History (ASH)
SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC;

6) Tablespace Usage


--Monitor Overall Oracle Tablespace
SELECT d.STATUS "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
d.initial_extent "Initial Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;

7) Check for Memory parameters usage




--Estimation Of Shared Memory Pool Size vs. Time Saved
SELECT shared_pool_size_for_estimate "Pool Size (MB)",
estd_lc_size "Lib Cache Size (MB)",
estd_lc_time_saved/1000000 "Lib Cache Time Saved (Sec)"
FROM v$shared_pool_advice;


--Estimation Of Buffer Cache Size vs. Physical Reads
SELECT size_for_estimate "Cache Size (MB)",
buffers_for_estimate "Buffers",
estd_physical_read_factor "Estd Phys|Read Factor",
estd_physical_reads "Estd Phys| Reads"
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT VALUE FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

---10g onwards
SYS@orcl> ---10g onwards
SYS@orcl> --SGA Advisor
SYS@orcl> select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

         SGA_SIZE   SGA_SIZE_FACTOR      ESTD_DB_TIME
----------------- ----------------- -----------------
              512                 1              3405
              256                 1              3724
              384                 1              3489
              640                 1              3385
              768                 2              2583
              896                 2              2547
             1024                 2              2547

7 rows selected.

SYS@orcl> ---10g onwards
SYS@orcl> --SGA Advisor
SYS@orcl> select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

         SGA_SIZE   SGA_SIZE_FACTOR      ESTD_DB_TIME
----------------- ----------------- -----------------
              512                 1              3405
              256                 1              3724
              384                 1              3489
              640                 1              3385
              768                 2              2583
              896                 2              2547
             1024                 2              2547

7 rows selected.

SYS@orcl>
SYS@orcl> ---PGA Advisor
SYS@orcl> select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW
----------------------- ----------------- -------------------
               40370176                 0           157892608
               80740352                 0           157892608
              161480704                 1           157892608
              242221056                 1                   0
              322961408                 1                   0
              387553280                 1                   0
              452145152                 1                   0
              516738048                 2                   0
              581329920                 2                   0
              645922816                 2                   0
              968884224                 3                   0
             1291845632                 4                   0
             1937768448                 6                   0
             2583691264                 8                   0

14 rows selected.

8) Tuning SQL using 

SQL> @?/rdbms/admin/sqltrpt

9)  AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id
SQL> @?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

10) RAC Related awr Report

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

SQL> @?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)
SQL> @?/rdbms/admin/awrgdrpt.sql -- AWR Global Diff Report (RAC)
 
Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql -- Server Performance RAC report
SQL> @?/rdbms/admin/awrsqrpt.sql -- Standard SQL statement Report
SQL> @?/rdbms/admin/awrddrpt.sql -- Period diff on current instance
SQL> @?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)

9) To more analysis why plan has changed one can check support.oracle.com

SQL to check Stale statistics

Many times Oracle SQL plan get change which will cause abnormal behavior, most probable reason could be stale statistics marked by Oracle. Below is useful queries to identify the Objects and gather stats for those Objects. Oracle do have auto jobs which takes care stats gather requirement in defined Windows. One need to calibrate window based on requirement as stats gather as this is resource Intensive process, and should not conflict Business transaction. Oracle include this nightly maintenance Jobs by default scheduled in night time. Stats gather is vast topic, one need carefully touch stats gather on PROD environment.

---Using Anonymous PL/SQL block
SQL> SET SERVEROUTPUT ON
  DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

---Oracle 10g Onwards.

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

Perl Oracle DB connectivity and send mail

Perl Script Create report using mentioned Script and send mail to Team.

This need Sendmail to configured on servers along with SMTP details.

#!/usr/bin/perl

#

#use EXCEL;

#use MAIL;

#use CGI;

use DBI;

#use Spreadsheet::WriteExcel::Big;

$attachement=$ARGV[0];

#$subject_line=$ARGV[1];

#$ENV{ORACLE_HOME} = '/u01/app/oracle/product/9.2.0';  ##Linux

$ENV{ORACLE_HOME} = 'C:\app\Dilip\product\11.1.0\db_1';

$stsql = "select * from dba_segments where rownum<=10";

my $ORACLE_SID="orcl";

my $user = 'xxxx';

my $password = 'xxxxx';

#my $data_source = 'dbi:Oracle:ORCL';

my $data_source = 'dbi:Oracle:orcl';

my $dbh =  DBI->connect($data_source, $user, $password)

  || die "Can't connect to $data_source: $DBI::errstr";

$sth = $dbh->prepare("select * from  dba_segments where rownum<=10 and segment_type='TABLE'")

       || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

# Hash Table for column wise return

#while (my $r = $sth->fetchrow_hashref) {

   #print "$r->{OWNER}, $r->{SEGMENT_NAME}\n";

#

#}

open my $fh, '>>', $attachement or die "Could not open file Output.csv: $!";

### Retrieve the returned rows of data All Rows

  while ( @row = $sth->fetchrow_array(  ) ) {

     # print "Row: @row\n";

     #push(@row);

     #print $fh qq{$res->{'Name'}\t$res->{'CompanyName'}\n};

     print $fh qq{@row\n};

  }

$sth->finish;

$dbh->disconnect();

close $fh;

send_mail($attachement,$subject_line,"","xyz\@gmail.com","","");

sub get_data

{

  undef $/;

  open (my $QFH, "< summary.sql") or die "error can't open this file $!";

  my $sth= $dbh_oracle->prepare(<$QFH>) or

      die ("Cannot connect to the database: ".$DBI::errstr."\n");

  $sth->execute;

  close $QFH;

  my $row = $sth->fetchrow_hashref;

  $sth->finish;

  return @$row{'MYTABLE','FLAG'};

}

sub send_mail {

#my ($dbh,$SEP_COL,$querystr_v,$SHEET_NAME)=@_;

#my ($SEP_COL,$querystr)=@_;

my ($attachement,$subject_line,$message,$to,$cc,$bcc)=@_;

my %mime =('GZ',"application/x-gzip", );

                   my $mailprog = '/usr/sbin/sendmail -t';

                   #my $subject_line = "Mail Demo gip file";

                   open(MAIL, "|$mailprog -t ") || &Abort ("Error sending mail Error is $!");

                   print MAIL "To: $to\n";

                   #print MAIL "Bcc:  xxxx@xyz.com, xxxx\@xyz.com\n";

                   #print MAIL "From: mobile\@xyz.com\n";

                   print MAIL "Subject: $subject_line\n";

                   print MAIL "MIME-Version: 1.0\n";

                   #Part 2 starts

                   print MAIL "Content-Type: multipart/mixed; boundary=------------$boundary\n";

                   print MAIL "\n";

                   print MAIL "This is a multi-part message in MIME format.\n";

                   print MAIL "--------------$boundary\n";

                   print MAIL "Content-Type: text/html; charset=us-ascii\n";

                   print MAIL "Content-Transfer-Encoding: 7bit\n\n";

                   print MAIL "$message\r\n";

                   print MAIL "--------------$boundary\n";

                   print MAIL "Content-Type: name='$attachement'\n";

                   print MAIL "Content-Transfer-Encoding: base64\n";

                   print MAIL "Content-Disposition: attachment; filename=$attachement\n\n";

                   my $buf; $/=0;

                   open INPUT, "$attachement"; # should be readable, we checked above [-r]

                   binmode INPUT if ($^O eq 'NT' or $^O eq 'MSWin32');

                   while(read(INPUT, $buf, 60*57))

                   {

                   print MAIL &encode_base64($buf);

                   }

                   close INPUT;

                   print MAIL "\n--------------$boundary--\n";

                   print MAIL "\n";

                   close MAIL;

                   sub Abort {

                   my ($msg) = @_;

                   } # end of sub Abort...

                   sub encode_base64 #($)

                   {

                                   my ($res, $eol, $padding) = ("", "\n", undef);

                                   while (($_[0] =~ /(.{1,45})/gs))

                                   {

                                   $res .= substr(pack('u', $1), 1);

                                   chop $res;

                                   }

                                   $res =~ tr#` -_#AA-Za-z0-9+/#;                  # ` help emacs

                                   $padding = (3 - length($_[0]) % 3) % 3;         # fix padding at the end

                                   $res =~ s#.{$padding}$#'=' x $padding#e if $padding; # pad eoedv data with =s

                                   $res =~ s#(.{1,76})#$1$eol#g if (length $eol); # lines of at least 76 characters

                                   return $res;

                   }

#exit;

}

exit;

###For DBI refer http://oreilly.com/catalog/perldbi/chapter/ch04.html