How important is having Unix/Linux OS Knowledge for Oracle DBAs?

Hello,

I wanna wish you all a very Happy New Year 2009 before being discussed on “How Important Having UNIX OS Knowledge for Oracle DBA?”

The main reason for discussing this topic is to encourage the novice or junior DBAs to have command on Unix/Linux Operating System as part of Database Administration. Because, when they were asked how comfortable they are working as DBA on Unix/Linux environment, then their responses are as following.

“They are pretty much comfortable working and administrating the databases on Windows Operating System as they were trained on administrating the database on Windows OS during their DBA Training”

“They are not enough confident in using the Unix/Linux OS commands to administer the databases”

“We didn’t get the chance yet to maintain the database on UNIX environment including Solaris, HP-Unix, IBM AIX, etc.”

“Working on windows environment is easier than working on Unix/Linux environment as it’s only required, on Windows OS, to select the files physically/copy/cut & paste from one location to another, or delete the files, and stopping the services by simply clicking, where as in Unix/Linux, one should use commands manually for the same”

…. and so on.

If anyone is with the above impression about administrating databases on Unix/Linux OS, then here are my suggestions as follows.

• First of all, it’s not compulsory for DBAs to have operating system administration skills. But, working knowledge, on both the operating systems including Windows and Unix/Linux, is always value added to Oracle DBAs
• The majority of the production database administration on Unix/Linux based operating systems is enormous in the globe, i.e. very large and mission critical databases are being maintained on Unix/LinuxOS.
• Obviously, all big companies require the database administrator who’s having good UNIX scripting knowledge and working knowledge of UNIX based environments. It means that the Oracle DBAs, having good working knowledge of UNIX, have demand in the IT market.
• Even though the DBAs intervention is manual in terms of creating directories, files, setting up environment variables, and starting/stopping the services in Unix environment, it’s very easy as good as maintaining in Windows environment.
• Administration and OS commands are more or less similar on any flavor of Unix/Linux Operating system i.e. Solaris, HP-Unix, IBM AIX, Red Hat, Fedora, Ubuntu, etc., So, start practicing Oracle database administration on Unix/Linux based systems.
• Probably, it may be difficult to have Unix OS installed in the personal computer for practical purpose. So, download the free/trail version of Linux (Oracle Unbreakable Linux) from Oracle, install and configure in the PC, and start practicing on Linux. To practice on different Unix/Linux OS flavors, get your pc installed with the VM Ware, where you can set up multiple and different flavors of Unix/Linux OS.
• Note: Of course, this post is not for showing the difference between Windows and Unix/Linux Operating Systems as every OS has its own significance, but to encourage the Oracle DBAs to learn the commands of Unix/Linux required administering the databases.

Quick Links:

Download: Enterprise Linux and Oracle VM

Information: Oracle and Linux

Installation Guides: Oracle 10g Instalaltion on Unix/Linux OS

Books: Oracle9i UNIX Administration Handbook

Linux Recipes for Oracle DBAs

Training: Oracle Database 10g: Managing Oracle on Linux for DBAs

Exam: Oracle Database 10g: Managing Oracle on Linux Certified Expert Oracle

** Your comemnts and inputs are welcomed to this post. **

Regards,

Sabdar Syed

Setting an Oracle Environment variable – ORACLE_HOME

Hello,

When I check my blog hit counter statistics, my blog is being hit and searched by the blog users with the word “Oracle Home”, but, looks like they are not finding the required information on “Oracle Home” Environment variable. So, I wanted to explain about ORACLE_HOME in my simple terms which is helpful for novice.

What is ORACLE_HOME used for?

* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.
* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.
* This directory can be used by any user who wants to use the particular database.
* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.
* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:
ORACLE_HOME=$ORACLE_BASE/product/10.2.0.

What is ORACLE_BASE used for?

* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.
* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.

Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.

How to check if ORACLE_HOME is set already?

On Unix/Linux Systems:

Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.

The user profile file can be
.bash_profile – Bash Shell
.profile – Bourne Shell or Korn shell
.login­ – C Shell

Note: This user profile file will be under user’s home directory i.e. $HOME/.bash_profile

To check specific environment variable set:

$ echo $ORACLE_HOME

To check all the environment variables set:

$ env

On Windows Systems:

To check specific environment variable set:

C:\> set ORACLE_HOME

OR

C:\echo %ORACLE_HOME%

To check all the environment variables set:

C:\> set

Or

C:\> env

Other way, to check the ORACLE_HOME, is as follows.

Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE

i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:

To find the ORACLE_HOME path in Oracle Database

How to set the ORACLE_HOME environment variable?

On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile

ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

Source the user profile as follows:

Bash shell:

$ . ./.bash_profile

Bourne shell or Korn shell:

$ . ./.profile

C shell:

% source ./.login

If no profile file is set with environment variables, then physically also be set as follows:

Bourne, Bash, or Korn shell:

$ ORACLE_BASE=/oracle/app
$ export ORACLE_BASE
$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0
$ export ORACLE_HOME

C Shell:

% setenv ORACLE_BASE /oracle/app
% setenv ORACLE_HOME /oracle/app/product/10.2.0

On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

Another way to physically set the variables as follow at the DOS prompt:

C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%

Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

Do not invoke SQL*Plus with a password On UNIX and Linux platforms.

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat.

For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server.

$ sqlplus apps/apps@proddb

Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below.

$ ps -efgrep sqlplus
oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb
oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus

So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials.

Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line.

$ sqlplus apps@proddb
Enter password: ****

Or, even not to expose the username and connecting string.

$ sqlplus
Enter user-name: apps@proddb
Enter password: ****

Or

$ sqlplus /nolog
SQL> connect apps@proddb
Enter password: ****

And also, do not use the password while invoking Export/Import Utility using exp/imp command line, and for any other command line utilities which you think the password will be exposed to others.

On Microsoft Windows, the command recall feature (the Up arrow) remembers user input across command invocations.

For example, if you use the CONNECT APPS/password notation in SQL*Plus, exit, and then press the Up arrow to repeat the CONNECT command, the command recall feature discloses the connect string and shows the password. So, it is advice *NOT* to pass the password while connecting to SQL * Plus on windows as well.

Your comments are welcomed.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

How to check the Physical RAM and Swap Space in Unix/Linux Systems.

Following are the most requred OS commands while installing and configuring Oracle Software on Unix/Linux Operating Systems.

Operating System: AIX 5L Based Systems (64-Bit)
Physical RAM: # /usr/sbin/lsattr -E -l sys0 -a realmem
Swap Space: # /usr/sbin/lsps -a

Operating System: HP Tru64 UNIX
PPhysical RAM: # /bin/vmstat -P grep "Total Physical Memory"
Swap Space: # /sbin/swapon -s

Operating System: HP-UX Itanium
Physical RAM: # /usr/contrib/bin/machinfo grep -i Memory
Swap Space: # /usr/sbin/swapinfo -a

Operating System: HP-UX PA-RISC (64-Bit)
Physical RAM: # grep "Physical:" /var/adm/syslog/syslog.log
Swap Space: # /usr/sbin/swapinfo -a

Operating System: IBM zSeries Based Linux, LinuxItanium/POWER/x86/x86-64
Physical RAM: # grep MemTotal /proc/meminfo
Swap Space: # grep SwapTotal /proc/meminfo

Operating System: Solaris SPARC 64-Bit/x86/x86-64
Physical RAM: # /usr/sbin/prtconf grep "Memory size"
Swap Space: # /usr/sbin/swap -s

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Group and User Creation in Solairs.

This post is for creating an Unix Group and a User.

Solaris 5.10
--------------------------------------
Creating a Group:
--------------------------------------

For instance: Group - dba

Login as ROOT User

login: root
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
#

To Check whther the group is already created.

# grep dba /etc/group
or
# cat /etc/groupgrep dba

If dba group info is not displayed, then

To create a Group:

# groupadd dba

Syntax: groupadd -g

# cat /etc/groupgrep dba

dba:103:

Note: The gid, which needs to be changed, should be not being the gid of other group.
Example:
# usermod -g 333 dba
UX: groupmod: ERROR: 103 is already in use. Choose another.

To remove a Group:

# groupdel dba

# cat /etc/groupgrep dba

Commands:
---------

Create Group - groupadd
Modify Group - groupmod
Remove Group - groupdel

For more details, check the man pages i.e. man groupadd, man groupmod, and man groupdel

--------------------------------------
Creating a User Account:
--------------------------------------

For instance: User - Oracle

Login as ROOT User

login: root
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
#

To Check whther the oracle user is already created.

# grep oracle /etc/passwd
Or
# id –a oracle

If Oracle group info is not displayed, then

To create a User:

# useradd –d /export/home/oracle –m -g dba -s /bin/sh oracle


Note: In the above command
Option -Discription -Value
------------- -------------------- --------------------
-d -Home for the Oracle user -/export/home/oracle
-m -For making directory
-g -Primary Group dba
-s -Default Shell for the User -/bin/sh
Login -Name -Oracle

# grep oracle /etc/passwd
oracle:x:100:100::/export/home0/oracle:/bin/sh

To modify a User:

For example: To Comment the user and assing secondray group to the user

# usermod -c "Owner of the Oracle Software 10g" -G oinstall oracle

Note: In the above command
Option -Discription -Value
------------- -------------------- --------------------
-c -Comment -"Owner of the Oracle Software"
-G -econdary -Group oinstall


# grep oracle /etc/passwd
oracle:x:100:100:Owner of the Oracle Software 10g:/export/home0/oracle:/bin/sh

To set a password:

# passwd -r files oracle
New Password: XXXXXXXX
Re-enter new Password: XXXXXXXX
passwd: password successfully changed for oracle

Note: Where XXXXXXXX is the new password for oracle User.

To remove a user:

# userdel -r oracle

Note: The "-r" option is used to remove the user's home directory from the system.
And the Oracle user account should not be removed until and unles required, and
this should not be done on Production Servers.

# grep oracle /etc/passwd


Commands:
---------
Create a user - useradd
Modify a user - usermod
Rmove a user - userdel

seta password to the user- passwd

For more details, please check the man pages in UNIX i.e. man useradd, man usermod, and man userdel


Regards,
Sabdar Syed.