KSar: Generating Graphs from SAR Reports

We all know the SAR (System Activity Report), however sometimes it’s dificult to visualize a large amount of data or even extract some long term meaningful information.
How wonderful would be to have a graphical visualization from this data? Well, it’s pretty simple using KSAR.

KSAR is a BSD licensed Java based application to create graph of all parameters from the data collected by Unix sar utilities and can be exported to PDF, JPG, PNG, CSV, TXT and others.
The project Codes are here. The latest Version is KSar2-0.0.4.

See below an I/O Graph from month of Dec, generated from a database server, as an example:

GrepOra-srv.jpg

To use it, first thing is to have SAR data. To get it we have basically 3 options:
A. Collect from current server.
B. Extract from other server using direct SSH connection.
C. Use a Generated SAR File
D. Run Java tool from Client Server.

Personally, I prefer to use option C, in order to avoid putting any code in client servers and also work in less intrusive mode as possible.
I also don’t use option B because we don’t usually have direct connection to client server, but sometimes with jumpboxes or similar.
There is a third reason: When Chosing option A or B, it’s automatically connected only daily data, but when using C, you can put all data you need. It need only to be available on server.

For reference regarding Option D, please check this link.

By the way, some other useful information about SAR:
1. SAR Collection Jobs can be checked on /etc/cron.d/sysstat
2. SAR Retention can be checked/adjusted on /etc/sysconfig/sysstat

Ok, now how to generate the SAR Files?
Using command: sar -A

Example:

[root@grepora-srvr ~]# cd /var/log/sa/
[root@grepora-srvr sa]# ls -lrt |tail -10
total 207080
-rw-r--r-- 1 root root 3337236 Dec 24 23:50 sa24
-rw-r--r-- 1 root root 3756100 Dec 24 23:53 sar24
-rw-r--r-- 1 root root 3337236 Dec 25 23:50 sa25
-rw-r--r-- 1 root root 3756113 Dec 25 23:53 sar25
-rw-r--r-- 1 root root 3337236 Dec 26 23:50 sa26
-rw-r--r-- 1 root root 3756104 Dec 26 23:53 sar26
-rw-r--r-- 1 root root 3337236 Dec 27 23:50 sa27
-rw-r--r-- 1 root root 3756096 Dec 27 23:53 sar27
-rw-r--r-- 1 root root 3337236 Dec 28 23:50 sa28
-rw-r--r-- 1 root root 3756100 Dec 28 23:53 sar28
-rw-r--r-- 1 root root 2317668 Dec 29 16:30 sa29
[root@grepora-srvr sa]# sar -A -f sa29 > sa29.txt
[root@grepora-srvr sa]# cat sa29.txt |head -10
Linux 3.8.13-118.4.2.el6uek.x86_64 (grepora-srvr) 12/29/2017 _x86_64_ (40 CPU)
12:00:01 AM CPU %usr %nice %sys %iowait %steal %irq %soft %guest %idle
12:10:01 AM all 97.74 0.00 1.71 0.01 0.00 0.00 0.52 0.00 0.02
12:10:01 AM 0 96.46 0.00 2.59 0.02 0.00 0.00 0.92 0.00 0.01
12:10:01 AM 1 98.55 0.00 1.24 0.01 0.00 0.00 0.20 0.00 0.00
12:10:01 AM 2 97.83 0.00 2.04 0.01 0.00 0.00 0.11 0.00 0.02
12:10:01 AM 3 98.44 0.00 1.41 0.01 0.00 0.00 0.14 0.00 0.01
12:10:01 AM 4 98.28 0.00 1.65 0.00 0.00 0.00 0.06 0.00 0.01
12:10:01 AM 5 98.27 0.00 1.70 0.00 0.00 0.00 0.02 0.00 0.00
[root@grepora-srvr sa]#

With this file you can copy it from client server your server and import using KSAR Interface. It’s pretty intuitive and easy to use.

But how to generate all available days or a set of specific days in past?
Here is a script I use for this:

### All Days of SAR
DT=$(ls /var/log/sa/sa[0-9][0-9] | tr '\n' ' ' | sed 's/\/var\/log\/sa\/sa/ /g')
## Explicit Days
#DT="07 08 09"
#DT="12"
# Today
#DT=`date +"%d"`
>/tmp/sar-$(hostname)-multiple.txt
for i in $DT; do
LC_ALL=C sar -A -f /var/log/sa/sa$i >> /tmp/sar-$(hostname)-multiple.txt
done
ls -l /tmp/sar-$(hostname)-multiple.txt

After this you can copy the generated file to you PC and generate the same report.

Hope you enjoy it!

Cheers!
Matheus.

DFS Lock Handle During RMAN Backup on Standby Database

Hi all!
Some time ago I faced some session on wait event DFS Lock Handle during a RMAN Backup on Standby Database. Btw, running backup on a Standby is a very interesting approach to avoid running backups on primary, so all nodes can be fully dedicated to application ends.

Turn that in my situation I noticed there was a lock with my apply process. The fix? Quite simple: Cancel apply process, run backup, and restart apply again. In my case, using DG Broker:

dgmgrl
connect / 
show configuration
show database 
edit database  set state = 'apply-off';

— Run Backup

edit database set state = 'apply-on';

 

Hope it helps!
Cheers!

SYSTEMSTATE DUMP

Hi guys!
I already posted about Hang Analyze (part1, part2) and ASHDUMP. Now, in the same ‘package’, let me show you about SYSTEMSTATE DUMP.

Systemstate is basically made by the process state for all process in instance (or instances) at the time the systemstate is called.
Through a systemstate it’s possible to identify enqeues, rowcache locks, mutexes, library cache pins and locks, latch free situations, and other kind of chains.

It’s a good tool to add in a SR, but it’s quite hard to habituate on reading/interpreting the file. To undertand exactly how to read a systemstate I’d recommend you the best: Read the manual!
The doc Reading and Understanding Systemstate Dumps (Doc ID 423153.1) has a very good explanation with examples, I’m not able to to it better. ūüôā

What I can do is share about the SYSTEMSTATE levels. I had some difficult to find it…
But before I show how you can do it, it‚Äôs important to mention that Oracle does not recommend you to use ‚Äėnumeric events‚Äô without a SR (MOS), according to Note: 75713.1.

More“SYSTEMSTATE DUMP”

ASHDUMP for Instance Crash/Hang ‘Post Mortem’ Analysis

Hi guys!
In the last weeks¬†I talked about ASHDUMP in the post HANGANALYZE Part 1. Let’s think¬†about it now…

Imagine the situation: The database is hanging, you cannot find what is going on and decided to restart the database OR your leader/boss yelled to you do it so, OR you know the database is going do get down, anyway…
Everyone has passed by this kind of situation at least once. After restart everything become OK and the ‘problem’ was solved. But now you are being asked about RCA (what caused this situation?). The database was hanging, so no snap was closed and you lost the ASH info…

For this cases I think is very useful to take 1 minute before database get down to generate an ASHDUMP. It’s very simple:

More“ASHDUMP for Instance Crash/Hang ‘Post Mortem’ Analysis”

HANGANALYZE Part 2

Hi!
See the first part of this post here: HANGANALIZE Part 1.

This post is just complement with a little kludge I liked…
First, let’s remmember that the hanganalyze is used when you are if some hanging in your environment, of course.

But what if you are having difficult to access the database, even with ‘/ as sysdba’?

You can create a ‘preliminary connection’ without create a session, like this:

sqlplus -prelim / as sysdba

This ‘feature’ is available since Oracle 10g, and it basically skips a session creation part (which could block) when logging on as SYSDBA.

More“HANGANALYZE Part 2”

HANGANALYZE Part 1

Hi all!
I realized I have some posts about database hangs but have no posts about hanganalyze, system state or ashdump usage. So let’s fix it. ūüôā
To organize the ideas I’m going to split the subject on three posts. This first will be about hanganalyse.

See the second part of this post here: HANGANALIZE Part 2.

Ok, so let me refer the most clear Oracle words I could found:
“Hanganalyze tries to work out who is waiting for who by building wait chains, and then depending on the level will request various processes to dump their errorstack.”

This is very similar to what we can do manually through v$wait_chains. But is quicker and ‘oficial’, so let’s use! ūüėÄ

But before I show how you can do it, it’s important to mention that Oracle does not recommend you to use ‘numeric events’ without a SR (MOS), according to Note: 75713.1.

So, how to do it? Basically 2 ways:

1) ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level LL'; OR EVENT="60 trace name HANGANALYZE level 5"
2) ORADEBUG hanganalyze LL

More“HANGANALYZE Part 1”

Backup Not Backuped Archivelogs and Delete Input

Hi all!
Sometimes you are caught in a situation where your database is not backuping archivelogs and need to generate a quick backup commands for those are not backuped yet and deleting it, right?
I saw this situation in this archived discussion at OTN. Unfortunately I couldn’t give my answer… But it’s how I do:

select
'backup archivelog from sequence '||sequence#||' until sequence '||sequence#||' thread '||thread#||' filesperset=1 delete input;',first_time
from v$archived_log where backup_count=0 and name is not null
order by first_time desc;

It generates an output like:

greporadb> select
  2  'backup archivelog from sequence '||sequence#||' until sequence '||sequence#||' thread '||thread#||' filesperset=1 delete input;',first_time
  3  from v$archived_log where backup_count=0 and name is not null
  4  order by first_time desc;
'BACKUPARCHIVELOGFROMSEQUEN                                                                                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
backup archivelog from sequence 152153 until sequence 152153 thread 1 filesperset=1 delete input;                                                                       
backup archivelog from sequence 152152 until sequence 152152 thread 1 filesperset=1 delete input;                                                                       
backup archivelog from sequence 152151 until sequence 152151 thread 1 filesperset=1 delete input;                                                                       
3 rows selected.

And be happy!

But an observation! It not works this way for databases with dataguard. For these cases you’ll need to add “and name’&dgname’” at select where clause… ūüėČ

See ya!
Matheus.

PL/SQL Developer Taking 100% of Database CPU

When using PL/SQL Developer (Allround Automations), a internal query is taking a lot of cpu cycles on database server (100% of a CPU).
Is this your problem? Please check if the query is like this:

select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')

It’s caused by the Describe Context Option of Code Assistant. To disable it:
Tools > Preferences > Code Assistant and disable the “Describe Context” option.

More“PL/SQL Developer Taking 100% of Database CPU”

TPS Chart on PL/SQL Developer

Hi all,
Since last post, some people asked me about how to make the charts using PL/SQL Developer. It basically works for every kind of query/data, like MS Excel.
I’d recommend you to use with historic data, setting time as “X” axis.

Here the example for the post Oracle TPS: Evaluating Transaction per Second:

PLSQL_Graph

And get:

7days_tps

PL/SQL Developer  is a commercial tool of Allround Automations.
You can access more information about licensing here.

Have a nice day!
Matheus.

Oracle Database: Compression Algorithms for Cloud Backup

Hi all!
Again talking about cloud backups for on-premise databases: An important aspect is to compress the data, so network consumption might be reduced once less data is being transfered.Compress_PDF
It’s also important to evaluate CPU consumption. As higher compress algorithm is, as much CPU it uses. So, pay attention!

Now, how to choose the compression algorithm? Here the options Oracle give us:

SQL> col ALGORITHM_NAME for a15
set line 200
SQL> select ALGORITHM_NAME,INITIAL_RELEASE,TERMINAL_RELEASE,ALGORITHM_DESCRIPTION,ALGORITHM_COMPATIBILITY  from v$rman_compression_algorithm;
ALGORITHM_NAME INITIAL_RELEASE    TERMINAL_RELEASE   ALGORITHM_DESCRIPTION                                            ALGORITHM_COMPATIB
-------------- ------------------ ------------------ ---------------------------------------------------------------- ------------------
BZIP2          10.0.0.0.0         11.2.0.0.0         good compression ratio                                           9.2.0.0.0
BASIC          10.0.0.0.0                            good compression ratio                                           9.2.0.0.0
LOW            11.2.0.0.0                            maximum possible compression speed                               11.2.0.0.0
ZLIB           11.0.0.0.0         11.2.0.0.0         balance between speed and compression ratio                      11.0.0.0.0
MEDIUM         11.2.0.0.0                            balance between speed and compression ratio                      11.0.0.0.0
HIGH           11.2.0.0.0                            maximum possible compression ratio                               11.2.0.0.0

How to identify our compression algorithm?

RMAN> show COMPRESSION ALGORITHM;
RMAN configuration parameters for database with db_unique_name EZM_PRFL are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

And how to change it?

RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH';
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> show COMPRESSION ALGORITHM;
RMAN configuration parameters for database with db_unique_name EZM_PRFL are:
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

Ok,
But how to evaluate my compression ratio?

See the difference between INPUT_BYTES_DISPLAY and OUTPUT_BYTES_DISPLAY columns from the query:

prddb> col STATUS for a10
prddb> col INPUT_BYTES_DISPLAY for a15
prddb> col OUTPUT_BYTES_DISPLAY for a15
prddb> col TIME_TAKEN_DISPLAY for a20
prddb> SELECT SESSION_KEY,
2         INPUT_TYPE,
3         STATUS,
4         TO_CHAR(START_TIME, 'mm/dd/yy hh24:mi') start_time,
5         TO_CHAR(END_TIME, 'mm/dd/yy hh24:mi') end_time,
6  --       ELAPSED_SECONDS / 3600 hrs,
7         COMPRESSION_RATIO,
8         INPUT_BYTES_DISPLAY,
9         OUTPUT_BYTES_DISPLAY,
10         TIME_TAKEN_DISPLAY
11    FROM V$RMAN_BACKUP_JOB_DETAILS
12    where input_type like 'DB%'
13   ORDER BY SESSION_KEY
14  /SESSION_KEY INPUT_TYPE    STATUS     START_TIME     END_TIME       COMPRESSION_RATIO INPUT_BYTES_DIS OUTPUT_BYTES_DI TIME_TAKEN_DISPLAY
----------- ------------- ---------- -------------- -------------- ----------------- --------------- --------------- --------------------
          2 DB FULL       COMPLETED  04/22/16 12:59 04/22/16 13:06        6,84838963     4.26G         636.50M       00:06:57
          9 DB FULL       COMPLETED  04/22/16 13:47 04/22/16 13:54        6,83764706     4.26G         637.50M       00:06:37
         14 DB FULL       COMPLETED  04/22/16 16:26 04/22/16 16:33        6,84189878     4.26G         637.25M       00:06:48

KB: https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmconfa.htm#BRADV89466

Done?
If you have any question, please let me know in the comments! ūüėČ
Matheus.