2024-S2-IT4011-Lecture -01-Oracle Database Architecture.pdf

Database Administration & Storage systems – IT4011 Database Administration – IT4040 ORACLE DATABASE ARCHITECTURE Indika Alahapperuma 21/7/2024 Oracle DB Server Architecture Oracle DB server consists of an...

Database Administration & Storage systems – IT4011 Database Administration – IT4040 ORACLE DATABASE ARCHITECTURE Indika Alahapperuma 21/7/2024 Oracle DB Server Architecture Oracle DB server consists of an Oracle instance and an Oracle database Combination of the SGA (System Global Area) and Oracle processes is called Oracle instance Oracle database is referred as storage structure of the database Multitenant Architecture Multitenant architecture enables an Oracle database to be a Container DB(CDB). CDB A CDB contains one or more user- created PDBs and application containers. PDB A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an application as a separate database. Application Container An application container is an optional, user-created container within a CDB that stores data and metadata for one or more applications. Database Instance Structure Oracle Database Processes A process is a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job, task, or thread. Instance Memory Structures Oracle Database creates and uses memory structures for program code, data shared among users, and private data areas for each connected user. Process Structure There are three kind of processes in a database server; client process, server process and background process A client process is started at the time a database user requests a connection to the Oracle server. Client process can run on a host other than the database host, but server process cannot. Server process connects to the Oracle instance and is started when a user establishes a session. The server process can read and write into SAG, but client process can’t. Background processes are started when instance is started 5 Overview of Server Process ▪ There are two types of server processes available in Oracle database instance to service client requests; ▪ Dedicated server process, which services only one user process ▪ Shared server process, which can service multiple user processes ▪ Database is always enabled to allow dedicated server process, but database need to be configured and enabled for shared server. 6 Dedicated Server Architecture 7 Shared Server configuration and Processes 8 Shared Server Architecture ▪ Potential benefits of shared server are; ▪ reduces the number of processes on the operating system ▪ reduces instance PGA memory ▪ increases application scalability and the number of clients that can simultaneously connect to the database ▪ may be faster than dedicated server when the rate of client connections and disconnections is high ▪ Disadvantages of shared server process are; ▪ including slower response time in some cases ▪ does not support full database features ▪ increased complexity for setup and tuning ▪ As a general guideline, only use shared server when you have more concurrent connections to the database than the operating system can handle. 9 Application Architecture ▪ The application architecture is the computing environment in which a database application connects to an Oracle database. ▪ Client/Server architecture, the client application initiates a request for an operation to be performed on the database server. ▪ Multitier architecture, one or more application servers perform parts of the operation. An application server contains a large part of the application logic, provides access to the data for the client, and performs some query processing. Load on the database decreases. The application server can serve as an interface between clients and multiple databases Provide an additional level of security. 10  Physical Database Structure Database consists of following three main types of files Control files: Contains the data about database itself and very critical Data files: Contains user or application data of the Control Data Online redo database files files log files Online redo log files: Allows for the instance recovery of the database Other files required for successful Parameter operation of the database Backup Archive log file Parameter file files files Alert log files Password file Backup files Password Alert and trace Archive log files file log files Trace files 11 Parameter File ▪ To create and start Oracle instance parameter file is required. ▪ There are two types of parameter files. Sample parameter file ▪ spfile: binary file and this is the preferred type for Oracle database. This file can be modified using database only. No manual modification is allowed. ▪ init file: text file this is used to start Oracle instance in the event spfile is not available in the default location. This is modified using text editor used in the OS. Alert Log ▪ Every database has an alert log, which is an XML file containing a chronological log of database messages and errors. ▪ Database errors, warnings and some administrative operations appear in the Portion of an alert.log alert.log Logical & Physical Storage Structures Oracle Database allocates logical space for all data in the database. The logical units of database space allocation are data blocks, extents, segments, and tablespaces. At physical level, the data is stored in data files on disk. The data in the data files is stored in operating system blocks. 14  Database Blocks, Extents and Segments ▪ Oracle allocates logical database space for all data in a database ▪ At the finest level of granularity, Oracle stores data in database blocks ▪ The data is stored in OS level as bytes ▪ At the time of database creation, database block size is fixed ▪ The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information ▪ A segment is a set of extents. Each segment is allocated for specific data structure and resides in same tablespace ▪ Eg: Table data is stored in table segment and index Segment Extents Data Disk data is stored in index’s segment blocks blocks 15 Tablespaces Oracle database stores data in logical structure called ‘Tablespace’ Tablespace consists of data files which reside on the file system of the database server. This is a physical file A tablespace consists of one or more data files One data file belongs only to one tablespace SYSTEM, SYSAUX and UNDO tablespaces are mandatory tablespace for a database DBA can create other tablespaces to store user/application data 16 Tablespaces and datafiles 17 The Relationships Among Segments, Extents, and Data Blocks 18 Segments, Extents & Data Blocks Within a Tablespace 19 Database Block Size ▪ Every database has a database block ▪ DB_BLOCK_SIZE initialization parameter sets database block size ▪ Block size of all tablespaces defaults to database block size ▪ If required different block size tablespaces can be created References ▪ Oracle Database Concepts, 23ai 21