offline troubleshooting

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:

~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  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/11.2.0.3/rdbms/log/
|   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/11.2.0.3/rdbms/log/
|   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/11.2.0.3/rdbms/log/
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:

~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  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:

SQL> select DBID,INSTANCE_NUMBER,DB_NAME,INSTANCE_NAME,HOST_NAME 
               from DBA_HIST_DATABASE_INSTANCE;

	 DBID INSTANCE_NUMBER DB_NAME	INSTANCE_NAME	 HOST_NAME
------------- --------------- --------- ---------------- --------------------
   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:

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME 
           from dba_hist_snapshot where DBID=1664148667;

SNAP_ID BEGIN_INTERVAL_TIME	  END_INTERVAL_TIME
------- ------------------------- -------------------------
   8096 16-FEB-13 04.00.52.846 AM 16-FEB-13 05.00.54.990 AM
   8097 16-FEB-13 05.00.54.990 AM 16-FEB-13 06.00.57.548 AM
   8098 16-FEB-13 06.00.57.548 AM 16-FEB-13 07.00.59.883 AM
   8099 16-FEB-13 07.00.59.883 AM 16-FEB-13 08.00.01.952 AM
   8100 16-FEB-13 08.00.01.952 AM 16-FEB-13 09.00.04.200 AM
   8101 16-FEB-13 09.00.04.200 AM 16-FEB-13 10.00.06.321 AM
   8102 16-FEB-13 10.00.06.321 AM 16-FEB-13 11.00.09.183 AM
   8103 16-FEB-13 11.00.09.183 AM 16-FEB-13 12.00.11.274 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;

TO_CHAR(T.BEGIN_INT	 VALUE
------------------- ----------
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 ):

SELECT * 
 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
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('query_rewrite_enabled' 'false')
      ALL_ROWS
      FORCE_XML_QUERY_REWRITE
      XML_DML_RWT_STMT
      XMLINDEX_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      NO_COST_XML_QUERY_REWRITE
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
      END_OUTLINE_DATA
  */

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: