SQL Queries - CAST Central Base - Queries on snapshots - How to get the history of creation and deletion of snapshots

Purpose of Query

To check the history of creation and deletion of snapshots ,the following query can be run on the central repository.

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT * 
FROM   dss_history dh 
WHERE  ( Lower(dh.description) LIKE 'dss_create_snapshot%' 
          OR dh.description = 'Start DSS_PURGE_SNAPSHOT' 
          OR dh.description LIKE 
             'Start ADG_DELETE_VIOLATION_STATUSES for snapshot %' ) 
ORDER  BY dh.action_date, 
          dh.history_id;

Query result example

“DSS_CREATE_SNAPSHOT 1 Computed on 201709131744”;“2017-09-13 18:15:38.264”;35

“DSS_CREATE_SNAPSHOT 2 Computed on 201709221225”;“2017-09-22 12:37:49.47”;1578
“DSS_CREATE_SNAPSHOT 3 Computed on 201709261549”;“2017-09-26 17:24:49.637”;3169
“DSS_CREATE_SNAPSHOT 4 Computed on 201709272023”;“2017-09-27 22:46:25.931”;4791
“Start DSS_PURGE_SNAPSHOT”;“2017-09-28 17:09:08.79”;6521
“Start ADG_DELETE_VIOLATION_STATUSES for snapshot 3”;“2017-09-28 17:09:08.822”;6527

Query result interpretation

A snapshot of Id n was computed when the description columns return a string value containing the following string value ‘DSS_CREATE_SNAPSHOT’ followed by the snapshot Id.

A snapshot was deleted when the description columns return the string value “Start DSS_PURGE_SNAPSHOT”, then the string value “Start ADG_DELETE_VIOLATION_STATUSES for snapshot” followed by the snapshot Id

The results above shows that snapshots 1, 2, 3 and 4 was computed, then snapshot 3 was deleted.

Query for Oracle

SELECT * 
FROM   dss_history dh 
WHERE  ( Lower(dh.description) LIKE 'dss_create_snapshot%' 
          OR dh.description = 'Start DSS_PURGE_SNAPSHOT' 
          OR dh.description LIKE 
             'Start ADG_DELETE_VIOLATION_STATUSES for snapshot %' ) 
ORDER  BY dh.action_date, 
          dh.history_id;

Query result example

“DSS_CREATE_SNAPSHOT 1 Computed on 201709131744”;“2017-09-13 18:15:38.264”;35

Query result interpretation

A snapshot of Id n was computed when the description columns return a string value containing the following string value ‘DSS_CREATE_SNAPSHOT’ followed by the snapshot Id

The results above shows that snapshots 1, 2, 3 and 4 was computed, then snapshot 3 was deleted.

Query for SQL server

SELECT * 
FROM   dss_history dh 
WHERE  ( Lower(dh.description) LIKE 'dss_create_snapshot%' 
          OR dh.description = 'Start DSS_PURGE_SNAPSHOT' 
          OR dh.description LIKE 
             'Start ADG_DELETE_VIOLATION_STATUSES for snapshot %' ) 
ORDER  BY dh.action_date, 
          dh.history_id;

Query result example

“DSS_CREATE_SNAPSHOT 1 Computed on 201709131744”;“2017-09-13 18:15:38.264”;35

Query result interpretation

A snapshot of Id n was computed when the description columns return a string value containing the following string value ‘DSS_CREATE_SNAPSHOT’ followed by the snapshot Id

The results above shows that snapshots 1, 2, 3 and 4 was computed, then snapshot 3 was deleted.

Notes/comments

Related Pages