Oracle DBA Interview Questions Answered: UNIX

View Also: Oracle DBA Interview Questions Answered: Technical

1. Q: How do you automate starting and shutting down of databases in Unix/Linux?

A: One of the approaches is to use dbstart and dbshut scripts by init.d
Another way is to create your own script. To do that, create your own script “dbora” in /etc/init.d/ directory

# touch /etc/init.d/dbora

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
# Applies to Orcle 10/11g
#
# Set ORA_HOME
# Set ORA_OWNER

ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: Error $ORA_HOME/bin/dbstart doesn't exist, cannot start "
exit
fi

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the Oracle databases:
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
rm -f /var/lock/subsys/dbora
;;
esac

Edit the “/etc/oratab” file and set the start flag of desired instance to ‘Y’

MYDB1:/u01/app/oracle/product/10.2.0:Y

#Add dbora to init.d
[root@host ~]# chkconfig --add dbora

#Set the right permissions
chmod 750 /etc/init.d/dbora

 

Q: How do you see how many oracle database instances are running?

2. Q: How do you see how many oracle database instances are running? ::

A: Issue the following command “ps -ef |grep pmon”

[oracle@host ~]$ ps -ef |grep pmon |grep –v grep
oracle    7200     1  0 21:16 ?        00:00:00 ora_pmon_my_db_SID
oracle    9297  9181  0 21:42 pts/0    00:00:00 grep pmon

This will show within the paths returned the names of all instances (if you are OFA compliant – Oracle Flexible Architecture).

#Count them all:
[oracle@host ~]$ ps -ef |grep pmon |grep –v grep |wc -l
1

3. Q: You have written a script my_backup.sh to take backups. How do you make it run automatically every week? ::
The Crontab will do this work.

Crontab commands:
crontab -e      (edit user’s crontab)
crontab -l      (list user’s crontab)
crontab -r      (delete user’s crontab)
crontab -i      (prompt before deleting user’s crontab)

Crontab syntax :
crontab entry consists of five fields: day date and time followed by the user (optional) and command to be executed at the desired time

4. Q: What is OERR utility? ::

Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files.

Oerr is installed with the Oracle Database software and is located in the ORACLE_HOME/bin directory.

Usage: oerr facility error

Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, “ora” is the facility and “7300”
is the error.  So you should type “oerr ora 7300”.

If you get LCD-111, type “oerr lcd 111”, and so on. These include ORA, PLS, EXP, etc.
The error is the actual error number returned by Oracle.

Example:

$ oerr ora 600
ora-00600: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
*Cause:  This is the generic internal error number for Oracle program
exceptions.  This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug - the first argument is the internal error number

5. Q: How do you see Virtual Memory Statistics in Linux?::
A: There is several ways to check mem stats: cat /proc/meminfo, top, free, vmstat…

“cat /proc/meminfo”

[user@host ~]$ cat /proc/meminfo
MemTotal:      5974140 kB
MemFree:       1281132 kB
Buffers:        250364 kB
Cached:         754636 kB
SwapCached:      68540 kB
Active:        3854048 kB
Inactive:       599072 kB
HighTotal:     5111744 kB
HighFree:      1018240 kB
LowTotal:       862396 kB
LowFree:        262892 kB
SwapTotal:     2096472 kB
SwapFree:      1880912 kB
Dirty:             364 kB
Writeback:           0 kB
Mapped:        3494544 kB
Slab:           203372 kB
CommitLimit:   5083540 kB
Committed_AS: 16863596 kB
PageTables:      19548 kB
VmallocTotal:   106488 kB
VmallocUsed:      3536 kB
VmallocChunk:   102608 kB
HugePages_Total:     0
HugePages_Free:      0
Hugepagesize:     2048 kB

“top”
[user@host ~]$ top (sort by memory consumption by pressing SHIFT+O and then press “n”)

“free”
[user@host ~]$ free
total       used       free     shared    buffers     cached
Mem:       5974140    4693336    1280804          0     250364     754636
-/+ buffers/cache:    3688336    2285804
Swap:      2096472     215560    1880912

TIP: free -m shows results in MB

"vmstat"
[user@host ~]$ vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
0  0 215560 1281652 250364 754636    0    0    18    15    1     2  3  2 95  0
6. Q How do you see how much hard disk space is free in Linux?::

A: “df” – reports filesystem disk space usage

TIP: “df -h” shows results in human readable format M/G/T

[user@host ~]$ df -h
Filesystem             Size   Used  Avail Use% Mounted on
/dev/mapper/lvroot     4.3G   3.3G   723M   2% /
/dev/sda1              104M    14M    85M  14% /boot
none                   3.1G      0   3.1G   0% /dev/shm
/dev/mapper/lvtmp      2.2G   209M   1.8G  11% /tmp
/dev/mapper/lvvar      2.2G   267M   1.8G  14% /var
/dev/mapper/lvoracle    38G    11G    26G  31% /u01

7. Q: What is SAR?::
Huh It could be anything;
SAR stands for Specific Absorption Rate, which is the unit of measurement for the amount of RF energy absorbed by the body when using a mobile phone.
SAR is an active remote sensing system; SAR antenna on a satellite that is orbiting the Earth and so on…

The question should be rather like this: What does sar command do in UNIX/LINUX like systems?

A: sar – Collect, report, or save system activity information.

[user@host ~]$ sar
Linux 3.6.10-26.0.0.0.2.ELsmp (host.domain.local)        02/23/2021
01:30:01 PM       CPU     %user     %nice   %system   %iowait     %idle
...
04:10:01 PM       all      2.00      0.00      2.05      0.08     95.86
04:20:02 PM       all      2.70      0.00      2.30      0.07     94.93
Average:          all      2.73      0.00      2.28      0.16     94.83

TIP: ls -la /var/log/sa/sar* ; man sar
More info: http://computerhope.com/unix/usar.htm

8. Q: What is SHMMAX?::

A: shmmax — maximum size (in bytes) for a UNIX/Linux shared memory segment
DESCRIPTION (docs.hp.com)
Shared memory is an efficient InterProcess Communications (IPC) mechanism.
One process creates a shared memory segment and attaches it to its address space.
Any processes looking to communicate with this process through the shared memory segment, then attach the shared memory segment to their corresponding address spaces as well.
Once attached, a process can read from or write to the segment depending on the permissions specified while attaching it.

How to display info about shmmax:

[user@host ~]$ cat /etc/sysctl.conf |grep shmmax
kernel.shmmax=3058759680

9. Q: Swap partition must be how much the size of RAM?::

A: A tricky question coz opinions about this are always a good topic for any discussions.
In the past; once upon the time, when systems used to have 32/64 or max 128Mb of RAM memory it was recommended to allocate as twice as your RAM for swap partition.
Nowadays we do have a bit more memory for our systems.
An advice: always take software’s vendor recommended settings into account and then decide.
For example, Oracle recommends always minimum 2GB for swap and more for their products – depends of the product and systems size.

Example of ORACLE Database 11g recommendations:
Amount of RAM             Swap Space
Between 1 GB and 2 GB     1.5 times the size of RAM
Between 2 GB and 16 GB     Equal to the size of RAM
More than 16 GB         16 GB

Another common example:
Equal the size of RAM, if amount of RAM is less than 1G
Half the size of RAM for RAM sizes from 2G to 4G.
More than 4G of RAM, you need 2G of Swap.

TIP: To determine the size of the configured swap space in Linux, enter the following command:

[user@host]~ grep SwapTotal /proc/meminfo
SwapTotal:     2096472 kB

To determine the available RAM and swap space use “top” or “free”.

10. Q: What is DISM in Solaris?

A: DISM = Dynamic Intimate Shared memory, which is used to support Oracle in Solaris Environment.

DISM is only supported from Solaris 9 and above version (not recommended to use in older version).
Until Solaris 8, only ISM (Intimate Shared Memory) which is of 8kb page size, from Solaris 9 Sun has introduced a new added feature which is DISM, which supports up to 4mb of page size.

Intimate Shared Memory

On Solaris systems, Oracle Database uses Intimate Shared Memory (ISM) for shared memory segments because it shares virtual memory resources between Oracle processes. ISM causes the physical memory for the entire shared memory segment to be locked automatically.
On Solaris 8 and Solaris 9 systems, dynamic/pageable ISM (DISM) is available. This enables Oracle Database to share virtual memory resources between processes sharing the segment, and at the same time, enables memory paging. The operating system does not have to lock down physical memory for the entire shared memory segment.

Oracle Database automatically selects ISM or DISM based on the following criteriA:

– Oracle Database uses DISM if it is available on the system, and if the value of the SGA_MAX_SIZE initialization parameter is larger than the size required for all SGA components combined. This enables Oracle Database to lock only the amount of physical memory that is used.
– Oracle Database uses ISM if the entire shared memory segment is in use at start-up or if the value of the SGA_MAX_SIZE parameter is equal to or smaller than the size required for all SGA components combined.

Regardless of whether Oracle Database uses ISM or DISM, it can always exchange the memory between dynamically sizable components such as the buffer cache, the shared pool, and the large pool after it starts an instance. Oracle Database can relinquish memory from one dynamic SGA component and allocate it to another component.
Because shared memory segments are not implicitly locked in memory, when using DISM, Oracle Database explicitly locks shared memory that is currently in use at start-up. When a dynamic SGA operation uses more shared memory, Oracle Database explicitly performs a lock operation on the memory that is put to use. When a dynamic SGA operation releases shared memory, Oracle Database explicitly performs an unlock operation on the memory that is freed, so that it becomes available to other applications.
Oracle Database uses the oradism utility to lock and unlock shared memory. The oradism utility is automatically set up during installation. It is not required to perform any configuration tasks to use dynamic SGA.

11. Q: How do you see how many memory segments are acquired by Oracle Instances?::

A: ipcs – provides  information on the ipc facilities for which the calling process has read acccess

#UNIX: SEGSZ
root> ipcs -pmb
IPC status from <running system> as of Mon Sep 10 13:56:17 EDT 2001
T         ID      KEY        MODE       OWNER    GROUP  SEGSZ  CPID
Shared Memory:
m       2400   0xeb595560 --rw-r-----   oracle   dba  281051136 15130
m        601   0x65421b9c --rw-r-----   oracle   dba  142311424 15161

#Linux: bytes
[user@host ~]$ icps
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 32769      oracle    644        122880     2          dest

12. Q: How do you see which segment belongs to which database instances?::

A: This can be achieved with help of ipcs tool and sqlplus; oradebug ipc

#Linux
[user@host ~]$ icps
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 32769      oracle    644        122880     2          dest

#UNIX:
root> ipcs -pmb
IPC status from <running system> as of Mon Sep 10 13:56:17 EDT 2001
T         ID      KEY        MODE       OWNER    GROUP  SEGSZ  CPID
Shared Memory:
m       32769  0xeb595560 –rw-r—–   oracle   dba  281051136 15130
m        601   0x65421b9c –rw-r—–   oracle   dba  142311424 15161
m        702   0xe2fb1874 –rw-r—–   oracle   dba  460357632 15185
m        703   0x77601328 –rw-r—–   oracle   dba  255885312 15231

#record value of shmid "32769" (ID in UNIX)
[user@host ~]$ sqlplus /nologin
SQL> connect system/manager as sysdba;
SQL> oradebug ipc

#Information have been written to the trace file. Review it.

In case of having multiple instances, grep all trace files for shmid 32769 to identify the database instance corrsponding to memory segments.

#scrap of trace file MY_SID_ora_17727.trc:
Area  Subarea    Shmid      Stable Addr      Actual Addr
1        1      32769 000000038001a000 000000038001a000

13. Q: What is VMSTAT?::

A:    vmstat – Reports virtual memory statistics in Linux environments.
It reports  information about processes, memory, paging, block IO, traps, and cpu activity.

[user@host ~]$ vmstat
procs ———–memory———- —swap– —–io—- –system– —-cpu—-
r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
3  0 170224 121156 247288 1238460    0    0    18    16    1     0  3  2 95  0

14. Q: How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?::

A: sysctl – configure kernel parameters at runtime

EXAMPLES
/sbin/sysctl -a (Display all values currently available)
/sbin/sysctl -w kernel.shmmax = 3058759680 ( -w this option changes a sysctl setting)

To modify settings permanetly edit /etc/sysctl – kernel sysctl configuration file and then issue the following command:

/sbin/sysctl -p /etc/sysctl.conf ( Load in sysctl settings from the file specified or /etc/sysctl.conf if none given)

15. Q: How do you remove Memory segments?::

A: We can use ipcs and ipcrm command
ipcs – provides information on ipc facilities
ipcrm – removes a message queue, semaphore set or shared memory id

First kill all Oracle database processes; A shared memory object is only removed after all currently attached processes have detached.

#UNIX
root>  ps -ef | grep $ORACLE_SID | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}

root> ipcs -pmb #displays held memory
IPC status from /dev/kmem as of Tue Sep 30 11:11:11 2011
T      ID     KEY        MODE     OWNER  GROUP  SEGSZ  CPID  LPID
Shared Memory:
m   25069 0x4e00e002 –rw-r—– oracle    dba 35562418  2869 23869
m       1 0x4bc0eb18 –rw-rw-rw-   root   root    31008   669   669

RAM memory segment owned by Oracle is ID=25069.

root> ipcrm –m 25069 #this command will release that memory segment

16. Q: What is the difference between Soft Link and Hard Link?::

“Symbolic links” (symlinks/soft link) are a special file type in which the link file actually refers to a different file or directory by name.
When most operations (opening, reading, writing, and so on) are passed the symbolic link file, the kernel automatically “dereferences” the link and operates on the target
of the link.  But remove operation works on the link file itself, rather than on its target!

A “hard link” is another name for an existing file; the link and the original are indistinguishable.
They share the same inode, and the inode contains all the information about a file.
It will be correct to say that the inode is the file. Hard link is not allowed for directory (This can be done by using mount with –bind option)!

ln – makes links between files.  By default, it makes hard links; with the “-s” option, it makes symbolic (soft) links.

Synopses:
ln [OPTION]… TARGET [LINKNAME]
ln [OPTION]… TARGET… DIRECTORY

EXAMPLE:
#hard links
[user@host ~]$ touch file1
[user@host ~]$ ln file1 file2
[user@host ~]$ ls -li
total 0
459322 -rw-r--r--  2 userxxx users 0 May  6 16:19 file1
459322 -rw-r--r--  2 userxxx users 0 May  6 16:19 file2 (the same inode, rights, size, time and so on!)

[user@host ~]$ mkdir dir1
[user@host ~]$ ln dir1 dir2
ln: `dir1′: hard link not allowed for directory

#symbolic links
[user@host ~]$ rm file2 #hard link removed
[user@host ~]$ ln -s file1 file2 #symlink to file
[user@host ~]$ ln -s dir1 dir2     #symlink to directory
[user@host ~]$ ls -li
total 12
459326 drwxr-xr-x  2 userxxx users 4096 May  6 16:38 dir1
459327 lrwxrwxrwx  1 userxxx users    4 May  6 16:39 dir2 -> dir1 (dir2 refers to dir1)
459322 -rw-r–r–  1 userxxx users       0 May  6 16:19 file1
459325 lrwxrwxrwx  1 userxxx users    5 May  6 16:20 file2 -> file1 (different inode, rights, size and so on!)

[user@host ~]$ rm file2 #will remove a symlink NOT a targed file; file1
[user@host ~]$ rm dir2
[user@host ~]$ ls -li
[user@host ~]$ ls -li
total 4
459326 drwxr-xr-x  2 userxxx users 4096 May  6 16:38 dir1
459322 -rw-r–r–  1 userxxx users    0 May  6 16:19 file1

[user@host ~]$ info coreutils ln #(should give you access to the complete manual)

17. Q: What is stored in oratab file?::

A: This file is being read by ORACLE software, created by root.sh script which is being executed manually during the software installation and updated by the Database Configuration Assistant (dbca) during the database creation.
File location: /etc/oratab

ENTRY SYNTAX:
$ORACLE_SID:$ORACLE_HOME:<N|Y>:

$ORACLE_SID – Oracle System Identifier (SID environment variable)
$ORACLE_HOME – Database home directory
<N|Y> Start or not resources at system boot time by the start/stop scripts if configured.

Multiple entries with the same $ORACLE_SID are not allowed.

EXAMPLES:
[user@host ~]$ cat /etc/oratab
MYDB1:/u01/app/oracle/product/10.2.0/db:Y
emagent:/u01/app/oracle/product/oem/agent10g:N
client:/u01/app/oracle/product/10.2.0/client_1:N
emcli:/u01/app/oracle/product/oem/emcli:N

18. Q: How do you see how many processes are running in Unix/Linux?::

A: “ps” with “wc” or “top” does teh job.

ps – report a snapshot of the current processes
wc – print the number of newlines, words, and bytes in files
top – display Linux tasks (better solution)

In other words ps will display all running tasks and wc will count them displaying results:
[user@host ~]$ ps -ef | wc -l
149
[user@host ~]$ ps -ef |grep -v "ps -ef" | wc -l #this will not count ps proces executed by you
148

#using top
[user@host ~]$ top -n 1 | grep Tasks
Tasks: 148 total,   1 running, 147 sleeping,   0 stopped,   0 zombie

19. Q: How do you kill a process in Unix?::

A: kill – terminate a process
killall – kill processes by name
kill -9 <PID> #kill process with <PID> by sending SIGKILL 9 Term Kill signal
killall <process name>

EXAPLES:
[user@host ~]$ ps -ef | grep mc
user 31660 31246  0 10:08 pts/2    00:00:00 /usr/bin/mc -P /tmp/mc-user/mc.pwd.31246
[user@host ~]$ kill -9 31660
Killed

#killall
[user@host ~]$ killall mc
Terminated

[user@host ~]$ killall -9 mc
Killed

20. Q: Can you change priority of a Process in Unix? ::

A:    YES. nice & renice does the job.
nice – Runs  COMMAND  with  an  adjusted  scheduling  priority. When no COMMAND specified prints the current scheduling priority.
ADJUST is 10 by default. Range goes from -20 (highest priority) to  19 (lowest).

renice – alters priority of running processes

EXAMPLES: (NI in top indicates nice prio)
[user@host ~]$ mc
[user@host ~]$ top
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
1896 userxxx  17   0  7568 1724 1372 S    0  0.0   0:00.03 mc

[user@host ~]$ nice -n 12 mc #runs the mc command with prio of 12.
[user@host ~]$ top
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
1763 userxxx  26  12  6832 1724 1372 S    0  0.0   0:00.03 mc

[root@host ~]# renice +16 1763 #must be a root user
1763: old priority 12, new priority 16