1 / 18

ArrayExpress Oracle DBA

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.

faunus
Download Presentation

ArrayExpress Oracle DBA

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. ArrayExpress Oracle DBA Ahmet Oezcimen

  2. 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

  3. 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.

  4. 2. System Overview Oracle Database Server (Oracle RDBMS 8.1.7.0.0) Application Server (Apache Tomcat v4.0.1) Backup Server (Legato Networker) RMAN JDBC SQL*Net Web Server AEDEV =ArrayExpress Development Backup Devices AETEST =ArrayExpress Test AEPUB =ArrayExpress Public AECUR =ArrayExpress Curation User AEDWD =ArrayExpress Data Warehouse Development AEDWP =ArrayExpress Data Warehouse Public AECLONE =Copy of the ArrayExpress Public database

  5. 3. Oracle DB System • Oracle Software • SQL*Plus • Svrmgrl • Exp/Imp • SQL*Loader • RMAN • Etc.. • DB Structure

  6. 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)

  7. 3. Oracle DB System (continued) • Oracle DB Objects • Tablespaces • Rollback Segments • Tables • Indexes • Constraints • Sequences • Synonyms, etc.. • Oracle DB Files • Data Files • Redo Log Files • Control Files • Init<SID>.ora File • Archive Log Files • Log and Trace Files • Audit Files, etc..

  8. 3. Oracle DB System (continued) Control Files Data Files Redo Log Files System Global Area (SGA) SMON Database Buffer Cache Dedicated Server Process L R U PMON Redo Log Buffer RECO Dirty Init<SID> .ora Shared Pool Area LCK0 Dictionary Cache Library Cache User Process Archived Redo Log Files DBWR CKPT LGWR ARC0

  9. 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.. 4. Database Monitoring

  10. 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

  11. 6. Performance and Tuning 60% of database performance problems are caused by applications Database Application

  12. 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)

  13. 6. Performance and Tuning (continued) • Database Tuning • Other Tuning Issues • Table/Index Partitioning • Analyse Tables and Indexes • Reorganise tables • Rebuild indexes

  14. 7. Backup and Recovery

  15. 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

  16. 7. Backup and Recovery (continued) • Recovery • Type of Recovery • Instance Recovery • Media Recovery • Recovery Methods • Recovery with Physical Backups • Recovery with Logical Backups Import

  17. 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

  18. 9. QUESTIONS?

More Related