Dba daily checks and best practice
This presentation is the property of its rightful owner.
Sponsored Links
1 / 45

DBA Daily Checks and Best Practice PowerPoint PPT Presentation


  • 48 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Dba daily checks and best practice

DBA Daily Checks and Best Practice

Gordon D. BrownRutherford Appleton Laboratory

3D WorkshopPIC, Barcelona20th-21st April 2009


Overview

Overview

  • Daily checks

  • Best practice

  • Future improvements

  • Your checks?


Dba daily checks and best practice

Which country is this?

Switzerland


Dba daily checks and best practice

Which country is this?

Finland


Dba daily checks and best practice

Which country is this?

Scotland


Dba daily checks and best practice

Which country is this?

Portugal


Dba daily checks and best practice

Which country is this?

China


Dba daily checks and best practice

Which country is this?

Iraq


Dba daily checks and best practice

Which country is this?

Somalia


Daily checks

Daily Checks

  • Grid Control

    • Green Pie!

    • Alerts, warnings

    • Performance

  • Other

    • Backups

    • Alert Logs

    • Interventions

    • Service exceptions

    • Your email


Grid control

Grid Control


Grid control home page

Grid Control: Home page


Grid control targets down

Grid Control: Targets down


Grid control alerts

Grid Control: Alerts


Grid control check emails

Grid Control: Check emails


Grid control jobs

Grid Control: Jobs


Grid control notification rules

Grid Control: Notification rules


Grid control notifications

Grid Control: Notifications


Dba daily checks and best practice

Which country is this?

Egypt


Alert logs

Alert Logs


Alert logs1

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)


Alert logs emails

Alert Logs: Emails


Alert logs report

Alert Logs: Report


Alert logs instance data

Alert Logs: Instance data


Alert logs status

Alert Logs: Status


Alert logs lines to ignore

Alert Logs: Lines to ignore


Dba daily checks and best practice

Alert Logs: Instance crontab

# Alert and Listener Logs

0 8 * * * /home/oracle/scripts/alert.shrcatrcat 10 rcatedb_alert <pword> >/dev/null 2>&1


Dba daily checks and best practice

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');


Dba daily checks and best practice

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


Alert logs job

Alert Logs: Job


Alert logs job1

Alert Logs: Job


Alert logs report script

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


Dba daily checks and best practice

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;


Alert logs emails1

Alert Logs: Emails


Dba daily checks and best practice

Which country is this?

Turkey


Good practice

Good Practice


Backups report

Backups: Report


Wiki home page

Wiki: Home Page


Wiki user notification

Wiki: User Notification


Dba daily checks and best practice

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


Dba daily checks and best practice

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


Dba daily checks and best practice

Future

  • Service exceptions

  • Disaster Recovery

  • Reporting (long term)

  • SLS


Dba daily checks and best practice

Which country is this?

Slovakia


Your daily checks

Your daily checks?


Questions and hopefully answers

Questions and (hopefully) Answers

[email protected]


  • Login