Time for action – using the ASH report from the standby database

From 11gR2 onwards, the Active Session History report can be created to monitor the performance of a standby database from the standby system. To use this feature, ensure that the database is up-and-running in the Read-Only mode and the session history has retained the memory. Then, you can perform the following steps:

  1. Generate an ASH report using the ashrpt.sql script. Before using this report you must know what parameters we have to pass while the report is running, for example, duration between two dates and times. Use the ashrpt.sql script as follows:
    SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1316772835 ORCL                1 INDIA
You are running ASH report on a Standby database. To generate the report
over data sampled on the Primary database, enter 'P'.
Defaults to 'S' - data sampled in the Standby database.
Enter value for stdbyflag: S
Using Primary (P) or Standby (S): S

    Once you initiate the ASH report, you can choose the text or HTML option for the report. The default mode of the ASH report is HTML. In the second phase you have the option to choose the instance type, so select the instance type as Standby.

  2. Now you have to specify the timeframe to generate the ASH report. Specific to this report, you can choose Sysdate-10. Hence, from the current date and time it will get the session history report for the past 10 minutes as follows:
    Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: -10
Report begin time specified: -10
  3. You can mention the report name or it will be created using the default name, which uses the time and interval of the selection as shown in the following code:
    </table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
End of Report
</body></html>
Report written to ashrpt_1_1107_1557.html
SQL>

    This report will be generated from the default directory where you been logged into the SQL *Plus session. Now you can save or open in any browser to view the statistics.

A sample ASH report output of a standby database is shown in the following screenshot:

Time for action – using the ASH report from the standby database

You can see the wait events Standby redo I/O and RFS write that are related to a standby database, as shown in the following screenshot:

Time for action – using the ASH report from the standby database

What just happened?

We've just revised how to generate an active session history report from a standby database; this report helps us to find out the top wait events with the standby database and also to find any sort of issue either with the database or with redo being written.

Have a go hero – running Statspack from a standby database

You can add a standby instance in Statspack to create reports specifically related to a standby database. The high-level steps are as follows:

  1. Ensure Statspack is already installed using @?/rdbms/admin/sbcreate.sql.
  2. The database must not shut down between two snapshot times to gather reports. For that, use the script @?/rdbms/admin/sbreport.sql.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.226.177.85