Transcript Oracle DBA - hep-proj-database Site

CERN/IT/DB
Oracle DBA
Nilo Segura
IT/DB/DBR
CERN/IT/DB
Agenda
•
•
•
•
•
•
Setting up a database server
User accounts
Security
DB Tuning
Backup and Recovery (C.Delamare)
Useful features
CERN/IT/DB
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 0+1 (stripping and mirroring) +
Logical Volume manager (SAN?)
– GbEthernet or higher, how many interfaces?
• A properly tuned database is ONLY CPU bound
CERN/IT/DB
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
CERN/IT/DB
Setting up a Database Server
SQL> CREATE DATABASE DEVDB
CHARACTER SET "WE8ISO8859P9“
DATAFILE
• Instance
'/ORADB/dbs1/devdb/system01.dbf'
size 250M
– Dedicated vs. Multi Threaded Server connections
LOGFILE
– Number
of processes
group
1(
– '/ORADB/dbs1/devdb/log1.dbf'
Store procedures (java, pin pl/sql code in memory)
–) Sort
size
size area
100M,
– Many
group
2 ( more…
• Backup
policy (see Catherine slides)
'/ORADB/dbs2/devdb/log2.dbf'
–) Has
be agreed
with the users
size to
100M
;
– Several options (exp/imp, hot/cold backups)
@$ORACLE_HOME/rdbms/admin/catalog
– But surprise surprise some critical LEP Oracle services
@$ORACLE_HOME/rdbms/admin/catproc
had no backup at all…
@$ORACLE_HOME/rdbms/admin/catrep
CERN/IT/DB
User accounts
• Any user can request an account in the central
development and production DB handled by IT
SQL>
create
user cms_anode_board
yupyup
– But
for physics
use, there will beidentified
dedicatedbyservers
defaulthave
tablespace
• Accounts
spacedata01
quota on one or more
temporary tablespace temp01
tablespaces
quota 100M on data01
• In thisquota
account,
theonuser
will create new database
unlimited
indx01;
applications (tables, indexes, types …)
SQL> grant designer to cms_anode_board;
• 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
CERN/IT/DB
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 user’s
data
CERN/IT/DB
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
CERN/IT/DB
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!!!)
CERN/IT/DB
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
Reducing
query
times
from
hours
to
minutes
(or
even
• Detailed statistics about execution of your queries
seconds)
is notadvanced
unusual tuning
for more
– Can easily identify hotspots
• Remedies
– Knowing your data & query patterns is essential
– Review/test your application carefully before declaring
it production
CERN/IT/DB
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
CERN/IT/DB
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)
CERN/IT/DB
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
CERN/IT/DB
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
CERN/IT/DB
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 plug&play)