Unix and DB Interview Q & A for Prod Support

Q: What is Certification renewal ?

A: There are software which needs annual certificate renewals in the environment. For this cert renewal activities we need to raise an incident ticket with OS admin team.

Q: What are the types of cert renewals?

A: Apache web cert, ssl certificate renewal, MQ cert renewal

Q: What are the things you configured in Jenkins? What was the flow in Jenkins.

A:

·         Dev team usually keep the version of the code on the SVN/GitHub.

·         Jenkins pickup the version of the code

·         Run test scripts (selenium)

·         Build the app and deploy it. In case of java application .WAR file gets created.

·         Once war file gets created it we use to deploy on the intended environment

Q: Which version control tool you used?

A: SVN, GitHub

Q: Which version of the linux you used ? What is the command to check it.

A: uname –a    or uname -v

Q: Welcome message store where in linux server.

A: ? ~/.bashrc

/etc/bash.bashrc

Q: What is  .profile file what is the usage of this file ?

A: .profile or .bash_profile files in your home directory. These files are used to set environmental items for a users shell. Items such as umask, and variables such as PS1 or PATH .

Q: What are environment variable in unix.

A: Environmental variables are used to pass information into processes that are spawned from the shell. Shell variables are variables that are contained exclusively within the shell in which they were set or defined. They are often used to keep track of ephemeral data, like the current working directory.

Q: How you define Environment variable in linux

$ export mypath=/home/jogtushar/tj

$ echo $mypath

Q: What is the location for Oraclehome ?

A: Go to ORACLE_HOME/bin 

Q: Db and db instance created users. Trying to connect db and its down. How will you troubleshoot. How will you check whether up or down

A: Check the oracle process runs:

On Un*x:

ps -ef|grep pmon

1.      if the command in any case returns output i.e. if the pmon/oracle process is running in your environment the database is running.

2.      Go to ORACLE_HOME/bin and run:

 ./sqlplus /nolog

If after login you get errors, then the database does not run:

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 31 21:61:61 2014

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

Enter user-name: oracle

Enter password:

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Process ID: 0

Session ID: 0 Serial number: 0

Q: How you will connect to database through linux box without jdbc

A: Connect locally (Environment variable setup is required in this type of connection.)

Environment variable setup command for bourne shell in linux:

source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

Connect username/passwd

Connect remotely

username/password@[//]host[:port][/service_name]

where:

// is optional

host is the host name or IP address of the computer that is running Oracle Database XE

port (optional) is the TCP port number on which the Oracle Net listener is listening. If not specified, the default port number 1521 is assumed.

service_name (optional) is the name of the database service to which to connect. For Oracle Database XE, the service name is XE. If service_name is omitted, Oracle Database XE Client appends a request for the default database service, which is configured during installation as XE.

Q: count the number of times word linux in the file

A: grep –c “linux” <filename>

Q What is grep –i does?

A: Case-insensitive search . It ingnore the case sensitive ness in the file and give output.

Q: linux all small cap change it to all caps :

A: sed s/linux/LINUX/g <file name>

Q: what does this g stands for ?

A: The s stands for substitute, while the g stands for global, which means that all matching occurrences in the line would be replaced.

Q: check the file size of particular file. How much bytes.

A: find /home/jogtushar -type f -exec du -Sh {} + | sort -rh | grep xab

Q: Db couple of table out of 10 tables take a backup of 6 tables. What query you will use

A: From Oracle 10g onward, expdp is standard export command and exp is deprecated.

expdp has include clause where in you can specify tables and views you want to export. Here is a sample command.

expdp scott/tiger@mydb schemas=MYSCHEMA

include=TABLE:"IN ('A', 'B','C')",VIEW:"IN('my_view')" 

 directory=MY_DIR dumpfile=Exp_ABC_MyView.dmp logfile=expdpExp_ABC_MyView.log

Further you can export only object definitions or data or both as well. more examples in https://oracle-base.com/articles/10g/oracle-data-pump-10g

Q: Port being occupied . how will you check

A: #netstat –ltnp|grep –w ‘:8080’

Q: Scheduling tool control-M knowledge

A: It’s a batch monitoring tool widely used in the industry. Interviewer will ask about types of jobs. Difference between ‘on ice’ job and ‘on hold’ job.

There is another tool Autosys which is similar tool as that of control M

Q: Configuration in geneos, what are the things you use to monitor on geneos

A:

Q: What are your day to day activities ?

A:

Q: Do you have experience in scripting ?

A:

SFTP, public key , private key, file transfer protocol

Have you done any deployment, pipe line, configuration.

Automated deployment (ansible)

Day to day activity in your most recent profiles.

If certain link is not working what steps you will take initially ?

What are the possible reason why particular site is not working?

Unix access management, NAS storage,mounting, SAN FS, storage, fixed disk , allocating space

What is the pattern of commands in unix

 command –option arguments

Example: ls –l <filename>

Which is the shell of unix

#echo $SHELL

How to check shell in the linux

Echo $0 or echo $SHELL or echo $$ (it will give you the PID now grep that PID to know that shell)

Important port numbers

FTP ports 20 , 21

SSH - 22

telnet -23

SMTP 25

DNS 53

DHCP 63

HTTP 80  HTTPS 443

Karbaros 88

SNMP 161

Syslogd 514

NFS 2049

SGID,SUID and stickybit

Command =  chmod enter

SUID : Set user identification.

Example :  chmod u+s <file name/dir name>

-rws-r—r--

SGID: Set group identification

Example: chmod g+s <file name/dir name>

rwx-rws-r--

Stickybit:

Example:-rwxrwxrwxt

SUID = chmod 4 permission / file location

GUID - chmod 2 permission / file location

STICKUBIT chmod 1 permission / file location

What are the fileds for command ls –ltr

-rwx rw –r—1  root  root  2048  jan13 07 : 11 file.exe

Total seven fileds 1) permission 2) number of links 3) owner name 4)owner group 5)file size 6) time of last modification 7) file/directory name

Procedure to set root password on the unix box.

Output of top command

How to extend the file system

fdisk –l  (This command will list partition table)

fdisk -cu /dev/sda (This will create a new partition)

How to clear the swap memory

swapoff -a && swapon -a

Command to clear the cache without interrupting application.

https://www.tecmint.com/clear-ram-memory-cache-buffer-and-swap-space-on-linux/

sync; echo 1 > /proc/sys/vm/drop_caches

To check swap space  

# free  –m

To check current swap space

 # swapon –s  or #cat /proc/swaps

Two methods to create a swap memory

Method 1: using a hard drive

# create a partition using a fdisk (/dev/sdc1)

#mkswap /dev/sdc1 (making swap memory)

#swapon /dev/sdc1 (making it active)

#make entry in /etc/fs tab if wants to automatic enable functionality .

Menthod 2: using a file

If you don’t have any additional disk, you can create file somewhere on your file system and use that file for swap space.

The below dd command example creates the swap file with the name “myswapfile” under /root directory with the size of 1024 MB (1GB)

#dd if =/dev/zero of=/root/myswapfile bs=1M count=1024

Change the permission of the swap file so that only root can access

#chmod 600 /root/myswapfile

#mkswap /root/myswapfile (making swap file)

#swapon /root/myswapfile (making it active)

#make entry in /etc/fstab  if wants to automatic enable functionality

To view that file #cat /etc/fstab

Difference between ulimit and umask

Ulimit: provide ways to limit and control the usage of system resources such as threads, files, and network connections on a per-process and per-user basis. These “ulimits” prevent single users from using too many system resources.

Umask: On Linux and other Unix-like operating systems, new files are created with a default set of permissions. Specifically, a new file's permissions may be restricted in a specific way by applying a permissions "mask" called the umask. The umaskcommand is used to set this mask, or to show you its current value. For normal users umask value is 002 for root user umask value is 022 for restricted files umask value is 077

How to check the uptime of the linux server

Using uptime command we can determine how long our linux box is running, also uptime can be viewed by the top and w command.

How to check which redhat version is installed on the server

Cat /etc/redhat –release

How to check the ip address of the lan card

Ifconfig

Hostname –i

How to determine the host name of the linux box

#hostname

#hostname –f (to get FQDN)

How to check default gateway

Route –n

Which command used to check the kernel version

Uname –v

How to check current run level of the linux box

Who –r        or runlevel or cat /etc/inittab

How to list hidden files in linux

Ls –a

How to change password of the user

Passwd <username>

To check last command ran successful

$?

How to check available/logged in users on the server.

 w

How to check user login history

Last –username

What is the file where all the logins information gets stored

/var/log/utmp   or /var/log/wtmp

How to check default umask value

Umask

How to change default umask value

We can change it under /etc/profile   or /etc/bashrc

You noticed a kernel error message during the boot procedure but it scrolled before your could read that. What log file you will check to find out that message.

The log file that contains kernel boot message is /var/log/dmesg

To check all the service status in your server

Service  - -status-all

To check a specific service status on the server

#service <service name> status

How do you find how many cpu are their in your system

Cat /proc/cpuinfo

How to differentiate how are  physical and virtual process on the server

How do you find which processes are using a particular file

Lsof (will list down PIDs of all the processes which is using a particular file)

#lsof –p <PID> | grep <file name>

#fuser -u <filename>

Deleting a single line from a file

#cat –n <file name> ( to do numbering in the file)

#sed –I ‘3d’ <file name> (3 is the line number which we want to delete)

Replace A with _ in string MAHARANAPRATAP

echo "MAHARANAPRATAP" |sed 's/A/_/g'

Find a string in a file

Grep “Tushar” a.txt

What difference between cmp and diff commands?

dupCmp - Compares two files byte by byte and displays the first mismatch

diff - tells the changes to be made to make the files identical

To check the differences between the data of two files cmp command is used. But it displays only the first difference.

Syn: $ cmp file1 file2

To display all the differences between the files diff command is used.

Syn: $ diff file1 file2

Note: Comparison between the files of different users is possible only when the present working user has the access permission on the other user.

Find a file name start with a with .txt extention

find . -name 'a*.txt'

To replace all specific word in the file.

Sed s /word/words/g filename

Tar command

#tar cvfz filename.tar.gz file1 file2 file3 (to create)

#tar –xvfz filename.tar.gz (to extract)

#tar –tf filename.tar.gz (to list the content)

#tar –x  O f filename.tar.gz file1 (we are reading file1 without extracting it from tar file)

Find command

#find . –maxdepth 3 –name

#find. –name *.xml –print 0 | xargs -0 rm

if application has a slowness issue, what are the steps you will take to resolve this.

https://alankent.me/2013/10/31/my-program-is-running-slow-and-i-dont-know-why-unix/

top

vmstat 5

Will check the services are up and running fine.

Check if there are any patches ran on the recent days/time

Check current log file and try to find anything suspicious

Check on network and database side if anything related to network/database then contact those teams for further assistance.

certain functionality was working fine but after few days that functionality is not working, how you will deal with this issue.

top

vmstat 5

Will check the services are up and running fine.

Check if there are any patches ran on the recent days/time

Check current log file and try to find anything suspicious

Check on network and database side if anything related to network/database then contact those teams for further assistance.

what is swap memory, one should have swap memory ?

Swap space in Linux is used when the amount of physical memory (RAM) is full. If the system needs more memory resources and the RAM is full, inactive pages inmemory are moved to the swap space. While swap space can help machines with a small amount of RAM, it should not be considered a replacement for more RAM.

If we have less RAM space for the application in that case swap memory plays an important role. Other wise it slows down the processes for the application since its gets accessed from disk and then it goes to RAM for further processing.

What is swaping

stale processes in linux? is thier issue if those processes are running?

Whenever process completes its execution, it exits and notify its parent process that his child has died. At this time,  parent process suppose to execute WAIT system call which reads dead child process status and other details. Once wait system call completes, dead child will be removed from memory. At this point, if parent process is not coded properly or unable to read this status from child for some reason then it wont fork wait system call. This in turns keeps dead child in memory & process table.

This whole operation completes very fast. Zombie takes very tiny amount of memory to live in so couple of zombies on server are harmless. Many zombie processes have parent PID as 1 which is init process. When child is dead but not cleared from memory and parent process exists then those child zombies will be taken over by init. Init usually runs its child zombie clearance periodically. So its possible those zombies gets cleared out in it.

Command to find zombie/stale process on the server

ps aux |grep Z

find the file which is created today

find /home/jogtushar/tj -daystart -ctime 0 -print

How to show all the services on the unix server

#service –status-all

How to check the process on the server

#ps –ef|grep tomcat

Which Process Listening on a Particular Port

netstat -ltnp|grep -w ‘:80’

Find a second column value and put in another file

#cat a.txt|awk '{print $2}' >b.txt

Find a string and replace the string ‘abc’ by ‘xyz’ in a file

#sed ‘s/abc/xyz/g’ <filename>

Which process is running on specified port

#netstat –ltnp|grep –w ‘:1023’

Which process is using which file

lsof -p 687

How to find a blank file in the given location

find . -type f -empty

Find 10^th value from file

awk ‘FNR==10 {print $2} t1

awk -F',' 'FNR == 3 {print $2}' t1

https://www.thegeekstuff.com/2010/01/8-powerful-awk-built-in-variables-fs-ofs-rs-ors-nr-nf-filename-fnr/?ref=binfind.com/web

Find largest file size in current directory using unix command,

ls -Slr|tail -1

Find 5 largest file in specified location

find /home/jogtushar -type f -exec du -Sh {} + | sort -rh | head -n 5

transfer file from one host to another

scp /jogtushar/tj jogtushar@i1: /working/files/  (If transfer a file locally)

wget if want to transfer a file over the internet or intranet.

unix- wants to split file with 50k records in one file  --

split -l 50000 <filename>

julian date type in unix

Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time

date +%j

10 records in file with three column, Write a command to locate third column+5th row

awk 'FNR == 5 {print $3}' xae

What is the command to check the disk utilization

df –h – size of directories in entire system

du –h  -- Size of a current directory

free –h – size and usage of physical memory (RAM)

What is the command to check the cpu utilization

top

Which of the following can be used to get system specific information such as Name of the OS, Current release and verstion etc?

uname -a

Comma seprated, tab seprated file working in a awk and sed

awk -F',' '{print $3}' t1

Combine awk and sed

Difference between DF and DU

du == Disk Usage. It walks through directory tree and counts the sum size of all files therein. It may not output exact information due to the possibility of unreadable files, hardlinks in directory tree, etc. It will show information about the specific directory requested. Think, "How much disk space is being used by these files?"

df == Disk Free. Looks at disk used blocks directly in filesystem metadata. Because of this it returns much faster that du but can only show info about the entire disk/partition. Think,"How much free disk space do I have?"

What is sed and how it is helpful.

Shell script to find a file in current directory and cat the ouput on the screen

echo "Enter File Name: "read filenameif [ -z $filename ]thenecho "File name is blank"elseffname=$(ls -ltr|awk '{print $9}'|grep $filename)if [ -z $ffname ]thenecho $ffname File does not existelseecho $ffname is presentcat $ffnamefifi

1. Display 10 most CPU intensive processes or Display process sorted on CPU usage. I know its ps, but the answer is more elaborate, something like ps -eo %cpu,pid,comm | sort -nr | head -10 or top command (?)

2. How to get the last 10 lines of a file (easy) tail filename

3. How to zip and unzip files in unix? (easy) gzip and gunzip bzip2 and bunzip2 zip and unzip (explain with syntax)

4. How do you display line nos. in files( easy) cat -n filename or nl pr(Paginating output) pr -n

5. How do you fire a shell script? (easy) sh file.sh bash file.sh or chmod +x file.sh // Give execute permission ./file.sh

6. How do you save and exit from vi editor? (easy) :wq

7. How do you create a new file? (This is a generic question & rather tricky. I hope one of the answer suffices!) vi input mode press [i] and type :x cat > foo touch emp.lst // creates a file if it doesn't exist

8. Name some shell scripts used for monitoring? (Generic no specific answer) Check alert log files for error messages, Checking disk usage so on.

Try below assignments.

1.    Display calendar of current month.

calendar

cal

2.    Search for a pattern in all the files/subdirectories from current directory.

3.    Count the no. of directories / sub directories in current directory.

4.    Display day of week for a given date. (ddmmyyyy).

5.    Display contents of all .lst files in the current directory.

6.    For a student file with the following fields, rollno, name, marks. Generate 2 files ‘Pass’ and ‘Fail‘containing records of student who have passed or failed. Also count the number of students who have passed or failed.

7.    Accept a date string from terminal and display the employees born after the input date.

8.    Find the number of employees belonging to a particular department specified by user.

9.    Find the count of people in each dept. of the employee file.

10. Generate a list of S.E. , who earn more than the amount specified by the user.

11. View the employee records in order of designations.

12. List employee details of all employees who earn more than the average salary of all employees.

http://www.techbeamers.com/20-shell-scripting-questions-answers/

uniq

/etc/passwd has how many entries.

Positional parameters.

Machine which sits between a network and the internet and plays the role of speeing up the most widely used protocols

Component of every file system on unix

BA

ACTIVITI (jBPM)

7S

Bpmn

DB interview questions

https://www.toptal.com/sql/interview-questions

types of indexes in oracle

what is execution plan, what is stats, query cost

EXPLAIN PLAN parses a query and records the "plan" that Oracle devises to execute it. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan

SQL Server Query Optimizer uses statistics to estimate the distribution of values in one or more columns of a table or index views, and the number of rows (called cardinality) to create a high-quality query execution plan.

difference between delete and truncate

TRUNCATE

• TRUNCATE is a DDL command
• TRUNCATE is executed using a table lock and whole table is locked for remove all records.
• We cannot use Where clause with TRUNCATE.
• TRUNCATE removes all rows from a table.
• Minimal logging in transaction log, so it is performance wise faster.
• TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Identify column is reset to its seed value if table contains any identity column.
• To use Truncate on a table you need at least ALTER permission on the table.
• Truncate uses the less transaction space than Delete statement.
• Truncate cannot be used with indexed views.

DELETE

• DELETE is a DML command.
• DELETE is executed using a row lock, each row in the table is locked for deletion.
• We can use where clause with DELETE to filter & delete specific records.
• The DELETE command is used to remove rows from a table based on WHERE condition.
• It maintain the log, so it slower than TRUNCATE.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
• Identity of column keep DELETE retain the identity.
• To use Delete you need DELETE permission on the table.
• Delete uses the more transaction space than Truncate statement.
• Delete can be used with indexed views.

difference between primary key and unique key

Primary Key and Unique Key are used for different things - understanding what they are for will help you decide when to use them.

The primary key is used to identify a row of data in a table. It is used whenever you need to refer to a particular row, eg. in other tables or by application code etc. In order to identify a row, the values of a PK must be unique. Furthermore, they can't be null, because most dbms treat null as not equal to null (since null typically means "unknown"). A table can only have one PK. All tables in your databse should have a PK (although this is not enforced by most dbms), and PK can span multiple columns.

Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns.

It is also worth knowing that, by default, many dbms index and physically order tables on disk using the PK. This means that looking up values by their PK is faster than using other values in a row. Typically, however, you can override this behaviour if required.

Show max 3^rd salary from employee table

select *

  from

  (

        select

            sal,dense_rank() over (order by sal desc) ranking

        from   emp

  )

  where ranking = 3

fetch the non matching rows from two tables in Oracle

select * from names

 where (class_id, stud_name ) not in

   (select course_id, stud_name from course);

Inner join

Create empty table by reffering one table

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

·         sequences

·         triggers

·         indexes

·         some constraints may not be copied

·         materialized view logs

This also does not handle partitions

Delete duplicate rows from table

DELETE FROM your_table

WHERE rowid not in

(SELECT MIN(rowid)

FROM your_table

GROUP BY column1, column2, column3);

Find duplicate department id

select distinct department_id from hr.employees where DEPARTMENT_ID in (

select department_id from

(

select department_id,count(*) as dep_ct from hr.employees group by department_id order by dep_ct asc

)

where dep_ct > 1

);