RMF has database link from both primary to standby and vice versa. Before a role transition additional work must be done by way of creating database link between future primary and future standby. In contrast the statspack only create one database link between primary and standby. As long as the TNS entry used for creating the DB link is available in the future primary then the statspack would continue to function. This is especially useful in a configuration where only subset of instances are candidate to become primary and all others are part of a reader farm.
In a multi-tenant architecture the data guard works on CDB level. As such the standby statspack is installed on the root container. This will create two users perfstat and stdbyperf. To accomplish this the statspack and standby statspack must be installed using catcon.pl script (Refer 2020285.1). The setup of standby consists of running spcreate.sql (normal statspack) and sbcreate.sql (standby statspack) scripts.
A script defining the statspack setup parameters could be used to automate the statspack creation. Output below shows content of a such script, which defines tablespace to create the statspack related tables and the password for perfstat user among other things.
cat sp.sql define default_tablespace='STATSPACKTBS' define temporary_tablespace='TEMP' define perfstat_password='asanga123' @?/rdbms/admin/spcreate.sql; @/home/oracle/statpack/chng.sql;The chng.sql contains the additional work must be done after setting up statspcak. This include issues mentioned in 382993.1 and 2437142.1. Content of this files is shown below.
cat chng.sql alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK; create index perfstat.STATS$MUTEX_SLEEP_PK on STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION); insert into stats$idle_event select name from v$event_name where wait_class='Idle' minus select event from stats$idle_event;If perl binary out of ORACLE_HOME is not in the path then following error will occur when running catcon.pl
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spcreate sp.sql Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/) at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin//catcon.pm line 497. BEGIN failed--compilation aborted at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin//catcon.pm line 497. Compilation failed in require at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/catcon.pl line 165. BEGIN failed--compilation aborted at /opt/cx/app/oracle/product/19.x.0/dbhome_1/rdbms/admin/catcon.pl line 165.To rectify this issue put perl binary out of OH into the path and run the catcon.pl specifying sp.sql script.
export PATH=$ORACLE_HOME/perl/bin:$PATH $ which perl /opt/cx/app/oracle/product/19.x.0/dbhome_1/perl/bin/perl perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spcreate sp.sql
Next step is to create the standby statspack. A similar script could be created for that as well. The most important parameter in this case is the TNS alias which will be used to create the database link that will connect to the standby.
cat sb.sql define default_tablespace='STATSPACKTBS' define temporary_tablespace='TEMP' define stdbyuser_password='asanga321' define perfstat_password='asanga123' define key='y' define tns_alias='ppdbxdb5tns' @?/rdbms/admin/sbcreate.sql; @/home/oracle/statpack/sbchng.sql;The sbchng.sql has additional work must be done on standby statspack. As stdbyperf user doesn't have access to v$event_name the idle event related workaround must be done after the setup. Also the STATS$MUTEX_SLEEP on standby statspack doesn't have DBID or instance number columns.
SQL> desc stdbyperf.stats$mutex_sleep Name Null? Type ----------------------------------------- -------- ---------------------------- SNAP_ID NOT NULL NUMBER DB_UNIQUE_NAME NOT NULL VARCHAR2(30) INSTANCE_NAME NOT NULL VARCHAR2(16) MUTEX_TYPE NOT NULL VARCHAR2(32) LOCATION NOT NULL VARCHAR2(40) SLEEPS NUMBER WAIT_TIME NUMBERInstead db_unique_name and instance_name must be used
cat sbchng.sql alter table stdbyperf.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK; CREATE INDEX stdbyperf.STATS$MUTEX_SLEEP_PK ON stdbyperf.STATS$MUTEX_SLEEP(SNAP_ID,DB_UNIQUE_NAME,INSTANCE_NAME,MUTEX_TYPE, LOCATION);Run the standby statspack creation with
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbcreate sb.sqlOnce standby statspack is created run as sys the following to update the idle events in stdbyperf user schema.
insert into stdbyperf.stats$idle_event select event from perfstat.stats$idle_event minus select event from stdbyperf.stats$idle_event;Additional standby instaces could be added to the standby statspack by using sbaddins.sql. This must be run using stdbyperf and must specify the TNS entry to the standby db. A script similar to below could be used for accomplishing this. As all the standby database instnaces use same set of tables no need to do the additional work on the subsequent standby instances.
cat sbaddin.sql connect stdbyperf/asanga321 define key='y' define tns_alias='ppdbxdb6tns' define perfstat_password='asanga123' @?/rdbms/admin/sbaddins.sql; perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbaddins sbaddin.sqlLogin as stdbyperf and run the below to view the current statspack standby configuration. This only shows the standby instances added to statspack. Not the actual data guard configuation.
select * from stats$standby_config ; DB_UNIQUE_NAME INST_NAME DB_LINK PACKAGE_NAME ------------------------------ ---------------- -------------------------------- ---------------------------- ppdbxdb5 ppdbxdb5 STDBY_LINK_ppdbxdb5tns STATSPACK_ppdbxdb5_ppdbxdb5 ppdbxdb6 ppdbxdb6 STDBY_LINK_ppdbxdb6tns STATSPACK_ppdbxdb6_ppdbxdb6
As mentioned earlie the standby statspack uses common set of tables to store data from all the standby instances. Only unique objects created for each standby instance are the database link and PL/SQL package which has the name STATSPACK_<db_unique_name>_<instance_name>. The corresponding package for the particular standby instance must be used in order to work with the instance. This inlcuding taking snapshots, changing statspack parameters and etc. For example in orde to change the snap level on ppdbxdb5 should run the STATSPACK_PPdbxDB5_PPdbxDB5 package. Similarly to change snap level on ppdbxdb6 should use STATSPACK_PPdbxDB6_PPdbxDB6. Below output shows chaning snap level on ppdbxdb6.
exec STATSPACK_PPdbxDB6_PPdbxDB6.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter=>'true'); select DB_UNIQUE_NAME,INSTANCE_NAME,snap_level from stats$statspack_parameter; DB_UNIQUE_NAME INSTANCE_NAME SNAP_LEVEL ------------------------------ ---------------- ---------- ppdbxdb5 ppdbxdb5 7 ppdbxdb6 ppdbxdb6 7Standby statspack use a common sequence to create snap id. As such when multiple instances takes snapshots there will be gaps between two consecutive snap ids.
Manual snapshots could be taken by running STATSPACK_<db_unique_name>_<instance_name>.snap. Snapshot taking is not automated and has to be done manually. Scheduling it in a dbms_scheduler job would allow the snapshot taking to work even after swichover in a reader farm configuration mentioned at the begining of the post.
The user stdbyperf doesn't have permission to create scheduler job. Grant create job and execute on dbms_scheduler (if revoked from public) to stdbyperf. Could create one scheduler job for each standby or have a single wrapper procedure with snap call for each standby. Below shows two separate scheduler job for automating snapshot taking.
--ppdbxdb5
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STANDBY_STATSPACK_PPdbxDB5',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_PPdbxDB5_PPdbxDB5.snap',
start_date => TO_TIMESTAMP('00' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/
--ppdbxdb6
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STANDBY_STATSPACK_PPdbxDB6',
job_type => 'STORED_PROCEDURE',
job_action => 'STATSPACK_PPdbxDB6_PPdbxDB6.snap',
start_date => TO_TIMESTAMP('01' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/When having multiple scheduler jobs are invoked at the same time it is possible to run into the following error.2020-06-29T18:02:49.768983+00:00 Errors in file /opt/cx/app/oracle/diag/rdbms/ppdbxdb1/ppdbxdb1/trace/ppdbxdb1_j001_119494.trc: ORA-12012: error on auto execute of job "STDBYPERF"."STANDBY_STATSPACK_PPdbxDB5" ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 3909 ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 5486 ORA-06512: at "STDBYPERF.STATSPACK_PPdbxDB5_PPdbxDB5", line 101 ORA-06512: at line 1 2020-06-29T18:06:57.404823+00:00To eliminate this have a time gap between two schedulers. Above schedulers are scheduled 1 minute apart.
Create standby statspack report by calling the sbreport. This will prompt to select the db_unique_name and instance_name and prompt the range of snap ids to create the statspack report.
SQL> @?/rdbms/admin/sbreport Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Unique Name Instance Name ------------------------------ ---------------- ppdbxdb5 ppdbxdb5 ppdbxdb6 ppdbxdb6 Enter the DATABASE UNIQUE NAME of the standby database to report Enter value for db_unique_name: ppdbxdb5 You entered: ppdbxdb5 Enter the INSTANCE NAME of the standby database instance to report Enter value for inst_name: ppdbxdb5 You entered: ppdbxdb5 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. PressingStandby statspack report will have a separate section on standby recoverywithout specifying a number lists all completed snapshots. Listing all Completed Snapshots Snap Instance Snap Id Snap Started Level Comment ------------ --------- ----------------- ----- -------------------- ppdbxdb5 14 30 Jun 2020 00:00 7 16 30 Jun 2020 01:00 7 18 30 Jun 2020 02:00 7 20 30 Jun 2020 03:00 7 22 30 Jun 2020 04:00 7
Recovery Progress Stats DB/Inst: ppdbxdb5/ppdbxdb5 End Snap: 32
-> End Snapshot Time: 30-Jun-20 09:00:04
-> ordered by Recovery Start Time desc, Units, Item asc
Recovery Start Time Item Sofar Units Redo Timestamp
------------------- ----------------- -------------- ------- ------------------
15-Jun-20 15:18:07 Log Files 11,360 Files
15-Jun-20 15:18:07 Active Apply Rate 659 KB/sec
15-Jun-20 15:18:07 Average Apply Rat 12 KB/sec
15-Jun-20 15:18:07 Maximum Apply Rat 13,280 KB/sec
15-Jun-20 15:18:07 Redo Applied 14,936 Megabyt
15-Jun-20 15:18:07 Recovery ID 0 RCVID
15-Jun-20 15:18:07 Last Applied Redo 0 SCN+Tim 30-Jun-20 09:00:15
15-Jun-20 15:18:07 Active Time 29,277 Seconds
15-Jun-20 15:18:07 Apply Time per Lo 1 Seconds
15-Jun-20 15:18:07 Checkpoint Time p 0 Seconds
15-Jun-20 15:18:07 Elapsed Time 1,273,511 Seconds
15-Jun-20 15:18:07 Standby Apply Lag 2 Seconds
-------------------------------------------------------------Purging of snapshots must also be done calling the purge procedure of the relavent standby instance package. Following procedure delete snapshots after 32 days of creation.create table deleted_snaps(delete_time timestamp, min_snapid number, max_snapid number, DB_UNIQUE_NAME VARCHAR2(30), INSTANCE_NAME VARCHAR2(16), primary key (DB_UNIQUE_NAME,INSTANCE_NAME,delete_time)); create or replace Procedure Delete_Snaps As min_snap_id stats$snapshot.snap_id%type; max_snap_id stats$snapshot.snap_id%type; db_unq_name stats$snapshot.DB_UNIQUE_NAME%type; ins_name stats$snapshot.INSTANCE_NAME%type; snapshots_purged pls_integer; stm varchar2(500); cursor delsnaps is select DB_UNIQUE_NAME,INSTANCE_NAME,min(snap_id) as min, max(snap_id) as max from stats$snapshot where to_char(snap_time,'YYYY-MM-DD') = to_char(sysdate-32,'YYYY-MM-DD') group by DB_UNIQUE_NAME,INSTANCE_NAME; begin open delsnaps; loop fetch delsnaps into db_unq_name,ins_name,min_snap_id,max_snap_id; Exit When Delsnaps%Notfound; stm := 'begin STATSPACK_'||db_unq_name||'_'||ins_name||'.purge(i_begin_snap =>'||min_snap_id||', i_end_snap =>'|| max_snap_id||', i_snap_range => true, i_extended_purge => true, I_DB_UNIQUE_NAME => '''||db_unq_name||''', I_INSTANCE_NAME => '''||ins_name||'''); end;'; dbms_output.put_line(stm); execute immediate stm; commit; Insert Into Deleted_Snaps values(Systimestamp,Min_Snap_Id,Max_Snap_Id,db_unq_name,ins_name); end loop; Close Delsnaps; commit; End; /Schedule the deletion of snapshots.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'Delete_Snaps_job', job_type => 'STORED_PROCEDURE', job_action => 'Delete_Snaps', start_date => trunc(SYSDATE + 1,'DD') + 15/1444, repeat_interval => 'FREQ=DAILY', enabled => TRUE); END; /To remove a standby instance from statspack configuration use $ORACLE_HOME/rdbms/admin/sbdelins.sql.
To drop standby statspack and statspack from the database use catcon.pl.
#remove standby statspack perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_sbdrop $ORACLE_HOME/rdbms/admin/sbdrop.sql # remove statspack perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys -s -e -c 'CDB$ROOT' -n 1 -b st_spdrop $ORACLE_HOME/rdbms/admin/spdrop.sqlUseful metalink notes
STATSPACK:Idle Wait Events Missing in STATS$IDLE_EVENT [ID 2673657.1]
STATSPACK REPORTS SHOW "IDLE" DATA GUARD WAIT EVENTS IN TOP 5 IN 12.2 [ID 2305287.1]
12.2 or later STATSPACK: Idle Wait Event Such as 'Data Guard: Timer' is Erroneously Included in Top 5 Timed Events [ID 2437142.1]
New Idle Events are Erroneously Listed in STATSPACK Report [ID 1998538.1]
STATSPACK:Idle Wait Events Missing in STATS$IDLE_EVENT [ID 2673657.1]
Related Posts
Statspack setup in Brief
AWR Reports on Standby when Active Data Guard is Used
