Friday, August 31, 2007

Oracle 11g New feaures Case Sensitive Password

Virag Sharma virag123@gmail.com


After upgrading production database to 11g, I thought to try some other features of Oracle database 11g on test database. So tried to start configuring standby database. Created password files with manager password.

But we noticed that, archive file is not shipping to physical standby side and getting authentication error again and again. Finally I have shutdown my PC and went to watch News. While watching movie idea came into mind, this error might be because of 11g new feature case sensitive password. Well it is, and I created password file with following command and after that thing work fine.

orapwd file=orclpwd password=manager ignorecase=y

Password case sensitive by default, new init.ora parameter sec_case_sensitive_logon is introduce in 11g to switch on/off

#
# Init.ora parameter
#

sec_case_sensitive_logon = (TRUE FALSE)

#
# On system level you can switch off
#

alter system set sec_case_sensitive_logon = false

#
#Find users who have case sensitive or case insensitive
#passwords
#

SQL> COL USERNAME form a19
SQL> SELECT USERNAME,PASSWORD_VERSIONS
FROM DBA_USERS where rownum <>


USERNAME PASSWORD
---------- --------
ABCXYZ12 10G 11G
ADKULABC 10G 11G
ORACLE12 10G 11G
VIRAGSHA 10G



Since password are case sensitive, so DB link created from pre-11G DB to 11G DB might not work. When you create database link in pre-11G DB by default password saved in upper case and when you use database link you will get error "ORA-01017: invalid username/password; logon denied"

When creating database link, use following work around to force case sensitive password

#
#
Use quote " with password like "tiger"
#

create database link abc connect to scott identified
by "tiger" using 'ORCL2';

Reference

Oracle Database Security Guide11g Release 1 (11.1)

Wednesday, August 22, 2007

SQL Performance Analyzer (SPA) Part - 2

Case: - You want to adjust some parameters in production and you want to check how over all application going to run i.e. is SQL performance improved or it degrade ?

Solution: - Oracle 11g nw features SQL Performance Analyzer (SPA) analyze the SQL performance impact of any type of system changes. SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement.

SPA report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, DBAs can remedy any negative outcome before their end users are affected and can validate, with significant time and cost savings, that the system change to the production environment will result in net improvemengt


The steps of the SQL Performance Analyzer workflow are as follows:

  1. Capture SQLs on Production Database
  2. Move SQLs to Test Database
  3. Create a SQL Performance Analyzer task on the test system
  4. Generate and store the query execution statistics before the change.
  5. Make changes (Database upgrade to 11g , parameter changes etc)
  6. Generate and store the query execution statistics after the change.
  7. Compare Performance
  8. Generate Report

1) Capture SQL ( Steps on production)

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';

BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,750000,1,
'MERGE',dbms_sqltune.MODE_ACCUMULATE_STATS);

END;
/

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

  • time_limit : 750000 ( The total amount of time, in seconds, to execute )
  • repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
  • capture_option During capture, either insert new statements, update existing ones, or both.
  • 'INSERT'
  • 'UPDATE',
  • 'MERGE'
  • capture_mode capture Option (UPDATE and MERGE capture options).

MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full
system workload by repeatedly polling the cursor cache over a specified interval.
This function is a lot more efficient than repeatedly using the SELECT_CURSOR_
CACHE and LOAD_SQLSET procedures to capture the cursor cache over an
extended period of time. This function effectively captures the entire workload, as
SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL
statements—or the LOAD_SQLSET procedure, which accesses the data source only
once.

2) Move SQLs to test system

Steps on production database

Create staging table for STS and pack STS data into staging table and import the staging table

a) Create a staging table

exec dbms_sqltune.create_stgtab_sqlset('SQLSET_TAB');

b) Pack the sqlset in a staging table

exec dbms_sqltune.pack_stgtab_sqlset('STS_RAG_CMUL','APPS','SQLSET_TAB','APPS');

c) Export the staging table

exp apps/apps file=sqlset_tab.dmp tables=sqlset_tab

Steps on test database

Import staging table and unpack staged data in STS

d) Import the staging table in the target database

imp apps/apps file=sqlset_tab.dmp full=y

e) unpack the staging table into sqlset

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.UNPACK_STGTAB_SQLSET(
SQLSET_NAME=>'%',
SQLSET_OWNER=>'APPS',
REPLACE=>true,
STAGING_TABLE_NAME=>'SQLSET_TAB',
STAGING_SCHEMA_OWNER=>'APPS'
);

3) Create a SQL Performance Analyzer task on the test system

Create a SQL Performance Analyzer task on the test system using the
SQL Tuning Set as its input source.

var l_task_id char(30)
begin
:l_task_id:=dbms_sqlpa.create_analysis_task(sqlset_name =>
'STS_RAG_CMUL',task_name => 'MY_REPLAY_TASK');
end;
/

>

Since we processing whole system load, So TIME_LIMIT
need to increase


exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'LOCAL_TIME_LIMIT',60);

exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'TIME_LIMIT',200000000000);

The time_limit parameter specifies the global time limit to process all SQL
statements in a SQL Tuning Set beforetiming out.

The local_time_limit parameter specifies the time limit to process each SQL
statement in a SQL Tuning Set before timing out.

4)Generate and store the query execution statistics before the change


Use SQL Performance Analyzer to build the pre-change performance data by executing the
SQL statements stored in the SQL Tuning Set.


Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'TEST EXECUTE',execution_name => 'MY_REPLAY_EXECUTE_BEFORE');
end;
/


Check status of Analysis task

SQL> col TASK_NAME format a30

SQL> col EXECUTION_NAME for a30

SQL> l
select execution_name, status, execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='MY_REPLAY_TASK'
order by execution_end

SQL> /

EXECUTION_NAME STATUS EXECUTION_END

-------------------- --------------------------- ---------------
MY_REPLAY_EXECUTE_BEFORE EXECUTING

--Check time

SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS
FROM USER_ADVISOR_TASKS
WHERE task_name = 'MY_REPLAY_TASK';

TASK_ID TASK_NAME TASK_NAME STATUS
---------- -------------------- -------------------- --------------
244563 MY_REPLAY_TASK MY_REPLAY_TASK EXECUTING


SQL> SELECT sofar, totalwork
FROM V$ADVISOR_PROGRESS
where TASK_ID=244563;

SOFAR TOTALWORK
---------- ----------
142 100630

5) Make database Changes

alter system set "_b_tree_bitmap_plans" = false;

--- More application change
--- Stats collection- etc

6) Generate and store the query execution statistics after the change.

Use SQL Performance Analyzer to build the post-change performance data
by re-executing the SQL statements in the SQL Tuning Set on the post-change
test system.

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_after');

end;
/

7) Compare execution plans

Use SQL Performance Analyzer to compare and analyze
the pre-change and post-change versions of performance data

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'COMPARE PERFORMANCE',
execution_name => 'MY_COMPARE_EXECUTION');

end;
/

8) Compare the execution changes due to your database changes

Generate a report to identify the SQL statements in the
SQL workload that have improved, remained unchanged, or
regressed after the system change. Review the report and
interpret the results.

--Displaying the Results of a SQL Tuning Task

set serveroutput on size 999999
set long 999999

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')from dual;

-- [ Click Here for Sample Report of above command ]OR


var rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK', -
'text', 'typical', 'summary');

SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130

PRINT :rep


--Note above commands will take few Min.

>

exec dbms_sqlpa.DROP_ANALYSIS_TASK('MY_REPLAY_TASK');


>

Some more article on 11g 

1. Oracle 11g TOP feature for DBA

2.11g Automatic Diagnostic Repository (ADR)

3. Sql Performance Analyzer (SPA) Part 1

4. Sql Performance Analyzer (SPA) Part 2

Tuesday, August 14, 2007

TOTAL RECALL Oracle 11g New Features



TOTAL RECALL, Oracle 11g New Feature
Virag Sharma virag123@gmail.com

Dont get confused with Arnold Picture and Movie call "Total Recall"
We going to discuss different total recall i.e. Oracle database 11g new features :- Total Recall

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

With Total Recall’s Flashback Data Archive technology, managing historical data should no longer be a painful task. It provides an extremely secure, efficient, easy to use and application transparent solution for managing historical data as well as a centralized, secure query-able historical data store that makes the most efficient use of all your resources – be it CPU, Storage or administrator time, and it enables and reduces your cost of compliance.

1) Creating a Flashback Data Archive
2) Specifying the Default Flashback Data Archive
3) Enabling and Disabling Flashback Data Archive
4) DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive
5) Viewing Flashback Data Archive Data

CREATE FLASHBACK ARCHIVE DEFAULT flatest TABLESPACE RAG_tbs1
QUOTA 10G RETENTION 1 YEAR;

ALTER TABLE emp FLASHBACK ARCHIVE flatest;

-When history data from emp exceeds the age of 1 years,
-it will purged


ALTER TABLE emp NO FLASHBACK ARCHIVE;

- Using Flashback Data Archive to Access Historical Data
- Emp Salary as on 31-DEC-2006 close of business Hr.

SELECT emp_id, sal FROM emp AS OF
TIMESTAMP TO_TIMESTAMP ('2006-12-31 17:00:00', 'YYYY-MM-DD HH24:MI:SS')


Coming soon ...all details about Total Recall



Restriction
Following DDL statements on a table enabled for Flashback
Data Archive causes error ORA-55610:

  • ALTER TABLE statement that does any of the following:
  • Drops, renames, or modifies a column
  • Performs partition or subpartition operations
  • Converts a LONG column to a LOB column
  • Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
  • DROP TABLE statement
  • RENAME TABLE statement
  • TRUNCATE TABLE statement


Refrence

Using Flashback Technology





Sunday, August 12, 2007

Oracle SQL REPLAY & Sql Performance Analyzer(SPA) Part 1

Oracle SQL REPLAY Part 1
Virag Sharma virag123@gmail.com


You can use Sql Performance Analyzer (SPA) to analyze the SQL performance impact of any type of system changes.

  • Implementation of tuning recommendations ( like parameter changes )
  • Schema changes (eg Application Patch )
  • Statistics gathering
  • Database upgrades
  • OS/hardware changes

SQL Performance Analyzer allows for the comparison of SQL performance statistics before and after changes and provide comparison report.

Steps for SQL REPLAY

  1. Collect Sqls
  2. Create the SQL Replay Task with SQL Tuning Set (STS)
  3. Collect SQL Performance Before Changes
  4. Make Database Change ( eg collect stats etc)
  5. Collect SQL Performance After Changes
  6. Comparing SQL Performance Before and After Change
  7. Display the Results of a SQL Replay Task


    Steps in details

1. Collect SQL

To create an SQL Tuning Set (STS)

exec dbms_sqltune.create_sqlset ('MYSQLSET');

PL/SQL procedure successfully completed.

Collect sqls in the SQL Tuning Set(STS) with all queries from the cursor cache.
You can call the procedure multiple times to add new SQL statements or replace
attributes of existing statements.

DECLARE

cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN cur FOR
SELECT VALUE(p)
FROM TABLE (dbms_sqltune.select_cursor_cache) p;

dbms_sqltune.load_sqlset(
sqlset_name => 'MYSQLSET',
populate_cursor => cur);
END;

/

Collect sql mentioned in AWR as well , You populate the tuning set with 'ACCUMULATE' as yourupdate_option because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.

DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN

OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,100) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET',
populate_cursor => cur,
load_option => 'MERGE',
update_option => 'ACCUMULATE');

END;

Note 1 is start snap id and 100 is END snap ID

  1. Create the SQL Replay Task with SQL Tuning Set (STS)

Create the SQL Replay Task with SQL Tuning Set(STS) named MYSQLSET

var l_task_id char(30)
begin
:l_task_id:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'MYSQLSET',
task_name => 'MY_REPLAY_TASK');
end;

/

Collect SQL Performance data Before Changes

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_BEFORE');
end;
/

  1. Make Database Change ( eg collect stats etc)

Make application , database changes

alter system set "_b_tree_bitmap_plans" = false;

alter system set optimizer_index_cost_adj=15

  1. Collect SQL Performance After Changes
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_after');
end;
/

  1. Comparing SQL Performance Before and After Change

Analyze the Performance (Improvement or regressions)

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'MY_COMPARE_EXECUTION');
end;

  1. Display the Results of a SQL Replay Task


-- Checking the Status of a SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'MY_REPLAY_TASK'

-- Checking the Progress of the SQL Tuning Advisor

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE task_name = 'MY_REPLAY_TASK'

--Displaying the Results of a SQL Tuning Task

set serveroutput on size 999999

set long 999999

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK') from dual;

--Note above command will take few Min.

OR

VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

- Sample Output of report

Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 92.33%

REP
------------------------------------------------
Improvement Impact : 92.33%
Regression Impact : 0%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 3813 0
Improved 2 0
Unchanged 3775 0
with Errors 36 0

In this article Sql Performance Analyzer Part 1,we just covered Sql Performance Analyzer (SPA) basic functionality , In part 2 I will cover more expects and case study of SPA

Some more article on 11g 

  1. Oracle 11g TOP feature for DBA
  2. 11g Automatic Diagnostic Repository (ADR)
  3. Sql Performance Analyzer (SPA) Part 1
  4. Sql Performance Analyzer (SPA) Part 2


SQL Tuning Information Views

· Advisor views,

  • DBA_ADVISOR_TASK
  • DBA_ADVISOR_EXECUTIONS,
  • DBA_ADVISOR_FINDINGS
  • DBA_ADVISOR_RECOMMENDATIONS
  • DBA_ADVISOR_RATIONALE views

· SQL tuning views,

  • DBA_SQLTUNE_STATISTICS
  • DBA_SQLTUNE_BINDS, and
  • DBA_SQLTUNE_PLANS views

· SQL Tuning Set views,

  • DBA_SQLSET,
  • DBA_SQLSET_BINDS,
  • DBA_SQLSET_STATEMENTS,
  • DBA_SQLSET_REFERENCES views.

· Captured execution plans for statements in SQL Tuning Sets

  • DBA_SQLSET_PLANS
  • USER_SQLSET_PLANS

· SQL tuning view,

  • V$SQL,
  • V$SQLAREA,
  • V$SQLSTATS,
  • V$SQL_BINDS views
· SQL Profile information is displayed in the DBA_SQL_PROFILES view.
The TYPE parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (if TYPE = MANUAL) or automatically by automatic SQL tuning (if TYPE = AUTO).

· Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.

References

Automatic SQL Tuning

Automatic Diagnostic Repository (ADR)

Automatic Diagnostic Repository (ADR)

Virag Sharma virag123@gmail.com

In Oracle 11g database New dimension fault diagnosability infrastructure added to Oracle self managing concept to reduce time for resolving problem/bug/SR and round trip between end-user and Oracle Support. Trace file, dump file , core file ADR, ADRCI etc are components of fault diagnosability infrastructure.

In early version of oracle, When ever critical error such as code bug , data corruption occur end user need to collect diagnostic data such as process dump , data structure dump etc. In oracle 11g database end user need to worry about what kind of dump need to collect , need not to search trace file which need to send to Oracle Support Oracle 11g Database proactively collect ,tagged and store diagnostic data in file based repository known as ADR. 11g used file based repository because even your database is down, you have diagnostic data to send to oracle support.

Key Components for Fault Diagnosability Infrastructure

  • ADR
  • ADRCI ( ADR Command line utility )
  • Alert log
  • Trace ,Dumps etc.

ADR (Automatic Diagnostic Repository)

ADR is file based repository for diagn

ostic data like trace file,process dump,data structure dump etc.
In oracle 11g trace. alert

not saved in *_DUMP_DEST directory even you set those parameters in init.ora.11g ignore *_DUMP_DEST and store data in new format , directory structure is given below

Diag/product_type/database_name/instance_name

ADR_base/diag/rdbms/orcl2/orcl2


Click on figure for clear view

Note : ADR_HOME is user define variable , I have define this variable make life easier

ADR root where ADR directory structure start.11g New initialize parameter DIAGNOSTIC_DEST decide location of ADR root,

  • By default location of DIAGNOSTIC_DEST is $ORACLE_HOME/log,
  • if ORACLE_BASE is set in environment then DIAGNOSTIC_DEST is set to $ORACLE_BASE

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and old style alert file in trace directory. Within ADR base, there can be many ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance. The location of an ADR home for a database is shown on the above graphic.

Note :- I have created on environment variable ADR_HOME= . I am using same in all my this document

SQL> show parameter diag
NAME TYPE VALUE
------------------------ ---------------------- ------------------------------
diagnostic_dest string /u01/app/oracle

Data

Old location

ADR location

Core Dump

CORE_DUMP_DEST

$ADR_HOME/cdump

Alert log data

BACKGROUND_DUMP_DEST

$ADR_HOME/trace

$ADR_HOME/alert (XML)

Background process trace

BACKGROUND_DUMP_DEST

$ADR_HOME/trace

User process trace

USER_DUMP_DEST

$ADR_HOME/trace



SQL> desc v$diag_info
Name Null? Type
----------------------------------------- -------- ----------------------------
INST_ID NUMBER
NAME VARCHAR2(64)
VALUE VARCHAR2(512)


SQL> select * from v$diag_info;

INST_ID NAME VALUE
---------- ------------------------- ---------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/orcl2/orcl2
1 Diag Trace /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
1 Default TraceFile /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_14385.trc
1 Active Problem Count 0
1 Active Incident Count 0


Retention policy

There is retention policy for ADR that allow to specify how long to keep the data
ADR incidents are controlled by two different policies:

  • The incident metadata retention policy ( default is 1 year )
  • The incident files and dumps retention policy ( Default is one month)

We can change retention policy using “adrci” MMON purge data automatically on expired ADR data.

adrci> show control

ADR Home = /u01/app/oracle *************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

3667832353 720 8760 2008-07-02 13:24:01.088681 -07:00 2008-07-22 00:20:04.113441 -07:00 1 2 0 1 2008-07-02 13:24:01.088681 -07:00

1 rows fetched

adrci>

Change Retention

adrci> set control (SHORTP_POLICY = 360 )
adrci>
set control (LONGP_POLICY = 4380 )

Automatic Diagnostic Repository (ADRCI)

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool. This tool
allow user to interact with ADR ,check alert log, check health monitor(HM) status , create report
on HM, Package incident and problem information into a zip file for send to Oracle Support. etc.
No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data
is secured only by operating system permissions on the ADR directories.
[Click Here for Oracle Documenation on adrci]

[oracle@apps001 ~] $ adrci
adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci>

adrci> SHOW INCIDENT
ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
********************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-05-14 18:41:03.609077
+05:30
1 incident info records fetched

Checking alert log with ADRCI

One can see alert log content with the help of ADRCI

adrci>>show alert

If just want to tail content of alert log

$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

Content of xml alert log ( log.xml )


type='UNKNOWN' level='16' host_id='apps001'
host_addr='192.20.182.113' module='' pid='19541'>
Incremental checkpoint up to RBA [0x10e.98cbe.0], current log tail at RBA [0x10e.98cea.0]

Since alert log saved as XML format ( log.xml ) , you can query xml file as well

Below is example to check all “ORA-“ in alert log

adrci>SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"

You can spool output for ADRCI using spool command same as we use in sqlplus

Problem and Incident

Problem

AD introduce new concept of problem and incident, problem is critical error in database and in ADR problem is identified by problem key. Problem key is consist of oracle error number, error parameter value etc
for example ORA600kci

Incident

Incident is single occurrence of problem , each incident is identified by unique number called incident id ,
which is unique in ADR home, all incident data stored in ADR. Each incident has a problem key and is
mapped to a single problem. When error occurred backup ground process make entry in alert.log and
collect data about incident (like process dump, data structure dump etc)

If similar incident happen more frequently , oracle will not collect data for all incident
By default only five dumps per hour for a given problem are allowed for single given problem and
this call flood control in 11g , some time you see "flood control" messages in alert.log / log.xml.
Incident can be created as manual as well, if needed.

adrci>>SHOW INCIDENT

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-05-14 18:41:03.609077 +05:30
1 incident info records fetched

IPS ( Incident package service )

DBA need not search trace , dump etc related particular error, to sent it to oracle support. In ADR
diagnostic data are tagged with incident id and IPS identified trace and dump for particular incident
and allow end user to create package from ADR to send to Oracle Support. Using IPS end user can
add some more file to package if needed.

How to create package

  • Create logical package
  • Add files to package
  • Create zip file to send to oracle support

Create logical package

Check incidents for which you want to create package

adrci>SHOW INCIDENT

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-08-14 18:41:03.609077
+05:30
1 incident info records fetched



We can use IPS CREATE PACKAGE command to create a logical package for above incident

adrci>ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

Add files to package

Adding diagnostic information for a particular (9817) incident

adrci>ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical
adrci>ips add incident 9817 package 4
Added incident 9817 to package 4

You can add additional files if needed, But file should be in ADR, below in example we adding alert log to package.

adrci> ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4
Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4 4

Create zip file to send to oracle support

adrci> ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20080814184516_COM_1.zip, mode
complete
adrci>

[root@apps001 tmp]#ls –l /tmp/*.zip

-rw-r--r-- 1 oracle oinstall 584410 May 14 18:50 ORA600kci_20080814184516_COM_1.zip

[root@apps001 tmp]# unzip -l ORA600kci_20080814184516_COM_1.zip
Archive: ORA600kci_20080814184516_COM_1.zip
Length Date Time Name
-------- ---- ---- ----
763210 05-14-08 18:41
diag/rdbms/orcl2/orcl2/incident/incdir_9817/orcl2_ora_5967_i9817.trm
2302506 05-14-08 18:41
diag/rdbms/orcl2/orcl2/incident/incdir_9817/orcl2_ora_5967_i9817.trc
186887 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log
491982 05-14-08 18:41 diag/rdbms/orcl2/orcl2/alert/log.xml
1122 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_diag_5931.trc
189 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_diag_5931.trm
1342 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_ora_5967.trc
773 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_ora_5967.trm
831 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_CONFIGURATION.dmp
338 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE.dmp
193 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_INCIDENT.dmp
1094 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_FILE.dmp
234 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_HISTORY.dmp
6004 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_FILE_METADATA.dmp
214 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_FILE_COPY_LOG.dmp
1273 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_DEF.dmp
1813 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp
204 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION.dmp
198 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp
353 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp
163 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp
614 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCIDENT.dmp
357 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCCKEY.dmp
202 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCIDENT_FILE.dmp
406 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/PROBLEM.dmp
710 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/HM_RUN.dmp
843 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_21.hm
708 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_41.hm
207 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/EM_USER_ACTIVITY.dmp
62624 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/config.xml 489 05-14-07 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/metadata.xml
9508 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/manifest_4_1.xml
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/alert/
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/cdump/
0 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/incident/
0 05-14-08 18:45 diag/rdbms/orcl2/orcl2/incpkg/
0 05-13-08 22:51 diag/rdbms/orcl2/orcl2/ir/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/lck/
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/metadata/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/stage/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/sweep/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/
489 05-14-08 18:49 metadata.xml
-------- -------
3838080 44 files
[root@apps001 tmp]#

Log a SR and upload this zip file to Oracle Support for diagnose and resolution.

IPS in Summary


$ adrci
adrci> help ips
adrci> show incident
( For example above command show incident No 9817 for ORA-600 [XYZ] )

adrci> ips create package incident 9817
<= ( it will give package No.)
adrci> ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

adrci> ips add incident 9817 package 4
Added incident 9817 to package 4

adrci>

adrci>>ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4

Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4

adrci>>ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20080814184516_COM_1.zip, mode
complete
adrci>>



Health Monitor (HM)

Health Monitor run diagnostic checks on various components of the database. Health Monitor checks
examine various components of the database, including files, memory, transaction integrity, metadata,
and process usage. In order to collect more data after critical error (incident) , oracle invoke health
monitoring implicitly. If need end-user can also run health monitoring procedure manually

Health Monitor checks run in two ways:

  • Reactive: The fault diagnosability infrastructure can invoke Health Monitor checks automatically
    in response to critical errors.
  • Manual: DBA can manually run Health Monitor health checks Manually

Kinds of health monitoring

Please look at the V$HM_CHECK view , it will list all Health monitoring checks

SQL> desc v$hm_check
Name Null? Type
--------- -------- -------
ID NUMBER
NAME VARCHAR2(64)
CLSID NUMBER
CLS_NAME VARCHAR2(15)
FLAGS NUMBER
INTERNAL_CHECK VARCHAR2(1)
OFFLINE_CAPABLE VARCHAR2(1)
DESCRIPTION VARCHAR2(64)

SQL> select name from v$hm_check ;

NAME
-------------------------
HM Test Check
Database Cross Check
Data Block Check
Redo Check
Logical Block Check
Table Check
Table-Index Cross Check
Table Row Check
Table-Index Row Mismatch
Transaction Check
Undo Segment Check
All Control Files Check
CF Member Check
All Datafiles Check
Single Datafile Check
Log Group Check
Log Group Member Check
Archived Log Check
Redo Revalidation Check
IO Revalidation Check
Block IO Revalidation Check
Txn Revalidation Check
Failure Simulation Check
Database Dictionary Check

25 rows selected.

High Lighted HM , we going to run Manually in next step

Health monitoring report

The checker generates a report of its execution in XML and stores the reports in ADR.
You can view these reports using either V$HM_RUN, DBMS_HM, ADRCI, or Enterprise Manager.

Run Health Check Manually

SQL> exec dbms_HM.RUN_CHECK('Database Dictionary Check');

PL/SQL procedure successfully completed.

$adrci
adrci>SHOW HM_RUN

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:

*************************************************************************

----------------------------------------------------------

RUN_ID 1
RUN_NAME HM_RUN_1
CHECK_NAME Database Cross Check
NAME_ID 2
MODE 2
START_TIME 2008-08-05 04:01:56.783059 +05:30
RESUME_TIME
END_TIME 2008-08-08 04:02:04.007178 +05:30
MODIFIED_TIME 2008-08-08 04:02:04.007178 +05:30
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE

----------------------------------------------------------

RUN_ID 21

RUN_NAME HM_RUN_21

CHECK_NAME Database Dictionary Check
NAME_ID 24
MODE 0
START_TIME 2008-08-13 23:09:43.831573 +05:30
RESUME_TIME
END_TIME 2008-08-13 23:09:47.713191 +05:30
MODIFIED_TIME 2008-08-14 00:03:01.470031 +05:30
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_21.hm

2 rows fetched

Create HM Report

adrci>>CREATE REPORT HM_RUN HM_RUN_21

You can create and view Health Monitor checker reports using the ADRCI utility. Make sure that Oracle environment variables are set properly, The ADRCI utility starts and displays its prompt as shown above.
You then enter the SHOW HM_RUN command to list all the checker runs registered in the ADR repository. Locate the checker run for which you want to create a report and note the checker run name using the corresponding RUN_NAME field. you can generate the report using the CREATE REPORT HM_RUN command. You view the report using the SHOW REPORT HM_RUN command or by running dbms_hm.get_run_report on sql prompt

View HM generated reports on SQL prompt

SQL> set long 1000000
SQL> select dbms_hm.get_run_report('HM_RUN_21') from dual;

DBMS_HM.GET_RUN_REPORT('HM_RUN_21')

--------------------------------------------------------------------------------
................

View HM generated reports on adrci prompt

adrci>>show report hm_run HM_RUN_21

View HM generated reports on OS level ( In ADR repository )

$cd $ADR_HOME/hm
$ pwd
/u01/app/oracle/diag/rdbms/orcl2/orcl2/hm

$ ls

HMREPORT_HM_RUN_21.hm

$ more HMREPORT_HM_RUN_21.hm

Conclusion

That was just a quick glance at some of the new features in Oracle Database 11g Release 1. Now I'm off to read the documentation in full so I can take advantage of all the new stuff.
Google