Oracle
Database 11g introduced Oracle Active Data Guard. With Active Data Guard, the physical
standby database can also be opened for read-only access.
Such a
standby, which has been opened in Active Data Guard mode keeps itself continuously
updated with changes from the primary - and at the same time - it can support read-only uses like generation of
reports.
With
Active Data Guard, the standby, which was in the nomount mode is now able to
support reporting needs. It helps makes fuller use of the standby server’s computing
resources while also relieving the primary reporting workload. All this, while
still preserving the data-protection objectives of Data Guard.
When you
have a reporting application workload that issues SQL against the standby
database, it will have some response time and throughput expectations, which
raises the question of tuning the workload.
The
standby database is doing media recovery. It is read-only and does not collect
AWR snapshots for the workload running against it. Therefore, the AWR based
tuning approach is not available for a standby database.
Oracle provides
a statspack based mechanism for tuning such databases.
Let us
explore this technique.
First you
have to install the regular statspack related schema.
Then,
after ensuring the standby is in open and in read only mode, configure the
STDBYPERF schema, which will store the snapshot data from standby.
The
primary will be the repository of the snapshots created on the standby, and it
will pull the snapshot information from the standby through a database link
that gets created as a part of the statspack configuration set up. Also, because
its statspack, the snapshot creation is manual.
References:
Pre-requisite :
Standby
database must be open READ-ONLY during this process.
First
task, is to run spcreate.sql --- create the ‘normal’ statspack schema.
Connect to
the database as SYS and run $ORACLE_HOME/rdbms/admin/perfstat.sql.
That
script will:
- Create the PERFSTAT user
- Create the supporting tables – named STATS$%.
- Install the STATSPACK package
But when
run against a container database, this script has a bug. Due to the bug, the
script attempts to create a common user without the C## prefix, resulting in this
error.
ORA-65096:
invalid common user or role name
After
googling, I could find a workaround for that one, which leads to successful
creation of the PERFSTAT user as a common user – without the C## prefix – by
setting an _oracle_script=true.
When
executed finally, the script asked for a password, tablespace, and a temp
tablespace for the perfstat schema.
I accepted
the defaults (password=oracle, tablespace=SYSAUX, temp tablespace=TEMPCDB)
The next
step is to run sbcreate.sql. This script will create the user named STDBYPERF,
which will be the actual repository for the standby snapshots, but reside in
the primary.
About sbcreate.sql
Like
spcreate, this script also needs a ‘tweak’ to enable the creation of the
STDBYPERF user without the C## prefix. This script is similar to spcreate.
- It creates the STDBYPERF schema and its tables.
- It will install the statspack package – standby version.
- Additonally, it will execute sbaddins.sql, which will add the standby instance to the statspack being configured on the primary.
About sbaddins.sql
This
script’s job is to add a standby instance to the Statspack configuration. A
Statspack configuration initially has 0 standby databases. It should be run as
the STDBYPERF user.
It will create
a database link which connects to the standby. Performance data from the
standby will be fetched across this link and saved in the primary.
The name of the database link is stdby_link_xxxxxx where xxxxx=tns alias of standby.
Having
added the standby database to the configuration with sbaddins, we can take
snapshots.
To take a
snapshot run this at SQL AS the STBYPERF user.
SQL >
EXECUTE STATSPACK_<DB_UNIQUE_NAME>_<INSTANCE_NAME>.snap
That’s
because the STDBYPERF statspack packge is named in that manner. You need at
least 2 snapshots to generate a statspack report.
Please see
below:
SQL> execute statspack_cdb1_dr_cdb1_dr.snap;
BEGIN statspack_cdb1_dr_cdb1_dr.snap; END;
*
ERROR at line 1:
ORA-28113: policy predicate has error
ORA-02063: preceding line from STDBY_LINK_CDB1_DR
ORA-06512: at
"STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR", line 3562
ORA-06512: at
"STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR", line 5412
ORA-06512: at "STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR",
line 101
ORA-06512: at line 1
There was
an error when executing the snapshot extraction function for the 2nd
snapshot:
Note that
this error is being reported in the primary from which you connected and issued
the snapshot request. However, the problem actually was encountered in the
standby – which is where the the snapshot-taking will occur.
You will
not find the trace files relating to this error on the primary. I was at a puzzled,
until I poked into the statspack package source and realized that is doing
snapshot activity against the standby.
Then, I
examined the standby trace file CDB1_DR_ora_5525.trc, and sure enough, found
the error:
Error information for ORA-28113:
Logon
user : PERFSTAT
Table/View : SYS.V_$RULE_SET
VPD Policy
name : CON_ID
Policy
function: SYS.CON_ID
RLS
view :
SELECT
"OWNER","NAME","CPU_TIME","ELAPSED_TIME","FIRST_LOAD_TIME","LAST_LOAD_TIME","LAST_LOADING_TIME","SHARABLE_MEM","RELOADS","INVALIDATIONS","EVALUATIONS","FIRST_HIT_EVALUATIONS","SIMPLE_RULES_ONLY_EVALUATIONS","SQL_FREE_EVALUATIONS","SQL_EXECUTIONS","CONDITIONS_PROCESSED","TRUE_RULES","MAYBE_RULES","VARIABLE_VALUE_FUNCTION_CALLS","VARIABLE_METHOD_FUNCTION_CALLS","EVALUATION_FUNCTION_CALLS","RESULT_CACHE_HITS","IS_RESULT_CACHE","RESULT_CACHE_ELEMENTS","CON_ID"
FROM "SYS"."V_$RULE_SET"
"V_$RULE_SET" WHERE (con_id IN (0, 1) )
ORA-00604: error occurred at
recursive SQL level 3
ORA-01000: maximum open cursors
exceeded
-------------------------------------------------------------
***
2015-03-13 18:24:35.058
Then I
checked the open_cursors, it was very low:
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
open_cursors
integer 50
After
fixing it, the snapshots got captured. The snapshots are shipped to the primary
and stored there via a dblink created for this purpose. The dblink is created
by sbaddins.sql script
SQL> select db_link, username,host,created from
user_db_links;
DB_LINK USERNAME HOST CREATED
----------------------------------- --------------------
--------------- -----------
STDBY_LINK_CDB1_DR PERFSTAT CDB1_DR 13-Mar-15 5
Having created the snapshots, you can generate a statspack report.
Below is a sample invocation
SQL>
@?/rdbms/admin/sbreport.sql;
Instances in this Statspack
schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Unique Name Instance Name
------------------------------
----------------
CDB1_DR CDB1_DR
Enter the DATABASE UNIQUE NAME
of the standby database to report
Enter value for
db_unique_name: CDB1_DR
You entered: CDB1_DR
Enter the INSTANCE NAME of the
standby database instance to report
Enter value for inst_name:
CDB1_DR
You entered: CDB1_DR
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
------------ ---------
----------------- ----- --------------------
CDB1_DR 1 13 Mar 2015 17:21 5
8 13 Mar 2015 18:23 5
12 13 Mar 2015 19:04 5
13 13 Mar 2015 19:04 5
Specify the Begin and End
Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 12
Begin Snapshot Id specified:
12
Enter value for end_snap: 13
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name
is sb_CDB1_DR_CDB1_DR_12_13. To use this
name,
press <return> to
continue, otherwise enter an alternative.
Enter value for report_name:
Below are
the partial contents statspack report… quite similar to awr.
STATSPACK Statistics Report for Physical Standby
Database
~~~~~~~~
DB Unique Name
Instance Startup Time Release
RAC
------------------------------ ------------ --------------- -----------
---
CDB1_DR
CDB1_DR 13-Mar-15 14:22
12.1.0.2.0 NO
Host Name: labomsb.example. Num CPUs: 2
Phys Memory (MB): 1,955
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ----------
------------------ -------- --------- -------------------
Begin Snap: 12 13-Mar-15
19:04:33 32 .8
End Snap: 13 13-Mar-15 19:04:47 32
1.7
Elapsed: 0.23 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ----------
----------
Buffer Cache: 564M Std Block Size: 8K
Shared Pool: 216M Log Buffer: 5,064K
Load
Profile Total Per Second
~~~~~~~~~~~~
------------------
-----------------
DB time(s): 6.7 0.5
DB CPU(s): 1.3 0.1
Redo MB applied: 1.3 0.1
Logical reads: 4,714.0 336.7
Physical reads: 264.0 18.9
Physical writes: 254.0 18.1
User calls: 167.0 11.9
Parses: 750.0 53.6
Hard parses: 145.0 10.4
W/A MB processed:
13.4 1.0
Logons: 0.0 0.0
Executes: 1,958.0 139.9
Rollbacks: 0.0 0.0
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00
Redo NoWait %:
Buffer Hit
%: 96.41 Optimal W/A Exec %: 100.00
Library Hit %:
78.20 Soft Parse %: 80.67
Execute to Parse %: 61.70
Latch Hit %: 100.00
Parse CPU to Parse Elapsd %:
84.62 % Non-Parse CPU: 51.47
Shared Pool Statistics Begin
End
------ ------
Memory Usage %: 77.72
85.74
Thanks for reading.
