Oracle DBA - PowerPoint PPT Presentation

About This Presentation
Title:

Oracle DBA

Description:

Raw partitions & RAID 0 1 (stripping and mirroring) Logical Volume manager (SAN? ... Users can also see/play with data that is on remote databases from the ... – PowerPoint PPT presentation

Number of Views:64
Avg rating:3.0/5.0
Slides: 16
Provided by: hepprojda
Category:

less

Transcript and Presenter's Notes

Title: Oracle DBA


1
Oracle DBA
  • Nilo Segura
  • IT/DB/DBR

2
Agenda
  • Setting up a database server
  • User accounts
  • Security
  • DB Tuning
  • Backup and Recovery (C.Delamare)
  • Useful features

3
Setting up a Database server
  • DB Server setup is the responsibility of the DBA
    team
  • Crucial to have a good platform (cheap does not
    pay!!!)
  • Hardware selection (cluster vs. single host), HA
    configuration ?
  • Raw partitions RAID 01 (stripping and
    mirroring) Logical Volume manager (SAN?)
  • GbEthernet or higher, how many interfaces?
  • A properly tuned database is ONLY CPU bound

4
Setting up a Database server
  • You have to plan for the database and for the
    instance
  • Database
  • Sizing the tablespaces (TS) is VERY important
  • General versus Dedicated
  • Data growing rate (TS can be created/dropped
    online)
  • Locally managed vs. Dictionary managed
  • Size and rate/duration of the transactions
  • redo logs and rollback segments
  • Size of the sorting operations
  • size of the temporary tablespaces

5
Setting up a Database Server
SQLgt CREATE DATABASE DEVDB CHARACTER SET
"WE8ISO8859P9 DATAFILE '/ORADB/dbs1/devdb/system
01.dbf' size 250M LOGFILE group 1
( '/ORADB/dbs1/devdb/log1.dbf'
) size 100M, group 2 (
'/ORADB/dbs2/devdb/log2.dbf' )
size 100M _at_ORACLE_HOME/rdbms/admin/catalog _at_OR
ACLE_HOME/rdbms/admin/catproc _at_ORACLE_HOME/rdbms/
admin/catrep
  • Instance
  • Dedicated vs. Multi Threaded Server connections
  • Number of processes
  • Store procedures (java, pin pl/sql code in
    memory)
  • Sort area size
  • Many more
  • Backup policy (see Catherine slides)
  • Has to be agreed with the users
  • Several options (exp/imp, hot/cold backups)
  • But surprise surprise some critical LEP Oracle
    services had no backup at all

6
User accounts
  • Any user can request an account in the central
    development and production DB handled by IT
  • But for physics use, there will be dedicated
    servers
  • Accounts have space quota on one or more
    tablespaces
  • In this account, the user will create new
    database applications (tables, indexes, types )
  • Users can also see/play with data that is on
    remote databases from the local account
  • Performance issues must be taken into account due
    to network access

SQLgt create user cms_anode_board identified by
yupyup default tablespace data01 temporary
tablespace temp01 quota 100M on data01
quota unlimited on indx01 SQLgt grant
designer to cms_anode_board
7
Security(1/2)
  • The database catalog (in tablespace SYSTEM) can
    not be modified directly
  • For everything that you can do in the database
    there is an associated database privilege
  • A DBA can group system privileges into roles and
    assign them to user accounts
  • A user can also create roles to assign access
    privileges (on his/her objects) to another users
  • By default, you can not see/modify other users
    data

8
Security(2/2)
  • Security can be enhanced in several ways
  • Data itself can be stored encrypted
  • Password can expire (like in the normal OS)
  • Secure the communication with the db using DES,
    Triple-DES, RSA RC4...
  • User authentication with Kerberos, SSL,
    Cybersafe, Biometrix
  • Although the default is everything in clear
  • We can switch to security mode immediately

9
DB Tuning client(1/2)
  • The ability to make your queries faster
  • 90 of the times all the tuning is done at the
    user code level
  • Most common tuning cases are due to
  • Missing index or wrong Query Plan chosen by the
    Optimizer
  • Poor application design (the fatal flaw of doing
    FILE TABLE!!!)

10
DB Tuning client(2/2)
  • You can always see the Query Plan before you run
    the query
  • If the optimizer chooses the wrong options you
    can force a different choice via Hints
  • Detailed statistics about execution of your
    queries for more advanced tuning
  • Can easily identify hotspots
  • Remedies
  • Knowing your data query patterns is essential
  • Review/test your application carefully before
    declaring it production

Reducing query times from hours to minutes (or
even seconds) is not unusual
11
DB Tuning - server
  • The database itself has many parameters that can
    be tweaked to improve the performance
  • The role of the DBA is to understand the impact
    of those parameters in the general performance of
    the db.
  • In the past, you could not modified them without
    stopping the database
  • Now many can be modified online
  • VERY IMPORTANT A vast library of tuning scripts
    (and 3rd party tools) is available

12
Useful features (1/4)
  • Partitioning (see Montse slides)
  • Data is separated physically according to a key
    although logically, we see it as single unit
  • Increased performance, improved data management
  • Bitmap index (see Kurt/Eric slides)
  • Very useful for low cardinality data
  • Even for high cardinality if we apply a function
    that reduces it
  • Function based index (see Eric slides)
  • Index is not created on the data but on the
    result of applying a function (native or external)

13
Useful features (2/4)
  • Server side processing (pl/sql, java)
  • Closer to data, code reusability
  • Database can do many more things for you than
    just storing/retrieving data
  • Resumable statements
  • Transaction freezes while the error condition is
    fixed
  • User code does not need to be modified to profit
  • Limited set of errors are trapped in 9i (initial
    version)
  • Java VM inside the database kernel
  • Java compiler to speed up the performance
  • JSP,Servlets,EJB,Java stored procedures

14
Useful features (3/4)
  • External procedures
  • Extend Oracle function libraries with your own
  • Using C for the moment
  • External table api allows any external source (ex
    a socket) to look like a normal table
  • XML
  • XSQL, XSL, XML Parsers for C/C/Java/PLSQL
  • XML native data type

15
Useful features (4/4)
  • Parallel Query (see Montse slides)
  • Query/analyze large amounts of data
  • Inter parallelism (SMP machines tested)
  • Intra parallelism (for clusters - not tested)
  • Transportable tablespaces (see Marcin/Catherine
    slides)
  • Allows you to move data from one database to
    another very easily and fast (almost plugplay)
Write a Comment
User Comments (0)
About PowerShow.com