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