Using logrorate in linux for oracle listener and alert logs

Do the following as root user

cd /etc/logrotate.d

vi oracle_listener

/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan3/trace/listener_scan3.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan2/trace/listener_scan2.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/11.2.0.4/grid/log/diag/tnslsnr/nasipexadb01/listener_scan1/trace/listener_scan1.log {
weekly
copytruncate
rotate 4
compress
}
/u01/app/oracle/diag/tnslsnr/nasipexadb01/listener/trace/listener.log {
weekly
copytruncate
rotate 4
compress
}

Refer logrorate manpage for more info on parameters.

The above does copy the log file to a diff name and zip it, then truncates the original files.

Rotation will be done weekly and 4 copies will be retained.

If you want to force rotation immediate run the below command

logrotate -f oracle_listener

Note: You may change permissions on the file to 777 just in case if you dont want to ask sys admin every time you want to edit the file

Hanganalyze and Systemstate collection during RAC hang

During hanging situation in database, you may do :

Collection commands for Hanganalyze and Systemstate: RAC

sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
SQL> oradebug tracefile_name

Then upload alert.log and in case hanging reoccur. 

[off topic] Thinkorswim scripts

MTD
plot mreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[21])/(close(period = AggregationPeriod.DAY)[21]),2);
;

WTD
plot wreturn =round(100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[5])/(close(period = AggregationPeriod.DAY)[5]),2);
;

1 year
plot yearreturn =100* (close(period = AggregationPeriod.DAY)-close(period = AggregationPeriod.DAY)[251])/(close(period = AggregationPeriod.DAY)[251]);
;

YTD

def startOfYear = GetYear() <> GetYear()[1];
rec startingClose = if startOfYear then close[1] else startingClose[1];
plot percentChange = 100 * (close / startingClose - 1);
percentChange.AssignValueColor(if percentChange > 1.0 then Color.BLACK else if percentChange < -1.0 then Color.BLACK else Color.CURRENT); AssignBackgroundColor(if percentChange > 1.0 then Color.GREEN else if percentChange < -1.0 then Color.RED else Color.CURRENT);


Study filter for scan


SV_1year_perf() is greater than or equal to 30 and SV_YTD_perf() is greater than or equal to 18

Find process using swap in linux

$top 
then press OpEnter
This will list process by swap usage. Hit C to show the full process name 

Or use this script to find. Got this script from below link. Credits to original writer

http://northernmost.org/blog/find-out-what-is-using-your-swap/

#!/bin/bash 
# Get current swap usage for all running processes
# Erik Ljungstrom 27/05/2011
# Modified by Mikko Rantalainen 2012-08-09
# Pipe the output to "sort -nk3" to get sorted output
# Modified by Marc Methot 2014-09-18
# removed the need for sudo

SUM=0
OVERALL=0
for DIR in `find /proc/ -maxdepth 1 -type d -regex "^/proc/[0-9]+"`
do
    PID=`echo $DIR | cut -d / -f 3`
    PROGNAME=`ps -p $PID -o comm --no-headers`
    for SWAP in `grep VmSwap $DIR/status 2>/dev/null | awk '{ print $2 }'`
    do
        let SUM=$SUM+$SWAP
    done
    if (( $SUM > 0 )); then
        echo "PID=$PID swapped $SUM KB ($PROGNAME)"
    fi
    let OVERALL=$OVERALL+$SUM
    SUM=0
done
echo "Overall swap used: $OVERALL KB"

View hidden parameters in oracle database 11g

select name,value from v$parameter where substr(name,1,1)='_';

Database resource manager oracle 11g

Using create_simple_plan procedure

This below query will automatically create resource manager plan, resource manager groups if they dont exist and assign directives to each group. First group will get level2 75% usage and second group will get 25% usage under contention.

Resource_manager_plan= EDW_PLAN
DEFAULT_CONSUMER_GROUP = 75%
GG_GROUP = 25%

begin
dbms_resource_manager.create_simple_plan(simple_plan=>'EDW_PLAN',
CONSUMER_GROUP1=>'DEFAULT_CONSUMER_GROUP',GROUP1_PERCENT=>75,
CONSUMER_GROUP2=>'GG_GROUP',GROUP2_PERCENT=>25);
END;
/

After this assign the users to their respective groups

BEGIN
    dbms_resource_manager_privs.grant_switch_consumer_group(
        grantee_name => 'SRAVAN',
        consumer_group => 'GG_GROUP',
        grant_option => TRUE
    );
END;
BEGIN
    dbms_resource_manager.set_initial_consumer_group(
        user => 'SRAVAN',
        consumer_group => 'GG_GROUP'
    );
END;


After this activate the resource manager plan

alter system set resource_manager_plan="EDW_PLAN";

You can verify and monitor DBRM using below queries

SELECT name, is_top_plan FROM v$rsrc_plan;

SELECT name, active_sessions, queue_length,consumed_cpu_time, cpu_waits, cpu_wait_time FROM v$rsrc_consumer_group;

SELECT se.sid sess_id, co.name consumer_group, se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time FROM v$rsrc_session_info se, v$rsrc_consumer_group co WHERE se.current_consumer_group_id = co.id;

SELECT sequence# seq, name plan_name,to_char(start_time, 'DD-MON-YY HH24:MM') start_time,to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name FROM v$rsrc_plan_history;

select sequence# seq, name, cpu_wait_time, cpu_waits,consumed_cpu_time from V$RSRC_CONS_GROUP_HISTORY;



*****************************************************************************

Normal procedure of creating resource manager plan

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.create_plan (
      plan                         => 'EDW_PLAN'
     ,mgmt_mth                     => 'EMPHASIS'
     ,active_sess_pool_mth         => 'ACTIVE_SESS_POOL_ABSOLUTE'
     ,parallel_degree_limit_mth    => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE'
     ,queueing_mth                 => 'FIFO_TIMEOUT'
     ,comment                      => 'Allocate 75% to default consumer group and 25% to golden gate user under contention
'
     ,sub_plan                      => FALSE);
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'OTHER_GROUPS'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 75
     ,mgmt_p3                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'OTHER_GROUPS Level 3'  );
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'SYS_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p1                      => 100
     ,switch_for_call              => FALSE
     ,comment                      => 'SYS Level 1'  );
  sys.dbms_resource_manager.create_consumer_group (
      consumer_group               => 'GG_GROUP'
     ,comment                      => 'Level 2 Group 2');
  sys.dbms_resource_manager.create_plan_directive (
      plan                         => 'EDW_PLAN'
     ,group_or_subplan             => 'GG_GROUP'
     ,switch_estimate              => FALSE
     ,mgmt_p2                      => 25
     ,switch_for_call              => FALSE
     ,comment                      => 'Level 2 Group 2'  );
  sys.dbms_resource_manager.submit_pending_area();
end;



Drop all the plans, plan directives, groups

begin
  sys.dbms_resource_manager.clear_pending_area();
  sys.dbms_resource_manager.create_pending_area();
  sys.dbms_resource_manager.delete_plan_cascade ('EDW_PLAN');
  sys.dbms_resource_manager.submit_pending_area();
end;
/

Note:

You can use toad or enterprise manager to to create, alter any resource plans in easier way if you feel the above code is complicated.

Please also make sure you modify default_maintenance_plan because this will be activated during automatic maintenance windows and if you did not include your allocations it will stick to default ones.

You can limit parallelism, CPU% and other parameters using DBRM


Switching user group

BEGIN
  SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  SYS.DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'SXXXX',
    consumer_group => 'BATCH_GROUP',
    grant_option   => FALSE);
  SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
BEGIN
  SYS.DBMS_RESOURCE_MANAGER.set_initial_consumer_group(
    user           => 'SXXXX',
    consumer_group => 'BATCH_GROUP');
END;
/

find inserts and updates count in oracle database

SELECT sum( stat.executions_delta ) insert_executions
  FROM dba_hist_sqlstat stat
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN TO_DATE('April 25, 2017, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.') AND TO_DATE('April 25, 2017, 11:50 A.M.','Month dd, YYYY, HH:MI A.M.')
   AND txt.command_type = 2;

SELECT sum( stat.executions_delta ) update_executions
  FROM dba_hist_sqlstat stat
       JOIN dba_hist_sqltext txt ON (stat.sql_id = txt.sql_id )
       JOIN dba_hist_snapshot snap ON (stat.snap_id = snap.snap_id)
 WHERE snap.begin_interval_time BETWEEN TO_DATE('April 25, 2017, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.') AND TO_DATE('April 25, 2017, 11:50 A.M.','Month dd, YYYY, HH:MI A.M.')
   AND txt.command_type = 6;