ASM DG to Physical Disk Mapping

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done

CRS Diagnostic Data Gathering

CRS Diagnostic Data Gathering

For 10gR2
=========
Ensure that the environment variable ORA_CRS_HOME is set to the CRS home
Ensure that the environment variable ORACLE_BASE is set
Ensure that the environment variable HOSTNAME is set to the name of the host.
$./diagcollection.pl -collect

For 11gR1
=========
Execute diagcollection.pl by passing the crs_home as the following
export ORA_CRS_HOME=/u01/crs
$ORA_CRS_HOME/bin/diagcollection.pl -crshome=$ORA_CRS_HOME --collect

For 11gR2
=========
Execute /bin/diagcollection.sh

NOTE: --nocore This option significantly reduces the size of the final file by excluding the core files

OS Watcher (OSW)
================
For platforms where Cluster Health Monitor is not available, OS Watcher can collect OS performance statistics.
The OS Watcher guide for Windows is found in Oracle Metalink Document 433472.1 - OS Watcher For Windows (OSWFW) User Guide. However, CHM for Windows is far superior to OS Watcher for Windows and should be used wherever possible.


For all other platforms, the OS Watcher user guide can be found in Document 301137.1

The OS Watcher output or the compressed output can be manually collected from the osw installation directories. Browsing the OSW output will show the server performance profile.
If OS Watcher is not running, then you can start the data collection manually from the osw installation directory:

nohup ./startOSW.sh &

OS Watcher should be in init.d to ensure that it starts automatically at server start.
The script tarupfiles.sh should be run regularly to compress the OS watcher data collection output. This should be configured in crontab.

Find out about dropped network packets

$ netstat -s
OR
$ ifconfig -a
the above gives information about "dropped network packets"

List files for a particular date

ls -latR | grep 'Jul 23'

Perl script to run any UNIX/LINUX command and email the output

The following script would run command "lsof -u oracle | wc -l" and then check for
the threshold value and if the threshold is exceeded, it will email the output.

#!/usr/bin/perl -w
use POSIX 'strftime';
my $date = strftime '%m-%d-%Y %H:%M:%S', localtime;
my $command = `/usr/sbin/lsof -u oracle | wc -l `;
my $host = `hostname`; chomp($host);
my $to = "abc\@yahoo.com";
my $title = "LSOF Threshold Exceeded" ;
my $from = "DBA\@yahoo.com";
my $subject = "Threshold lsof exceeded";
my $thresh = 10;

if( $command ge $thresh ) {
open(MAIL, "|/usr/sbin/sendmail -t ");

print MAIL "To: $to\n";
print MAIL "From: $from\n";
print MAIL "Subject: $title for host : $host\n";

print MAIL "$date\n HOSTNAME: $host\n LSOF Count: $command\n\n";
print MAIL "LSOF Count has Exceeded the threshold of $thresh";

close(MAIL);

}

Remove Job from another user : DBMS_IJOB.REMOVE

SQL> exec dbms_job.remove(40682);
BEGIN dbms_job.remove(40682); END;

*
ERROR at line 1:
ORA-23421: job number 40682 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 687
ORA-06512: at "SYS.DBMS_JOB", line 174
ORA-06512: at line 1


SQL> EXECUTE SYS.DBMS_IJOB.REMOVE (40682);

PL/SQL procedure successfully completed.

SQL>

Find session activity

select event,'/usr/ucb/ps -aux | grep'||spid,pga_used_mem,sid,a.serial#,b.inst_id,logon_time,a.username,module,last_call_et/60,subst
r(machine,1,20),process,sql_id
from gv$session a,gv$process b where addr=paddr
and status='ACTIVE'
and a.username is not null
and a.username = 'GCP_USER'
and a.inst_id=b.inst_id
and last_call_et/60 > 1
order by b.inst_id
/

Who's using the UNDO segments

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
/

Find out who's locking the accounts

set lines 200
set pages 200

column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

Query to find HISTOGRAMS

select owner,table_name,histogram from DBA_TAB_COL_STATISTICS where
owner='SCOTT' and table_name='EMPLOYEE'

Default STATS Collection in 11g

- The GATHER_STATS_JOB Oracle’s default stats collection job does not exist in
11g (the name does not exist) as it was there in 10g. Instead it has been
included in Automatic Maintenance Tasks

- How to check, Oracle’s default stats collection job is enable or disabled


SQL> select CLIENT_NAME,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

- How to disable if it is enabled (run below query to disable it). Below PL/SQL block has to be executed by SYS

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

EXPDP - EXCLUDE Multiple TABLES and SCHEMAS

The below example gives syntax to EXCLUDE multiple tables and multiple schemas while doing a full database export using expdp

=== BEGIN expdp_exclude.par

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=abc.dmp
LOGFILE=abc.log
FULL=Y
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN ('NAME', 'ADDRESS' , 'EMPLOYEE' , 'DEPT')"
EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN')"

=== END expdp_exclude.par

In the above example parameter file; tables NAME and ADDRESS are owned by SCOTT and tables EMPLOYEE and DEPT are owned by HR
EXCLUDE=TABLE => You do not have to prefix the OWNER name, in fact, if you put the OWNER.TABLE_NAME, it would not work.
It will EXCLUDE all TABLES having the name mentioned in the list, even if more than one owner has the same object name.
For example: If ADDRESS table is owned by user SCOTT and user HR, that table will be EXCLUDED from both the users.

The above commands would work only via parameter file and would not work on the command line.


COMMAND LINE SYNTAX for EXPDP

expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y
EXCLUDE=TABLE:\"IN \(\'NAME\', \'ADDRESS\' , \'EMPLOYEE\' , \'DEPT\'\)\"
EXCLUDE=SCHEMA:\"IN \(\'WMSYS\', \'OUTLN\'\)\"

Find Current CPU or PSU Applied

mylx1:product/11.1.0/OPatch/ $ ./opatch lsinv -bugs_fixed | grep -i 'database psu'
8833297 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
9209238 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
9352179 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
mylx1:product/11.1.0/OPatch/ $

REM This script outputs the current CPU applied on the database.column action format a15
column action_time format a30
column comments format a35
column action format a20
set linesize 300

select comments,action_time,action
from
(select action,action_time,comments
from sys.registry$history
where action in ('CPU','APPLY')
order by action_time desc)
where comments <> 'view recompilation'
and rownum < 2
/
-- Output from above script --
COMMENTS ACTION_TIME ACTION
----------------------------------- ------------------------------ --------------------
PSU 11.1.0.7.3 09-AUG-10 09.14.20.562314 AM APPLY

Expdp Options

expdp system/******** schemas=SCOTT directory=SCOTT_DUMP dumpfile=scott.dmp logfile=scott.log EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\"

if you just type EXCLUDE=TABLE:"LIKE 'EMP%'", TABLE:"LIKE '%ABC%'":

you will get the following error.

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character

you need to include escape characters in the statement, e.g.:
EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\" ,
this would exclude tables starting with EMP and any tables having ABC in their table name.

Using the NOT IN OPERATOR
EXCLUDE=TABLE:\"NOT IN \(\'ABC\',\'XYZ\'\)\"

Using the IN OPERATOR
EXCLUDE=TABLE:\"IN \(\'ABC\',\'XYZ\'\)\"