Oracle Database 11.2.0.3 Installation on RHEL 6

Oracle Installation  of 11.2.0.3 method’s are clearly indicated below. Each and every step is crucial to perform a error free setup.Oracle 11.2.0.3 software binaries are available in Oracle Patch 10404530, This software is not available via Oracle Download’s in oracle.com, they are only available via Oracle Support login. The following Installation is performed on a RHEL 6 64 Bit OS. The software Binaries vary from 32 and 64 Bit, be sure to download the correct software binaries.The files shown below are those for 64 Bit Linux OS. Download only the first two files for installing Database Software.

p10404530_112030_Linux-x86-64_1of7.zip
p10404530_112030_Linux-x86-64_2of7.zip

Let’s get started with the Installation, there are two major parts of any Oracle Database Installation.

1. Operating System Settings
2. Oracle Database Installation

Let’s start with each one of them,

1. Operating System Settings

All settings indicated here are to be performed after RHEL 6 64 Bit OS Installations

• Ensure hostname and IP are set up properly, you will need to do the check same after Linux 64 Bit Installation.

[root@rhel2 ~]# more /etc/hosts
127.0.0.1 localhost
192.168.1.10 rhel1
192.168.1.11 rhel2
[root@rhel2 ~]# ifconfig -a | grep 192
 inet addr:192.168.1.11 Bcast:192.168.1.255 Mask:255.255.255.0
[root@rhel2 ~]# more /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=rhel2
GATEWAY=192.168.1.1
[root@rhel2 ~]#

• Disable Firewall Settings : Firewall Settings can cause network issues while communicating with Applications.

[root@rhel2 ~]# service iptables save
[root@rhel2 ~]# service iptables stop
[root@rhel2 ~]# chkconfig iptables off
[root@rhel2 ~]# service network restart

• Kernel Settings : Kernel Settings are of utmost importance to configure system wide configurable memory and allowable Port Ranges

[root@rhel2 ~]# more /etc/sysctl.conf
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
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=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

• Ensure the /ete/sysctl.conf settings are setup fine, the following command should not throw any error’s

[root@rhel2 ~]# sysctl -p
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
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=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

• Create Users,Groups & Folder Structures

[root@rhel2 ~]# groupadd -g 501 oinstall
[root@rhel2 ~]# groupadd -g 502 dba
[root@rhel2 ~]# groupadd -g 503 oper
[root@rhel2 ~]# groupadd -g 504 asmadmin
[root@rhel2 ~]# groupadd -g 506 asmdba
[root@rhel2 ~]# groupadd -g 505 asmoper
[root@rhel2 ~]# useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
[root@rhel2 ~]# passwd oracle
[root@rhel2 ~]# mkdir -p /u01/app/oracle/product/11.2.0/db_1
[root@rhel2 ~]# mkdir -p /u01/softwares
[root@rhel2 ~]# chown -R oracle:oinstall /u01
[root@rhel2 ~]# chmod -R 775 /u01

• Add the following Entries to /etc/security/limits.conf

/etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240

• Change Settings in /etc/security/limits.d/90-nproc.conf and /etc/selinux/config as shown below

/etc/security/limits.d/90-nproc.conf

# Comment the following Line
* soft nproc 1024

# Include the following Line
* - nproc 16384
/etc/selinux/config
SELINUX=permissive

• Transfer the Oracle Database Media files p10404530_112030_Linux-x86-64_1of7.zip and p10404530_112030_Linux-x86-64_2of7.zip to /u01/softwares

Mount the RHEL 6 64 bit CD or you can mount the ISO image

[root@rhel2 ~]# mkdir -p /media/rhel6iso
[root@rhel2 ~]# mount -o loop /u01/softwares/rhel_6.2_x86_64_disc_1.iso /media/rhel6iso
[root@rhel2 ~]# df -h
The df -h output shows you that the ISO media for RHEL6 64bit ISO has been mounter

The df -h output shows you that the ISO media for RHEL6 64bit ISO has been mounted

• Transfer ( using sftp, ftp in bin mode,scp,filezilla, etc.. ) Oracle Database Media files p10404530_112030_Linux-x86-64_1of7.zip and p10404530_112030_Linux-x86-64_2of7.zip to /u01/softwares. Mount the RHEL 6 64 bit CD or you can mount the ISO image

[root@rhel2 ~]# mkdir -p /media/rhel6iso
[root@rhel2 ~]# mount -o loop /u01/softwares/rhel_6.2_x86_64_disc_1.iso /media/rhel6iso
[root@rhel2 ~]# df -h
----------The df -h output shows you that the ISO media for RHEL6 64bit ISO has been mounter
[root@rhel2 ]# cd /media/rhel6iso/Packages
[root@rhel2 Packages]# touch /u01/softwares/rpm_inst.sh
[root@rhel2 Packages]# echo "cd /media/rhel6iso/Packages" > /u01/softwares/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep gcc >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep cpp >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep ppl >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep cloog >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep libaio >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep libltdl >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep thread >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep libc >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep binutils >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep compat >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep libXp >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep kernel >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep libaio >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep make >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep gdbm >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep unux >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep sysstat >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep ksh >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep std >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep elf >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep mpfr >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep unixODBC >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep std >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep sysstat >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# ls -l | awk '{ print $9 }' | grep gomp >> /tmp/rpm_inst.sh
[root@rhel2 Packages]# chmod +x /u01/softwares/rpm_inst.sh
-------- Use vi Editor and include rpm -ivh at the beginning of every line (except first line) using esc :s/^/rpm -ivh /g
[root@rhel2 Packages]# sh /u01/softwares/rpm_inst.sh
-------- Rerun the script about 6 times or so that all dependency rpms will be installed

2. Oracle Database Installation

• Login as Oracle User and set the Environment Variables in  /home/oracle/.bash_profile

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

ORACLE_HOSTNAME=rhel1; export ORACLE_HOSTNAME
ORACLE_UNQNAME=db11g; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=db11g; export ORACLE_SID

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/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

/u01/softwares/database/stage/cvu/cv/admin
CV_ASSUME_DISTID=OEL4
to
CV_ASSUME_DISTID=OEL6
More information see metalink [ID 1304727.1]

• In case you are directlly working on the server Set “xhost +” as root user and start working from oracle user.
• In case you are working from a Ubuntu client, login to oracle as “ssh -X oracle@192.168.1.11“
• In case you are connecting to oracle user from a Windows PC to the Serve through putty, install Xmanager Software and export DISPLAY=<IP_of _your_PC>:0.0 and start XManager in the Start Tray

• Start Installation of Oracle with software only option ( You will need to run a couple of scripts as root user at the End of the Installation )

[oracle@rhel2 ]# cd /u01/softwares/database
[oracle@rhel2 database]# ./runInstaller
[root@rhel2 ~]# /u01/app/oraInventory/orainstRoot.sh
[root@rhel2 ~]# /u01/app/oracle/product/11.2.0/db_1/root.sh
[root@rhel2 ~]#

•  Create Database using dbca, use the GUI to set up all parameters for Database Creation, the Listener should be running at this time.

</pre>
 [oracle@rhel2 ]# dbca
 --------- Now the database should be up and running
 [root@rhel2 ~]# su - oracle
 [oracle@rhel2 ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 15 02:55:17 2013

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

Enter user-name: sys as sysdba
 Enter password:

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

SQL> select instance_name,status from v$instance;

 INSTANCE_NAME    STATUS
 ---------------- ------------
 db11g            OPEN

SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 [oracle@rhel2 ~]$ ps -ef|grep pmon
 oracle 440 359 0 02:55 pts/1 00:00:00 grep pmon
 oracle 23926 1 0 Jul14 ? 00:00:12 ora_pmon_db11g

That brings to the End of Oracle Software Installation & Database Creation.

Tablespace information in Oracle

Every DBA evolves his/her own style over a period of time. They build handy scripts  to carry out each and every activity in the database, including monitoring jobs, gathering tablespace information etc etc. Here is one particular script that I use to gather free/utilized space information on tablespaces.

SELECT A.TABLESPACE_NAME, A.TOTALSPACE TOTALSPACE,(A.TOTALSPACE - B.FREESPACE) USEDSPACE, B.FREESPACE FREESPACE FROM
 (SELECT TABLESPACE_NAME,SUM/1024/1024/1024 TOTALSPACE FROM DBA_DATA_FILES where TABLESPACE_NAME in (select tablespace_name from dba_segments where segment_name in ('SET OF TABLES T1','T2','T3'.'T4'))GROUP BY TABLESPACE_NAME) A,
 (SELECT TABLESPACE_NAME,SUM/1024/1024/1024 FREESPACE FROM DBA_FREE_SPACE where TABLESPACE_NAME in select tablespace_name from dba_segments where segment_name in ('SET OF TABLES T1','T2','T3'.'T4')) GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME =B.TABLESPACE_NAME ;

Sample Output for the above script will be

TABLESPACE_NAME       TOTALSPACE        USEDSPACE       FREESPACE
GPAPP                                           101                      92.4963989       8.50360107
PSINDEX                                      167                     150.486206       16.5137939

Hope it will be useful for you. Comments are most welcome.

“Appreciation is a wonderful thing:It makes what is excellent in others belong to us as well”

Creating database in oracle

In oracle, database creation can be done in two ways. We can create database automatically along with the installation and the other way is to create the database Manually. Automatic creation is the easiest way.Because all  files and folders  are maintained under the ORACLE_HOME directory,But for administrating all the things  as a adminstrator most of the people  prefer to create database by manaually.Let me explain, how to create the database manaully.

Here are  the steps to create database manually,

1)Create a folder with the name of database name.

2)Create five folder under the database folder as pfile, bdump, udump,cdump, control.

3)Create a file name called init.ora in the pfile folder.

4) Create init.ora file using the following paramter and  also don’t forget to save along with its extension.

db_name=<INSTANCE_NAME>
compatible=10.2.0
background_dump_dest='<drive>:\<foldername>\bdump\’
core_dump_dest='<drive>:\<foldername>\cdump\’
user_dump_dest='<drive>:\<foldername>’
control_files='<drive>:\<foldername>\control.ctl’
remote_login_passwordfile= exclusive
sga_target=700m

5)Connect to the sql prompt using sqlplus.

SQL> Conn sys/sys as sysdba.

6)Before creating the database.Intilize the memory using the pfile at no mount stage using

SQL>startup pfile='<drive>:\<foldername>\pfile\init<dbname>.ora’ nomount.

7)And then Create the database using this statements.Edit the statement according to your creation diretory.
CREATE DATABASE   <SID>
    maxdatafiles  1021
    maxinstances  1
    maxlogfiles   8
    maxlogmembers 4
    CHARACTER SET WE8ISO8859P15
    NATIONAL CHARACTER SET UTF8
    DATAFILE ‘<drive>:\oradata\<SID>\system01.dbf’ SIZE 2000M
    REUSE
    AUTOEXTEND ON
   NEXT 10240K
   MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE ‘<drive>:\oradata\<SID>\sysaux01.dbf’ SIZE 120M
   REUSE
  AUTOEXTEND ON
  NEXT  10240K
  MAXSIZE UNLIMITED
  LOGFILE GROUP 1 (‘<drive>:\oradata\<SID>\redo01.log’) SIZE 100M,
        GROUP 2 (‘<drive>:\oradata\<SID>\redo02.log’) SIZE 100M,
        GROUP 3 (‘<drive>:\oradata\<SID>\redo03.log’) SIZE 100M;

It will automatically comes to the open state once when the creation gets completed.Check with the below query to conform it.

SQL> Select open_mode from v$database.

It show that the database is in read-write mode.

I hope it will be more useful to create database manaually .

changing DBID in oracle database

While cloning the database using RMAN. We usually face the problem in DBID.After cloning a database it change only the name of the database but  DBID remains as same before.It reflects in various concern. I have faced this issue while  exporting table from cloned database to another database.In order to change the dbname along with DBID we have option.We can use oracle utilities called as NID.Using NID we can change database name without changing the DBID

otherwise we can change only the database name. But there is no option to change only the DBID using the NID utility.

If we want to change only the DBID. Follow these steps which is given below

NOTE: Before any major or significant changes to a database, you should perform a cold (closed) backup.

Steps to change the DBID.
To Change an Oracle Database Identifier (DBID)

1. Connect to the database in SYSDBA mode.
2. Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.

shutdown immediate

3. Startup the database in mount mode

statup mount

4. Check the present (soon to be old) database ID (DBID)

select dbid, name from v$database;

Output:
DBID                    NAME

——————   ————-

578456994  HR9DEMO

5. Generate the create controlfile statement into a trace file.

alter database backup controlfile to trace;

6. Generate a new DBID using the zeroDbid function of dbms_backup_restore

execute dbms_backup_restore.zeroDbid(0)

File: DBMSBKRS.SQL

NOTE: The value zero (0) is a special case, which is the case we need.

7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles .

9. Start up the database in nomount mode.

startup nomount

10. Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).

Change from:
CREATE CONTROLFILE REUSE DATABASE “SNEAKERS” NORESETLOGS NOARCHIVELOG

Change to:
CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

11. Open the database.

alter database open resetlogs;

12. Check the new DBID. It should be different.

select dbid, name from v$database;

Now you can find the different DBID in the database.