qperf - Monitoring network bandwidth and latency

 

I will use qperf: https://docs.oracle.com/cd/E86824_01/html/E54763/qperf-1.html

You can check a detailed example here: https://access.redhat.com/solutions/2122681

In destination machine you need to run the following command:

qperf &

You need to open ports 19765 and 19766 in this machine.

In source machine you need to create the following script, in my case, network-monitoring.sh:

#!/bin/sh

echo "$(date +'%Y/%m/%d %H:%M:%S') comprobando red"
HOSTNAME=$(hostname)
DESTINO=[destinationMachine]

ANCHO_BANDA=$(qperf -v -ip 19766  -t 5 --use_bits_per_sec  $DESTINO  tcp_bw | grep bw | awk '{print $3";"$4}' | sed -n '2 p')
LATENCIA=$(qperf -v -ip 19766  -t 5 --use_bits_per_sec  $DESTINO  tcp_lat | grep latency | awk '{print $3";"$4}')


echo "$(date +'%d/%m/%Y');$(date +'%H:%M:%S');$HOSTNAME;$DESTINO;$ANCHO_BANDA;$LATENCIA" >> /root/network-monitoring.csv

 

Oracle - Count all rows in all tables in a database

 Count all rows in all tables in a database

CREATE GLOBAL TEMPORARY TABLE TMP_TOTALES (
  TABLA VARCHAR2(32),
  TOTAL NUMBER
)
ON COMMIT DELETE ROWS;
         
         

set serveroutput ON;
DECLARE        
          total NUMBER:=NULL;
          cursor tablas_cursor IS select TABLE_NAME from all_Tables where owner = 'myUser' AND NOT table_name like '%$%' ORDER BY TABLE_NAME ASC;
           
          v_sqltxt        varchar2(32767);
          no_privs        exception;
         pragma exception_init(no_privs, -1031);
 BEGIN
     FOR tabla IN tablas_cursor loop
        v_sqltxt:='select count(1) from '|| tabla.TABLE_NAME ||'';      
        BEGIN
          EXECUTE immediate v_sqltxt INTO total;
          INSERT INTO TMP_TOTALES(TABLA, TOTAL) VALUES (tabla.TABLE_NAME, total);
          dbms_output.put_line(tabla.TABLE_NAME || ' ' || total);                  
         END;     
      END loop;        
END;
/


select * from TMP_TOTALES ORDER BY TOTAL DESC;

Oracle DB - Check tablespaces usage

 Tablespaces and datafiles usage:

select
    DTFIL.tablespace_name, DTFIL.file_name,
    ROUND(DTFIL.bytes/power(1024,2),2) USED_MB,
    DTFIL.autoextensible,
    Round(DTFIL.maxbytes/power(1024,3), 2) Maxsize_GB,
    Round((DTFIL.bytes / DTFIL.maxbytes)*100, 2) USED,
    round((DTFIL.increment_by * TBSP.block_size)/power(1024,2), 2) step_MB,
    DTFIL.increment_by
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
order by DTFIL.tablespace_name, DTFIL.file_name;

Tablespaces usage, including datafiles:

select
    DTFIL.tablespace_name,
    Round((sum(DTFIL.bytes) / sum(DTFIL.maxbytes))*100, 2) USED
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
group by DTFIL.tablespace_name
order by 2 desc;

Tablespaces usage acording with actual allocated space:

-- Work tablespaces
SELECT
       a.tablespace_name AS tablespace_name,
       (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100)) percent_used,
       (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100, 2)) pct_used,
       round(a.bytes_alloc / 1024/ 1024,2) allocated,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024/ 1024,2) used,      
       round(nvl(b.bytes_free, 0) / 1024/ 1024,2) free,
       a.datafiles
from (select
        f.tablespace_name,
        sum(f.bytes) bytes_alloc,
        sum(decode(f.autoextensible, 'yes', f.maxbytes, 'N0',f.bytes)) maxbytes,
        count(1) datafiles
      FROM
        dba_data_files f
      GROUP BY tablespace_name) a,
    (SELECT 
        f.tablespace_name,
        SUM(f.bytes) bytes_free
     FROM
        dba_free_space f
     GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name(+)
 
UNION ALL
 
-- TEMP Tablespaces
select
    t.tablespace_name,
    t.percent_used,
    t.pct_used,
    t.allocated,
    t.used,
    t.free,
    f.datafiles
from
    (select
        h.tablespace_name as tablespace_name,
        (100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))* 100)) percent_used,
        (100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))* 100,2)) pct_used,
        round(sum(h.bytes_free + h.bytes_used) / 1048576,2) allocated,
        round(sum(nvl(p.bytes_used, 0))/ 1048576,2) used,
        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576,2) free
    from
        sys.gv_$TEMP_SPACE_HEADER h,
        sys.gv_$Temp_extent_pool p,
        dba_temp_files f
        where
            p.file_id(+) = h.file_id
            and p.tablespace_name(+) = h.tablespace_name
            and f.file_id = h.file_id
            and f.tablespace_name = h.tablespace_name
        group by h.tablespace_name ) t,
    (select tablespace_name, count(1) datafiles from dba_temp_files group by tablespace_name ) f
where
    t.tablespace_name = f.tablespace_name
ORDER BY 3 desc

Oracle DB - SQLPLUS connect

Setup

System properties configuration

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

Connection

Connect with tnsnames from command line.

sqlplus username/pass@'(description=(address=(protocol=tcp)(host=myServer)(port=1521))(connect_data=(sid=mySID)))'

Connect with tnsnames from file

sqlplus username/pass@ORCL

Where tnsnames.ora file is as follows.

ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myServer)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myService)
    )
  )

Run SQL file

Run SQL from command line

sqlplus username/pass@'(description=(address=(protocol=tcp)(host=myServer)(port=1521))(connect_data=(sid=mySID)))' @myFile.sql

SQL file

set linesize 100 pagesize 50

select
    DTFIL.tablespace_name,
    Round((sum(DTFIL.bytes) / sum(DTFIL.maxbytes))*100, 2) USED
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
group by DTFIL.tablespace_name
order by 2 desc;

exit;

CentOS check system info

Show system inf for a CentOS server:

# CPU info
 more /proc/cpuinfo

# Memory info
 dmidecode -t memory
 dmidecode -t memory | grep -i "installed size"

# Operatin System
 cat /etc/os-release
 hostnamectl
 uname -r