Oracle exports Ash's dump information

Recently, while looking at some Oracle shares, often mentioned that the export of ash dump to other people to do the analysis, but I do not have any relevant operation, do not know what is the operation of the process, so the Internet read a variety of blog posts. So do your own experiments to do an export import.

Lab Environment: Oracle 11.2.0.4+rhel 7.2

Execute the following statement to do a dump operation on Ash information

[Email protected]>alter system Set Events ' immediate trace name ashdump level 10 ';

The level in the above statement means to dump the last n minutes of data from ash buffer, which takes 10 minutes as an example.

Use the following statement to hit the corresponding trace file

[Email protected]>select value from v$diag_info WHERE name = ' Default Trace File '; VALUE-------------------------------------------------------------------/u01/app/oracle/diag/rdbms/ora11g/ Ora11g/trace/ora11g_ora_7556.trc

Let's look at the contents of the trace file briefly:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/07/44/wKiom1nGKjDybuqCAADAJ3BVIGQ357.png "style=" float : none; "title=" 1.png "alt=" Wkiom1ngkjdybuqcaadaj3bvigq357.png "/>

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/A5/F5/wKioL1nGKfiycWVXAACIYhiYpMA350.png "style=" float : none; "title=" 2.png "alt=" Wkiol1ngkfiycwvxaaciyhiypma350.png "/>

As you can see from the two diagrams above, the trace file contains three types of information: The basic information of trace, the method of importing the dump and the actual data of ash, in fact, it is easy to import the dump data into the database simply by the method given in the trace file.

1. Create a temporary table

CREATE TABLE ashdump Asselect * from SYS. Wrh$_active_session_history WHERE rownum < 0;

2. Generate Sqlldr Control file

[Email protected] ash]$ sed-n ' 1,/^step 2:/d;/^step 3:/, $d;p ' ora11g_ora_7556.trc |sed '/^-/d ' > Ashldr.ctl

3. Use the Sqlldr tool to import data from the trace file into a temporary table

[[email protected] ash]$ sqlldr zx/zx control=ashldr.ctl data=ora11g_ora_7556. trc errors=1000000sql*loader: release 11.2.0.4.0 - production on sat  sep 23 17:16:40 2017copyright  (c)  1982, 2011, oracle and/or its  affiliates.  all rights reserved. commit point reached - logical record count 19commit point  Reached - logical record count 37commit point reached - logical  record count 55Commit point reached - logical record count  73commit point reached - logical record count 91commit point  Reached - logical record count 109commit point reached - logical  record count 127commit point reached - logiCal record count 145commit point reached - logical record count  163commit point reached - logical record count 181commit point  reached - logical record count 199Commit point reached -  logical record count 217commit point reached - logical record  count 235commit point reached - logical record count 253commit  point reached - logical record count 271commit point reached -  logical record count 289commit point reached - logical record  count 307commit point reached - logical record count 325commit  point reached - logical record count 343commit point reached  - logical record&Nbsp;count 361commit point reached - logical record count 379commit  point reached - logical record count 397commit point reached  - logical record count 415Commit point reached - logical  record count 433commit point reached - logical record count  451commit point reached - logical record count 469commit point  Reached - logical record count 487commit point reached - logical  record count 505commit point reached - logical record count  523commit point reached - logical record count 541commit point  reached - logical record count 559Commit point reached -  logical record count 577commit point reached - logical record count 595commit point  Reached - logical record count 613commit point reached - logical  record count 631commit point reached - logical record count  649Commit point reached - logical record count 651[email  Protected]>select count (*)  from ashdump;  count (*)----------        650

Once the data has been imported successfully, you can now do a closer analysis based on the specific problem.

In fact, the steps to export and import are very simple, and Oracle gives the steps directly, and it's hard to figure out how to use the data to perform a near-step analysis to find the root cause of the problem.

The number of sessions per Sampletime is analyzed as follows

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/A5/F5/wKioL1nGLUGRx4j_AABJYZtjTBQ239.png "title=" Capture.png "alt=" Wkiol1nglugrx4j_aabjyztjtbq239.png "/>

Reference: http://www.eygle.com/archives/2009/08/howto_dump_ashinfo.html

https://antognini.ch/2017/07/offline-analysis-of-ash-data-with-ashdump/

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1968021

Oracle exports Ash's dump information