Systems Engineering and RDBMS

Archive for March 20th, 2007

Removing Semaphore and Memory for a crashed Oracle Instance

Posted by decipherinfosys on March 20, 2007

Sometimes we run into issue where database has not released its shared memory and semaphore because of abrupt shutdown or particular instance crashed. This becomes more critical when there are multiple instances running on the same machine. Unix provides ipcs command to see the semaphores and shared memory segments used by Oracle. Normally when oracle is shutdown, semaphore and shared memory segments are released.  To check run following command from $ prompt on the unix box.

$ipcs | grep oracle

If instance is up and running or instance has gone down but memory and semaphores are not released you will see some entries there. It will have two segments. Shared memory (denoted by m) and Semaphores (denoted by s). If you are running single instance, it is very easy to release the semaphore and shard memory using following command.

$ipcrm -m id (id is the id displayed for memory in ipcs under memory section)
$ipcrm -s id (id is the id displayed for semaphore in ipcs under semaphore section)

You have to be extra careful when doing this. This is to be done only when instance is not up but memory and/or semaphore are not released. So please make sure that none of the oracle process is running at this time (which should be the case).

When there are multiple instances running on the same database server, it becomes more crucial to identify which shared memory and semaphore set needs to be killed. Here is the link to an article which explains this scenario in great detail.

http://www.idevelopment.info/data/Oracle/DBA_tips/Unix/UNIX_2.shtml

Posted in Oracle | Leave a Comment »

Checking database parameters in Oracle

Posted by decipherinfosys on March 20, 2007

A very easy way to check parameter values for Oracle database is to issue following command from SQL*Plus prompt. It is followed by abbreviated output.

SQL>show parameter

NAME                        TYPE        VALUE
————————— ———– ——————————
background_core_dump        string      partial
background_dump_dest        string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\BDUMP
backup_tape_io_slaves       boolean     FALSE
bitmap_merge_area_size      integer     1048576
blank_trimming              boolean     FALSE

To view specific values of parameters related to SGA command should be

SQL>show parameter area

NAME                        TYPE        VALUE
————————— ———– ——————————
bitmap_merge_area_size      integer     1048576
create_bitmap_area_size     integer     8388608
hash_area_size              integer     131072
sort_area_retained_size     integer     0
sort_area_size              integer     65536
workarea_size_policy        string      AUTO

Alternatively Oracle provides two dynamic performance views to look at all the parameter values specified for the database.

V$Parameter – provides information about all parameters. It also indicates whether parameters are modifiable at session level or system level. Following is the query to look for parameters related to SGA.

SQL> SELECT name,value,isses_modifiable,issys_modifiable
FROM v$parameter
WHERE name like ‘%sga%’;

NAME            VALUE           ISSES ISSYS_MOD
————— ————— —– ———
sga_max_size    612368384       FALSE FALSE
pre_page_sga    FALSE           FALSE FALSE
lock_sga        FALSE           FALSE FALSE
sga_target      612368384       FALSE IMMEDIATE

V$NLS_Parameters – provides information about all parameters related to NLS settings.

SQL> select * from v$nls_parameters;

PARAMETER                      VALUE
—————————— ——————————
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8MSWIN1252
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

Certain parameters can be changed dynamically using ALTER SYSTEM command. Other parameter values need to be changed in init.ora or spfile or both.

Posted in Oracle | 2 Comments »