Data Scientist

Strata New York 2011: John Rauser, "What is a Career in Big Data?"

http://www.youtube.com/watch?feature=player_embedded&v=0tuEEnL61HM

Really cool video...

I liked mostly, the comparison Euler and Mayer, which seems to be living in same decades. Euler is Mathematician, Mayer is close to Engineering discipline. And the way they looked at the data.

Real World Performance seminar

Thanks TROUG - TuRkish Oracle User Group, giving us a chance for one day Real World Performance seminar by Andrew Holdsworth, Tom Kyte, and Graham Wood.

There is nothing to say about the guys, I appreciate for their efforts to come and share their knowledge.

You could find the slides, videos and TROUG announcement  from below external links:

Slides

DWH video

OLTP video

Announcement

Weekend in Antalya

We had been to Antalya, for 2 nights, annual meeting of my company, based on invention and simplicity was held.

"Steal" Jobs video shown by our CEO is impressive to me at first...
Steve Jobs interviewed just before returning to Apple
http://www.youtube.com/watch?v=SaJp66ArJVI&feature=youtube_gdata_player

But after a day passed, only impressive thing was "simply" :

Beautiful sand, shore, and sea....
waves, sand and sea shells...

Check out stellarium, star map application, which I had a chance to try it in Antalya's open sky, in which you could easily sea stars... A snap from application:

Distributed transaction error ORA-02049: time-out: distributed transaction waiting for lock

Problem

While working with dblinks, I got the below error:

ORA-02049: time-out: distributed transaction waiting for lock

 

Cause

More than one session is accessing the remote object.

 

Solution

I had issued commit, rollback for one of the session and the issue is resolved.

But, the key thing here is that; I could only reproduce the error while issueing below statement:

insert /*+ APPEND PARALLEL(4)*/ into admin.test2 
select /*+ DRIVING_SITE(T) PARALLEL(4) */ * from admin1.test2@MYDB2.WORLD T where 1=1
;

While trying it without APPEND hint it does not occur.

Honestly speaking, I did not know the real cause, because sometimes I could not reproduce the error.

The transactions  are not listed in:

select * from dba_2pc_pending; 

select * from dba_2pc_neighbors;

 

 

Hardening and Healthcheck of DBs with its OS environment

Following there is a sample healthcheck table, which I prepared for easily monitor and create tasks for improvement of whole system by sharing with my colleques.



All of them are not have to be accomplished, there are also nice to have items.

The list is formed with the feedbacks coming from OS admin, DB admin.

HPOV is alarm integration tool. OS backup, filesystem backups needed, in OS category, DDMI, centrify and opsware are technologies for keeping track of automatic inventory, SSO, and OS installation&configuration respectively. Grid category is formed by basic UDM definitions. Logging category is, OSWatcher utility provided by Oracle, HW Inventory is inventory application of HW, DB and other configuration items. DNS definition is mainly needed for cluster scan IPs in RAC, and clients coming over names.

"Prerequisite check "CheckActiveFilesAndExecutables” failed error" While issueing opatch apply

Problem

While applying interim patch, I got Prerequisite check "CheckActiveFilesAndExecutables” failed error and resolved the issue with the help of fuser.

oracle@mydb1>opatch apply
 Invoking OPatch 11.2.0.1.8

Oracle Interim Patch Installer version 11.2.0.1.8
 Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
 Central Inventory : /u01/app/oraInventory
 from           : /etc/oraInst.loc
 OPatch version    : 11.2.0.1.8
 OUI version       : 11.2.0.2.0
 Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-02-01_21-16-28PM.log

Applying interim patch '9578670' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
 Verifying environment and performing prerequisite checks...
 Prerequisite check "CheckActiveFilesAndExecutables" failed.
 The details are:

Following executables are active :
 /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
 [ Error during Prerequisite for apply Phase]. Detail: ApplySession failed during prerequisite checks: Prerequisite check "CheckActiveFilesAndExecutables" failed.
 Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-02-01_21-16-28PM.log

Recommended actions: OPatch needs to modify files which are being used by some processes.

OPatch failed with error code 41

Cause

Something is using oracle library.

Solution

I had solved the issue first looking at the log file and saw that the inuse control made by fuser, this is also a clue for me to find who is using the library file.

oracle@mydb1>/sbin/fuser /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1
 /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1: 12195m
 oracle@mydb1>

oracle@mydb1>ps -ef |grep 12195
 oracle   12195 11944  0 Jan19 pts/7    00:00:00 adrci
 oracle   22813  3717  0 21:18 pts/13   00:00:00 grep 12195
 oracle@mydb1>kill -9 12195

After than I had successfully applied the patch.

Grid SQL Monitoring

In order to store and later analyze the SQLs in SQL Monitor in Enterprise Manager Grid window; you could note using below SQL:

First, create a temporary table in order not to lose the data in gv$ view.

create table my_sql_monitor as select * from gv$sql_monitor;

And later analyze, for instance, find long running session SQLs, whether they ran parallel or not, so on by below SQL.

select distinct sql_id,min(px_servers_requested),min(px_servers_allocated),
min(sql_exec_start),max(elapsed_time),max(concurrency_wait_time),sql_text from  my_sql_monitor where sql_text is not null
group by sql_id,sql_text
order by max(elapsed_time) desc,1 desc;

Grid and Oracle Home Housekeeping, in 11.2 with cron jobs and opatch util cleanup

Grid and oracle home filesystem space exhaust problems, is a real headache which needs to be handled before it happens.

It is not a "nice to have" job; because, you will find yourself in following situations ultimately:

- getting up at midnight because of a call coming from 7*24 personnel that your db archive process can not write redo

- you can find yourself in a situation that trying to find out who ate my disk space in the middle of patch apply/PSU upgrade scenario.

If you are not motivated enough, so here comes the results:

Below there is the filesystem size before the operation:
grid@node1:/u01/app/11.2.0:>df -g /u01
/dev/fslv00 99.00 28.50 72% 120470 2% /u01

After the cleanup:
grid@node1:/u01/app/11.2.0:>df -g /u01
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/fslv01 96.88 49.21 50% 89422 1% /u01



So what I did?

First of all I had set the critical alarm level of filesystem usage to 70 pecent, which was 95 before. And I got the alarms...

In order for cleaning up of u01 filesystem, I had used "opatch util cleanup" for unnecessary opatch stored files and cron jobs to age out or remove old trace or log files.

1. CRONTAB SCRIPTS:

oracle@node1:/u01/app/11.2.0/grid>crontab –l

## trace cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/diag/rdbms/mydb/MYDB1/trace -type f -name "MYDB1*tr*" -mtime +10 -exec rm {} \; > /dev/null 2>&1

## audit cleanup
0 15 * * * /usr/bin/find /u01/app/oracle/admin/MYDB/adump -type f -name "*aud" -mtime +10 -exec rm {} \; > /dev/null 2>&1

## agent emd cleanup
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*dmp" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*trc" -mtime +3 -exec rm {} \; > /dev/null 2>&1
0 15 * * * /usr/bin/find /u01/app/agent/agent11g/node1/sysman/emd -type f -name "*core*" -mtime +3 -exec rm {} \; > /dev/null 2>&1

grid@node1:/home/grid:>crontab -l

## asm trace files cleanup
14 0 * * * /usr/bin/find /u01/app/grid/diag/asm/+asm/+ASM1/trace -type f -name "*ASM*.tr*" -mtime +20 -exec rm {} \; > /dev/null 2>&1

## listener log cleanup
15 0 * * * /usr/bin/find /u01/app/grid/diag/tnslsnr/node1/listener*/alert -type f -name "log*.xml" -mtime +3 -exec rm {} \; > /dev/null 2>&1

## audit cleanup
16 0 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit -type f -name "*aud" -mtime +1 -exec rm {} \; > /dev/null 2>&1

## listener log aging
17 0 * * 0 /usr/bin/compress -f /u01/app/grid/diag/tnslsnr/node1/listener/trace/listener.log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan1/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan2/trace/*log
17 0 * * 0 /usr/bin/compress -f /u01/app/11.2.0/grid/log/diag/tnslsnr/node1/listener_scan3/trace/*log

# rdbms log cleanup
0 15 * * * /usr/bin/find /u01/app/11.2.0/grid/rdbms/log -type f -name "*.tr*" -mtime +3 -exec rm {} \; > /dev/null 2>&1



2. OPATCH CLEANUP

DB HOME
oracle@node1:/u01/app/11.2.0/grid>opatch util cleanup
Invoking OPatch 11.2.0.1.5

Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-01-25_11-41-54AM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" before cleanup is 1159144429 bytes.
Size of directory "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage" after cleanup is 526164263 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

GRID HOME
grid@node1:/u01/app/11.2.0:>opatch util cleanup
Invoking OPatch 11.2.0.1.5

Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /u01/app/11.2.0/grid
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /u01/app/11.2.0/grid/oui
Log file location : /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch2012-01-25_11-38-10AM.log

Patch history file: /u01/app/11.2.0/grid/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/u01/app/11.2.0/grid/.patch_storage" before cleanup is 40399439700 bytes.
Size of directory "/u01/app/11.2.0/grid/.patch_storage" after cleanup is 23080457091 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.

OPatch succeeded.

Opatch cleanup MOS Note:
How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]

4. ) In preparation for patching systems, is there any way to predict the amount of space that will be required in $ORACLE_HOME/.patch_storage?