Chapter 10Oracle ServerOracle Database StructureOracle Database Structure (con’t)Oracle InstanceSystem Global AreaOracle Instance ProcessesOracle ProcessesOracle Background ProcessesSlide 10Slide 11Slide 12Oracle Startup & ShutdownSlide 14Schema ObjectsSlide 16Slide 17Slide 18Oracle’s Data DictionaryItem in the Data DictionaryTriggersStoragePhysical StorageData BlocksData Blocks (con’t)ExtentsSegmentsChapter 10 1Chapter 10Chapter 10 2Oracle Server•An Oracle Server consists of an Oracle database (stored data, control and log files.) The Server will support SQL to define and manipulate data and PL/SQL to control the flow of SQL, allow the use of variable & error handling procedures•Oracle Instance which is made up of the various processes (both system and user) to support a specific instance.Chapter 10 3Oracle Database Structure•One or more Data files•Two or more Log files called redo files. This files are used in recovery and trace the source of specific transaction.•One or more Control files which contain the database name, related file names and locations and the creation timestamp.Chapter 10 4Oracle Database Structure (con’t)•Trace Files and alert Log•A database is defined in terms of Schema Objects and Tablespaces.–Schema Objects contain definitions of tables, views, sequences, stored procedures indexes, clusters and database links.–Tablespaces, segments and extents are the terms used to describe the physical storage.Chapter 10 5Oracle Instance•System Global Area (SGA): Real memory area where database information is shared by users. For performance it should be as large as possible.Chapter 10 6System Global Area•SGA is further divided into 3 areas.–Database buffer cache: Most recently accessed data blocks from the database.–Redo log buffer –Shared pool, contains shared memory constructs; such as, shared SQL area which is made up of parse trees of SQL queries & execution plans for executing SQL statements.Chapter 10 7Oracle Instance Processes•User processes: A user process is created for the execution of some application.•Program Global Area (PGA): Contains data and control information for the server processes.•Oracle Processes: A Process or Thread of control which is assigned a private memory area in which it runs.Chapter 10 8Oracle Processes•Oracle creates Server Processes to handle requests. This is a multithreaded server operation for efficiency rather than creating a separate process for each request to the server.Chapter 10 9Oracle Background Processes•Database Writer (DBWR): Writes those blocks that were modified from the cache to the disk•Log Writer (LGWR): Writes from the log buffer to the online disk log file.•Checkpoint (CKPT): Refers to an event during which all the modified buffers in the SGA since the last checkpoint are written to the data files. It works with DBWR.Chapter 10 10Oracle Background Processes•System Monitor(SMON): Performs instance recovery, manages storage areas by making the space contiguous & recovers transactions.•Process Monitor(PMON): Performs process recovery when a user process fails. It manages the cache and other resources used by a user process.Chapter 10 11Oracle Background Processes•Archiver (ARCH): Archives on-line log files to archival storage location; such as, disk or tape ( if configured to do so).•Recoverer Process(RECO): Resolves distributed transactions that are pending due to a network or system failure in a Distributed database.Chapter 10 12Oracle Background Processes•Dispatchers (Dnnn): In a multi-threaded server configurations, routes requests from the connected user processes to available shared server processes. There is 1 dispatcher per standard communication protocol supported.•Lock Processes(LCKn): Used for inter-instance locking when Oracle in Parallel ModeChapter 10 13Oracle Startup & Shutdown•Starting an Instance: This is where the SGA is allocated, the background processes are created and the instance is initialized.•Mounting the Database: This associates a database with an instance. This is one step that is required to make the database available to users.Chapter 10 14Oracle Startup & Shutdown•Opening a database: This is the second operation needed to make the database available to users. It opens the on-line data files and the log files.•The reverse of the above procedure is used to shutdown the database.•These are ALL actions done by the DBA.Chapter 10 15Schema Objects•TABLES: Tables are the basic unit that is used in Oracle to store data. It conforms with the relational model. Each column has a column name, datatype and width.•Views: View are virtual tables which on defined either on other views or tables.Chapter 10 16Schema Objects•Synonyms: Direct references to objects. They are used to provide public access to an object while masking its real name or owner. Private synonyms may be created that are only available to that user.•Program Unit: Procedures, functions or packages written in PL/SQLChapter 10 17Schema Objects•Sequence: A special provision of a data type in Oracle for attribute value generation.•Indexes: An index can be generated on one or more columns of a table as requested using SQL.Chapter 10 18Schema Objects•Cluster: A group of records from one or more tables physically stored in a mixed file. This is to improve performance. Such as using an Index Cluster.•Database Links: Named objects in Oracle that establish paths from one database to another. (Used in distributed databases.)Chapter 10 19Oracle’s Data Dictionary•This is made up of a read-only set of tables that store the Metadata. Access to the information in these tables is usually done through views .•These views begin with one of three special prefixes: USER,DBA, or ALL.Chapter 10 20Item in the Data Dictionary•Names of users•Security Information: Privileges and roles.•Schema Object Information•Integrity constraints•Space Allocation & Utilization of the database objects.•Statistics on attributes, tables and predicates.•Access audit trail information.Chapter 10 21Triggers•There are some business rules that we wish to actively enforce every time some action is done; such as, insert, delete or update. In Oracle this is done through the use of triggers.•Triggers either enforce additional constraints or perform additional actions.Chapter 10 22Storage•Each Database is divided into one or more different