Installation Of oracle 11g r2 on Oracle Linux 7

Friday, July 20, 2018

ROW CHAINING AND ROW MIGRATION

HOW TABLE DATA IS STORED

When a table is created, a segment is created to hold its data
A tablespace contains a collection of segments.
A row is ultimately stored in a database block in the form of a row piece.

ROW CHAINING:

Normally, we encounter row chaining when the size of the row is larger than the size of the database block used for storing it.
In this situation, the row is split across more than one database block.
When this row needs to be accessed, more than one database blocks are traversed, resulting in greater I/O.
To avoid row chaining we can create larger size data block table space.

Non-default data block tablespace creation:

Before we start we need to alter an initialization parameter. Assuming the default block size is 8096 KB.

SQL>ALTER SYSTEM SET DB_16K_CACHE_SIZE=16M SCOPE=BOTH;

We can understand row chaining by following steps:

STEP 1

Create a new table ROW_CHAIN

SQL> CREATE TABLE SCOTT.ROWS_CHAIN
(
Id number not null primary key,
Field1 char(2000) default 'A' not null,
Field2 char(2000) default 'B' not null,
Field3 char(2000) default 'C' not null,
Field4 char(2000) default 'D' not null,

);

STEP 2

INSERT VALUES IN TABLE

INSERT INTO SCOTT.ROW_CHAIN (ID) SELECT ROWNUM FROM SYS.DBA_OBJECTS WHERE ROWNUM<101;

STEP 3

GATHER STATISTICS FOR THE TABLE

SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 4

CHECK IF ROW CHAINING IS THERE

SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

CHAIN_CNT
----------

100

STEP 5

CREATE A TABLESPACE WITH DIFFERENT BLOCK SIZE

SQL> CREATE TABLESPACE TS_16K BLOCKSIZE 16K DATAFILE 'TS_16K.DBF' SIZE 30M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

STEP 6

MOVE TABLE ROW_CHAIN IN NEWLY CREATED TABLESPACE

SQL> ALTER TABLE SCOTT.ROW_CHAIN MOVE TABLESPACE TS_16K;

STEP 7

REBUILD INDEX OF TABLE ROW_CHAIN

SQL> ALTER INDEX SCOTT.PK_ROW_CHAIN REBUILD;

STEP 8

NOW REFRESH THE TABLE STATISTICS

SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 9

NOW CHECK FOR THE ROW CHAINING

SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

CHAIN_CNT

----------

TO CHECK ROW CHAINING IN ANY TABLE

SQL> SELECT owner, table_name, NVL(chain_cnt,0) "Chained Rows" FROM all_tables WHERE owner NOT IN ('SYS','SYSTEM') AND NVL(chain_cnt,0) > ORDER BY owner, table_name;

ROW MIGRATION

In row migration, Oracle Database moves the entire row to a new data block, because in block there is not enough size to fit in the block after updation the entire row will migrated to another block and and the original row piece of a migrated row contains a pointer of forwarding address to the new block containing the migrated row. The rowid of migrated row does not change.

Row migration is depends on the pct_used and pct_free of oracle block.

When varchar2 datatype's value will be updated and if there is insufficient space is available in pct_free then row chaining will takes place.

Avoid Row Chaining

To avoid row migration we can move table in another tablespace.

Monday, July 2, 2018

Managing Oracle database instance

Starting Up an Oracle Database Instance

A normal database operation means that an instance is started and database is mounted and open.

Oracle database instance has 3 phase of start up

SQL> startup nomount

An instance is typically started only in nomount mode during database creation, during re creation of control file, or during certain backup and recovery scenarios.

Starting an instance includes the following tasks:

Searching ORACLE_HOME/dbs for a parameter file of a particular name the sequence.
search for spfile<SID>.ora
If spfile<SID>.ora not found, search for spfile.ora
If spfile.ora is not found, search for init<SID>.ora

Allocating the SGA

Starting the background processes

Opening the alert_<SID>.log file and trace files.

This command starts the instance and the database is not mounted.

SQL> startup mount

Associating a database with a previously started instance.
Locating and opening all control files specified in the parameter file.
Reading control files to obtain the names and statuses of the data files and online redo log files.
However, no checks are performed to verify the existence of the data files and online redo log files at this time.

We can open database in mount mode to perform certain maintenance operations.

Renaming datafiles

Enabling and disabling online redo log file archive operations

Performing full database recovery

SQL> startup

Opening database includes following tasks:

Opening the data files

Opening the online redo log files

If any of above file is not present when you attempt to open the database, it will returns an error.

Sequence of command in case we open it in mount or no mount state.

SQL> startup nomount
SQL> alter database mount
SQL> alter database open

Using srvctl starting database

$srvctl start database -d orcl -o mount

During this final stage, the oracle server verifies that all data files and online redo log files can be opened, and checks the consistency of the database. If requires SMON background process will perform Instance Recovery.

Note:when the database is enabled with ORACLE RESTART, the srvctl utility can be used to start oracle database instance, the advantage is it can also start all required dependent resources such as asm instance, asm disk group and listener. srvctl utility is located in both the $ORACLE_HOME/bin directory for grid infrastructure and for the oracle database software.

SHUTTING DOWN ORACLE DATABASE INSTANCE

There is four modes of shutting down oracle database instace

SQL> shutdown abort;

Current SQL statements being processed by the Oracle server are immediately terminated.
The Oracle server does not wait for users who are currently connected to the database to disconnect.
Database and redo buffers are not written to disk.
Uncommitted transactions are not rolled back.
The instance is terminated without closing the files.
The database is not closed or mounted.
The next startup requires instance recovery, which occurs automatically.

SQL> shutdown Immediate;

Current SQL statements being processed by the oracle database are not completed.

the oracle server does not wait for the users who are currently connected to the database to disconnect.

The oracle server rolls back active transactions and disconnects all connected users.

The Oracle server closes and dismounts the database before shutting down the instance.

The next startup does not require an instance recovery.

NOTE: IMMEDIATE is the default shutdown mode when using OEM.

SQL> shutdown transactional;

It will allow existing transactions to finish,(It will wait till user commit the transaction or rollback the pending transaction) but not starting new transactions.

No client can start a new transaction on this particular instance.

When all transactions have been completed, a shutdown occurs immediately.

The next startup does not require an instance recovery.

SQL> shutdown;(normal)

It will waits for all sessions to disconnect.

No new connections can be made.

Database and redo buffers are written to disk.

Background processes are terminated and the SGA is removed from memory.

The Oracle server closes and dismounts the database before shutting down the instance.

The next startup does not require an instance recovery.

$srvctl stop database -d orcl -o abort

If we think about time that is taken to perform shutdown abort is fastest and normal is the slowest.

Thank You,

Tuesday, June 26, 2018

Oracle 11g database architecture

ORACLE 11G DATABASE ARCHITECTURE

ORACLE SERVER:

It is simply a machine where oracle server software is installed and an oracle database is residing.

PURPOSE OF ORACLE SERVER:

It manages large amount of data without affecting performance of the database.
It provides concurrent access in a multi user environment.
It prevents unauthorized access by providing basic security of password.
It provides bundle of recovery solutions in case of failure.

ORACLE SERVER CONSISTS OF:

ORACLE INSTANCE
ORACLE DATABASE

ORACLE INSTANCE:

It is gateway to access any of the database.
It opens one and only one database at a time

It consists of:

SGA (Memory Structure of oracle) is allocated memory from physical memory(RAM) of server.
Background processes are started in background.

ORACLE DATABASE:

It is collection of data which is treated as a single unit.

It consists of:

Physical components : made up of operating system's physical files.
Logical components

PHYSICAL COMPONENTS:

They are made up of operating system's physical files like

Data Files (1) (.dbf)
Online Redolog Files (2) (.log)
Control Files (1) (.ctl)
Password File
Parameter File (.ora)
Archive Files (.arc)
Trace Files (.trc)
Backup Files

Out of all the files above first 3 files (DFs, ORDLFs,CFs) are mandatory files. That is loss of any of this file results into database crash. Hence this files are known as mandatory DATABASE FILES.

Where as rest of all the files are not mandatory but required for better functionality of the database, that is loss of any of this file doesn't result into database crash .Hence this files are known as NON-DATABASE FILES.

DATA FILES:

This files contains actual data of the database. This files are in binary format.

ONLINE REDO LOG FILES:

It records changes done to the database by means of DML and DDL statements.
Purpose of this file is INSTANCE RECOVERY.

CONTROL FILES:

It records physical structure of database. (for eg: database name,timestamp of database creation, names & location of data files and redolog files, etc....)
Purpose of this file is to maintain integrity and consistency of the database.

Parameter file:

It contains list of non default initialization parameters. This is first file to be read while startup.
Default location of this file is : $ORACLE_HOME/dbs

Password file:

It contains password of user having SYSDBA or SYSOPER privileges. (not of scott,hr,etc...)
Default location of this file is : $ORACLE_HOME/dbs

Archive files:

This are offline copies of online redolog files and are created only if database is in archivelog mode.
By default database is always is noarchivelog mode.
Purpose of this files is MEDIA RECOVERY.
Default location of this file is FLASH or FAST_RECOVERY_AREA

Trace files:

This files are diagnostic files which records errors diagnosed by user process or background process.
Three types of trace files are there :

user trace file
background trace files
alert_SID.log file

Default location of this file is specified by DIAGONISTIC_DEST parameter($ORACLE_BASE/diag/rdbms/'dbname'/'SID'/trace)

Backup files:

This are physical files holding of the data.

LOGICAL COMPONENTS:

The first logical division of the database is called tablespace. Each and every database is made up of one or more than one tablespace. From oracle 10g 2 tablespaces: SYSTEM and SYSAUX are mandatory.
Each and every tablespace is made up of one or more than one segments. Segments are nothing but database objects like tables,views,Packages,Procedures, etc...
Each and every segment is made up of one or more than one extents. Extents are set of contiguous oracle blocks.
Oracle Block is multiple of operating system block.
It is defined by parameter DB_BLOCK_SIZE and its possible values are 2k or 4k or 8k or 16k or 32k
Its default value is 8k
This parameter is defined while database creation. Once it is configured and database is created then this parameter cannot be altered. Hence it is known as STANDARD DATABASE BLOCK SIZE.
Oracle block is defined at finest level of granularity and it is minimum unit of I/O.

ORACLE INSTANCE:

MEMORY STRUCTURES (SGA) SYSTEM GLOBAL AREA:

SGA is allocated memory at instance startup and deallocated at instance shutdown.
It is Sized by parameter SGA_MAX_SIZE which is a static parameter
But components of SGA are dynamic.

Components are:

STREAMS POOL - sized by parameter STREAMS_POOL_SIZE
JAVA POOL - sized by parameter JAVA_POOL_SIZE
LARGE POOL - sized by LARGE_POOL_SIZE
DATABASE BUFFER CACHE - sized by DB_CACHE_SIZE
REDO LOG BUFFER - sized by LOG_BUFFER
SHARED POOL - sized by SHARED_POOL_SIZE

Sum of memory allocated to all this components should be less than or equal to SGA_MAX_SIZE
Out of all this components last three i.e DBBC,RDLB,SP are mandatory whereas first three are optional memory structures to be configured only if required.
Upto 9i all above components where to be configured manually by their parameters.
From 10g one new parameter called SGA_TARGET is introduced. If this parameter is configured then no need to configure all above components separately, they all(except log buffer) will be allocated memory from SGA_TARGET. That means if SGA_TARGET is set to a non-zero value then AUTOMATIC SHARED MEMORY MANAGEMENT (ASMM) feature is enabled.
SGA_TARGET is dynamic and can have maximum value upto SGA_MAX_SIZE parameter.
DBBC : It records data blocks read from data files because of any SQL statement
RDLB : It records changes done to database by means of DML and DDL statements. (i.e. DML/DDL statement, SCN,timestamp etc.)
SP : It records most recently executed SQL/PLSQL statements and Most recently used data definitions.
SP is further classified into : LIBRARY CACHE and DATA DICTIONARY CACHE
LC : Records SQL/PLSQL statments
DDC : Records Data definitions

LRU ALGORITHM:

DBBC,DDC,LC are regulated by LRU algorithm. According to this algorithm memory structure has two ends:

MRU END (Most Recently Used) and LRU END (Least Recently Used). All data blocks or statements enters from middle of the memory , If that data blocks or statements are repeatedly reused it moves towards MRU END and if not repeatedly reused it moves towards LRU END. Once the memroy ids full then last statement at LRU END ages out making free space for new coming entries.

USER PROCESS,SERVER PROCESS,CONNECTION,SESSION:

First of all user starts client machine then starts an aplication for eg: SQL*PLUS, then writes down connection request(i.e username/password) , at that time USER PROCESS is created on client machine.

Now this user process along with connection request will go to server via network, at server end a process called SERVER PROCESS is created for this user process. Now this server process will work on behalf of user process, so as soon as user is validated by oracle server CONNECTION will be established between USER PROCESS and ORACLE SERVER and SESSION will be started. Session is completed only when user logs out normally or abnormally.

DEDICATED AND SHARED MODE:

When per user process there is one dedicated server process it is said that database is in DEDICATED SERVER MODE. By default database is in DEDICATED MODE. Through out the session user process remains connected to same server process.

In Shared Mode, all user processes are served by few server processes.

DRL DML DDL statements Processing:

DRL : When ever a DRL (SELECT) statement is executed for first time it under goes four phases:

Parse
Bind
Execute
Fetch

In PARSE phase first of all it checks for syntax, if syntax is correct it checks for semantics( Object validation,user validation,table,column,etc) if this is done it generates a compiled code for the statement known as PARSE CODE for this statement.

In BIND phase it obtains values of bind variables if referred in the statement.

In EXECUTE phase , oracle optimizer(inbuilt function) invokes which generates best execution plan for the statement.

In FETCH stage it fetches the rows and returns to user process.

DML: It under goes only three phases:

Parse
Bind
Execute

DDL : It under goes two phases:

Parse
Execute

CHECKPOINT EVENT:

Say suppose any of the user fires a DML statement (for eg: update emp set sal=10000 where deptno=10; ) then

pre + post images are generated and written to DBBC as well as RDLB.

After sometime say suppose user fires COMMIT at that time FAST COMMIT MECHANISM takes place under which 3 things are happening :

POST SCN (system change or commit number) is generated and written to RDLB
PRE Image is removed from DBBC only.
LGWR (Logwriter) background process is invoked and starts writing all redo entries from RDLB to RDLF's

Now LGWR writes sequentially so as soon as RDLF-1 gets full it switches to RDLF-2, this event is called LOG SWITCH under which 2 things are happening simultaneously:

LSN (Log Sequence Number) is generated.
Checkpoint event is fired in background.

Under checkpoint Event 2 things are happening:

Ckpt (checkpoint) background process invokes which generates ckpt no.
Updates checkpoint information in Control Files and header of all Datafiles.

As soon as this task is over it is said that Checkpoint of RDLF-1 is over and hence it signals DBWn(Database Writer) background process to flush DIRTY BUFFERS( Buffers holding post images) from DBBC to Data Files.

Finally committed data is saved to datafiles.

While checkpoint of RDLF-1 was going on, LGWR was writing to RDLF-2 so as soon as RDLF-2 gets full it switches back to RDLF-1 but starts over writing only when checkpoint of RDLF-1 is over.

BACKGROUND PROCESSES:

There are bundle of background processes out of which five background processes are mandatory.

LGWR (Log Writer)
CKPT (Checkpoint)
DBWn (Database Writer)
PMON (Process Monitor)
SMON (Sytem Monitor)

LGWR:

Task of this process is to write down all redo entries from RDLB to RDLF

Situations under which this process invokes:

At commit.
Every 3 Seconds.
1Mb of redo data generated
one-third of RDLB full
Atleast before DBWn writes.

CKPT:

Task of this process is to perform checkpoint( i.e. to generate ckptno and update checkpoint info in CFs and header of all DFs)

Situations under which this process invokes:

At every Log Switch automatically.
As requested by DBA manually:

Alter system switch logfile;
Alter system checkpoint;

Whenever database is shutdown with normal,immediate or transactional mode.
Whenever tablespace is taken offline,read only or in begin backup mode.

DBWn:

n stands for 0 to 9 ,by default only one process DBW0 is running.

Task of this process is to flush dirty buffers from DBBC to datafiles.

Situations under which this process invokes:

At completion of checkpoint as signalled by ckpt process.
Time out occurs (Fast_start_mttr_target parameter)
Buffers reached threshold or No free buffers left
RAC ping request made
Whenever database is shutdown with normal,immediate or transactional mode.
Whenever tablespace is taken offline,read only or in begin backup mode.

PMON:

Task of this process is to cleanup after USER PROCESS failure.

It dynamically registers database services to listner.
It releases all the resources which were in use.
It releases all the DML locks which were held.
It restarts dead dispatchers.

SMON:

Task of this process is to perform INSTANCE RECOVERY

Phases of instance recovery are:

It Rolls forward all data from RDLF's to Datafiles.
It opens the database.
Rollback uncommitted changes from datafile by reading undo segments.

ARCHIVER BACKGROUND PROCESS (ARCn):

This is an optional background process but very important background process when database is in

ARCHIVELOG MODE.

Task of this process is to generate offline copies of online redo log files.

It invokes at every log switch and generates an archive file
Its default destination is $ORACLE_BASE/fast_recovery_area
Upto 10 destinations can be defined for this files with parameter log_archive_dest_*

PROGRAM GLOBAL AREA (PGA):

This is another memory area of oracle which is allocated memory whenever a server process is created or background process is started. And deallocation whenever server process or background process is terminated.

As PGA is private per server process per background process it is also known as PRIVATE GLOBAL AREA.

It consists of UGA( Users Global area) and Stack Space.

And UGA consists of SORT AREA,CURSOR STATE,SESSION INFORMATION.

SORT AREA: This memory area is used whenever query contains any of sorting keywords like ORDER BY,GROUP BY etc..

CURSOR STATE: This memory area is run time area for DRL statements.

SESSION INFORMATION: This memory area holds information about session like session id,values of bind variables,.

STACK SPACE: This memory area is run time area for DDL/DML statements.

Whenever database is in dedicated mode UGA and stack space both are allocated memory from PGA

Whenever database is in shared mode UGA is allocated memory from SGA (SP or LP) and stack space is allocate from PGA.

The parameter to size pga is PGA_AGREGGATE_TARGET .

Database Creation in Oracle 11g in Linux

Pre-requisite for database creation

oracle database software must be installed.(Check my blog for installation)
at least one listener should be there.

Listener creation using netca

netca's location is $ORACLE_HOME/bin directory.

[oracle@pal bin]$ netca

network configuration assistance window will open.

Step 1: Select Listener configuration

Step 2: Select Add>Next

Step 3:Enter name of Listener (you can give any name, chose appropriate name)

I am giving name as Listener

Step 4: Select TCP protocol

Step 5: Enter port no for Listener, default port number is 1521.

Listener will be created using netca.

Listener creation using netmgr

netmgr's location is $ORACLE_HOME/bin directory.

[oracle@pal bin]$ netmgr

Oracle Net Manager's window will open.

Step 1: go to Local>listeners

Step 2: click on + on left panel.

Step 3: Select Listening Location from drop down list on top.
Click on add address from bottom.

select protocol as TCP/IP

Enter Host Name

Enter Port no

Step 4: Select Database Services from drop down list on top.
Click on add database.

Enter name of Database (orcl)

Enter path of oracle_home (/u01/app/oracle/product/11.2.0/dbhome_1)

Enter name of oracle_sid (orcl)

Save Network setting and exit

Manually listener creation

By default, the listener.ora file is located in the
$ORACLE_HOME/network/admin directory on UNIX operating systems and the ORACLE_HOME\network\admin directory on Windows.

Open listener.ora file in text editor

[oracle@pal ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@pal admin]$ vi listener.ora
[oracle@pal admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle

Standalone database creation using DBCA.

DBCA's location is $ORACLE_HOME/bin directory.

[oracle@pal ~]$ dbca

Step 1: Select create database>click next

Step 2: Select general purpose or transaction processing>click next

Step3: Enter global database name orcl
Enter SID orcl>click next

Step 4: on top select enterprise manager and If you want to manage database using oracle enterprise manager enable the check box of configure enterprise manager.

On top select automatic Maintenance task and enable check box.

Step 5: Select use the same administration password for all accounts and set the password (you can set different password).>click next

Step 6: select storage type to file system

select storage location to use database file locations from template>click next

Step 7: enable specify flash recovery area

enter path of flash recovery area (/u01/app/oracle/flash_recovery_area)

enter sized of flash recovery area (4096 oracle recommended, you can add more size as per your need).

enable archiving for generate archive log files.

Step 8: If you want you can enable sample schema, it will give you some basic data to perform certain operations or you can use it as test database.

Step 9:

from top select memory

select Typical enter memory size 40% of ram size

enable use automatic memory management

from top select sizing

enter block size 8192

set processes as per your need(150)

from top select character sets

select use the defaults

from top select connection mode

chose dedicated server mode

Step 10: click next

Step 11: click finish

You can unlock schemas from password management or you can do it later as per your need.

Set Oracle SID

[oracle@pal ~]$ . oraenv

ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@pal ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 25 22:45:33 2018

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

Thank You.

installation of oracle database 11g r2 (11.2) on Linux

Installing Oracle Linux On Virtual Box manager

I am installing Oracle Linux on virtual box manager, you can download it from https://www.virtualbox.org/wiki/Downloads

I am using here Oracle Linux 7.4, download it from http://iso.linuxquestions.org/oracle/oracle-linux-7.4/#oraclelinux-r7-u4-server-x86_64-dvd

After installation of Virtual Box Manager, Click on New on left side.

New pop box will display, Name: OEL7, choose type to linux, version to Oracle(64 bit) click on next.
chose hard disk file type to VDI( Virtual Disk Image) click on next.
storage on physical hard disk chose to dynamically allocated( it will save storage while not using vmbox)
File Location choose as you want, size of 70 GB click on create.

now you can see OEL7, right click on it and go for settings.

on left panel go to motherboard

select base memory (RAM size 2 GB recommended)

go for storage>controller

select optical drive: give path of Oracle linux.iso image path

go to network

enable network adapter
select bridged to attached to

click ok

click on show on top panel, it will start installation of Oracle linux 7

select Istalling media
select language click next
set date and time
set keyboard
don't touch installation source( we have already selected it)
software selection> select all the packages
Installation destination>

A 500 MB partition for an ext4 file system that contains /boot
A partition in the remaining disk space configured an LVM physical volume for an LVM volume group that contains Root(/)
A logical volume for a swap partition swap size should be double than RAM 4 GB

disable kdump
click on done.

it will start installing Oracle Linux.

set password for root user.

after installing all packages go for reboot.

accept license information and go for done.

it will start oracle linux

Installation of Oracle 11g r2 database

This steps should be performed as root user.

If you plan to use the "oracle-validated" package to perform all your prerequisite setup, follow the instructions at http://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.

# yum install oracle-validated

All necessary prerequisites will be performed automatically.

It is probably worth doing a full update as well, but this is not strictly necessary.

# yum update

open terminal and perform following steps

[root@pal ~]# groupadd -g 501 dba

[root@pal ~]# grep dba /etc/group
dba:x:501:

[root@pal ~]# useradd -g dba -u 101 oracle
[root@pal ~]# grep oracle /etc/passwd
oracle:x:101:501::/home/oracle:/bin/bash

[root@pal ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

[root@pal ~]# mkdir -p /u01/app/oracle

[root@pal ~]# ls -ld /u01
drwxr-xr-x 3 root root 4096 Dec 19 13:49 /u01

[root@pal ~]# chown -R oracle:dba /u01

[root@pal ~]# ls -ld /u01
drwxr-xr-x 3 oracle dba 4096 Dec 19 13:49 /u01

[root@pal ~]# ls -l /u01
total 4
drwxr-xr-x 3 oracle dba 4096 Dec 19 13:49 app

[root@pal ~]# cd /home/oracle

[root@pal oracle]# ls

[root@pal oracle]# ls -a
. .. .bash_logout .bash_profile .bashrc .emacs .kde .zshrc

[root@pal oracle]# vim .bashrc

[root@pal oracle]# cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# User specific aliases and functions
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

[root@pal oracle]# cd /etc

[root@pal etc]# vim sysctl.conf

[root@pal etc]# cat sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 4294967295

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 268435456

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

[root@pal etc]# cd /security

[root@pal security]# vim limits.conf
[root@pal security]# cat limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain>        <type> <item> <value>
#
#Where:
#<domain> can be:
#        - an user name
#        - a group name, with @group syntax
#        - the wildcard *, for default entry
#        - the wildcard %, can be also used with %group syntax,
#                 for maxlogin limit
#
#<type> can have the two values:
#        - "soft" for enforcing the soft limits
#        - "hard" for enforcing hard limits
#
#<item> can be one of the following:
#        - core - limits the core file size (KB)
#        - data - max data size (KB)
#        - fsize - maximum filesize (KB)
#        - memlock - max locked-in-memory address space (KB)
#        - nofile - max number of open files
#        - rss - max resident set size (KB)
#        - stack - max stack size (KB)
#        - cpu - max CPU time (MIN)
#        - nproc - max number of processes
#        - as - address space limit
#        - maxlogins - max number of logins for this user
#        - maxsyslogins - max number of logins on the system
#        - priority - the priority to run user process with
#        - locks - max number of file locks the user can hold
#        - sigpending - max number of pending signals
#        - msgqueue - max memory used by POSIX message queues (bytes)
#        - nice - max nice priority allowed to raise to
#        - rtprio - max realtime priority
#
#<domain>      <type> <item>         <value>
#

#*               soft    core            0
#*               hard    rss             10000
#@student        hard    nproc           20
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4
oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile          65536
# End of file

[root@pal security]#usermod -g dba apache

[root@pal security]# /etc/init.d/network restart
Shutting down interface eth0:                              [ OK ]
Shutting down loopback interface:                          [ OK ]
Bringing up loopback interface:                            [ OK ]
Bringing up interface eth0:                                [ OK ]

[root@seat11g security]#