Top Linux processes and Oracle

Today due to application bug I had to check several time mapping between Linux PID processes and Oracle sessions. With small BASH script this is quite easy now

#!/bin/bash

# ps doesn't show real time CPU usage but average - skewed for long running processed
#TOP5=`ps xo pid --sort pcpu | tail -5 | xargs echo | sed -e 's/\s/,/g'`
TOP5=`top -b -d 1 -n 1 | head -12 | tail -5 | awk '{print $1;}' | xargs echo | sed -e 's/\s/,/g'`

sqlplus / as sysdba <<-EOF
        set linesize 200 pagesize 999
        select spid, s.username, s.program, sql_id, event from v\$session s, v\$process p where s.paddr = p.addr and spid in ($TOP5);
        exit
EOF

regards,
Marcin 

Rolling upgrads using logical standby database.

Couple of weeks ago there was a Twitter discussion started by Martin Bach (@MartinDBA) about cases for logical standby implementation. A rolling upgrade was mentioned by Tim Gorman (@timothyjgormanas) as one of potential recommendations for using this rare use product. I have been involved in such project in the past and I prepared an instruction and did quite large number of rolling upgrades from 11.1 into 11.2.

There are couple of my “gotchas”

• Support for data types – make sure that all data type in your application are supported by logical standby
• Support for Oracle features like reference partitioning or compression
• Logging all apply related errors during a logical standby phase
• Keep DML operations running on big dataset to minimum – keep in mind that update tab1 set col1=2 will be translated into separated update for every row in table and you really want to avoid it.
• Compatible parameter – if you are using flashback to rollback changes you can change compatible parameter with restore points

If you checked that all your types and features are supported this is a list of advantage you can get from rolling upgrade:

• Keep your application downtime low – in reality we have an average downtime around 3 min (including additional restart of instance to remove a restore points and change compatible parameter)
• If you have a problems with upgrading you can rollback it quite easy and revert logical standby into physical
• Your upgrade script can work longer as your primary database is still running
• After upgrade you can have a read only access to your production database for tests if needed

There are two good Oracle white papers about Rolling upgrades :

• First using a manual steps - Oracle Best Practices for High Availability Database Rolling Upgrade Using Transient Logical Standby Oracle Data Guard 11g
• Second using script provided by Oracle - Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database

First one is longer and required more work but also can give you more control over the process. Second one is more automated and easier but you have less control over switchover time.

This is I hope a first post from rolling upgrade series – in next one I will post more details about manual process.

Regards,
Marcin