Purge statspack snaps of source db on test db having different DBID?
As most DBAs are aware, the clean deletion of the old statspack snaps is very difficult up to rdbms version 9i. Thanks to the statspack.purge procedure introduced by oracle from 10gR1 version, now it's possible to purge un-referenced data too. This blog post explains about how to use statspack.purge procedure, but for the test/clone databases which uses different DataBase IDentifier(DBID) compared to the source database. Please remember the steps explained on this blog are
not required when the source and test databases have the
same DBID. Normally DBID gets changed on cloned databases during the following scenarios. 1. The most commonly used RMAN 'duplicate database' feature to create the test database. 2. The database utility 'nid' used to change the test database name and dbid. 3. The test database controlfile gets created using syntax based on the scripts available on text backup controlfile. Consider you have production/source database is configured to generate statspack snaps once in 20 minutes and the retention were 90 days. When this source database gets cloned using above methods to create test database, it inherits the same behavior. Now the test database contains statspack snaps belongs to source database as well as for the current database too. Even when you modify the existing purge script to retain less snaps, it would valid only for the snaps belong to the current DBID. The snaps belongs to other than current DBID would never get purged by this script, even though they are no longer valid for this test database. 1. Gather the DBID details from stats$snapshot table on the test database. For example, SQL> select distinct dbid from stats$snapshot; DBID ---------- 1215068670 ==> This is the source database DBID 393689388 ==> This is the test database DBID 2. Gather the snaps range handled by the source database using the following queries. For eg: SQL> select min(snap_id) from stats$snapshot where dbid=1215068670; MIN(SNAP_ID) ------------ 90920 SQL> select max(snap_id) from stats$snapshot where dbid=1215068670; MAX(SNAP_ID) ------------ 93775 3. Gather the row count on various tables to verify the successful purge activity completion. For eg: SQL> select count(1) from stats$snapshot where dbid=1215068670; COUNT(1) ---------- 2211 SQL> select count(1) from stats$sqltext where last_snap_id < 93776; COUNT(1) ---------- 380056 SQL> select count(1) from STATS$STATSPACK_PARAMETER where dbid=1215068670; COUNT(1) ---------- 1 SQL> select count(1) from STATS$UNDOSTAT where snap_id < 93776; COUNT(1) ---------- 4422 4. Copy the $ORACLE_HOME/rdbms/admin/sppurge.sql to your home directory and modify it accordingly. i) Remove the dbid column from this select statement on the script. select d.dbid dbid ==> Remove this column being selected. , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; ii) Substitute the source database DBID on this location on the script. begin :dbid := &dbid; ==> Change the '&dbid' value as 1215068670 :inst_num := &inst_num; :inst_name := '&inst_name'; :db_name := '&db_name'; end; / iii) Change the variable "i_extended_purge" value as 'true' on the script. :snapshots_purged := statspack.purge( i_begin_snap => :lo_snap , i_end_snap => :hi_snap , i_snap_range => true , i_extended_purge => false ==> Change the value as true , i_dbid => :dbid , i_instance_number => :inst_num); end; 5. Execute this custom purge script on the test database and provide the snaps range when requested. For eg: Specify the Lo Snap Id and Hi Snap Id range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for losnapid: 90920 Using 90920 for lower bound. Enter value for hisnapid: 93775 Using 93775 for upper bound. Deleting snapshots 90920 - 93775. 6. Now logged into the test database and execute the queries to verify the deletion happened. SQL> select count(1) from stats$snapshot where dbid=1215068670; COUNT(1) ---------- 0 SQL> select count(1) from stats$sqltext where last_snap_id < 93776; COUNT(1) ---------- 7840 SQL> select count(1) from STATS$STATSPACK_PARAMETER where dbid=1215068670; COUNT(1) ---------- 1 SQL> select count(1) from STATS$UNDOSTAT where snap_id < 93776; COUNT(1) ---------- 0 As you noticed, This is very simple action plan, which may require some more modification on the custom purge script when it was used on RAC database.
