Passion for technology

Performance Tuning Scripts

Posted by Sanjay on March 20, 2012

To see username, machine name, program and read etc.

========

select s.sid,
s.username,
s.osuser,
s.status,
s.program,
i.consistent_gets,
i.physical_reads,
round(( i.physical_reads / i.consistent_gets ),2) * 100 pct_misses,
a.sql_text
from v$session s,
v$sqlarea a,
v$sess_io i
where i.sid = s.sid
and   i.consistent_gets != 0
and   a.address         = s.sql_address
and   a.hash_value      = s.sql_hash_value
having round(( i.physical_reads / i.consistent_gets ),2) * 100 > 30
group by
a.sql_text,
s.sid,
s.username,
s.osuser,
s.status,
s.program,
i.consistent_gets,
i.physical_reads;
=============
SCRIPT TO FIND LONG RUNNING QUERIES.
=============

rem LONGOPS.SQL
rem Long Running Statements
rem Helmut Pfau, Oracle Deutschland GmbH
set linesize 120
col opname format a20
col target format a15
col units format a10
col time_remaining format 99990 heading Remaining[s]
col bps format 9990.99 heading [Units/s]
col fertig format 90.99 heading “complete[%]“
select sid,
opname,
target,
sofar,
totalwork,
units,
(totalwork-sofar)/time_remaining bps,
time_remaining,
sofar/totalwork*100 fertig
from v$session_longops
where time_remaining > 0
/

==============

LOG SWITCH PER HOUR

==============

alter session set nls_date_format = ‘DD.MM.YYYY:HH24:MI’;

ttitle left ‘Redolog File Status from V$LOG’ skip 2

select group#, sequence#, Members, archived, status, first_time from v$log;

ttitle left ‘Number of Logswitches per Hour’ skip 2

select
substr(completion_time,1,5) day,
to_char(sum(decode(substr(completion_time,12,2),’00′,1,0)),’99′) “00″,
to_char(sum(decode(substr(completion_time,12,2),’01′,1,0)),’99′) “01″,
to_char(sum(decode(substr(completion_time,12,2),’02′,1,0)),’99′) “02″,
to_char(sum(decode(substr(completion_time,12,2),’03′,1,0)),’99′) “03″,
to_char(sum(decode(substr(completion_time,12,2),’04′,1,0)),’99′) “04″,
to_char(sum(decode(substr(completion_time,12,2),’05′,1,0)),’99′) “05″,
to_char(sum(decode(substr(completion_time,12,2),’06′,1,0)),’99′) “06″,
to_char(sum(decode(substr(completion_time,12,2),’07′,1,0)),’99′) “07″,
to_char(sum(decode(substr(completion_time,12,2),’08′,1,0)),’99′) “08″,
to_char(sum(decode(substr(completion_time,12,2),’09′,1,0)),’99′) “09″,
to_char(sum(decode(substr(completion_time,12,2),’10′,1,0)),’99′) “10″,
to_char(sum(decode(substr(completion_time,12,2),’11′,1,0)),’99′) “11″,
to_char(sum(decode(substr(completion_time,12,2),’12′,1,0)),’99′) “12″,
to_char(sum(decode(substr(completion_time,12,2),’13′,1,0)),’99′) “13″,
to_char(sum(decode(substr(completion_time,12,2),’14′,1,0)),’99′) “14″,
to_char(sum(decode(substr(completion_time,12,2),’15′,1,0)),’99′) “15″,
to_char(sum(decode(substr(completion_time,12,2),’16′,1,0)),’99′) “16″,
to_char(sum(decode(substr(completion_time,12,2),’17′,1,0)),’99′) “17″,
to_char(sum(decode(substr(completion_time,12,2),’18′,1,0)),’99′) “18″,
to_char(sum(decode(substr(completion_time,12,2),’19′,1,0)),’99′) “19″,
to_char(sum(decode(substr(completion_time,12,2),’20′,1,0)),’99′) “20″,
to_char(sum(decode(substr(completion_time,12,2),’21′,1,0)),’99′) “21″,
to_char(sum(decode(substr(completion_time,12,2),’22′,1,0)),’99′) “22″,
to_char(sum(decode(substr(completion_time,12,2),’23′,1,0)),’99′) “23″
from
V$ARCHIVED_LOG
group by
substr(completion_time,1,5)
/

==========
– %Purpose: Monitor Private SQL Areas and PL/SQL space in the UGA and SGA
–           PL/SQL allocates most memory from the UGA which is located in the SGA when shared servers are used
–           UGA = User Global Area
–           SGA = System Global Area
=========
–
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
set termout on;

column NA          head ‘STATISTIC’    format a29
column NR_SESS     head ‘#USERS’       format 9999
column C1          head ‘SUM|[kbyte]‘ format 999990.90
column C2          head ‘AVG|[kbyte]‘ format 999990.90
column C3          head ‘MIN|[kbyte]‘ format 999990.90
column C4          head ‘MAX|[kbyte]‘ format 999990.90

ttitle left ‘Monitor Private SQL Areas and PL/SQL space’ skip 2

select
rpad (B.NAME, 29, ‘.’) as NA,
COUNT(*) as NR_SESS,
SUM(A.VALUE)/1024.0 as C1,
AVG(A.VALUE)/1024.0 as C2,
MIN(A.VALUE)/1024.0 as C3,
MAX(A.VALUE)/1024.0 as C4
from
V$SESSTAT A,
V$STATNAME B
where
A.STATISTIC# = B.STATISTIC#
and (B.NAME like ‘%pga%’
or B.NAME like ‘%uga%’
or B.NAME like ‘%stored%’)
group by
B.NAME
/

=============
Top 10 by Buffer Gets:
=============

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
buffer_gets, executions, buffer_gets/executions “Gets/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;

================
Top 10 by Physical Reads:
=============

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
disk_reads, executions, disk_reads/executions “Reads/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;

============
Top 10 by Executions:
===========

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
executions, rows_processed, rows_processed/executions “Rows/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;

===============
Top 10 by Parse Calls:
===============

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;

=================
Top 10 by Sharable Memory:
==============

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;

=========
Top 10 by Version Count:
===========

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,350) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10
;

Rate this:

Like this:

Be the first to like this post.

Posted in Performance, Scripts | Leave a Comment »

Posted by Sanjay on January 5, 2012

===========
Small script: It will delete the audit files older than 90 days.
===========

[oracle@LONDT778W-D4 ~]$ cat test_aud_del.sh
#!/usr/bin/ksh

ORACLE_SID=SANJAY
export ORACLE_SID
ORACLE_HOME=/sanjay/sw/oracle/database/11.2.0.2
export ORACLE_HOME

LOGFILE=”/tmp/delete_old_aud.log”

echo `date` >>$LOGFILE
if [ $ORACLE_SID = "SANJAY" ] >>$LOGFILE
then

echo “Deleting the audit files older than 90 days for database `echo $ORACLE_SID`” >>$LOGFILE

echo “Audit files older than 90 days are: `find /sanjay/sw/oracle/database/admin/SANJAY/adump/*.aud -type f -mtime +90| wc -l`” >>$LOGFILE

find /sanjay/sw/oracle/database/admin/SANJAY/adump/*.aud -type f -mtime +90 -ls -exec rm {} \;

echo “Space usage at this moment is:
`df -h /sanjay/sw/oracle/`” >>$LOGFILE
else
echo “Environment is not set properly” >>$LOGFILE

fi
exit

Rate this:

Like this:

Be the first to like this post.

Posted in Scripts, Unix | Leave a Comment »

Unix Script Part-1

Posted by Sanjay on December 13, 2011

How to create a shell script in unix.

open a file in vi editor and save it with .sh extention
to run in backgrougn use nohup sh filename.sh &
to see the job status $jobs ( it will show runing or done etc)
to bring/send in forward/background. ctrl+z fg ctrl +z bg

below is a very simple example to start shell script.

“SHELL=/bin/ksh
export SHELL
export ORACLE_SID=instance_name
export ORACLE_HOME=/oraclehome ( can try $ORACLE_HOME also).
export ORACLE_BASE=/oraclebase
sqlplus /nolog <
connect username/password@instance_name
write full command here ;
exit ;

For example if you want to delete files older than 30 days.

find /u1/database/prod/udump/ -type f -mtime +30 -exec rm {} \;

Or as bash script:
Code:
#!/bin/bash

find /u1/database/prod/arch -type f -mtime +30 -exec rm {} \;
The only 2 commands used are find and rm.

Find looks for files older then 30 days (-type f -mtime +30). All its findings are given to rm (-exec rm {} \; ).

You can place the rm statement outside of find, which is supposed to be faster:

find /u1/database/prod/arch -type f -mtime +3 | xargs rm

Now you can crontab it to remove old files (more than 30 days) in a script.

“

Rate this:

Like this:

Be the first to like this post.

Posted in Unix | Leave a Comment »

Unix Command building

Posted by Sanjay on December 13, 2011

>ps –ef|grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9

We ‘ll walk through the process of building the command. To begin, we want to get a list of active processes on the server. We can do that using the following command:

>ps –ef

If we execute ps –ef on our server, we’ ll see a long list of processes – both for Oracle and for many other things. However, we want to limit your output to only those processes that are related to the Oracle Database. The grep command can be used to do this. Oracle background process names always begin with “ora_”, so piping the output of ps –ef through grep “ora_” will remove all but the Oracle background processes. For example:

>ps –ef |grep “ora_”
oracle 12011 1 0 06-Dec … ora_dbwr_JAP
oracle 12789 20202 0 12:10:55 00:00 grep ora_
oracle 13202 1 0 06-Dec … ora_smon_JAP
oracle 14983 1 0 06-Dec … ora_arch_JAP
oracle 10209 1 0 06-Dec … ora_pmon_JAP
oracle 2090 1 0 06-Dec … ora_reco_JAP
oracle 10404 1 0 06-Dec … ora_lgwr_JAP
oracle 10403 1 0 06-Dec … ora_dbwr_TEST
oracle 10401 1 0 06-Dec … ora_lgwr_TEST

In the above output as we can see it includes the process that’s running grep command. Pipe this output through grep –v grep to remove the grep command, so you don’t kill your own process. T

>ps –ef |grep “ora_”|grep –v grep
oracle 12011 1 0 06-Dec … ora_dbwr_JAP
oracle 13202 1 0 06-Dec … ora_smon_JAP
oracle 14983 1 0 06-Dec … ora_arch_JAP
oracle 10209 1 0 06-Dec … ora_pmon_JAP
oracle 2090 1 0 06-Dec … ora_reco_JAP
oracle 10404 1 0 06-Dec … ora_lgwr_JAP
oracle 10403 1 0 06-Dec … ora_dbwr_TEST
oracle 10401 1 0 06-Dec … ora_lgwr_TEST

Next, we should filter out all processes except those for the current ORACLE_SID. That way we delete the background processes only for that one instance instead of for all instances (if there are multiple database instance running). Do that by grepping for the SID name:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID
oracle 12011 1 0 06-Dec … ora_dbwr_JAP
oracle 13202 1 0 06-Dec … ora_smon_JAP
oracle 14983 1 0 06-Dec … ora_arch_JAP
oracle 10209 1 0 06-Dec … ora_pmon_JAP
oracle 2090 1 0 06-Dec … ora_reco_JAP
oracle 10404 1 0 06-Dec … ora_lgwr_JAP

Now that we have an accurate list of processes that you want to kill, you can use the awk command to get the process ID (PID) for each of these processes. The PID is in the second column, so we will use the awk ‘{print $2}’ command to display only that column:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’
12011
13202
14983
10209
2090
10404

Now we have a list of process Id numbers for the Oracle background processes. For the last step, we use the xargs command to pipe the list of PIDs to the kill command. For example:

>ps –ef |grep “ora_”|grep –v grep|grep $ORACLE_SID|awk ‘{print $2}’|xargs kill –9
Now that we’ve created this compound command, we can assign it to a Unix alias or we can put it in a file and make it a shell script so that we can execute it with a single short command.

===================
Find command usage:
===================

The Simplest Example
To print the names of all of the files in the directory and all subdirectories
find . -print
find ~ ~barnett /usr/local -print
find ~ ~barnett /usr/local -print
find / -print

Using find with other commands

ls -ld `find . -print`

Using xargs with find

ls -ld `find / -print`

find / -print | xargs ls -ld

Looking for files with particular names

find . -name *.o -print
find . -name ‘*.o’ -print
find . -name ‘[a-zA-Z]*.o’ -print

Looking for files by type

find . -type f -print | xargs ls -l

find . -type l -print | xargs ls -ld | awk ‘{print $10}’

Looking for files by sizes

find . -size 27 -print

find . -size +10000c -size -32000c -print

Searching for old files

find . -mtime 7 -print

find . -mtime +6 -mtime -8 -print

find . -type f -atime +30 -print

Searching for files by permission

find . -name *.o -perm 664 -print

find . -type d -perm 777 -print

find . -perm -20 -print

find . -perm -100 -print

Owners and groups

find . -user root -perm -4000 -print

find . -group staff -perm -2000 -print

find . -user 0 -perm -4000 -print
find . -group 10 -perm -2000 -print

Find and commands

find . -ls

find . -print | xargs ls -gilds

Find and Cpio

find . -depth -cpio >/dev/rmt0
find . -depth -ncpio >/dev/rmt0
find . -depth -print | cpio -oB >/dev/rmt0
find . -depth -print | cpio -ocB >/dev/rmt0

Using Find to Execute Commands

find . -exec echo {} ;

find . -exec echo {} ‘;’

find `pwd` -group staff -exec find {} -type l -print ;

find . -perm -20 -exec chmod g-w {} ;

or
find . -perm -20 -print | xargs chmod g-w

find . -inum 31246 -exec rm [] ‘;’

File comparisons

find /usr -newer /usr/FirstFile -print

Expressions

find /usr ! -newer /FirstFile -print
“find . ( -name a.out -o -name *.o ) -print
“

Keeping find from going too far

find * -type f -print -o -type d -prune

find . -print -o -name SCCS -prune

“

Rate this:

Like this:

Be the first to like this post.

Posted in Unix | Leave a Comment »

RMAN Part-2

Posted by Sanjay on December 3, 2011

========
Rman can back up the following types of files:
=========

• Archived redo log files
• Backup pieces
• Control files
• Datafiles Spfile
• spfile.

Note: It is ABCDS

RMAN will back up full backups, incremental backups, control file autobackups, and archive redo
log files. Keep in mind that flashback logs, online redo log files, and the current control file are not backed up.

Note: Always enable the autobackup for spfile (only backed up if db starting using spfile) and control file feature.
=====

RMAN> configure controlfile autobackup on;

RMAN> configure spfile autobackup on;

========

How to configure RMAN to write to specific locations on disk.

RMAN> configure channel 1 device type disk format ‘/ora01/rman/rman1_%U.bk’;

Note: CONFIGURE command must be executed before you run the BACKUP command.

=========
Backup Database.
=========

The term “RMAN level 0 incremental backup” doesn’t exactly describe itself very well, either. A level
0 incremental backup is backing up the exact same blocks as a full backup. In other words, the following
two commands back up the exact same blocks in a database:

RMAN> backup as backupset full database;

RMAN> backup as backupset incremental level=0 database;

RMAN> backup database not backed up since time=’sysdate-1′;

RMAN> backup database skip readonly skip offline skip inaccessible; backup database skip offline;

RMAN> backup database skip readonly;

RMAN> backup database skip inaccessible;

————-

Backup Sets versus Image Copies

The default backup mode of RMAN instructs it to only back up blocks that have been used in a datafile;
these are known as backup sets. RMAN can also make byte-for-byte copies of the datafiles; these are
known as image copies. Creating a backup set is the default type of backup that RMAN creates. The next
command creates a backup set backup of the database:

RMAN> backup database; backup as backupset database;

You can instruct RMAN to create image copies by using the AS COPY command. The following
creates image copies of every datafile in the database:

RMAN> backup as copy database; backup incremental level=0 database plus archivelog;

=======
Backup tablespace.
======

RMAN> backup tablespace system, sysaux;

RMAN> configure exclude for tablespace users; configure exclude for tablespace users clear; <== to clear the excluded tablespace.

=======
Backup datafile.
======

RMAN> backup datafile ‘/app/sanjay/11R2/system01.dbf’;

RMAN> backup datafile 1,5;

RMAN> backup as copy datafile 3;

RMAN> backup incremental level 1 datafile 4;

RMAN> backup database not backed up; <== To backup the datafile not backed up.

=======
Backup controlfile.
======

RMAN> backup current controlfile;

RMAN> configure controlfile autobackup format for device type disk to ‘/app/sanjay/rman/rman_ctl_%F.bk’;

=======
Backup spfile.
======

RMAN> backup spfile;

Note: RMAN can only back up the spfile if the instance was started using a spfile.

=========
Backup Archive logs.
=========

RMAN> backup archivelog all;

RMAN> backup archivelog all delete input;

RMAN> backup archivelog sequence 300;

RMAN> backup archivelog sequence between 300 and 400 thread 1;

RMAN> backup archivelog from time “sysdate-7″ until time “sysdate-1″;

Rate this:

Like this:

Be the first to like this post.

Posted in Backup and Recovery | Leave a Comment »

OEM

Posted by Sanjay on November 6, 2011

OEM 11g Docs

http://download.oracle.com/docs/cd/E11857_01/index.htm

OEM 10g Docs

http://download.oracle.com/docs/cd/B16240_01/doc/install.102/e10953/toc.htm

“

Rate this:

Like this:

Be the first to like this post.

Posted in OEM | Leave a Comment »

PL/SQL Part-1

Posted by Sanjay on October 12, 2011

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation to enhance the capabilities of SQL.
What is PL/SQL Engine?

Oracle uses PL/SQL engine to processes the PL/SQL statements, A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

What are advantages of PL/SQL?

A. Block Structures: PL/SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
B. Procedural Language Capability: PL/SQL consists of procedural language constructs such as conditional statements and loops.
C. Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
D. Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

What are PL/SQL Placeholders?
Placeholders are temporary storage area, those can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block. Depending on the kind of data you want to store, you can define placeholders with a name and a data type. Few of the data types used to define placeholders are as under.
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile

What is a PL/SQL Block?
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block and a PL/SQL block consist three sections:
A. The Declaration section (optional).
B. The Execution section (mandatory).
C. The Exception or Error handling section (optional).

A. The Declaration section (optional):
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders. Placeholders (may be any of below type) which stores data temporarily.
1. Variables
2. Constants
3. Cursors
4. Records
5. Records those are used to manipulate data in the execution section.

B. The Execution section (mandatory):
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a MUST section where the program logic is written to perform any task/s. The programmatic constructs like form the part of execution section.
1. Conditional statement
2. SQL statements
3. Loops

C. The Exception or Error handling section (optional):
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
1. Every statement in the above three sections must end with ; (semicolon).
2. PL/SQL blocks can be nested within other PL/SQL blocks.
3. Comments can be used to document code.
This is how a sample PL/SQL Block looks.

1. HOW to count and group together.

select distinct(column_1),count(*) from table_name group by column_1 ;

Rate this:

Like this:

Be the first to like this post.

Posted in SQL | Leave a Comment »

Unix Solaris

Posted by Sanjay on October 10, 2011

=============
HOW TO COLLECT EXPLORER LOGS FOR SOLARIS
=============
# /opt/SUNWexplo/bin/explorer

run it on the box..

the output is available in:

cd /opt/SUNWexplo/output

1. to see the memory size.
prtconf

2. to see the details
========================
prtdiag and prtdiag -v for
1. CPU status
2. Memory configuration
3. IO Cards
4. Environment status
a. system temperature.
b. Front panel status
c. Disk status
d. Fan status
e. Power suply
f.
========================

3. to see file i/o
iostat -xtnM 5

show from time to time 2-3 procent %w , (for /var and /export)

The Database server is a V440 with 16 Gb ram / 4 x 1,3Ghz CPU Only 1 scsi bus

yesterday 1528 study / 92539 object arrive

sk@sanjay01:/usr/sk] $ prtconf
System Configuration: Sun Microsystems sun4u
Memory size: 16384 Megabytes
System Peripherals (Software Nodes):

4.
/var/adm/messages

5.
/etc/vfstab

6.
format (as root user will scan all disk (san disk from san server)
See below.

================================
HOW TO CHECK O.S. LEVEL FILES.
================================

/var/adm/messages

/var/opt/SUNWscor/oracle_server/messages.log.oracle

/var/opt/SUNWscor/oracle_listener/messages.log.listener

/var/opt/SUNWscor/oracle_listener

/var/opt/SUNWscor/oracle_server

/data/logs/impax.log

/data/logs/oracle/bdump/alert_sk.log

==============================
prtdiag -v SAMPLE OUTPUT
==============================

prtdiag -v

[sk@sanjay01:/usr/sk] $ prtdiag -v
System Configuration: Sun Microsystems sun4u Sun Fire V490
System clock frequency: 150 MHz
Memory size: 8192 Megabytes

========================= CPUs ===============================================

Run E$ CPU CPU
Brd CPU MHz MB Impl. Mask
— —– —- —- ——- —-
A 0, 16 1500 32.0 US-IV+ 2.2
A 2, 18 1500 32.0 US-IV+ 2.2

========================= Memory Configuration ===============================

Logical Logical Logical
MC Bank Bank Bank DIMM Interleave Interleaved
Brd ID num size Status Size Factor with
— — —- —— ———– —— ———- ———–
A 0 0 1024MB no_status 512MB 8-way 0
A 0 1 1024MB no_status 512MB 8-way 0
A 0 2 1024MB no_status 512MB 8-way 0
A 0 3 1024MB no_status 512MB 8-way 0
A 2 0 1024MB no_status 512MB 8-way 0
A 2 1 1024MB no_status 512MB 8-way 0
A 2 2 1024MB no_status 512MB 8-way 0
A 2 3 1024MB no_status 512MB 8-way 0

========================= IO Cards =========================

Bus Max
IO Port Bus Freq Bus Dev,
Type ID Side Slot MHz Freq Func State Name Model
—- —- —- —- —- —- —- —– ——————————– ———————-
PCI 8 B 3 33 33 3,0 ok SUNW,XVR-100 SUNW,375-3290
PCI 8 B 5 33 33 5,0 ok scsi-pci1000,30.1000.10c0.8/disk+ LSI,1030
PCI 8 B 5 33 33 5,1 ok scsi-pci1000,30.1000.10c0.8/disk+ LSI,1030
PCI 8 A 0 66 66 1,0 ok SUNW,qlc-pci1077,2312.1077.149.2+
PCI 8 A 1 66 66 2,0 ok SUNW,qlc-pci1077,2312.1077.149.2+

========================= Environmental Status =========================

System Temperatures (Celsius):
——————————-
Device Temperature Status
—————————————
CPU0 48 OK
CPU2 50 OK
DBP0 23 OK

=================================

Front Status Panel:
——————-
Keyswitch position: NORMAL

System LED Status:

LOCATOR FAULT POWER
——- ——- ——-
[OFF] [OFF] [ ON]

=================================

Disk Status:
————
DISK 0: [NO_FAULT]
DISK 1: [NO_FAULT]

=================================

Fan Status:
———–

Fan Tray Fan RPM Status
———– —- —– ———-
FAN_TRAY_0 CPU0_FAN 5454 [NO_FAULT]
FAN_TRAY_0 CPU1_FAN 4054 [NO_FAULT]
FAN_TRAY_0 CPU2_FAN 3947 [NO_FAULT]
FAN_TRAY_1 IO0_FAN 4054 [NO_FAULT]
FAN_TRAY_1 IO1_FAN 4166 [NO_FAULT]

=================================

Power Supplies:
—————

Supply Status Fault Fan Fail Temp Fail
—— ———— ——– ——— ———
PS0 [NO_FAULT ] OFF OFF OFF
PS1 [NO_FAULT ] OFF OFF OFF

=================================

========================= HW Revisions =======================================

System PROM revisions:
———————-
OBP 4.18.1 2005/06/13 11:39

IO ASIC revisions:
——————
Port
Model ID Status Version
——– —- —— ——-
Schizo 8 ok 7
Schizo 9 ok 7
[sk@sanjay01:/usr/sk] $

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
PERFORMANCE RELATED QUERIES
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Introduction to iostat , vmstat and netstat

This document is primarily written with reference to solaris performance monitoring and tuning but these tools are available in other unix variants also with slight syntax difference.

iostat , vmstat and netstat are three most commonly used tools for performance monitoring . These comes built in with the operating system and are easy to use .iostat stands for input output statistics and reports statistics for i/o devices such as disk drives . vmstat gives the statistics for virtual Memory and netstat gives the network statstics .

Following paragraphs describes these tools and their usage for performance monitoring.

Table of content :
1. Iostat
* Syntax
* example
* Result and Solutions

2. vmstat
* syntax
* example
* Result and Solutions

3. netstat
* syntax
* example
* Result and Solutions

Input Output statistics ( iostat )
iostat reports terminal and disk I/O activity and CPU utilization. The first line of output is for the time period since boot & each subsequent line is for the prior interval . Kernel maintains a number of counters to keep track of the values.

iostat’s activity class options default to tdc (terminal, disk, and CPU). If any other option/s are specified, this default is completely overridden i.e. iostat -d will report only statistics about the disks.

syntax:
Basic synctax is iostat interval count
option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t or -tdc ) . x options gives the extended statistics .

interval – is time period in seconds between two samples . iostat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . iostat 4 5 will give data at 4 seconds interval 5 times

Example

====================
$ iostat -xtc 10 5
====================
extended disk statistics tty cpu
disk r/s w/s Kr/s Kw/s wait actv svc_t %w %b tin tout us sy wt id
sd0 2.6 3.0 20.7 22.7 0.1 0.2 59.2 6 19 0 84 3 85 11 0
sd1 4.2 1.0 33.5 8.0 0.0 0.2 47.2 2 23
sd2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
sd3 10.2 1.6 51.4 12.8 0.1 0.3 31.2 3 31

The fields have the following meanings:
disk name of the disk
r/s reads per second
w/s writes per second
Kr/s kilobytes read per second
Kw/s kilobytes written per second
wait average number of transactions waiting for service (Q length)
actv average number of transactions actively being serviced
(removed from the queue but not yet completed)
%w percent of time there are transactions waiting
for service (queue non-empty)
%b percent of time the disk is busy (transactions
in progress)
Results and Solutions
The values to look from the iostat output are:
* Reads/writes per second (r/s , w/s)
* Percentage busy (%b)
* Service time (svc_t)

If a disk shows consistently high reads/writes along with , the percentage busy (%b) of the disks is greater than 5 percent, and the average service time (svc_t) is greater than 30 milliseconds, then one of the following action needs to be taken

1.) Tune the application to use disk i/o more efficiently by modifying the disk queries and using available cache facilities of application servers .

2.) Spread the file system of the disk on to two or more disk using disk striping feature of volume manager /disksuite etc.

3.) Increase the system parameter values for inode cache , ufs_ninode , which is Number of inodes to be held in memory. Inodes are cached globally (for UFS), not on a per-file system basis

4.) Move the file system to another faster disk /controller or replace existing disk/controller to a faster one.

========================================
Virtual Memory Statistics ( vmstat )
========================================

vmstat – vmstat reports virtual memory statistics of process, virtual memory, disk, trap, and CPU activity.

On multicpu systems , vmstat averages the number of CPUs into the output. For per-process statistics .Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted.

syntax
Basic synctax is vmstat interval count

option – let you specify the type of information needed such as paging -p , cache -c ,.interrupt -i etc.

if no option is specified information about process , memory , paging , disk ,interrupts & cpu is displayed .

interval – is time period in seconds between two samples . vmstat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . vmstat 4 5 will give data at 4 seconds interval 5 times.

Example
The following command displays a summary of what the system
is doing every five seconds.

example% vmstat 5

procs memory page disk faults cpu
r b w swap free re mf pi p fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 11456 4120 1 41 19 1 3 0 2 0 4 0 0 48 112 130 4 14 82
0 0 1 10132 4280 0 4 44 0 0 0 0 0 23 0 0 211 230 144 3 35 62
0 0 1 10132 4616 0 0 20 0 0 0 0 0 19 0 0 150 172 146 3 33 64
0 0 1 10132 5292 0 0 9 0 0 0 0 0 21 0 0 165 105 130 1 21 78

The fields of vmstat’s display are
procs
r in run queue
b blocked for resources I/O, paging etc.
w swapped
memory (in Kbytes)
swap – amount of swap space currently available
free – size of the free list

page ( in units per second).
re page reclaims – see -S option for how this
field is modified.
mf minor faults – see -S option for how this
field is modified.
pi kilobytes paged in
po kilobytes paged out
fr kilobytes freed
de anticipated short-term memory shortfall (Kbytes)
sr pages scanned by clock algorithm
disk ( operations per second )
There are slots for up to four disks,
labeled with a single letter and number.
The letter indicates the type of disk
(s = SCSI, i = IPI, etc).
The number is the logical unit number.

faults
in (non clock) device interrupts
sy system calls
cs CPU context switches

cpu – breakdown of percentage usage of CPU time.
On multiprocessors this is an a
average across all processors.
us user time
sy system time
id idle time
Results and Solution from iostat

A. CPU issues
Following columns has to be watched to determine if there is any cpu issue

1. Processes in the run queue (procs r)
2. User time (cpu us)
3. System time (cpu sy)
4. Idle time (cpu id)

procs cpu
r b w us sy id
0 0 0 4 14 82
0 0 1 3 35 62
0 0 1 3 33 64
0 0 1 1 21 78
Problem symptoms

A.) Number of processes in run queue
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .
2.) if this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.
3.) If the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us) system is facing shortage of CPU resources.

Resolution
Resolution to these kind of issues involves tuning of application procedures to make efficient use of cpu and as a last resort increasing the cpu power or adding more cpu to the system.

B. Memory Issues
Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage.

Resolution
1. Tune the applications & servers to make efficient use of memory and cache.
2. Increase system memory .
3. Implement priority paging in s in pre solaris 8 versions by adding line “set priority paging=1? in
/etc/system. Remove this line if upgrading from Solaris 7 to 8 & retaining old /etc/system file.

========================================
Network Statistics (netstat)
========================================

netstat displays the contents of various network-related data structures in depending on the options selected.

Syntax

netstat
multiple options can be given at one time.

Options
-a – displays the state of all sockets.
-r – shows the system routing tables
-i – gives statistics on a per-interface basis.
-m – displays information from the network memory buffers. On Solaris, this shows statistics
for STREAMS
-p [proto] – retrieves statistics for the specified protocol
-s – shows per-protocol statistics. (some implementations allow -ss to remove fileds with a value of 0 (zero) from the display.)
-D – display the status of DHCP configured interfaces.
-n do not lookup hostnames, display only IP addresses.
-d (with -i) displays dropped packets per interface.
-I [interface] retrieve information about only the specified interface.
-v be verbose

interval – number for continuous display of statictics.

Example
$netstat -rn

Routing Table: IPv4
Destination Gateway Flags Ref Use Interface
——————– ——————– —– —– —— ———
192.168.1.0 192.168.1.11 U 1 1444 le0
224.0.0.0 192.168.1.11 U 1 0 le0
default 192.168.1.1 UG 1 68276
127.0.0.1 127.0.0.1 UH 1 10497 lo0
This shows the output on a Solaris machine who’s IP address is 192.168.1.11 with a default router at 192.168.1.1

Results and Solutions

A.) Network availability
The command as above is mostly useful in troubleshooting network accessibility issues . When outside network is not accessible from a machine check the following

1. if the default router ip address is correct
2. you can ping it from your machine.
3. If router address is incorrect it can be changed with route add command. See man route for more information.

route command examples
$route add default
$route add 192.0.2.32

If the router address is correct but still you can’t ping it there may be some network cable /hub/switch problem and you have to try and eliminate the faulty component .

B.) Network Response
$ netstat -i

Name Mtu Net/Dest Address Ipkts Ierrs Opkts Oerrs Collis Queue
lo0 8232 loopback localhost 77814 0 77814 0 0 0
hme0 1500 server1 server1 10658 3 48325 0 279257 0
This option is used to diagnose the network problems when the connectivity is there but it is slow in response .

Values to look at:

* Collisions (Collis)
* Output packets (Opkts)
* Input errors (Ierrs)
* Input packets (Ipkts)

The above values will give information to workout

i. Network collision rate as follows :

Network collision rate = Output collision counts / Output packets

Network-wide collision rate greater than 10 percent will indicate

* Overloaded network,
* Poorly configured network,
* Hardware problems.

ii. Input packet error rate as follows :

Input Packet Error Rate = Ierrs / Ipkts.

If the input error rate is high (over 0.25 percent), the host is dropping packets. Hub/switch cables etc needs to be checked for potential problems.

C. Network socket & TCP Connection state

Netstat gives important information about network socket and tcp state . This is very useful in
finding out the open , closed and waiting network tcp connection .

Network states returned by netstat are following

CLOSED —- Closed. The socket is not being used.
LISTEN —- Listening for incoming connections.
SYN_SENT —- Actively trying to establish connection.
SYN_RECEIVED —- Initial synchronization of the connection under way.
ESTABLISHED —- Connection has been established.
CLOSE_WAIT —- Remote shut down; waiting for the socket to close.
FIN_WAIT_1 —- Socket closed; shutting down connection.
CLOSING —- Closed,
then remote shutdown; awaiting acknowledgement.
LAST_ACK —- Remote shut down, then closed ;awaiting acknowledgement.
FIN_WAIT_2 —- Socket closed; waiting for shutdown from remote.
TIME_WAIT —- Wait after close for remote shutdown retransmission..
Example

———————–
#netstat -a
———————–

Local Address Remote Address Swind Send-Q Rwind Recv-Q State
*.* *.* 0 0 24576 0 IDLE
*.22 *.* 0 0 24576 0 LISTEN
*.22 *.* 0 0 24576 0 LISTEN
*.* *.* 0 0 24576 0 IDLE
*.32771 *.* 0 0 24576 0 LISTEN
*.4045 *.* 0 0 24576 0 LISTEN
*.25 *.* 0 0 24576 0 LISTEN
*.5987 *.* 0 0 24576 0 LISTEN
*.898 *.* 0 0 24576 0 LISTEN
*.32772 *.* 0 0 24576 0 LISTEN
*.32775 *.* 0 0 24576 0 LISTEN
*.32776 *.* 0 0 24576 0 LISTEN
*.* *.* 0 0 24576 0 IDLE
192.168.1.184.22 192.168.1.186.50457 41992 0 24616 0 ESTABLISHED
192.168.1.184.22 192.168.1.186.56806 38912 0 24616 0 ESTABLISHED
192.168.1.184.22 192.168.1.183.58672 18048 0 24616 0 ESTABLISHED
if you see a lots of connections in FIN_WAIT state tcp/ip parameters have to be tuned because the
connections are not being closed and they gets accumulating . After some time system may run out of
resource . TCP parameter can be tuned to define a time out so that connections can be released and used by new connection.

==============
CPU information
==============

[sk@sanjay01:/tmp] $ psrinfo
0 on-line since 04/20/2011 22:22:44
1 on-line since 04/20/2011 22:22:44
2 on-line since 04/20/2011 22:22:44
3 on-line since 04/20/2011 22:22:42
16 on-line since 04/20/2011 22:22:44
17 on-line since 04/20/2011 22:22:44
18 on-line since 04/20/2011 22:22:44
19 on-line since 04/20/2011 22:22:44
[sk@sanjay01:/tmp] $ psrinfo -p
4
[sk@sanjay01:/tmp] $
[sk@sanjay01:/tmp] $ psrinfo -pv
The UltraSPARC-IV physical processor has 2 virtual processors (0, 16)
The UltraSPARC-IV physical processor has 2 virtual processors (1, 17)
The UltraSPARC-IV physical processor has 2 virtual processors (2, 18)
The UltraSPARC-IV physical processor has 2 virtual processors (3, 19)

“

Rate this:

Like this:

Be the first to like this post.

Posted in Unix | Leave a Comment »

Automatic Storage Management

Posted by Sanjay on October 7, 2011

ASM (Automatic Storage Management)
1		ASM History:	ASM was introduced in oracle 10g.


2		ASM doesn’t:
		1	Doesn’t perform I/O on behalf of the RDBMS, I/O is done by RDBMS and ASM help to locate them. So there is no “translation layer” for Oracle I/O to datafiles into disk block offsets. I/O from databases is directly applied to disk volumes without modification. It reduces overhead and improves performance.
		2	ASM doesn’t have any datafile.
		3	You need not more than one ASM instance per server. Means only 1 ASM instance can manage all ASM files for all databases on a server.
		4	It does not require large amounts of memory for cache. ASM requires typically a few hundred MB for internal administration shared across all databases.
		5	Backup cannot be done with traditional methods that just backup OS files. Therefore you need integrated tool or using RMAN is one of the best option.



3		ASM, What is:
		1	ASM works on all major operating systems so it is platform independent.
		2	SAP supports Oracle ASM.
		3	EMC fully supports ASM including various tools that integrate with Oracle as Replication Manager, backup and reporting tools.
		4	ASM is available in EXADATA.
		5	ASM distributes chunks of data pseudo-randomly across all available logical disks in a disk group. Hence remove potential performance hot-spots.
		6	Files that can be stored in ASM: typical database data files, control files, redologs, archivelogs, flashback logs, spfiles,RMAN backups and incremental tracking bitmaps, datapump dumpsets. In 11gR2, ASM has been extended to allow storing any kind of file using Oracle ACFS capability.
		7	If ASM instance is down than database will also either be automatically shutdown or crash.
		8	RMAN is the only way to backup ASM disks.
ASM Backgroud processes

1	SMON, PMON, LGWr and DBWr			Like normal database instances ASM instance too have the usual background processes like SMON, PMON, DBWr, CKPT and LGWr. In addition to that the ASM instance also have the following background processes,
2
3	RABL			Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.
4	ARBx			Actual Rebalancer: They perform the actual rebalancing activities. The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.
5	ASMB			ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.
6	MARK			marks ASM allocation units as stale following a missed write to an offline disk. This essentially tracks which extents require resync for offline disks.

4		ASM, Underlying tools

	ASMCMD: ASM

		asmcmd utility supports common Linux commands:
		http://docs.oracle.com/cd/E11882_01/server.112/e16102/asm_util001.htm#BABJHJJB
		$ export ORACLE_SID=+ASM
		$ cd $ORACLE_HOME/bin

	Category of command	Commands
1	Instance Management	dsget, dsset, lsct, lsop, lspwusr, orapwusr, shutdown, spbackup, spcopy, spget, spmove, spset, startup






2	File Management	cd, cp, du, find, ls, lsof, mkalias, pwd, rm, rmalias





3	Disk Group Management	chdg, chkdg, dropdg, iostat, lsattr, lsdg, lsdsk, lsod, md_backup, md_restore, mkdg, mount, offline, online, rebal, remap, setattr, umount



4	Template Management	chtmpl, lstmpl, mktmpl, rmtmpl





5	File Access Control	chgrp, chmod, chown, groups, grpmod, lsgrp, lsusr, mkgrp, mkusr, passwd, rmgrp, rmusr





6	Volume Management	volcreate, voldelete, voldisable, volenable, volinfo, volresize, volset, volstat












	ACFS: ASM Cluster File System:






	ADVM: ASM Dynamic Volume Manager
		ADVM translates the system calls to the underlying devices


	ASMCA: ASM Configuration Assistant:



	Cooked Files:


	ASMLib
		Note: All ASMLib installations require the oracleasmlib and oracleasm-support packages appropriate for their machine. The driver packages are named after the kernel they support. Run the “uname -r” command on your machine to determine your kernel version. The corresponding package has the name oracleasm-<kernel_version>.

	To see the ASMLib Kernel Matrix
		http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html

		Oracle ASMLib Release Notes
		http://www.oracle.com/technetwork/server-storage/linux/release-notes-092521.html


	Installing and Configuring ASMLib :
		Download the following packages from the OTN
		http://www.oracle.com/technetwork/server-storage/linux/whatsnew/index.html?origref=http://learnwithme11g.wordpress.com/2010/03/18/11gr2-rac-shared-storage-preparationasm-part1/
		The ASMLib is highly recommended if using ASM for shared storage within the cluster. It provides many performance and managability benefits.
		ASMLib automatically provides LUN persistence, so when using ASMLib there is no need to manually configure LUN persistence for the ASM devices.
		ASMLib kernel driver MUST match the kernel revision number, the kernel revision number of system can be checked by “uname -r” command.
		oracleasm-support-2.1.3-1.el5x86_64.rpm
		oracleasmlib-2.0.4-1.el5.x86_64.rpm
		oracleasm-2.6.18-92.1.17.0.2.el5-2.0.5-1.el5.x86_64.rpm
	1. Install the RPMs by running the following as the root user:
		# rpm -ivh oracleasm-support-2.1.3-1.el5x86_64.rpm \
		oracleasmlib-2.0.4-1.el5.x86_64.rpm \
		oracleasm-2.6.18-92.1.17.0.2.el5-2.0.5-1.el5.x86_64.rpm
	2. To configure ASMLib run following as root user:
		#/etc/init.d/oracleasm configure
		Configuring the Oracle ASM library driver.
		This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets (‘[]‘). Hitting <ENTER> without typing an answer will keep that current value. Ctrl-C will abort.
		Default user to own the driver interface []: grid
		Default group to own the driver interface []: asmdba
		Start Oracle ASM library driver on boot (y/n) [n]: y
		Scan for Oracle ASM disks on boot (y/n) [y]: y
		Writing Oracle ASM library driver configuration: done
		Initializing the Oracle ASMLib driver: [ OK ]
		Scanning the system for Oracle ASMLib disks: [ OK ]
	3. Repeat steps 1 and 2 on all cluster nodes.
		4. Mark shared disks as candidate disks
		To create ASM disks by using ASMLib:
		5. As the root user use the following syntax:
		# /usr/sbin/oracleasm createdisk disk_name device_partition_name
		In this command, disk_name is the name you choose for the ASM disk. The name you choose must contain only ASCII capital letters, numbers, or underscores, and the disk name must start with a letter, for example, DISK1 or VOL1, or RAC_FILE1. The name of the disk partition to mark as an ASM disk is the device_partition_name. For example:
		# /usr/sbin/oracleasm createdisk OCR_VOTE01 /usr/sda1
		# /usr/sbin/oracleasm createdisk ASMDATA01 /usr//sdd1
		# /usr/sbin/oracleasm createdisk ASMDATA02 /usr/sde1

	Commands:
		1. To unmark a disk that was used in createdisk command use following command as root user:
		# /usr/sbin/oracleasm deletedisk disk_name
		2. listdisks command to verify the disks availability:
		# /usr/sbin/oracleasm listdisks
		OCR_VOTE01
		ASMDATA01
		ASMDATA02
		3. On all nodes the ‘scandisks’ command as root user to see newly created ASM disks. You do not need to create the ASM disks on each node, only on one node in the cluster.
		# /usr/sbin/oracleasm scandisks
		Scanning system for ASM disks [ OK ]

	ASM New Features in 11g
	How to connect to ASM in 11g.
	$ sqlplus / as sysasm <== Yes, you read it right, it is sysASM not sysdba.SQL*Plus: Release 11.1.0.7.0 – Production on Fri Oct 10 10:23:09 2009Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options
	Although ASM instance is with no database, you can still create users:SQL> create user sanjay identified by sanjay ; User created.Now you can grant SYSASM role to the user: SQL> grant sysasm to sanjay; Grant succeeded. Now user sanjay can perform all ASM management functions instead of the user SYS. The user can connect with the clause as sysasm, similar to the “as sysdba” clause in regular databases. $ sqlplus sanjay/sanjay as sysasm
1	Variable size of AU and Extents: AU (smallest allocation unit) in ASM can varry for different diskgroup starting from 1 MB and can go upto 64 MB.
	In general 1 MB is enough for 1 objects but it may not true in many cases. If need it will be taken care automatically. Once thresshold will reach AU size will increase 1M to 4M to 16M to 64M.
	In 11g you can set the diskgroup attribute ‘au_size’ during the disk group creation:
	create diskgroup dg9 external redundancy disk ‘/dev/sanjay/raw15′ attribute ‘au_size’ = ’2M’ ;Note: You can define the size in bytes as well, example: Attribute ‘au_size’=’2097152′
	To check the AU size use V$ASM_DISKGROUP view.
	select name, allocation_unit_size from v$asm_diskgroup ;NAME ALLOCATION_UNIT_SIZE ——- ——————– DG4 1048576 DG8 1048576 DG9 2097152

2	One ASM instance can support all RDBMS versions by setting COMPATIBILITY and DATABASE_COMPATIBILITY parameters for given disk groups.
	SQL> select compatibility, database_compatibility from v$asm_diskgroup where name = ‘DG3′ ;
	COMPATIBILITY DATABASE_COMPATIBILITY ———————- ———————- 10.1.0.0.0 10.1.0.0.0
	In above example:
	COMPATIBILITY= ASM compatibility i.e. 10.1.0.0.0 here, means this diskgroup supports up to 10.1 ASM structure. Thus this diskgroup can have any RDBMS structure.
	DATABASE_COMPATIBILITY= RDBMS compatibility i.e 10.1.0.0.0 here, means the ASM diskgroup DG1 can be used for any RDBMS from version 10.1 onwards.
	SQL> alter diskgroup dg3 set attribute ‘compatible.asm’=’11.1′;
	SQL> alter diskgroup dg3 set attribute ‘compatible.rdbms’=’11.1′;

	Note: compatibility is set for one diskgroup (dg3) not for entire ASM instance. This feature allows you to use only one ASM instance for all types of database versions. Depending on which version is used, you can set the attribute appropriately and it will reduce inter-version communication.

2	Fast failure repair with disk_repair_time





3	Metadata backup and restroe made easy with md_backup and md_restore
	If there were any data it will be lost. Reason md_backup is not a backup of the data but rather the metadata of the ASM instance. The data is backed up by RMAN. After the diskgroup is created, along with all the directories, you can restore the RMAN backup to this diskgroup.

	ASMCMD> md_backup

	ASMCMD> md_restore

	User option -f to create a sql file instead of restoring directly.ASMCMD [+] > md_restore -b dg9.backup -t full -f cr_dg9.sqlNote: you need to run the sql script manually in ASM instance to create all disk groups and other objects. Script looks like as below. create diskgroup DG9 EXTERNAL redundancy disk ‘/dev/raw/raw19′ name DG9_0000 size 100M ; alter diskgroup /ASMCMD AMBR/DG9 alter template TEMPFILE attributes (UNPROTECTED COARSE); alter diskgroup /ASMCMD AMBR/DG9 alter template FLASHBACK attributes (UNPROTECTED FINE); alter diskgroup /ASMCMD AMBR/DG9 alter template ARCHIVELOG attributes (UNPROTECTED COARSE); alter diskgroup /ASMCMD AMBR/DG9 alter template BACKUPSET attributes (UNPROTECTED COARSE); alter diskgroup /ASMCMD AMBR/DG9 alter template XTRANSPORT attributes (UNPROTECTED COARSE); alter diskgroup /ASMCMD AMBR/DG9 alter template DATAGUARDCONFIG attributes (UNPROTECTED COARSE);


4	Drop diskgroup force including contents:
	To drop the diskgroup even if the disks are not mounted.
	SQL> drop diskgroup dg7 force including contents; Note: The available disks show up as FORMER, means it was part of group.




5	ASMCMD got lsdsk to check the disk with different options like -k, -s, -p, -t, Ik ( i is capital and k is small), -t -g and help lsdsk






6	Prefered mirror read option with asm_prefered_read_failure_groups

	ASM writes on disk group on round robin manner for example: If disk group DG5 has 2 failgroups named DG5_0000 and DG5_0001 1st extent goes to DG5_0000 with a copy going to DG2_0001, 2nd extent goes to DG5_0001 with a copy on DG5_0000, 3rd one back to DG5_0000 with copy on DG5_0001, and so on… By this method ASM maintains a copy of one disk on the other one.
	But ASM read extents, they are always read from the primary failgroup (DG5_0000, in this case); not from the secondary (DG2_0001). The secondary is read only when the primary is not available. Oracle Database 11g allows you to configure a node to read from a specific failgroup
	Issue below command from instance 1. SQL> alter system set asm_preferred_read_failure_groups = ‘DG5.DG5_0000′,’DG3.DG3_0000′ ;Issue below command from instance 2.SQL> alter system set asm_preferred_read_failure_groups = ‘DG5.DG5_0001′,’DG3.DG3_0001′ ; Note: Now if a session from instance 1 wants to read from diskgroup DG5 then disk DG5_0000 will be read. If the disk is not available then disk DG5_0001 will be read. Similarly when a session connected to the instance 2 reads the data, the disk DG5_0001 is read.
	To check how different disks of the diskgroups are being utilized query view V$ASM_DISK_IOSTAT. Which simulates the IOSTAT utility found in UNIX systems:
	select instname, dbname, group_number, failgroup, disk_number, reads, writes from v$asm_disk_iostat ;INSTNAM DBNAME GROUP_NUMBER FAILGROUP DISK_NUMBER READS WRITES ——- ——– ———— ———- ———– ———- ———- PRONE31 PRONE3 2 DG5_0000 0 1950 910 PRONE32 PRONE3 2 DG5_0001 1 2845 910

7	Mount restricted.














	ASM New Features in 11.2
1	No raw device option




2	Same home for CRS and ASM




3	SYSASM super user for ASM




4	Renaming a diskgroup.




5	ASM files and ASM Dynamic Volume Manager:




6	Snapshot feature to track the changes:




7	Hot and Cold attrubutes for diskgroup:




8	ASMCMD enhancement




	http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#BABCCEJC




9	ASMCMD additional feature: template management:




10	Access Control with [set attribute 'ACCESS_CONTROL.ENABLED' ]and ['ACCESS_CONTROL.UMASK']

		ASM How to
	1	How to create new database with ASM


	2	How to convert existing database from Non ASM to ASM.


	3	How to convert existing ASM database to Non ASM.


	4	How to add datafile under tablespace in ASM.


	5	How to drop disk group in ASM

	6	How to add a disk group in ASM


	7	How to rebalance ….


Upgrading ASM using DBUA
		Database Update Assistant (DBUA) can be used to upgrade the ASM instance from Oracle Database 10g to Oracle Database 11g.

	1	Change the directory to the new $ORACLE_HOME/bin and launch DBUA.
		cd $ORACLE_HOME/bin
	2	$ ./dbua
	3	On the Upgrades Operations page, click the Upgrade Automatic Storage Management Instance radio button, and click the Next button.
	4	On the summary page, confirm the source and target information, then click on Finish button.
	5	When the operation finishes, a successful message should be displayed. Note: The DBUA logs can be found in file: $ORACLE_BASE/cfgtoollogs/dbua/logs/ASMUpgrade.log.

Upgrading ASM Manually from existing Oracle 10g ASM to 11g:
	1	Install the Oracle Database 11g software to a new ORACLE_HOME directory.
	2	Update the /etc/oratab or /var/opt/oracle/oratab file with the new ASM ORACLE_HOME location.
	3	Copy the ASM initialization file from the old ORACLE_HOME to the new one.
	4	Edit any directory-based parameters (such as diag and dump) in the ASM initialization file as required.
		If you are upgrading a non-RAC ASM instance, you should reconfigure the Oracle CSS using the new ORACLE_HOME. Do this by executing the localconfig command from the new home. Once the CSS configuration is complete, you need to change your ORACLE_HOME to the new Oracle version 11.1 ORACLE_HOME and start the ASM instance.
	5	cd $ORACLE_HOME/bin # ./localconfig reset
		If upgrading ASM instance in a RAC environments, you can modify the new ASM home within the OCR using the srvctl utility: srvctl modify asm -n racnode1 -i +ASM1 -o /apps/oracle/product/11.1.0/asm -p init+ASM1.ora
	6	Grant the SYSASM role to the SYS
		If you have obsolete initialization parameters, you can address them now. To get a listing of all the obsolete initialization parameters, refer to the ASM alert log file.

ASM bad block recovery:

		If ASM cannot read a physical block from a disk. It considers that the block has IO error. In this case, ASM will automatically read a mirrored block and write a relocated copy to produce successful copy. Although, you can manually repair blocks that have read disk I/O errors. Use ‘remap’ command for the same.
		remap <diskgroup name> <disk name> <block range>

Examples of using ASM disks

		create tablespace test datafile ‘+diskgrpA’ size 100m;
		alter tablespace test add datafile ‘+diskgrpA’ size 100m;
		alter database add logfile group 4 ‘+dg_log1’,’+dg_log2’ size 100m;
		alter system set log_archive_dest_1=’location=+dg_arch1’;
		alter system set db_recovery_file_dest=’+dg_flash’;

		SQL> create tablespace skumar datafile ‘+SKIT_DATA’ size 100M autoextend off ;
		Tablespace created.
		SQL> select FILE_ID, TABLESPACE_NAME, FILE_NAME from dba_data_files where tablespace_name=’SKUMAR’ ;
		FILE_ID TABLESPACE_NAME FILE_NAME
		———- ——————————
		56 SKUMAR +SKIT_DATA/skumars1d/datafile/skumar.463.775478783
Display performance status:
		select path, reads, writes, read_time, write_time, read_time/decode(reads,0,1,reads) “AVGRDTIME”, write_time/decode(writes,0,1,writes) “AVGWRTIME” from v$asm_disk_stat;

	5. Important views	Comments

1	V$ASM_ALIAS:	In an ASM instance, contains one row for every alias present in every disk group mounted by the ASM instance. In a DB instance, contains no rows.
2	V$ASM_ATTRIBUTE:	Displays one row for each attribute defined. In addition to attributes specified by CREATE DISKGROUP and ALTER DISKGROUP statements, the view may show other attributes that are created automatically. Note that attributes are only displayed for disk groups where COMPATIBLE.ASM is set to 11.1 or higher.
3	V$ASM_CLIENT:	In an ASM instance, identifies databases using disk groups managed by the ASM instance. In a DB instance, contains information about the ASM instance if the database has any open ASM files.
4	V$ASM_DISK:	In an ASM instance, contains one row for every disk discovered by the ASM instance, including disks that are not part of any disk group. In a DB instance, contains rows only for disks in the disk groups in use by that DB instance. This view performs disk discovery every time it is queried.
5	V$ASM_DISK_IOSTAT:	Displays information about disk I/O statistics for each ASM client. In a DB instance, only the rows for that instance are shown.
6	V$ASM_DISK_STAT:	In an ASM instance, contains the same columns as V$ASM_DISK, but to reduce overhead, does not perform a discovery when it is queried. It does not return information about any disks that are new to the storage system. For the most accurate data, use V$ASM_DISK instead.
7	V$ASM_DISKGROUP:	In an ASM instance, describes a disk group (number, name, size related info, state, and redundancy type). In a DB instance, contains one row for every ASM disk group mounted by the local ASM instance. This view performs disk discovery every time it is queried.
8	V$ASM_DISKGROUP_STAT:	In an ASM instance, contains the same columns as V$ASM_DISKGROUP, but to reduce overhead, does not perform a discovery when it is queried. It does not return information about any disks that are new to the storage system. For the most accurate data, use V$ASM_DISKGROUP instead.
9	V$ASM_FILE:	In an ASM instance, contains one row for every ASM file in every disk group mounted by the ASM instance. In a DB instance, contains no rows.
10	V$ASM_OPERATION:	In an ASM instance, contains one row for every active ASM long running operation executing in the ASM instance. In a DB instance, contains no rows.
11	V$ASM_TEMPLATE:	In an ASM or DB instance, contains one row for every template present in every disk group mounted by the ASM instance.

	ASM, Known issues
1	Background process ASMB termination instance.
Error:	7099389 ORA-15064: communication failure with ASM instance 7099390 ORA-03113: end-of-file on communication channel 7099391 Process ID: 7099392 Session ID: 115 Serial number: 13 7099393 ASMB (ospid: 2651): terminating the instance due to error 15064 7099394 Wed Jan 04 16:31:42 2010
Findings:	Find if usage of ASMB backuground is not coming down
	select TO_CHAR (SYSDATE, ‘MM-DD-YYYY HH24:MI:SS’) “NOW”, PROGRAM, PGA_ALLOC_MEM/1024/1024, PGA_USED_MEM/1024/1024, PGA_MAX_MEM/1024/1024 from v$process where PROGRAM like ‘%ASM%’;
	NOW PROGRAM PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PGA_MAX_MEM/1024/1024 ——————- ———————————————— ———————– ———————- ——————— 01-23-2012 10:25:33 oracle@CLRACW02D (ASMB) 15.8068485 8.09796333 15.8068485
	Note: Value of PGA_MAX_MEM for ASM background process will keep on increasing and will cause instance crash at a point.

	select ‘Host Name :’ \|\| host_name \|\|chr(10) \|\| ‘Instance_Name :’ \|\| instance_name \|\|chr(10)\|\| ‘DB Start Time :’ \|\| to_char(STARTUP_TIME,’yyyy/mon/dd hh24:mi:ss’) \|\|chr(10)\|\| ‘Current Time :’ \|\| to_char(sysdate,’yyyy/mon/dd hh24:mi:ss’) “Instance Particulars” from v$instance;
Solution:	It is a bug in
	ASM INSTANCE CRASHES FREQUENTLY WITH ORA-15082 in 11g [ID 1056695.1]

	Interim patch 6851110 exists for certain platform/version: Apply this patch.
	https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28viewingMode=1143&bmDocTitle=ASM%20INSTANCE%20CRASHES%20FREQUENTLY%20WITH%20ORA-15082%20in%2011g&bmDocType=PROBLEM&bmDocDsrc=KB&bmDocID=1056695.1&from=BOOKMARK%29%29
	Workaround: Restart the database instance

ASM, Docs and articles

http://docs.oracle.com/cd/E11882_01/server.112/e18951/toc.htm

Master Note for Automatic Storage Management (ASM) [ID 1187723.1]

“

Rate this:

Like this:

Be the first to like this post.

Posted in ASM | Leave a Comment »

Unix commands for DBA Part-1

Posted by Sanjay on October 6, 2011

1. How to sort based on column

[sanjay@rwgdbwf01:/tmp] $ ps -ef| grep LOCAL=NO| sort +5
sanjay 21148     1 0   May 01 ?       1886:04 oracleSanjay (LOCAL=NO)
sanjay 4480     1 0   Apr 03 ?       145:05 oracleSanjay (LOCAL=NO)
sanjay 1815     1 0   Apr 03 ?       305:34 oracleSanjay (LOCAL=NO)
sanjay 4394     1 0   Apr 03 ?       413:50 oracleSanjay (LOCAL=NO)

2. Different options with ‘find’.
2.1 To find a file named “wherewere.you” under all sub-directories of /usr/oracle
find /usr/oracle -name wherewere.you -print

2.2 To remove all the files under /usr/oracle which end with .tmp
find /usr/oracle -name “*.tmp” -print -exec rm -f {} \;

2.3 To list all files under /usr/oracle which are older than a week.
find /usr/oracle -mtime +7 -print

2.4 To list all files under /usr/oracle which are modified within a week.
find /usr/oracle -mtime -7 -print

2.5 To compress all files which end with .dmp and are more than 2 MB.
find /usr/oracle -size +2097152c -name “*.dmp” -print -exec compress {} \;

3. Shared memory segment sizes
ipcs -mb
ipcs -b
ipcrm -m <== Use for the Shared Memory entry
ipcrm -s <== Use for the Semaphore entry

4. How to see the users logged in to the server and their IP address
who -T

5. How to convert the contents of a text file to

A. UPPERCASE
tr “[a-z]” “[A-Z]” < filename > newfilename

6. How to convert the contents of a text file to lowercase.
tr “[A-Z]” “[a-z]” < filename > newfilename

7. How to see lines 100 to 120 of a file
head -120 filename | tail -20

8. How to see the versions of all Oracle products installed on the server.
$ORACLE_HOME/orainst/inspdver

Rate this:

Posted in Unix | Leave a Comment »

« Previous Entries

Passion for technology

Challenging the challenges over a decade. Place for Oracle DBAs, Oracle Apps DBAs, Unix Administrators and Developers

It's me

Subscribe

Categories

Admin

Top Rated

Top Clicks

Blogroll

Performance Tuning Scripts

Rate this:

Like this:

Rate this:

Like this:

Unix Script Part-1

Rate this:

Like this:

Unix Command building

Rate this:

Like this:

RMAN Part-2

Rate this:

Like this:

OEM

Rate this:

Like this:

PL/SQL Part-1

Rate this:

Like this:

Unix Solaris

Rate this:

Like this:

Automatic Storage Management

Rate this:

Like this:

Unix commands for DBA Part-1

Rate this:

Follow “Passion for technology”