Analyzing why Oracle archive log takes a lot of space

Problem

Oracle archivelog grows extensively. Need to figure out what objects are being changed most frequently.

Solution

Run following statement to get statistics by snapshot date, object name and max number of changes.

SELECT to_char(begin_interval_time,'YYYY-MM-DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2020-05-22 17','YYYY-MM-DD HH24')
                                           AND to_date('2020-05-22 21','YYYY-MM-DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY-MM-DD HH24:MI'),
           dhsso.object_name order by maxchages asc;

In order to find out SQL statements that cause these changes, please use following query:

SELECT to_char(begin_interval_time,'YYYY-MM-DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%<your object name>%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
 AND begin_interval_time BETWEEN to_date('2020-01-10 01','YYYY-MM-DD HH24') AND to_date('2020-01-20 21','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;

Complete explanation can be found at great article: http://cheatsheet4oracledba.blogspot.com/2013/05/huge-archive-log-generation-what-to-do.html

Convert tab delimited file into comma delimited on Mac

Problem

Need to convert tab delimited file to comma delimited on Mac.

Solution

Use sed utility, note that standard version doesn't understand \t, so you can either generate tab symbol by pressing Ctrl+V and Tab or install posix sed.

sed 's/ /,/g' <source file> > <target file>

Simple build script that works in Windows and Linux

Problem

Have two development environments:

Laptop
MacOS, Visual Studio Professional for Mac, .NET Core 3.0

Desktop
Windows 10, Visual Studio Professional for Windows, .NET Core 3.0

Need to write a script that will be working on Windows (.bat file) and MacOS (bash). 

For example sake, scripting languages or msbuild conditions should not be used.

Solution

Never thought that it's possible, definitely not an option for complex scenarios, but for basic commands go following (assuming that script build_webpack.cmd).

echo off

goto(){
#!/bin/bash
NODE=`which node`
cd "$(dirname "$0")/.."
$NODE ./node_modules/webpack/bin/webpack.js --config webpack.config.vendor.js $@
}

goto $@
exit

:(){
SET mypath=%~dp0
CD "%mypath:~0,-1%\\.."
node node_modules\\webpack\\bin\\webpack.js --config webpack.config.vendor.js %*

Now, if we need to invoke this script during project build, add the following line:

    <Exec Command="ClientApp/scripts/build_webpack.cmd --env.dev" />

Detailed explanation can be found at: https://nastytester.com/posts/script-that-works-in-windows-and-linux.html

How to find out what processes were killed by OOM on Linux

Problem

Services with high memory utilization are dying sporadically.

Solution

Run the following command to get a list of processes killed by oom.

dmesg | egrep -i 'killed process'

Useful articles:

1. How to troubleshoot OOM
https://access.redhat.com/solutions/2612861

2. How to add swap file to Linux Azure VM
https://support.microsoft.com/en-us/help/4010058/how-to-add-a-swap-file-in-linux-azure-virtual-machines

vi /etc/waagent.conf

ResourceDisk.Format=y
ResourceDisk.EnableSwap=y
ResourceDisk.SwapSizeMB=xx

service walinuxagent restart

How to find out how long the process has been running on Linux

Problem

Need to find out how long process is running

Solution

In order to get time how long process is running, execute following command;

ps -o etime= -p <your pid>

Azure VM is not accessible /proc/net/route contains no routes

Problem

Oversudden Azure VMs running Ubuntu 18LTS became unreachable (WestUS region)
/proc/net/route contains no routes


Solution

Connected to Azure VM via Console and got following running lines of error messages

2019/05/30 18:24:20.747552 ERROR ExtHandler /proc/net/route contains no routes

Restart waagent service produced the following:

INFO Daemon Azure Linux Agent Version:2.2.32.2
2019/05/30 18:24:18.242022 INFO Daemon OS: ubuntu 18.04
2019/05/30 18:24:18.246632 INFO Daemon Python: 3.6.7
2019/05/30 18:24:18.251476 INFO Daemon Add daemon process pid 2330 to walinuxagent systemd cgroup
2019/05/30 18:24:18.258645 INFO Daemon CGroups: ok
2019/05/30 18:24:18.263173 INFO Daemon Run daemon
2019/05/30 18:24:18.268142 INFO Daemon Clean protocol
2019/05/30 18:24:18.272685 INFO Daemon Provisioning already completed, skipping.
2019/05/30 18:24:18.277567 INFO Daemon RDMA capabilities are not enabled, skipping
2019/05/30 18:24:18.285862 INFO Daemon Determined Agent WALinuxAgent-2.2.40 to be the latest agent
2019/05/30 18:24:18.613217 INFO ExtHandler Agent WALinuxAgent-2.2.40 is running as the goal state agent
2019/05/30 18:24:18.635339 INFO ExtHandler Detect protocol endpoints
2019/05/30 18:24:18.642625 INFO ExtHandler Clean protocol
2019/05/30 18:24:18.651472 INFO ExtHandler WireServer endpoint is not found. Rerun dhcp handler
2019/05/30 18:24:18.658883 INFO ExtHandler Test for route to 168.63.129.16
2019/05/30 18:24:18.665334 WARNING ExtHandler No route exists to 168.63.129.16
2019/05/30 18:24:18.670932 INFO ExtHandler Checking for dhcp lease cache
2019/05/30 18:24:18.675843 INFO ExtHandler looking for leases in path [/var/lib/dhcp/dhclient.*.leases]
2019/05/30 18:24:18.684096 INFO ExtHandler cached endpoint not found
2019/05/30 18:24:18.690504 INFO ExtHandler Cache exists [False]
2019/05/30 18:24:18.695994 INFO ExtHandler Send dhcp request
2019/05/30 18:24:18.700569 INFO ExtHandler Examine /proc/net/route for primary interface
2019/05/30 18:24:18.705882 ERROR ExtHandler /proc/net/route contains no routes
2019/05/30 18:24:18.710763 WARNING ExtHandler Could not determine primary interface, please ensure /proc/net/route is correct
2019/05/30 18:24:18.717827 WARNING ExtHandler Contents of /proc/net/route:
Iface   Destination     Gateway         Flags   RefCnt  Use     Metric  Mask            MTU     Window  IRTT

2019/05/30 18:24:18.732775 WARNING ExtHandler Primary interface examination will retry silently
2019/05/30 18:24:20.747552 ERROR ExtHandler /proc/net/route contains no routes
2019/05/30 18:24:22.755975 ERROR ExtHandler /proc/net/route contains no routes

VM reboot didn't help, but VM instance stop/start helped.

References
https://bugs.launchpad.net/ubuntu/+source/linux-azure/+bug/1822133
https://github.com/Azure/WALinuxAgent/issues/980
https://github.com/Azure/WALinuxAgent/issues/1439

Tibco Jaspersoft throws error java.lang.NoClassDefFoundError: Could not initialize class java.awt.Color

Problem

Tibco Jaspersoft Report server throws java.lang.NoClassDefFoundError: Could not initialize class java.awt.Color when attempting to render report containing graphical elements.

Solution

Check missing dependencies using following command:

ldd /opt/jasperreports-server-7.1.1/java/lib/amd64/libawt_xawt.so

Results:

        linux-vdso.so.1 (0x00007ffff17f4000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f346bc2f000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f346b891000)
        libawt.so => /opt/jasperreports-server-7.1.1/java/lib/amd64/libawt.so (0x00007f346b5bf000)
        libXext.so.6 => /usr/lib/x86_64-linux-gnu/libXext.so.6 (0x00007f346b3ad000)
        libX11.so.6 => /usr/lib/x86_64-linux-gnu/libX11.so.6 (0x00007f346b075000)
        libXrender.so.1 => not found
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f346ae71000)
        libXtst.so.6 => not found
        libXi.so.6 => not found
        libjava.so => /opt/jasperreports-server-7.1.1/java/lib/amd64/libjava.so (0x00007f346ac45000)
        libjvm.so => not found
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f346a854000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f346c0a7000)
        libjvm.so => not found
        libxcb.so.1 => /usr/lib/x86_64-linux-gnu/libxcb.so.1 (0x00007f346a62c000)
        libjvm.so => not found
        libverify.so => /opt/jasperreports-server-7.1.1/java/lib/amd64/libverify.so (0x00007f346a41d000)
        libXau.so.6 => /usr/lib/x86_64-linux-gnu/libXau.so.6 (0x00007f346a219000)
        libXdmcp.so.6 => /usr/lib/x86_64-linux-gnu/libXdmcp.so.6 (0x00007f346a013000)
        libjvm.so => not found
        libbsd.so.0 => /lib/x86_64-linux-gnu/libbsd.so.0 (0x00007f3469dfe000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f3469bf6000)

Install missing packages using following command, libjvm.so can be ignored.

sudo apt-get install libfontconfig1 libxrender1 libxi6 libxtst6

Restart Jasper Report server using commands

sudo /opt/jasperreports-server-7.1.1/ctlscript.sh stop
sudo /opt/jasperreports-server-7.1.1/ctlscript.sh start