DBA Quick Reference

This is a list of common commands used by oracle DBA. This reference came from “psoug” (http://www.psoug.org/reference/unix_vi.html).

General
Command Syntax	command [option] [target file]
Whatis	whatis <command>
	whatis grep
Manual	man
	man ls man -k ls man -k ls | grep list
Directory Structure
/ Root /bin Commands that can be accessed by all users /boot Static boot loader files /dev Device files /etc Host-specific configuration files /home User home directories /lib Shared libraries and kernel modules /mnt Mount point for temporary file systems /opt Add on application software /root Home directory for root user /sbin System binaries /tmp Temporary files /usr Shareable, read-only /var Variable data files
User
Change User	su <user_name>
	su root su oracle exit exit
Become root	su -
	su - exit
Current user account	whoami
	whoami su - whoami exit whoami
Navigation
Change Directory … down one level	cd <directory_name>
	cd etc
Change Directory … up one level	cd
	cd ..
Change To Root Directory	cd
	cd /
File Handling
Make Directory	mkdir <directory_name>
	mkdir morgan
Remove Directory	rmdir <directory_name>
	rmdir morgan
Remove Directory & Subdirectories	rm -i <directory_name>
	cd $HOME ls -al mkdir morgan ls -al cd morgan mkdir subdir ls -al cd .. rm -ir morgan
Create File	touch <file_name>
	touch psoug.ora
Remove A File	rm <file_name>
	rm psoug.ora
Copy	cp <old_name> <new_name>
	cp psoug.ora /
Copy with Confirmation	cp -i <old_name> <new_name>
	cp psoug.ora /
Recursive Copy	cp -ir <old_name> <new_name>
	cp -ir psoug.ora /
Secure CoPy	scp <user_name>@<server_name>:<source_path_and_file_name> <target_path_and_file_name>
	scp oracle@bigdog:/home/oracle/*.gz .
Delete	rm <file_spec>
	touch delfile ls -l rm delfile
List	ls <switches> <file_spec>
	ls ls *ora ls -l *ora ls -larn ls -larn *ora ls -lt
Move	mv <starting_file> <resulting_file>
	touch myfile ls -l mv myfile /home/myfile ls -l
Rename	mv <starting_file> <resulting_file>
	touch file1.aaa ls -l mv file1.aaa file1.bbb ls -l
Wildcards: Multiple Characters	*
	mv cp del
Wildcard: Single Character	?
	mv cp del
Change Owner	chown <owner_name> <directory_or_file_name>
	touch chngdemo ls -l chown oracle chngdemo ls -l
Change Group	chgrp <group_name> <directory_or_file_name>
	touch chngdemo ls -l chgrp dba chngdemo ls -l
Change Mode Triplets	ROOT – GROUP – USER
	drwxrwxrwx
Change Mode	chmod <code> <file_name>
	touch chngdemo ls -l chmod 755 chngdemo ls -l
CHMOD and UMASK Codes and Results	Code UMASK Result 111 666 —x–x–x 222 555 –w–w–w- 333 444 –wx-wx-wx 444 333 -r–r–r– 555 222 -r-xr-xr-x 666 111 -rw-rw-rw- 777 000 -rwxrwxrwx 124 653 —x-w-r– 644 133 —wr–r– 755 022 -rwxr-xr-x
Demo
Navigation Exercise	– navigate to root directory cd / – verify location pwd ls -l – navigate to home directory cd home – make a some directories mkdir  morgan cd morgan pwd mkdir dest mkdir src ls -l cd src pwd touch psoug ls -l mv psoug psoug.src mv psoug
Environment Manipulation
rename host server	Rename a Linux server’s name
	su cd /etc/sysconfig – change the host name entry
uname	List hardware/operating system environment
	uname -a
env	List environment variables for the current session
	Same as set. See export demo
export	Sets environment variables
	set export ORACLE_SID=orabase set
set	List environment variables for the current session
	Same as env. See export demo
command history	$HISTSIZE
	$HISTSIZE !10 !-4 $HISTSIZE=100 $HISTSIZE
command history	$HOME
	echo $HOME cd $HOME cd / cd ~
File Backup & Restore
Tape Archive (tar)	tar <switches> <file_spec> <tarball_name>
	tar -cvf * beta1RAC.tar
Compressed TAR	tar <switches> <file_spec> <tarball_name>
	tar -cxvf * beta1RAC.tar.gz
UNTAR	tar <switches> <file_spec> <tarball_name>
	tar -xvf beta1RAC.tar
UNTAR Compressed	tar <switches> <file_spec> <tarball_name>
	tar -zxvf beta1RAC.tar.gz
UNJAR A Java File	jar <switches> <file_spec> <jar_name>
	jar -xf patch.jar
CPIO	cpio -ivf <file_name>
	cpio -idv < as_linux_x86_101300_disk1.cpio
Cron Commands
crontab cronfile	Submits jobs to be scheduled
	?
crontab -e	Makes changes to an existing chron schedule
	?
crontab cronfile	Submits jobs to be scheduled
	?
Applications
Concatenate (CAT) An obscure word meaning “to connect in a series”	cat <file_name>
	cat ?
Disk Free Space	df <switches>
	df -k df -m df -h (Linux only)
View environment variable’s value	echo <environment variable>
	echo $ORACLE_SID
Find Files	find . -name “<file_name>” -print
	find . -name “grep” -print
Find archive logs older than 3 days and delete them	find
	find . -name *.arc -type f -ctime +3 -exec rm {} \;
Delete files over 14 days old	find $WORKDIR -name “<filemask>” -mtime+<days> -exec rm {}\;
	find $WORKDIR -name “*.dat.*” -mtime + 14 -exec rm {} \;
Find and delete trace files more than 7 days old	find . -name “<file_name>” -print
	find $ORACLE_BASE/admin/$ORACLE_SID -name “*.trc” -mtime +7 -exec rm -f {} \;
Global regular expression print (GREP). Prints all lines matching a certain pattern	grep [-options] pattern [filename]
	grep
Disk i/o Memory Statistics	iostat
	iostat
More	more
	cd /etc more protocols
Processor related statistics	mpstat
	mpstat
Processes	ps <switches> grep <filter>
	ps -ef ps -ef | grep ora
System Activity Statistics (Paging)	sar
	sar -B
Tee Splits the output of another command, sending it to a file and to the terminal.	tee <file_name>
	cd $HOME ls -larn ls -larn | tee zzyzx ls -larn more zzyzx
Top CPU processes	top
	top q
Virtual Memory Statistics	vmstat
	vmstat
View the full path of shell commands	which <shell command name>
	which grep which id which which
System Management
Wireless Configuration	iwconfig <switches>
	iwconfig wifi0 power off
Shared Library Dependencies	ldd <program_name>
	ldd ?
Display Loaded Kernel Modules	lsmod
	lsmod
Display Open Files	lsof
	lsof
Display Formatted Process Tree	pstree <switches>
	pstree -ca
User Management
Change Password and Expiration Information	chage
	chage ?
Force Password Change At Next Logon	chage
	chage -d0
Create Group	groupadd <group_name>
	groupadd
Display All Resource Limits for the Current User	ulimit <switches>
	ulimit -a
Display data about one or more users	finger <switches> users
	finger -l oracle
vi
create a new file or open a file for editing	vi
	vi initSID.ora
quit without saving	:q!
	:q!
save and quit	:wq
	:wq
insert	Esc i
	i
append	Esc A
	A
open a new line	Esc o
	o
replace a single character	Esc r
	r
replace a multiple characters	Esc R
	R
delete a single character	Esc x
	x
delete line	Esc dd
	dd
delete word	Esc dw
	dw
undo	Esc u
	u
SQL*Plus Shell Script Demo
bash demo	– log onto Linux as the user oracle – verify the UNIX user [oracle@gamma2 home]$ whoami oracle – log into Oracle as scott/tiger [oracle@gamma2 ~]$ sqlplus scott/tiger@orcl SQL*Plus: Release 10.2.0.1.0 – Production on Mon Nov 20 17:18:37 2006 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options – create a test table SCOTT@orcl > create table test ( 2  testcol VARCHAR2(30)); Table created. – exit SQL*Plus SCOTT@orcl >exit; [oracle@gamma2 ~]$Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Production With the Partitioning Option, Real Application Clusters, OLAP and Data Mining options – verify location is Oracle’s home directory [oracle@gamma2 ~]$ pwd /home/oracle – create a file named “morgan” [oracle@gamma2 ~]$ touch morgan – look at the file’s permissions [oracle@gamma2 ~]$ ls -al morgan -rw-r–r–  1 oracle oinstall 0 Nov 20 15:56 morgan – make it executable [oracle@gamma2 ~]$ chmod 755 morgan – verify it is executable [oracle@gamma2 ~]$ ls -al morgan -rwxr-xr-x 1 oracle oinstall 0 Nov 20 15:56 morgan – open the file using vi [oracle@gamma2 ~]$ vi morgan – insert the following w/o quotes: “touch cline” – verify the file’s contents [oracle@gamma2 ~]$ more morgan touch cline – execute it [oracle@gamma2 ~]$ ./morgan – verify the cline file was created [oracle@gamma2 ~]$ ls -al cline -rw-r–r–  1 oracle oinstall 0 Nov 20 16:04 cline – delete the “cline” file [oracle@gamma2 ~]$ rm cline – open morgan with vi, remove the TOUCH command – and put in what you see after “more morgan” below [oracle@gamma2 ~]$ vi morgan [oracle@gamma2 ~]$ more morgan sqlplus scott/tiger@orcl <<EOF INSERT INTO test (testcol) VALUES (‘Cline’); COMMIT; EXIT EOF – execute it … everything else is what scrolls on screen [oracle@gamma2 ~]$ ./morgan SQL*Plus: Release 10.2.0.1.0 – Production on Mon Nov 20 17:20:16 2006 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SCOTT@orcl > 1 row created. SCOTT@orcl > Commit complete. SCOTT@orcl > exit; Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options [oracle@gamma2 ~]$
Example Configuration Files
Export Display	export DISPLAY=localhost:0:0 echo $DISPLAY
.bash_profile	alias ob=’cd $ORACLE_BASE’ alias oh=’cd $ORACLE_HOME’ alias cm=’cd $ORACLE_HOME/oracm/log’ alias sql=’sqlplus “/ as sysdba”‘
hosts.equiv	# Comment/Uncomment those entries for your cluster below. alpha1 oracle alpha2 oracle #beta1 oracle #beta2 oracle #gamma1 oracle #gamma2 oracle delta1 oracle delta2 oracle alpha-node1 oracle alpha-node2 oracle #beta-node1 oracle #beta-node2 oracle #gamma-node1 oracle #gamma-node2 oracle #delta-node1 oracle #delta-node2 oracle # DO NOT Comment out this entry! topdog oracle