Oracle Database Architecture

Oracle Database Architecture

Oracle is an RDBMS (Relational Database Management System). The Oracle database architecture can be described in terms of logical and physical structures. The advantage of separating the logical and physical structure is that the physical storage structure can be changed without affecting the logical structure.

Logical Structure

The logical structure for Oracle RDBMS consists of the following elements:
· Tablespace
· Schema

Tablespace:
The Oracle database consists of one or more logical portions called as ‘Tablespaces’. A tablespace is a logical grouping of related data. A database administrator can use Tablespaces to do the following:
· Control disk space allocation for database data.
· Assign specific space quotas for database users.
· Perform partial database backup or recovery operations.
· Allocate data storage across devices to improve performance.

Each database has at least one Tablespace called SYSTEM Tablespace. As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace. Although a small database can fit within the SYSTEM tablespace, it's recommended that to create a separate tablespace for user data. Oracle uses the SYSTEM tablespace to store information like the data dictionary. Data dictionary stores the metadata (or the data about data). This includes information like table access permissions, information about keys etc.
Data is stored in the database in form of files called as datafiles. Each Tablespace is a collection of one or more Datafiles. Each data file consists of ‘Data blocks’, ‘extents’ and ‘segments’.

Data Blocks:
At the finest level of granularity, an ORACLE database's data is stored in data blocks (also called logical blocks, ORACLE blocks, or pages). An ORACLE database uses and allocates free database space in ORACLE data blocks.

Extents:
The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks that are allocated for storing a specific type of information.

Segments:
The level of logical database storage above an extent is called a segment. A segment is a set of extents that have been allocated for a specific type of data structure, and all are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. ORACLE allocates space for segments in extents. Therefore, when the existing extents of a segment are full, ORACLE allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk, and may or may not span files. An Oracle database can use four types of segments:

· Data segment--Stores user data within the database.
· Index segment--Stores indexes.
· Rollback segment--Stores rollback information. This information is used when data must be rolled back.
· Temporary segment--Created when a SQL statement needs a temporary work area; these segments are destroyed when the SQL statement is finished. These segments are used during various database operations, such as sorts.

Schema:
The database schema is a collection of logical-structure objects, known as schema objects that define how you see the database's data. A schema also defines a level of access for the users. All the logical objects in oracle are grouped into a schema. A scheme is a logical grouping of objects such as:

· Tables
· Clusters
· Indexes
· Views
· Stored procedures
· Triggers
· Sequences

Physical Structure:

The physical layer of the database consists of three types of files:
1. One or more Datafiles
2. Two or more redo log files
3. One or more control files



Datafiles (.dbf files):
Datafiles store the information contained in the database. One can have as few as one data file or as many as hundreds of datafiles. The information for a single table can span many datafiles or many tables can share a set of datafiles. Spreading tablespaces over many datafiles can have a significant positive effect on performance. The number of datafiles that can be configured is limited by the Oracle parameter MAXDATAFILES.

Data is stored in the Oracle database in form of files called as datafiles. Each Tablespace is a collection of one or more Datafiles. Each data file consists of ‘Datablocks’, ‘Extents’ and ‘Segments’. Some systems put a limit on the number of datafiles that can be used. So, efficient definition of tablespaces and datafiles is important.

Redo Log Files (.rdo & .arc):
Oracle maintains logs of all the transaction against the database. These transactions are recorded in files called Online Redo Log Files (Redo Logs). The main purpose of the Redo log files is to hold information as recovery in the event of a system failure. Redo log stores a log of all changes made to the database. The redo log files must perform well and be protected against hardware failures (through software or hardware fault tolerance). If redo log information is lost, one cannot recover the system. When a transaction occurs in the database, it is entered in the redo log buffers, while the data blocks affected by the transactions are not immediately written to disk. In an Oracle database there are at least three or more Redo Log files. Oracle writes to redo log file in a cyclical order i.e. after the first log file is filled, it writes to the second log file, until that one is filled. When all the Redo Log files have been filled, it returns to the first log file and begin overwrite its content with new transaction data. Note, if the database is running in the ARCHIVELOG Mode, the database will make a copy of the online redo log files before overwriting them.

Control Files (.ctl):
Control files record control information about all of the files within the database. These files maintain internal consistency and guide recovery operation. Control files contain information used to start an instance, such as the location of datafiles and redo log files; Oracle needs this information to start the database instance. Control files must be protected. Oracle provides a mechanism for storing multiple copies of control files. These multiple copies are stored on separate disks to minimize the potential damage due to disk failure. The names of the database’s control files are specified via the CONTROL_FILES initialization parameter.


Oracle Instance and Database:
In Oracle to access the data in the database, Oracle uses a set of Background processes that are shared by every user. Also, along with this there is some memory structure that stores the most recently queried data from the database.

A Database Instance (also save as Server) is set of memory structures and background processes that access a set of database files. It is possible for the single database to contain multiple instances, which is known as Real Application Cluster. The parameters that determine the size and composition of an oracle instance are either stored in an initialization file called init.ora or in spfile.ora. The initialization parameter file is read during instance start up and may be modified by the DBA. Any modification made will not be affected until the next start up.

System Global Area (SGA):
Oracle uses an area of shared memory called the system global area (SGA) and a private memory area for each process called the program global area (PGA). The SGA consists of: System global area: The SGA is a shared memory region that contains data and control information for one oracle instance. Oracle allocates the SGA when an instance starts and de-allocates it when the instance shut downs. Every instance has the SGA .The entire SGA should be large as possible to increase the system performance and to reduce disk I/O.

The information stored in the SGA is divided into three memory structures,

1) Database buffers cache
2) Redo log buffers
3) Shared pool

Database buffers cache:
The database buffer stores the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently and most frequently used data is kept in memory. It improves the performance of system by reducing the disk I/O operations.

Redo log buffers:
The redo log buffer stores redo entries. This is a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log. An online redo log is a set of two or more files that record all the changes made to oracle data files and control files.

Shared pool:
The shared pool caches various constructs that can be shared among users, for example SQL shared area. For example’s SQL statements are cached so that they can be reused. Stored procedures can be cached for faster access. Note that in previous versions “out of memory” error were occurs if the shared memory was full. In oracle 10g it does not happen. It provides automatic shared memory tuning.

Program global area:
PGA is a memory buffer that contains data and control information for a server process. A server process is a process that services a client’s requests. A PGA is created by oracle when a server process is started. The information in a PGA depends on the oracle configuration. The PGA area is a non-shared area of memory created by oracle when a server process is started. The basic difference between SGA and PGA is that PGA cannot be shared between multiple processes in the sense that it is used only for requirements of a particular process whereas the SGA is used for the whole instance and it is shared.

Processes:
The relationships between the databases’ physical and memory structures are maintained and enforced by the Background Process. These are database’s own background processes that may vary in the number depending in your database configuration. The Trace files are only created when there is any problem.

Some of the Background Processes are:

SMON:
SMON stands for System Monitor and this database background process performs instance recovery at the start of the database. In a multiple instance system, SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. It coalesces i.e. combines contiguous free extents into larger free extents.

PMON:
PMON stands for the Process Monitor and this database background process cleans up failed user processes. PMON is responsible for releasing the lock i.e. cleaning up the cache and freeing resources that the process was using. Its effect can be seen when a process holding a lock is killed.

DBWR:
The DBWR (Database Writer) background process is responsible for managing the contents of the data block buffer cache and dictionary cache. DBWR performs batch writes of changed block. Since Oracle uses write-ahead logging, DBWR does not need to write blocks when a transaction commits. In the most common case, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first. Although there is only one SMON and one PMON process running per database instance, one can have multiple DBWR processes running at the same time. Note the number of DBWR processes running is set via the DB_WRITER_PROCESSES.

LGWR:
The LGWR (Log Writer) background process manages the writing of the contents of the redo log buffer to the online redo log files. LGWR writes the log entries in the batches form. The Redo log buffers entries always contain the most up-to-date status of the database. Note LGWR is the only one process that writes to the online redo log files and the only one that directly reads the redo log buffer during to the normal database operation.

Archiver (ARCH):
The Archiver process reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s). Actually, for most databases, ARCH has no effect on the overall system performance. On some large database sites, however, archiving can have an impact on system performance. We can specify up to ten ARCn processes for each database instance LGWR will start additional Archivers as needed, based on the load, up to the limit specified by the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.

Checkpoint process (CKPT):
All modified information in database buffer in the SGA is written to the datafiles by a database write process (DBWR). This event indicates a checkpoint. The checkpoint process is responsible for signaling DBWR at checkpoints and updating all of the datafiles and control files of the database.

Recover (RECO):
The recover process automatically cleans up failed or suspended distributed transactions.

Oracle Database 10g: The Top 20 Features for DBA's

Oracle Database 10g: The Top 20 Features for DBAs
This can be found at the below given link: http://www.oracle.com/technology/pub/articles/10gdba/index.html

Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and FireWire

Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and FireWire

One of the most efficient ways to become familiar with Oracle Real Application Clusters (RAC) 10g technology is to have access to an actual Oracle RAC 10g cluster. There's no better way to understand its benefits - including fault tolerance, security, load balancing, and scalability — than to experience them directly. To know more abt this just go thru this link:

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html

Installing Oracle Database 10g Release 2 on Linux

Installing Oracle Database 10g Release 2 on Linux

The procedure & steps for "Installing Oracle Database 10g Release 2 on Linux x86" can be found here on this link:
http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

How does one rename a database? (For Starter DBA's / Juniors)

How does one rename a database? (For Starter DBA's / Juniors)

Follow these steps to rename a database:

1. Start by making a full database backup of your database (in case you need to restore if this procedure is not working).

2. Execute this command from sqlplus while connected to 'SYS AS SYSDBA':

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

3. Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql

4. Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".

5. Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql

6. Rename the database's global name:
ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

Creating a Database in Oracle (Complete Steps & Procedures)

Creating a Database in Oracle (Complete Steps & Procedures)

Every wondered how the Oracle database is structured? To learn more about the structure and how one would create an Oracle database using the latest features read this article. When creating a database, the main concept is to know how the database is structured in Oracle. You should be aware that ever since the release of Oracle 8i, the Oracle database support object orientated structures. This means that Oracle supports abstract data types and methods.
If you are new to Oracle, then perhaps you are unclear on how data is stored and what files are included internally and externally. With this article you will be clear about how to create a database in Oracle.

How Data is Stored:
Data in Oracle is stored in tables and accessed data via a relational model. This means that one may use the tables of data items inside the database in a manner such that the tables relate to one another with the use of primary and foreign keys.

Tables that contain a foreign key are often translated as being a lookup table. Each table should have a primary key, so that each row can be uniquely identified. The primary key is used to connect with the foreign key in another table to form a relationship.

Oracle also supports object-orientated structures. This allows the database to include abstract data types and methods. Due to this object orientation property, objects may related to other objects and that object may exist inside other objects.

Files are used to store data in Oracle. Oracle 9i removes the risk of having orphan file. That is, like the name suggests the file doesn’t have a parent, which means that the datafile was not removed when it’s tablespace is removed. To prevent this from ever happening to your database again, Oracle has introduced “Oracle Managed Files”.

Oracle Managed files, abbreviated as OMF, uses a brand new system of storing files. OMF uses file system directories as opposed to filenames for files that are associated with the tablespace.
This means that when a tablespace is created, the files are given a unique system-generated name, using the locations that have been assigned to them along with two new initialization parameters. So when the tablespace is removed, the files will be removed with it.

Another new feature in Oracle 9i is the System Managed Undo, abbreviated as SMO. The SMO was created to make the creation of the database simpler for the DBA and to reduce the problem relating to misused rollback segments. The DBA now creates a tablespace as an UNDO type to allow the Oracle database to dynamically and automatically manage the number of undoes, along with the size of the undo segments within the tablespace.

A database is partitioned using tablespaces. The tablespace named SYSTEM is the databases default tablespace. These tablespaces allows for the grouping of elements within the system that transact with the database.

A tablespace contains files. These files are called datafiles, which are used to physically store data for the database. Notice this property suggests that datafiles are internal and external structure types, since they are parented by a tablespace (internal property) and are physical storage files (external). The datafile may be resized after it has been created.

Rules about Tablespaces and Datafiles:
-A tablespace is parented by the database. So a tablespace cannot be a part of another database.
-A datafile is parented by the tablespace. So a datafile cannot be part of another database.
-A datafile may not be removed from a tablespace.

Now that we know how data is stored using Oracle, there are other special physical files that are associated with data storage and will assist in data recovery. The files that are created when the database in created and they are as follows:

Redo log files, which will monitor transactions in a chronological manner. These files are known as online redo log files. The reason why this is an important file to have associated with the database is evident when the database malfunctions.

According to the Oracle 9i DBA Handbook, a database should consist of 3 or more online redo files. To archive old online redo files before they are inevitably over written, you may set the database to run in ARCHIVELOG mode. This allows the DBA to look further back into the history of transactions with the database.

Control files, which holds the location of all the files the database will require. Multiple copies are made and stored on different disks to back up this operation-critical file. The CONTROL_FILES parameter specifies the names of the database control files. An instance must be shut down prior to an addition of a new control file.

Temp files, which acts as a temporary storage for a result set. If the result exceeds the memory available in the RAM’s buffer, then the result set will be stored into the temporary data file. Make sure that the size of the file is large enough to hold large sorting operations.

The other files associated with an Oracle 9i database are called parameter files. There are two types of parameter files. These are. Static parameter files – This is what the init.ora file is. This are common known as a PFILE and should be based on the init.ora file and renamed as init{SID}.ora, where SID is the system identifier. Server parameter files – This is a new feature in Oracle 9i named the SPFILE. These parameter files are written in binary and should not be altered manually. The files should be named as spfile{SID}.ora, where SID is again the system identifier. The Oracle server always maintains these files.To create the SPFILE we do the following,

CREATE SPFILE = ‘\spfile{SID}.ora’ FROM PFILE = ‘init{SID}.ora’

There are two types of parameter that are located in these parameter files. These are, Implicit parameter, which are parameters with no value. Oracle therefore assumes that the value for the parameter is the Oracle default values. Explicit parameter, which are parameter that have a value assigned to it.Now we know how the parameter files work. Well, to access data inside the database, Oracle uses a set of background processes that are shared between users.
Oracle states that an instance is “ a set of memory structures and background processes that access a set of database files.” Every instance has a large memory structure known as the System Global Area, abbreviated as SGA. The SGA will be explained in another article.

But where do the parameter fit in?

Well the parameters are used to set the size and composition of an instance and are the stored in one of the two specific parameter files as stated previously. The parameter file is read during the startup process. So, to access a database one must start an instance, which may call either the PFILE or the SPFILE. If there exist an SPFILE, then the STARTUP command will read that parameter file. If the SPFILE doesn’t exist, the default SPFILE is read. If that doesn’t exist than the PFILE is read. If you don’t wish for the server to read the PFILE, then simply override the default SPFILE with the PFILE.

STARTUP PFILE = ‘.init{SID}.ora’

Now you have a good understanding of architecture of an Oracle database, let us create one.

Creating a Database in Oracle - Procedure & Steps:
There are two ways that you can create a database in Oracle 10g. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 10g’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory.

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

SET ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Sample Database Creation Script:

CREATE DATABASE
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/data_file_location/system01.dbf' SIZE 600M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/data_file_location/sysaux01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/temp_file_location/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS"
DATAFILE '/data_file_location/undotbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
LOGFILE GROUP 1
('/redolog_file_location/redog1m1.log', '/redolog_file_location/redog1m2.log') SIZE 150M,
GROUP 2
('/redolog_file_location/redog2m1.log', '/redolog_file_location/redog2m2.log') SIZE 150M,
GROUP 3 ('/redolog_file_location/redog3m1.log', '/redolog_file_location/redog3m2.log') SIZE 150M;

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command.

For example if you want to create a database by name "SABIC" then the create database script will look something like this. The datafile, tempfile, logfile directory location will differ as per the mount point structure on the server/machine. Below given is for the one for the unix platform... Here is the code:

CREATE DATABASE SABIC
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u02/oradata/data/system01.dbf' SIZE 600M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/oradata/data/sysaux01.dbf' SIZE 500M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/temp/data/temp01.dbf' SIZE 500M
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u02/oradata/data/undotbs01.dbf' SIZE 500M
REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
LOGFILE GROUP 1
('/u03/oradata/log/redog1m1.log', '/u03/oradata/log/redog1m2.log') SIZE 150M,
GROUP 2
('/u03/oradata/log/redog2m1.log', '/u03/oradata/log/redog2m2.log') SIZE 150M,
GROUP 3 ('/u03/oradata/log/redog3m1.log', '/u03/oradata/log/redog3m2.log') SIZE 150M;

Save the above script something like cr8db.sql & then log in the database from sqlplus while connected to 'SYS AS SYSDBA' then start up the database in nomount state.

SQL> STARTUP NOMOUNT

So, if we wish to override the default spfile to start the new instance in nomount mode then type,
SQL> STARTUP NOMOUNT PFILE = ‘\pfile_location\init{SID}.ora’

SQL> @ cr8db.sql
Database created.

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@$ORACLE_HOME\rdbms\admin\catalog.sql

@$ORACLE_HOME\rdbms\admin\catproc.sql

@$ORACLE_HOME\sqlplus\admin\pubbld.sql

Okies now our database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.