Monday, October 12, 2009

Difference between a PFILE and SPFILE.

Today I'm going to write about oracle PFILE and SPFILE parameters. Let's have some definitions first:

- PFILE is statics text file that must be updated with a standard text editor like "notepad" or "vi".
This file normally resides on the server and DBA's commomly refer to this file as INIT.ora file.

- SPFILE or Server Parameter File is as persistent binary file that can only be modified with the "ALTER SYSTEM SET" command. Editing an SPFILE will corrupit it and you will not be able to start your database.
Well, now how do I know if my database is using a PFILE or SPFILE? Let's try executing below query into database:

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type" FROM sys.v_$parameter WHERE name = 'spfile';


File T

------
SPFILE


SQL>


You can also use the v$spparameter view to check if you are using a PFILE or not.


SQL> desc v$spparameter

Name Null? Type

----------------------------------------- -------- ----------------------------

SID VARCHAR2(80)
NAME VARCHAR2(80)

VALUE VARCHAR2(255)
DISPLAY_VALUE VARCHAR2(255)
ISSPECIFIED VARCHAR2(6)

ORDINAL NUMBER
UPDATE_COMMENT VARCHAR2(255)

SQL> select NAME from v$spparameter where name like '%pfile%';


NAME
--------------------------------------------------------------------------------
spfile


SQL>

If the column value is NULL for all parameters, you are using a PFILE.
I'll update this post with more tips soon!!!

Regards,
Marcello Travassos.

Oracle Statspack Survival Guide.

Hello!

STATSPACK is a performance diagnosis tool, available since Oracle8i. For everybody looking for a good place to start....check this out!!

Oracle Statspack Survival Guide

Don't forget to have a look at Oracle Docs also!
Oracle9i Database Performance Tuning Guide and Reference - Release 2 (9.2)


Jonathan Lewis has good examples also!

Good Reading!!

Regards,
Marcello Travassos.

UNIX Commands for DBAs.

Hi,

Below I'm going to show some good commands for DBAs under Unix environment.

Basic File Navigation
The "pwd" command displays the current directory:

$> pwd
/u01/app/oracle/product/9.2.0.1.0

The "ls" command lists all files and directories in the specified directory. If no location is defined it acts on the current directory:

$> ls
$> ls /u01
$> ls -al

The "-a" flag lists hidden "." files.
The "-l" flag lists file details.

The "cd" command is used to change directories:
$> cd /u01/app/oracle

The "touch" command is used to create a new empty file with the default permissions:
$> touch my.log

The "rm" command is used to delete files and directories:
$> rm my.log
$> rm -R /archive

The "-R" flag tells the command to recurse through subdirectories.
The "mv" command is used to move or rename files and directories:
$> mv (from) (to)
$> mv my.log my1.log
$> mv * /archive
$> mv /archive/* .

The "." represents the current directory
The "cp" command is used to copy files and directories:
$> cp [from] [to]
$> cp my.log my1.log
$> cp * /archive
$> cp /archive/* .

The "mkdir" command is used to create new directories:
$> mkdir archive

The "rmdir" command is used to delete directories:
$> rmdir archive

The "find" command can be used to find the location of specific files:
$> find / -name dbmspool.sql
$> find / -print | grep -i dbmspool.sql

The "/" flag represents the staring directory for the search.
Wildcards such as "dbms*" can be used for the filename.

The "which" command can be used to find the location of an executable you are using:
oracle> which sqlplus

The "which" command searches your PATH setting for occurences of the specified executable.

File Permissions

The "umask" command can be used to read or set default file permissions for the current user:
$> umask 022

The umask value is subtracted from the default permissions (666) to give the final permission:
666 : Default permission
022 : - umask value
644 : final permission

The "chmod" command is used to alter file permissions after the file has been created:
$> chmod 777 *.log

Character eqivalents can be used in the chmod command:
$> chmod o+rwx *.log
$> chmod g+r *.log
$> chmod -Rx *.log

The "chown" command is used to reset the ownership of files after creation:
$> chown -R oinstall.dba *

The "-R" flag causes the command ro recurse through any subdirectories.

OS Users Management
The "useradd" command is used to add OS users:
$> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user

The "-G" flag specifies the primary group.
The "-g" flag specifies the secondary group.
The "-d" flag specifies the default directory.
The "-m" flag creates the default directory.
The "-s" flag specifies the default shell.

The "usermod" command is used to modify the user settings after a user has been created:
$> usermod -s /bin/csh my_user

The "userdel" command is used to delete existing users:
$> userdel -r my_user

The "-r" flag removes the default directory.
The "passwd" command is used to set, or reset, the users login password:
$> passwd my_user

The "who" command can be used to list all users who have OS connections:
$> who
$> who | head -5
$> who | tail -5
$> who | grep -i ora
$> who | wc -l

The "head -5" command restricts the output to the first 5 lines of the who command.
The "tail -5" command restricts the output to the last 5 lines of the who command.
The "grep -i ora" command restricts the output to lines containing "ora".
The "wc -l" command returns the number of lines from "who", and hence the number of connected users.

Process Management
The "ps" command lists current process information:
$> ps
$> ps -ef | grep -i ora

Specific processes can be killed by specifying the process id in the kill command:
$> kill -9 12345

Uname and Hostname
The "uname" and "hostname" commands can be used to get information about the host:
$> uname -a
Linux vmlinux3.example.com 2.6.9-42.0.0.0.1.ELsmp #1 SMP Sun Oct 15 14:02:40 PDT 2006 i686 i686 i386 GNU/Linux

$> uname -a | awk '{ print $2 }'

vmlinux3.example.com

$> hostname
vmlinux3.example.com

Error Lines in Files
You can return the error lines in a file using:

$> cat alert_ORATEST10g.log | grep -i ORA-

The "grep -i ORA-" command limits the output to lines containing "ORA-". The "-i" flag makes the comparison case insensitive. A count of the error lines can be returned using the "wc" command. This normally give a word count, but the "-l" flag alteres it to give a line count:

$> cat alert_ORATEST10g.log | grep -i ORA- | wc -l

File Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:

#!/bin/ksh
if test -s /backup/daily_backup.log
then
DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
fi

Remove Old Files
The find command can be used to supply a list of files to the rm command:

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;

Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

sed -e 's/^M$//' filename > tempfile

The newly created tempfile should have the ^M character removed.

Run Commands As Oracle User From $
The following scripts shows how a number of commands can be run as the "oracle" user the "$" user:

#!/bin/ksh
su - oracle <
ORACLE_SID=LIN1; export ORACLE_SID
rman catalog=rman/rman@w2k1 target=/ cmdfile=my_cmdfile log=my_logfile append
EOF

This is often necessary where CRON jobs are run from the $ user rather than the oracle user.

Compress Files
In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the gzip or the compress commands. The gzip command results in a compressed copy of the original file with a ".gz" extension. The gunzip command reverses this process:

gzip myfile
gunzip myfile.gz

The compress command results in a compressed copy of the original file with a ".Z" extension. The uncompress command reverses this process:

compress myfile
uncompress myfile

General Performance
$ vmstat 5 3
$ sar -u 10 8
$ mpstat 10 2

ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:

SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = '&pid';

Hope it helps!

Regards,
Marcello Travassos.

OS commands to diagnose Database Performance issues.

How to use OS commands to diagnose Database Performance issues?

Below you will find some helpful commands to diagnose database performance issues like memory, swap space, CPU % usage and idle, IO usage in the server in general and also to find a few most OS resouce consuming processes to get better understanding about how the UNIX server is performing and whether any of these resources are causing a bottleck in the database performance or not.
Since the popular UNIX systems do not support the same commands or even same options for a command.
Please refer to the corresponding OS man pages for detailed information about each UNIX command mentioned in this post.

Solaris:
$ /usr/sbin/prtconf |grep -i "Memory size"
$ swap -s
$ df -k
$ /usr/local/bin/top
$ vmstat 5 100
$ sar -u 2 100
$ iostat -D 2 100
$ mpstat 5 100

Out of these commands, top command may not be installed in your server by default.
In that case,you can get it for free from www.sunfreeware.com and install it preferably under /usr/local/bin directory and then use it. Please take a few snpshots of the top command output and store it in a file.It refreshes the output screen every 5 sec.

-Prtconf command will show how much Physical Memory (RAM) the Solaris server has.
-Swap command will provide us with the usage of swap space including the RAM.
-Df command will indicate how much space is free in each mount point and also provides information about swap space(s).
-Top command wil provide the above information plus information about top CPU consuming processes, CPU usage in the system, etc.
-Vmstat will provide information about process run queue, memory usage, paging and swapping and CPU % usage in the server. Different options in vmstat can provide more specific information, if required.
-Iostat provides IO usage by Disk, CPU % usage, etc. depending on the options used. Sar command with "-u" option also provides CPU usage and idle time information.
-Mpstat will provide CPU usage stats for a Solaris server with 1 or more CPUs.
This command is very useful for multi-processor system to provide details about the CPU usage by every CPU in the server.

HP-UX 11.0:
$ grep Physical /var/adm/syslog/syslog.log
$ df -k
$ sar -w 2 100
$ sar -u 2 100
$ /bin/top
$ vmstat -n 5 100
$ iostat 2 100
$ top

AIX:
$ /usr/sbin/lsattr -E -l sys0 -a realmem
$ /usr/sbin/lsps -s
$ vmstat 5 100
$ iostat 2 100
$ /usr/local/bin/top # May not be installed by default in the server

Linux [RedHat 7.1 and RedHat AS 2.1]:
$ dmesg | grep Memory
$ vmstat 5 100
$ /usr/bin/top

Please take at least 10 snapshots of the "top" command to get an idea aboud most OS resource comsuming processes in the server and the different snapshot might contain a few different other processes and that will indicate that the use of resouces are varying pretty quickly amound many processes.

Regards,
Marcello Travassos.

Finding particular Data Dictionary Table or View.

Hi,
Sometimes I need to execute some selects into database and to get it done I have to remember the name of some Data Dictionary Tables and/or Dynamic Performance views regularly, but sometimes I really don't remember that particular view. For sure it’s not easy to remember all the dictionary views!

Instead, there is a data dictionary view called “DICTIONARY” and this view can help us to find the particular or list of pertinent views of any object in the database that we are looking for.

From oracle documentation the DICTIONARY view is a data dictionary view used to find the list of Data Dictionary and Dynamic Performance (V$) views and it contains description of data dictionary tables and views. Lets try it...

SQL> desc dictionary
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

SQL>

Please check this out for more info:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4196.htm#i1629189

The O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges.
If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior).
The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in the SYS schema.
Read more about here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams134.htm#CHDGCHHA

1-) In order to find the list of data dictionary tables and dynamic performance views.

SQL>SELECT * FROM dict;

2-) In order to find the particular data dictionary table or dynamic performance view.

SELECT * FROM dictionary WHERE table_name LIKE '%Search_String%';

Playing with this view...

SQL> SELECT * FROM dictionary WHERE table_name LIKE '%REDO%';

TABLE_NAME COMMENTS
------------------ --------------------------------------------------------
V$BACKUP_REDOLOG Synonym for V_$BACKUP_REDOLOG
GV$BACKUP_REDOLOG Synonym for GV_$BACKUP_REDOLOG

SQL> SELECT * FROM dictionary WHERE table_name='DICTIONARY';

TABLE_NAME COMMENTS
------------ --------------------------------------------------------
DICTIONARY Description of data dictionary tables and views

SQL>

Note: While querying, either the complete word DICTIONARY or first four characters DICT, which is a synonym for DICTIONARY, can be used.

Reference at "Oracle® Database Reference 10g Release 2 (10.2)"
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

Well, for me this is very helpfull!

Happy Reading!
Marcello Travassos.

Welcome to My Blog!!

Hello Everybody!

This is my first post in this blog ! ! !.

Regards,
Marcello Travassos.

PS.: In order to develop this blog with full of technicle articles, please feel free to leave your precious comments or suggestions about my blog.