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.