ASM Disk Group Configuration with asmca

Set Oracle environment to the ASM instance before executing the asmca command.

Click on Create to create new Disk Group.

Type Disk Group Name and Select Disk Path to configure new ASM Disk Group and Click on OK to create new disk group.

Disk Group created Successfully message will be displayed once new disk group created.

Click on Exit to close asmca window.

Click on Yes to close ASM  Configuration Assistance window.

1. Install Oracle 11g Grid Infrastructure With Oracle Virtualbox
2. ASM Disk Creation with asmca
3. Install Oracle 11g R2 Software
4. Create Oracle database with ASM Disk Group

Read More

Install Oracle 11g release 2 software only

Environment

Hostname: oel1.localdomain
Arch: x86_64
OS: OEL 6.3
Oracle: 11.2.0.3
RAM: 4G
HDD: 500G

Required Linux Package:

binutils-2*x86_64*
glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
glibc-2*i686* nss-softokn-freebl-3*i686*
compat-libstdc++-33*x86_64*
glibc-common-2*x86_64*
glibc-devel-2*x86_64*
glibc-devel-2*i686*
glibc-headers-2*x86_64*
elfutils-libelf-0*x86_64*
elfutils-libelf-devel-0*x86_64*
gcc-4*x86_64*
gcc-c++-4*x86_64*
ksh-*x86_64*
libaio-0*x86_64*
libaio-devel-0*x86_64*
libaio-0*i686*
libaio-devel-0*i686*
libgcc-4*x86_64*
libgcc-4*i686*
libstdc++-4*x86_64*
libstdc++-4*i686*
libstdc++-devel-4*x86_64*
make-3.81*x86_64*
numactl-devel-2*x86_64*
sysstat-9*x86_64*
compat-libstdc++-33*i686*
unixODBC-*x86_84
unixODBC-devel-*x86_84

Execute following command to verify the package installation.

rpm -q binutils glibc nss-softokn-freebl compat-libstdc++-33 glibc-common glibc-devel \
glibc-headers elfutils-libelf elfutils-libelf-devel gcc gcc-c++ ksh libaio libaio-devel \
libgcc libstdc++ libstdc++-devel make numactl-devel sysstat unixODBC unixODBC-devel

Add following kernel parameters. Add following lines on the /etc/sysctl.conf

fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
#kernel.shmmax = 1054504960
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
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=1048586

Execute following command to reflect the above kernel parameters

/sbin/sysctl -p

Add following lines to /etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

Add following line /etc/pam.d/login

session    required     pam_limits.so

Disable selinux. Modify /etc/selinux/config

SELINUX=disable

Stop iptables on Linux and disable iptables on startup

/etc/init.d/iptables stop
chkconfig iptables off

Execute following command to create OS groups and users.

groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper

useradd -u 502 -g oinstall -G dba,oper oracle
passwd oracle

Create required directories for oracle installation.

mkdir -p /u01/app/oracle/product/11.2.0.4/db_1
chown -R oracle:oinstall /u01/app/oracle

Add following environment variables on .bash_profile

ORACLE_HOSTNAME=OEL1.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
DB_HOME=$ORACLE_BASE/product/11.2.0.4/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_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

Verify Oracle Home is properly set.

source .bash_profile
echo $ORACLE_HOME
cd $ORACLE_HOME
pwd

On terminal window change directory to source and execute ./runInstaller

Once you execute the runInstaller executable above screen will be appeared. Check out "I wish to receive security updates via My Oracle Support"

Click on Next after checkout the security updates option.

Warning message window will be prompt. Click on Yes to continue.

Click on Skip software updates and Click on Next to continue.

Click on Install database software only and Click on Next to continue. 

Click on Single instance database installation and Click on Next to continue.

Select required languages and Click on Next to continue.

Select Database Edition. In my case I have selected Enterprise Edition. Click on Next to continue.

Select appropriate Oracle_Base and Software Location. Click on Next to continue.

Select OSOPER and OSDBA group. I just left default and clicked on Next.

Since we have less swap space then recommended the pre-requisite failed. Select Ignore All and Click on Next.

Warning message will be poped up Simply Click on Yes to continue.

Click on Save Response File and Give appropriate name to save response file.

Click on Install to begin installation.

Installation on progress ##########.

Once execute configuration script window will be displayed. Execute the listed script as user root and Click on OK to continue.

Database installation was successful.  Click on Close to end installation.

1. Install Oracle 11g Grid Infrastructure With Oracle Virtualbox
2. ASM Disk Creation with asmca
3. Install Oracle 11g R2 Software
4. Create Oracle database with ASM Disk Group

Read More

Create CDROM as yum repository

Mount cdrom

mount /dev/sr0 /mnt

Copy repos file from cdrom to the /etc/yum.repos.d directory and change the permission of the repos file.

cp /mnt/media.repo /etc/yum.repos.d/OEL6.3dvd.repo
chmod 644 /etc/yum.repos.d/OEL6.3dvd.repo

List the content ot the repos file

cat /etc/yum.repos.d/OEL6.3dvd.repo

Change gpgcheck to 1 and add following lines and verify the latest content of repos file
cat /etc/yum.repos.d/OEL6.3dvd.repo

baseurl=file:///mnt/
gpgpkey=file:///mnt/RPM-GPG-KEY

Import public key defined on the gpgpkey's value

rpm --import /mnt/RPM-GPG-KEY

Remove public-yum-ol6.repo

rm  public-yum-ol6.repo

Clear all the yum contents

yum clean all

List the current repo on the system.

yum repolist

Verify newly created yum repos by adding a package

yum -y install mc

Read More

Oracle Tips and Tricks I

Check the user profile and set it to unlimited if you have password expired issue. Please execute following SQL statements to set password life time unlimited.

Login as the dba user. In this case I have logged in as the user SYS with OS authentication.

sqlplus / as sysdba

desc dba_users;

Search for the user's profile and alter the profile to set password life time unlimited.

select profile from dba_users where username in ('USER1','USER2');

alter profile default limit password_life_time unlmited;

UNICODE Support on the insert command using Windows CMD.
chcp 65001
set NSL_LANG=.AL32UTF8

Check the Temp segments used by the sessions.

SELECT *
FROM v$sort_usage;

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE, su.segtype, su.CONTENTS
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;

Check the Undo segments used by the sessions.

SELECT s.username, s.SID, s.serial#, s.logon_time, t.xidusn, t.ubafil, d.tablespace_name,
t.ubablk, t.used_ublk, t.start_date, t.status
FROM v$session s, v$transaction t ,dba_rollback_segs d
WHERE s.saddr = t.ses_addr
and t.xidusn=d.segment_id;

Resize Datafiles

ALTER DATABASE DATAFILE '' | FILEID  RESIZE 2M;

AWR generating script

#!/bin/bash

start_snap_id=21997
end_snap_id=22009
db_id=2409431886
file_name_prefix="AWR_"$(hostname);


while [ ${start_snap_id} -le ${end_snap_id} ]
do
        echo ${start_snap_id}
        let stop_snap_id=start_snap_id+1
sqlplus -S utility/oracle< ${file_name_prefix}_${start_snap_id}_${stop_snap_id}.html
        set feedback off;
        set heading off;
        select output from table (dbms_workload_repository.awr_report_html (${db_id},1,${start_snap_id},${stop_snap_id}));
        exit;
EOF
        let start_snap_id=stop_snap_id

done

Getting hostname of the machine

SELECT UTL_INADDR.get_host_name FROM dual;

Oracle setting table column unused and drop unused column later.

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

List all unused columns

SELECT * FROM DBA_UNUSED_COL_TABS;

Drop unused columns

ALTER TABLE table_name drop unused columns;

Read More