1 / 16

Oracle Database Architecture

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. System Global Area (SGA). Background processes. Database Structures.

torgny
Download Presentation

Oracle Database Architecture

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. 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

  2. System Global Area (SGA) Background processes Database Structures Memory structures Instance Process structures Database files Storage structures

  3. Oracle Memory Structures . Server process 1 Server process 2 Background process PGA PGA PGA SGA Streams pool Large pool Shared pool Database buffer cache Redo log buffer Java pool

  4. SGA Process Structures • User process: Is started at the time a database user requests a connection to the Oracle server • 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 PGA Server process User process Background processes

  5. Oracle Instance Management SGA Shared pool Streams pool Large pool Java pool Database buffer cache Redo log buffer System Monitor (SMON) Process Monitor (PMON) Database Writer (DBWn) LogWriter (LGWR) Archive log files Checkpoint (CKPT) Archiver (ARCn) Control files Data files Redo log files

  6. DBWn Server Process and Database Buffer Cache • Buffers: • Pinned • Clean • Free or unused • Dirty Server process SGA Database buffer cache Data files

  7. Initialization Parameter Files spfileorcl.ora

  8. Simplified Initialization Parameters Advanced Basic CONTROL_FILES DB_BLOCK_SIZE PROCESSES UNDO_MANAGEMENT … DB_CACHE_SIZE DB_FILE_MULTIBLOCK_READ_COUNT SHARED_POOL_SIZE …

  9. Starting Up an Oracle Database Instance: OPEN OPEN STARTUP All files opened as described by the control file for this instance MOUNT Control file opened for this instance NOMOUNT Instance started SHUTDOWN

  10. Shutdown Modes Shutdown Mode Allows new connections Waits until current sessions end Waits until current transactions end Forces a checkpoint and closes files A No No No No I No No No Yes T No No Yes Yes N No Yes Yes Yes • Shutdown mode: • A = ABORT • I = IMMEDIATE • T = TRANSACTIONAL • N = NORMAL

  11. SHUTDOWN Options • On the way down: • Uncommitted changes rolledback, forIMMEDIATE • Database buffer cache written todata files • Resources released • On the way up: • No instance recovery During SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL or SHUTDOWN IMMEDIATE Consistent database (clean database)

  12. SHUTDOWN Options • On the way down: • Modified buffersnot written to data files • Uncommitted changes not rolled back • On the way up: • Online redo log files used to reapply changes • Undo segments used to roll back uncommitted changes • Resources released During SHUTDOWN ABORT or Instance failure or STARTUP FORCE Inconsistent database (dirty database)

  13. Using SQL*Plus to Start Up and Shut Down [oracle@EDRSR9P1 oracle]$ sqlplus dba1/oracle as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed Size 1218472 bytes Variable Size 250177624 bytes Database Buffers 33554432 bytes Redo Buffers 262144 bytes Database mounted. Database opened. SQL>

  14. 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

  15. 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

  16. 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.

More Related