background process that exists in 11gr2 and functionality

Process Name	Functionality
crsd	•The CRS daemon (crsd) manages cluster resources based on configuration information that is stored in Oracle Cluster Registry (OCR) for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes.
cssd	•Cluster Synchronization Service (CSS): Manages the cluster configuration by controlling which nodes are members of the cluster and by notifying members when a node joins or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interfaces with your clusterware to manage node membership information. CSS has three separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and provides input/output fencing. This service formerly was provided by Oracle Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure results in Oracle Clusterware restarting the node.
diskmon	•Disk Monitor daemon (diskmon): Monitors and performs input/output fencing for Oracle Exadata Storage Server. As Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is started.
evmd	•Event Manager (EVM): Is a background process that publishes Oracle Clusterware events
mdnsd	•Multicast domain name service (mDNS): Allows DNS requests. The mDNS process is a background process on Linux and UNIX, and a service on Windows.
gnsd	•Oracle Grid Naming Service (GNS): Is a gateway between the cluster mDNS and external DNS servers. The GNS process performs name resolution within the cluster.
ons	•Oracle Notification Service (ONS): Is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events
oraagent	•oraagent: Extends clusterware to support Oracle-specific requirements and complex resources. It runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1).
orarootagent	•Oracle root agent (orarootagent): Is a specialized oraagent process that helps CRSD manage resources owned by root, such as the network, and the Grid virtual IP address
oclskd	•Cluster kill daemon (oclskd): Handles instance/node evictions requests that have been escalated to CSS
gipcd	•Grid IPC daemon (gipcd): Is a helper daemon for the communications infrastructure
ctssd	•Cluster time synchronisation daemon(ctssd) to manage the time syncrhonization between nodes, rather depending on NTP

Environment Variables for a Running Process in linux

Checking  environment Variables detail for a Running Process in linux.

strings –a /proc/<pid_of_the_process>/environ

[root@joshi11 ~]# ps -ef |grep pmon
root     59844 12268  0 23:21 pts/0    00:00:00 grep pmon
oracle   62026     1  0 20:42 ?        00:00:02 asm_pmon_+ASM2
[root@joshi11 ~]# strings -a /proc/62026/environ
__CLSAGFW_TYPE_NAME=ora.mdns.type
ORA_CRS_HOME=/prod/grid/11.2.0/grid
HOSTNAME=joshi11
SHELL=/bin/bash
TERM=xterm
__CRSD_CONNECT_STR=(ADDRESS=(PROTOCOL=IPC)(KEY=OHASD_IPC_SOCKET_11))
HISTSIZE=1000
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
CRF_HOME=/prod/grid/11.2.0/grid
WAS_ROOTMACRO_CALL_MADE=YES
GIPCD_PASSTHROUGH=false
SSH_TTY=/dev/pts/0
LC_ALL=
__CRSD_AGENT_NAME=/prod/grid/11.2.0/grid/bin/oraagent_oracle
__CRSD_MSG_FRAME_VERSION=2
USER=root

Script to show change in query execution

Below script just queries DBA_HIST_SQLSTAT for a given sql_id. A sql_id corresponds to a single SQL query.  

set linesize 32000
set pagesize 1000
set long 2000000000
set longchunksize 1000

select ss.sql_id,
ss.plan_hash_value,
sn.END_INTERVAL_TIME,
ss.executions_delta,
ELAPSED_TIME_DELTA/(executions_delta*1000) "Elapsed Average ms",
CPU_TIME_DELTA/(executions_delta*1000) "CPU Average ms",
IOWAIT_DELTA/(executions_delta*1000) "IO Average ms",
CLWAIT_DELTA/(executions_delta*1000) "Cluster Average ms",
APWAIT_DELTA/(executions_delta*1000) "Application Average ms",
CCWAIT_DELTA/(executions_delta*1000) "Concurrency Average ms",
BUFFER_GETS_DELTA/executions_delta "Average buffer gets",
DISK_READS_DELTA/executions_delta "Average disk reads",
ROWS_PROCESSED_DELTA/executions_delta "Average rows processed"
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where ss.sql_id = 'bjxwm53smzfjn'
and ss.snap_id=sn.snap_id
and executions_delta > 0
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER
order by ss.snap_id,ss.sql_id;

SQL_ID      PLAN_HASH_VALUE END_INTERVAL_TIME EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
bjxwm53smzfjn   3241932711 21-NOV-13 11.06.17.602 PM    2603 11457.6083 1242.76082    8398.43699  0 0       18.4320926   101602.754       5548.3227     24.2708413
bjxwm53smzfjn   3241932711 22-NOV-13 12.00.43.481 AM    1430 38261.3023 1765.16249    10753.5862  0 0       178.180512   140427.258      6062.37692     16.3657343
bjxwm53smzfjn   3241932711 22-NOV-13 01.00.08.144 AM    1559 62753.3383 1648.73349    18714.1897  0 0       503.047884   114864.889      5246.56062     23.0667094
bjxwm53smzfjn   3241932711 22-NOV-13 02.00.39.527 AM    1997  51748.324 1763.35454    29884.9149  0 0 108.84675   103847.485      16511.4447      25.897346
bjxwm53smzfjn   3241932711 22-NOV-13 03.00.41.453 AM    9911 5908.77018 1121.02559    4629.14669  0 0       64.4795458   91980.3728      195.806881     7.65533246
bjxwm53smzfjn   3241932711 22-NOV-13 04.00.43.330 AM    7690 12101.7682 1191.67764    10476.0536  0 0       170.186179   92665.0322      370.703771     7.87412224

In general  If the number of executions for one week over the next were different that would indicate a change in query volume.  If the per execution times were different that would indicate a change in the way each query ran.

2 node RAC Local listener parameter has auto-changed .

Today we notice One of our 2 node RAC db Local listener parameter has auto-changed  .

Application is behind firewall and for them both ports 2221(local listener) and Scan listener port(1621) is opened. So if local listener port changes to 1521, they are not able to access application.


Local_listener value we seted 2221 and it got automatically changed to  1521.

After checking all db and Cluster logfile did not find any clue how it got changed . so we raised Sr with oracle. oracle confirm this is bug .

Bug 11772838 - oraagent modifies LOCAL_LISTENER with an incorrect value, when the parameter was set in spfile (Doc ID 11772838.8)

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6141], [336116], [336119], [], [], [], [], [], [], []

Today we  were a power failure causing on db server  which lead logical corruption in controlfile .
using below step i solve corruption .


as per  db alert logfile.

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6141], [336116], [336119], [], [], [], [], [], [], []
Incident details in: _ora_2046_i528270.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Aborting crash recovery due to error 600
Errors in file _ora_2046.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6141], [336116], [336119], [], [], [], [], [], [], []
Errors in file _ora_2046.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6141], [336116], [336119], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
Dumping diagnostic data in directory=[cdmp_20131114231835], requested by (instance=1, osid=2046), summary=[incident=528270].
Thu Nov 14 23:19:30 2013
Sweep [inc][528270]: completed
Sweep [inc2][528270]: completed

Startup mount ;

Show parameter control_files
Note down the name of the control files name and location.
select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT'

Note down the name of the redo log
Shutdown abort ;

take controlfile  file backup ,
SQL>recover database using backup controlfile until cancel ;

Enter location of redo log shown as current in Query 1 when prompted for recovery

SQL> Startup mount ;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2235208 bytes
Variable Size            2516583608 bytes
Database Buffers         1744830464 bytes
Redo Buffers               12132352 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 1071851209 generated at 11/14/2013 18:00:54 needed for thread
1
ORA-00289: suggestion :
/Oracle/product/product/11.2.0/dbhome_11.2.0.3/dbs/arch/1_6141_817121922.dbf
ORA-00280: change 1071851209 for thread 1 is in sequence #6141


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/redo1/oracle/mstq2/redo/redo01g4.log  - this was my old redo logfile  name.
Log applied.
Media recovery complete.
SQL> Alter database open resetlogs ;

Database altered.


take immediately backup of db.

history inside VIM editor

~/..viminfo store history VIM editor  history.

[oracle@joshiv~]$ vim ~/.viminfo

# This viminfo file was generated by Vim 7.0.
# You may edit it if you're careful!

# Value of 'encoding' when this file was written
*encoding=utf-8


# hlsearch on (H) or off (h):
~H
# Last Search Pattern:
~MSle0/that

# Last Substitute Search Pattern:
~MSle0~&SHELL

# Last Substitute String:
$script

# Command Line History (newest to oldest):
:q!
:wq!
:%s/SHELL/script/g
:%s/shell/script/g
:c$
:c0
:/love
:!
:.,.+1!
:1g
:!pwd
:!date
:x!
:q1
:/alter
:wq1
:qq
:/LOG_ARCHIVE_DEST_2
:1
:/ALTER
:/control
:w!
:q~!

df not showing correct free space in linux

Last week i faced below issue-

When i did “df -h” and “du -sh /Mounted_parition_name” and find some difference output.

My sysadmin say it will solve/OK after server reboot.

Did some workaround on this my own i found  below.

After removing some file because of some reason the file is in open state means it is used by some process.
And in Unix every running process has some PID.

using  "lsof |grep /Mounted_parition_name" .

After  killing those process all set.