Oracle database backup report from OEM repository

To generate a report of all full and incremental backups from an Oracle Enterprise Manager (OEM) database, you can use the following SQL query. This query retrieves information from the OEM database repository tables.




SELECT
    j.job_name AS "Backup Job Name",
    b.start_time AS "Backup Start Time",
    b.completion_time AS "Backup Completion Time",
    b.backup_level AS "Backup Level",
    b.status AS "Backup Status",
    b.backup_type AS "Backup Type",
    b.incremental_level AS "Incremental Level",
    b.backup_size AS "Backup Size (bytes)",
    b.output_device_type AS "Output Device Type",
    b.output_device_name AS "Output Device Name",
    b.pieces AS "Number of Pieces"
FROM
    mgmt$job j
JOIN
    mgmt$backup_set b ON j.job_id = b.job_id
WHERE
    j.job_type = 'Backup'
ORDER BY
    b.start_time DESC;

SELECT
    j.job_name AS "Backup Job Name",
    b.start_time AS "Backup Start Time",
    b.completion_time AS "Backup Completion Time",
    b.backup_level AS "Backup Level",
    b.status AS "Backup Status",
    b.backup_type AS "Backup Type",
    b.incremental_level AS "Incremental Level",
    b.bytes AS "Backup Size (bytes)",
    b.device_type AS "Output Device Type",
    b.device_name AS "Output Device Name",
    b.number_of_pieces AS "Number of Pieces"
FROM
    sysman.mgmt_jobs j
JOIN
    sysman.mgmt_backup_set b ON j.job_id = b.job_id
WHERE
    j.job_type = 'Backup'
ORDER BY
    b.start_time DESC;

In Oracle OEM 13.5, the table structures might be different. Adjustments have been made to use the appropriate table names (sysman.mgmt_jobs and sysman.mgmt_backup_set) and column names (bytes instead of backup_size, device_type instead of output_device_type, and device_name instead of output_device_name). Always ensure to verify the table and column names in your specific version of Oracle OEM. Additionally, ensure that you have the necessary privileges to access these tables.