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:
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
.
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
</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:
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:
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.
You can add a standby instance in Statspack to create reports specifically related to a standby database. The high-level steps are as follows:
@?/rdbms/admin/sbcreate.sql
.@?/rdbms/admin/sbreport.sql
.