Frequently used OS (Linux/Solaris/AIX) Command for DBA

As a DBA you need to use frequent OS command or alt least how to query the OS and its hardware. Usually we do it before fresh install upgrade, migrate of database/operating system. Here is some of the useful frequently used day to day OS command for DBA.
To find and delete files older than N number of days:
find . -name ‘*.*’ -mtime +[N in days] -exec rm {} ;
Example : find . -mtime +5 -exec rm {} ;
The above command is specially useful to delete log, trace, tmp file
To list files modified in last N days:
find . -mtime – -exec ls -lt {} ;
Example: find . -mtime +3 -exec ls -lt {} ;1
The above command will find files modified in last 3 days
To sort files based on Size of file:
ls -l | sort -nk 5 | more
useful to find large files in log directory to delete in case disk is full
To find files changed in last N days :
find -mtime -N –print
Example: find -mtime -2 -print
To find CPU & Memory detail of linux:
cat /proc/cpuinfo (CPU)
cat /proc/meminfo (Memory)
Linux: cat /proc/cpuinfo|grep processor|wc -l
HP: ioscan -fkn -C processor|tail +3|wc -l
Solaris: psrinfo -v|grep “Status of processor”|wc –l
psrinfo -v|grep “Status of processor”|wc –l
lscfg -vs|grep proc | wc -l
To find if Operating system in 32 bit or 64 bit:
ON Linux: uname -m
On 64-bit platform, you will get: x86_64 and on 32-bit patform , you will get:i686
On HP: getconf KERNEL_BITS
On Solaris: /usr/bin/isainfo –kv
On 64-bit patform, you will get: 64-bit sparcv9 kernel modules and on 32-bit, you will get: 32-bit sparc kernel modules. For solaris you can use directly: isainfo -v
If you see out put like: “32-bit sparc applications” that means your O.S. is only 32 bit but if you see output like “64-bit sparcv9 applications” that means youe OS is 64 bit & can support both 32 & 64 bit applications.
To find if any service is listening on particular port or not:
netstat -an | grep {port no}
Example: netstat -an | grep 1523
To find Process ID (PID) associated with any port:
This command is useful if any service is running on a particular port (389, 1521..) and that is run away process which you wish to terminate using kill command
lsof | grep {port no.} (lsof should be installed and in path)
How to kill all similar processes with single command:
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory:
find . -print |grep -i test.sql
To remove a specific column of output from a UNIX command:
For example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk ‘{ print $2 }’
Changing the standard prompt for Oracle Users:
Edit the .profile for the oracle user
PS1=”`hostname`*$ORACLE_SID:$PWD>”
Display top 10 CPU consumers using the ps command:
/usr/ucb/ps auxgw | head -11
Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l
Display the number of CPU’s in Solaris:
psrinfo -v | grep “Status of processor”|wc -l
Display the number of CPU’s in AIX:
lsdev -C | grep Process|wc -l
Display RAM Memory size on Solaris:
prtconf |grep -i mem
Display RAM memory size on AIX:
First determine name of memory device: lsdev -C |grep mem
then assuming the name of the memory device is ‘mem0’ then the command is: lsattr -El mem0
Swap space allocation and usage:
Solaris : swap -s or swap -l
Aix : lsps -a
Total number of semaphores held by all instances on server:
ipcs -as | awk ‘{sum += $9} END {print sum}’
View allocated RAM memory segments:
ipcs -pmb
Manually deallocate shared memeory segments:
ipcrm -m ”
Show mount points for a disk in AIX:
lspv -l hdisk13
Display occupied space (in KB) for a file or collection of files in a directory or sub-directory:
du -ks * | sort -n| tail
Display total file space in a directory:
du -ks .
Cleanup any unwanted trace files more than seven days old:
find . *.trc -mtime +7 -exec rm {} ;
Locate Oracle files that contain certain strings:
find . -print | xargs grep rollback
Locate recently created UNIX files:
find . -mtime -1 -print
Finding large files on the server:
find . -size +102400 -print
Crontab Use:
To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1
For more related Linux/Solaris Basic command click on the link: Basic Linux/Solaris Command
Advertisements

Basic Linux Command for Beginners

The purpose of this document is to understand the Basic Linux command in a fast or simple way. It assumes that we have very little exposure of Linux command.

If we are familiar with DOS command then it is easy to understand Linux command with the following differences:

  1. In Linux/UNIX, commands and filenames are case sensitive, meaning that typing “EXIT” instead of the proper “exit” is a mistake.
  2. In DOS, the forward-slash “/” is the command argument delimiter, while the backslash “” is a directory separator. In Linux/UNIX, the “/” is the directory separator, and the “” is an escape character.
  3. The DOS world uses the “eight dot three” filename convention, meaning that all files followed a format that allowed up to 8 characters in the filename, followed by a period (“dot”), followed by an option extension, up to 3 characters long (e.g. FILENAME.TXT). In UNIX/Linux, there is no such thing as a file extension. Periods can be placed at any part of the filename, and “extensions” may be interpreted differently by all programs, or not at all.

cat

Display the contents of a text file on the screen. For example: cat contact.txt would display the file we created in the previous section.

tail -f

Display the last few lines of a text file, and then output appended data as the file grows (very useful for following log files!). Example: tail -f /var/log/messages

top

Displays CPU processes in a full-screen GUI. A great way to see the activity on your computer in real-time. Type “Q” to quit.

echo

Display text on the screen. Mostly useful when writing shell scripts. For example: echo “Hello World”

find

It can be used to search for files matching certain patterns, as well as many other types of searches. example: find . -name *mp3. This example starts searching in the current directory “.” and all subdirectories, looking for files with “mp3” at the end of their names.

locate

A quick way to search for files anywhere on the file system. For example, you can find all files and directories that contain the name “oracle” by typing: locate oracle

which

Shows the full path of shell commands found in your path. For example, if you want to know exactly where the “grep” command is located on the filesystem, you can type “which grep”. The output should be something like: /bin/grep

cp, mv

First one Copies a file from one location to another. Example: cp mp3files.txt /tmp (copies the mp3files.txt file to the /tmp directory) and second one moves a file to a new location, or renames it. Example: mv mp3files.txt /tmp (copy the file to /tmp, and delete it from the original location)

rm

Delete a file. Example: rm /tmp/mp3files.txt

mkdir, rmdir

First one Make Directory. Example: mkdir /tmp/myfiles/ and second one Remove Directory. Example: rmdir /tmp/myfiles/

ls

List all files in the current directory, in column format For example: ls /usr/bin/d* This command list all files whose names begin with the letter “d” in the /usr/bin directory.

cat /proc/cpuinfo, cat /proc/meminfo

Display information about CPU and Memory usage

uname -a

Prints system information to the screen (kernel version, machine type, etc.)

ps

Lists currently running process (programs).

more, less

First command Display a file, or program output one page at a time and second one is the improved replacement of first one allows you to move backward as well as forward. Example: ls -la | more

grep

Search for a pattern in a file or program output. For example, to find out which TCP network port is used by the “nfs” service, you can do this: grep .nfs. /etc/services. This looks for any line that contains the string “nfs” in the file “/etc/services” and displays only those lines.

su

It allows you to switch to another user account temporarily. Example: su Shahid, su – (Switch to root, and log in with root’s environment)

“history”

It shows your complete command history.

chmod

To change file access permission.

Note: Press (CTRL-R) and then type any portion of a recent command. It will search the commands for you, and once you find the command you want, just press ENTER.

Help: When you are struck and needing help with a Linux command just press “-h” or “help”

Example:  grep  — help (built in help)

Example: man ls (get the help on ls) or man man (a manual about how to use the manual)

Example: info df (load information page)

For more frequently used OS Command for DBA click on the link: Frequently used OS (Linux/Solaris/AIX) Command for DBA

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.

UNIX Useful Commands

1) Find whether OS is 64/32 Bit Kernel in UNIX.

uname -a

2) Find free physical memory in UNIX.

free -m

3) Find CPU details in UNIX.

cat /proc/cpuinfo

4) Find files modified within specific time.

find . -mtime -3 (modified less than 3days ago)

5) command used to alter file permissions.

chmod 777 abc.txt

6) Command used to reset the Ownership.

chown oracle:dba abc.txt

7) command used to set, or reset, the users login password.

Passwd username

8) Kill specific process in UNIX.

Kill -9 processid

9) Command used for display last given lines of a file.

tail -n alert_PROD.log

10) Command used for intall a rpm package.

rpm -ivh packagename.rpm

11) Command used to querry about any rpm package

rpm -q packagename

12) Command to Check the server up time

uptime

13) Command to check the file versions

strings -a <filename> |grep ‘$Header’

14) Command will keep ‘n’ number of days files and remove rest of file.

find . -mtime +n -exec rm  {} ; &

15) Basic commands for  vi editor

i   :- insert before cursor.

l   : insert begining of the line.

a  :- append after the cursor.

A  :- Append at the end of the line.

o :- insert a blank line below the cursor.

O :- insert a blank line above the cursor position.

h :- from current position one char towards left .

I :- from current position one char towards right.

j :- from current position one line towards down.

k :- from current position one line towards up.

Shift+g  :- go to end of the file.

Shift+:1 :- go to top of the file.

dd –> delete the ful line.

:q! —> closing the file without saving any changes.

:wq! –> save the changes and force close.

:w –> to save the changes without closing the file.

Make operating system user non expiry

Many times proper functioning of application demands that the account/password of the OS (Linux/UNIX) user which owns the application should not expire.

chage is the OS command by which we can control the number of days between password changes and the date of the last password change.

The chage command is restricted to the root user, except for the -l option, which may be used by an unprivileged user to determine when the password or account is due to expire.

Let us make password of ‘oracle’ OS user account as non-expiry.

1) CHECK THE CURRENT STATUS OF USER

2) CHANGE AND MAKE IT NON-EXPIRY

# chage -m -1 -M -1 -I -1 -E -1 oracle

Where:

[-m mindays]
[-M maxdays]
[-d lastday]
[-I inactive]
[-E expiredate]
[-W warndays]

3) VERIFY

[root@rclnperp20 ~]# chage -l oracle

UNIX Crontab Basics

A crontab file has five fields for specifying day , date and time followed  by the command to be run at that interval.
#——————————————————————————————
# Minute     Hour    Month Day      Month       Weekday                      Command
#  0-59       0-23      1-31                 1-12           0-6 (0=Sunday)
#——————————————————————————————
* * * * * command to be executed
– – – – –
| | | | |
| | | | +—– day of week (1 – 7) (monday = 1)
| | | +——- month (1 – 12)
| | +——— day of month (1 – 31)
| +———– hour (0 – 23)
+————- min (0 – 59)
crontab -e Edit your crontab file, or create one if it doesn’t already  exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file.
Example:
To run the calendar command at 6:30 a.m. every Monday, Wednesday, and Friday, enter:

To run the calendar command every day of the year at 6:30, enter the following:

To run a script called maintenance every day at midnight in August, enter the following:

You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use  crontab if your name does not appear in the file  /usr/lib/cron/cron.deny.  If only cron.deny exists and is empty, all users can use crontab. If neither  file exists, only the root user can use crontab. The allow/deny files  consist of one user name per line.

Useful UNIX/LINUX commands for DBAs

Below are some of the useful UNIX/LINUX commands which we use frequently:

–LIST YOUR FILES

ls — lists your files
ls -l — lists your files in ‘long format’, which contains lots of useful information, e.g. the  exact size of the file, who owns the file and who has the right to look at it, and when it was last modified.
ls -a — lists all files, including the ones whose filenames begin in a dot, which you do  not always want to see.

–COMPRESSING THE FILE

gzip filename — compresses files, so that they take up much less space. There are other tools for this  purpose, too (e.g. compress), but gzip usually gives the highest compression rate. Gzip produces files with the ending ‘.gz’ appended to the original filename.
gunzip filename — uncompresses files compressed by gzip.

–PRINTING RELATED COMMANDS 

lpr filename — print to default printer
lpr -P<printer_name> filename — Use the -P option to specify the printer name if you want to  use a printer other than your default printer.
lpq — check out the default printer queue.
lpq <printer_name>— check out specific printer queue.
lprm jobnumber — remove specific job queue from the printer queue. Y

–DIRECTORY RELATED COMMANDS

mkdir dirname — make a new directory
cd dirname — change directory.  ‘cd ..‘ will get you one level  up from your current position.
pwd — tells you where you currently are.

–MOVING, RENAMING, AND COPYING FILES

cp file1 file2 —copy a file
mv file1 newname — move or rename a file
rm file1 [file2 ...] —remove or delete a file
rm -r dir1 [dir2...] —recursivly remove a directory and its contents

–VIEWING AND EDITING FILES

cat filename —Dump a file to the screen in ascii.
more filename— Progressively dump a file to the screen: ENTER = one line  down SPACEBAR = page down q=quit
less filename —Like more, but you can use Page-Up too. Not available on all systems.
vi filename —Edit a file using the vi editor. All UNIX systems will have vi in some form.
head filename —Show the first few lines of a file.
head -n filename —Show the first n lines of a file.tail filename —Show the last few lines of a file.
tail -n filename —Show the last n lines of a file

–FIND COMMAND

find search_path -name filename — Find command format
find . -name aaa.txt —Finds all the files named aaa.txt in the current  directory or  any subdirectory tree.
find / -name vimrc —Find all the files named ‘vimrc’ anywhere on the  system.
find /usr/local/games -name “*xpilot*” —Find all files whose names contain the string  ‘xpilot’ which  exist within the ‘/usr/local/games’ directory tree.

–VI EDITOR – VERY IMPORTANT TO LEARN

Start with vi <filename>

Edit modes: These keys enter editing modes and type in the text of your document.

i —Insert before current cursor position

I —Insert at beginning of current line

a —Insert (append) after current cursor position

A —Append to end of line

r —Replace 1 character

R —Replace mode

<ESC> —Terminate insertion or overwrite mode

Deletion of text

x —Delete single character

dd —Delete current line and put in buffer

ndd —Delete n lines (n is a number) and put them in buffer

J —Attaches the next line to the end of the current line (deletes carriage return).

Undo

u —Undo last command

Cut and Paste

yy —Yank current line into buffer

nyy —Yank n lines into buffer

p —Put the contents of the buffer after the current line

P —Put the contents of the buffer before the current line

Cursor Positioning

ctrl-d —Page down

ctrl-u —Page up

:n —Position cursor at line n

:$ —Position cursor at end of file

^g —Display current line number

h,j,k,l —Left,Down,Up, and Right respectivly. Your arrow keys should also work

String Substitution

:n1,n2:s/string1/string2/[g] —Substitute string2 for string1 on lines n1 to n2. If g is included (meaning global),
all instances of string1 on each line are substituted. If g is not included, only the first instance per matching line is substituted.

^ —matches start of line

. —matches any single character

$ —matches end of line

These and other “special characters” (like the forward slash) can be “escaped” with

i.e to match the string “/usr/STRIM100/SOFT” say “/usr/STRIM100/SOFT”

Examples:

:1,$:s/dog/cat/g —Substitute ‘cat’ for ‘dog’, every instance

for the entire file – lines 1 to $ (end of file)
:23,25:/frog/bird/ —Substitute ‘bird’ for ‘frog’ on lines 23 through 25. Only the first instance on each line is substituted.

Saving and quitting commands
These commands are all prefixed by pressing colon (:) and then entered in the lower left corner of the window.

Press <ESC> to exit from an editing mode.

:w —Write the current file.

:w new.file —Write the file to the name ‘new.file’.

:w! existing.file —Overwrite an existing file with the file currently being edited

:wq —Write the file and quit.

:q —Quit.

:q! —Quit with no changes.

:e filename —Open the file ‘filename’ for editing.

:set number —Turns on line numbering

:set nonumber —Turns off line numbering

–KILLING ALL LOCAL=NO PROCESS BY SINGLE COMMAND  

kill ps -ef | grep <INSTNAME> |grep LOCAL=NO| egrep -v grep | awk '{print $2}'

or

ps -ef| grep -i <INSTNAME> | grep LOCAL=NO| awk {‘print $2′}| xargs kill -9

-CUSTOMIZE UNIX COMMAND PROMPT

Put below value in .profile of user

HOSTNAME=$(uname -n)
PS1='(${?})${LOGNAME}@${HOSTNAME}:${ORACLE_SID:-“NO_SID”}: ${PWD}
$ ‘
PS2=’> ‘

–MOUNT COMMAND

mount -t nfs -o <options> server-name-or-ip-address:/<file_system_to_mount> /<file_system_to_mount>

–To find the number of physical CPUs:

$ cat /proc/cpuinfo | grep "^physical id" | sort | uniq | wc -l
 2

–To find the number of cores per CPU:

$ cat /proc/cpuinfo | grep "^cpu cores" | uniq
cpu cores : 4

The total number of processors available is the number of physical CPUs multiplied by the number of cores per CPU.

–To find the total number of processors:

$ cat /proc/cpuinfo | grep "^processor" | wc -l
 16