1 / 45

DBA Daily Checks and Best Practice

DBA Daily Checks and Best Practice. Gordon D. Brown Rutherford Appleton Laboratory. 3D Workshop PIC, Barcelona 20 th -21 st April 2009. Overview. Daily checks Best practice Future improvements Your checks?. Which country is this?. Switzerland. Which country is this?. Finland.

pello
Download Presentation

DBA Daily Checks and Best Practice

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBA Daily Checks and Best Practice Gordon D. BrownRutherford Appleton Laboratory 3D WorkshopPIC, Barcelona20th-21st April 2009

  2. Overview • Daily checks • Best practice • Future improvements • Your checks?

  3. Which country is this? Switzerland

  4. Which country is this? Finland

  5. Which country is this? Scotland

  6. Which country is this? Portugal

  7. Which country is this? China

  8. Which country is this? Iraq

  9. Which country is this? Somalia

  10. Daily Checks • Grid Control • Green Pie! • Alerts, warnings • Performance • Other • Backups • Alert Logs • Interventions • Service exceptions • Your email

  11. Grid Control

  12. Grid Control: Home page

  13. Grid Control: Targets down

  14. Grid Control: Alerts

  15. Grid Control: Check emails

  16. Grid Control: Jobs

  17. Grid Control: Notification rules

  18. Grid Control: Notifications

  19. Which country is this? Egypt

  20. Alert Logs

  21. Alert Logs • Goals • Highlight any errors • Reduce time for DBAs to check • Each instance • Email logs to DBA team • Checks for lines to ignore • “Reports” status to central database • Central database • Has details of all instances • List of all lines to ignore • produces report (run through Grid Control)

  22. Alert Logs: Emails

  23. Alert Logs: Report

  24. Alert Logs: Instance data

  25. Alert Logs: Status

  26. Alert Logs: Lines to ignore

  27. Alert Logs: Instance crontab # Alert and Listener Logs 0 8 * * * /home/oracle/scripts/alert.shrcatrcat 10 rcatedb_alert <pword> >/dev/null 2>&1

  28. Alert Logs: Instance Script 1 if [ `grep -v –f alert_log_match.lst $ALERT_LOG_DIR/alert_${sdbi}.log | wc -l` -eq 0 ]; then SUBJECT="CLEAR“ else SUBJECT="ERRORS“ fi # Report the alert sqlplus -s /nolog <<EOF WHENEVER SQLERROR EXIT SQL.SQLCODE; WHENEVER OSERROR EXIT FAILURE; CONNECT $ldbu/$ldbp@$ldbs INSERT INTO alert_log_status (alert_log_status_id, database_name, instance_name, alert_log_status_date, status) VALUES (seq_alert_log_status.NEXTVAL, '${sdbs}', '${sdbi}', SYSDATE, '$SUBJECT');

  29. Alert Logs: Instance Script 2 # Email the log echo -e "ERRORS:\n" "`grep -v -f "/home/oracle/scripts/alert_log_match.lst" $ALERT_LOG_DIR/alert_${sdbi}.log`" "\n\nLOG:\n" "`cat $ALERT_LOG_DIR/alert_${sdbi}.log`" | mail -s "ORACLE - ${sdbi} ${SUBJECT}: alert log for `date`" db_admins

  30. Alert Logs: Job

  31. Alert Logs: Job

  32. Alert Logs: Report script #################################################################### # # This script Produces an Alert Log Report to be sent to the DBAs # # Amendments : # GDB - 19-JAN-2009 Initial construction # #################################################################### # set the rcat environment . /home/oracle/ora10rcat.sh sqlplusedb_alert/<pword> <<EOF @/home/oracle/scripts/alert_log_report.sql EOF cat /home/oracle/scripts/alert_log_report.txt | mailx -s "Alert Log Report for `date`" db_admins

  33. Alert Logs: Report SQL SET ECHO OFF; SET PAGESIZE 100; SET FEEDBACK OFF; SET LINESIZE 120; SET SQLPROMPT "" COLUMN database_name FORMAT A20; COLUMN instance_name FORMAT A20; COLUMN status FORMAT A20; SPOOL /home/oracle/scripts/alert_log_report.txt; SELECT instance.database_name, instance.instance_name, alert_log_status.alert_log_status_date, alert_log_status.status FROM instance, alert_log_status WHERE instance.database_name = alert_log_status.database_name(+) AND instance.instance_name = alert_log_status.instance_name(+) AND alert_log_status.alert_log_status_date(+) >= TO_DATE(TO_CHAR(SYSDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') ORDER BY database_name, instance_name;

  34. Alert Logs: Emails

  35. Which country is this? Turkey

  36. Good Practice

  37. Backups: Report

  38. Wiki: Home Page

  39. Wiki: User Notification

  40. Instances: Crontab User: Oracle #Backups for the database 00 23 * * 1 /home/oracle/scripts/backup/rmanfullback.sh ngs11 rman <pword> rcat >/dev/null 2>&1 00 23 * * 2-7 /home/oracle/scripts/backup/rmanincback.sh ngs11 rman <pword> rcat >/dev/null 2>&1 #Alert Logs sent out 0 8 * * * /home/oracle/scripts/alert.sh ngs11 ngs111 11 rcatedb_alert <pword> >/dev/null 2>&1 #Housekeeping 0 9 * * * /home/oracle/scripts/ora11housekeep.sh ngs11 ngs111 2>&1 User: Root #Backup Oracle OCR and Voting Disk 0 3 * * * /root/oscripts/backupOCR 10 3 * * * /root/oscripts/backupVD

  41. Housekeeping • Log rotation • Removing old logs/trace files etc • See my talk at WLCG in 2007: • http://indico.cern.ch/sessionDisplay.py?sessionId=7&slotId=0&confId=20080#2007-11-30

  42. Future • Service exceptions • Disaster Recovery • Reporting (long term) • SLS

  43. Which country is this? Slovakia

  44. Your daily checks?

  45. Questions and (hopefully) Answers databaseservices@stfc.ac.uk

More Related