InstallUseStandbyStatspackin11g

InstallUseStandbyStatspackin11g

Statspack/AWR cannot be executed on a standby due to its read-only nature. Therefore tuning the performance of the apply process involves manually collecting statistics. 

In 11gR1, using the new Active Data Guard option, users can now use statspack from the primary database to collect data from a standby database that is opened read-only and performing recovery. The standby statspack is installed in a separate schema on the Primary database, STDBYPERF which is then propagated to the standby. This new user does not have DBA privileges and has no access to local V$ tables.

SCOPE

This document applies to 11gR1 and up.

DETAILS

1. Standby Statspack Usage

• 1.1 Statspack Installation

• The perfstat schema and statspack related objects must be present on the primary and standby prior to installing standby statspack.
• If the perfstat user does not exist in the primary site then you will need to create the perfstat schema using the following:

• sqlplus / as sysdba
• SQL> @?/rdbms/admin/spcreate.sql
• The standby statspack installation script (sbcreate.sql) creates the standby statspack schema to hold the standby snapshots. The script asks for:
  1. A password for stdbyperf user
  2. Default tablespace
  3. Temporary tablespace
• The script creates the ‘stdbyperf’ user and assigns it the selected default and temporary table space. After the user has been created it calls sbaddins.sql to add the first standby instance to the configuration.

• Example:
  SQL> @?/rdbms/admin/sbcreate
• Note: Due to Bug 11899453, ensure global_names is set to 'FALSE' on the Standby Database while you run this Script. See Note 11899453.8 for some more Details.
• Ensure the Initialization Parameter 'open_cursors' is set to at least 100 on the Primary and Standby Database.
• 1.2 Add an Instance to Statspack Configuration

• Log in to the primary as the 'stdbyperf' user and run the script sbaddins.sql to add a standby instance to the configuration. The script asks for:

• ## The TNS alias of the standby database instance
  1. The password of the perfstat user on the standby site
• The script then creates a private database link to the perfstat schema on the standby site and a separate gathering package for each standby instance. Example:

• SQL> connect stdbyperf/your_password
• SQL> @?/rdbms/admin/sbaddins
• Input inst2_alias as the tns alias.
• 1.3 Collect Performance Data from a Standby Instance

• The script sbaddins.sql creates a separate PL SQL package for each standby instance. Run the snap procedure of those packages to collect the performance data. The statspack_<instance_name>.snap procedure accesses the data dictionary and stats$ views on the standby database via database link connected to the original perfstat user und stores the data to stats$ tables on the primary instance. For example, while the standby is opened read only, login to the primary database and create the snap:

• SQL> connect stdbyperf/your_password
• SQL> exec statspack_<db_unique_name>_<instance_name>.snap
• 1.4 Generate Standby Statistics Report

• The script sbreport.sql generates the standby statistics report. The script asks for: database id, instance number, high and low snapshots id to create the report. Example:

• SQL>@?/rdbms/admin/sbreport
• 1.5 Purge a Set of Snapshots

• The script sbpurge.sql purges a set of snapshots. The script asks for database id, instance number, low and high snapshots ids. The script purges all snapshots between the low and high snapshot ids for the given instance. Example:

• SQL>@?/rdbms/admin/sbpurge
• 1.6 Delete an Instance from the Configuration
• The script sbdelins.sql deletes an instance from the configuration, and deletes the associated PL SQL package. The scripts asks for instance name. The snapshots are not automatically purged when the instance is deleted. After deleting the instance, you are not able to generate reports for that instance. Example:

• SQL> @?/rdbms/admin/sbdelins
• 1.7 Drop Statspack Schema

• The script sbdrop.sql drops the stdbyperf user and tables. The script must be run when connected to SYS (or internal).
• Note that if you consider to reinstall Statspack you may have to drop all public synonyms for STATSPACK and STAT$%%. Else you may get an Error when trying to run spcreate again.
• Example:

• SQL> connect / as sysdba
• SQL> @?/rdbms/admin/sbdrop

2. New Statistics Collected

• Two new sections are added to standby statspack report: (1) Recovery Progress Stats, and (2) Managed Standby Stats. An example is provided below.

• Recovery Progress Stats DB/Inst: MADISON/madison1 End Snap: 2

• -> End Snapshot Time: 20-Jun-07 13:59:29

• -> ordered by Item, Recovery Start Time desc

• Recovery Start Time Item Sofar Units Redo Timestamp

• ------------------- ----------------- -------------- ------- ------------------

• 08-Jun-07 11:58:15 Active Apply Rate 8,420 KB/sec

• 08-Jun-07 11:58:15 Active Time 4,291 Seconds

• 08-Jun-07 11:58:15 Apply Time per Lo 267 Seconds

• 08-Jun-07 11:58:15 Average Apply Rat 3 KB/sec

• 08-Jun-07 11:57:15 Average Apply Rat 955 KB/sec

• 08-Jun-07 11:58:15 Checkpoint Time p 0 Seconds

• 08-Jun-07 11:58:15 Elapsed Time 1,044,073 Seconds

• 08-Jun-07 11:57:15 Elapsed Time 6 Seconds

• 08-Jun-07 11:58:15 Last Applied Redo 15,273,580 SCN+Tim 20-Jun-07 13:59:29

• 08-Jun-07 11:57:15 Last Applied Redo 13,945,701 SCN+Tim 08-Jun-07 11:56:16

• 08-Jun-07 11:58:15 Log Files 16 Files

• 08-Jun-07 11:57:15 Log Files 33 Files

• 08-Jun-07 11:58:15 Redo Applied 3,181 Megabyt

• 08-Jun-07 11:57:15 Redo Applied 6 Megabyt

• -------------------------------------------------------------

• Managed Standby Stats DB/Inst: MADISON/madison1 End Snap: 2

• -> End Snapshot Time: 20-Jun-07 13:59:29

• -> ordered by Process

• Process pid Status Resetlog Id Thread Seq Block Num

• ----------- ---------- ------------ ----------- ------ ------- -----------

• Client Proc Client pid Blocks Delay(mins)

• ----------- ---------- -------------- --------------

• ARCH 29360 CLOSING 624693241 2 59 18433

• ARCH 29360 340 0

• ARCH 29358 CLOSING 624693241 1 57 94209

• ARCH 29358 1,596 0

• ARCH 29356 CLOSING 624693241 1 56 903169

• ARCH 29356 1,835 0

• ARCH 29354 CLOSING 624693241 2 61 919553

• ARCH 29354 770 0

• MRP0 30839 APPLYING_LOG 624693241 2 62 57

• N/A N/A 2,097,152 0

• RFS 28886 IDLE 0 0 0 0

• N/A 16388 0 0

• RFS 28875 IDLE 624693241 2 62 2164

• LGWR 16137 432 0

• RFS 30192 IDLE 624693241 1 58 51

• LGWR 2092 1 0

• RFS 28892 IDLE 0 0 0 0

• UNKNOWN 16384 0 0

• RFS 30326 IDLE 0 0 0 0

• N/A 2268 0 0

• -------------------------------------------------------------

• 3. List of New Scripts and Short Descriptions

• All those Scripts below are located in $ORACLE_HOME/rdbms/admin

• sbcreate.sql - Install standby statspack

• sbcusr.sql - Called from sbcreate.sql to create the schema

• sbctab.sql - Called from sbcreate.sql to create tables holding snapshots

• sbaddins.sql - Called from sbcreate.sql to add a standby database instance to the configuration.

• sbaddins.sql - Add a standby database instance to the configuration

• sbcpkg.sql - Called from sbaddins.sql to create the instance specific statspack package

• sblisins.sql - List instances in the standby statspack configuration

• sbreport.sql - Create a standby statistics report

• sbrepcon.sql - Called from sbreport.sql to get the report configuration

• sbrepins.sql - Called from sbreport.sql to create the actual report

• sbpurge.sql - Purge a set of snapshots identified by low and high snapshot ids

• sbdelins.sql - Delete an instance from the standby statspack configuration

• sbdrop.sql - Drop the stdbyoperf user and tables of the standby statspack

• sbdtab.sql - Called from sbdrop.sql to drop tables

• sbdusr.sql - Called from sbdrop.sql to drop user, must run from an account that connects to internal (SYS)

• PLEASE NOTE
• It is possible during the creation of the standby statspack package through the execution of spcreate.sql that it fails reporting the following error:

• ORA-00972: identifier is too long

• This has resulted from the length of package names being limited to 30 characters.

• To overcome this you would need to edit the script $ORACLE_HOME/rdbms/admin/sbaddins.sql and change the following line

• FROM:

• insert into stats$standby_config

• values ('&&db_unique_name'

• , '&&inst_name'

• , 'STDBY_LINK_'||'&&tns_alias'

• , 'STATSPACK_'||'&&db_unique_name'||'_'||'&&inst_name');

• TO:

• insert into stats$standby_config

• values ('&&db_unique_name'

• , '&&inst_name'

• , 'STDBY_LINK_'||'&&tns_alias'

• , 'SP_'||'&&db_unique_name'||'_'||'&&inst_name');

• Invoking the package to collect snapshots would then mean you need to execute

• SP_<db_unique_name>_<instance_name>.snap

• NOTE: if you are not sure how to do this contact support and this ONLY needs to be altered if the number of characters in STATSPACK_<db_unique_name>_<instance_name> exceeds 30.