SQL Plus Commands

 To Run SQL Commands in Oracle. In env variables we need to set the following variables after installation.

1. ORACLE_HOME :  This the home directory of oracle client where we installed the software.

2. TNS_ADMIN :  This the path where tnsnames.ora file and sqlnet.ora file stored.  This will be ideally

$ORACLE_HOME\network\admin.

3. PATH :  This path variable should contain bin diretory of oracle. This directory contains all the utilites or tools available.

This path will be $ORACLE_HOME\bin

These three variables are necessary for oracle client setup. we will get the below error if we do not set the variables properly.

'sqlplus' is not recognized as an internal or external command,

'tnsping' is not recognized as an internal or external command,

4. Ensure that TNS Listner service is running. 

Otherwise we will get below error.

TNS-12541: TNS:no listener

Once all pre-requsites are ready we can test using tnsping

tnsping TestDB 

to check whether database available

1) To Connect as admin from SQL Plus

           Sample:   sqlplus username/password@TestDB AS SYSDBA

2) To Connect as user using sqlplus

Configure the Oracle 19c Client with Oracle Wallet Manager or Command Utility

 

Below are the steps to install oracle 19c client in UNIX server.

1) To ensure successful database,clinet,and grid infrasturctue installation ,set IOCP to available

2) Check you have atleast 5 GB free space so that there will not be any issue during instllation

use the command df -g .

3) Check the ulimit value should be set to unlimited.

4) Check the permission to run the command oslevel -s

It should display the UNIX version

Also the user who is installing oracle should have read  permission to 

/etc/oraInst.loc

5) Go to Oracle site and download the client.Download the highlighted one.

    https://www.oracle.com/database/technologies/oracle19c-aix-193000-downloads.html 

6) After downloading place the file oracle folder that needs to be installed and provide the directoy to 775 permissions or create a directory inside that

mkdir -m 775 Oracle19c

chmod 775 Oracle19c

7) Unzip the copied software using the unzip command

unzip AIX.PPC64_193000_client.zip

8) After unzip the files go to client/response directory

take backup of the file client_install.rsp

Then edit using vi editor. Change the below properties

UNIX_GROUP_NAME -- This is the group of who owns the current folder

INVENTORY_LOCATION -- This oracle invetory location which store the oracle installations version

This path will be in /opt/sw/oracle/app/oracle/oraInventory

ORACLE_HOME -- This is oracle home for the oracle 19c where you have bin directory and other tns files saved here.

Ideally this path will be set to /opt/sw/oracle/product/19.3.0/client/home

ORACLE_BASE -- This is oracle path when login to the server. This is ideally /opt/sw/oracle

oracle.install.client.installType=Runtime

9) Then go to the ORACLE_BASE path which is /opt/sw/oracle

create a temp directory and go to temp run the command umask 022.

This will set the permssions to rw-r-r on the current temp directory

unset ORACLE_HOME

unset ORACLE_BASE

unset ORACLE_SID

unset TNS_ADMIN

9)  After running this go the client path where we have unzipped the files.

/opt/sw/oracle/software/Oracle19c/client

run the command

./runInstaller -silent -responseFile /opt/sw/oracle/software/Oracle19c/client/response/client_install.rsp

10) This will install successfully 

11)  After update the .profile to use the new oracle client.

12) restart the profile using . ./.profile then the installation completed.

13) Change the settings in sqlnet.ora to point the oracle client wallet to create trust between server and client. 

14) Ensure that Oracle Client wallet installed successfully and create the java keystore to create trust between oracle server and client. This can be configured using oracle wallet manager or orapki

15) update the tns entires in the tnsnames.ora file

16) run the comand TNSPING connectionname

18 ) Connect using sqlplus to test connectivity. if it connected succesfully it will show which client and sqlnet.ora file it used to connect to the database.