Dear Readers,
Recently one of BU person in my project requested us to send scheduled report for RMAN Backup completion for one of critical databases. As this requirement was from Business, I thought of having it simple & less techie. After exploring few options, I thought of using OEM reporting functionalities & found its really amazing.
This post will guide you on how to create custom information publisher report.
1. Login to OEM. This is how my home page looks:
2. Click on Enterprise ==> Reports ==> Information Publisher Reports
3. This is landing page for Information Publisher Reports. Click on “Create”.
4. Report creation basically has 4 steps: General, Elements, Schedule, Access.
5. Fill the details under “General” tab as per your requirement. Check the box “Run report using target privileges of report owner (SYSMAN)”
6. Click on “Elements” tab, click on “Add”:
7. As I need to run a query against repository database, I will select “Table from SQL” & click on “continue”.
8. Click on “Set parameters” icon & configure your custom query as per your need. In my case, I am using the below & then click “Continue”
SELECT start_time as "Start", end_time as "End", output_bytes / 1024 / 1024 / 1024 as "Size in GB", elapsed_Seconds / 60 as "Time taken in Minutes", input_type as "Type", output_device_type as "Device", status as "Status" FROM sysman.DB_BACKUP_HISTORY_E WHERE target_GUID = '<GUID of your database>' -- you may need to query SYSMAN.EM_ALL_TARGETS to get these details. and start_time>sysdate-1 ORDER BY start_time;
9. Click on “Preview” to see if you are getting desired results. If yes, click on “Return to Create Report Definition”.
10. Click on “Schedule” tab & setup schedule as per your need. Once done click “OK”.
11. Your report is now ready.
12. Check for simple yet powerful report in your inbox 🙂
Hope u will find this post very useful.
Cheers
Regards,
Adityanath
Categories: 12c, 19c, Administration, Advanced features, automation, backup & recovery, Monitoring, OEM, Scripts
There is no elapsed_Seconds column in this table DB_BACKUP_HISTORY_E.
Instead its ‘TIME_TAKEN_DISPLAY’
Hello,
Its there in my database. My repository database is on 12.2:
Regards,
Adi
thank you for sharing with us, I think this website really stands out : D.
You have made some really good points there. I checked on the net for more info about the issue and found most people will go along with your views on this web site.
I always was interested in this subject and still am, thanks for putting up.
Hi,
I’m getting the below error. Is it checking as sysman user?
I don’t have sysman in my database.
Error rendering element. Exception: ORA-00942: table or view does not exist
Hello,
Can you please provide below output from OMS repository database:
desc sysman.DB_BACKUP_HISTORY_E
select object_name, OWNER,object_type,status
from dba_objects
where object_NAME = ‘DB_BACKUP_HISTORY_E’;
Also provide version of repository database.
Regards,
Adi
Hi Adi,
Please find the details below.
OBJECT_NAME OWNER OBJECT_TYPE STATUS
——————– ————— ——————– ——-
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE PARTITION VALID
DB_BACKUP_HISTORY_E SYSMAN TABLE VALID
36 rows selected.
SQL> desc sysman.DB_BACKUP_HISTORY_E
Name Null? Type
—————————————– ——– —————————-
TARGET_GUID NOT NULL RAW(16)
COLLECTION_TIMESTAMP DATE
SESSION_KEY NOT NULL NUMBER
SESSION_RECID NOT NULL NUMBER
SESSION_STAMP NOT NULL NUMBER
NAME VARCHAR2(33)
STATUS VARCHAR2(23)
INPUT_TYPE VARCHAR2(13)
OUTPUT_DEVICE_TYPE VARCHAR2(17)
START_TIME DATE
END_TIME DATE
TIME_TAKEN_DISPLAY VARCHAR2(72)
INPUT_BYTES NUMBER
OUTPUT_BYTES NUMBER
MEDIA VARCHAR2(65)
KEEP_UNTIL DATE
KEEP VARCHAR2(3)
KEEP_OPTIONS VARCHAR2(11)
COMPRESSION_RATIO NUMBER
INCREMENTAL_LEVEL NUMBER
ELAPSED_SECONDS NUMBER
TAG NOT NULL VARCHAR2(32)
CONTAINER VARCHAR2(600)
COMPRESSED VARCHAR2(3)
ENCRYPTED VARCHAR2(3)
INPUT_BYTES_DISPLAY VARCHAR2(4000)
OUTPUT_BYTES_DISPLAY VARCHAR2(4000)
OUTPUT_BYTES_PER_SEC NUMBER
OUTPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000)
CON_ID NUMBER
SQL> select count(*) from sysman.DB_BACKUP_HISTORY_E;
COUNT(*)
———-
0
Repository Database version – 12.2.0.1.0 – 64bit