offline troubleshooting

February 28, 2013 — Leave a comment

the situation: a customer faces issues with his oracle database. you cannot go on-site for some reasons and you cannot connect to the database in question. what options do you have to support them ?

  • request a current awr/ash report and try find an issue from there
  • request execution plans if the issue is with bad performing statements and try if you might see anything there without knowing the data
  • try to support them by phone

there is another option some people might not know or are not aware of: dumping the awr data and importing it to your own database for further analysis. as this will include only awr data no user data will be submitted ( except for literals in sql statements, if there are any ) and you don’t need to worry about data privacy.

first of all you need to ask for an awr extract. there is a script which does all the work and you just need to execute it:

SQL> @?/rdbms/admin/awrextr.sql

the header displayed already tells you what the script will do and how to use it:

~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the	   ~
~  following information:					   ~
~     (1) database id						   ~
~     (2) snapshot range to extract				   ~
~     (3) name of directory object				   ~
~     (4) name of dump file					   ~

the parameters the script will ask for are the same as when creating awr reports except you’ll additionally need a directory to store the dump file ( DATA_PUMP_DIR, by default ).

that’s it. the script will start the dump process:

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /opt/oracle/product/base/
|   demo.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  *** AWR Extract Started ...
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /opt/oracle/product/base/
|   rman.log

End of AWR Extract

… and once finished the dump file can be copied from the location provided:

SQL> !ls /opt/oracle/product/base/
rman.dmp  rman.log  dp.log

so far for the dump part. once you received the dump file how to import it into your own database ? not a big deal:

SQL> @?/rdbms/admin/awrload.sql

again, the header will tell you pretty much everything you’ll need to know:

~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object			      ~
~     (2) name of dump file				      ~
~     (3) staging schema name to load AWR data into	      ~

a few things to take care of:

  • when you get asked for the dumpfile pass the filename without the file extension ( in my case “rman” instead of “rman.dmp” )
  • the load will temporarily create a staging schema ( AWR_STAGE ) and will drop it once the load finished

so, now you have the remote databases’ awr data available on your private database. what to do next ? maybe, in the first step you would like to create a set of awr reports to get an overview of the remote system. now, your are able to do this.

in a first step you’ll need the dbid and instance id for the remote database:

               from DBA_HIST_DATABASE_INSTANCE;

------------- --------------- --------- ---------------- --------------------
   3551282359		    1 DEMO	demo		 ol6ora.local
   1664148667		    1 RMAN	rman		 ol6ora2.local

in may case “RMAN” is the remote database for which the awr statistics where imported to my local database “DEMO”. let’s see what snapshots are available for this dbid:

           from dba_hist_snapshot where DBID=1664148667;

------- ------------------------- -------------------------
   8096 16-FEB-13 AM 16-FEB-13 AM
   8097 16-FEB-13 AM 16-FEB-13 AM
   8098 16-FEB-13 AM 16-FEB-13 AM
   8099 16-FEB-13 AM 16-FEB-13 AM
   8100 16-FEB-13 AM 16-FEB-13 AM
   8101 16-FEB-13 AM 16-FEB-13 AM
   8102 16-FEB-13 AM 16-FEB-13 AM
   8103 16-FEB-13 AM 16-FEB-13 PM

that’s enough information to produce some reports:

SQL> define inst_num     = 1;
SQL> define inst_name    = 'rman';
SQL> define db_name      = 'RMAN';
SQL> define dbid         = 1664148667;
SQL> define begin_snap   = 8098;
SQL> define end_snap     = 8100;
SQL> define report_type  = 'html';
SQL> define report_name  = /tmp/first_awr_report.html
SQL> @?/rdbms/admin/awrrpti.sql

maybe the reports you generated are sufficient for giving the right hints. but there is even more you could do with all the statistic data available:

as an example, let’s say you want to display the load of the server for a period of two snapshots. for this there is statistic called “LOAD” which you can query:

SELECT to_char(t.begin_interval_time, 'DD.MM.YYYY-HH24:MI:SS' )
     , o.value
  FROM dba_hist_osstat o
     , dba_hist_snapshot t
 WHERE o.dbid = 1664148667
   AND o.dbid = t.dbid
   AND o.snap_id = t.snap_id
   AND o.snap_id BETWEEN 8098 AND 8110
   AND o.stat_name = 'LOAD'
 ORDER BY o.snap_id;

------------------- ----------
16.02.2013-06:00:57 .829101563
16.02.2013-07:00:59 .629882813
16.02.2013-08:00:01 .309570313
16.02.2013-09:00:04   .1796875
16.02.2013-10:00:06 .329101563
16.02.2013-11:00:09 .279296875
16.02.2013-12:00:11 .119140625
16.02.2013-13:00:13 .649414063
16.02.2013-14:00:15 .919921875
16.02.2013-15:00:17 .459960938
16.02.2013-16:00:20 .889648438
16.02.2013-17:00:22 .629882813
16.02.2013-18:00:24 .549804688

wouldn’t it be nice to display theses values in a picture ? put these numbers to a file:

echo "16.02.2013-06:00:57 .829101563
16.02.2013-07:00:59 .629882813
16.02.2013-08:00:01 .309570313
16.02.2013-09:00:04   .1796875
16.02.2013-10:00:06 .329101563
16.02.2013-11:00:09 .279296875
16.02.2013-12:00:11 .119140625
16.02.2013-13:00:13 .649414063
16.02.2013-14:00:15 .919921875
16.02.2013-15:00:17 .459960938
16.02.2013-16:00:20 .889648438
16.02.2013-17:00:22 .629882813
16.02.2013-18:00:24 .549804688" > /tmp/graph1.lst

define a gnuplot definition:

echo "set terminal png 
set xlabel \"Time\"
set xdata time
set timefmt "%d.%m.%Y-%H:%M:%S" 
set title 'OS-Load'
set format y \"%10.1f\" 
plot \"/tmp/graph1.lst\" using 1:2 with lines title 'OS Load'" > /tmp/graph1.plot

.. create the png file:

gnuplot /tmp/graph1.plot > graph1.png

… and have a look at it:

display graph1.png

server load

for those who don’t know: dbms_xplan also works with dbid, so you may generate the execution plans for statements from the remote instance ( although the below is not a very useful example ):

 FROM TABLE ( dbms_xplan.display_awr ( '3s1hh8cvfan6w'
                                     , '2137789089'
                                     , 1664148667     -- remote_dbid
                                     , 'ADVANCED' ) );
Plan hash value: 2137789089

| Id  | Operation			  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT		  |	    |	    |	    |	 30 (100)|	    |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |	 30   (4)| 00:00:01 |

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$F5BB74E1 / KOKBF$0@SEL$2

Outline Data
      OPT_PARAM('query_rewrite_enabled' 'false')
      FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")

combine all this into a set of scripts and you are ready to do some analysis even if you do not have access to the database. together with the power of gnuplot for creating nice graphs you are in a position for doing an initial troubleshooting.

two more sample images generated out of the awr data:

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.