Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 求dataguard怎么采集awr或sp报告的详细步骤. ...

lxhbww

164 积分	0 好友	16 主题

发消息

求dataguard怎么采集awr或sp报告的详细步骤.

1^#

发表于 2012-3-26 11:30:19 | 查看: 8505| 回复: 10

分享0

收藏0 回复只看该作者道具举报

tohjb

2^#

发表于 2012-3-26 22:18:12

@？/rdbms/admin/awrrpt.sql

是说的这个吗？

回复只看该作者道具举报

vicry21

3^#

发表于 2012-3-27 13:52:31

dg和single database 没啥区别啊。

回复只看该作者道具举报

xifenfei

4^#

发表于 2012-3-30 08:42:13

干嘛要在备库上采集，备库的信息和主库一样的啊

回复只看该作者道具举报

回忆未来

5^#

发表于 2012-3-30 12:48:38

standby 正常情况下是不能做AWR ,因为AWR要做一些SQL 递归的操作,而standby 是做不了的

ERROR at line 5:
ORA-06550: line 5, column 13:
PL/SQL: ORA-01219: database not open: queries allowed on fixed tables/views
only
ORA-06550: line 4, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 13:
PL/SQL: ORA-01219: database not open: queries allowed on fixed tables/views
only
ORA-06550: line 10, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 5:
PLS-00201: identifier 'RAISE_APPLICATION_ERROR' must be declared
ORA-06550: line 23, column 5:
PL/SQL: Statement ignored
ORA-06550: line 33, column 5:
PLS-00201: identifier 'RAISE_APPLICATION_ERROR' must be declared
ORA-06550: line 33, column 5:
PL/SQL: Statement ignored

可以自己写一些脚本做standby 的一些检查

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-3-30 19:31:52

ODM FINDING:

Installing and Using Standby Statspack in 11g

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
Purpose
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 and Application
This document applies to 11gR1 and up.
Installing and Using Standby Statspack in 11g
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.  The standby statspack installation script (sbcreate.sql) creates the standby statspack schema to hold the standby snapshots. The script asks for:

         A password for stdbyperf user
         Default tablespace
         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> @sbcreate

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
      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> @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_<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>@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>@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> @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). Example:

      SQL> connect / as sysdba
      SQL> @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)

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-3-30 19:35:30

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.

==> 由于STANDBY的READ ONLY特性所以 statspack/AWR无法在Standby上使用，但是还是通过V$视图来收集性能信息。

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.

=>从11gR1开始使用Active Data Guard选项后，可以在Primary上收集Standby数据库的性能数据。

这是11gR1的新特性，具体见以上Note。

回复只看该作者道具举报