Unix for Oracledba - www.oracle.com/technology/dba/index.html

www.oracle.com/technology/dba/index.html


man man command Manual Pages - Help with any UNIX command
man ps Help on the UNIX ps command

clear clear To clear the screen

pwd pwd Present / Current Working Directory

cd cd [directoryname] Change directory, without argument will change your working directory to your home directory.
cd work Change working directory to "work"
cd .. Change working directory to parent directory (.. is parent and . is current directory)

ls ls [-options] [names] List files. [names] if omitted, will list all files and subdirectories in the directory. Wild cards can be specified.
ls -l List files with date and permissions
-rw-rw-r-- 1 oracle dba 706 Sep 23 17:26 storparms.sql

-rwxrwx--- 1 oracle dba 377 Aug 28 15:00 sysdelstat.sql

drwxrwxr-- 2 oracle dba 2048 Oct 22 16:12 work
[column1] [2] [3] [4] [5] [6] [7]
Column1 - Permissions of the file or directory; r-read, w-write, x-execute
Position 1 indicates if it is a directory
Positions 2-4 is the permission for owner
Positions 5-7 is the permission for group
Positions 8-10 is the permission for others
Column2 - Owner of the file/directory
Column3 - Group which the owner belogs to
Column4 - Size of the file in bytes
Column5 - Last Modified Date
Column6 - Last Modified Time
Column7 - Name of the file/directory
ls -al List files with date and permissions including hidden files
ls -lt List files with date, sorted in the date modified
ls -ltr bt* List files with date, sorted in the date modified, oldest first, with filenames starting with bt

Wildcards * Any character, any number of positions
? Any character, one position
[] A set of characters which match a single character position.
- To specify a range within []
ls *x* List all files which contains an x in any position of the name.
ls x* List all files which start with x
ls *T0[1-3]ZZ List all files which contain T0 followed by 1,2 or 3 followed by ZZ. The following files match this condition:
analyzeall.AAAT01ZZ
dbaoc_err.AAAT03ZZ
dbstart_log.AAAT03ZZ
calerterr.AAAT01ZZ
dbaoc_log.AAAT01ZZ
ls job?.sql List files which start with job followed by any single character followed by .sql
Example: jobd.sql jobr.sql
ls alert*.???[0-1,9] alert_AAAT01ZZ.1019
alert_AAAD00ZZ.1020
alert_AAAI09ZZ.1021

touch - touch filename Create a 0 byte file or to change the timestamp of file to current time (wild cards as above can be used with the file names)

mkdir mkdir directoryname Create Directory
mkdir -p directorypath Create directory down many levels in single pass
mkdir -p /home/biju/work/yday/tday

rmdir rmdir directoryname Remove directory

rm rm filename Remove file
rm -rf directoryname Remove directory with files. Important - There is no way to undelete a file or directory in UNIX. So be careful in deleting files and directories. It is always good to have rm -i filename for deletes

cp cp filename newfilename Copy a file
cp -r * newloc To copy all files and subdirectories to a new location, use -r, the recursive flag.

mv mv filename newfilename Rename (Move) a file. Rename filename to newfilename.
mv filename directoryname Move filename under directoryname with the same file name.
mv filename directoryname/newfilename Move filename to directoryname as newfilename.
mv * destination If you use a wildcard in the filename, mv catenates all files to one sigle file, unless the destination is a directory.
cp -i file1 file2
mv -i file1 file2
rm -i file* Use the -i flag with rm, mv and cp to confirm before destroying a file.

file file filename To see what kind of file, whether editable. Executable files are binary and you should not open them.
file d* dbshut: ascii text
dbsnmp: PA-RISC1.1 shared executable dynamically linked -not stripped
dbstart: ascii text
dbv: PA-RISC1.1 shared executable dynamically linked -not stripped
demobld: commands text
demodrop: commands text

vi vi filename Edit a text file. Vi is a very powerful and "difficult to understand" editor. But once you start using, you'll love it! All you want to know about vi are here. More vi tricks later!!

cat cat filename See contents of a text file. cat (catenate) will list the whole file contents. Cat is mostly used to catenate two or more files to one file using the redirection operator.
cat file1 file2 file3 > files Catenate the contents of file1, file2 and file3 to a single file called files. If you do not use the redirection, the result will be shown on the standard output, i.e., screen.

more
page more filename
page filename Show the contents of the file, one page at a time. In more/page, use space to see next page and ENTER to see next line. If you wish to edit the file (using vi), press v; to quit press q.

tail tail -n filename To see the specified number of lines from the end of the file.

head head -n filename To see the specified number of lines from the top of the file.

pg pg filename To show the contents of the file, page by page. In pg, you go up and down the pages with + and - and numbers.

1 First Page of the file
$ Last Page of the file
+5 Skip 5 pages
-6 Go back 6 pages
ENTER Next page
- Previous Page
q Quit
/string Search for string

env env To see value of all environment variables.
To set an environment variable: In ksh or sh "export VARIABLENAME=value", Note there is no space between =.
In csh "setenv VARIABLENAME value"
echo $VARIABLENAME See value of an environment variable

echo echo string To print the string to standard output
echo "Oracle SID is $ORACLE_SID" Will display "Oracle SID is ORCL" if the value of ORACLE_SID is ORCL.

lp lp filename To print a file to system default printer.

chmod chmod permission filename Change the permissions on a file - As explained under ls -l, the permissions are read, write, execute for owner, group and others.
You can change permissions by using numbers or the characters r,w,x. Basically, you arrive at numbers using the binary format.
Examples:
rwx = 111 = 7
rw_ = 110 = 6
r__ = 100 = 4
r_x = 101 = 5
chmod +rwx filename
chmod 777 filename Give all permissions to everyone on filename
chmod u+rwx,g+rx,o-rwx filename
chmod 750 filename Read, write, execute for owner, read and execute for group and no permission for others

chown chown newuser filename Change owner of a file

chgrp chgrp newgroup filename Change group of a file
chown newuser:newgroup filename Change owner and group of file

compress compress filename Compress a file - compressed files have extention .Z. To compress file you need to have enough space to hold the temporary file.

uncompress uncompress filename Uncompress a file

df df [options] [moutpoint] Freespace available on the system (Disk Free); without arguments will list all the mount points.
df -k /ora0 Freespace available on /ora0 in Kilobytes. On HP-UX, you can use "bdf /ora0".
df -k . If you're not sure of the mount point name, go to the directory where you want to see the freespace and issue this command, where "." indicates current directory.

du du [-s] [directoryname] Disk used; gives operating system blocks used by each subdirectory. To convert to KB, for 512K OS blocks, divide the number by 2.
du -s gives the summary, no listing for subdirectories

find Find files. find is a very useful command, searches recursively through the directory tree looking for files that match a logical expression. It has may options and is very powerful.
find /ora0/admin -name "*log" -print Simple use of find - to list all files whose name end in log under /ora0/admin and its subdirectories
find . -name "*log" -print -exec rm {} \; to delete files whose name end in log. If you do not use the "-print" flag, the file names will not be listed on the screen.

grep Global regular expression print to search for an expression in a file or group of files. grep has two flavours egrep (extented - expands wild card characters in the expression) and frep (fixed-string - does not expand wild card characters). This is a very useful command, especially to use in scripts.
grep oracle /etc/passwd to display the lines containing "oracle" from /etc/passwd file.
grep -i -l EMP_TAB *.sql to display only the file names (-l option) which contains the string EMP_TAB, ignore case for the string (-i option), in all files with sql extention.
grep -v '^#' /etc/oratab display only the lines in /etc/oratab where the lines do not (-v option; negation) start with # character (^ is a special character indicating beginning of line, similarly $ is end of line).

ftp ftp [hostname] File Transfer Protocol - to copy file from one computer to another
ftp AAAd01hp invoke ftp, connect to server AAAd01hp.
Connected to AAAd01hp.com.
220 AAAd01hp.com FTP server (Version 1.1.214.2 Mon May 11 12:21:14 GMT 1998) ready.
Name (AAAd01hp:oracle): BIJU program prompts for user name, enter the login name to AAAd01hp.
331 Password required for BIJU.
Password: enter password - will not be echoed.
230 User BIJU logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> ascii Specifying to use ASCII mode to transfer files. This is used to transfer text files.
200 Type set to A.
ftp> binary Specifying to use binary mode to transfer files. This is used for program and your export dump files.
200 Type set to I.
ftp> ls To see the files in the remote computer.
200 PORT command successful.
150 Opening ASCII mode data connection for /usr/bin/ls.
total 8
-rw-rw-rw- 1 b2t dba 43 Sep 22 16:01 afiedt.buf
drwxrwxrwx 2 b2t dba 96 Jul 9 08:47 app
drwxrwxrwx 2 b2t dba 96 Jul 9 08:49 bin
-rw-rw-rw- 1 b2t dba 187 Jul 30 14:44 check.sql
226 Transfer complete.
ftp> get check.sql transfer the file check.sql from the remote computer to the local computer. The file will be copied to the present directory with the same name. You can optionally specify a new name and directory location.
200 PORT command successful.
150 Opening BINARY mode data connection for check.sql (187 bytes).
226 Transfer complete.
187 bytes received in 0.02 seconds (7.79 Kbytes/s)
ftp> !ls ! runs commands on the local machine.
AAAP02SN a4m08.txt tom3.txt
a4m01.txt
ftp> put a4m01.txt /tmp/test.txt transfer file from local machine to remote machine, under /tmp directory with name test.txt.

mail mail "xyz@abc.com" < message.log Mail a file to internet/intranet address. mail the contents of message.log file to xyz@abc.com
mail -s "Messages from Me" "xyz@abc.com" "abc@xyz.com" < message.log mail the contents of message.log to xyz and abc with a subject.

who who [options] to see who is logged in to the computer.
who -T Shows the IP address of each connection
who -r Shows when the computer was last rebooted, run-level.

ps ps process status - to list the process id, parent process, status etc. ps without any arguments will list current sessions processes.
ps -f ull listing of my processes, with time, terminal id, parent id, etc.
ps -ef as above for all the processes on the server.

kill kill [-flag] processid to kill a process - process id is obtained from the ps command or using the v$process table in oracle.
kill 12345 Kill the process with id 12345
kill -9 12345 To force termination of process id 12345

script script logfilename to record all your commands and output to a file. Mostly useful if you want to log what you did, and sent to customer support for them to debug. start logging to the logfilename. The logging is stopped when you do "exit".

hostname hostname displays the name of the computer.

uname uname -a to see the name of the computer along with Operating system version and license info.

date date displays the current date and time.
date "+%m%d%Y" displays date in MM/DD/YYYY format

cal cal displays calender of current month
cal 01 1991 Displays January 1991 Calender

telnet telnet [hostname] to open a connection to another computer in the network. Provide the alias name or IP address of the computer.

& command & add & to the end of the command to run in background
nohup command & no hangup - do not terminate the background job even if the shell terminates.

fg fg to bring a background job to foreground

bg bg to take a job to the background. Before issuing this command, press ^Z, to suspend the process and then use bg, to put it in the background.

jobs jobs to list the current jobs in the shell.

rcp rcp [-r] sourcehost:filename destinationhost:filename Remote copy. Copy files from one coputer to another. To set up the computer for remote copy and remote login (rlogin) will be discussed later.
rcp host1:/ora0/file1.txt host2:/ora0/temp/file1.txt Copy file from host1 to host2. If the computer name is omitted, the hostname is assumed.

DO YOU KNOW....
You can identify the semaphore id of an Oracle SGA (this gives semaphores, shared memory sizes also) from Server Manager using the command "oradebug ipc"
svrmgrl
SVRMGRL> connect internal
SVRMGRL> oradebug ipc


To see errors from Alert log file
cd alertlogdirectory;
grep ORA- alertSID.log

To see the name of a user from his unix id (Provided your UNIX admin keeps them!)
grep userid /etc/passwd

To see if port number 1521 is reserved for Oracle
grep 1521 /etc/services

To see the latest 20 lines in the Alert log file:
tail -20 alertSID.log

To see the first 20 lines in the Alert log file:
head -20 alertSID.log

To find a file named "whereare.you" under all sub-directories of /usr/oracle
find /usr/oracle -name whereare.you -print

To remove all the files under /usr/oracle which end with .tmp
find /usr/oracle -name "*.tmp" -print -exec rm -f {} \;

To list all files under /usr/oracle which are older than a week.
find /usr/oracle -mtime +7 -print

To list all files under /usr/oracle which are modified within a week.
find /usr/oracle -mtime -7 -print

To compress all files which end with .dmp and are more than 1 MB.
find /usr/oracle -size +1048576c -name "*.dmp" -print -exec compress {} \;

To see the shared memory segment sizes
ipcs -mb

To see the space used and available on /oracle mount point
df -k /oracle

To see the users logged in to the server and their IP address
who -T

To change passwd of oracle user
passwd oracle

To convert the contents of a text file to UPPERCASE
tr "[a-z]" "[A-Z]" < filename > newfilename

To convert the contents of a text file to lowercase.
tr "[A-Z]" "[a-z]" < filename > newfilename

To kill a process from Unix.
kill unixid
OR
kill -9 unixid

To see the oracle processes
ps -ef | grep SIDNAME

To see the number of lines in a text file (can be used to find the number of records while loading data from text file).
wc -l filename

To change all occurrences of SCOTT with TIGER in a file
sed 's/SCOTT/TIGER/g' filename > newfilename

To see lines 100 to 120 of a file
head -120 filename | tail -20

To truncate a file (for example listener.log file)
rm filename; touch filename

To see if SQL*Net connection is OK.
tnsping SIDNAME

To see if the server is up.
ping servername
OR
ping IPADDRESS

To see the versions of all Oracle products installed on the server.
$ORACLE_HOME/orainst/inspdver
DO YOU KNOW....
You can give permissions for all users to read the trace files generated by Oracle7 (in USER_DUMP_DEST directory) by including the parameter _TRACE_FILES_PUBLIC = TRUE in init.ora file and cycle the database (note the parameter starts with "_" )

RMAN fullbackup - shellscript

#/usr/bin/sh
export ORACLE_SID=db01
export ORACLE_HOME=/home/oracle/product/10.2.0.1
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/product/10.2.0.1/bin:/home/oracle/bin
echo "ORACLE_SID -> ${ORACLE_SID}"
echo "ORACLE_HOME -> ${ORACLE_HOME}"
#-----------------------------------------------
dt=`date +%Y%m%d`
SUBJECT="db01 rmanbackup errors please investigate "
SUBJECTP="db01 backup DONE"
EMAIL_ADDRESS="mail2satheeshmba@gmail.com"
BODY="check the log files and space utilization "
thresold=90
verror=""
outofspace=" The disks is out of space "

#----------------------------------------------
$ORACLE_HOME/bin/rman nocatalog log = /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt<connect target /
configure controlfile autobackup on;
configure device type disk parallelism 4;
configure retention policy to redundancy 7;
configure channel 1 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman1_%U.bk';
configure channel 2 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman2_%U.bk';
configure channel 3 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman3_%U.bk';
configure channel 4 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman4_%U.bk';
backup database plus archivelog delete all input;
crosscheck backup;
delete noprompt obsolete;
exit

verror= `cat /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt |grep -i error | wc -l`
# the above should be a log file > in crontab
if [ $verror -gt 0 ]; then
echo "$BODY" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS"
echo $verror

fi
exit;

CRONTAB - HOW TO SUBMIT JOB

How to add jobs to cron under Linux ?


To edit your crontab file, type the following command:
$ crontab -e


Syntax of crontabYour cron job looks like as follows:

1 2 3 4 5 /path/to/command arg1 arg2

Where,
• 1: Minute (0-59)
• 2: Hours (0-23)
• 3: Day (0-31)
• 4: Month (0-12 [12 == December])
• 5: Day of the week(0-7 [7 or 0 == sunday])
• /path/to/command - Script or command name to schedule
Same above five fields structure can be easily remembered with following diagram:
* * * * * command to be executed
- - - - -
| | | | |
| | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)
Example(s)
If you wished to have a script named /root/backup.sh run every day at 3am, my crontab entry would look like as follows:
(a) Install your cronjob:# crontab -e(b)Append following entry:0 3 * * * /root/backup.shRun five minutes after midnight, every day:5 0 * * * /path/to/commandRun at 2:15pm on the first of every month:15 14 1 * * /path/to/commandRun at 10 pm on weekdays: 0 22 * * 1-5 /path/to/command Run 23 minutes after midnigbt, 2am, 4am ..., everyday:23 0-23/2 * * * /path/to/commandRun at 5 after 4 every sunday:5 4 * * sun /path/to/command

RMAN Backups

Monitor running RMAN Backups:


prompt RMAN Backup Status:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'

Script to recomplile invalid objects in oracle database

@$ORACLE_HOME/rdbms/admin/utlrp.sql

How to create a Oracle Database in unicode

Creating a Database with a Unicode Character Set
To create a database with the AL32UTF8 character set, use the CREATE DATABASE statement and include the CHARACTER SET AL32UTF8 clause. For example:
CREATE DATABASE sample
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE
'disk1:df1.dbf' AUTOEXTEND ON,
'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp_ts
UNDO TABLESPACE undo_ts
SET TIME_ZONE = '+02:00';