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 0+1 (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. SQL> CREATE DATABASE DEVDB CHARACTER SET "WE8ISO8859P9“ DATAFILE '/ORADB/dbs1/devdb/system01.dbf' size 250M LOGFILE group 1 ( '/ORADB/dbs1/devdb/log1.dbf' ) size 100M, group 2 ( '/ORADB/dbs2/devdb/log2.dbf' ) size 100M ; @$ORACLE_HOME/rdbms/admin/catalog @$ORACLE_HOME/rdbms/admin/catproc @$ORACLE_HOME/rdbms/admin/catrep Setting up a Database Server • 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 SQL> create user cms_anode_board identified by yupyup default tablespace data01 temporary tablespace temp01 quota 100M on data01 quota unlimited on indx01; SQL> 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 user’s 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 plug&play)