Database weekly reports Zbigniew Baranowski Carlos Fernando Gamboa
Database weekly reports Zbigniew Baranowski Carlos Fernando Gamboa
Database weekly reports • Based on reports used for experiments database reporting • HTML format • Content of DB report – – – – Top 5 active users/sites Sessions per user and per service Resource utilization by users Short connections Broken jobs & Invalid objects Queries not using bind variables Connection errors & passwords expiring & not used schema Fragmented table & Disk space usage per schema 2
Top 5 Sites & Users 3
Database weekly reports • Database activity overview 4
Database weekly reports • Queries not using bind variables 5
Database weekly reports 6
Architecture • USERMON schema - containing audit data local repository on each monitored database • PHP script – report generator – Located on your web server – Uses USERMON schema to generate report based on session activity data and other db diagnostic views T 1 Get data Get reports for DB BNL Get data Authorized user 3 d Web Server RAL 7
Deployment • PL/SQL installation script – To be run as sys – Creates USERMON schema – Moves AUD$ table to USERMON tablespace (takes care about reasonable size of it) – Creates and schedule daily collector job – Enables CREATE SESSION auditing • Registration of DB (USERMON credentials) – Via web 8
Reports access • Who : Experiments DBAs & T 1 administrators • Where : CERN web server for 3 D https: //oms 3 d. cern. ch: 1159/admin/t 1 reports. php • How: – Via web repository interface – Authentication (reuse of accounts for T 1) – Limitations (each site can access to own reports only) 9
Reports repository 10
Questions?
- Slides: 11