Oracle Database Architecture

 

Database Architecture                   

An Oracle database server consists of an Oracle database and an Oracle instance.

Oracle Database architecture is based on physical and logical structures. 

 

Oracle Database Physical Structure

Oracle database consists of physical files to store information. Basically there are three types of physical files required for database:

Core Files:

• Data Files                    Contains all the data of database
• Control Files              Contains data about database(metadata)
• Redo Log Files          Contains all changes made to data, allows recovery

Other Files

• Parameter File         Defines how instance will be configured when it starts up
• Password File           Allow users to connect remotely to the database
• Archive Log Files   Contains history of redo log files
• Trace Files                 Contains messages written by server and background process
• Alert File                    Special trace file, chronological log of messages and errors              

 

 

Oracle Database Logical Structure

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest.

• Tablespace
• Segments
• Extents
• Data blocks

Tablespace

Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Segment

Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

• Data segment  (stores user data within the database)
• Index Segment (store Indexes)
• Temporary Segment ( stores rollback segment)
• Rollback/Undo segment (created when sql statement needs a temporary work area)

Extent

An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file.

Data Blocks

Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device

 

 

 

Oracle Instance

Oracle instance is comprised of memory structures and background processes that are required to perform different tasks to keep the instance and database running. Instance does not exist until database is started, which involves reading initialization parameter file and configuring instance according to the information within the parameter file.

Memory Structures

Instance has two major memory structures:

• System Global Area (SGA):  stores information in memory shared by processes in Oracle
• Program Global Area (PGA): contains information private to a particular process

Background Processes

Oracle creates a set of background processes for an instance that  manage the memory structures, asynchronously perform I/O to write data to disk, and do general housekeeping.
The most common background processes are the following:

• System monitor (SMON): Performs crash recovery when the instance starts after a failure
• Process monitor (PMON): Performs process cleanup when a user process fails
• Database writer (DBWn): Writes modified blocks from the database buffer cache to the files on disk
• Checkpoint (CKPT): Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint
• Log writer (LGWR): Writes redo log entries to disk
• Archiver (ARCn): Copies the redo log files to archival storage when the log files are full or a log switch occurs

 

Related articles

• Oracle 9i Architecture (samadhandba.wordpress.com)
• Oracle 10g R2 Architecture (detailed with respect to SQL Rewrite)

Oracle Parameter Files

Parameter Files

Oracle must read either an initialization parameter file or a server
parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter.

• Server Parameter Files

• Initialization Parameter Files 

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:

• spfile$ORACLE_SID.ora
• spfile.ora
• init$ORACLE_SID.ora

Initialization Parameters

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Complete List of Initialization Parameters (10g)

Basic Parameters

There are about 29 basic parameters. Only a few of these parameters must be explicitly set because the default values are adequate in the majority of cases.

DB_DOMAIN	NLS_LANGUAGE
DB_NAME , DB_UNIQUE_NAME	NLS_TERRITORY
DB_RECOVERY_FILE_DEST	OPEN_CURSORS
DB_RECOVERY_FILE_DEST_SIZE	PGA_AGGREGATE_TARGET
DB_CREATE_ONLINE_LOG_DEST_n	PROCESSES
COMPATIBLE	REMOTE_LISTENER
CONTROL_FILES	REMOTE_LOGIN_PASSWORDFILE
CLUSTER_DATABASE	ROLLBACK_SEGMENTS
DB_BLOCK_SIZE	SESSIONS
DB_CREATE_FILE_DEST	SGA_TARGET
INSTANCE_NUMBER	SHARED_SERVERS
JOB_QUEUE_PROCESSES	STAR_TRANSFORMATION_ENABLED
LOG_ARCHIVE_DEST_n	UNDO_MANAGEMENT
LOG_ARCHIVE_DEST_STATE_n	UNDO_TABLESPACE

  

Advanced Parameters

Other than basic parameters, several parameters are used for database tuning and other advance services. Few examples of parameters

SESSION_CACHED_CURSORS

CURSOR_SHARING

DB_KEEP_CACHE_SIZE

OPEN_LINKS

AUDIT_FILE_DEST

JAVA_POOL_SIZE

LARGE_POOL_SIZE

STREAMS_POOL_SIZE

QUERY_REWRITE_ENABLED

Hidden/Undocumented Parameters

Hidden parameters should only be used as the last resort and preferably when advised by Oracle Support.

_abort_recovery_on_join

_alert_message_purge

_backup_io_pool_size

_cursor_plan_enabled

_log_checkpoint_recovery_check

Complete List of Hidden Parameters(10g)

 

Viewing and Changing Parameters Settings

• SHOW PARAMETERS command from SQL*PLUS can be used to to view parameter values
  • SQL> SHOW PARAMTERS CONTROL_FILES
    
    
• V$PARAMETER, V$PARAMETER2 views is used to display current parameter values
• V$SPPARAMETER view is used to display contents of server parameter file.

CHANGING PARAMETER VALUES 

  PFILE can be edited with any text editor but the SPFILE is a binary file and its parameter values can be changed using “ALTER SYSTEM SET” and “ALTER SYSTEM RESET”commands.

Removing a Parameter from SPFILE: 

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;

 

Parameter Setting to SPFILE / Memory/ Both

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE; SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH SID='*';

 

The scope parameter can be set to SPFILE, MEMORY or BOTH. Memory is default if PFILE was used at STARTUP and BOTH is default if SPFILE was used at STARTUP.

• MEMORY:  The parameter value is set for the current instance only.
• SPFILE: The parameter value is updated in SPFILE and takes effect at next STARTUP.
• BOTH: The parameter value is set for current instance and persists to the SPFILE

Recommended init parameters for Websphere Portal

Recommended init parameters for IBM Websphere Portal

wspprd.__db_cache_size=587202560
wspprd.__java_pool_size=16777216
wspprd.__large_pool_size=16777216
wspprd.__shared_pool_size=939524096
wspprd.__streams_pool_size=33554432
*.compatible=’10.2.0.3.0′
*.db_block_size=8192
*.db_cache_size=524288000
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_recovery_file_dest=’/wspprd/oracle/product/10.2.0/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=1600
*.pga_aggregate_target=314572800
*.pre_page_sga=TRUE
*.processes=600
*.sessions=665
*.sga_max_size=1610612736
*.sga_target=1610612736
*.shared_pool_size=536870912
*.db_writer_processes=20
*.sort_area_size=65536
*.cursor_space_for_time=TRUE
*.session_cached_cursors=500
*.log_checkpoints_to_alert=TRUE
*.log_checkpoint_interval=0
*.log_checkpoint_timeout=0
*.fast_start_mttr_target=3600
*.log_buffer=419430400

Related articles by Zemanta

• Creating Oracle Database for Websphere Portal (mohibalvi.wordpress.com)

Locks and Queries

Query to check blocking and waiting sessions

SELECT holding_session bsession_id, waiting_session wsession_id,
b.username busername, a.username wusername, c.lock_type type,
mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.waiting_session=a.sid and c.holding_session=b.sid

 

To Find which session is blocking other sessions with some system info

 

select s1.username || ‘@’ || s1.machine   || ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘
  || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1   and l2.id2 = l2.id2 ;

 

Lock type and the ID1 / ID2 columns

The first place to look is the TYPE column. There are only three types of user locks, TX, TM and UL. UL is a user-defined lock — a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it’s acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It’s acquired once for each object that’s being changed. The ID1 column identifies the object being modified.

Selecting locking information using v$lock, v$session, dba_objects. First look up for rows with block > 0. Pick the sid and look for rows having same id1(one row should have sid that has another row with block > 0). Create rowid using sid identified earlier as blocking session. Using rowid look up the row in the table.

select * from v$lock

select object_name from dba_objects where object_id=68046

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=135

select do.object_name,
   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,  ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    from v$session s, dba_objects do
    where sid=135
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

    select * from sys.tstlock where rowid = ‘AAAQnOAABAAAQ9yAAA’

For RAC

SELECT substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;

Isolation Levels are how Oracle executes SQL statements in regards to read consistency and is directly related to what lockmay be ignored.

• Read Committed (Default)
• Serializable Transactions
• Read-only

Read Committed: Each query executed by a transaction sees only data that was committed before the query (not the transaction) began.

Serializable Transaction: See only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

Read-Only: See only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

Oracle Lock Modes

• Exclusive Lock Mode
• Share Lock Mode

Exclusive Lock Mode: Prevents the associates resource from being shared. This lockmode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share Lock Mode: Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

Oracle Lock Types

• DML locks (data locks)
• DDL locks (dictionary locks)
• Oracle Internal Locks/Latches
• Oracle Distributed Locks
• Oracle Parallell Cache Management Locks

Oracle DML Lock Types

• Row Level Locks
• Table Level Locks

        Oracle Row Locks [TX]

• All DML locks Oracle acquires automatically are row-level locks.
• No limit to the number of row locks held by a transaction.
• Oracle does not escalate locks from the row level.
• Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.
• Readers of data do not wait for writers of the same data rows.
• A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
• If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

        Oracle  Table Level Lock [TM]

• A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCK TABLE.  Reasons are to reserve DML access to the table on behalf of a transaction and prevent DDL operations
• Table locks prevent the an exclusive DDL lock on the same table which prevents DDL operations.  Example, a table cannot be altered or dropped if any uncommitted transaction holds a table lock for it.
• A table lock can be held in several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X).

Oracle DDL Lock Modes

• Exclusive DDL Locks
• Shared DDL Locks
• Breakable Parse Locks

Oracle Latches

• Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.

Oracle Internal Locks

• Data Dictionary Locks
  • Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.
• File and Log Management Locks
  • Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.
• Tablespace and Rollback Segment Locks
  • Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Data Dictionary Tables

DBA_LOCK_INTERNAL
1 row for every lock or latch held or being requested

DBA_LOCKS
All locks or latches held or being requested

DBA_DML_LOCKS
All DML locks held or being requested in DB

DBA_DDL_LOCKS
All DDL locks held or being requested in DB

DBA_BLOCKERS
Non-waiting sessions holding locks being waited on

DBA_WAITERS
All sessions waiting on, but not holding waited for locks

 

Good Link for Locks

ORAFAQ on Locks

OPEN CURSORS

Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

Monitoring open cursors

v$open_cursor shows cached cursors, not currently open cursors, by session. If you’re wondering how many cursors a session has open, don’t look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name=’opened cursors current’. This will give the number of currently opened cursors, by session:

   --Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

Tuning OPEN_CURSORS

To see if you’ve set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS

select max(a.value) as highest_open_cur, p.value as max_open_cur

   from v$sesstat a, v$statname b, v$parameter p

   where a.statistic# = b.statistic#

   and b.name = ‘opened cursors current’

   and p.name= ‘open_cursors’

   group by p.value;

Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_textfrom v$open_cursor c, v$sql sqlwhere c.sql_id=sql.sql_id — for 9i and earlier use: c.address=sql.addressand c.sid=&sid

Check Session Cursor Cache tuning for details.

 

UNDO Tablespace

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified. The header of an undo segment contains a transaction table where information about the current transactions using the undo segment is stored.
A serial transaction uses only one undo segment to store all of its undo data.
Many concurrent transactions can write to one undo segment.

Initialization Parameter
UNDO_MANAGEMENT
If AUTO, use automatic undo management. The default is MANUAL.

UNDO_TABLESPACE
An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Transaction Rollback
When a transaction modifies a row in a table, the old image of the modified columns (undo data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores the original values by writing the values in the undo segment back to the row.

Transaction Recovery
If the instance fails while transactions are in progress, the Oracle server needs to undo any uncommitted changes when the database is opened again. This rollback is part of transaction recovery. Recovery is possible only because changes made to the undo segment are also protected by the redo log files.

Read Consistency
While transactions are in progress, other users in the database should not see any uncommitted changes made by these transactions. In addition, a statement should not see any changes that were committed after the statement begins execution. The old values (undo data) in the undo segments are also used to provide the readers a consistent image for a given statement.

Read Consistency
The Oracle server guarantees that a statement sees data from a consistent time, even if that data is modified by other transactions.
When the Oracle server begins executing a SELECT statement, it determines the current system change number (SCN) and ensures that any changes not committed before this SCN are not processed by the statement. Consider the case where a long-running query is executed at a time when several changes are being made. If a row has changes that were not committed at the start of the query, the Oracle server constructs a read-consistent image of the row by retrieving the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.

TYPES of UNDO Segment

• SYSTEM: Used for objects in the SYSTEM
tablespace
• Non-SYSTEM: Used for objects in other
tablespaces:
– Auto Mode: Requires an UNDO tablespace
– Manual Mode:
– Private: Acquired by a single instance
– Public: Acquired by any instance
• Deferred: Used when tablespaces are taken offline
immediate, temporary, or for recovery

Dropping an UNDO Tablespace
• The DROP TABLESPACE command drops an UNDO
tablespace.
• An UNDO tablespace can only be dropped if it is
currently not in use by any instance.
• To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete

You can drop tablespace UNDOTBS after all transactions within the tablespace are complete.
To determine whether any active transactions exists use the following query:
SQL> SELECT a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS’
)  AND a.usn = b.usn;
NAME                        STATUS
————————- —————
_SYSSMU4$           PENDING OFFLINE

Automatic Undo Management:

– UNDO_SUPPRESS_ERRORS
     Set to TRUE, this parameter suppresses errors while attempting to  execute  manual operations in AUTO mode.
– UNDO_RETENTION
Controls the amount of undo data to retain for consistent read

Determines how long to retain undo data to provide for consistent reads. Retaining undo data longer allows for longer queries and also requires larger data files for the UNDO tablespace.
The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.

ALTER SYSTEM SET UNDO_RETENTION=900;

A value of 900 retains undo data for 15 minutes.

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires
three pieces of information
• (UR) UNDO_RETENTION in seconds
• (UPS) Number of undo data blocks generated per second
• (DBS) Overhead varies based on extent and file size (db_block_size)

        UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
            FROM v$parameter
            WHERE name = ‘undo_retention’),
      (SELECT (SUM(undoblks)/SUM(((end_time –
            begin_time)*86400))) AS UPS
            FROM v$undostat),
      (SELECT value AS DBS
            FROM v$parameter
            WHERE name = ‘db_block_size’);

Divide the result by 1048576 to get size in MB.

Data Dictionary Views

– DBA_ROLLBACK_SEGS
• Dynamic Performance Views
– V$ROLLNAME
– V$ROLLSTAT
– V$UNDOSTAT
– V$SESSION
– V$TRANSACTION

SELECT segment_name,owner,tablespace_name,status
FROM dba_rollback_segs;

SEGMENT_NA  OWNER   TABLESPACE  STATUS
——————— ———–   ——————-  ——
SYSTEM               SYS          SYSTEM         ONLINE
_SYSSMU1$     PUBLIC     UNDO1          ONLINE

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.

SELECT n.name, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

 

To check the use of a undo segment by currently active transactions, join the
V$TRANSACTION and V$SESSION views:

SELECT s.username, t.xidusn, t.ubafil,  t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

Automatic segment space management

Automatic Segment-Space Management

• Bitmap segments contain a bitmap that describes
the status of each block in the segment with
respect to its available space.
• The map is contained in a separate set of blocks
referred to as bitmapped blocks (BMBs).
• When inserting a new row, the server searches the
map for a block with sufficient space.
• As the amount of space available in a block
changes, its new state is reflected in the bitmap.

Automatic segment-space management can be
enabled at the tablespace level only, for locally
managed tablespaces.
• After a tablespace is created, the specifications
apply to all segments created in the tablespace.

CREATE TABLESPACE data02
DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

Tablespaces

Tablespaces
The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.
• A tablespace may consist of zero or more segments.
• Tablespaces can be brought online while the database is running.
• Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.
• Tablespaces can be switched between read-write and read-only status.

Data Files
• Each tablespace in an Oracle database consists of one or more files called data files.
These are physical structures that conform with the operating system on which the Oracle server is running.
• A data file can belong to only one tablespace.
• An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.
• The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow.

Segments
• A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment.
• A tablespace may consist of one or more segments.
• A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace.
• Each segment is made up of one or more extents.

Extents
Space is allocated to a segment by extents.
• One or more extents make up a segment.
– When a segment is created, it consists of at least one extent.
– As the segment grows, extents get added to the segment.
– The DBA can manually add extents to a segment.
• An extent is a set of contiguous Oracle blocks.
• An extent cannot span a data file but must exist in one data file.

Data Blocks
The Oracle server manages the storage space in the data files in units called Oracle blocks or data blocks.
• At the finest level of granularity, the data in an Oracle database is stored in data blocks.
• Oracle data blocks are the smallest units of storage that the Oracle server can allocate, read, or write.
• One data block corresponds to one or more operating system blocks allocated from an existing data file.
• The standard data block size for an Oracle database is specified by the
DB_BLOCK_SIZE initialization parameter when the database is created.
• The data block size should be a multiple of the operating system block size to avoid unnecessary I/O.
• The maximum data block size is dependent on the operating system.

 

Advantages of Locally Managed Tablespaces
Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:
• Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table.
• Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
• The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed
tablespace.
• Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

 

Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed.

The keyword auto tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, andFREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

The following statement creates tablespace lmtbsb with automatic segment-space management:

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M   EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Dictionary Managed Tablespaces• Extents are managed in the data dictionary• Each segment stored in the tablespace can have a different storage clause• Coalescing required

CREATE TABLESPACE userdata

DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE ( initial 1M NEXT 1M );

Undo Tablespace

• Used to store undo segments

• Cannot contain any other objects

• Extents are locally managed

• Can only use the DATAFILE and EXTENT MANAGEMENT clauses of the CREATE TABLESPACE command

CREATE UNDO TABLESPACE undo1

DATAFILE ‘/u01/oradata/undo101.dbf’ SIZE 40M;

 

Temporary Tablespace

 

• Used for sort operations

• Cannot contain any permanent objects

• Locally managed extents recommended

CREATE TEMPORARY TABLESPACE temp

TEMPFILE ‘/u01/oradata/temp01.dbf’ SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

 

Dropping Tablespaces

 

• Tablespace removed from data dictionary

• Optionally, contents removed from data dictionary

• OS files can be deleted with the optional AND DATAFILES clause:

DROP TABLESPACE userdata

INCLUDING CONTENTS AND DATAFILES;

 

CHANGING SIZE OF DATAFILE

ALTER DATABASE

DATAFILE ‘/u03/oradata/userdata02.dbf’ RESIZE 200M;

ALTER TABLESPACE app_data

ADD DATAFILE ‘/u01/oradata/userdata03.dbf’ SIZE 200M;

 

Moving Data Files:

ALTER TABLESPACE

• The tablespace must be offline.

• The target data files must exist.

ALTER TABLESPACE userdata

RENAME  DATAFILE ‘/u01/oradata/userdata01.dbf’

TO ‘/u01/oradata/userdata01.dbf’;

ALTER DATABASE

• The database must be mounted.

• The target data file must exist.

ALTER DATABASE RENAME

FILE ‘/u01/oradata/system01.dbf’

TO ‘/u03/oradata/system01.dbf’;

Data dictionary views

DBA_TEMPFILES

DBA_TABLESPACES

DBA_DATA_FILES

V$TABLESPACE

V$TEMPFILE

V$DATAFILE

Redo Logs

Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions that have not yet been committed, undo segment information, and schema and object management statements. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are used only for recovery.

Online Redo Log Groups
• A set of identical copies of online redo log files is called an online redo log group.
• The LGWR background process concurrently writes the same information to all online
redo log files in a group.
• The Oracle server needs a minimum of two online redo log file groups for the normal
operation of a database.

Online Redo Log Members

• Each online redo log file in a group is called a member.
• Each member in a group has identical log sequence numbers and the same size.
• The log sequence number is assigned each time the Oracle server starts writing to a log group to identify each redo log file uniquely.
• The current log sequence number is stored in the control file and in the header of all data files.

 

How Redo Logs Work
The redo entries are written from the redo log buffer to one of the online redo log
groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits
• When the redo log buffer becomes one-third full
• When there is more than a megabyte of changed records in the redo log buffer
• Before the DBWn writes modified blocks in the database buffer cache to the data files

Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log
sequence number that is overwritten each time the log is reused.

Checkpoints
During a checkpoint:

• A number of dirty database buffers covered by the log being checkpointed are written to the data files by DBWn. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified.
• The checkpoint background process CKPT updates the headers of all data files and control files to reflect that it has completed successfully.

Checkpoints can occur for all data files in the database or for only specific data files.
A checkpoint occurs, for example, in the following situations:
• At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN
BACKUP] cause checkpointing on specific data files.

ADDING ONLINE REDO LOG MEMBERS

ALTER DATABASE ADD LOGFILE MEMBER
‘$HOME/ORADATA/u04/log1c.rdo’ TO GROUP 1,
‘$HOME/ORADATA/u04/log2c.rdo’ TO GROUP 2,
‘$HOME/ORADATA/u04/log3c.rdo’ TO GROUP 3;

Obtaining Log Group and Member Information
V$LOG

The following query returns information about the online redo log file from the control file:

 
SQL> SELECT group#, sequence#, bytes, members, status
2 FROM v$log;
</span>

<span style="font-family: 'Times New Roman'; font-size: small;">GROUP# SEQUENCE# BYTES MEMBERS STATUS
--------- ---------- -------- --------- ------------------------
1              688                 1048576    1           CURRENT
2              689                 1048576    1           INACTIVE</span>

<span style="font-family: 'Times New Roman'; font-size: small;">

 
 
The following items are the most common values for the STATUS column:




UNUSED indicates that the online redo log group has never been written to. This is the state of an online redo log file that was just added.
CURRENT indicates the current online redo log group. This implies that the online redo log group is active.
ACTIVE indicates that the online redo log group is active but is not the current online redo log group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
INACTIVE indicates that the online redo log group is no longer needed for instance recovery. It may or may not be archived.

 
 
Archived Redo Log Files



Archiving redo log files is accomplished by ARCn (Archiver) or manually through SQL statements.
An entry in the control file recording the archive log name, log sequence number, and high and low
SCN number is made whenever a redo log is successfully archived.
A filled redo log file cannot be reused until a checkpoint has taken place and the redo log file has been backed up the ARCn process.
Archived redo log files can be multiplexed.
Archived redo log files must be maintained by the DBA.

 
COMMANDS
ALTER SYSTEM SWITCH LOGFILE

ALTER SYSTEM CHECKPOINT

ARCHIVE LOG LIST

ALTER DATABASE ADD LOGFILE

ALTER DATABASE ADD LOGFILE MEMBER

ALTER DATABASE RENAME FILE

ALTER DATABASE DROP LOGFILE

ALTER DATABASE DROP LOGFILE MEMBER

ALTER DATABASE CLEAR LOGFILE
Dynamic Performance Views
V$THREAD

V$LOG

V$LOGFILE

V$DATABASE
			

CONTROL FILE

The control file is a small binary file necessary for the database to start and operate
successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open.

The control file is a binary file that defines the current state of the physical database..
• Loss of the control file requires recovery
• Is read at MOUNT stage
• Is required to operate
• Is linked to a single database
• Should be multiplexed
• Maintains integrity of database
• Sized initially by CREATE DATABASE

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.

 

Obtaining Control File Information

The SHOW PARAMETERS command can also be used to find the location of the control files.

show parameters control_files;

SELECT name FROM V$CONTROLFILE;

Information in several of the other dynamic performance views is obtained from the control
file:
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATEBASE

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.

These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.