---------------------------------------------------------------
Make sure the alias connects to only one instance (without load balancing).
Enter value for tns_alias: DG2
You entered: DG2
Enter the PERFSTAT user's password of the standby database
---------------------------------------------------------
Performance data will be fetched from the standby database via
database link. We will connect to user PERFSTAT.
Enter value for perfstat_password: Amy
You entered: Amy
... Creating database link
... Selecting database unique name
Database
------------------------------
dg2
... Selecting instance name
Instance
------------
dg2
... Creating package
Creating Package STATSPACK_dg2_dg2..
No errors.
Creating Package Body STATSPACK_dg2_dg2..
No errors.
NOTE:
SBCPKG complete. Please check sbcpkg.lis for any errors.
SQL>
SQL> undefine key tns_alias inst_name perfstat_password pkg_name db_unique_name
SQL>
SQL>
SQL>
SQL>
SQL>
至此完成了逻辑备库的dg性能分析工具的statspack的创建。
那么如何使用呢？
需要在主库上执行所创建的包，来收集备库信息，如下：
SQL> connect stdbyperf/Amy
Connected.
SQL> exec statspack_dg2_dg2.snap;
SQL>
SQL>
SQL>
SQL> exec statspack_dg2_dg2.snap;
SQL> @ /rdbms/admin/sbreport.sql
SQL> Rem
SQL> Rem sbreport.sql
SQL> Rem
SQL> Rem Copyright (c) 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sbreport.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script calls sbrepins.sql to produce standby statspack report
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must run as the standby statspack owner, stdbyperf
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem shsong 02/15/07 - fix bug
SQL> Rem wlohwass 12/04/06 - Created, based on spreport.sql
SQL>
SQL>
SQL> @@sbrepins
SQL> Rem
SQL> Rem sbrepins.sql
SQL> Rem
SQL> Rem Copyright (c) 2001, 2012, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem sbrepins.sql - StandBy statspack REPort INStance
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*Plus command file to report on differences between
SQL> Rem values recorded in two snapshots.
SQL> Rem
SQL> Rem This script requests the user to provide database unique name
SQL> Rem and instance number of the instance to report on, then produce
SQL> Rem the standby statspack report.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Run as the standby statspack owner, stdbyperf
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem shsong 01/25/10 - Bug 9307098
SQL> Rem shsong 08/21/09 - use db_unique_name as primary key
SQL> Rem shsong 06/16/08 - add active_agents to Managed Standby Stats
SQL> Rem shsong 02/05/07 - Add stats for recovery_progress etc
SQL> Rem shsong 02/04/07 - Created
SQL> Rem
SQL>
SQL> set echo off;
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Unique Name Instance Name
------------------------------ ----------------
dg2 dg2
Enter the DATABASE UNIQUE NAME of the standby database to report
Enter value for db_unique_name: dg2
You entered: dg2
Enter the INSTANCE NAME of the standby database instance to report
Enter value for inst_name: dg2
You entered: dg2
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing witho