Course : (IS436 / IS337) Text Book : Oracle Database11g Administration I By John Watson – ISBN 978-0-07-159102-7 Publis

1. Welcome to all of you. Course : (IS436 / IS337) Text Book : Oracle Database11g Administration I By John Watson – ISBN 978-0-07-159102-7 Published by McGraw Hill. Time : 3-5 ( Sunday, Tuesday)

2. Grades assignment plan for the course: Assignments and quiz’s 10 Mid-Term Exam 15 Lab Test I 15 Lab Test II 20 Final Exam 40 Total 100

3. Name :Eyas El-Qawasmeh Email :eyasa@usa.net URL : www.sdiwc.us/eyas Cell phone : 0564569838 Office number : 1258

4. Your lab midterm exam is Oct. 27 ( practical) – Tuesday- 15% Your theory exam is Sept. 3 ( during the class second half) – Sunday – 15%

6. ORACLE DATABASE ARCHITECTURE

7. Tasks of an Oracle Database Administrator • A prioritized approach for designing, implementing, and maintaining an Oracle database involves the following tasks: 1. Evaluating the database server hardware 2. Installing the Oracle software 3. Planning the database and security strategy 4. Creating, migrating, and opening the database 5. Backing up the database 6. Enrolling system users and planning for their Oracle Network access 7. Implementing the database design 8. Recovering from database failure 9. Monitoring database performance

8. Objectives • After completing this lesson, you should be able to: • List the major architectural components of Oracle Database • Explain the memory structures • Describe the background processes • Correlate the logical and physical storage structures

9. Storagegrid Databasegrid Applicationgrid Gridcontrol Automatic Storage Management Real Application Clusters Oracle Streams Enterprise ManagerGrid Control Oracle Database 11g • “g” Stands for Grid • Global Grid Forum (GGF) • Oracle’s grid infrastructure: • Low cost • High quality of service • Easy to manage

10. Oracle Server Oracle Database Architecture • An Oracle server: • Is a database management system that provides an open, comprehensive, integrated approach to information management • Consists of an Oracle instance and an Oracle database

11. Oracle Database • The Oracle relational database management system (RDBMS) provides an open, comprehensive, integrated approach to information management

12. Connecting to a Server Client Middle tier Server Multitier architecture shown

13. Oracle Database Architecture Instance Others SMON PMON RECO SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache PGA Serverprocess DBWn CKPT LGWR ARCn Database Userprocess Archived log files Control files Online redo log files Data files

14. Userprocess Serverprocess Connection Connecting to the Database • Connection: Communication between a user process and an instance • Session: Specific connection of a user to an instance through a user process SQL> Select … Session User Session

15. Interacting with an Oracle Database Instance Userprocess Serverprocess SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache DBWn CKPT LGWR SMON PMON RECO Others ARCn User

16. Userprocess Serverprocess Oracle Database Server Structures Instance Memory structures SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache Processes DBWn CKPT LGWR SMON PMON ARCn RECO Others Database Storage structures Control files Online redo log files Data files

17. Oracle Database Memory Structures Serverprocess 1 Serverprocess 2 Backgroundprocess PGA PGA PGA SGA Data dictionary cache Shared SQL area Other Library cache Shared pool Redo log buffer Free memory I/O buffer Database buffercache Response queue Request queue Java pool Streams pool Large pool

18. Database Buffer Cache • Is part of the SGA • Holds copies of data blocks that are read from data files • Is shared by all concurrent users Instance SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache DBWn CKPT LGWR SMON PMON RECO Others ARCn

19. Redo Log Buffer • Is a circular buffer in the SGA • Holds information about changes made to the database • Contains redo entries that have the information to redo changes made by operations such as DML and DDL Instance SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache DBWn CKPT LGWR SMON PMON RECO Others ARCn

20. Shared Pool • Is a portion of the SGA • Contains: • Library cache • Shared SQL area • Data dictionary cache • Control structures Instance Shared SQL area SGA Shared pool Data dictionary cache Databasebuffercache Redo logbuffer Library cache Data dictionarycache Library cache Other DBWn CKPT LGWR SMON PMON RECO Others ARCn

21. Allocation and Reuse of Memory in the Shared Pool • Server process checks the shared pool to see if a shared SQL area already exists for an identical statement. • Server process allocates a private SQL area on behalf of the session. Data dictionary cache Shared SQL area Serverprocess Library cache Other Shared pool

22. Large Pool • Provides large memory allocations for: • Session memory for the shared server and the Oracle XA interface • I/O server processes • Oracle Database backup and restore operations Databasebuffercache Redo logbuffer Shared pool Free memory I/O buffer Response queue Request queue Java pool Streams pool Large pool Large pool

23. Java Pool and Streams Pool • Java pool memory is used in server memory for all session-specific Java code and data in the JVM. • Streams pool memory is used exclusively by Oracle Streams to: • Store buffered queue messages • Provide memory for Oracle Streams processes Java pool Streams pool

24. Process Architecture • User process • Is started when a database user or a batch process connects to Oracle Database • Database processes • Server process: Connects to the Oracle instance and is started when a user establishes a session • Background processes: Are started when an Oracle instance is started Instance SGA Shared pool Databasebuffercache Redo logbuffer Library cache Data dictionarycache PGA Userprocess Serverprocess Background processes DBWn CKPT LGWR SMON PMON RECO Others ARCn

25. Process Structures Server Server Server Server Server Server n processes SGA Shared pool Databasebuffercache Redo logbuffer Library cache SGA Data dictionarycache CKPT RECO PMON SMON DBWn LGWR ARCn Others Oracle background processes

26. Database Writer Process (DBWn) • Writes modified (dirty) buffers in the database buffer cache to disk: • Asynchronously while performing other processing • Periodically to advance the checkpoint DBWn Database buffer cache Database writer process Data files

27. LogWriter Process (LGWR) • Writes the redo log buffer to a redo log file on disk • Writes: • When a user process commits a transaction • When the redo log buffer is one-third full • Before a DBWn process writes modified buffers to disk LGWR Redo log buffer LogWriter process Redo log files

28. Checkpoint Process (CKPT) • A checkpoint is a data structure that defines a system change number (SCN) in the redo thread of a database. • Records checkpoint information in • Control file • Each data file header CKPT Control file Checkpoint process Data files

29. System Monitor Process (SMON) • The System Monitor process (SMON) performs recovery at instance startup if necessary. • Cleans up unused temporary segments SMON Instance System Monitor process Temporary segment

30. Process Monitor Process (PMON) • Performs process recovery when a user process fails • Cleans up the database buffer cache • Frees resources that are used by the user process • Monitors sessions for idle session timeout • Dynamically registers database services with listeners PMON User Failed user process Process Monitor process Database buffer cache

31. Recoverer Process • Used with the distributed database configuration • Automatically connects to other databases involved in in-doubt distributed transactions • Automatically resolves all in-doubt transactions • Removes any rows that correspond to in-doubt transactions RECO Recoverer processin database A In-doubt transactionin database B

32. Archiver Processes (ARCn) • Copy redo log files to a designated storage device after a log switch has occurred • Can collect transaction redo data and transmit that data to standby destinations ARCn Archiver process Copies of redo log files Archive destination

33. Other Processes • MMON: Performs manageability-related background tasks • MMNL: Performs frequent and lightweight manageability-related tasks • MMAN: Performs automatic memory management tasks • CJQ0: Runs user jobs used in batch processing • QMNC: Monitors the Streams Advanced Queuing message queues

34. Server Process and Database Buffer Cache • Buffers: • Pinned • Clean • Free or unused • Dirty SGA Serverprocess Databasebuffercache DBWn Database writer process Data files

35. Database Storage Architecture Control files Data files Online redo log files Parameter file Backup files Archived redo log files Password file Alert log and trace files

36. Database Storage Architecture • The files that constitute an Oracle database are organized into the following: • Control files: Contain data about the database itself. Without them, you cannot open data files to access the data in the database. • Data files: Contain the user or application data of the database, as well as metadata and the data dictionary • Online redo log files: Allow for instance recovery of the database. • The following additional files are important to the successful running of the database: • Parameter file: Is used to define how the instance is configured when it starts up • Password file: Allows sysdba, sysoper, and sysasm to connect remotely to the instance and perform administrative tasks • Backup files: Are used for database recovery. • Archived redo log files: Contain an ongoing history of the data changes (redo) that are generated by the instance.

37. Logical and Physical Database Structures Logical Physical Database Data file Schema Tablespace Segment Extent OS block Oracle datablock

38. USERS tablespace Tablespaces and Data Files • A database is divided into logical storage units called tablespaces. • Tablespacesconsist of one or more data files. • Data files belong to only one tablespace. Data file 2 Data file 1

39. SYSTEM and SYSAUX Tablespaces • The SYSTEM and SYSAUX tablespaces are mandatory tablespaces. • They are created at the time of database creation. • They must be online. • The SYSTEM tablespace is used for core functionality (for example, data dictionary tables). • The auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise Manager Repository).

40. Segments, Extents, and Blocks • Segments exist within a tablespace. • Segments are made up of a collection of extents. • Extents are a collection of data blocks. • Data blocks are mapped to disk blocks. Segment Extents Data blocks Disk blocks

41. REGIONS REGION_ID (PK) REGION_NAME REGIONS JOBS JOB_ID (PK) JOB_TITLE MIN_SALARY MAX_SALARY JOBS COUNTRIES COUNTRY_ID (PK) COUNTRY_NAME REGION_ID (FK) COUNTRIES JOB_HISTORY EMPLOYEE_ID (PK) START_DATE (PK) END_DATE JOB_ID (FK) DEPARTMENT_ID (FK) JOB_HISTORY LOCATIONS LOCATION_ID (PK) STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID (FK) EMPLOYEES EMPLOYEE_ID (PK) FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID (FK) SALARY COMMISION_PCT MANAGER_ID (FK) DEPARTMENT_ID (FK) LOCATIONS EMPLOYEES DEPARTMENTS DEPARTMENT_ID (PK) DEPARTMENT_NAME MANAGER_ID LOCATION_ID (FK) DEPARTMENTS Course Examples: The HR Schema

42. Dynamic Performance Views • Dynamic performance views provide access to information about changing states and conditions in the database. Session data Wait events Memory allocations Running SQL UNDO usage Open cursors Redo log usage And so on Oracle instance

43. Dynamic Performance Views: Considerations • These views are owned by the SYS user. • Different views are available at different times: • The instance has been started. • The database is mounted. • The database is open. • You can query V$FIXED_TABLE to see all the view names. • These views are often referred to as “v-dollar views.” • Read consistency is not guaranteed on these views because the data is dynamic.

44. Dynamic Performance Views: Usage Examples SQL> SELECT sql_text, executions FROM v$sql WHERE cpu_time > 200000; a SQL> SELECT * FROM v$session WHERE machine = 'EDRSR9P1' and logon_time > SYSDATE - 1; b SQL> SELECT sid, ctime FROM v$lock WHERE block > 0; c

45. Summary of Structural Components • Memory structures: • System Global Area (SGA): Database buffer cache, redo buffer, and various pools • Program Global Area (PGA) • Process structures: • User process and Server process • Background processes: SMON, PMON, DBWn, CKPT, LGWR, ARCn, and so on • Storage structures: • Logical: Database, schema, tablespace, segment, extent, and Oracle block • Physical: Files for data, parameters, redo, and OS block