Vikrant's Oracle Blog

Manually Creating Oracle Database

2009-06-28T22:30:00.000-07:00

hello all,

As we have learn how to install oracle on RHEL-5, one might be interested in creating oracle database. There are two procedure through which you can create oracle database if you have oracle software installed.

For creating oracle database on windows, first install oracle binaries (software) from CD to your system. then follow these steps to create database manually. you can create database with the help of 'Database Configuration Assistant' (DBCA) a tool provided by oracle to create database and installed when you install oracle software and it requires no extra license.

Before you start creating database please make sure that following are done:-

1. Make yourself very familiar with oracle initialization parameter.
2. Consider oracle managed file for creating your database.
3. Plan you operating system and partitions.
4. Make sure sufficient disk space exist in your system.
5. Make sure you have sufficient memory to start oracle instance.
6. Decide your table,index structure for your oracle database.

Once these basic needs are fulfilled proceed to oracle database specific requirements.

A. Decide your oracle instance identifier.
B. Authenticate your operating system account with administrator privileges.
C. Create the password file using 'orpwd' utility on command prompt to create password file for your database.

c:\> orapwd file= password= entries=
where,

file =complete path and name of password file
password = your password for sysdba
entries = maximum number of DBA's

example:- orapwd file=d:\oracle\product\database\orclpwd.ora password=password entries=10

D. create initialization parameter file (pfile), if you can't create one then copy from sample. It should be in form init$oracle_sid.ora. It is generally located in $ORACLE_HOME/database. Below is the sample init.ora file

# Cache and I/O
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520

# Cursors and Library Cache
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300

# Diagnostics and Statistics
BACKGROUND_DUMP_DEST=D:\oracle\admin\mynewdb\bdump
CORE_DUMP_DEST=D;\oracle\admin\mynewdb\cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=D:\oracle\admin\mynewdb\udump

# Control File Configuration
CONTROL_FILES=("D:\oracle\oradata\mynewdb\control01.ctl",
               "D:\oracle\oradata\mynewdb\control02.ctl",
               "D:\oracle\oradata\mynewdb\control03.ctl")

# Archive
LOG_ARCHIVE_DEST_1='LOCATION=D;\oracle\oradata\mynewdb\archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
#               "(PROTOCOL=TCPS)(PRE=oracle.aurora.server)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
            "(PROTOCOL=TCP)(PRE=oracle.aurora.server)",
             (PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=mynewdb

# Distributed, Replication and Snapshot
DB_DOMAIN=us.oracle.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

# Network Registration
INSTANCE_NAME=mynewdb

# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800

# Processes and Sessions
PROCESSES=150

# Redo Log and Recovery
FAST_START_MTTR_TARGET=300

# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes
SORT_AREA_SIZE=524288

# Automatic Undo Management
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undotbs

E. Check whether all directories mentioned in the init.ora file exists.
F. Create oracle instance if on windows with following command. We assume that our SID is "orcl"

c:\>oradim -new -sid orcl -intpwd password -startmode a

Note:- if you have created password file then this command will overrite that password file. so better create password file with this option.

G. Now connect to the instance we have created in previous step

c:\> set oracle_sid=orcl
c:\>sqlplus/nolog
connect sys as sysdba
password: password
sql>

H. Now you are on sql prompt. start the oracle instance in nomount state.

sql> startup nomount;

I. Now run the create database script as follows;-

create database orcl
user sys identified by password
user system identified by password
logfile group1('d:\oracle\oradata\redo01.log') size 100m,
group2('d:\oracle\oradata\redo02.log') size 100m,
group3('d:\oracle\oradata\redo03.log') size 100m,
maxlogfiles 5
maxlogmembers 6
maxloghistory 1
maxdatafiles 100
maxinstances 1
character set US7ASCII
national character set AL16UTF16
datafile 'd:\oracle\oradata\system01.dbf' size 1024m reuse,
'd:\oracle\oradata\users01.dbf' size 2048m reuse,
'd:\oracle\oradata\tools01.dbf' size 300m reuse,
'd:\oracle\oradata\index01.dbf' size 500m reuse,
extent management local
dafault temporary tablespace temp
datafile 'd:\oracle\oradata\temp01.dbf' size 1024m reuse,
undo tablespace unotbs
datafile 'd:\oracle\oradata\undo01.dbf' size 1024m reuse,
archivelog;

With above script database is created with:-
1.database nam orcl
2.sys and system password is password.
3. maxlog files specify the limit for log group.
4.maxlog members specify the limit for maximum members to be created in one group.
5.maxinstance specify that onle one instance can have this database open and mounted.
6. three log group having one member each.
7. four datafile along with system and users.
8.temporary tablespac temp
9.undo tablespace undotbs.

J.Once you have done with this database creatin task create data dictiobary views by running ctalog and catproc.sql scripts by opening database.

sql>@d:\oracle\rdbms\admin\catalog.sql;
sql>@d:\oracle\rdbms\admin\catproc.sql;

K.Then create server parameter file by command

sql> create spfile from pfile='d:\oracle\admin\orcl\pfile\initorcl.ora';

the spfile will be created in dfault location 'd:\oracle\database\spfileorcl.ora' if you do not specify location for spfile.

sql> shut immediate;
sql> startup;

this time your database will be opened with spfile you have creatd in last step.

This way you database is created manually...

Note:- change paths accordingly.

Recovery Catalog Creation

2009-03-31T04:19:00.000-07:00

hello guys,

Today we are going to discuss the step-by-step procedure to create recovery catalog for your database.

This discussion describes how to create an RMAN recovery catalog, which holds RMAN repository data for one or more databases in a separate database schema, in addition to using the control files of the databases.

If you are creating your recovery catalog in an already-existing database, add enough room to hold the default tablespace to the recovery catalog schema. If you are creating a new database to hold your recovery catalog, then, in addition to the space for the recovery catalog schema itself, you must allow space for other files in the recovery catalog database:

SYSTEM tablespace
Temporary tablespaces
Rollback segment tablespaces
Online redo log files

Assume that we are having our primary database MKTG3, and other database RCAT on which we are going to crate recovery catalog.

You should not install the catalog in the target database: this tactic defeats the purpose of the catalog. Also, decide whether to operate the catalog database in ARCHIVELOG mode, which is recommended.

the process of creating recovery catalog owner is as follows:-

After choosing the recovery catalog database and creating necessary space, you are ready to create the owner of the recovery catalog and grant this user necessary privileges.

To create the recovery catalog schema in the recovery catalog database:

1) connect to the database with administrator privilages containing the recovery catalog:

conn sys/admin@rcat

2) create a user and schema for recovery catalog

create user recovery identified by catalog
temporary tablespace
default tablespace
quota unlimited on ;

3) grant the recovery_catalog_owner role to schema owner.

sql> grant recovery_catalog_owner to recovery;

4)make necessary changes to tnsnames.ora and listener.ora files and configure listeners if you are using multiple listener to proivde services to this standby database.

5) grant RECOVRY TYPE privilages to recovery catalog owner

sql> grant create type to recovery;

6) connect to the database that will contain catalog,as catalog owner

% rman catalog recovery/catalog@rcat

you can also connect from RMAN prompt

RMAN> connect catalog recovery/catalog@rcat

7) run the CREATE CATALOG command to create catalog.

RMAN> create catalog;

8) Optionally, start SQL*Plus and query the recovery catalog to see which tables were created:

sql> SELECT TABLE_NAME FROM USER_TABLES;

Registering a Database in the Recovery Catalog:-

The first step in using a recovery catalog with a target database is registering the database in the recovery catalog. Use the following procedure:

1)after making sure the recovery catalog database is open,connect RMAN to the target database.

RMAN> connect target sys/admin@mktg3

2) to register to recovery catalog the target database must be mounted or open.

3) then connect to the recovery catalog

RMAN> connect catalog recovery/catalog@rcat

4) Register the target database in the connected recovery catalog:

RMAN> register database;

RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.

Verify that the registration was successful by running REPORT SCHEMA:

RMAN> REPORT SCHEMA;

These are the steps to crate recovery catalog... Hope this will help you in using RMAN more efficiently.

Oracle Control Files

2009-03-19T04:05:00.000-07:00

Hi all,

Every person has a friend, a very good friend. who knows everything about that person. and this friend is very crucial to that person.same way oracle control file is also very crucial to oracle database. It is needed to mount the database.

Oracle control file stores the complete information about the physical structure of database and and it's status. oracle control file contains following information about oracle database, but not limited to it:

Database information (RESETLOGS SCN and their time stamp)
Archive log history
Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
Redo threads (current online redo log)
Database's creation date
database name
current archive log mode
Log records (sequence numbers, SCN range in each log)
RMAN catalog
Database block corruption information
Database ID, which is unique to each DB

This way control file contain contain almost all information about oracle database. The control file must be available for writing by the oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file is created at the time of database creation on the location specified by initialization parameter file. It is recommended that you should multiplex your control file on same host or another machine, because you might need them in case of recovery.

To backup the control file in running instance, execute following command as sys user:

SQL> alter database backup controlfile to trace;

or if you want to backup controlfile in user readable format then execute

SQL> alter database backup controlfile to '';

you can multiplex control files with operating system copy command, but for this purpose you have to shut down your database. Once the controlfile are multiplexed, all we be synchronized by oracle if some changes are made to database. follow the following steps to multiplex the control files on you oracle host:

1.shut down database.
2. copy existing control files to the multiplexing location with operating system copy command. ('copy' in windows and 'cp' in Linux).
3. make entries in initialization parameter file, edit the control_files parameter specifying path newly multiplexed control files.
4. recreate server parameter (sp file) file from newly edited parameter file.
5. open the database.
6. check with view v$controlfile, whether multiplexed controlfile are there in list.

This way you can multiplexed your control files. If lost in worst situation you can recreate your control file for disaster recovery. following script will explain how to recreate your control file.

startup nomount
create controlfile reuse database 'dbname' noresetlogs noarchivelog/archivelog
maxlogfiles 30
maxlogmembers 5
maxdatafiles 30
maxinstances 1
maxloghistory 500
logfile
group 1 '/uo1/oracle/dbs/redo01.log' size 100m,
group 2 'u01/oracle/dbs/redo02.log' size 100m
datafile
'/u01/oracle/dbs/system01.dbf',
'/u01/oracle/dbs/tools01.dbf',
'/u01/oracle/dbs/index01.dbf',
'/u01/oracle/dbs/users01.dbf',
'/u01/oracle/dbs/undotbs01.dbf',
'/u01/oracle/dbs/fin01.dbf',
add temporary tablespace temp
tempfile ''/u01/oracle/dbs/temp01.dbf',
character set WE8DEC
;

This way you can create your control file in needed. but always backup and multiplex control files for safe reasons.

Installing Oracle 10g R2 on RHEL 5

2009-03-05T22:03:00.000-08:00

Hi guyes,

This article will discuss the installation process to Install Oracle Database 10g R2 on Red Hat Enterprise Linux 5 (RHEL5).

This article is based on server installation, with SE linux disabled and following group packages installaed:

* GNOME Desktop Environment
* Editors
* Graphical Internet
* Text-based Internet
* Development Libraries
* Development Tools
* Legacy Software Development
* Server Configuration Tools
* Administration Tools
* Base
* Legacy Software Support
* System Tools
* X Window System

The following steps instruct you to install oracle and set kernel parameter required to work oracle safely.

Download database software:- Download database software for oracle 10g R2 from OTN or Metalink.

Unzip the files in a directory

tar -zxvf 10201_database_linux.tar.gz

Say we are unzipping this tar file in /temp/oracle directory.

Edit the /etc/host file by adding following lines with specifications

Example:- 192.168.0.200 dbserver.test.com dbserver

Set kernel Parameters:- By adding following lines in /etc/sysctl.conf file

***********************************************************************************************************************************************

#kernel.shmall = 2097152

#kernel.shmmax = 2147483648

kernel.shmmni = 4096

#semaphores: semmsl, semmns, semopm, semmni

kernel.sem = 250 32000 100 128

#fs.file_max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

***********************************************************************************************************************************************

As the kernel parameters are set, run the following command to change the current parameters:

/sbin/sysctl -p

Edit the /etc/security/limits.conf file by adding following lines to it:

* soft nproc 2047

* hard nproc 16384

* soft nofile 1024

* hard nofile 65536

Add the following lines to /etc/pam.d/login file, if it does not already exist

session required /lib/security/pam_limits.so

Disable SELINUX by editing flag as follows in /etc/selinux/config file,

SELINUX=disable

Now all the prerequisite are set. We are going to start a database installation, but before that check whether following packages are installed or not:

To check whether package is installed execute following command as root:

rpm -qa | grep

This will give you the output and package version and name if installed. If package version is lower that what we need, then insert media of RHEL 5 and execute following command as root:

rpm -Uvh

List Of Packages:
binutils
setarch-2*
make-3*
glibc-2*
libaio-2*
compat-libstdc++-33-3*
compat-gcc-34-3*
compat-gcc-34-c++-3*
gcc-4*
libxp-1*
openmotif-2*
compat-db-4*
control-center
glibc-common
gnome-libs
libstdc++-devel
pdksh

As the all packages are installed, create the new groups and users:

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba oracle
passwd oracle

With this command enter new password for oracle.

Now create the directories where oracle software will be installed

mkdir -p /u01/product/server/db_1
chown -R oracle.oinstall /u01

In this way we created directories and make oracle the owner.

Now login as root user and issue this command

xhost +

Edit the /etc/redhat-release file replacing the current release information (Red Hat Enterprise Linux Server 5 (Tikanga)) with following:

redhat-4

Now login as oracle user and ad the following lines to /home/.bash_profile file at the end

*********************************************************************************************************************************************

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/product; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/server/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
*********************************************************************************************************************************************

Now execute following command

DISPLAY=

:0.0; export DISPLAY

By navigating to oracle bib directory in $ORACLE_HOME execute

./runinstaller

Hope this will help you to install oracle on Linux system.....

Oracle Datafiles

2009-02-24T02:10:00.000-08:00

It's not at all easy to learn a technology completely, but we will try to learn oracle as we can. Let us see oracle datafiles, their structure, how to manage them and how they work.

Datafiles are physical structure of oracle database. Oracle stores your important data in these datafiles. These are located on hard disk in directory $ORACLE_HOME/oradata/SID along with others important files like redo log files, controlfiles etc.

One or more physical datafiles make a tablespace. It means a datafile can have only one tablespace, but a tablespace can have more than one datafiles. Oracle creates a datafile by allocating sufficient amount of disk space plus the overhead required for the file header.

When the datafile is created, the associated disk space is formatted, but does not contain any user data. Oracle reserve this space to use it in future.

When you create database with DBCA, oracle allocates first datafile to system tablespace as it is most important tablespace in database. We can explicitly allocate datafiles to tablespace as and hen we want. Have a look on tablespace and datafiles structure in oracle:

The data related to related schema in tablespace is physically stored in datafiles. Schema object does not corresponds to a specific datafile, but a datafile is repository for the schema objects. A datafile can be allocated to multiple schema, but it is not necessary that a schema object (say table) will reside only in that datafile as shown in above example.

Datafiles contain first block as it's header, which contains information about space allocated, space available, blocks present etc. in datafile. It also contain other important information such as block size, tablespace, file size, timestamps etc. When database opens, oracle checks whether information in datafile header matches with that of control file. When user request some data, oracle checks it first in buffer cache in SGA and if it is not present there then oracle reads it from appropriate datafile. CKPT process writes in datafile header the current SCN and log sequence number.

You can check information about datafiles by using v$datafile. You can take datafiles offline any time for you purpose. For this purpose execute following command:

sql> alter database datafile 'data/file/path.dbf' offline;

and online by executing following command

sql> alter database datafile 'data/file/path.dbf' online;

but remember, when taking datafile online it may ask for media recovery, then you can do

sql>alter database recover datafile 'data/file/path.dbf';

That's all what we can discuss about datafiles now. I am sure as we go on, we ill definitly get to know more about them.

enjoy...

SGA Memory Structures

2009-02-17T22:31:00.000-08:00

We are learning oracle SGA since last couple of posts, and now we are very much into it. Let's have discussion on memory structures of oracle SGA(System Global Area).

As we discussed that oracle SGA is consist of several memory structures and background processes. We discussed background processes in last post, let's now have look at memory structures of SGA.

SGA is consist of following memory structures:
1. Database buffer cache
2. Shared pool
3. Redo log buffer cache
4. Java pool
5. Large pool
6. Streams pool

1. Database Buffer Cache:

The database buffer cache is that region of SGA which holds the copy of data blocks read from datafiles. The buffer cache is shared among all connected user of database. When user try to retrieve data from database, server process read it first from datafiles and place a copy of recently used data blocks in database buffer cache. It is managed by least recently used (LRU) algorithm.Data updation is done in this area. There are three types of buffer in buffer cache:

A. Dirty buffer:- Dirty buffer are those which are changed or modified, and need to be written to the disk. This task is done by DBWR, and these dirty buffer are flushed out to datafiles.

B. Free buffers:- Free buffers are those which do not contain any data and ready to use. When server process fetch any data from datafiles, it si stored in free buffers.

C. Pinned buffers:- Pinned buffer are those which are currently being used or explicitly retained for future used.

2. Shared Pool:

The shared pool contains most recently used SQL and PL/SQL statements and data definition, data dictionary, character set information. It is further divided into:

A. Library cache: The library cache includes the shared SQL areas, private SQL areas,PL/SQL procedures and packages, and control structures such as locks and library cache handles. The shared SQL area contains recently executed SQL commands and their execution plans and parse tree where private SQL area contains values for bind variables and run time buffers.

PL/SQL program units are processed the same way as SQL statements by Oracle. When a PL/SQL program unit is executed, the code is moved to the shared PL/SQL area while the individual SQL commands within the program unit are moved to the shared SQL area. Again, the shared program units are maintained in memory with an LRU algorithm.

B. Data Dictionary Cache:

The data dictionary cache is collection of database tables and views containing metadata about database, its structure, its privileges and its users. Oracle access the data dictionary during the parsing of SQL statements. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers(which hold entire blocks of data).

3. Redo Log Buffer Cache:

The redo log buffer cache is circular buffer in SGA which holds information about changes made to database. It contains the redo entries which contains the information to redo the changes made to database in case of failure. Changes are made to the database through any of these commands:INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP.

4. Java Pool:

The java pool services parsing requirements for java commands. It is required if installing and using java.

5. Large Pool:

The Large pool is a voluntary area in the SGA that can be configured by the database administrator to provide large memory allocations for specific database operations such as an Oracle backup or restore. The large pool allows Oracle to request large memory allocations from a separate pool to prevent contention from other applications for the same memory. The large pool does not have an LRU list.

6. Streams Pool:

The streams pool is used when you are using oracle streams. It is used on capture activity. In prior releases of oracle it is assigned 10% from shared pool, which often faces overflow.

Other than this some memory structures are still there, they are as follows:-

1. Program Global Area:

This is another memory structure of Oracle.

(PGA) is the area in the memory that contains the data and process information for one process. This area is non-shared memory. The PGA size is fixed and is dependent on the operating system. The contents of the PGA vary depending on server configuration.

The memory allocated to hold variables, arrays, and other information that belongs to the session is called stake space.PGA is allocated when the process is completed. Unlike the SGA that is shared by several processes, the PGA provides sort space, session information, stack space, and cursor information for a single server process.

2.Sort Area Size:

The memory area that Oracle uses to sort data is known as the sort area, which uses memory from the PGA for a dedicated server connection. For multi threaded server configurations, the sort area is allocated from the SGA. Sort area size can grow depending on the need. The parameter SORT_AREA_RETAINED_SIZE determines the size to which the sort area is reduced after the sort operation. The memory released from the sort area is kept with the server process; it is not released to the operating system.

Oracle Background Processes

2009-02-16T22:40:00.000-08:00

Hi folks, actually i was bit busy since last couple of days, so was unable to post on blog.

As post name suggest, we are going to discuss background process that drives oracle for its optimum performance. As the name suggest, these are processes which runs behind the scene, but are the main operating part of oracle database.

Let's have look at them in detail.

As we have discuss, when instance is allocated at RAM (physical memory) means SGA is created. This SGA has some memory structures and background processes. Although memory processes and background processes are interdependent, they holds their own importance too. Some of important background processes and their functions are described in this post.

First we will discuss all the basic BG processes, which are known and most important.

1. PMON:

The process monitor recovers failed process resources. It checks if any user process fails, it cleans up all resources that user process has acquired,and roll back uncommitted transaction and releasing locks. In shared server environment, PMON restarts any failed dispatcher or server process.

2. SMON:

The system monitor recovers after instance crash. It performs crash recovery when crashed instance is start again. It monitors and clean temporary extents. It also merges contiguous areas of free space in datafile, this process is called as coalescing. In RAC in non-failed instance, SMON performs instance recovery of other failed instance.

3. DBWR:

The database writer process writes dirty blocks from database buffer cache to datafiles. Dirty blocks are those blocks which are modified by server process, and need to be flushed out to make room for new blocks in cache.DBWR never ever modify database buffer cache, it is done by server process. DBWR writes to datafiles when space is needed in cache, on commit and when forceful checkpoint is occurred. You can configure more than one DBWR process up to 10.

4. LGWR:

The log writer process writes redo log buffer entries (redo entries) into online redo log files on disk. This is done by LGWR when transaction is committed, every 3 seconds and when DBWR signals LGWR to do so.

5. CKPT:

The checkpoint process regularly initiates a checkpoint, and updates controlfile and datafile header. This process flushes the redo entries by means of LGWR. Uses DBWR to write all dirty buffer to datafiles and updates datafile header.

6. ARCH:

The archive process is optional process. If your database is in archive log mode and automating archiving is enabled, then this process is active. This process writes filled redo log files to archive log location specified by you. It is required while recovering database. LGWR is responsible for starting multiple ARCH process if workload increase. There can be up to 10 archiver processes.

7. MMAN:

The MMAN dynamically adjust the size of SGA components. It is new process added to oracle 10g a a part of "Automatic Shared Memory Management".

These are the basic background processes in oracle SGA. Other than this some background processes are:-

CQJ0,FMON,Pnnn etc.

We will see them in detail later.

Oracle Physical Structure

2009-02-04T23:03:00.000-08:00

hi friends,

In last topic we discussed about basic oracle architecture. In this discussion we will discuss physical structure of oracle database. But before that, we have to learn the logical architecture which is involved in physical structure.

confused....???? let's see.

Oracle physical structure which include logical structure is as follows:-

Logical Structures

Oracle database is divided into smaller logical units to manage, store, and retrieve data efficiently. The logical units are tablespace, segment, extent, and data block. The following figure will explain it more clearly. Have a look:

Tablespace

Tablespace is a logical structure which holds other logical structure of database. Tablespace is further broken into segments. segment is further broken into extents. This way tablespace holds the segments , extents and datafiles for a particular purpose. Like we have system tablespace, users tablespace, index tablespace and etc., All tablespace segments and extents for that particular purpose. Tablespace can be named, as we seen in above example.

Segments

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are Temporary Segment and Rollback Segment.

Extents

A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes. the database block consist of multiple operating system blocks.

Now lets have a look at actual physical structure of oracle database. It is a structure which holds your actual data. It consist of some files, which resides on disk. These files are datafiles, control files, redo log files and password file.

Datafiles

Datafiles holds actual data of oracle database.A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace, but one tablespace can has more than one datafiles. When ever user request data, oracle retrieve that data from these datafiles with the help of some processes in the SGA (System Global Area). And also manipulated data is written back to these datafiles, so that changed data is available to all users.

Redo Log files

Redo log files are the files that store the redo entries generated by DML statements. It can be used for recovery processes. These files also stores data, but contain only changes made to database. These changes are useful when recovering database. These file can be archived, that is can be copied offline for recovery purpose. this process is called archiving. These files belongs to certain group. We will discuss redo log files in more details later.

Control Files

Control files are used to store information about physical structure of database, such as datafiles size and location, redo log files location, etc. Control files are very crucial for database operation.
An Oracle database cannot be started without at least one control file.It contains (but is not limited to) the following types of information:

* Database information (RESETLOGS SCN and their time stamp)
* Archive log history
* Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
* Redo threads (current online redo log)
* Database's creation date
* database name
* current archive log mode
* Log records (sequence numbers, SCN range in each log)
* RMAN catalog
* Database block corruption information
* Database ID, which is unique to each DB

Hope you have got a bit of information about from these blogs. We will definitely discuss all structures in details in our future topics.

Oracle Basic Architecture

2009-02-03T21:40:00.000-08:00

Hi all,

As an oracle DBA you must have the very sound knowledge of oracle instance and its architecture.you should be able to differentiate between logical and physical layer of database.

In this article we will discuss the basic architecture of oracle database, what is an instance, what are physical components and logical components of oracle database.

oracle database is consist of two main components
1. Oracle Instance (virtual)
2. Oracle files (physical)

Oracle Instance:- It is an virtual component that is mounted on your system's physical memory (RAM) when oracle services starts. It occupies some space on your RAM and it is the component of the database which deals with the physical layer of oracle database. The following figure shows all virtual components of Instance, lets see them and discuss about them:-

As we have discussed that Instance is virtual and is allocated on our system's physical memory, one may ask 'what is instance made up of?', the answer to this question is, instance is made up of 'System Global Area (SGA)' and 'Background Processes', which communicate with physical files of database to serve request made by user.

It is recommended by oracle that 50%-70% memory should be given to SGA. We will discuss how to allocate size to SGA.Now SGA is consist of following components:

1. Shared Pool:-This is area of SGA which is divided into two parts, one is library cache which stores information about commonly used SQL and PL/SQL statements. means again if the same statement is issued by another user then oracle would not require to access it from datafile (discussed later) and can prevent lot of hard work. It is maintained by Least Recently Used algorithm, means the statement which are not used frequently are thrown out of library cache.

The other part is called as dictionary cache which holds information about definition of objects in database, indexes, users and their privileges, authentication information so that no invalid user can access database etc.

2.Database Buffer Cache:-Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Just don't think about other buffer pool components specified in the figure, we will discus them later.

3. Redo Log Buffer Cache:-Each DML statement (insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the database physical structure. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

you will get complete idea about redo log file in our next discussion 'Oracle Physical Components'.

4. Large Pool:-Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

5. Java Pool:-As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

Now as we have discussed the SGA components, let us now switch Background process which work behind the scene in instance, in cooperation with SGA:-

1. DBWn:-Database writer (DBWn) process is used to write data from buffer cache into the datafiles (in next discussion). Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

2. LGWR:- Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer into the redo log files.

3. CKPT:- Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

4. SMON:- System Monitor (SMON) process is used to recover the system crach or instance failure by applying the entries in the redo log files to the datafiles.

5. PMON:- Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

So, in this article we discussed the basic virtual (logical) components of oracle database and instance. Physical structure of database is some thing which is easy to understand and main component . So let us see it in next discussion.

Your comments are always welcome.

Few Things To Remember For Oracle Students

2009-02-02T21:29:00.000-08:00

Hi all,

In this article i am going to discuss my own experience and what i was taught when i was an oracle student.

Many of us always confuse on 'How to study oracle?', 'How to prepare for oracle online exams?' and lot more question about oracle. Many people on oracle forum ask how oracle can be studied?

Look oracle is a very interesting technology and practical too. If you have joined oracle training program then they will teach you all the theory part, how oracle works, what are the components of oracle and let you practice on their system. But is it sufficient? practicing a topic of oracle one in a day may make you perfect in that topic, but is it sufficient to be a good DBA? answers might vary.

But according to me, along with practicing oracle topic with theory, you should practice previous or old topic as well. This will keep you connected with your total progress. Following are some tips which are discussed by experts like Aman sharma, Pavan kumar, Satish kandi, and many more on oracle forum for oracle beginners. Lets have a look:-

1. As an oracle student you should have enough knowledge about RDBMS and oracle corporation and its product. Its not in your OCP curriculum, but its a good gesture to have knowledge about the company, whose product you are going to use.

2. First of all if you have a system at your home, install oracle database on it and start practicing. If you don't have one, then you have to spend some extra hours at your institute.

3. Try to learn how oracle services start and stop on windows. You should have a bit knowledge about operating system process for this. But best suggestion is try to learn oracle on Linux platform. I heard that all institute are providing training on Linux platform now a days, which is a good thing.

4. Don't hesitate to ask, if you stuck on something. Please remember there is always a senior/expert to ask in IT sector.

5. Oracle has some components which are virtual, so be sure to identify them clearly. You should have very good understanding of oracle instance in order to understand oracle internals.

6. Read a lot of documents as you can. I am very bad at this. But still it is recommended by experts to keep reading. Everything is documented in oracle, you jut have to Google it.

7. Last but not the least, DBA is a very responsible job to do. Do not expect to get job as you complete you certification. Keep practicing on you machine, do what worst can happen to your database and try to recover it. Treat your home PC as a production database and you will find you are getting real world experience.

hope this helps you to be a good oracle student.

Oracle Certifications

2009-02-01T23:07:00.000-08:00

Many students who are interested in making career in oracle, ask me ' how we can get certified?' ,'what is importance of certification?', 'what are the steps to get certified?'

Look, learning oracle means to learn how oracle works, its internals, its component, how we can get optimum from our system, how to resolve user queries and many more...

Lets have a detailed look on above topics,

1. Oracle corporation offers oracle certification. if you clear these exams then you can be appointed as oracle DBA, database analyst, backup admin etc.,

2. Very popular certification which is also a minimum requirement for perusing career in oracle and to get job as 'Database Administrator' is 'Oracle Certified Professional (OCP)'. OCP's have a deep knowledge of oracle working.

3. To get certified you must complete as hands on course for oracle certification examination. Find for prometric centers in your city on oracle website.

4. There are 4 papers which you have to complete to acquire OCP certification.
A) Introduction to oracle
B) Fundamental I

After completing these two papers you are called as 'Oracle Certified Associate (OCA). In this duration you learn how to use SQL language to retrieve, manipulate, delete and insert data in oracle database. In second paper you learn the architecture of oracle database, what are the components of oracle database, how they are work, etc.,

C) Fundamentals II
D) Performance Tuning

During these season you will learn backup and recovery strategies for oracle database (we will discus them in coming topics). and in last paper you will learn how to tun your database for best performance, means how to size database components for their optimum use.

there are other certifications too, which hold very high level in oracle community like Oracle Certified Master (OCM), RAC administrator etc. you can find information about all of them on following site:

http://education.oracle.com/

Hey, don't get scared....oracle is not a devil, by this time you have a very little idea what is oracle and there are some component that runs it.

As we discover oracle in our future topic you will become more clear. and i know you are little confuse also by this time. don't worry you can post comments and let me know what you want to know, i will try to post it as soon as possible.

what Is Oracle?

2009-02-01T21:54:00.000-08:00

I always wonder when some people as me 'what is oracle?' not necessarily these people are from It field, but oracle is definitely not an UFO for all of us.

Besides having dictionary meaning ' a shrine where an oracular god is consulted' and 'an authoritative person who divines the future' oracle holds lot of meaning associated with it. For us oracle is a RDBMS (Relational Database Management System). Oracle is an information company. Oracle provides database solutions. rather, explaining it in simple words, 'oracle is a database which holds all your data, tables and if you want images too'.

Oracle database is #1 database with many features. Larry Ellison and his friends started Software Development Laboratories (SDL) in 1977 and rename their company as oracle, based on their CIA-funded project having code name 'Oracle'.

Oracle is a database system which holds your data as you want. like an organization may use it to save their employee information, Id's, salary and lots more information. A vendor may use it for storing information about his dealers.

Like any other excel sheet oracle uses tables and columns to store data, and unlike any other sheet it allows you to see only a particular row about particular thing like employee, dealer etc.,

Oracle database use SQL as its manipulation language. you can edit, insert, update and modify your data with this command language.

For large scale operations oracle is considered as most scalable, consistent and firm database than any other database in market.

this is what we called very basic about oracle, we can discuss here, as name of this blog is basic oracle. in upcoming post we see how oracle database works, its architecture and its component.