Dataguard從庫效能的監控

SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password

-----------------------------------

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle

oracle

Choose the Default tablespace for the PERFSTAT user

---------------------------------------------------

Below is the list of online tablespaces in this database which can

store user data. Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace. This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME      CONTENTS STATSPACK DEFAULT TABLESPACE

------------------------------ --------- ----------------------------

SYSAUX             PERMANENT *

USERS             PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user

-----------------------------------------------------

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas). Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME      CONTENTS DB DEFAULT TEMP TABLESPACE

------------------------------ --------- --------------------------

TEMP             TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace:

SQL> @?/rdbms/admin/sbcreate

Choose the STDBYPERF user's password

-----------------------------------

Not specifying a password will result in the installation FAILING

Enter value for stdbyuser_password: oracle

oracle

Choose the Default tablespace for the STDBYPERF user

---------------------------------------------------

Below is the list of online tablespaces in this database which can

store user data. Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the STDBYPERF users's default tablespace. This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME      CONTENTS STATSPACK DEFAULT TABLESPACE

------------------------------ --------- ----------------------------

SYSAUX             PERMANENT *

USERS             PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as STDBYPERF default tablespace.

Choose the Temporary tablespace for the STDBYPERF user

-----------------------------------------------------

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas). Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the STDBYPERF user's Temporary tablespace.

TABLESPACE_NAME      CONTENTS DB DEFAULT TEMP TABLESPACE

------------------------------ --------- --------------------------

TEMP             TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

SQL> @?/rdbms/admin/sbaddins

The following standby instances (TNS_NAME alias) have been configured

for data collection

DATABASE    INSTANCE DB LINK PACKAGE

----------------- -------------- ---------------------------- -------------------------------

TIANJIN      joe     STDBY_LINK_TIANJIN STATSPACK_TIANJIN_joe

=== END OF LIST ===

THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

Do you want to continue (y/n) ?

Enter value for key:

You entered:

Enter the TNS ALIAS that connects to the standby database instance

-----------------------------------------------------------------

Make sure the alias connects to only one instance (without load balancing).

Enter value for tns_alias: TIANJIN #tnsnames檔案配置

You entered: TIANJIN

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: oracle # PERFSTAT使用者的密碼

You entered: oracle

[oracle@db01 admin]$ sqlplus stdbyperf/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 2 02:42:09 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec statspack_TIANJIN_joe.snap

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/sbreport

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name         Instance Name

------------------------------ ----------------

TIANJIN          joe

Enter the DATABASE UNIQUE NAME of the standby database to report

Enter value for db_unique_name: TIANJIN

You entered: TIANJIN

Enter the INSTANCE NAME of the standby database instance to report

Enter value for inst_name: joe

You entered: joe

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 <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                 Snap

Instance Snap Id Snap Started      Level Comment

------------ --------- -------------------- -----------

joe     3 02 Mar 2017 02:42 5

DB Unique Name Instance Startup Time Release RAC

------------------------------ ------------ --------------- ----------- ---

TIANJIN joe 18-Apr-17 14:05 11.2.0.3.0 NO

Host Name: db02 Num CPUs: 2 Phys Memory (MB): 2,000

~~~~

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~ ---------- ------------------ -------- --------- -------------------

Begin Snap: 1 01-Mar-17 05:27:28 32 .8

End Snap: 2 01-Mar-17 05:28:26 32 .7

Elapsed: 0.97 (mins)

Cache Sizes Begin End

~~~~~~~~~~~ ---------- ----------

Buffer Cache: 356M Std Block Size: 8K

Shared Pool: 224M Log Buffer: 7,032K

Load Profile Total Per Second

~~~~~~~~~~~~ ------------------ -----------------

DB time(s): 1.6 0.0

DB CPU(s): 1.2 0.0

Redo MB applied: 1.4 0.0

Logical reads: 1,972.0 34.0

Physical reads: 12.0 0.2

Physical writes: 1,560.0 26.9

User calls: 492.0 8.5

Parses: 211.0 3.6

Hard parses: 163.0 2.8

W/A MB processed: 11.3 0.2

Logons: 1.0 0.0

Executes: 1,095.0 18.9

Rollbacks: 0.0 0.0

Instance Efficiency Indicators

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %:

Buffer Hit %: 99.85 Optimal W/A Exec %: 100.00

Library Hit %: 78.84 Soft Parse %: 22.75

Execute to Parse %: 80.73 Latch Hit %: 100.00

Parse CPU to Parse Elapsd %: 47.98 % Non-Parse CPU: 45.39

Shared Pool Statistics Begin End

------ ------

Memory Usage %: 55.11 64.14

% SQL with executions>1: 71.92 83.57

% Memory for SQL w/exec>1: 75.27 89.08