Docstoc

Oracle DBA

Document Sample
Oracle DBA Powered By Docstoc
					ArrayExpress Oracle DBA
     Ahmet Oezcimen
                Agenda
1. Tasks
2. System Overview
3. Oracle DB System
4. Database Monitoring
5. Database Security
6. Performance and Tuning
7. Backup and Recovery
8. Literature
9. Questions
                 1. Tasks
• Installation, configuration, upgrading and patching of
  Oracle software
• Creation and maintenance databases and their objects
• Establishment and maintenance backup and recovery
  policies and procedures
• Implementation and maintenance database security
• Performance and tuning of databases
• Plan growth and changes (capacity planning)
• Give consultation to development teams
• Interface with Oracle Corporation for technical support.
                    2. System Overview
Application Server          Oracle Database Server                 Backup Server
(Apache Tomcat v4.0.1)      (Oracle RDBMS 8.1.7.0.0)               (Legato Networker)

                                           RMAN

                         JDBC




Web Server                       AEDEV     =ArrayExpress Development

                                AETEST     =ArrayExpress Test              Backup Devices

                                 AEPUB      =ArrayExpress Public

                                 AECUR      =ArrayExpress Curation
             User
                                 AEDWD      =ArrayExpress Data Warehouse Development
                                 AEDWP      =ArrayExpress Data Warehouse Public
                                AECLONE =Copy of the ArrayExpress Public database
         3. Oracle DB System
• Oracle Software
  –   SQL*Plus
  –   Svrmgrl
  –   Exp/Imp
  –   SQL*Loader
  –   RMAN
  –   Etc..
• DB Structure
    3. Oracle DB System (continued)
• DB Structure
  – Instance
     • Background Processes
         –   DBWR = database writer
         –   LGWR = log writer
         –   PMON = process monitor
         –   SMON = session monitor, etc.
     • System Global Area (SGA)
         – Database Buffer Cache
         – Shared Pool Area
             » Dictionary Cache
             » Library Cache
         – Redo Log Buffer
  – Processes
     • User Process
     • Server Process
  – Database (Database Files, Database Objects)
3. Oracle DB System (continued)

– Oracle DB Files         – Oracle DB Objects
  • Data Files              • Tablespaces
  • Redo Log Files          • Rollback Segments
  • Control Files           • Tables
  • Init<SID>.ora File      • Indexes
  • Archive Log Files       • Constraints
  • Log and Trace Files     • Sequences
  • Audit Files, etc..      • Synonyms, etc..
3. Oracle DB System (continued)
                   System Global Area (SGA)                                  SMON
                    Database Buffer Cache
 Dedicated
  Server                                            L
                                                    R            Redo        PMON
  Process                                           U             Log
                                                                 Buffer
                                                  Dirty                      RECO
                                                                                    Init<SID>
                                                                                    .ora
                          Shared Pool Area
                                                                             LCK0
                           Dictionary         Library
  User                       Cache             Cache
 Process
                                                                              Archived
                                                                              Redo Log
                   DBWR           CKPT          LGWR      ARC0                Files




             Data Files                      Redo Log Files               Control Files
      4. Database Monitoring
•   All Instances are up
•   Any new alert log entries
•   Success of DB backup
•   Free space in tablespaces
•   User activities
•   Blocking processes
•   Invalid Objects
•   Objects that break rules
•   Fragmentation
•   Security Policy Violations, etc..
           5. Database Security
• Access on DB
  – Username and Password
  – Profiles
• Access on DB Objects
  – Roles
  – Privileges
     • System Privileges
     • Object Privileges
• Auditing
  - Statement Auditing
  - Privilege Auditing
  - Object Auditing
6. Performance and Tuning

                 40%

 60% of
 database          Database
 performance
 problems are
 caused by             Application
 applications
                                     60%
6. Performance and Tuning (continued)
 • Application Tuning
   – SQL Tuning
 • Database Tuning
   – Tuning Memory Usage
      • Tuning the Shared Pool
      • Tuning the Data Buffer Cache
      • Tuning Redo Log Buffer
   – Tuning Disk Utilization
      •   I/O Distribution
      •   Detecting I/O Problems
      •   Using Locally Managed Tablespaces
      •   Tuning Rollback Segments
      •   Tuning Sorts
6. Performance and Tuning (continued)

  • Database Tuning
    – Other Tuning Issues
       •   Table/Index Partitioning
       •   Analyse Tables and Indexes
       •   Reorganise tables
       •   Rebuild indexes
7. Backup and Recovery
7. Backup and Recovery (continued)
• Backup
  – Why Backups?
    • Mistakes by
       – Hardware
       – Oracle
       – User, Programmer, DBA
  – Type of Backups
    • Physical Backup with or without RMAN
       – Online (Hot) Backup
       – Offline (Cold) Backup
    • Logical Backup Using Export
7. Backup and Recovery (continued)
• Recovery
  – Type of Recovery
    • Instance Recovery
    • Media Recovery
  – Recovery Methods
    • Recovery with Physical Backups
    • Recovery with Logical Backups Import
         8. Literature
• http://www.oracle.com/ Oracle Company
• http://otn.oracle.com/software/content.html Oracle
  Technology Network > Software
• http://technet.oracle.com/ The technical sites of
  Oracle
• http://metalink.oracle.com/ The Oracle Metalink
• http://education.oracle.com/ The Oracle Company:
  Education
• http://www.oracle.com/download The Oracle
  Company: Download Products
• http://oracle-ftp.oracle.com/ The Oracle Company:
  Patch Sets
• http://industry.ebi.ac.uk/~ahmet/ My Home page

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:15
posted:11/2/2011
language:English
pages:18