Quiescing a Database - Suspending a Database -  Dropping a Database- Managing Initialization Files - Managing Parameters in SPFILE

Quiescing a Database

 Users will remain logged in, and they can continue to execute their requests that are in

progress, while the database is in the quiesced state. The database, however, will block all

new transactional requests except those made by the users SYS and SYSTEM (not DBA

grantees).

ALTER SYSTEM QUIESCE RESTRICTED;

ALTER SYSTEM UNQUIESCE;

SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Suspending a Database

 All reads from and writes to the datafiles and control files are suspended.

ALTER SYSTEM SUSPEND;

ALTER SYSTEM RESUME;

SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Dropping a Database

 Datafiles, redo log files, and control files are removed automatically. Alert log and

parameter file are not deleted.

CONNECT sys/sys_passwd AS SYSDBA

SHUTDOW IMMEDIATE

STARTUP RESTRICT MOUNT

SELECT name FROM v$database;

DROP DATABASE;

Initialization Files

Managing Initialization Files

 During startup, in $ORACLE_HOME/dbs (for UNIX/Linux) Oracle will look for the correct

initialization file to use, in the following order:

o spfile$ORACLE_SID.ora

o spfile.ora

o init$ORACLE_SID.ora

# currently used SPFiles (if null, pfile was used)

select * from v$parameter where name = 'spfile'

# create pfile

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM

PFILE='/u01/oracle/dbs/test_init.ora'

# use pecific spfile during startup

STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

# PFILE can indicate to use SPFILE

SPFILE = /database/startup/spfileDBA1.ora

In Oracle 11g

# write current values of instance parameters

CREATE PFILE FROM MEMORY;

CREATE SPFILE FROM MEMORY;

Managing Parameters in SPFILE

-- display current value of a parameter

select * from v$parameter where name = 'spfile'

-- parameter values set in SPFILE

select NAME, ISSPECIFIED from V$SPPARAMETER where name like '%dest%';

-- in SQL Plus

show parameter target

-- set parameter value

alter system set parameter=value scope=SPFILE|MEMORY|BOTH

-- delete a parameter from SPFILe

ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='node01';

-- Only sessions started after the statement is executed are affected

-- this option is a must for the parameters whose ISSYS_MODIFIABLE column

-- in V$PARAMETER is DEFERRED. You cannot use it, if the column value is

FALSE

ALTER SYSTEM SET parameter_name DEFERRED;

-- undocumented parameters

SELECT

a.ksppinm parameter, a.ksppdesc description,

b.ksppstvl session_value, c.ksppstvl instance_value

FROM x$ksppi a, x$ksppcv b, x$ksppsv c

WHERE

a.indx = b.indx

AND a.indx = c.indx

AND SUBSTR (a.ksppinm,1,1) = '_'

ORDER BY a.ksppinm;

Alert and Trace Files

Monitoring Alert and Trace Files

# to obtain the Location of Trace Files
column NAME format a35
column VALUE format a35
select name, value from v$parameter
where name in ('background_dump_dest','user_dump_dest')
# to set maximum size of trace files (excluding the alert file)
# in number of block unless you sepcify K or M
select * from v$parameter where upper(name )= 'MAX_DUMP_FILE_SIZE'
alter session set MAX_DUMP_FILE_SIZE='100M'

Read More »

Autostart of Database in Windows and Automatically Starting Databases in Unix

Autostart of Database in Windows

 As a service or name OracleServiceSID. You can manage the service using ORADIM utility.
 You can control the shutdown type of the database when you shutdown its corresponding
service by editing the registry entry ORA_<SID>_SHUTDOWNTYPE in Oracle home entry.

Automatically Starting Databases in Unix

 For Oracle 10.1, refer to Note 222813.1

/* Example 1 */
# compatible with Note 281912.1 (and Note 760051.1)
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y

#(3) Create dbora script in the directory /etc/init.d
# fix ORA_HOME and ORA_OWNER values as required
vi /etc/init.d/dbora
#!/bin/bash
#
# description: Start/Stop the Databases..
#
# chkconfig: 2345 99 10
#
# processname: oracle
# config: /etc/oratab
# pidfile: /var/run/oracle.pid
# Source function library.
. /etc/init.d/functions
RETVAL=0
ORA_OWNER="oracle"
ORA_HOME="/u01/app/oracle/product/11.2.0/db_1"
prog="oracle"
start() {
echo -n $"Starting $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dbora
return $RETVAL
}
stop() {
echo -n $"Stopping $prog: "
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
RETVAL=$?
echo
[ $RETVAL -eq 0 ] && rm -r /var/lock/subsys/dbora
return $RETVAL
}
restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
echo $"Usage: $0 {start|stop|restart}"
exit 1
esac
exit $?
#(4) as root:
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora
cd /sbin
chkconfig --add dbora
chkconfig --list | grep ora
# to test, restart or:
/etc/init.d/dbora start
/* Example 2 */
-- have been tested on an Oracle 10g Db on Linux 4
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y
#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required
#!/bin/sh
#
# /etc/rc.d/init.d/dbora
# Description: Starts and stops the Oracle database and listeners
#
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle Databases as part of system up."
>> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle

echo "Done."
echo -n "Starting Oracle Listeners: "
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle Listeners: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Shutting Down Oracle Databases as part of system
down." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c "lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle Databases: "
su - oracle -c dbshut >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
;;
restart)
echo -n "Restarting Oracle Databases: "
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle Databases as part of system up."
>> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
su - oracle -c dbstop >> /var/log/oracle
su - oracle -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners: "
su - oracle -c "lsnrctl stop" >> /var/log/oracle
su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo ""
echo "------------------------------------" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop|restart}"
exit
esac
# (4) set file group and permissions
chgrp dba dbora
chmod 750 dbora
# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

/* Example 3 */
#(1) login as root
#(2) Set it to Y in /etc/oratab
mydb:/u01/app/oracle/product/10.2.0/db_1:Y
#(3) Create dbora script in the directory /etc/init.d
# fix ORACLE_HOME value as required
vi /etc/init.d/dbora
#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script
# Source function library.
. /etc/rc.d/init.d/functions
ORACLE_OWNER="oracle"
ORACLE_HOME="/u01/app/oracle/product/10.2.0/db_1"
case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
echo -n "Starting Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
echo -n "Stopping Oracle Listeners:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac
# (4) set file group and permissions
chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora
# (5) Create the symbolic links: In Linux,
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

# (6) configuring execution for specific runlevels
chkconfig --add dbora --level 0356

Read More »

Data Dictionary and Dynamic Performance Views

Data Dictionary Creation
sql.bsq creates base tables
catalog.sql creates data dictionary views
catproc.sql runs scripts required for server-side PL/SQL
 they run as SYSDBA
 they are located in \rdbms\admin

Startup and Shutdown

Startup Levels
STARTUP [FORCE] [RESTRICT] [PFILE=filename] [OPEN [RECOVER] [database] |MOUNT
|NOMOUNT]
ALTER DATABASE { MOUNT | OPEN }
ALTER DATABASE OPEN [READ WRITE| READ ONLY]
ALTER SYSTEM ENABLE | DISABLE RESTRICTED SESSION;
Shutdown Levels
SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Read More »

Database Administration Authentication 

 

Using Operating System Authentication

Following are the steps to create an Oracle administrator using OS authenticaion. Creating
normal user who is capable of using OS authentication is a bit different. See External (OS)
Authentication.
1. Create an operating system account for the user.
2. Add the account to the OSDBA or OSOPER operating system defined groups.
Unix User Groups: dba, oper
Windows: ORA_DBA, ORA_OPER
3. login to OS as the created user
4. In SQL*Plus
CONNECT / AS SYSDBA
CONNECT / AS SYSOPER

Using Password File Authentication

5. Create the password file
6. orapwd FILE=filename PASSWORD=password ENTRIES=max_users
7. alter system set REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE scope=SPFILE;
8. CONN / AS SYSDBA
9. GRANT SYSDBA TO GRANTEDUSER
10. CONN GRANTEDUSER/USERPASSWORD AS SYSDBA
Identifying Users SYSDBA or SYSOPER Users
SELECT * FROM V$PWFILE_USERS

Read More »

Obtaining Information about the SGA

 

select value from v$parameter where upper(name)='MEMORY_TARGET';
select value/1024/1024 mb
from v$parameter where upper(name) = 'SGA_MAX_SIZE'
-- size taken by a memory component
select pool, round(sum(BYTES)/1024/1024) MB
from V$SGASTAT
group by pool
select name , value/1024/1024 MB
from v$parameter
where upper(name) in (
'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE')
-- 10g
SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0
-- Oracle 11g
SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS;
-- To know how Oracle has modified the memory area sizes by time
select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME

Clearing the Buffer Cache

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE ;

Read More »

Managing Oracle Database Instance

Product Release Number

Oracle Database Release Number Format
SELECT * FROM PRODUCT_COMPONENT_VERSION;
SELECT * FROM V$VERSION
1.2.3.4.5
1 Major database release number
2 Maintenance release number
3 application server release number
4 component specific release number
5 platform specific release number

Obtaining License Information

-- Number of users and CPU/Processors
select L.SESSIONS_MAX, L.SESSIONS_WARNING, L.SESSIONS_CURRENT,
L.SESSIONS_HIGHWATER,
L.USERS_MAX, L.CPU_COUNT_CURRENT, L.CPU_SOCKET_COUNT_CURRENT,
L.CPU_COUNT_HIGHWATER,
L.CPU_CORE_COUNT_CURRENT, L.CPU_CORE_COUNT_HIGHWATER,
L.CPU_SOCKET_COUNT_HIGHWATER
from v$license l;
-- Database Edition
select banner from v$version where BANNER like '%Edition%';
-- Oracle Partitioning installed
select decode(count(*), 0, 'No', 'Yes')
from dba_part_tables
where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;
-- Oracle Spatial installed:
select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;
-- Oracle RAC installed:
select decode(count(*), 0, 'No', 'Yes')
from v$active_instances where rownum <= 2;

Managing the Instance Architecture

Obtaining Information about the Instance Processes

ps –ef | grep ora
select name, description from V$BGPROCESS order by 1;

 

Read More »

Installing Oracle 11g R2 on Enterprise Linux 5.5 with ASM

 

Note: for any installation, you should check the Release Notes documenation before taking

any practical step.

Installation Environment

 Emulation software: VMWare Workstation 7 for Windows

 OS: Oracle Enterprise Linux 5.5 for x86 64-bit

Required Software

 Oracle Database 11g Release 2 for Linux x86 64-bit

 Oracle Database 11g Release 2 Grid Infrastructure (11.2.0.1.0) for Linux x86 64-bit

Used Hardware

 In the VMWare: create one virtual machine with the following specs:

o 2.5 GB RAM

o One ethernet card configured as bridged or host-only in VMware

o CPU Count: 2

o Disk1: 34 GB on SCSI 0:0 used to install the OS and software

o Disk2: of 12 GB. It will be used for +Data. Set it on controller SCSI 1:1

o Disk3: of 6 GB. It will be used for +Flash. Set it on controller SCSI 1:2

Installation Plan

1. Preinstallation tasks

 Hardware requirements

 Software requirements

 Environment configuration

2. Oracle Grid Infrastructure installation

3. Oracle Grid Infrastructure Patching

4. Oracle Database 11g R2 Software Installation

5. Oracle Database 11g R2 Software Patching

6. Install EM Agent in cluster nodes (if required)

7. ASM Diskgroups Creation

8. Database Creation

9. Complete postinstallation tasks

10. Useful postinstallation tasks

1. Preinstallation tasks

 Install Oracle Enterprise Linux in the first local hardisk. Install nothing in the remaining

disks.

Note: for a production system, consider becoming an Oracle Unbreakable Linux customer

and register your server on the Unbreakable Linux Network.

o Configure the swap area in the local hardisk to have 6 GB disk space.

o Give the ethernet card IP 192.168.4.105 the hostname srv07

o Insall the following packages:

 Desktop Environments

o GNOME Desktop Environment

 Applications

o Graphical Internet (optional)

o Editors (optional)

 Development

o Development Libraries

o Development Tools

 Servers

o Do not select anything in this group.

 Base System

o Administration Tools

o System Tools

 Add the package 'sysstat' by clicking on the Details link

and selecting "sysstat - The sar an iostat system

monitoring commands." from the Optional Packages list.

 X Window System

 Complete the installation

 Install further packages:

# to know distribution and version of Linux

cat /etc/issue

# to know kernel version (and its errata level) (2.6.18-194.el5)

uname -r

# to list missed packages:

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils \

compat-libstdc++-33 \

elfutils-libelf \

elfutils-libelf-devel \

gcc \

gcc-c++ \

glibc \

glibc-common \

glibc-devel \

glibc-headers \

ksh \

libaio \

libaio-devel \

libgcc \

libstdc++ \

libstdc++-devel \

make \

sysstat \

unixODBC \

unixODBC-devel

# for missed packages, install them:

rpm -Uvh elfutils-libelf-0.137-3.el5.i386.rpm

rpm -ivh elfutils-libelf-devel-static-0.137-3.el5.i386.rpm elfutils-libelfdevel-

0.137-3.el5.i386.rpm

rpm -Uvh kernel-headers-2.6.18-194.el5.x86_64.rpm

rpm -Uvh glibc-headers-2.5-49.x86_64.rpm

rpm -Uvh glibc-devel-2.5-49.i386.rpm

rpm -Uvh glibc-devel-2.5-49.x86_64.rpm

rpm -Uvh libgomp-4.4.0-6.el5.x86_64.rpm

rpm -Uvh gcc-4.1.2-48.el5.x86_64.rpm

rpm -Uvh gcc-c++-4.1.2-48.el5.x86_64.rpm

rpm -Uvh libstdc++-devel-4.1.2-48.el5.x86_64.rpm

rpm -Uvh gcc-c++-4.1.2-48.el5.x86_64.rpm

rpm -Uvh libaio-devel-0.3.106-5.i386.rpm

rpm -Uvh libaio-devel-0.3.106-5.x86_64.rpm

rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm

rpm -Uvh unixODBC-2.2.11-7.1.x86_64.rpm

rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm

rpm -Uvh unixODBC-devel-2.2.11-7.1.x86_64.rpm

# Oracle ASM Libaray and drivers can be downloaded from here

# to know the kernel verion: uname -rm

# In this case we need:

# library and tools

rpm -Uvh oracleasm-support-2.1.3-1.el5.x86_64.rpm

rpm -Uvh oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm

rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

# SELINUX must be disabled

cat /etc/selinux/config | grep SELINUX=

vi /etc/selinux/config

SELINUX=disabled

shutdown -h now -r

 Check the hardware requirements

# Hardware Requirements (in cluster nodes)

# At least 1.5 GB of physical memory but practically 1.5 is not fine

grep MemTotal /proc/meminfo

# swap space: same as the amount of physical memory

grep SwapTotal /proc/meminfo

# to display swap and memory in one command:

free

# if you don't have enought swap,

# you can add swap space by creating a temporary swap file.

# let's say about 500MB:

dd if=/dev/zero of=tempswap bs=1k count=500000

chmod 600 tempswap

mke2fs tempswap

mkswap tempswap

swapon tempswap

# 1 GB disk space in /tmp

df -h /tmp

# 8 GB of disk space for Oracle software

df

The size of the shared memory should be at least the greater of

MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.

To determine the amount of shared memory available, enter the following

command:

df -h /dev/shm/

# to adjust the shared memory file system size:

umount tmpfs

mount -t tmpfs shmfs -o size=1200m /dev/shm

 Create the required network configuration:

ping srv07

# Network names Resolution

cat /etc/hosts

127.0.0.1 srv07 localhost.localdomain localhost

 Create and configure the required OS users and groups

Note: When I tried using differenct OS users and groups for Grid Infrastrucutre and ASM,

I got error later in DBCA.

# Grid Infrastructure (GI) and the Oracle RDBMS home will

# be installed using different users:

/usr/sbin/groupadd -g 501 oinstall

/usr/sbin/groupadd -g 502 dba

/usr/sbin/groupadd -g 504 asmadmin

/usr/sbin/useradd -u 502 -g oinstall -G dba,asmadmin oracle

# set passwords

passwd oracle

# make sure nobody user exists (if not there, create it useradd nobody)

id nobody

# define the env variables for oracle user

vi /home/oracle/.bash_profile

# Oracle Settings

export EDITOR=vi

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=srv07; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=oradb; 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/jdk/jre/lib/:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

export CLASSPATH

# shell startup file

vi /etc/profile

if [ $USER = "oracle" ] || [ $USER = "grid" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

# for C shell

vi /etc/csh.login

if ( $USER = "oracle" || $USER = "grid" ) then

limit maxproc 16384

limit descriptors 65536

endif

 Configure kernel parameters and shell limits

Note: If you make a mistake with a parameter setting and your system does not start,

then you must start Linux in the single-user runlevel (runlevel 1). At this runlevel, the

/etc/sysctl.conf file is not run.

# Kernel Parameters

# to tune thme, refer to metalink document 169706.1

# Append the following to the /etc/sysctl.conf file as the root user:

vi /etc/sysctl.conf

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.aio-max-nr = 1048576

fs.file-max = 6815744

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

# to take immediate effect

/sbin/sysctl -p

# User Shell Limits

# memlock is used to increase the per-process max locked memory

vi /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

vi /etc/pam.d/login

session required pam_limits.so

 Create partitions in the disks:

Note: On a real life storage, you would create a single whole-disk partition with exactly 1 MB

offset on each LUN to be used as ASM Disk. In fdisk: u (to change units from cylinder to

sectors), n, p, 1, 2048, w.

# as a root, for the disks /dev/sdb .. /dev/sdd

# confirm they are seen:

ls /dev/sd*

#partition the disks:

fdisk /dev/sdb

# answers: "n", "p", "1", "Return", "Return", "p" and "w"

Note: if the following message appears after the "w" command:

WARNING: Re-reading the partition table failed with error 16: Device or

resource busy, then you can avoid restarting the machine by the following

command: partprobe

# to make sure partions are created

ls -lX /dev/sd*

 Configure ASM drivers:

# as root (to be done in all nodes)

oracleasm configure -i

Default user to own the driver interface []: oracle

Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y

Fix permissions of Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

# Load the kernel module using the following command:

/usr/sbin/oracleasm init

# If you have any problems, make sure you have the correct

# version of the driver (may require Internet connection):

/usr/sbin/oracleasm update-driver

# mark the shared disks: (one node)

/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1

/usr/sbin/oracleasm createdisk DISK2 /dev/sdc1

# check the disks are marked and seen:

/usr/sbin/oracleasm listdisks

#If you need to unmark a disk that was used in a createdisk command:

/usr/sbin/oracleasm deletedisk DISK1

/usr/sbin/oracleasm deletedisk DISK2

 Create the required directories for the Oracle software:

# to know if there is an existing oracle inventory

# from its output, ORACLE_BASE will be parent of oraInventory

more /etc/oraInst.loc

# to identify existing Oracle home directories

more /etc/oratab

# Oracle Inventory Directory

# as a root

mkdir -p /u01/app/oraInventory

chown -R oracle:oinstall /u01/app/oraInventory

chmod -R 775 /u01/app/oraInventory

# Grid Infrastructure Home Directory

mkdir -p /u01/11.2.0/grid

chown -R oracle:oinstall /u01/11.2.0/grid

chmod -R 775 /u01/11.2.0/grid

# Oracle Base Directory

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/app/oracle

#needed to ensure that dbca is able to run after the rdbms installation

mkdir /u01/app/oracle/cfgtoollogs

chown -R oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/app/oracle

# Oracle RDBMS Home Directory

mkdir -p /u01/app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1

chmod -R 775 /u01/app/oracle/product/11.2.0/db_1

2. Oracle Grid Infrastructure installation

# software staging folder

mkdir -p /u01/app/stage/ora11gr2gridinfra

chown -R oracle:oinstall /u01/app/stage/ora11gr2gridinfra

chmod -R 775 /u01/app/stage/ora11gr2gridinfra

mkdir -p /u01/app/stage/ora11gr2db

chown -R oracle:oinstall /u01/app/stage/ora11gr2db

chmod -R 775 /u01/app/stage/ora11gr2db

# as oracle: copy Grid software files to /u01/app/stage/ora11gr2db

# as root:

chmod -R 775 /u01/app/stage/ora11gr2gridinfra

# lunch OUI from the clusterware ( as oracle)

cd /u01/app/stage/ora11gr2gridinfra

./runInstaller

Installation Option

>Select radio button 'Install and Configure Grid Infrastructure for a

Standalone Server'

>Next

Product Language

>Accept 'English' as language'

>Next

Creat ASM Disk Group

>Disk Group Name: Data (12GB disk: DISK1)

>Redundancy: external

>Next

NOTE: If you see an empty screen for you candidate disks it is likely that

ASMLib has not been properly configured or installed. Try reconfigure them.

If you are sure that ASMLib has been properly configured click on 'Change

Discovery Path' and provide the correct destination.

ASM Password

>Specify and conform the password you want to use

>Next

Privileged OS Groups

>Assign the correct OS groups for OS authentication (mostly default is OK)

>Next

Installation Location

>ORACLE_BASE: /u01/app/oracle

Software location: /u01/11.2.0/grid

>Next

Create Inventory

>Specify the locations: /u01/app/oraInventory

>Next

Perform Prerequisite Checks

>Check that status of all checks is Succeeded

>Next

Summary

>Finish

Execute Configuration Scripts

>Run the scripts as instructed in the screen

>OK

>Next

Message: The installation of the Grid Infrastructure was successfull.

>Close

3. Oracle Grid Infrastructure Patching

Apply patch set, if there is any.

4. Oracle Database 11g R2 Software Installation

# as oracle: copy DB software files into /u01/app/stage/ora11gr2db

# as root:

chmod -R 775 /u01/app/stage/ora11gr2db

-- start OUI

su - oracle

cd /u01/app/stage/ora11gr2db

./runInstaller

Follow the steps.

Install database software only.

When executing root.sh, select "y" for ovewriting questions.

5. Oracle Database 11g R2 Software Patching

6. Install EM Agent in cluster nodes (if required)

7. ASM Diskgroups Creation

# as grid user: start the ASM Configuration Assistant (ASMCA)

su - oracle

cd /u01/11.2.0/grid/bin

./asmca

>Disk Groups tab

>Create button

>Disk Group Name: FLASH

>Redundancy: External

>DISK2

>OK

>Exit

>Yes

8. Database Creation

# as oracle

cd /u01/app/oracle/product/11.2.0/db_1/bin

./dbca

follow the steps to create a database with sid: oradb

The DBCA may pop up the following message in the end of db creation:

"Error securing Database control, Database Control has been brought up in nonsecure

mode. To secure the Database Control execute the following commands:

... "

# check Oracle processes:

ps -eo pid -o command | grep ora_ | grep -v grep

9. Postinstallation tasks

# backup the root.sh script

cp /u01/app/oracle/product/11.2.0/db_1/root.sh ~/root.sh.bak

cont>>

10. General Useful Postinstallation Tasks in Linux

 Consider using rlwrap utility with SQL*Plus and RMAN:

o Using rlwrap Utility with RMAN in Unix-Based Systems

o Using rlwrap Utility with SQL*Plus in Unix-Based Systems

/* Make crs_stat -t more readable */

/* copy the following script into ~/scripts/crstat.sh */

#!/usr/bin/ksh

#

# Sample 10g CRS resource status query script

#

# Description:

# - Returns formatted version of crs_stat -t, in tabular

# format, with the complete rsc names and filtering keywords

# - The argument, $RSC_KEY, is optional and if passed to the script, will

# limit the output to HA resources whose names match $RSC_KEY.

# Requirements:

# - $ORA_CRS_HOME should be set in your environment

# suggested scrip name: crstat.sh

RSC_KEY=$1

QSTAT=-u

AWK=/usr/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""

$AWK \

'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";

printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:

$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \

'BEGIN { FS="="; state = 0; }

$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};

state == 0 {next;}

$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}

$1~/STATE/ && state == 2 {appstate = $2; state=3;}

state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate;

state=0;}'

# then add the following in the .bashrc of oracle user

# if the file was saved in ~/scripts/crstat.sh

alias crstat='~/scripts/crstat.sh'

/* Easy Acces to crs and db homes */

# it is common to access bin directories in clusterware and db homes

# add the following to .bashrc of oracle user

alias db='cd /u01/app/oracle/product/11.2.0/db_1/bin'

# add the following to .bashrc of grid user

alias crs='cd /u01/app/oracle/crs/bin'

Read More »

Installing Oracle 11g R2 on Enterprise Linux 5

 

Note: for any installation, you should check the Release Notes documenation before taking any practical step.

Installation Environment

 Emulation software: VMWare Workstation 7 for Windows

 OS: Red Hat Enterprise Linux 5.2 for x86

Required Software

 Oracle Database 11g Release 2 for Linux x86 32-bit

Used Hardware

 In the VMWare: create one virtual machine with the following specs:

o 2.0 GB RAM

o One ethernet cards: can be configured as bridged or host-only in VMware.

o One local hardisk with 32 GB on SCSI 0:0. It will be used for software installation.

o One local hardisk with 20 GB on SCSI 1:0. It will be used for Oracle Database data

files.

o One local hardisk with 20 GB on SCSI 1:1. It will be used for Oracle Database flash

recovery.

o CPU Count: 2 (optional)

Installation Plan

1. Preinstallation tasks:

o Hardware requirements

o Software requirements

o Environment configuration

2. Oracle Database 11g Software Installation

3. Apply Patchset

4. Configure Listeners

5. Create Database

6. Postinstallation tasks

1. Preinstallation tasks

 Install Oracle Enterprise Linux in the first local hardisk. Install nothing in the remaining

disks.

Note: for a production system, consider becoming an Oracle Unbreakable Linux customer
and register your server on the Unbreakable Linux Network.

o Give the ethernet card IP 192.168.4.100 and the hostname srv100.mydomain.com.

Define a gateway. If it does not exist, make it same as the host IP address.

o Insall the following packages:

 Desktop Environments

o GNOME Desktop Environment

 Applications

o Graphical Internet (optional)

o Editors (optional)

 Development

o Development Libraries

o Development Tools

 Servers

o Do not select anything in this group.

 Base System

o Administration Tools

o System Tools

 Add the package 'sysstat' by clicking on the Details link

and selecting "sysstat - The sar an iostat system

monitoring commands." from the Optional Packages list.

 X Window System

 Complete the installation

 RHEL 5 Bug: After the Installation compelets, RHEL 5.2 and below will hang on booting

when it reaches to "starting udev" line. To solve this problem, shutdown the Vmware

machine and change the CPU count and Core Count to only one. Implement the changes

below, then shutdown the machine, set CPU count back to 2 and startup the machine.

put the kernel command line parameters at the end of the "kernel" line:

vi /boot/grub/grub.conf

add divider=10 clocksource=acpi_pm

For example: kernel /vmlinuz-2.6.18 .. clock=acpi_pm divider=10

 For Vmware machines, install VMWare tools.

 Install further packages:

# to know distribution and version of Linux (Red Hat Ent. 5.2 used)
cat /etc/issue
# to know kernel version (and its errata level) (2.6.18-92 or newer)
uname -r
# to list missed packages:
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \

gcc-c++ \

glibc \

glibc-common \

glibc-devel \

glibc-headers \

ksh \

libaio \

libaio-devel \

libgcc \

libstdc++ \

libstdc++-devel \

make \

sysstat \

unixODBC \

unixODBC-devel

# for missed packages, install them:

rpm -Uvh libaio-devel-0.3.106-3.2.i386.rpm

rpm -Uvh unixODBC*

# SELINUX must be disabled

cat /etc/selinux/config | grep SELINUX=

vi /etc/selinux/config

SELINUX=disabled

shutdown -h now -r

 Check the hardware requirements

# Hardware Requirements (in cluster nodes)

# At least 1.0 GB of physical memory

grep MemTotal /proc/meminfo

# swap space: same as the amount of physical memory

grep SwapTotal /proc/meminfo

# to display swap and memory in one command:

free

# if you don't have enought swap,

# you can add swap space by creating a temporary swap file.

# let's say about 500MB:

dd if=/dev/zero of=tempswap bs=1k count=500000

chmod 600 tempswap

mke2fs tempswap

mkswap tempswap

swapon tempswap

# 1 GB disk space in /tmp

df -h /tmp

# 4 GB of disk space for Oracle software

df

The size of the shared memory should be at least the greater of

MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.

To determine the amount of shared memory available, enter the following

command:

df -h /dev/shm/

# to adjust the shared memory file system size:

umount tmpfs

mount -t tmpfs shmfs -o size=1200m /dev/shm

 Create the required network configuration:

ping srv100

ping srv100.mydomain.com

# Network names Resolution

# configure /etc/hosts if no domain server is used

cat /etc/hosts

127.0.0.1 srv100.mydomain.com srv100 localhost.localdomain

localhost

 Create and configure the required OS users and groups

# all group and user ids on all the nodes must have identical id

# Grid Infrastructure (GI) and the Oracle RDBMS home will

# be installed using different users:

# oracle inventory group

/usr/sbin/groupadd -g 501 oinstall

/usr/sbin/groupadd -g 502 dba

/usr/sbin/useradd -u 502 -g oinstall -G dba oracle

# set passwords

passwd oracle

# make sure nobody user exists (if not there, create it useradd nobody)

id nobody

# define the env variables for oracle user

vi /home/oracle/.bash_profile

# Oracle evn vars

export EDITOR=vi

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=srv100.mydomain.com

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=ora11gr2

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

# shell startup file

vi /etc/profile

if [ $USER = "oracle" ] || [ $USER = "grid" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

# for C shell

vi /etc/csh.login

if ( $USER = "oracle" || $USER = "grid" ) then

limit maxproc 16384

limit descriptors 65536

endif

 Configure kernel parameters and shell limits

Note: If you make a mistake with a parameter setting and your system does not start, then

you must start Linux in the single-user runlevel (runlevel 1). At this runlevel, the

/etc/sysctl.conf file is not run.

# Configuring kernel parameters and shell limits

# they can be tuned for a production db

# Append the following to the /etc/sysctl.conf file as the root user:

vi /etc/sysctl.conf

# kernel.shmmax not stated in 11g R2 (max: 4GB) (169706.1)

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.aio-max-nr = 1048576

fs.file-max = 6815744

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

# to take immediate effect

/sbin/sysctl -p

# User Shell Limits

# memlock is used to increase the per-process max locked memory

vi /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

vi /etc/pam.d/login

session required pam_limits.so

 Create the required directories for the Oracle database software:

# to know if there is an existing oracle inventory

# from its output, ORACLE_BASE will be parent of oraInventory

more /etc/oraInst.loc

# to identify existing Oracle home directories

more /etc/oratab

# Oracle Inventory Directory

# as a root

mkdir -p /u01/app/oraInventory

chown -R oracle:oinstall /u01/app/oraInventory

chmod -R 775 /u01/app/oraInventory

# Oracle Base Directory

mkdir -p /u01/app/oracle

#needed to ensure that dbca is able to run after the rdbms installation

mkdir -p /u01/app/oracle/cfgtoollogs

chown -R oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/app/oracle

# Oracle RDBMS Home Directory

mkdir -p /u01/app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1

chmod -R 775 /u01/app/oracle/product/11.2.0/db_1

# staging area to hold software installation

mkdir -p /u01/stage11g/db

chown -R oracle:oinstall /u01/stage11g/db

chmod -R 775 /u01/stage11g/db

 Partition and mount the disks:

#define the disks to partition

fdisk -l | grep '^Disk'

# as a root, for the disks /dev/sdb and /dev/sdc

#partition the disks:

fdisk /dev/sdb

# answers: "n", "p", "1", "Return", "Return", "p" and "w"

Note: if the following message appears after the "w" command:

WARNING: Re-reading the partition table failed with error 16: Device or

resource busy, then you can avoid restarting the machine by the following

command: partprobe

# to make sure partions are created

ls -lX /dev/sd*

# format the paritions

mkfs.ext3 /dev/sdb1

mkfs.ext3 /dev/sdc1

# Mount the new disk

mkdir /u02

mount /dev/sdb1 /u02

mkdir /u03

mount /dev/sdc1 /u03

df -H

# Update /etc/fstab

vi /etc/fstab

/dev/sdb1 /u02 ext3 defaults 1 2

/dev/sdc1 /u03 ext3 defaults 1 2

# create folder for the db data

mkdir -p /u02/oradata/

chown -R oracle:oinstall /u02/oradata/

chmod -R 775 /u02/oradata/

# create folder for the flash area

mkdir -p /u03/oraflash/

chown -R oracle:oinstall /u03/oraflash/

chmod -R 775 /u03/oraflash/

 As oracle, copy software installation into /u01/stage11g/db

2. Oracle Database 11g Software Installation

-- start OUI

su - oracle

cd /u01/stage11g/db

./runInstaller

Follow the steps.

Install database software only.

3. Apply Patchset

4. Configure Listeners

cd /u01/app/oracle/product/10.2.0/db_1/bin

./netca &

Add a new listener

Add Naming mtehods: Local Naming, EZConnect

5. Create Database

cd /u01/app/oracle/product/10.2.0/db_1/bin

./dbca &

follow the steps to create a database with sid: ora11gr2

The DBCA may pop up the following message in the end of db creation:

"Error securing Database control, Database Control has been brought up in nonsecure

mode. To secure the Database Control execute the following commands:

... "

6. Postinstallation Tasks

 Verify OEM:

# verify that OEM is working

https://srv100.mydomain.com:1158/em

# restart the dbconsole if required

emctl status dbconsole

emctl stop dbconsole

emctl start dbconsole

# check Oracle processes:

ps -eo pid -o command | grep ora_ | grep -v grep

 Consider implementing automatic database startup. See: Automatically Starting Databases

 Consider implementing automatic EM Database Control startup. See: Implementing EM

Database Control Auto Startup

 Consider using rlwrap utility with SQL*Plus and RMAN:

o Using rlwrap Utility with RMAN in Unix-Based Systems

o Using rlwrap Utility with SQL*Plus in Unix-Based Systems

 For easy Oracle Home access:

echo "alias db='cd $ORACLE_HOME'" >> /home/oracle/.bashrc

Read More »

Installing Oracle 10g R2 on Enterprise Linux 4

Installation Environment

 Emulation software: VMWare Server 2 on for Windows
 OS: Oracle Linux Enterprise 4.5 for x86: kernel 2.6.9

Required Software

 Oracle Database 10g Release 2 for Linux x86 32-bit

Used Hardware

 In the VMWare: create one virtual machine (oradb1) with the following specs:
o 2 GB RAM
o an ethernet card
o one local hardisk with 20 GB
Installation Plan
1. Preinstallation tasks:
o Hardware requirements
o Software requirements
o Environment configuration
2. Oracle Database 10g Software Installation
3. Apply Patchset 3 (10.2.0.4) for Clusterware and Database Software
4. Configure Listeners
5. Create Database
6. Postinstallation tasks
1. Preinstallation tasks
 Install Oracle Enterprise Linux in the first local hardisk. Install nothing in the remaining
disks.
Note: for a production system, consider becoming an Oracle Unbreakable Linux customer
and register your server on the Unbreakable Linux Network.
o Give the ethernet card IP 192.168.4.21 and the hostname oradb1.mydomain.com.
Define a gateway. If it does not exist, make it same as the host IP address.
o Insall the following packages:
 Desktop Environments
o GNOME Desktop Environment

 Desktop
o X Window System
o Gnome
 Applications
o Graphical Internet (optional)
 Servers
o Do not select anything in this group.
 Development
o Development Tools
 System
o Administration Tools
o System Tools
 Add the package 'sysstat' by clicking on the Details link
and selecting "sysstat - The sar an iostat system
monitoring commands." from the Optional Packages list.
 Miscellaneous
o Do not select anything in this group.
 Complete the installation
 Install further packages:
# to know distribution and version of Linux
cat /etc/issue
# to know kernel version (and its errata level)
uname -r
# from CD 3
rpm -Uvh libaio*
rpm -Uvh openmotif21-2.1.30-11.RHEL4.6.i386.rpm
rpm -Uvh openmotif-2.2.3-10.1.el4.i386.rpm
# those packages downloaded from http://rpm.pbone.net
rpm -e compat-libstdc++-296-2.96-132.7.2
rpm -Uvh compat-libstdc++-7.3-2.96.128.i386.rpm
rpm -Uvh compat-libstdc++-devel-7.3-2.96.128.i386.rpm
rpm -Uvh compat-gcc-7.3-2.96.128.i386.rpm
rpm -Uvh compat-gcc-c++-7.3-2.96.128.i386.rpm
# confirm the required packages are installed:
rpm -qa|grep gccrpm
-qa|grep glibcrpm
-qa|grep compat-dbrpm
-qa|grep compat-gccrpm
-qa|grep compat-gcc-c++-
rpm -qa|grep compat-libstdc++-
rpm -qa|grep compat-libstdc++-develrpm
-qa|grep control-center-2.8.0
rpm -qa|grep openmotif21-
rpm -qa|grep setarch-
# SELINUX must be disabled

cat /etc/selinux/config | grep SELINUX=
vi /etc/selinux/config
SELINUX=disabled
shutdown -h now -r
 Check the hardware requirements
# Hardware Requirements
# At least 2 GB of physical memory
grep MemTotal /proc/meminfo
# swap space: twice the amount of physical memory
grep SwapTotal /proc/meminfo
# if you don't have enought swap,
# you can add swap space by creating a temporary swap file.
# let's say about 500MB:
dd if=/dev/zero of=tempswap bs=1k count=500000
chmod 600 tempswap
mke2fs tempswap
mkswap tempswap
swapon tempswap
# 400 MB disk space in /tmp
df -k /tmp
# 4 GB of disk space for Oracle software
df
The size of the shared memory should be at least the greater of
MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer.
To determine the amount of shared memory available, enter the following
command:
df -h /dev/shm/
# to adjust the shared memory file system size:
umount tmpfs
mount -t tmpfs shmfs -o size=1200m /dev/shm
 Create the required network configuration:
# Network names Resolution
# configure /etc/hosts if no domain server is used
cat /etc/hosts
127.0.0.1 localhost.localdomain oradb1.mydomain.com localhost
oradb1
 Create and configure the required OS users and groups
# inventory group
groupadd -g 501 oinstall
groupadd -g 502 dba
# oracle software owner user
/usr/sbin/useradd -u 200 -g oinstall -G dba oracle
passwd oracle
# make sure nobody user exists (if not there, create it useradd nobody)
id nobody
# The oracle User Environment
# in /home/oracle/.bash_profile
# export DISPLAY if required
export ORACLE_BASE=/u01/app/oracle
if [ $USER = "oracle" ]; then

if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
export EDITOR=vi
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_SID=ora10g
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NLS_DATE_FORMAT="mm/dd/yyyy hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp
# User Shell Limits
# memlock is used to increase the per-process max locked memory
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft memlock 3145728
oracle hard memlock 3145728
 Configure kernel parameters and shell limits
Note: If you make a mistake with a parameter setting and your system does not start, then
you must start Linux in the single-user runlevel (runlevel 1). At this runlevel, the
/etc/sysctl.conf file is not run.
# Configuring kernel parameters and shell limits
# they can be tuned for a production db
# Append the following to the /etc/sysctl.conf file as the root user:
vi /etc/sysctl.conf
kernel.sem = 250 32000 100 128
# maximum size may be given to SGA (max 4GB)
# kernel.shmmax = 536870912 (512 M)
# following is 1 GB
kernel.shmmax =1073741824
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304

net.core.wmem_default = 262144
net.core.wmem_max = 262144
# to take immediate effect
/sbin/sysctl -p
 Configure hangcheck-timer kernel module:
# check hangcheck-timer Module Configuration
# with this module, if the kernel hangs, the machine will reboot
# verify the module is loaded
/sbin/lsmod | grep -i hang
# if not loaded, load it
vi /etc/modprobe.conf
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
# execute and add in the file
vi /etc/rc.local
/sbin/modprobe hangcheck-timer
 Create the required directories for the Oracle database software:
# to know if there is an existing oracle inventory
# from its output, ORACLE_BASE will be parent of oraInventory
more /etc/oraInst.loc
# to identify existing Oracle home directories
more /etc/oratab
# in the example above, /u01 should be owned by the root user
# and writable by group oinstall
cd /
chown -R oracle:oinstall /u01
chmod -R 775 /u01/oracle
mkdir -p /u01/app/oracle/product/10.2.0/db_1
mkdir /u01/stage
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/oracle
chown oracle:oinstall /u01/stage
 As oracle, copy software installation into /u01/stage10g/db
 Disable screensavers on host & guest machines.
o In Oracle Linux: Applications-> Preferences-> Screen Saver-> Mode: Disable Screen
Saver
o Do the same after logging off and logging on again as oracle user.
 Restart the machine.

2. Oracle Database 10g Software Installation

-- start OUI
su - oracle
cd /u01/stage10g/db
./runInstaller
Follow the steps.
Install database software only.
3. Apply Patchset 3 (10.2.0.4) for Clusterware and Database Software
# extract 10g Release 2 (10.2.0.4) Patch Set 3 for Linux x86 to

/u01/stage10g/patch10.2.0.4/Disk1
mkdir /u01/stage10g/patch10.2.0.4
cd /u01/stage10g/patch10.2.0.4/Disk1
./runInstaller
Select path of ORACLE_HOME ->Next
4. Configure Listeners
cd /u01/app/oracle/product/10.2.0/db_1/bin
./netca &
Add a new listener
-- optionally, use net manager to manually register the database:
./netmgr
5. Create Database
cd /u01/app/oracle/product/10.2.0/db_1/bin
./dbca &
follow the steps to create a customized database of SID "ora10g" and Global
nmae "ora10g.oradb1".
Do not use specific templates (non-Custom).
The DBCA may pop up the following message in the end of db creation:
"Error securing Database control, Database Control has been brought up in nonsecure
mode. To secure the Database Control execute the following commands:
... "
# check the Oracle processes:
ps -eo pid -o command | grep ora_ | grep -v grep
6. Postinstallation Tasks
 Verify OEM:
# verify that OEM is working
http://oradb1:1158/em
# restart the dbconsole if required
emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole
 Verify iSQL*Plus:
# in the browser
http://oradb1:5560/isqlplus
# if not running:
isqlplusctl start
 Consider implementing automatic database startup. See: Automatically Starting Databases
 Consider implementing automatic EM Database Control startup. See: Implementing EM
Database Control Auto Startup
 Consider using rlwrap utility with SQL*Plus and RMAN:
o Using rlwrap Utility with RMAN in Unix-Based Systems
o Using rlwrap Utility with SQL*Plus in Unix-Based Systems
 For easy Oracle Home access:

echo "alias db='cd $ORACLE_HOME'" >> /home/oracle/.bashrc

Read More »

Oracle Database Installation Optimal Flexible Architecture

 The OFA is a set of recommendations from Oracle Corporation aimed at simplifying
management of complex software and databases often running under multiple versions of
software.

 

 OFA Guidelines
o Arrange at least four mount points that have names like /u01, /u02, /u03, and so
on.
o If the Oracle software owner is "oracle", make /u01/app/oracle your ORACLE_BASE
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
o Make Oracle_HOME $ORACLE_BASE/product/version/{db|client|companion}_[n].

Examples:
/u01/app/oracle/product/11.1.0/db_1
/u01/app/oracle/product/11.1.0/client_1
/u01/app/oracle/product/10.1.0.2.0/db_1
o Install Flash Recovery Area in $ORACLE_BASE/flash_recovery_area in a mount point
different from db files.
o Administrative Files are to be created in ORACLE_BASE/admin/dbname
o Store Oracle DB files in the mounted points.
# mkdir /mount_point/oradata
# chown oracle:oinstall /mount_point/oradata
# chmod 775 /mount_point/oradata
/u01/oradata/prod1/control01.ctl
/u05/oradata/prod1/control02.ctl
/u02/oradata/prod1/redo01.log
/u04/oradata/prod1/redo02.log
data file has this format:
/u20/oradata/prod/system01.dbf

 

Oracle Products Installed with the 11.1 Release

 

 The following products are installed by default with database server 11g:

o Oracle Application Express
o Oracle Warehouse Builder
o Oracle Configuration Manager: Tool that collects and uploads configuration
information to the Oracle configuration repository
o Oracle SQL Developer
o Oracle Database Vault: Tool that enables you to secure business data

Read More »

Oracle Database Installation  Estimating Disk and Memory Requirements Consideration

•  Make sure the storage vendor is listed in the Oracle Storage Compatibility Program (OSCP).

•  Allocate swap space that is about two to three times your Oracle RAM allocation.

•  On Estimating the RAM:

  o OS RAM: 20 percent of total RAM for MS-Windows, 10% of RAM for UNIX

  o Each Oracle connection consumes two megabytes of RAM + sort_area_size + hash_area_size

   

   If dynamic memory sizing is not used:

•  Shared_pool_size: If all the SQL statements that sent to ORACLE are using bind variable adequately, then 300M is enough in most cases

•  Large_pool_size: For dedicated Oracle server: 20-30M

• Java_pool_size=10M

•  Data buffer: All the rest RAM should be allocated to Data buffer.

Read More »