Saturday, March 19, 2011

oradebug gather all impotant information for troubleshooting

If a database is hung, then we cannot even login to sqlplus using "/ as sysdba", right?

Not entirely true, atleast for 10g and above.

We can login as follows and collect vital information for analyzing the reason for the hang before bouncing the db

The prelim option allows us to connect to the SGA but does not establish a session
and hence we can perform hanganalyze commands to dump information for further review
of hang conditions and reasons. This is a sessionless SQL Plus connection to the SGA.
Same thing can be done from EM as well by having a Direct SGA connection


$sqlplus -prelim
SQL>

At this point, it will not say anything like "Connected to Database"...etc.
Now you can perform oradebug hanganalyze commands

SQL> oradebug setmypid
SQL> oradebug hanganalyze 12


===>>>>>>>>>>>>>>>>>>>>>>>


- Get the sid from the pid or vice versa:

SELECT s.sid, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND ... < p.spid = or perhaps
s.sid = >




If certain processes get PROCESSSTATE dump.
- Trace the process from the os level (see step 4).

Get processstate dumps. Do 3 times. This generates a trace file in
your user_dump_dest (from sqlplus: show parameter user_dump_dest).

$ sqlplus "/as sysdba"
oradebug setospid
oradebug unlimit
oradebug dump processstate 10



Get errorstacks from the process. Do 3 times. This generates a
trace file in your user_dump_dest (from sqlplus: show parameter user_dump_dest).

$ sqlplus "/as sysdba"
oradebug setospid
oradebug unlimit
oradebug dump errorstack 3




====>>>>>>>>




oradebug dump treedump

Dumps the structure of an index tree. The dump file contains one line for each block in the tree,
indented to show its level, together with a count of the number of index entries in the block.



Syntax Parameter:

oradebug dump treedump object_id


Example:

select object_id from sys.dba_objects
where owner = upper('&Owner') and
object_name = upper('&IndexName');


SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump treedump 40
Statement processed.

Instead of oradebug dump treedump command you can also use,
alter session set events 'immediate trace name treedump level n';

That will give you a trace file with the one line for each block in the B*-tree and a row
count for each block.If there are large numbers of empty or nearly empty blocks, then the
index is a good candidate for being rebuilt.



=============>>>>>>>>>>>==========

oradebug for oracle Background process :


oradebug procstat
Dump process Statistics.


Syntax Parameter :
oradebug procstat OS PID


Example
Dump statistics for the DBWR background process:


select pid,name
from v$process p, v$bgprocess b
where b.paddr = p.addr
SQL> /
PID NAME
---------- -----
2 PMON
3 DBW0
4 LGWR
5 CKPT
6 SMON
7 RECO

6 rows selected.

SQL> oradebug setorapid 3

Unix process pid: 15668, image: oracle@apollo (DBW0)

SQL> oradebug procstat
Statement processed.

To find out where is the trace file located run:

SQL> oradebug tracefile_name
/ora-main/app/oracle/admin/test/bdump/test_dbw0_15668.trc

Rolling Patch - OPatch Support for RAC

PURPOSE
-------

Describe the current support of OPatch for Real Application Clusters.
Before reading OPatch RAC Support, you should be familiar with
single-instance OPatch processing.

For more information about Opatch, please refer to:
Note.189489.1 Oracle9i Data Server Interim Patch Installation (OPatch)


SCOPE & APPLICATION
-------------------

This document is intended for DBAs and System Administrators that
are going to apply Oracle Interim Patches on RAC environment.


Rolling Patch - OPatch Support for RAC
--------------------------------------

1 - RAC Patching methods

OPatch supports 3 different patch methods on a RAC environment:

. Patching RAC as a single instance (All-Node Patch)

In this mode, OPatch applies the patch to the local node first, then
propagates the patch to all other nodes, and finally updates the inventory.
All instances will be down during the whole patching process.

· Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

In this mode, OPatch patches the local node, asks users for a sub-set of
nodes, which will be the first nodes to be patched. After the initial
subset of nodes are patched, Opatch propagates the patch to the other nodes
and finally updates the inventory. The downtime would happen between the
shutdown of the the second subset of nodes and the startp of the initial
subset of nodes patched.

· Patching RAC using a rolling strategy - No down time (Rolling Patch)

With this method, there is no downtime. Each node would be patched and
brought up while all the other nodes are up and running, resulting in
no disruption of the system.


2 - Flow diagrams

. All-Node Patch

. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up

. Minimum downtime

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3

. Rolling patch (no downtime)

. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3


3 - How does Opatch select which method to use?

To be eligible to be applied on as a rolling patch, the patch need to meet
certain criteria, which are determined by Oracle developers. To be applied
on a "rolling fashion", the patch must be designated as a "rolling
updatable patch" or simply "rolling patch".

The algorithm used to decide which method is going to be used is the
following:

If (users specify minimize_downtime)
patching mechanism = Min. Downtime
else if (patch is a rolling patch)
patching mechanism = Rolling
else
patching mechanism = All-Node


4 - Availability of rolling patches

As patches are released they will qualified and identified as a "rolling" or
"not rolling patch". While most patches can be applied in a rolling
fashion some patches can not be applied in this fashion.
Patches that could potentially be installed on rolling fashion include:

. Patches that do not affect the contents of the database.
. Patches not related to the RAC internode communication infrastructure.
. Patches that change procedural logic and do not modify common header
definitions of kernel modules. This includes client side patches that
only affect utilities like export, import, sql*plus, sql*loader, etc.

Only individual patches -- not patch sets -- will be “rollable”. It should
also be noted that a merge patch of a “rolling patch” and an ordinary patch
will not be a “rolling patch”.

From 9.2.0.4 on, all patches released will be marked as a "rolling" or
"not rolling patch", based on defined set of rules. Patches previously
released are packaged as "not rolling".

Because the set of rules currently defined are very conservative, patches
released as "not rolling patches", either before and after 9.2.0.4, may be
eligible to be re-released as "rolling patches", after analysis from
Oracle Development.

If you plan to apply a patch that is marked as "not rolling" and want to
check if is possible to take advantage of the rolling patch strategy,
please contact Oracle Support.



5 - How to determine if a patch is a "rolling patch" or not?

- 9i or 10gR1: (run)
$ opatch query -is_rolling

Opatch will ask the patch location and then will inform if the patch is or
not a "rolling patch"

- 10gR2: (run)
$ opatch query -all | grep rolling

For Windows, the following command can be used as grep is not suitable:
opatch query -all C:\stage\10.2.0.3_Mini_Patches\5731537 | findstr rolling
Patch is a rolling patch: false

6 - Current Limitations

. Patching with Shared File System

Currently OPatch treats Shared File System, like CFS, as a single-instance
patch. It means that OPatch will blindly patch files under a given
ORACLE_HOME knowing that other nodes will pick up the changes via the
Shared File System. Unfortunately, this means that OPatch cannot take
advantage of a rolling patch on a Shared File System environment;
all nodes must be down throughout the patching process.


. Patching one node at time

The Opatch strategies discussed above (All-Node, Min. Down-Time, and Rolling)
presumes that all nodes will be patched at the same time. Additionally,
each node can be patched individually, at different times, using the "-local"
key word, which will patch only the local node.