Thursday, December 3, 2009

RMAN Recovery Scenario

SCENARIO: Server Crash, same server was used for catalog database. Now we have RMAN backup, DBID. Db files(ctl, bdf, tmp,log, spfile) and catalog db is not available.

How are we going to recover the database???

We'll assume that we are performing the restore & recovery on a new server having the same environment setup(OS, oracle software, etc) as the crashed server.

CONDITIONS:

For successful restore, the controlfile record keep time must suffice the backup set.

We must know the DBID.

We must have the RMAN backups and know the latest autobackup information.

STEPs:

Start RMAN without Catalog

rman target / nocatalog

RMAN> set dbid=1439101873

RMAN> startup nomount;

It will show error as spfile not found, along with starting instance with basic parameters. Now restore spfile & controlfile from autobackup, specify location as we do not have catalog database.

RMAN> restore spfile from '/ora100base/admin/mydb/rman/bkups/c-10330…';

RMAN> restore controlfile from '/ora100base/admin/mydb/rman/bkups/c-10330…';

RMAN> restore database;

RMAN> sql 'alter database mount';

RMAN> recover database noredo; (as we don't have the current redo logs)

RMAN> sql 'alter database open resetlogs';

Again for using catalog db just connect to catalog database and resync catalog.

rman connect / target catalog rman/rman@rcatdb

RMAN> resync catalog;

Wednesday, October 28, 2009

What is a Namespace


Namespace: A namespace defines a group of object types, within which all names must be uniquely identified—by schema and name. Objects in different namespaces can share the same name
Object types all sharing the same namespace:
Tables, Views, Sequences, Private synonyms, Stand-alone procedures, Stand-alone stored functions, Packages, Materialized views, User-defined types, etc.

Thus it is impossible to create a view with the same name as a table; at least, it
is impossible if they are in the same schema.

Object types having their own namespace:
Indexes, Constraints, Clusters, Database triggers, Private database links, Dimensions
Thus it is possible for an index to have the same name as a table, even within the
same schema.

Non schema objects with their own namespace:
User roles, Public synonyms, Public database links, Tablespaces, Profiles, etc.

Let's see an Example:

SQL> create table test (eno number);
Table created.

SQL> create index test on test(eno);
Index created.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER

SQL> create view test as select * from test;
create view test as select * from test
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Here, we see that I can create an index with the same name as of table but I can't create a view with the same name as of a table as they share the same namespace.

Tuesday, October 6, 2009

Codd's Rules for RDBMS

Codd's Rules for RDBMS

In 1985, Edgar Codd published a set of 13 rules which he defined as an evaluation scheme for a product which claimed to be a Relational DBMS.

Although these rules were later extended - and they now number in the 100s - they still serve as a foundation for a definition of a Relational DBMS.
(0) The foundation rule:

This is Codd's Rule Zero which is the foundation for the other rules.

The rules states that, any system which claims to be a relation database management system must be able to manage databases entirely through its relational capabilities and means that the RDBMS must support:

A data definition language

A data manipulation language

A data integrity language

A data control language

all of which must work on several records at a time, that is, on a relation.

(1) Information Rule:
All information in a relational database including table names column names are
represented explicitly by values in tables. Knowledge of only one language is necessary to
access all data such as description of the table and attribute definitions integrity
constraints action to be taken when constraints are violated and security information.

(2)Guaranteed Access Rule:
Every piece of data in the relational database can be accessed by using a
combination of a table name a primary key value that identifies the row and a column
that identifies the cell. The benefit of this is that user productivity is improved
since there is no need to resort to using physical pointers addresses. Provides
data independence.

(3)Systematic treatment of Nulls Rule:
The RDBMS handles, that have unknown or inapplicable values in a predefined fashion.
RDBMS distinguishes between zeros blanks and nulls in records and handles such values in
a consistent manner that produces correct answers comparisons and calculations.

(4)Active On-Line Catalog Based on the Relational Model:
The description of a database and its contents are database tables and therefore
can be queried online via the data language. The DBA's productivity is improved since
changes and additions to the catalog can be done with the same commands that are
used to access any other table. All queries and reports can be done as with other tables.

(5)Comprehensive Data Sub-language Rule:
A RDBMS may support several languages but at least one of them allows the user to do all
of the following: define tables view query and update data set integrity constraints
set authorization and define transactions.

(6)View Updating Rule:
Any view that is theoretically updatable if changes can be made to the tables that
effect the desired changes in the view. Data consistency is ensured since changes
in the underlying tables are transmitted to the view they support. Logical data
independence reduces maintenance cost.

(7)High Level Inserts Update and Delete:
The RDBMS supports insertion updation and deletion at a table level. With this the
RDBMS can improve performance by optimizing the path to be taken to execute the action
ease of use improved since commands act on set of records.

(8)Physical data Independence :
The execution of adhoc requests and application programs is not affected by changes
in the physical data access and storage methods. Database administrators can make
the changes to physical acccess and storage methods which improve performance
but do not changes in the application programs or adhoc requests. This reduces
maintenance costs.

(9)Logical data Independence:
Logical changes in tables and view such as adding/deleting columns or changing
field lengths do not necessitate modifications in application programs or in the
format of adhoc requests.

(10)Integrity Independence:
Like table/view definitions integrity constraints are stored in the
on-line catalog and therefore can be changed without necessitating changes
in application programs or in the format of adhoc requests .
The following two integrity constraints must be supported.
(a)Entity Integrity:
No component of primary key is allowed to have a null value.
(b)Referential integrity:
For each distinct non-null foreign key value in a relational database
there must exist a matching primary key from the same range of data value.

(11)Distribution Independence:
Application programs and adhoc requests are not affected by changes
in the distribution of the physical data.

(12)Non sub-version Rule:
If the RDBMS has a language change that accesses the information
of a record at a time this language cannot be used to by-pass the
integrity constraints. In order to adhere to this rule the RDBMS must have an
active catalog that contains the constraints and must have a logical data independence.

Wednesday, September 16, 2009

What is a Checkpoint

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk. It offers Oracle the means for ensuring the consistency of data modified by transactions. The mechanism of writing modified blocks on disk in Oracle is not synchronized with the commit of the corresponding transactions.
A checkpoint has two purposes:
(1) to establish data consistency, and
(2) enable faster database recovery.
The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Depending on the number of datafiles in a database, a checkpoint can be a highly resource intensive operation, since all datafile headers are frozen during the checkpoint. Frequent checkpoints will enable faster recovery, but can cause performance degradation

Key Initialization parameters related to Checkpoint performance.

  • FAST_START_MTTR_TARGET
  • LOG_CHECKPOINT_INTERVAL
  • LOG_CHECKPOINT_TIMEOUT
  • LOG_CHECKPOINTS_TO_ALERT
FAST_START_MTTR_TARGET: It enables you to specify the number of seconds the database takes to perform crash recovery
of a single instance. Based on internal statistics, incremental checkpoint automatically adjusts the checkpoint target to meet the requirement of FAST_START_MTTR_TARGET. V$INSTANCE_RECOVERY.ESTIMATED_MTTR shows the current estimated mean time to recover (MTTR) in seconds. This value is shown even if FAST_START_MTTR_TARGET is not specified.

LOG_CHECKPOINT_INTERVAL: It influences when a checkpoint occurs, which means careful attention should be given to the setting of this parameter, keeping it updated as the size of the redo log files is changed. The checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that if the system crashes, more time will be needed for the database to recover. Shorter checkpoint intervals mean that the database will recover more quickly, at the expense of increased resource utilization during the checkpoint operation

LOG_CHECKPOINT_TIMEOUT: The parameter specifies the maximum number of seconds the incremental checkpoint target should lag the current log tail. In another word, it specifies how long a dirty buffer in buffer cache can remain dirty. Checkpoint frequency impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that more time will be required during database recovery.

LOG_CHECKPOINTS_TO_ALERT: It lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

Relationship between Redologs and Checkpoint: A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint. Maintain well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches. The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, checkpoints occur.

Checkpoint not complete: This message in alert log indicates that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log. In this case, Oracle must wait until the checkpoint position passes that log.When the database waits on checkpoints,redo generation is stopped until the log switch is done. This situation may be encountered if DBWR writes
too slowly, or if a log switch happens before the log is completely full, or if log file sizes are too small.

Thursday, September 10, 2009

What information is Stored in Alert.log

Oracle's Alert Log [alert_sid.log/alert_(db_name).log] is a text file which can be opened with any text editor. The directory where it is found can be determined by the initialization parameter " background_dump_dest ", it records messages and errors that arise during the day to day database activities.
The messages in the alert log includes:

  • Information of Instance Startup and Shutdown
  • Errors causing generation of trace files.
  • Create, alter and drop SQL statements executed on database, tablespaces and rollback segments.
  • Errors arising during a materialized view is refreshed.
  • ORA-00600 internal errors.
  • Block Corruption (ORA-01578) Errors.
  • Dead Locks(ORA-00060) Errors.
  • Pointers to error generated trace files and dump files.

The alert log can be deleted and renamed if required for ex: if it reaches certain size, then you can delete or rename the alert log. Oracle will simply create a new alert log and start writing to it. Deleting the alert log or renaming won't affect the database functionality or performance.

Tuesday, September 8, 2009

Things a DBA must know

There are some things a good DBA must know.

1. This task? its easy, go to Google, DB Forums, etc.

But I know, however, that it cannot be a replacement for foundational knowledge of your chosen database server.
Sure, I can (and sometimes do) google the syntax for a command to get it right, but foundational knowledge is too important to be left to searches.

2. Continuing education is very Crucial, be aware of your technology.

This is in continue of point #1. Things change, even in the world of Oracle, so keep yourself up to date on the latest releases, patches, etc.

3. Monitoring is critical.

It doesn't matter which monitoring tool/system you use(OEM, Grid Control, third party tools, etc), just make sure you have one in place and it should be working! You should know when a server has too many connections, not enough memory, or if it's running out of disk space (or a hundred other things). Montoring is the base of tuning.

4. There is nothing more important than backups.

Performance tuning and building an awesome server are cool, but this is the bottom line: our data is sacred. Before anything else, you have to make regular backups and you have to make certain that those backups work properly. You must have a backup strategy, verify all the scheduled backups run successfully(by checking the backup logs) on a daily basis and perform test recoveries if needed to check the backup status.

5. Have fun at your job.

I hope you do what you do because it's your passion. It is for me, when I manage databases I am protecting client's most valuable resource and always learning something new. I get satisfaction from what I do. Because it matters a lot, whether you like your work or not.

Friday, September 4, 2009

Oracle Grid Control Architecture

Overview of Oracle Grid Architecture

The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.

Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Difference between a cluster and a grid

Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.

At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered - as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don't know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name, The Grid. This view of utility computing is, of course, a "client side" view.

From the "server side", or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

Thursday, September 3, 2009

DBA Activities

DBA BEST PRACTICES

One question that comes up frequently for new DBAs or under trainee DBAs are

1. what is my job?

2. when I take over a new system what should I do first?


This page gives you a slight review of dba best practices, advice on what to do in certain situations. Actions are on the left, commands on the right.

What should I do today?


1. Review the alert log

Check for any errors.

2. Determine if there is block level corruption

conn / as sysdba

SELECT * FROM v$database_block_corruption;

3. Verify the status of last night's backup

conn / as sysdba

SELECT * FROM v$backup_corruption;


4. Look for newly invalidated objects

conn / as sysdba

SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;

5. Are there any hung resumable sessions

SELECT user_id, session_id, status, suspend_time, error_number
FROM dba_resumable;


6. Are there any blocked sessions

SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee,
b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;

What should I do in a new environment/system?


1. Determine the actual version of Oracle

conn / as sysdba

SELECT * FROM v$version;

Also run the demos under DBMS_UTILITY.DB_VERSION and DBMS_UTILITY.PORT_STRING


2. Determine what components are installed

conn / as sysdba

col comp_name format a40

SELECT comp_name, version, status
FROM dba_registry;


3. Determine if there are any invalid objects in the DB

conn / as sysdba

SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;

4. Is the instance part of a RAC cluster

Run the demo code under DBMS_UTILITY.ACTIVE_INSTANCES


5. Is there Data Guard replication running?

conn / as sysdba

SELECT protection_mode, protection_level, remote_archive, database_role, dataguard_broker,
guard_status
FROM v$database;


6. Is the database in ARCHIVE LOG mode?

conn / as sysdba

SELECT log_mode FROM v$database;


6. Are FLASHBACK LOGS being collected?

conn / as sysdba

SELECT flashback_on FROM v$database;


7. Is there supplemental logging in place?

conn / as sysdba

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui,
supplemental_log_data_fk, supplemental_log_data_all
FROM v$database;


8. Where are the control files?

conn / as sysdba

SELECT name FROM v$controlfile;


9. Where are the log files?

conn / as sysdba

SELECT l.group#, lf.type, lf.member, l.bytes, l.status LOG_STATUS, lf.status LOGFILE_STATUS
FROM v$log l, v$logfile lf
WHERE l.group# = lf.group#
ORDER BY 1,3;


10. What are the initialization parameters?

conn / as sysdba

CREATE PFILE='/home/oracle/initparams.txt' FROM memory;


11. Capture database information

conn / as sysdba

SELECT dbid, name, open_mode, database_role, platform_name,
FROM v$instance;


12. Capture instance information

conn / as sysdba

SELECT instance_name, host_name, status, archiver, database_status, instance_role, active_state
FROM v$instance;


13. Are default passwords in use?

conn / as sysdba

SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
AND account_status = 'OPEN'
ORDER BY 2,1;

If default passwords are in either lock the accounts for change them:

ALTER USER <user_name> ACCOUNT LOCK;

ALTER USER <user_name> IDENTTIFIED BY <new_password>;


14. Is BLOCK CHANGE TRACKING enabled?

conn / as sysdba

SELECT filename, status, bytes
FROM v$block_change_tracking;

15. What features are being used?

Run the demo code under DBMS_FEATURE_USAGE_REPORT


16. What profiles exist and are in use?

conn / as sysdba

SELECT name, value
FROM gv$parameter
WHERE name = 'resource_limit';

SELECT profile, resource_name, limit
FROM dba_profiles
ORDER BY 1,2;

SELECT username, profile
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY 1;


17. Are case sensitive passwords enabled?

SELECT name, value
FROM gv$parameter
WHERE (name LIKE '%sensitive%');

18. Is FORCE LOGGING enabled?

SELECT force_logging FROM v$database;

19. Is ADVANCED REWRITE in use?

SELECT owner, name FROM dba_rewrite_equivalences;

Wednesday, September 2, 2009

Oracle Initialization Parameters

Initialization Parameters: Oracle designed its database products to be flexible and configurable on a variety of hardware platforms and they succeeded. At present Oracle software can run on more than 70 platforms, from mainframes to home PC networks to PDAs. The secret of its success lies in the software initilization parameters. These parameters can be set and tune for top performance in countless environments. On the other side improper setting and can slow down and even halt the system.
Oracle has now divided the initialization parameters into two groups basic and advanced. In majority of cases, it is necessary to tune only the basic parameters of which there are 20 to 30 to get reasonable performance from the database. In rare cases modification of advanced parameters may required.
Below are the Basic Parameters in alphabetical order with their default value underlined and their breif description.
1. CLUSTER_DATABASE (TRUE/FALSE) boolean: It is a real application cluster RAC parameter that specifies whether or not RAC is enabled.
2. COMPATIBLE(9.2.0 to Default release) integer: It allows you to use a new release at the same time guaranteeing backward compatibility with an earlier release.
3. CONTROL_FILES(OS dependent , 1 to 8 filenames) string: Every database has a controlfile which contains entries that describe the structure of the database. We can specify one or more filenames seperated with a comma.
4. DB_BLOCK_SIZE(8192, 2048 to 32768 depending on OS) integer: It determines the size of the blocks at the time of creation of a database. The value of this parameter must be a multiple of the phyisical block size at device level.
5. DB_CREATE_FILE_DEST(no default value, directory) string: Specifies the default location for Oracle Managed datafiles and controlfiles.
6. DB_CREATE_ONLINE_LOG_DEST_n( no default value, directory) string: Specified the default location of oracle manged controlfiles and redo log files, where n=1, 2, 3, ....5.
7. DB_DOMAIN(no default value, any legal string of name components) string: In a distributed database system, this parameter specifies the logical location of the database within the network structure.
8. DB_NAME(no default value, any legal string) string:It specifies the database identifier of up to 8 characters. It must be specified and must correspond to the name specified in the create database statement.
9. DB_RECOVERY_FILE_DEST(no default value, directory) string: It specifies the default locatiton for flash recovery area(FRA). FRA contains multiplexed copies of contro files, redo logs, archived logs, flashback logs and RMAN backup.
10. DB_RECOVERY_FILE_DEST_SIZE(no default value, bytes)Big integer: It specifies the hard limit on the total space to be used by target database recover files created in flash recovery area.
11. DB_UNIQUE_NAME(db_name/+ASM, any legal string) string: It specifies a globally unique name for the database. Databases with the same db_name within the same db_domain must have a unique db_unique_name.
12. INSTANCE_NUMBER(lowest available no./0, 1-max instances specified during db creation stmt) Integer: It is a RAC parameter that can be specified in parallel mode or exclusive mode.
13. JOB_QUEUE_PROCESSES(0, 0 to 1000) Integer: It specifies the maximum number of processes that can be created for the execution of jobs.
14. LOG_ARCHIVE_DEST_n(no default value, any legal string) string: It is the attribute to specify where to archive redo data, where (n=1, 2, 3...10) destinations.
15. LOG_ARCHIVE_DEST_STATE_n(enable, alternate, reset, defer, enable) string: It specifies the availability state of the corresponding destination, where (n=1, 2, 3...10) for the LOG_ARCHIVE_DEST_n destination parameters.
16. NLS_LANGUAGE(OS dependent, language) string: It specifies the default language of the database.
17. NLS_TERRITORY(OS dependent, territory) string: It specified the name of the territory whose conventions are to be followed for day and week numbering.
18. OPEN_CURSORS(50, 0 to 65535) integer: It specifies the maximum number of open cursors(handles to private SQL areas) a session can have at once.
19. PGA_AGGREGATE_TARGET(10M or 20% of the size of SGA, whichever is greater, 10m-4096gb-1) big integer: It specifies the target aggregate PGA memory available to all server processes attached to the instance.
20. PROCESSES(40 to OS dependent, 6 to OS dependent) integer: It specifies the maximum number of OS user processes that can simultaneously connect to Oracle, including backgroun processes.
21. REMOTE_LISTENER(no default value, any legal string) string: It specifies a network name that resolves to an address or address list of Oracle Net remote listeners(i.e. listeners that are not running on the same machine as this instance).
22. REMOTE_LOGIN_PASSWORD_FILE(shared, none/shared/exclusive) string: It specifies whether oracle checks for a password file.
23. ROLLBACK_SEGMENTS(if not specified, instance uses public rbs, unless undo_management is set to auto) string: Allocates one or more rollback segments by name to this instance.
24. SESSIONS({derived :1.1*PROCESSES)+5} ) It specifies max no. of sessions that can be created in the system. The value should be your estimate of the max no. of concurrent users, plus no. of the backgroun processes, plus approximately 10% recursive sessions.
25. SGA_TARGET(0, OS dependent) big integer: It specifies the total size of all SGA components. If it is specified then following memory parameters are automatically sized.
a. Buffer Cache(DB_CACHE_SIZE)
b Shared Pool( SHARED_POOL_SIZE)
c Large Pool(LARGE_POOL_SIZE)
d Java Pool (JAVE_POOL_SIZE)
e Streams Pool(STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero then that value is used as min leves by ASMM.
26. SHARED_SERVERS(0, value should be less than max_shared_servers) integer: It specifies the number of server processes that you want to create when an instance is started.
27. UNDO_MANAGEMENT(manual, manual/auto) string: It specifies which undo space management mode the system should use.
28. UNDO_TABLESPACE(the first available undo tbs in the db, undo tbs name) string: It specifies the undo tablespace to be used when an instance starts up.

Tuesday, September 1, 2009

Oracle Database Structures


The Relational Data Model has three major aspects
1. Structures
2. Operations
3. Integrity Rules
Structures: These are well defined objects (tables) that store data of a database. Structure and the data within them can be manipulated by operations.
Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations must adhere to a pre-defined set of integrity rules.
Integrity Rules: Integrity rules are the laws that govern which operations are allowed on the data and structure of a database. Integrity rules protect data and the structure of a database.
An Oracle Database has both Logical and Physical Structure. (Fig#1 for reference)
Logical Database Structure: An Oracle database's logical structure is determined by one or more tablespaces, the database's schema objects (ex. tables, views, indexes, clusters, sequences, stored procedures, etc) The logical structures including tablespaces, segments, extents and blocks dictates how the physical space of a database is used. A tablespace can be associated with only one database.
Physical Database Structure: An Oracle database's physical structure is determinded by datafiles. One or more datafiles are grouped together to make a tablespace. A datafile can be associated with only one tablespace.
Although databases, tablespaces, datafiles and segments are closely related they have important difference. The differences are categorized below.
1. Databases & Tablespaces: An Oracle Database comprises of one or more logical storage untis called tablespaces. The database's data is collectively stored in the database's tablespaces.
2. Tablespaces & Datafiles: In Oracle Database each tablespace comprises of one or more physical operating system files called Datafiles. A tablespace's datafile physically stores the associated database data on disk.
3. Databases & Datafiles: A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
The units of logical database allocations are data blocks, extents and segments.
(Fig#2 for reference)
1. Data Blocks: At the finest level of granularity, an Oracle Database's data is stored in data blocks, also known as logical blocks, oracle blocks, or pages.
2. Extents: An extent is a specific number of contigious data blocks that are allocated for storing a specific type of information.
3. Segments: A segment is a set of extents which have been allocated to for a specific type of data structure, and all are stored in the same tablespace. i.e. a segment can be associated with only one tablespace, though it can be spread over one of more datafiles.

Wednesday, August 26, 2009

Oracle Server Architecture


An Oracle Server consists of an Oracle Database and an Oracle Instance. Everytime a database is started a System Global Area (SGA) is allocated in the memory and Oracle Background processes are started. The combination of background processes and SGA is called an Oracle Instance.
System Global Area (SGA) is a shared memory region that contains data and control information for an Oracle Instance. Oracle allocates the SGA when an Instance is started and deallocates it when the instance is shutdown. Users currently connected to an Oracle Server share the data in SGA. For optimal performance the entire SGA should be as large as possible, still it should fit in Real Memory(RAM), to minimize the Disk I/O.
Components of SGA:
1. Database Buffer Cache
2. Redo Log Buffer
3. Shared Pool
4. Large Pool
5. Java Pool

1> Database Buffer Cache: Database buffers store the most recently used blocks data. The set of database buffers in an Instance is the Database Buffer Cache. It holds the modified and unmodified blocks. The most recently used data is kept in memory, less Disk I/O is necessary and it improves the performance. Managed through (LRU) Least Recently Used algorithm.
2> Redo Log Buffer: It stores redo entries ( a log of changes made to the data). The redo entries stored in the redo log buffers are written to an Online redo log, which comes in use for database recovery. The size of the redo log is static.
3> Shared Pool: The shared pool portion of the SGA contains two major components. a)Library Cache & b) Data Dictionary Cache.
a) Library Cache: It consists of two structures, Shared SQL Area & Shared PL/SQL Area. It stores information about the most recently used SQL & PL/SQL statements. Is managed by (LRU) Least Recently Used Algortihm.
b) Data Dictionary Cache: It holds the information about the most recently used definitions in the database. During the parse phase, the server process looks at the data dictionary cache for information to resolve object names and validate access.
4> Large Pool: It is an optional area in the SGA, which can be configured to relieve the burden placed on the shared pool. It is used for Session memory (UGA) for the shared server, backup and restoration operations or RMAN. It does not use LRU algorithm.
5> Java Pool: Services parsing requirements for Java commands. It is required if installing and using Java.

PGA: Program Global Area is a memory buffer that contains data and control information for a server process. A PGA is created by oracle when a server process is started.

Process Structure
Oracle take advantage of various types of processes.
User Process: Started at the time a database user requests connection to the Oracle Server. It is a program that requests interaction with the Oracle Server. Must first establish a connection.
Server Process: Connects to the Oracle Instance and allocated when a user establishes a session. It directly interacts with the Oracle Server, it can be a dedicated or shared server. Fulfills calls generated and return results.
Background Processes: Started when an Oracle Instance is started. They are responsible for maintaining relationships between physical and memory structures.
Mandatory Background Processes: PMON, SMON, DBWn, LGWR & CKPT.
Optional Background Processes: ARCn, RECO, CJQ0, LCKn, etc.
PMON(Process Monitor): Performs process recovery when a user process fails. It is responsible for rolling back failed transactions, releasing locks, other resources and restarting dead dispatchers.
SMON(System Monitor): Perform Instance recovery when a failed instance starts up again. It is responsible to roll forward changes in redo logs, rollback uncommitted transactions, coalesces free space, deallocates temporary segments and open database for user access.
DBWn(Database Writer): It writes modified blocks from the database buffer cache to the datafiles. It writes when the checkpoint occurs, dirty buffers reach threshold, timeout occurs, there are no free buffers, etc.
LGWR(Log Writer): It writes redo log entries from redo log buffer in the SGA to the current redo log file on disk. It writes at commit, when the redo log buffer is one third 1/3 full, when there is 1 MB of redo, before DBWn writes and every three seconds.
CKPT(Checkpoint): It is responsible for signalling DBWn at checkpoints and updating all datafiles header and controlfiles with the checkpoint information.
ARCn(Archiver): It is an optional Background process, responisble for automatically archiving online redo logs when a log switch occurs. The database must be in archivelog mode.
RECO(Recoverer): It is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database.