Oracle 11g New feaures Case Sensitive Password

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)

SQL Performance Analyzer (SPA) Part - 2

SQL Performance Analyzer (SPA) Part - 2
Virag Sharma virag123@gmail.com

[Click Here for PDF format]

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

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

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

2. 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; /

4. 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

5. 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; /

6. 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;

7. 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

• Oracle 11g Performance Tuning Guide - 23.SQL Performance Analyzer
• Oracle 11g PL/SQL Types and Packages Reference - DBMS_SQLPA
• Oracle 11g Reference
  
  • DBA_ADVISOR_TASKS
  • DBA_ADVISOR_EXECUTIONS
  • DBA_ADVISOR_LOG
  • DBA_ADVISOR_FINDINGS
  • DBA_ADVISOR_SQLPLANS
  • DBA_ADVISOR_SQLSTATS
    

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.