Transparent Application Failover (TAF) Feature

Transparent Application Failover:

After an Oracle RAC node crashes - usually from a hardware failure, all new application transactions are automatically rerouted to a specified backup node. The challenge in rerouting is to not lose the transactions that were in "in flight" at the exact moment of the crash. One of the requirements of continuous availability is the ability to restart in-flight application transactions, allowing a failed node to resume processing on another server without interruption.

TAF feature is a run time failover for high availability environments. It enables client applications to automatically reconnect to the database if the connection fails and optionally resume a select statement that was in progress. The reconnection happens automatically from within the Oracle Call Interface (OCI) library.

The TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level:

SELECT failover: With SELECT failover, Oracle Net keeps track of all SELECT statements issued during the transaction, tracking how many rows have been fetched back to the client for each cursor associated with a SELECT statement. If the connection to the instance is not lost, Oracle Net establishes a connection to another Oracle RAC node and re-executes the SELECT statements.

SESSION failover: When the connection to an instance is lost, SESSION failover results only in the establishment of a new connection to another Oracle RAC node; any work in progress is lost. SESSION failover is ideal for OLTP systems, where the transactions are small.

Failover Methods:
BASIC failover: In this approach, the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created.

PRECONNECT failover: In this approach, the application simultaneously connects to both a primary and backup node. This offers fast failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.

• Make sure that instances are running:

crs_stat -t
If either of database is offline, then start the instance
srvctl start instance -d RACDB -i RAC1

• Create a service: Before you can use TAF, you need to create an application service using EM or DBMS_SERVICE package.

EM -> Click Maintenance -> Cluster Managed Database Services -> Create Service

exec dbms_service.modify_service (service_name => SERV1,
failover_method => dbms_service.failover_method_basic,
failover_type => dbms_service.failover_type_select,
failover_retries =>
.....)

• Before you can start using your service, add corresponding entry in tnsnames.ora file.

RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2_VIP) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1_VIP) (PORT=1521))
(FAILOVER = ON)
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SERV1)
)
)

TAF allows DBA to configure the type and method of failover for each Oracle Net Client.

Limitations of TAF:
The following types of transactions do not automatically failover and must be restarted by TAF:
Transactional statements: Transactions invloving INSERT, UPDATE, or DELETE statements are not supported by TAF
ALTER SESSION statements. ALTER SESSION and SQL*Plus SET statements do not failover

The following do not failover and cannot be restarted:
Temporary objects. Transactions using temporary segments in the TEMP tablespace and global temporary tables do not failover.

Refrence: Oracle Video Presentation by Ritesh Das, Oracle India.

Oracle Cluterware

Oracle clusterware is software that enables servers to operate together as if they are one server. Each server looks like any standalone server. However, each server has additional processes that communicate with each other so the seperate servers appear as if they are one server to applications and end users.

The benefits of using a cluster include:

• Scalability for applications
• Using lower-cost hardware
• Ability to fail over
• Ability to grow the capacity over time by adding servers, when needed

Oracle Clusterware provides:

• Event Management: Monitoring what's going on and processes events. Alerting other pieces of what's going on
• High Availability Framework: Makes sure that everything is maintained in an up capability
• Process Monitor: Verify that the required processes are running
• Group Membership: Keeps track of who is part of the cluster

Oracle Clusterware has 2 heartbeats: Network heartbeat and Disk heartbeat.
If a node does not send a network heartbeat for misscount (time in seconds), then the node is evicted from the cluster
If disk heartbeat is not updated in IO Timeout, then the node is evicted from the cluster

Miscount (MC) in RAC:
The Cluster Sychronization Services (CSS) on RAC has miscount parameter. This value represents maximum time, in seconds, that a network heartbeat can be missed before enterigng into a cluster reconfiguration to evict the node. The default value is 30 seconds.(Linux 60 seconds in 10g, 30 sec in 11g)

Clusterware Process Details:

• CSS: Cluster Sychronization Services: Notifies members when a node joins or leaves the cluster
• CRS: Cluster Ready Services: Monitors, starts, stops, failover operation of CRS resources (Instances, Listeners, Services, etc.)
• EVM: Event Management: The background process that publishes Oracle Clusterware events. EVM scans the designated callout directory and with the help of ONS - runs all scripts in that directory when an event occurs.
• ONS: Oracle Notification Service: Allows clusterware events to be sent to: Nodes in the cluster, Middle-tier application servers, Clients, EVMD publishes events through ONS.
• OPROCD: Process Monitor Daemon: Provides I/O fencing, OPROCD performs its check, stops runnning, and if the wake up is beyond the expected time, then OPROCD resets the processor and rebobots the node.

I/O Fencing:
There will be situations where the leftover write operations from failed database instances (the cluster functions failed on nodes, but the nodes are still running at OS level) reach the storage system after the recovery process starts. Since these write operations are no longer in proper serail order, they can damage the consistency of the stored data. Therefore, when a cluster fails, the failed node needs to be fenced off from all the shared disk devices or disk groups. This methodology is called I/O Fencing or Disk Fencing or Failure fencing.

The two main functions of I/O fencing are to prevent updates by failed instances, and to detect failue and prevent split-brain in the cluster.

CRS Alert Log file:
$CRS_HOME/log/hostname/alerthostname.log

commands like ocrconfig, ocrcheck etc dump their log here.

Voting Disk and OCR (Oracle Cluster Registry): Oracle Clusterware requires following critical two files:

Voting Disk:

• Oracle RAC uses Voting Disk to determine which instances are members of the cluster
• The voting file is essentially used by CSSD
• In the event of node failure, voting disk is used to determine which instance takes control of cluster
• The voting disk must reside on shared disk
• For high availability, Oracle recommends that you have multiple voting disks. Oracle Clusterware supports enabling multipling voting disks but you must have an odd number of voting disks.(having odd number of voting disks helps in Split-Brain scenarios, so that CRS would know which node to reboot)
• Voting disk is used by CSS to resolve split-brain scenarios and is owned by oracle user.

OCR:

• Is a binary file owned by root user which maintains cluster configuration information (cluster node list, which instances are runnning on which nodes)
• OCR must reside on shared disk that is accessible by all nodes in your cluster
• Oracle clusterware can multiples the OCR
• You can replace a failed OCR online and you can update OCR through supported APIs such as Enterprise Manager, the Server Control Utility (SRVCTL), or the Database Configuration Assistant (DBCA)

Why use Virtual IP?
The goal is application availability. When a node fails, the VIP associated with it automatically failed over to some other node. When this occurs follwing things will happen:

• VIP detects public network failure which generates a FAN event
• The new node announces the world indicating a new MAC address for the IP
• Connected clients through VIP, immediately receive ORA-3113 error or equivalent
• New connection requests rapidly traverse the tnsnames.ora address list skipping over the dead nodes, instead of having to wait on TCP-IP timeouts

Wihtout using VIPs, clients connected to a node that died will often wait for a TCP timeout period (which can up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.

Public IP: Clients use these to connect to nodes
Private IP: Used to interconnect, cache fusion
VIP: for failover (all applications connect using VIP) which is a floating IP.

Connecting with Public IP:
RACDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1) (PORT=1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)

If a session is connected using above public IP, and if the node it connected to fails, the session will TCP Timeout errors i.e. End-Of-Communication errors

Connecting with VIP:
RACDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-VIP) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT=1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)

In above case, NO TCP TIMEOUT WILL ENCOUNTER

Refrence: Oracle Video Presentation by Ritesh Das, Oracle India.

UPDATE query in SQL

In the following query, the subquery

select name1 from testa where no1=6;

returns no rows. In that case, it will update with NULL

SQL> update mytab set name=(select name1 from testa where no1=6), tno=9 where tno=1;

1 row updated.

SQL> select * from mytab;

TNO NAME
---------- ---------------
9
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

sudo and cpio UNIX commands

sudo: super user do, that allows users to run programs with the security privileges of another user (normally the superuser, aka. root). By default, sudo will prompt for a user password but it may be configured to require the root password, or require it only once per pseudo terminal, or no password at all.

cpio: Copies files into and out of archive storage and directories.
cpio -o [ a ] [ c ] [ v ] [ B | C Value ] Output

cpio -i [ b ] [ c ] [ d ] [ f ] [ m ] [ M ] [ r ] [ s ] [ t ] [ u ] [ v ] [ S ] [ 6 ] [ B | C Value ] [ Pattern... ] Input

cpio -p [ a ] [ d ] [ l ] [ m ] [ M ] [ u ] [ v ] Directory FileName

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 .

The most common shells available on UNIX systems are the Bourne Shell (sh), the C Shell (csh) and the Korn shell (ksh)


Directions
To extract the cpio file, move the cpio file to an empty directory, then do: cat filename.cpio | cpio -icd

Extract from each uncompressed downloadable using "cpio" (example: cpio -idmv <>

ORDER BY clause in SQL

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
6 sdf 3223
1 vinay 8000
2 suman 2000
1 vinay 9000
3 ravi 3000
4 sai 2000
2 suman 4000
1 vinay 3000
1 vinay 3000
2 suman 4000


SQL> select * from sample order by sno;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000


[OR]

Above query can also be rewritten as follows:
SQL> select * from sample order by 1;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000

LogMiner dynamic view

SQL> select * from v$logmnr_contents;
select * from v$logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

Joins in SQL

Joins:

SELECT t1.column, t2.column
FROM t1
[NATURAL JOIN t2] |
[JOIN t2 USING (column_name)] |
[JOIN t2
ON (t1.column_name = t2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN t2
ON (t1.column_name = t2.column_name)]|
[CROSS JOIN t2];

Natural Join is based on having all the column names same in both two tables i.e join condition is an equijoin of all columns with same name. The column names that have same name in both the tables should be of same type, else an error is returned.
SQL> select empno, ename, deptno
2 from emp
3 natural join dept;

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20

EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

14 rows selected.


USING clause: Can be used when several columns have the same names but the data types are different. You can match only one column. Note that table name or alias name should not be used in the referenced columns.

SQL> select empno,ename,deptno
2 from emp
3 join dept using (deptno);

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20

EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

14 rows selected.


Using JOIN with the ON clause: Natural join is an equijoin of all column names with same name. Using the ON clause you specify columns to join.

SQL> select empno,ename,d.deptno,sal
2 from emp e
3 join dept d on e.deptno=d.deptno;

EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7369 SMITH 20 800
7499 ALLEN 30 1600
7521 WARD 30 1250
7566 JONES 20 2975
7654 MARTIN 30 1250
7698 BLAKE 30 2850
7782 CLARK 10 2450
7788 SCOTT 20 3000
7839 KING 10 5000
7844 TURNER 30 1500
7876 ADAMS 20 1100

EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7900 JAMES 30 950
7902 FORD 20 3000
7934 MILLER 10 1300

14 rows selected.

Inner Join will display only the matched rows. A join between the two tables that returns the results of the inner join as well as the unmatched rows from left (or right) tables is called left (or right) outer join.

Full outer join returns the results of an inner join as well as the results of a left and right join.

Using IN clause in SQL

SQL> select * from emp where empno in(7369,7876);

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- --------------------------------
7369 SMITH CLERK 7902 17-DEC-80 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM

Hierarchial Queries

SQL> select empno, ename, job, mgr from emp
2 start with empno=7521
3 connect by prior mgr=empno;

EMPNO ENAME JOB MGR
---------- ---------- --------- ----------
7521 WARD SALESMAN 7698
7698 BLAKE MANAGER 7839
7839 KING PRESIDENT

CrossJoin in SQL

Cross join returns the cartesian product i.e. all rows in the first table is joined to all rows in the second table. To aviod cartesian product, always include a valid join condition.

SQ> select empname, dname from emp cross join dept;

TimeZones in SQL

Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;

Current Time
------------------------
Tue 10-Feb-2009 00:40:27

[OR]

SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-feb-2009 00:47:48

[OR]


SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-feb-2009 00:48:52

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
-05:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------
10-FEB-09 12.53.13.998000 AM -05:00

Above returns TimeStamp with Time ZOne

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------
10-FEB-09 12.54.29.396000 AM

Above returns only timestamp



SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------
-05:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select hiredate from emp;

HIREDATE
--------------------
17-dec-1980 00:00:00
20-feb-1981 00:00:00
22-feb-1981 00:00:00
02-apr-1981 00:00:00
28-sep-1981 00:00:00
01-may-1981 00:00:00
09-jun-1981 00:00:00
19-apr-1987 00:00:00
17-nov-1981 00:00:00
08-sep-1981 00:00:00
23-may-1987 00:00:00

HIREDATE
--------------------
03-dec-1981 00:00:00
03-dec-1981 00:00:00
23-jan-1982 00:00:00

14 rows selected.


SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


You can convert from DATE to TIMESTAMP when the column has data, but you cannot convert from DATE or TIMESTAMP to TIMESTAMP WITH TIME ZONE unless the column is empty.

You can specify the fractional seconds precision for timestamp. If none is specified, as in the above example, then it defaults to 6.

SQL> alter table emp modify hiredate timestamp(7);

Table altered.

SQL> select hiredate from emp;

HIREDATE
------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM
02-APR-81 12.00.00.0000000 AM
28-SEP-81 12.00.00.0000000 AM
01-MAY-81 12.00.00.0000000 AM
09-JUN-81 12.00.00.0000000 AM
19-APR-87 12.00.00.0000000 AM
17-NOV-81 12.00.00.0000000 AM
08-SEP-81 12.00.00.0000000 AM
23-MAY-87 12.00.00.0000000 AM

HIREDATE
------------------------------------
03-DEC-81 12.00.00.0000000 AM
03-DEC-81 12.00.00.0000000 AM
23-JAN-82 12.00.00.0000000 AM

14 rows selected.

EXTRACT function
------------
SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND]
[TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);


SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
2009

SQL> select extract(timezone_region from current_timestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from current_timestamp) from dual;

EXTRACT(TI
----------
UNK

TZ_OFFSET function:
--------------------------
returns the time zone offset. For example, if the function returns -05:00, it indicates that the time zone where the command was executed is five hours behind UTC (Coordinated Universal Time).


SQL> select tz_offset(sessiontimezone) from dual;

TZ_OFFS
-------
-05:00

SQL> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
+00:00

Query v$timezone_names to get valid time zone name values:
SQL> select * from v$timezone_names;


FROM_TZ function: Converts a TIMESTAMP value to TIMESTAMP WITH TIME ZONE value
----------------------------

SQL> select from_tz(timestamp '2008-03-20 10:00:00', 'US/Pacific') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','US/PACIFIC')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM US/PACIFIC


SQL> select from_tz(timestamp '2008-03-20 10:00:00', '-05:00') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','-05:00')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM -05:00

TO_TIMESTAMP
---------------
SQL> select to_timestamp('05-12-09 13:20:00', 'MM-DD-YY HH24:MI:SS') from dual;

TO_TIMESTAMP('05-12-0913:20:00','MM-DD-YYHH24:MI:SS')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM


TO_TIMESTAMP_TZ
-------------------
SQL> select to_timestamp_tz('05-12-09 13:20:00 -5:00', 'MM-DD-YY HH24:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('05-12-0913:20:00-5:00','MM-DD-YYHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM -05:00


TO_YMINTERVAL: Convert character string to an INTERVAL YEAR TO MONTH datatype.
-------------------
SQL> select hiredate from emp;

HIREDATE
-----------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM

SQL> select hiredate + to_yminterval('02-01') as new_hire_date from emp;

NEW_HIRE_DATE
-------------------------------------------------------------------------
17-JAN-83 12.00.00.000000000 AM
20-MAR-83 12.00.00.000000000 AM
22-MAR-83 12.00.00.000000000 AM

The character string can also have negative value. Belos, it returns a date that is one year and two months before the hire date.
SQL> select hiredate + to_yminterval('-01-02') as new_hire_date from emp;

NEW_HIRE_DATE
---------------------------------------------------------------------------
17-OCT-79 12.00.00.000000000 AM
20-DEC-79 12.00.00.000000000 AM
22-DEC-79 12.00.00.000000000 AM

ADRCI Utility in 11g

ADR and ADRCI utility in 11g:

Automatic Diagnostic Repository (ADR):
In 11g all the diagnostic related files are stored in a common directory structure called ADR. All the udump, bdump and cdump files will be located in this central repository.
The location for this repository can be defined using “diagnostic_dest” initialization parameter. Default value for this parameter points to ORACLE_BASE. If ORACLE_BASE is not set then it points to ORACLE_HOME/log.

Problems and Incidents:
• Problem: is a critical error in the database
Eg: ora-600, ora-7445, ora-4031 etc.

• Problem key: Every problem has a problem key, which is a text string that includes an error code (such as ORA 600) and in some cases, one or more error parameters.
Eg: ‘ORA 4030’ , ‘ORA 600 [ktfacht1-0]’

• Incident: is a single occurrence of a problem. Each incident has a numeric incident id.

V$DIAG_INFO:

select name, value from v$diag_info;

NAME,VALUE
Diag Enabled,TRUE
ADR Base,/xtdevawlk1/oracle/admin/XDDB1
ADR Home,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1
Diag Trace,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/trace
Diag Alert,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/alert
Diag Incident,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/incident
Diag Cdump,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/cdump
Health Monitor,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/hm
Default Trace File,/xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/trace/XDDB1_ora_11149480.trc
Active Problem Count,2
Active Incident Count,133

ADR Command Interpreter (ADRCI):
ADRCI is command line utility with which ADR is accessed. Some of the commands are listed below.

echo $ORACLE_HOME

echo $ORACLE_SID

adrci

ADRCI: Release 11.2.0.1.0 - Production on Fri Apr 30 18:21:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/xtdevawlk1/oracle/admin/XDDB1"

adrci> spool testCmds.log

adrci> show base
ADR base is "/xtdevawlk1/oracle/admin/XDDB1"

adrci> show home
ADR Homes:
diag/rdbms/xddb/XDDB1

adrci> show incident -mode detail -p "incident_id=80916"

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 80916
STATUS ready
CREATE_TIME 2010-04-22 12:07:40.692000 -04:00
PROBLEM_ID 2
CLOSE_TIME
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 600
ERROR_ARG1 kokbmolc2
ERROR_ARG2
ERROR_ARG3
ERROR_ARG4
ERROR_ARG5
ERROR_ARG6
ERROR_ARG7
ERROR_ARG8
ERROR_ARG9
ERROR_ARG10
ERROR_ARG11
ERROR_ARG12
SIGNALLING_COMPONENT
SIGNALLING_SUBCOMPONENT
SUSPECT_COMPONENT
SUSPECT_SUBCOMPONENT
ECID
IMPACTS 0
PROBLEM_KEY ORA 600 [kokbmolc2]
FIRST_INCIDENT 12178
FIRSTINC_TIME 2010-04-07 17:23:34.778000 -04:00
LAST_INCIDENT 80920
LASTINC_TIME 2010-04-22 12:26:41.206000 -04:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME SID
KEY_VALUE 2252.46445
KEY_NAME ProcId
KEY_VALUE 25.9
KEY_NAME Client ProcId
KEY_VALUE oracle@awlk26 (TNS V1-V3).10789242_1
KEY_NAME PQ
KEY_VALUE (0, 1271952460)
OWNER_ID 1
INCIDENT_FILE /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/incident/incdir_80916/XDDB1_ora_10789242_i80916.trc
OWNER_ID 1
INCIDENT_FILE /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/trace/XDDB1_ora_10789242.trc
1 rows fetched

To list all incidents (by default shows last 50 incidents) use the below command:
adrci> show incident

adrci> show problem -p "problem_id=2"

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2 ORA 600 [kokbmolc2] 80920 2010-04-22 12:26:41.206000 -04:00
1 rows fetched

To list all problems (by default shows last 50 problems) use the below command:
adrci> show problem

To view alert messages from alert log:
adrci> show alert -tail 10
adrci> show alert
adrci> show alert -p "message_text like 'ORA%'"

adrci> show tracefile -i 80916
diag/rdbms/xddb/XDDB1/incident/incdir_80916/XDDB1_ora_10789242_i80916.trc

To get help on any command:
adrci> help show alert

NOTE: The ADRCI interface is only supposed to modify the XML-formatted alert file, not the Text-formatted alert file, which is only kept in 11g version for compatibility purpose.

Using IPS to Package and Send Critical Error Diagnostic Information to Support:
One of the new features in 11g is Incident Packaging Service (IPS), which can be accessed through EM or ADRCI. IPS uses rules to correlate all relevant dumps and traces from ADR for a given problem and allows you to package them to ship to Oracle Support.

Walkthrough example using IPS:

echo $ORACLE_HOME

echo $ORACLE_SID

adrci

ADRCI: Release 11.2.0.1.0 - Production on Fri Apr 30 18:21:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/xtdevawlk1/oracle/admin/XDDB1"

adrci> show problem

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
2 ORA 600 [kokbmolc2] 80920 2010-04-22 12:26:41.206000 -04:00
1 ORA 4031 12187 2010-04-07 18:07:47.580000 -04:00
2 rows fetched

adrci> show incident -all

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
12257 ORA 4031 2010-04-07 16:53:52.946000 -04:00
12361 ORA 4031 2010-04-07 16:53:53.038000 -04:00
12345 ORA 4031 2010-04-07 16:53:53.138000 -04:00
12433 ORA 4031 2010-04-07 16:53:53.238000 -04:00
12241 ORA 4031 2010-04-07 16:54:01.883000 -04:00
12178 ORA 600 [kokbmolc2] 2010-04-07 17:23:34.778000 -04:00
12145 ORA 600 [kokbmolc2] 2010-04-07 17:26:40.856000 -04:00
12146 ORA 600 [kokbmolc2] 2010-04-07 17:27:23.185000 -04:00
12147 ORA 600 [kokbmolc2] 2010-04-07 17:31:08.705000 -04:00
12188 ORA 600 [kokbmolc2] 2010-04-07 19:10:14.533000 -04:00
12189 ORA 600 [kokbmolc2] 2010-04-07 19:59:24.460000 -04:00
12180 ORA 600 [kokbmolc2] 2010-04-07 20:02:59.157000 -04:00
12190 ORA 600 [kokbmolc2] 2010-04-07 20:07:46.903000 -04:00
12181 ORA 600 [kokbmolc2] 2010-04-07 20:10:29.868000 -04:00
12182 ORA 600 [kokbmolc2] 2010-04-08 11:28:58.166000 -04:00
12183 ORA 600 [kokbmolc2] 2010-04-08 12:01:00.010000 -04:00
12184 ORA 600 [kokbmolc2] 2010-04-08 12:16:01.712000 -04:00
16848 ORA 600 [kokbmolc2] 2010-04-08 12:22:52.785000 -04:00
16851 ORA 600 [kokbmolc2] 2010-04-08 12:23:53.391000 -04:00
16859 ORA 600 [kokbmolc2] 2010-04-08 12:31:58.770000 -04:00
16886 ORA 600 [kokbmolc2] 2010-04-08 12:35:29.012000 -04:00
16888 ORA 600 [kokbmolc2] 2010-04-08 12:38:53.459000 -04:00
16889 ORA 600 [kokbmolc2] 2010-04-08 12:58:54.124000 -04:00
16890 ORA 600 [kokbmolc2] 2010-04-08 14:05:48.989000 -04:00
16891 ORA 600 [kokbmolc2] 2010-04-08 14:06:42.049000 -04:00
16892 ORA 600 [kokbmolc2] 2010-04-08 14:16:33.875000 -04:00
33125 ORA 600 [kokbmolc2] 2010-04-08 16:59:46.710000 -04:00
33101 ORA 600 [kokbmolc2] 2010-04-09 12:38:05.778000 -04:00
65127 ORA 600 [kokbmolc2] 2010-04-12 11:52:50.732000 -04:00
65128 ORA 600 [kokbmolc2] 2010-04-12 11:53:48.015000 -04:00
65129 ORA 600 [kokbmolc2] 2010-04-12 11:55:14.323000 -04:00
65130 ORA 600 [kokbmolc2] 2010-04-12 11:58:12.096000 -04:00
65131 ORA 600 [kokbmolc2] 2010-04-12 12:01:13.717000 -04:00
65103 ORA 600 [kokbmolc2] 2010-04-12 16:44:41.115000 -04:00
65104 ORA 600 [kokbmolc2] 2010-04-12 16:46:25.264000 -04:00
65105 ORA 600 [kokbmolc2] 2010-04-12 16:48:11.583000 -04:00
65106 ORA 600 [kokbmolc2] 2010-04-12 16:52:49.470000 -04:00
65107 ORA 600 [kokbmolc2] 2010-04-12 17:04:33.023000 -04:00
65183 ORA 600 [kokbmolc2] 2010-04-13 11:55:07.059000 -04:00
80910 ORA 600 [kokbmolc2] 2010-04-13 11:58:01.491000 -04:00
80911 ORA 600 [kokbmolc2] 2010-04-13 12:06:23.137000 -04:00
80912 ORA 600 [kokbmolc2] 2010-04-13 12:10:47.061000 -04:00
80913 ORA 600 [kokbmolc2] 2010-04-13 12:11:37.638000 -04:00
65135 ORA 600 [kokbmolc2] 2010-04-15 14:11:03.169000 -04:00
65136 ORA 600 [kokbmolc2] 2010-04-15 14:11:15.527000 -04:00
65137 ORA 600 [kokbmolc2] 2010-04-15 14:11:24.126000 -04:00
65138 ORA 600 [kokbmolc2] 2010-04-15 14:11:32.751000 -04:00
80915 ORA 600 [kokbmolc2] 2010-04-20 12:05:22.521000 -04:00
65139 ORA 600 [kokbmolc2] 2010-04-22 11:58:22.351000 -04:00
80916 ORA 600 [kokbmolc2] 2010-04-22 12:07:40.692000 -04:00
80917 ORA 600 [kokbmolc2] 2010-04-22 12:09:34.738000 -04:00
80920 ORA 600 [kokbmolc2] 2010-04-22 12:26:41.206000 -04:00
52 rows fetched

NOTE: You can see that there can be multiple incidents reported for the same problem (Highlighted for problem ORA 4031)

To get all the incidents pertaining to a particular problem:

adrci> show incident -p "problem_key='ORA 4031'"

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
12257 ORA 4031 2010-04-07 16:53:52.946000 -04:00
12361 ORA 4031 2010-04-07 16:53:53.038000 -04:00
12345 ORA 4031 2010-04-07 16:53:53.138000 -04:00
12433 ORA 4031 2010-04-07 16:53:53.238000 -04:00
12241 ORA 4031 2010-04-07 16:54:01.883000 -04:00
5 rows fetched

Use the below command to get more details related to a particular incident:
adrci> show incident -mode detail -p "incident_id=12361"

ADR Home = /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 12361
STATUS ready
CREATE_TIME 2010-04-07 16:53:53.038000 -04:00
PROBLEM_ID 1
CLOSE_TIME
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 4031
ERROR_ARG1 32792
ERROR_ARG2 shared pool
ERROR_ARG3 unknown object
ERROR_ARG4 sga heap(1,0)
ERROR_ARG5 PX msg pool
ERROR_ARG6
ERROR_ARG7
ERROR_ARG8
ERROR_ARG9
ERROR_ARG10
ERROR_ARG11
ERROR_ARG12
SIGNALLING_COMPONENT KGH
SIGNALLING_SUBCOMPONENT
SUSPECT_COMPONENT
SUSPECT_SUBCOMPONENT
ECID
IMPACTS 0
PROBLEM_KEY ORA 4031
FIRST_INCIDENT 12257
FIRSTINC_TIME 2010-04-07 16:53:52.946000 -04:00
LAST_INCIDENT 12187
LASTINC_TIME 2010-04-07 18:07:47.580000 -04:00
IMPACT1 34668547
IMPACT2 34668546
IMPACT3 0
IMPACT4 0
KEY_NAME Client ProcId
KEY_VALUE oracle@awlk26.7971268_1
KEY_NAME SID
KEY_VALUE 756.37
KEY_NAME ProcId
KEY_VALUE 45.2
KEY_NAME PQ
KEY_VALUE (16777216, 1270673619)
OWNER_ID 1
INCIDENT_FILE /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/incident/incdir_12361/XDDB1_p022_7971268_i12361.trc
OWNER_ID 1
INCIDENT_FILE /xtdevawlk1/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/trace/XDDB1_p022_7971268.trc
1 rows fetched

Note the incident number for which you need to create a package.
adrci> ips pack incident 12361 in /var/tmp
Generated package 2 in file /var/tmp/ORA4031_20100503171107_COM_1.zip, mode complete

See the package created in /var/tmp for incident 12361
/var/tmp 1067-> ls -ltr ORA4031_20100503171107_COM_1.zip
-rw-r----- 1 oracle dba 3874078 May 3 17:11 ORA4031_20100503171107_COM_1.zip

The zip file generated can be uploaded to Oracle Support. This file will include all the trace files, alert log and other diagnostic information for that problem.

Also find packages generated in ‘incpkg’ folder found in ADR home.
/oracle/admin/XDDB1/diag/rdbms/xddb/XDDB1/incpkg 1058-> ls -ltr
total 24
drwxr-x--- 3 oracle dba 256 May 3 16:38 pkg_1
-rw-r----- 1 oracle dba 6702 May 3 16:38 manifest.xsl
drwxr-x--- 3 oracle dba 256 May 3 17:11 pkg_2
-rw-r----- 1 oracle dba 515 May 3 17:11 tstperl.txt

Oracle Support Article References:

• 11g Understanding Automatic Diagnostic Repository [ID 422893.1]
• ADR Different Methods to Create IPS Package [ID 738732.1]
• 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support[Video] [ID 443529.1]