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 (tick) 
8.2.x (tick) 
8.1.x (tick) 
8.0.x (tick) 
Applicable RDBMS

 

RDBMS
Yes/No
Oracle Server (tick) 
Microsoft SQL Server (tick) 
CSS2 (tick) 
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

"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 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

"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.

Notes/comments
 

  

Related Pages