Saturday, April 23, 2011

Unix operating system interview questions for DBA

Hi,
Below are some of the most important interview questions from Operating system
point of view to a DBA.

1)How can you check the files permission in Unix OS?
Ans: 'ls' command with the below option is used for checking files permission.
$ ls -altr
total 198
drwxr-xr-x 2 root root 4096 Aug 8 2008 srv
drwxr-xr-x 2 root root 4096 Aug 8 2008 mnt
drwxr-xr-x 3 root root 4096 Mar 7 05:10 home
drwxr-xr-x 7 root root 4096 Mar 7 05:47 lib64

2)How can you schedule job in Unix OS?
Ans: 'crontab' command is used for scheduling job in Unix OS.Crontab can be installed and removed as followed:

For commands that need to be executed repeatedly (e.g. hourly, daily or weekly), use crontab, which has the following options:

crontab filename Install filename as your crontab file.
crontab -e Edit your crontab file.
crontab -l Show your crontab file.
crontab -r Remove your crontab file.

Eg: Create a backup script as per your backup strategy and schedule as per strategy

$ vi backupdb.sh

. $HOME/.profile
export ORACLE_SID=
rman nocatalog << EOF
connect target
# backup controlfile
configure controlfile autobackup on;
# backup database,archivelogs and delete backed up archivelogs
backup database plus archivelog delete input;
# delete obsolete
delete obsolete;
yes
EOF

:wq
=>save and quit

Note: In the line ". $HOME/.profile" there has to be a space between . and $, to execute .profile in current shell.
Now schedule it with crontab(cronjob) command.
crontab -e

30 20 * * 1,3,5 /u01/backupdb.sh 1>/u01/backupdb.log 2>&1

:wq
=>save and quit displays the below message.

crontab: installing new crontab

Meaning: 00 =>seconds 00-59,Here it is 30 minutes;
20=>hours in a day 00-23,Here it is 8 pm;
*=>Day of the month,1-30,Here * signify the value does not apply
*=>Month of the year,1-12,Here * signify the value does not apply
1,3,5 =>Day of the week,0-6,Here 1,3,5 is monday,wednesday,friday.

/u01/backupdb.sh=>our backup script
> =>for overriding the content
/u01/backupdb.log =>for checking the output after the scheduled job is executed
& =>for running in backup

3)Which tool you use to copy files from windows to linux or linux to windows?
Ans: Winscp can be used for copying files from windows to linux.Winscp is easy to install GUI based utility for copying files in binary mode by default.Howerver 'ftp' command line utility can also be used.

4)How can you search file in Unix OS?
Ans: Using 'find' command we can search file in unix OS.

$ find /home/oracle -name "*.vim*" -print
/home/oracle/.viminfo

where: /home/oracle: path of the directory where you are searching file.

5)Which command is used to see the disk space details in Unix OS?
Ans: We use 'df -h' =>Linux OS, 'bdf' =>HP-UX OS,'df -g' =>AIX 0S

]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 5.7G 4.0G 1.5G 73% /
/dev/sda7 4.9G 4.1G 516M 90% /u01
/dev/sda3 6.3G 2.7G 3.3G 46% /u03
/dev/sda2 34G 32G 720M 98% /u02

Note: In IBM-AIX OS we have to use 'df -g' ; In HP-UX OS we have to use 'bdf' for checking the Disk Space details .

6) Which comand is used to see files usage details in Unix OS?
Ans:'du' command is used to see directories and file usage details in Unix OS.

$ cd /u01
[oracle@node1.u01]$ du -csh *
16K lost+found
756K oracle
4.1G oracle11g

7)Which command is used to schedule job without user intervention and backend?
Ans: 'nohup' command is used to run a command without user intervention.If we use '&' symbol than the command will run in backend.'nohup' command is my favourite command for running export and import jobs.I use this command very often.

Eg: nohup sh file_name.sh >file_output.out &

8)What are frequently used OS command for DBA's?
Ans: mkdir =>for creating directories in Unix OS; cd =>For changing the directory;
rm =>For removing files;rmdir =>For removing directories;grep =>For searching characters in a file; man =>User manual for all commands; useradd=>for creating OS user; chmod=For granting permission to files and Directories;chown=>For changing ownership of Directories.

Eg: mkdir test =>Create test directory
cd /u01/test =>Will change the path of Directory
man grep =>user manual for all command man has it own advantage.
chmod -R 775 /u01/test =>Grants permissions as read,write,execute to owner,groups
useradd -d oracle =>For Creating Operating system user as Oracle.

9)Which command is used to copy or synchronizing two Directories in a secure way on any Unix environment?
Ans : 'rsync' command can be used to copy or synchronize two directories.It is very important command and very handy tool for copying files fast for a DBA,Ofcourse
'scp' can also be use.But I really like using 'rsync' .Below is the example:

Eg:
----
[oracle@node1 HEALTH_CHECK_DB]$ rsync -av /u04/HEALTH_CHECK_DB/ /u05/SCRIPTS_DBA_TASKS/
building file list ... done
./
Database_status.sh
archive_gen.sql
c:&FileName._TESTDB1_20110509.html
c:&FileName._TESTDB2_20110509.html
db_monitor.sql
health_check.sql

sent 4225122 bytes received 158 bytes 8450560.00 bytes/sec
total size is 4224061 speedup is 1.00
[oracle@node1 HEALTH_CHECK_DB]$ cd ..
[oracle@node1 u04]$ cd /u05/SCRIPTS_DBA_TASKS/
[oracle@node1 SCRIPTS_DBA_TASKS]$ ls -altr
total 4176
-rwxrwxrwx 1 oracle dba 1815 Mar 27 23:53 archive_gen.sql
-rwxrwxrwx 1 oracle dba 2724 Mar 27 23:59 db_monitor.sql
-rw-r--r-- 1 oracle dba 1233385 May 9 11:40 health_check.sql
-rw-r--r-- 1 oracle dba 1421433 May 9 11:41 c:&FileName._TESTDB1_20110509.html
-rw-r--r-- 1 oracle dba 1564704 May 9 11:43 c:&FileName._TESTDB2_20110509.html
-rw-r--r-- 1 oracle dba 0 May 18 12:24 Database_status.sh
drwxrwxrwx 2 oracle dba 4096 May 18 12:24 .
drwxrwxr-x 9 oracle oinstall 4096 May 26 12:37 ..
-rwxrwxr-x 1 oracle dba 486 May 26 12:39 SCHEMA_CREATE_SCRIPT_TESTDB1_TESTDB2.sql
-rwxrwxr-x 1 oracle dba 846 May 26 14:06 profile_create.sql
-rwxrwxr-x 1 oracle dba 1252 May 27 20:44 crontab.oracle
-rwxrwxr-x 1 oracle dba 143 May 30 17:12 tablespace_create_script.sql

If you want to copy files from one linux(unix server) to other again it can be very handy,below is the syntax:

rsync -avc my_stuff2/ user@remotehost:~/mystuff3/

rsync copies only those files which are not copyied and this is definetly
useful in syncronizing source and destination directories.Hence it is very fast.

10)Which command is useful to see line by line display in Unix environment?
Ans: 'less' command is used to see line-by-line display('more' for page-by-page display).I find less more useful specially for seeing log files and to find the errors or Warnings in the log files.

Eg: less test1.log =>Will display test1.log file

11)Which command can be used to view file in readonly mode?
Ans:'view' command can be used to view file in readonly mode.A very good option to see cronjob file specially because at any this file should not get modified by mistake as all your daily jobs will be scheduled using cronjob.

Eg: view crontab.oracle

12)How we can delete the files which are few days(N days) old?
Ans:To save the disk space you might be deleting the old files or backup which are 1 week(2 weeks) old or depending on your Disk Space and other requirement.We should automate these tasks as a DBA.We can do this as follows:

For Unix environment:
-----------------------------------

Eg: If I want to delete files from a path which are 7 Days old:
Write one shell script as given below:

#remove_files_back.sh
#Removing 7 days old dump files
find /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup -mtime +6 -exec rm {} \;

Where: find =>Finding the file; /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup =>path;
-mtime=>Modified time,Here I'm giving 7 days(>6);-exec rm =>execute removing for files. Now as per your convenience schedule cronjob for doing this task,For example every sunday at 9 pm than:

00 21 * * 0 /u03/DB_BACKUP_TESTDB/expdp_fulldb_backup/remove_files_back.sh 2>&1 >/u05/DB_BACKUP_TESTDB/logs/CRONJOBS_LOGS/TESTDB_BACK_cron.log

Note: For Windows Environment you can create a .bat file as follows:

--remove_file_back.bat
forfiles /p "D:\dbbackup\testdb\daily" /s /d -7 /c "cmd /c del @file : date >=7 days >NUL"

Where:All files which are 7 days old are removed;D:\dbbackup\testdb\daily =>path.
However Please make a note Please don't use the above command for your /(root) directory or any software specified and confirm and test in test environment before using on the actual system.

14) How to FIND whether OS (kernel) is 32 or 64 bit in Unix Operating systems?
Ans: file /usr/bin/initdb

15)Which command can be use to convert Windows file to Unix file?
Ans: 'dos2unix' command can be use for this purpose.

After copying files with winscp use dos2unix utility to convert.
Unix uses a convention that a line is ended by a line feed character (Ascii 10). Windows/DOS uses a convention that a line is ended by a two character sequence, carriage-return line-feed (Ascii 13 then ascii 10). The dos2unix command converts for Windows/DOS format to Unix format. It writes the result to standard output. To write to a file, just redirect the standard output to the file. For example, use

$dos2unix myfile >mynewfile

Offcourse there are lot of useful commands which a DBA uses in Day-to-Day activities.If I come across few more useful commands I will attach this thread.

Hope it helps.

Happy UNIX OSs Learning


Best regards,

Rafi.

11 comments:

  1. Hi,
    Sir that's really good set of questions .. and eagerly waiting for more such posts

    Regards
    Kavita

    ReplyDelete
  2. Thanks for sharing this nice interview questions. Thanks for the great help.

    Interview Questions

    ReplyDelete
  3. Thanks for the appreciation Kavita and Yogesh.
    All the best for your Oracle DBA Career.



    Best regards,

    Rafi.

    ReplyDelete
  4. great questions for interview , I have also shared questions collected from my friends and colleagues here Top 30 Unix Command Interview Questions asked in IB . hope it would be useful to you guys.

    ReplyDelete
  5. Hi Javin,
    Thanks very much for compliment.The link given by you are having very nice question.I'm sure it will help DBA's a lot....

    Best regards,

    Rafi.

    ReplyDelete
  6. Thanks Rafi Sir Ur Blogs r really the best blogs on oracle dba really appreciated. I want to ask a question m a fresher in the field of dba, i attended the training program from institute sql star but didn't get any job in dba bcoz of fresher could u plz guide me what should i do to groom my career bcoz i search lots of job portal newspaper but even a single company is not interested in fresher everywhere it is written fresher need not to apply.....plz guide me...

    ReplyDelete
  7. Hi Yashwant,
    Thanks very much for complimentary words.I always believe in learning and sharing the knowledge.I understand in Oracle DBA field it is very tough to enter as fresher,Please start your career as SQL/PL SQL Developer also focus on getting knowledge of shell scripting and Linux Operating system knowledge,After 2 yrs you can slowly shift to DBA by upgrading skills like RAC,Dataguard,ASM,RMAN and plenty of things to learn in this big Ocean of Oracle.As of now focus on learning and practise.Time will come when you see yourself as DBA.All the best.For any queries mail me.

    ReplyDelete
  8. found 1 mistake, The 30 here is 30 min NOT the 30 sec.

    30 20 * * 1,3,5 /u01/backupdb.sh 1>/u01/backupdb.log 2>&1

    :wq
    =>save and quit displays the below message.

    crontab: installing new crontab

    Meaning: 00 =>seconds 00-59,Here it is 30 seconds;
    20=>hours in a day 00-23,Here it is 8 pm;
    *=>Day of the month,1-30,Here * signify the value does not apply
    *=>Month of the year,1-12,Here * signify the value does not apply
    1,3,5 =>Day of the week,0-6,Here 1,3,5 is monday,wednesday,friday.

    ReplyDelete
  9. Hi Steve,
    I corrected.Sorry there was typo from my end.Thanks for noticing :)

    Best regards,

    Rafi.

    ReplyDelete
  10. Your blog is much effective and thanks for sharing information.. Here we are providing training & materilas and dumps for those who are preparing for cca 175 spark and hadoop developer certification exam and in the related fields of cloudera hadoop developer certification exam.
    cca 175 certification
    cloudera hadoop developer certification
    cca 175 spark and hadoop developer certification
    hadoop developer certification
    cca 500 certification

    ReplyDelete
  11. Are you looking to make money from your websites by popunder ads?
    In case you are, have you considered using PopAds?

    ReplyDelete