Dataguard從庫效能的監控
【前言】Oracle 11G開始支援了active dataguard,這時候從庫就可以分擔一些主庫的讀的壓力了。這種架構有個問題就是從庫的效能壓力很難排除出來。有個朋友也是使用從庫進行資料的抽取,但是這個從庫每隔一段時間就會由於壓力過大而導致系統當機了。在排查問題的時候很多時候需要讀取awr報告,但是從庫的awr報告是屬於主庫的,給整個排查增加了難度。還好這個時候從庫是可以生成statspack報告的。
這個操作需要進行一些簡單的配置,以下的操作都是在主庫上面進行的,透過sys使用者登入,詳細操作如下:
- 建立statspack 所需要的schemas:PERFSTAT
- 設定使用者的密碼
- 資料表空間
- Temp表空間
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: |
- 建立schemas:stdbyperf
- 設定使用者的密碼
- 資料表空間
- Temp表空間
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. |
- 建立stdbyperf的dblink
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 |
- stdbyperf收集系統效能資訊
SQL> connect stdbyperf/your_password
SQL> exec statspack_<db_unique_name>_<instance_name>.snap
[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. |
- stdbyperf收集系統效能資訊
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 |
- 其他刪除操作
SQL>@?/rdbms/admin/sbpurge #刪除快照
SQL>@?/rdbms/admin/sbdelins #刪除配置
SQL>@?/rdbms/admin/sbdrop #刪除schema
- 詳細報告
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 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-2137984/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【DATAGUARD 學習】監控primary庫和standby庫
- 資料庫效能監控資料庫
- 監控資料庫效能的SQL資料庫SQL
- 監控備庫效能,為Active DataGuard的備庫生成statspack報告並實現定時傳送
- oracle資料庫效能監控的SQL(轉)Oracle資料庫SQL
- 監控資料庫效能的SQL彙總資料庫SQL
- 【DataGuard】Oracle DataGuard 日誌傳輸狀態監控Oracle
- 資料庫效能SQL監控指令碼資料庫SQL指令碼
- 監控長時間執行的查詢(監控資料庫效能的SQL ) -- 轉資料庫SQL
- 前端效能監控前端
- php效能監控PHP
- 監控寶SQL Server效能監控的功能和配置SQLServer
- 資料庫效能監控瓶頸理論資料庫
- 監控Oracle資料庫效能的指令碼段整理Oracle資料庫指令碼
- APM效能監控軟體的監控型別服務及監控流程型別
- AIX常用的效能監控命令AI
- Performance --- 前端效能監控ORM前端
- 效能監控工具YourKit
- JVM 效能監控工具JVM
- CentOS效能監控工具CentOS
- 效能監控調優
- MySQL sys效能監控MySql
- 某次資料庫效能監控中發現的問題資料庫
- sqlserver資料庫系統效能監控步驟SQLServer資料庫
- 一種對雲主機進行效能監控的監控系統及其監控方法
- Android的效能監控工具StrictModeAndroid
- 用LoadRunner監控windows的效能Windows
- 效能測試之資料庫監控分析工具PMM資料庫
- mysql效能監控相關MySql
- iOS網路效能監控iOS
- linux效能監控命令Linux
- Chrome DevTools - 效能監控Chromedev
- 效能監控命令總結
- mysql效能監控指標MySql指標
- Linux 效能監控工具Linux
- 效能測試之JVM的監控GrafanaJVMGrafana
- JAMon監控web工程方法的呼叫效能Web
- 資料庫監控資料庫