How do we report on databases that haven’t been backed up? We create a report from Enterprise manager and see what has failed and what backups are potentially missing<\/p>\n
Run this in Enterprise Manager <\/p>\n
\/* mark young 23-August 2011 *\/\r\nSELECT a.HOST_NAME,\r\n a.DISPLAY_NAME,\r\n CASE\r\n WHEN b.end_time < SYSDATE - 2 THEN '3. BACKUP OLDER THAN 2 DAYS'\r\n WHEN b.status='FAILED' THEN '1.FAILED' \r\n WHEN b.status='COMPLETED WITH WARNINGS' THEN '4. COMPLETED WITH WARNINGS' \r\n WHEN b.status='COMPLETED' THEN '5. COMPLETED' \r\n ELSE NVL (b.status, '2. NO RMAN BACKUP')\r\n END\r\n status,\r\n c.database_ROLE,\r\n a.target_type,\r\n b.start_time,\r\n b.end_time,\r\n b.input_type,\r\n b.output_device_type,\r\n b.output_bytes_display\r\n FROM ( SELECT T.HOST_NAME,\r\n T.DISPLAY_NAME,\r\n T.target_guid,\r\n T.target_type\r\n FROM mgmt$target t\r\n WHERE target_type = ('rac_database')\r\n GROUP BY T.HOST_NAME,\r\n t.target_type,\r\n t.target_guid,\r\n T.DISPLAY_NAME) a, mgmt$ha_backup b, mgmt$ha_info c\r\n WHERE a.target_guid = b.target_guid(+)\r\n AND a.target_guid = c.target_guid\r\n AND c.database_role = 'PRIMARY'\r\nUNION\r\nSELECT a.HOST_NAME,\r\n a.DISPLAY_NAME,\r\n CASE\r\n WHEN b.end_time < SYSDATE - 2 THEN '3. BACKUP OLDER THAN 2 DAYS'\r\n WHEN b.status='FAILED' THEN '1.FAILED' \r\n WHEN b.status='COMPLETED WITH WARNINGS' THEN '4. COMPLETED WITH WARNINGS' \r\n WHEN b.status='COMPLETED' THEN '5. COMPLETED' \r\n ELSE NVL (b.status, '2. NO RMAN BACKUP')\r\n END\r\n status,\r\n c.database_ROLE,\r\n a.target_type,\r\n b.start_time,\r\n b.end_time,\r\n b.input_type,\r\n b.output_device_type,\r\n b.output_bytes_display\r\n FROM ( SELECT T.HOST_NAME,\r\n T.DISPLAY_NAME,\r\n T.target_guid,\r\n T.target_type\r\n FROM mgmt$target t\r\n WHERE target_type = ('oracle_database')\r\n AND t.type_qualifier3 <> 'RACINST'\r\n GROUP BY T.HOST_NAME,\r\n t.target_type,\r\n t.target_guid,\r\n T.DISPLAY_NAME) a,\r\n mgmt$ha_backup b,\r\n mgmt$ha_info c\r\n WHERE a.target_guid = b.target_guid(+)\r\n AND a.target_guid = c.target_guid\r\n AND c.database_role = 'PRIMARY'\r\nORDER BY 3<\/pre>\n","protected":false},"excerpt":{"rendered":"How do we report on databases that haven’t been backed up? We create a report from Enterprise manager and see what has failed and what backups are potentially missing Run this in Enterprise Manager \/* mark young 23-August 2011 *\/ SELECT a.HOST_NAME, a.DISPLAY_NAME, CASE WHEN b.end_time < SYSDATE – 2 THEN ‘3. BACKUP OLDER THAN … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[50],"class_list":["post-175","post","type-post","status-publish","format-standard","hentry","category-enterprise-manager","tag-backup"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=175"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/175\/revisions"}],"predecessor-version":[{"id":176,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/175\/revisions\/176"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}