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)