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
Friday, February 18, 2011
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.
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
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
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;
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
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
Thursday, February 17, 2011
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]
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]
Subscribe to:
Posts (Atom)