Posts Tagged ‘tablespace’

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

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:

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

 

Advertisements

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

Temporary Tablespace DDL

What are TEMPFILES?

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL>DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1M;

Oracle9i Default Temporary Tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/u02/oradata/TESTDB/temp2_01.dbf’ SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;



Drop Tempfile Command Method – (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with the above method, this should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop. For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp01.dbf:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’  SIZE 512m 

AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

select TABLESPACE_NAME, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB” from V$TEMP_SPACE_HEADER;

select tsh.TABLESPACE_NAME,file_name, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB”

from V$TEMP_SPACE_HEADER tsh,dba_temp_files dbf

where tsh.tablespace_name = dbf.tablespace_name;

To Check who is using temp space

select b.tablespace ,b.segfile# ,b.segblk#

,round(((b.blocks*p.value)/1024/1024),2) size_mb

,a.sid ,a.serial# ,a.username ,a.osuser ,a.program ,a.status

from v$session a ,v$sort_usage b ,v$process c ,v$parameter p

where p.name=’db_block_size’

and a.saddr = b.session_addr

and a.paddr=c.addr

order by  b.tablespace,b.segfile#,b.segblk#,b.blocks

IDEVELOPEMT TEMP TBLSPACE

ORAFAQ ON TEMP FILES