INTERNAL ARCHITECTURE – ORACLE SERVER



INTERNAL ARCHITECTURE OF ORACLE SERVER

1) Tablespaces

a) CREATE TABLESPACE Command and using it in a CREATE TABLE command

b) Kinds of Tablespaces

i) Read-Only

ii) Transportable

iii) Locally Managed

2) Segments

3) Tables

a) Partitioned tables

b) Temporary tables

4) Indexes

a) B*Tree Indexes

b) Reverse Key index

c) Bitmap Indexes

d) Function Based indexes

5) Index-Organized tables

6) Clusters

7) Rollback Segments

8) Temporaray Segments

9) Database Blocks

10) Views

a) Materialized Views

11) ORACLE Data dictionary

a) Metadata

b) Schemas

c) Distributed data dictionary

i) Location Transparency through Synonyms and Views

12) Secutiry Features

a) Schemas and Users

b) Profiles

c) Password management

d) Systems Privileges

e) Object Priveileges

i) Roles

ii) Auditing

iii) Virtual Private Databases

13) ORACLE Optimizer

14) I/O Operations

15) Sort Operations

16) Backup and Recovery Features

ORACLE data are physically stored in datafiles. These datafiles are logically organized into certain tablespaces. Physical datafiles are managed by the operating system. Logical Objects are observable only from within the ORACLE environment. A tablespace serves as a link between the physical files and logical oracle objects. While creating tablespace one or more datafiles are explicitly allocated to that tablespaces. This implies that these physical areas are exclusively allocated to this tablespace and cannot be used for any other purpose. In other words, tablespace is simply a logical grouping of physical files.

SELECT TABLESPACE_NAME

FROM dba_tablespaces;

Will display a listing of available tablespaces in the target database. Some universal tablespaces in Oracle databases include SYSTEM for Oracle's meta data, a TEMP tablespace for temporary objects, and RBS for rollback segments.

The smallest definable physical area is a BLOCK. This block size is definable by the data base administrator in ORACLE. Smaller the database lower should be the value of a block. Typically used values range from 2K for a small databse to 16 K for a large database. If the block size is 16K, it means that each block can store a certain number of rows whose total size is 16K. From the performance standpoint it is more effective to put large number of rows in one block.

Another unit of storage in ORACLE is an EXTENT. An Extent consists of a given number of blocks. As the table grows, initial EXTENT of the table will first get used up. Oracle then allocates another extent for the table. The DBA can specify the size of the initial extent and all subsequent extents. These settings are specified when you create a tablespace. Tables will use the storage settings of the tablespace they are created in. If necessary once can override these tablespace settings by specifying different settings in the CREATE TABLE statement.

TABLESPACES

DATAFILES

EXTENTS

BLOCKS

Example ORACLE Commands

CREATE TABLE SPACE msis660_data

DATAFILE ‘E:\ORADATA\ msis660_data01.dbf’ SIZE 200M

DEFAULT STORAGE (INITIAL 1M

NEXT 1M

PCTINCREASE 0)

CREATE TABLE vijay_inventory

(partid CHAR(3) PRIMARY KEY,

qty_on_hand NUMBER(6,0),

price NUMBER(10,2)

TABLESPACE msis660_data

STORAGE (INITIAL 1M,

NEXT 1M,

PCTINCREASE 0));

Every oracle table contains the default SYSTEM tablespace. If no other specific tablespaces are created, all tables and indexes are created within this SYSTEM tablespace

Kinds of Tablespaces:

READ-ONLY Tablespace: Oracle allows the status of a tablespace to be set to ‘read-only.’ If so specified, ORACLE will not allow any changes to the underlying datafiles. This is a very useful setting for datawarehouse applications. Since datafiles in read-only tablespaces DO NOT change, there is no need to back them up periodically.

Transportable tablespaces are used to copy a set of tables from one Oracle database to another. Use of transportable tablespaces have practical usefull in many data warehouse applications. It can be used for feeding data from OLTP systems (operational databases) to data warehouse systems. It can also be also used to update data warehouses and data marts from staging systems, to loading data marts from central data warehouses.

Transportable tablespaces is a new feature of Oracle 8i. There are some limitations in using this method of replication. An important limitation is the requirement that the both the source and target databases must reside in the OS environment. The two databases mut also have the same block size.

The process of moving (copying) the tablespace is done is four steps.

Step 1: Pick a self-contained set of tablespaces.

It is not possible to transport tablspaces that have references from inside the tablespaces to an object ouside of the tablespace. Partitions and referential integrity constraints can also cause violations of the self-contained requirement if they refer to other objects outside the tablespace being transported.

Step 2: Generate a transportable tablespace set.

All tablespaces being copied must be placed in read-only mode. Second, Export is invoked to extract the metadata about the tables being copied for the tablespace set.

Step 3: Transport the tablespace set.

The export dump file and the datafiles are physically copied to the environment of the target database. This unix-level copy may be accomplished using file copy (if the target and source machines are physically connected) or through a network-based copy such as ftp or secure copy.

Step 4: Plug in the tablespace set.

Once the datafiles are placed in a location where the target database can access them Import is executed in Transport-tablespace mode to plug in the tablespace set. As mentioned, the target tablespace name must be the same name as was exported from the source database. User names do not have to be the same. The owners of the tables may be changed in the target database by using the Import FROMUSER/TOUSER parameters.

A transportable tablespace can contain the following objects: tables, index, bitmap indexes, index-organized tables, LOB's, nested tables, varrays and tables with user-defined columns.

OBJECT Behavior and Limitations:

Some objects do not behave normally when transferred using transportable tablespaces. The list is presented here with a short description of the exceptional behavior.

ROWIDs

When a database contains tablespaces that have been plugged in (from other databases), the ROWIDs in that database are no longer unique. A ROWID is guaranteed unique only within a table.

Advanced Queues

You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0-compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up via the built-in PL/SQL routine dbms_aqadm.start_queue().

Indexes

You can transport regular indexes and bitmap indexes. When the transportable set fully contains a partitioned table, you can also transport the global index of the partitioned table.

Function-based indexes and domain indexes are not supported. If they exist in a tablespace, you must drop them before you can transport the tablespace.

Triggers

Triggers are exported without a validity check. In other words, Oracle does not verify that the trigger refers only to objects within the transportable set. Invalid triggers will cause a compilation error during the subsequent import.

Snapshots/Replication

Transporting snapshot or replication structural information is not supported. If a table in the tablespace you want to transport is replicated, you must drop the replication structural information and convert the table into a normal table before you can transport the tablespace.

REFs

REFs are not checked when Oracle determines if a set of tablespaces is self-contained. As a result, a plugged-in tablespace may contain dangling REFs. Any query following dangling REFs returns a user error.

Privileges

Privileges are transported if you specify GRANTS=y during export. During import, some grants may fail. For example, the user being granted a certain right may not exist, or a role being granted a particular right may not exist.

Partitioned Tables

You cannot move a partitioned table via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces. You must ensure that all partitions in a table are in the tablespace set,

OTHER LIMITATIONS and CAUTIONS:

Advanced Queues

You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0-compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up via the built-in PL/SQL routine dbms_aqadm.start_queue().

DBA-level Authority

Execution of the Transportable Tablespace function requires DBA authority and ownership of the underlying data files.

New Feature

Transportable Tablespace is a new feature in Release 8i with the risk associated with all Version 1 products.

Upward Compatibility / Mixed Oracle Versions

Because Transportable Tablespaces is platform and operating-system dependent, there may be additional concerns compatibility across Oracle release levels. For example, will there be upward compatibility from Release 8.1.5 (Release 8i) and above? Or must the release levels be identical between the source and the target for Transportable Tablespaces?

Possible Applications

Transport tablespace could possibly be used for replication of data to create the ODS for some or all of the OLTP applications supporting DSS access.

Additionally, the transport tablespace approach could be used to quickly and efficiently stage Oracle tables for the manipulation required for building datagroupd.

Implementation

Using Transport Tablespace to create the ODS.

The advantage of transport tablespace is the speed of transferImplementation issues involve the organization of the tablespaces being replicated. An application area may use a single tablespace for data tables and a separate tablespace for indexes to those tables. In this example transport tablespace may or may not be feasible depending on the number of tables in the tablespaces that are not required in the DSS database. If on the other hand more tablespaces are used to contain the large number of application tables it may be more feasible to copy a set of tablespaces to the DSS environment even though all of the tables will not be used for an ODS or datagroups. The efficiency of transfer may compensate for any overhead occuring when un-needed tables are transferred.

Using Transport Tablespace to stage DSS data.

Because of the increased efficency of transfer, it may be desirable to copy sets of tables to a staging or assembly area in the DSS environment for subsequent manipulation and extraction. Using transportable tablespaces in this way may help quickly capture the OLTP data and permit asynchronous/parallel processing of that table data. This parallel processing of different groups of application tables may help reduce the pressure of the limited batch window.

Some or all of these transfers may be the equivalent of building the ODS for an application. The point of distinction here is that of asynchronous processing of the separate groups of tables.

Whether an ODS or a non-ODS group of application tables, the contents of the source tablespaces would be very important in using transportable tablespaces to stage table data for further processing. If many tables are held in few tablespaces, transferring tablespaces would require large disk-storage capabilites for the temporary staging of that data. If many of the tables in a tablespace are not needed to build the DSS objects then transferring tables at the tablespace level could be very inefficient with regard to disk space.

And finally, space allocation within each tablespace could be an issue. If some or many of the tablespaces are allocated with a significant amount of free space then transferring tablespaces could require a conisiderable amount of free space on the target DSS machine or node to accomodate the physical file transfer.

Evaluation

Transportable tablespaces could offer enhanced performance in transferring data between Oracle database environments. The value in this method will come more into focus as we better understand the schema organization, the number of tablespaces and their contents. The transportable tablespace method should be kept in mind when the application and DSS environment data modeling and database designs are carried out.

Recommendation

A careful effort should be made to document the performance difference between transportable tablespaces and other replication methods. It may be that other methods can be used in concert with transportable tablespaces to obtain a result that blends the efficiency of UNIX-level file transfer with the granularity of control that is possible when using Export/Import or other methods to replicate data at the table and schema levels.

Transportable tablespaces should be studied and tested as a means of replicating Oracle data out of the OLTP environment into the DSS environment. More needs to be known about the organization of the tablespace containers that will hold the application tables.

Documentation Source Material: Chapter 9: Managing Tablespaces Oracle8i Administrator's Guide Release 8.1.5 A67772-01

Locally Managed Tablespaces

Oracle has to store information regarding the extents for each tablespace somewhere. By default, this information is stored in the data dictionary (which is in the system tablespace). Therefore, whenever an extent is freed or allocated, the data dictionary has to be updated. However, Oracle 8i also provides locally managed tablespaces, where data regarding the extents is stored in a bitmap in the tablespace itself.

On a locally managed tablespace, one cannot specify the default storage settings. But there are two additional settings that can be used to simplify space allocation. If you specify AUTOALLOCATE, Oracle will automatically handle the sizing for objects. If you specify the UNIFORM SIZE clause, however, Oracle creates all extents in the locally managed tablespace using size you give it.

Here's an example of two tablespaces using the above options:

CREATE TABLESPACE msis660_data

DATAFILE 'e:\oracle\oradata\appl_data01.dbf' size 50M,

'e:\oracle\oradata\jlaxt2\ msis660_data.dbf' size 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

CREATE TABLESPACE msis660_large

DATAFILE 'e:\oracle\oradata\msis660_large.dbf' size 50M,

'e:\oracle\oradata\msis660_large.dbf' size 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Settings for a tablespace can be extracted using the following query:

SELECT TABLESPACE_NAME, INITIAL_EXTENT,NEXT_EXTENT,

MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,

EXTENT_MANAGEMENT,ALLOCATION_TYPE

FROM DBA_TABLESPACES

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download