Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other
Grid computing is a information technology architecture that provides lower cost enterprise information systems. Using grid computing, independent hardware, and software components can be connected and rejoined on demand to meet the changing needs of businesses. It also enables the use of smaller individual hardware components.
Large dedicated server
It has expensive costly components.
High incremental costs.
It has single point of failure.
Enterprise service at higher cost.
It has low cost modular components.
Low incremental costs.
It has no single point of failure.
Enterprise service at low cost.
The computing componenets of oracle grid are
Oracle Enterprise Manager and Grid Control
Oracle 10g Database and Real Application Clusters.
ASM Storage Grid.
Oracle Real Application Clusters 10g (RAC) enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.
The Oracle Automatic Storage Management (ASM) is a feature of Oracle Database 10g which provides a virtual layer between the database and storage so that group of disks can be treated as a single disk group and disks can be dynamically added or removed while keeping databases online.
The Grid Management feature of Oracle Enterprise Manager 10g provides a single console to manage multiple systems together as a logical group.
When Oracle starts, it reads the initialization parameter file to determine the values of initialization parameters. After this, it allocates an SGA and creates background processes.
When you start, the database instance comes into picture into system memory. Combination of the SGA and the Oracle processes is called an Oracle instance.
There are several tools for interfacing with the database using SQL
Oracle SQL*Plus and iSQL*Plus
Oracle Forms, Reports, and Discoverer
Oracle Enterprise Manager
An instance has started on the database server.
A client established a connection to the server, using the proper Oracle Net Services driver.
The server creates a dedicated server process on behalf of the user process.
The user executes SQL statement and commits the transaction.
The server process receives the statement and checks for any shared SQL area that contains a similar SQL.
The server process retrieves data from datafile (table) or SGA.
The server process modifies data in the SGA area. The DBWn process writes modified blocks permanently to disk. The LGWR process records the transaction in the redo log file.
The server process sends a message to the application.
Redo Log Files
Archive Log Files
Alert and Trace Log Files
Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema's and a schema can have multiple tablespace's. Database creates "SYSTEM tablespace" by default during database creation. It contains read only data dictionary tables which contains the information about the database.
Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.
Oracle stores data in data blocks also called as logical blocks, Oracle blocks or pages. A data block represents specific number of bytes of space on disk.
An extent is a specific number of consecutive data blocks allocated for storing a specific type of information
A segment is a group of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space
Database contain one or more Rollback Segments to roll back transactions and data recovery.
Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment
A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema's. We can create a new Schema/User. But we can't drop default database schema's.
Oracle Database automatically creates a schema when you create a user.
A view is a tailored presentation of the data contained in one or more tables or other views. A view is output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. A view is not assigned any storage space, nor does a view actually contain data
It provides security by restricting access to a predetermined set of rows or columns of a table. It hides data complexity. It simplifies statements for the user.
An example would be the views, which allow users to select data from multiple tables without actually knowing how to perform a join.
It presents the data in a different perspective from that of the base table. It isolate applications from changes in definitions of base tables. It saves complex queries.
These are schema objects that are used to summarize, compute, replicate, and distribute data. They can be used in various environments for computation such as data warehousing, decision support, and distributed or mobile computing and it also provides local access to data rather than accessing from remote sites. In data warehouses, MVs are used to compute and store aggregated data.
A dimension is hierarchical relationships between pairs of columns or column sets. Each value at the child level is tied with one value at the parent level. A dimension is a container of logical relationships between columns and it does not contain any data.
The sequence generator gives a sequential series of numbers.The sequence generator is especially useful for generating unique sequential numbers. Sequence numbers are Oracle integers of up to 38 digits defined in the database. A sequence definition provides information, such as
The sequence name
ascending or descending sequence
The interval between numbers
Whether Oracle should cache sequence numbers in memory
Sequence numbers are generated independent of any tables. The same sequence generator can be used for many tables. Sequence number generation can be used to produce primary keys for your data automatically. Oracle stores the definitions of all sequences for a particular database as rows in data dictionary table in the SYSTEM table- space.
Indexes are structures associated with tables and clusters. You can create indexes on one or more columns of a table to enhance the speed of SQL statement execution on that table. Just as the index in Oracle manual helps you to locate information faster than if there were no indexes. An Oracle index provides a faster access path to table data.
Oracle provides several indexing schemes
B-tree cluster indexes
Hash cluster indexes
Reverse key indexes
Bitmap join indexes
A synonym is an alias for database objects such as table, view, materialized view, sequence, procedure, function, package. Because a synonym is simply an alias, it does not require storage other than its data dictionary definition.
CREATE PUBLIC SYNONYM sales FOR jward.sales_data;
After the public synonym is created, you can query the table SALES_DATA with a simple SQL statement
SELECT * FROM sales;
The multiuser database management system's concern is how to control concurrency, which is the concurrent access of the same data by multiple users. Without sufficient concurrency controls, data could be updated or changed improperly, trading off with data integrity. To manage data concurrency is to make each user wait for a turn.
The goal of a database management system is to decrease the waiting time so it is either nonexistent or negligible to each user. The data manipulation language statements should proceed with as little intervention as possible, and destructive interactions among concurrent transactions should be prevented.
Destructive interaction is any interaction that updates data or alters underlying data structures incorrectly. Neither performance nor data integrity can be sacrificed. Oracle solves such problems by using various types of locks and a multi-version consistency model.
This architecture removes the need for a dedicated server process for each connection. A dispatcher routes various incoming network session requests to a shared server processes pool. An idle shared server process from a shared pool of server processes chooses a request from a common queue, which means a small number of shared servers can do the same amount of processing as many dedicated servers.
It is because of the amount of memory required for each user is comparatively small, less memory and process management are required, and more users can be supported.
It copies redo log files to a designated storage device after a log switch has occurred. Archiver processes are there only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 Archi ver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload.
Job queue processes are used for processing batch. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs like PL/SQL statements or procedures on an Oracle instance.
The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.
5th April | 08:00 AM