20 Most Frequently Used Unix commands for Oracle DBA

As an Oracle DBA we need to work on UNIX systems as well. Here, I am listing some basic Unix commands for Oracle DBA used in day to day activities. I have also listed important SQL commands in this post. Below listed are most commonly used Unix Commands which Oracle DBA has to execute.

1. "ls" command: This command is used to list the objects in the current directory.

[oracle@demo1 test]$ ls
dp.log  export.log  import.log  test.dmp  test.dmp.gz

options with "ls" command

"ls -ltrh":  used to list objects with details as read, write and execute permission, owner of file, date of creation and size of file.
[oracle@ddemo1 test]$ ls -ltrh

total 815M
-rw-r--r-- 1 oracle oinstall  116 Nov 18 06:10 dp.log
-rw-r--r-- 1 oracle oinstall 544M Dec 30 02:07 test.dmp
-rw-r--r-- 1 oracle oinstall 5.8K Jan  3 01:16 import.log
-rw-r----- 1 oracle oinstall 271M May  2 03:24 test.dmp.gz
-rw-r--r-- 1 oracle oinstall 9.3K May  2 03:24 export.log
"ls  /home/oracle": this command is used to list all objects in directory. Pass directory name as and argument.

[oracle@demo1 test]$ ls /home/oracle
abc.txt  test.zip  oradiag_oracle

"ls -al"This command is used list all objects including hidden files in directory.
[oracle@itc-1709 ~]$ ls -al

total 1512
drwx------ 7 oracle oinstall    4096 May  2 12:54 .
drwxr-xr-x 3 root   root        4096 Apr 14 13:07 ..
-rw------- 1 oracle oinstall     383 Apr 19 10:50 .bash_history
-rw-r--r-- 1 oracle oinstall      33 Apr 14 13:07 .bash_logout
-rw-r--r-- 1 oracle oinstall     247 Apr 18 15:04 .bash_profile
-rw-r--r-- 1 oracle oinstall     124 Apr 14 13:07 .bashrc
-rw-r--r-- 1 oracle oinstall     515 Apr 14 13:07 .emacs
drwxr-xr-x 3 oracle oinstall    4096 Apr 14 13:07 .kde
drwxr-xr-x 4 oracle oinstall    4096 Apr 14 13:07 .mozilla
drwxr-xr-x 3 oracle oinstall    4096 May  2 12:54 test
drwxr-xr-x 3 oracle oinstall    4096 Apr 14 14:09 .oracle
drwxr-xr-x 3 oracle oinstall    4096 Apr 18 15:03 oradiag_oracle
-rw------- 1 oracle oinstall     658 Apr 18 15:04 .viminfo
-rw------- 1 oracle oinstall      65 Apr 18 15:04 .Xauthority
-rw-r--r-- 1 oracle oinstall     658 Apr 14 13:07 .zshrc
2. "pwd" command: This command is used to find out present working directory. e.g.

[oracle@demo1 test]$ pwd
/home/oracle/test1/test

3. "cd" command: This command is used to change the directory. e.g.

[oracle@demo1 /]$ cd /home/oracle/
[oracle@demo1 ~]$ pwd
/home/oracle

4. "mkdir" command: This command is used to make a directory. e.g.

[oracle@demo1 ~]$ mkdir testdir
[oracle@demo1 ~]$ ls -l
total 1460
drwxr-xr-x 3 oracle oinstall    4096 Apr 18 15:03 oradiag_oracle
drwxr-xr-x 2 oracle oinstall    4096 May  2 15:04 testdir

We can also make a directory other than present working directory by giving full path.

[oracle@demo1 ~]$ mkdir /home/oracle/testdir

5. "rm" command: This command is used to remove a file/directory. e.g.

[oracle@demo1 ~]$ rm testdir
[oracle@demo1 ~]$ ls -l
total 1460
drwxr-xr-x 3 oracle oinstall    4096 Apr 18 15:03 oradiag_oracle

To recursively remove sub directories and files from a directory use rm -rf directory_name
[oracle@demo1 ~]$ rm -rf testdir

6. "cp" command: This command is used to copy  file/directory e.g.

[oracle@demo1 ~]$ cp [source] [destination]
[oracle@demo1 ~]$ cp testdir /opt/oracle/

* is used for all e.g.
[oracle@demo1 ~]$ cp * /opt/oracle/
This will copy everything in present directory to /opt/oracle. 

7. "mv" command: This command is used to move files from one place to anther place.

[oracle@demo1 ~]$ mv [source] [destination]
[oracle@demo1 ~]$ mv testdir /opt/oracle/

The directory testdir will move to /opt/oracle

* is used for all files e.g.
[oracle@demo1 ~]$ mv * /opt/oracle/

This will move everything in present directory to /opt/oracle.

8. "cat" Command: This is another unix command for Oracle DBA used to display contants of a file. Most common use is for displaying contents of /etc/oratab etc.
[oracle@database ~]$ cat /etc/oratab

#Backup file is  /etc/oracle/oracle/srvm/admin/oratab.bak.database line added by Agent
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/etc/oracle/grid:N
orcl:/etc/oracle/oracle:N               # line added by Agent
9. "tail" Command: This command is used for display last given lines of a file.

Syntex: tail -n file_name

-n defines number of lines. In below example i am displaying last 20 lines of alter_orcl.log
[oracle@database bin]$ tail -20 /etc/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 

CJQ0 started with pid=27, OS id=3117 
Sun May 12 08:31:22 2013
db_recovery_file_dest_size of 4002 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Setting Resource Manager plan SCHEDULER[0x310D]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun May 12 08:31:26 2013
Starting background process VKRM
Sun May 12 08:31:27 2013
VKRM started with pid=25, OS id=3119 
Sun May 12 08:31:38 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun May 12 08:31:51 2013
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun May 12 08:31:58 2013
Starting background process SMCO
Sun May 12 08:31:58 2013
SMCO started with pid=31, OS id=3147 [oracle@database bin]$ 
Another very good for of unix tail command for DBA is to display real time data from a file. There are situations when you want to do some change in Database and want to see what is shown in database alter log file. In this case, tail -f will help you. This will keep on display real time data of file.
[oracle@database bin]$ tail -f /etc/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 

Sun May 12 08:31:27 2013
VKRM started with pid=25, OS id=3119 
Sun May 12 08:31:38 2013
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun May 12 08:31:51 2013
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun May 12 08:31:58 2013
Starting background process SMCO
Sun May 12 08:31:58 2013
SMCO started with pid=31, OS id=3147 
10 "touch" Command: Sometimes for testing purpose, DBA needs to make files in unix or linux. So, touch command is the solution for this.

[oracle@database ~]$ touch touchtest.txt
[oracle@database ~]$ ls
Desktop  oradiag_oracle  test  touchtest.txt

11. "find" Command: Many times a Database administrator has to find files into unix system. find command is the command for Oracle unix DBA.

Syntex: find -iname filename

-iname is used for case in-sensitve find.

[oracle@database ~]$ find -iname test
./test

Note: find command will only find file in it's current sub-directories only. To do a whole system level find. go to root and then start find command.

12. "ssh" Command: This is another very usefull unix/linux command for  DBA. A remote DBA has to switch from one node to another node. So, instead of opening new putty session. He can login to another system using ssh command.

syntex: ssy {systemIP}
[root@remotedb1 ~]# ssh 192.168.11.102
The authenticity of host '192.168.11.102 (192.168.11.102)' can't be established.
RSA key fingerprint is 83:02:70:36:27:f3:dc:dd:75:3d:a6:6c:cf:03:09:76.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.11.102' (RSA) to the list of known hosts.
root@192.168.11.102's password:
Last login: Fri May 10 15:01:17 2013 from remotedb2.server.com
[root@remotedb2 ~]#
Now, you have reached on remotedb2.server.com server. Complete your task and type exit you will be back to your current system.

[root@remotedb2 ~]# exit
[root@remotedb1 ~]#

13. "gzip" command: This command is used to compress a file.

[oracle@database ~]$ gzip touchtest.txt

[oracle@database ~]$ ls
Desktop  oradiag_oracle  test  touchtest.txt.gz

To decompress  a compressed file, use gzip -d command.

[oracle@database ~]$ gzip -d touchtest.txt.gz

[oracle@database ~]$ ls
Desktop  oradiag_oracle  test  touchtest.txt

14. "shutdown" command: As suggested by name, A database administrator sometimes needs to shutdown the unix server.

[oracle@database ~]$ shutdown -h now

Shutdown the system after 10 minutes.

[oracle@database ~]$ shutdown -h +10

15. "crontab -l" command: This unix command is for listing all crontab scheduled in the system.

[oracle@database ~]$ crontab -l
no crontab for oracle


16. "chmod" Command: This command is used to change the permissions for file. from read to read, write and execute etc.

Give full access to user and group (i.e read, write and execute ) on a particular file.

$ chmod ug+rwx file.txt


17. "chown" Command: This is another unix command frequently used by Oracle DBA's. This will change the ownership of a file.

syntex: chown -r oracle:oinstall bin

r is used for recrusive.

[oracle@database ~]$ chown oracle:oinstall dba.sh

18. "passwd" Command: This command is used to change the password for current user.

[oracle@database ~]$ passwd
Changing password for user oracle.
Changing password for oracle
(current) UNIX password:
New UNIX password:
Password changed.

[oracle@database ~]$

Super user can use passwd command to reset others password. This will not prompt for current password of the user.

# passwd USERNAME

19 "uname" command: This displays important information about the system such as: Kernel name, Host name, Kernel release number, Processor type etc.

Most important for a Unix DBA is OS version and bit type of O/S 32 or 64 bit.

[oracle@database ~]$ uname -a
Linux database.example.com 2.6.18-194.el5 #1 SMP Mon Mar 29 20:06:41 EDT 2010 i686 i686 i386 GNU/Linux

This is a linux 32 bit system (i386 means 32 bit)

[root@dmorlcel11 ~]# uname -a
Linux database1.example.com 2.6.32-400.11.1.el5uek #1 SMP Thu Nov 22 03:29:09 PST 2012 x86_64 x86_64 x86_64 GNU/Linux 

This is a linux 64 bit system (x86_64 means 64 bit)

20. "su" Command: Switch to a different user account using su command. Super user can switch to any other user without entering their password, but to switch as root user we need to give root user password.

[oracle@database ~]$ su root
Password:
[root@database oracle]#          

Does this post helps you ? Please share your views about this post.                                                             

No comments:

Post a Comment