Oracle Database Architectural Quick Reference

how to determine an index need to be rebuild

Is an index required to be rebuilt? We can check a post regarding this:

Richard Foote's Oracle Blog

Indexes will occupy a lot of space, so you can check whether it was being used. For this you can check out for v$object_usage. If needed you can also enable Monitoring usage of indexes by using 

Alter index enable Monitoring Usage ; 

If there is Fragmentation of Index then it needs to be rebuilt. Refer the above link mentioned in this discussion which is a very useful one.

We can check if an index needs to be rebuilt by executing the following statements:


ANALYZE INDEX index_name VALIDATE STRUCTURE;

SELECT HEIGHT, DEL_LF_ROWS, LF_ROWS, LF_BLKS FROM INDEX_STATS;
If the value for DEL_LF_ROWS/LF_ROWS is greater than 2, or LF_ROWS is lower than LF_BLKS, or HEIGHT is 4 then the index should be rebuilt.

Installation of Oracle Database 11g on Linux with ASM

Let’s assume that you have already installed Linux on your server. And disks are already bounded to the server.

1. Creating OS groups and users.

#Creating groups for Grid Infrastructure

groupadd asmadmin
groupadd asmdba
groupadd asmoper

#Creating groups for Oracle Software

groupadd oinstall
groupadd dba
groupadd oper

#Creating user for Grid Infrastructure

useradd -g oinstall -G dba,asmadmin,asmdba,asmoper -d /home/grid grid

#Creating user for Oracle Software

useradd -g oinstall -G dba,oper,asmdba -d /home/oracle oracle

#Setting password for users

passwd grid
passwd oracle

2. Creating necessary directories

mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/oracle
chown -R grid:oinstall /u01
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01

3. Creating .bash_profile-s

#For Oracle user

su – oracle
vi .bash_profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

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

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”
export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

umask 022

#For Grid user

su – grid
vi .bash_profile

if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=+ASM; export ORACLE_SID

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/grid; export ORACLE_BASE

ORACLE_HOME=/u01/app/11.2.0/grid; export ORACLE_HOME

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”; export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

umask 022

4. Setting resource limits

Edit the following files:

# /etc/security/limits.conf

[root@orcl ~]# cat >> /etc/security/limits.conf <grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536

oracle soft nproc 2047
oracle  hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

# /etc/pam.d/login

[root@orcl ~]# cat >> /etc/pam.d/login <session required pam_limits.so
EOF

# /etc/profile

[root@orcl ~]# cat >> /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
EOF

# /etc/csh.login

[root@orcl ~]# cat >> /etc/csh.login <if ( \$USER == “oracle” || \$USER == “grid” )
then
limit maxproc 16384
limit descriptors 65536
endif
EOF

# /etc/hosts

[root@orcl ~]#  vi /etc/hosts

127.0.0.1 localhost.localdomain localhost
192.168.34.150 orcl

5. Installing ASMlib and creating disk groups

Download link: http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html

# To know your kernel version

uname -r
2.6.18-194.el5xen

# Download the following files

oracleasm-support-2.1.7-1.el5.x86_64.rpm
oracleasm-2.6.18-308.el5-2.0.5-1.el5.x86_64.rpm

oracleasmlib-2.0.4-1.el5.x86_64.rpm

# Installing

rpm -Uvh oracleasm-support-2.1.7-1.el5.x86_64.rpm
rpm -Uvh oracleasm-2.6.18-308.el5-2.0.5-1.el5.x86_64.rpm
rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

# Configure ASM

/usr/sbin/oracleasm configure -i

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

# Load ASM kernel module

/usr/sbin/oracleasm init

##Partition available disks for ASM

#list available disks and partitions

fdisk -l

#partitioning

fdisk /dev/sdb

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-52216, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-52216, default 52216):
Using default value 52216

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

fdisk /dev/sdc

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-52216, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-52216, default 52216): +100M

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

# Restart the server

init 6

# Creating ASM disks

/usr/sbin/oracleasm createdisk VOL1 /dev/sdb1
/usr/sbin/oracleasm createdisk CRSVOL /dev/sdc1

# List disks

/usr/sbin/oracleasm listdisks
VOL1
CRSVOL

# Testing disk discovery, that is used by DBCA

oracleasm-discover

Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library – Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:CRSVOL [208782 blocks (106896384 bytes), maxio 512]
Discovered disk: ORCL:VOL1 [838849977 blocks (429491188224 bytes), maxio 512]

6. Installing Oracle Grid Infrastructure

Download link: http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_grid.zip

Login as a grid user.

# Unzip and install

mkdir  /home/grid/myinstall
unzip /tmp/linux.x64_11gR2_grid.zip  -d  /home/grid/myinstall

cd /home/grid/myinstall
chmod -R 777 *
./runInstaller

Choose the following options:

6.1. Install and Configure Grid Infrastructure for a Standalone Server

6.2.  English

6.3. English

6.4.
Disk Group Name: CRS
Redundancy: External
Add Disks->Candidate Disks, choose just  ORCL:CRSVOL

6.5. Speciify password(s).

6.6.
ASM Database Administrator(OSDBA) Group : asmdba
ASM Instance Administration Operator(OSOPER) Group: asmoper
ASM Instance Administrator(OSASM) Group: asmadmin

6.7.
Oracle Base: /u01/app/gridSoftware Location: /u01/app/11.2.0/grid

6.8. On the pop-up windows press Yes.

6.9. Inventory Directory: /u01/app/oraInventory

6.10. On the prerequisite checks page, there will be failed checks. To solve:

6.10.1 Press Fix & Check Again
6.10.2 Login as root user and run the following:

[root@orcl ~]# /tmp/CVU_11.2.0.1.0_grid/runfixup.sh

6.10.3 Press OK on the Execute Fixup window

Left just libaio* and unixODBC* packages that should be installed. To solve:

6.10.4 Mount Oracle Enterprise Linux installation disk and go to the Server folder, where rpms are located.

[root@orcl Server]# rpm -Uvh libaio-devel-0.3.106-5.i386.rpm
warning: libaio-devel-0.3.106-5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@orcl Server]# rpm -Uvh libaio-devel-0.3.106-5.x86_64.rpm
warning: libaio-devel-0.3.106-5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:libaio-devel           ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-2.2.11-7.1.x86_64.rpm
warning: unixODBC-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC               ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]
[root@orcl Server]# rpm -Uvh unixODBC-devel-2.2.11-7.1.x86_64.rpm
warning: unixODBC-devel-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing…                ########################################### [100%]
1:unixODBC-devel         ########################################### [100%]

6.10. 5   Press Check Again.(There should not be any error)

6.11.

[root@orcl ~]# /u01/app/oraInventory/orainstRoot.sh
[root@orcl ~]# /u01/app/11.2.0/grid/root.sh

Press enter…

7. Creating ASM disk groups for database

Connect as a grid user.

[grid@orcl ~]$ asmca

7.1 Press Create button.

7.2
Disk Group Name: DATARedundancy: External(None)Select Member Disks->Show Eligible, choose ORCL:VOL1

8. Installing Database, create instance on ASM

Connect as an oracle user.

Download Link: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html?ssSourceSiteId=ocomen

Unzip downloaded file to /home/oracle/myinstall.

[oracle@orcl linux.x64_11gR2_database]$ ./runInstaller

8.1 Uncheck “I with to receive security updates via My Oracle Support”

8.2 On the pop-up window choose yes.

8.3 Install database software only

8.4 Single instance database installation

8.5 English

8.6 Enterprise Edition (4.29GB)

8.7
Oracle Base: /u01/app/oracleSoftware Location:  /u01/app/oracle/product/11.2.0/db_1

8.8
Database Administrator(OSDBA) Group: dba
Database Operator(OSOPER) Group: oper

8.9 I have some failed checks that are fixable so I press Fix & Check Again button, login as a root user and run:

[root@orcl ~]# /tmp/CVU_11.2.0.1.0_oracle/runfixup.sh

Click OK on the Execute Fixup window.

8.10

[root@orcl ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
Press Enter…
y
y
y

9. Run DBCA as an oracle user.

[oracle@orcl ~]$ dbca

and follow the steps (choose ASM instead of File System option)

That’s all.

Source