1 / 38

Database Administration

Database Administration. Judy Riniker and Greg Girard from the KSU Information Systems Office. Topics. KSU Information System Office, Database Administration Oracle Database Architecture Database Administration Short Demo of OEM. KSU Information Systems Office.

mali
Download Presentation

Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Administration Judy Riniker and Greg Girard from the KSU Information Systems Office

  2. Topics KSU Information System Office, Database Administration Oracle Database Architecture Database Administration Short Demo of OEM

  3. KSU Information Systems Office Database Administration Staff • CA IDMS Database Administrators (2) • Student data 7 Gb • Student financial and loan data 9 Gb • Total 17 Gb of Production Data • Test and development • OS/390

  4. KSU Information Systems Office Oracle Database Administrators (5) • Human Resources • Production 23 Gb • Stress Test 23 Gb • Development 2.1 Gb • Training 1.7 Gb • Delivered 1.2 Gb • Upgrade 436 Mb

  5. KSU Information Systems Office Oracle Database Administrators (5) • Facilities • Financials • Production 6.8 Gb • Test 6.9 Gb • Development 4.2 Gb • Facilities Management System • Production 2.1 Gb • Test 2.1 Gb • Development 2.1 Mb

  6. KSU Information Systems Office Oracle Database Administrators (5) • Student Related Systems • KATS Interface 220 Mb • DARS • Production 3.6 Gb • Test 3.6 Gb • Helpdesk 685 Mb • Department 4.4 Gb

  7. KSU Information Systems Office Oracle Database Administrators (5) • Telecommunications • Production Billing 13.4 Gb • Production Billing 1.7 Gb • Production Call loading 4.5 Gb • Production Directory 1.0 Gb • Test Directory 1.0 Gb

  8. KSU Information Systems Office Oracle Database Administrators (5) • Division of Continuing Education • Production 1.1 Gb • Test 847 Mb • Development 1.7 Gb • Development 807 Mb • Design 820 Mb

  9. KSU Information Systems Office Oracle Database Administrators (5) • Application/Database Utilities & Tools • OEM 590 Mb • Internet Directory 500 Mb • Version Control 285 Mb • ISO Development 1.6 Gb • Video Server 2 Gb Raw

  10. Tables, Indexes, Views • Peoplesoft 7.5 HRMS (HR) • Tables 4099 • Indexes 5947 • Views 2922 • Oracle Financials (Facilities Accounting) • Tables 2344 • Indexes 5019 • Views 2811 • FAMIS (Facilities Work Management) • Tables 333 • Indexes 202 • Views 31

  11. Server Hardware • Sun Microsystems: E4500, E1000, E450, Sparc10 • Storage: RAID 10 - Striped and mirrored • CPUs range from 50 MHz Sparc to 248 MHz UltraSparc • RAM 192 Mb to 3G. • CNS does the Unix System Administration

  12. Oracle Database Instance • Architecture • Memory Areas • Processes • Physical Files

  13. Oracle Database Overview Instance SGA Shared Pool User Process Library Cache Data Buffer Cache Redo Log Buffer Data Dict. Cache SMON DBW0 PMON CKPT LGWR ARCH Server Process PGA Parameter Data files Control Redo Log Memory Processes Physical Files Archive Database

  14. Memory Areas: SGA Area : System Global • Memory area used to store database information that is shared by database processes • Data Block Buffer Cache • holds data blocks that are read from data segments in the database such as tables and indexes • Size is set by DB_BLOCK_BUFFERS in the parameter file; but should typically be 1-2% of the total size of the database. • LRU Least Recently Used Algorithm

  15. Memory Areas: SGA: System Global Area • Shared SQL Pool • During the parse phase of a SQL query, the database server process uses this area to compile the SQL statement • Stores the data dictionary cache and the library cache • Size is set by SHARED_POOL_SIZE in the parameter file.

  16. Memory Areas: • Shared SQL Pool • Data dictionary Cache • Information about database objects is stored in the data dictionary tables such as user account data, table descriptions, privileges, datafile names, etc. • When the information is needed by the database, the data dictionary tables are read and the data returned is stored in the SGA in the dictionary cache • If too small, database will have to repeatedly query the data dictionary tables for information. • LRU Least Recently Used Algorithm

  17. Memory Areas: • Shared SQL Pool • Library Cache • Stores the most recently used SQL statements • Text of the SQL Statement • Parse Tree : Compiled version of the statement • Execution Plan: Steps take when executing the statement • If a SQL statement is reexecuted and a shared SQL area already contains the execution plan for the statement, the server process does not need to parse the statement.

  18. Memory Areas: SGA: System Global Area • Redo Log Buffer • Describe the changes that are made to the database • “Re-do” entries are written in the on-line log files so they can be used in roll forward operations during database recoveries • The entries are first stored in the SGA area in the Redo Log Buffer and then are periodically written to the physical files. • Size is set by LOG_BUFFER in the parameter file.

  19. Memory Areas: SGA: System Global Area • Large Pool • Optional area • Can be dedicated to supporting large SQL statements • Size is set by LARGE_POOL_SIZE in the parameter file. • Can also use parameter SHARED_POOL_RESERVED_SIZE instead of large pool.

  20. Memory Areas: SGA: System Global Area • Java Pool • Services the parsing requirements for Java commands • Size is set by JAVA_POOL_SIZE in the parameter file.

  21. Memory Areas: PGA: Program Global Area • Memory that is used by a single Oracle user process • PGA is not sharable • Contains: • Sort area • Session information • Cursor state (stage in processing current SQL statements)

  22. Oracle Database Overview Instance SGA Shared Pool User Process Library Cache Data Buffer Cache Redo Log Buffer Data Dict. Cache SMON DBW0 PMON CKPT LGWR ARCH Server Process PGA Parameter Data files Control Redo Log Memory Processes Physical Files Archive Database

  23. Processes • SMON - System Monitor • Checks for consistency of the database • If necessary, initiates recovery of the database when the database is open • Coalesces contiguous free extents into larger free extents.

  24. Processes • PMON - Process Monitor • Cleans up failed user processes • If an update fails, PMON releases the row locks • DBWR - Database Writer • Manages the contents of the data block buffer cache and the dictionary cache • Performs batch writes of changed blocks from the SGA area to the data files

  25. Processes • LGWR - Log Writer • Manages the writing of the contents of the redo log buffer to the online redo log files • CKPT - Checkpoints • Updates database status information in the control files • Checkpoints cause DBWR to write all of the blocks that have been modified since the last checkpoint to the data files.

  26. Processes • ARCH - Archive • After the first redo log is filled, the second redo log will go into use, then the third,…then back to the first • ARCH will make copy of the each redo log before is overwritten • Serves in recovering a database

  27. Oracle Database Overview Instance SGA Shared Pool User Process Library Cache Data Buffer Cache Redo Log Buffer Data Dict. Cache SMON DBW0 PMON CKPT LGWR ARCH Server Process PGA Parameter Data files Control Redo Log Memory Processes Physical Files Archive Database

  28. Physical Files • Database files • Control Files (control1.ctl) • Records information about all the files in a database • Serves to maintain consistency and aid in recovery • Redo Logs (redo_1a.log) • Log of all transactions • Used in recovering in event of a database crash • 3 or more

  29. Physical Files • Database files • Data files • Database objects are stored in tablespaces. Tablespace is a logical division of the database. • System Tablespace (system.dbf file) • Data dictionary • Temporary Tablespace (temp.dbf file) • dynamically created objects that store data during large sorting operations • Rollback Tablespace (rbs.dbf file) • Maintain both statement-level and transaction-level read consistency with in the database • Data Tablespace (hrlarge.dbf file) • User and application tables,objects

  30. Physical Files • Database files • Data files • Each tablespace is made of 1 or more data files • Creating a new tablespace requires creating a new data file • Alert Log (alert.log) • Records commands and command results of major events in the life of a database • Tablespace creations • Log switching • Startup and shutdowns • Problems (internal errors)

  31. Basic Database • One or more data files • One or more control files • Two or more online redo logs • Internally • Users/Schemas • Rollback segments • User Tablespaces • Data dictionary tables • User objects

  32. Basic Database • Server • SGA • SMON • PMON • DBWR • LGWR • CKPT • ARCH • Users PGA

  33. Database Administration • Because an Oracle database system can be quite large and have many users, someone or some group of people must manage this system. The database administrator (DBA) is this manager. Every database requires at least one person to perform administrative duties.

  34. Database Administration • Installing and upgrading the Oracle server and application tools • Allocating system storage and planning future storage requirements for the database system • Creating primary database storage structures (tablespaces) after application developers have designed an application

  35. Database Administration • Creating primary objects (tables, views, indexes) once application developers have designed an application • Modifying the database structure, as necessary, from information given by application developers • Enrolling users and maintaining system security

  36. Database Administration • Ensuring compliance with database vendor license agreement • Controlling and monitoring user access to the database • Monitoring and optimizing the performance of the database • Planning for backup and recovery of database information

  37. Database Administration • Maintaining archived data on tape • Backing up and restoring the database • Logical - Database records • Export/Import • Physical - redo logs, data and control files • Offline (Cold) • Online (Hot) • Contacting database vendor for technical support

  38. Tools • OEM • Scripts • Oracle Support Site Demo of OEM

More Related