Configure Toad and SQL developer with Oracle Database 11gR2

Oracle Database 11gR2 has built-in SQLPLUS batch query tool (A command line user interface). You may use Oracle SQL developer (ntegrated development environment (IDE) for working with SQL in Oracle databases) or Toad for Oracle (tool from Quest Software (www.quest.com) for creating and testing PL/SQL code) instead also. We have installed Oracle Database 11gR2 11.2.0.4 on Oracle linux 6 update 6 with ASM in Previous post.

In this post I am going to configure Toad for oracle and Oracle developer. Toad and SQL developer are more user friendly and has more features.

Before installing Toad you need to install Oracle database 11gR2 Client on your Workstation (Windows 10, Windows 8, Windows 7 etc....). Your workstation and Oracle database server machine has to be on the same network. 

Toad for Oracle:

Download Toad from http://software.dell.com/products/toad-for-oracle/

Toad need Oracle database client to connect to oracle  database You can download the Oracle database client for windows from Oracle. 

Install the client 
Copy then contents of tnsname.ora from /u01/app/oracle/product/11.2.0/db_1/network/admin/ (Database Server)

to
C:\app\araza\product\11.2.0\client_1\network\admin (Windows Machine where you installed Toad)

Just change the IP Address instead of host name. You can leave host name as it is if your DNS server is same for both client machine and Oracle Database machine to resolve the host-name of database server.

Start Toad for Oracle

And you are connect to Oracle Database 11gR2 11.2.0.4 on Oracle Liuux 6. Check with select statement and all is well. 

SQL developer:

For SQL developer you don't need Oracle Database client. Download SQL developer with JDK 8 from Oracle. 

Before running sqldeveloper.exe make sure the Database listener is running on Oracle Database server by running following command. 

[oracle@oel6]$ lsnrctl status

Listener is running. If you face no listener then start the listener by running following command.

[oracle@oel6]$ lsnrctl start

Run sqldeveloper.exe from installed folder.

Click + button

Click Connect and you will see following. 

Lets check with Select statement.

We have successfully configured Toad for Oracle and Oracle SQL Developer. 

That's All

Cheers-:) 

How to Install Oracle Database 11g R2 on Oracle Linux 6 with ASM

In this demo we are going to install oracle database 11gR2 with ASM (Automatic Storage Management) on Oracle Linux 6 virtual machine which we have created in previous post.

First we have shutdown the Oracle Linux 6 VM to create virtual disks for ASM disk groups. we are going to create three virtual hard disks.

Open Virtual machine Settings and create new virtual hard disks.

Power on the virtual machine and login as root. Now we have to create required groups and user for Grid Infrastructure and set permissions.

Create Directories for Oracle Database installation

We have to assign oracle user also to asmdba group in order to allow oracle user to discover asm disks during the database creation. Oper group will be used by oracle software.

(solution for an issue while dbca doesn’t see ASM disks, this issue might happen when we use separate accounts for managing ASM as a grid and an oracle account to manage database instance)

Now we have to create login script for gird and oracle user. 

To create login script for grid user switch the user to grid and edit .bash_profile

Copy and paste following in .bash_profile for grid user and save the file.

#----------------------------------------------------------------------------------------------------------
#.bash_profile
# ---------------------------------------------------
# OS User: grid
# Get the aliases and functions
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

#-----------------------------------------------------------------------------------------------------

Switch to oracle user and edit .bash_profile

Copy and paste following in .bash_profile for oracle user and save the file

# .bash_profile
# ---------------------------------------------------
# OS User: oracle
# Application: Oracle Database Software Owner
# Version: Oracle 11g Release 2.4
# ---------------------------------------------------
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

ORACLE_SID=DB11G; export ORACLE_SID

ORACLE_UNQNAME=DB11G; 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
#----------------------------------------------------------------------------------------------

Now Verify if Oracle ASM package is installed. it must be installed as we have selected it during Oracle Linus VM creation. 

we need one more package which is not included in Oracle Linux 6 DVD. Download it from ASMLib for Oracle Linux 6 and install.

Now we have to configure ASM library driver as user root.

Check if your disks are available

Creating partition for disks we are going to use for ASM

DO the same as above for /dev/sdc and /dev/sdd

fdisk /dev/sdc

n,p,1,1,w

fdisk /sdd

n,p,1,1,w

System restart is required to apply the changes.

Creating ASM Disks

Test Disks Discovery

We are ready to install Grid infrastructure. 

Logout and login with grid user on virtual machine

Extract Grid installation folder in /home/grid/Downloads

You will have grid folder after extraction.

If you face error. click fix and check again. Run the given script as root. 

Click ok

Run root scripts as root

You will see " Successfully configured Oracle Grid Infrastructure for a standalone server"

At this moment we have Oracle ASM instance and related services up and running:

To verify the installation execute the following as user grid.

Check sqlplus

Create ASM disk group for Oracle Database Installation (+FRA and +DATA)

As a grid user run asmca (ASM Configuration Assistant)

Now we have to install the Oracle database 11gR2. Logout and login with user root and change the password for user oracle as we didn't set the password for user oracle

.

Logout and login with user oracle and Extract the database folder to /home/oracle/Downloads and run the installer. 

Run Script as user root.

Oracle Database 11gR2 software has been installed successfully.  Now we have to create the database using dbca.

Pay attention to database control file location in initialization parameter configuration step (place control files on ASM disks +DATA and copy on +FRA)

("+DATA/{DB_UNIQUE_NAME}/control01.ctl", "+FRA/{DB_UNIQUE_NAME}/control02.ctl")

Close and Click Next

Database has been created successfully. Now we can use sqlplus to connect to database or Enterprise Manager Database Control from a browser. 

Lets Check sqlplus

Open Enterprise Manager Database Control from a browser. 

That's all. We have successfully installed oracle database 11gR2 11.2.0.4 with ASM on a Stand alone Oracle Linux server 6.

Cheers-:)