使用statspack监控Active Dataguard的性能—2-创建ADG的性能报告

后台执行一个脚本，模拟登录风暴

#!/bin/bash
for i in {1..1000}
do
echo $i
sqlplus -S lunar/lunar@DB16<<EOF
select instance_name from v\$instance;
EOF
sleep 1
done
exit 0

[oracle@lunar ~]$ nohup ./login.sh &
[1] 27907
[oracle@lunar ~]$ nohup: appending output to `nohup.out’

[oracle@lunar ~]$ jobs
[1]+ Running nohup ./login.sh &
[oracle@lunar ~]$ jobs
[1]+ Running nohup ./login.sh &
[oracle@lunar ~]$ jobs
[1]+ Running nohup ./login.sh &
[oracle@lunar ~]$ jobs
[1]+ Running nohup ./login.sh &
[oracle@lunar ~]$ jobs
[1]+ Running nohup ./login.sh &
[oracle@lunar ~]$
[oracle@lunar ~]$

收集一个快照：
08:06:05 STDBYPERF@lunarp>exec STATSPACK_lunarc_lunarc.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.49
08:06:13 STDBYPERF@lunarp>

过几分钟，再次打一个快照：
08:08:36 STDBYPERF@lunarp>exec STATSPACK_lunarc_lunarc.snap;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.71
08:10:36 STDBYPERF@lunarp>

做个报表：
08:10:36 STDBYPERF@lunarp>@?/rdbms/admin/sbreport

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name Instance Name
—————————— —————-
lunarc lunarc

Enter the DATABASE UNIQUE NAME of the standby database to report
Enter value for db_unique_name: lunarc
You entered: lunarc

Enter the INSTANCE NAME of the standby database instance to report
Enter value for inst_name: lunarc
You entered: lunarc

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 without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap
Instance Snap Id Snap Started Level Comment
———— ——— —————– —– ——————–
lunarc 1 03 Aug 2014 08:08 5
2 03 Aug 2014 08:10 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sb_lunarc_lunarc_1_2. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:
。。。。。。。。。。。。这里省略部分报告内容

init.ora Parameters DB/Inst: lunarc/lunarc Snaps: 1-2

End value
Parameter Name Begin value (if different)
—————————– ——————————— ————–
audit_file_dest /u01/app/oracle/admin/lunarc/adum
audit_trail NONE
compatible 11.2.0.3.0
control_files /tempdisk/lunarc/control01.ctl, /
db_block_size 8192
db_domain
db_file_name_convert /stage/lunar/, /tempdisk/lunarc/
db_name lunar
db_recovery_file_dest /stage/fast_recovery_area
db_recovery_file_dest_size 1218762752
db_unique_name lunarc
deferred_segment_creation FALSE
diagnostic_dest /u01/app/oracle
fal_client lunarc
fal_server lunarp, lunars
filesystemio_options setall
log_archive_config DG_CONFIG=(lunarp,lunars,lunarc)
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DES
log_archive_dest_2 SERVICE=lunarp LGWR ASYNC AFFIRM
log_archive_dest_3 SERVICE=lunars LGWR ASYNC AFFIRM
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 ENABLE
log_file_name_convert /stage/lunar/, /tempdisk/lunarc/
open_cursors 300
pga_aggregate_target 153092096
processes 150
remote_login_passwordfile EXCLUSIVE
sec_return_server_release_ban FALSE
service_names lunar
sga_target 629145600
standby_file_management AUTO
undo_tablespace UNDOTBS1
————————————————————-

End of Report ( sb_lunarc_lunarc_1_2.lst )

08:11:58 STDBYPERF@lunarp>!
[oracle@lunar ~]$ ls
awrinfo.txt lunar_baby.txt lunar.env nohup.out sbctab.lis sbdtab.lis sb_lunarc_lunarc_1_2.lst spctab.lis spdtab.lis test
login.sh lunar_banner.txt lunarstb.env sbcpkg.lis sbcusr.lis sbdusr.lis spcpkg.lis spcusr.lis spdusr.lis tmp
[oracle@lunar ~]$

可以看到下面的报告每秒钟登录110次，O(∩_∩)O哈哈~：

---

---

这里使用的完整的示例报告如下：
sb_lunarc_lunarc_1_2