Lack of swap

Environment:
Oracle 10g (10.2.0.4), Solaris 10
Problem:
alert.log:
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn5
ORA-27303: additional information: skgpspawn5
Mon Mar  8 14:07:46 2010
Process J007 died, see its trace file
Mon Mar  8 14:07:46 2010
kkjcre1p: unable to spawn jobq slave process

Solution:
It happened to be lack of swap. For Solaris use:

swap -s

swap -l

Useful info:
For other status number of ORA-27300 check Metalink note ID 579365.1

Generate tnsnames from OEM repository

Environment: Oracle 10g, 10.2
To get tnsnames from OEM, connect to OEM repository and run the following SQL.
p3.property_value and t.target_name can be the same, or one of them can include domain name. You can choose one or both.
Script:

select p3.property_value||', '||t.target_name||' = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||p1.property_value||')(PORT = '||p2.property_value||'))) (CONNECT_DATA = (service_name = '||p3.property_value||')))'
from SYSMAN.MGMT_TARGETS t, SYSMAN.MGMT_TARGET_PROPERTIES p1, SYSMAN.MGMT_TARGET_PROPERTIES p2, SYSMAN.MGMT_TARGET_PROPERTIES p3
where upper(t.TARGET_NAME) like 'DH%' -- filter targets if you don't want all of them
and t.target_type='oracle_database'
and t.target_guid = p1.target_guid
and t.target_guid = p2.target_guid
and t.target_guid = p3.target_guid(+)
and p1.property_name='MachineName'
and p2.property_name='Port'
and p3.property_name(+)='ServiceName'
order by 1;

SQL Plus profile

When you start SQL Plus or connect, it runs the glogin.sql site profile script and then runs the login.sql user profile script.
The global site profile is $ORACLE_HOME/sqlplus/admin/glogin.sql
The user profile script is login.sql. SQL Plus looks for login.sql in the current directory, and then in the directories specified by the SQLPATH environment variable.
e.g. Following script sets up date format, SQL prompt and shows where you are connected to.

set head off feedback off
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd HH24:MI:SS';
SELECT
'user: '||SYS_CONTEXT('USERENV','CURRENT_USER')||
' inst: '|| SYS_CONTEXT('USERENV','INSTANCE_NAME')||
' db: '|| SYS_CONTEXT('USERENV','DB_NAME')||
' host: '|| SYS_CONTEXT('USERENV','SERVER_HOST') c1
FROM DUAL;
set sqlprompt "_user'@'_connect_identifier> "

set head on feedback on
prompt ___________________________________________________________________
set serveroutput on size 20000 line 200 

Oracle documentation (SQL*Plus® User's Guide and Reference Release 10.2)

Send mail from Windows

When we create backup scripts sometimes we need to email notifications of success or failure.
There are few ways to do it. One of them is to use blat. Another one is VBScript. Here is example of such vbs:

Set oShell = CreateObject( "WScript.Shell" )
user=oShell.ExpandEnvironmentStrings("%UserName%")
comp=oShell.ExpandEnvironmentStrings("%ComputerName%")
Set objEmail = CreateObject("CDO.Message")
objEmail.From = user+"@"+comp
objEmail.To = "your-name@your-email.com"
objEmail.Subject = comp + " RMAN backup failed"
objEmail.Textbody = "Please check RMAN log file"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "your.smtp.server.com"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

Database performance monitoring

Some handy commands to help with database performance monitoring and troubleshooting.
Environment: Oracle 10.2.0.4 on Solaris

1. PGA usage, top consumers
   select
   round(p.pga_alloc_mem/1024/1024) "pga_alloc_MiB",
   round(p.pga_used_mem/1024/1024) "pga_used_MiB",
   p.spid "OS_pid",
   s.sid, s.serial#, s.username, s.status, s.client_info, s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$process p, v$session s
   where p.addr=s.paddr
   order by p.pga_alloc_mem desc;
   
2. UNDO usage, top consumers
   select round(t.used_ublk*(select block_size from dba_tablespaces where tablespace_name=(select value from v$parameter where name = 'undo_tablespace'))/1024/1024) "undo_used_MiB",
   t.used_ublk used_blocks,
   t.START_DATE trn_started,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from V$TRANSACTION t, v$session S
   where t.SES_ADDR=s.saddr
   order by used_ublk desc;
   
3. TEMP usage, top consumers
   select t.tablespace, t.blocks,
   round((t.blocks * ts.block_size)/1024/1024) "Size_MiB", t.segtype,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$session s, v$tempseg_usage t, dba_tablespaces ts
   where t.tablespace=ts.tablespace_name and s.saddr = t.session_addr
   order by t.blocks desc;
   
4.  CPU usage
   
   • UNIX top command, it can be sorted by CPU or memory (SIZE or RES). Press “h” for help.
   • In OEM you can look at Performance page of host.
   • UNIX Solaris command prstat
     sorted by CPU usage:
     
     
     prstat
     processes sorted by CPU and totals per user
     prstat -a
     sorted by memory and totals per user. Careful! It is different from top command as it shows shared memory as well.
     prstat -a -s rss
     
     
     
   
   
5. Free memory
   UNIX top command and OEM host performance page can be used. They show the same result.
    
6.  Paging and Swapping
   In OEM:
   Go to target host, "Performance" tab. Click "Swap Utilization" number to see a graph or click "Paging Activity" to see more metrics.
   Unix commands:
   vmstat -S 3 12
   Where 3 is the number of seconds between statistics samplings and 12 is the number of samples to take. Watch "si" and "so", swap in/out columns, and scan rate "sr". If scan rate is non-zero for an extended period of time, you need more RAM.
   Note that first line of output includes all activity since the last vmstat report.

Video problem in Skype on Linux

Environment:
Ubuntu 9.10 Karmic Koala 64-bit, Skype 2.1.0.47 (deb package downloaded from skype.com)
Problem:
test video is just a green screen.
Solution:
You need to use correct v4l1compat.so library.
az@az1:~$ locate v4l1compat.so
/usr/lib/libv4l/v4l1compat.so
/usr/lib32/libv4l/v4l1compat.so

Second one works ok for 64-bit linux. Try from shell:
LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype &

To make it permanent go to (using Gnome)
System > Preferences > Main Menu
and change properties of Skype shortcut to

env LD_PRELOAD=/usr/lib32/libv4l/v4l1compat.so skype

Oracle LogMiner (3)

Environment

Oracle 10g (10.2.0.4) on Solaris 10

Purpose

Extract data with given time interval and filter.

Time interval:

from 2009-12-09 10:35 to 2009-12-09 10:35

Filter:

table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

Solution

Switch logs to make sure that latest data changes are in archived logs.

alter system switch logfile;

Find needed logs:

select * from v$archived_log

where completion_time > to_date('2009-12-09 10:35','yyyy-mm-dd hh24:mi')

order by first_time;

Create SQL script to add logs to LogMiner session:

select 'DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''||name||''');' from v$archived_log

where first_time > to_date('2009-12-06 10:35','yyyy-mm-dd hh24:mi')

order by first_time;

After adding DBMS_LOGMNR.NEW to first log, it looks like this:

begin

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_628_689683506.dbf',OPTIONS => DBMS_LOGMNR.NEW);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_629_689683506.dbf');

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_630_689683506.dbf');

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/archive/DHMERR03/disk/1_631_689683506.dbf');

end;

Alternatively you can define only first log and use option CONTINUOUS_MINE to add following logs automatically.

Start LogMiner:

Begin

DBMS_LOGMNR.START_LOGMNR(startTime => to_date('2009-12-09 10:35','yyyy-mm-dd hh24:mi'), endTime => to_date('2009-12-15 11:10','yyyy-mm-dd hh24:mi'), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

End;

As PK supplemental logging was used in this case, ROWID could be excluded by adding option DBMS_LOGMNR.NO_ROWID_IN_STMT to previous statement. It would look like:

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.NO_ROWID_IN_STMT)

Return data changes using given filter:

select * from V$LOGMNR_CONTENTS

where table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

If necessary you can record the result into table.

create table alexz.LOGMINER_CONTENTS_20091215 tablespace users

as select * from V$LOGMNR_CONTENTS

where table_space='TABLES' and operation in ('UPDATE','INSERT','DELETE');

grant select on alexz.LOGMINER_CONTENTS_20091215 to public;

Finish LogMiner session

EXECUTE DBMS_LOGMNR.END_LOGMNR;