OraNA :: Oracle News Aggregator

Semaphores and shared memory are two very distinct sets of Operating System resources.

Semaphores are a system resource that Oracle utilizes for interprocess communication and they occupy a relatively small memory space, while shared memory is utilized to contain the SGA and can garner a large portion of physical memory.

That is message on Oracle Doc.

Before we install Oracle, we should tune kernel parameters for Semaphores and shared memory.

System Parameters
=================

SHMMAX - kernel parameter controlling maximum size of one shared memory segment
SHMMNI - kernel parameter controlling maximum number of shared memory segments in the system
SHMSEG - kernel parameter controlling maximum number of shared memory segments a process can attach
SEMMNS - kernel parameter controlling maximum number of semphores in the system
SEMMNI - kernel parameter controlling maximum number of semaphore sets. Semphores in Unix are allocated in sets of 1 to SEMMSL.
SEMMSL - kernel parameter controlling maximum number of semaphores in a semphore set.
SHMLBA - kernel parameter controlling alignment of shared memory segments; all segments must be attached at multiples f this value.


Anyway How can I display Semaphores and shared memory ?


# $ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "testdb" :
Shared Memory:
ID KEY
1108115457 0x15fb4638

Semaphores:
ID KEY
23003136 0xa6a3e088

Oracle Instance alive for sid "testdb"

"sysresv", that is commamd from oracle software:
usage : sysresv [-if] [-d ] [-l sid1 ...]
-i : Prompt before removing ipc resources for each sid
-f : Remove ipc resources silently, oevrrides -i option
-d : List ipc resources for each sid if on
-l sid1 .. : apply sysresv to each sid

Default : sysresv -d on -l $ORACLE_SID

If you need to remove ipc resouce:

Example:

$ $ORACLE_HOME/bin/sysresv -if -l testdb

IPC Resources for ORACLE_SID "testdb" :
Shared Memory:
ID KEY
1108115457 0x15fb4638

Semaphores:
ID KEY
23003136 0xa6a3e088
Oracle Instance alive for sid "testdb"

SYSRESV-005: Warning
Instance maybe alive - aborting remove for sid "testdb"

>>>

If you need to display Semaphores and shared memory by unix command:

you can use "ipcs" command:

ipcs [ -asmq ] [ -tclup ]
ipcs [ -smq ] -i id
ipcs -h

Example:

$ ipcs -a

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status
0x15fb4638 1108115457 oracle 660 4096 0
0x5a164a00 1100414980 oracle 660 4096 0

------ Semaphore Arrays --------

key semid owner perms nsems
0xa6a3e088 23003136 oracle 660 204
0x8d3ac6b4 14876675 oracle 660 104

>>>

$ ipcs -l

------ Shared Memory Limits --------

max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 16777216
min seg size (bytes) = 1

------ Semaphore Limits --------

max number of arrays = 100
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 128
semaphore max value = 32767

------ Messages: Limits --------

max queues system wide = 16
max size of message (bytes) = 8192
default max size of queue (bytes) = 16384

>>>

$ ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status
0x15fb4638 1108115457 oracle 660 4096 0
0x5a164a00 1100414980 oracle 660 4096 0

>>>

$ ipcs -s

------ Semaphore Arrays --------

key semid owner perms nsems
0xa6a3e088 23003136 oracle 660 204
0x8d3ac6b4 14876675 oracle 660 104

And you need to remove Semaphores and shared memory (use "ipcrm")

ipcrm [ -M key -m id -Q key -q id -S key -s id ] ...

Shared Memory Segments:

$ ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status
0x15fb4638 1108115457 oracle 660 4096 0
0x5a164a00 1100414980 oracle 660 4096 0

$ ipcrm -m 1100414980

$ ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status
0x15fb4638 1108115457 oracle 660 4096 0

Semaphores:

$ ipcs -s

------ Semaphore Arrays --------

key semid owner perms nsems
0xa6a3e088 23003136 oracle 660 204
0x8d3ac6b4 14876675 oracle 660 104

$ ipcrm -s 14876675

$ ipcs -s

------ Semaphore Arrays --------
key semid owner perms nsems


We use "ipcrm" to remove "shared memory segment", when we shutdown Oracle database and no reallocate "shared memory segment".

Before use "ipcrm" to remove Semaphores and shared memory, we must check.... and check, Because it can make Oracle Instance DOWN!

I need to relay mail to MTA Server[Mail Server]
Anyway no start sendmail service

How?

1. Make sure OS can resolve DNS

# dig google.com a
-- FOUND RESULT--

Or use "nslookup"
# nslookup google.com
-- FOUND RESULT--

If can not resolve DNS, try to check "/etc/resolv.conf" file ("root" user)

Example:
nameserver dns.server.com

and TEST
.
.
.

2. Edit submit.cf file ("root" user)

Assume: Mail server = mail.server.com
Go to /etc/mail PATH and edit submit.cf file

D{MTAHost}[127.0.0.1]

Edit to ->

D{MTAHost}[mail.server.com]

and stop sendmail service:
# /etc/init.d/sendmail stop

and then check:

# netstat -an | grep 25
--NOT FOUND--
# ps -aef | grep sendmail
--NOT FOUND--

3. Test

# echo "TEST" | sendmail email@domain

# cat /var/log/maillog

Jun 30 00:50:37 hostname sendmail[14352]: m5THobfw014352: from=root, size=5, class=0, nrcpts=1, msgid=<200806291750.m5thobfw014352@hostname>, relay=root@localhost

Jun 30 00:50:38 hostname sendmail[14352]: m5THobfw014352: to=email@domain, ctladdr=root (0/0), delay=00:00:01, xdelay=00:00:01, mailer=relay, pri=30005, relay=[mail.server.com] [mail.server.com], dsn=2.0.0, stat=Sent (m5THok2L014587 Message accepted for delivery)

...

I need to monitor everyone login server [assume -> linux OS] and "opened" status:


How?

use "root" user =>

1. Modify /etc/syslog.conf file (retrive information about authen... to /var/log/auth.log file)

.
.
.
auth,authpriv.* /var/log/auth.log


2. create /var/log/auth.log file and restart syslog service:

# touch /var/log/auth.log
# chmod 600 /var/log/auth.log
# /etc/init.d/syslog restart
...

3. write program (assume: /root/sbin/getauth.pl file):

#!/usr/bin/perl


my $LOCAL_TIME;
my $LOG_FILE='/var/log/auth.log';
my $TMP_FILE='/root/sbin/.auth.log';
my $smail='sender@domain';
my $rmail='to@domain';
my $content='opened';
my $chk=0;
my $host=`hostname`;

system ("/bin/cat /dev/null > $TMP_FILE");
system ("/usr/bin/tail -0f $LOG_FILE | /usr/bin/tee -a $TMP_FILE &");
while ( 1 ) {

$chk=`/bin/cat $TMP_FILE | /bin/grep $content |/usr/bin/wc -l` ;

if ($chk > 0){
unless(open (MAIL, "|/usr/sbin/sendmail -t")) {
print "error.\n";
warn "Error starting sendmail: $!";
}else{
print MAIL "From: $smail\n";
print MAIL "To: $rmail\n";
print MAIL "Subject: [Info] Session opened on $host\n\n";
print MAIL "------------------------------------------\n";
print MAIL `/bin/cat $TMP_FILE | /bin/grep $content`;
print MAIL "\n";
print MAIL "------------------------------------------\n";
close(MAIL) || warn "Error closing mail: $!";
print "Mail sent.\n";
}
system ("/bin/cat /dev/null > $TMP_FILE");
}
sleep 5;
}

4. start program to background process...

# /root/sbin/getauth.pl > /dev/null 2>&1 &

# ps -aef | grep getauth.pl

root 24933 1 0 Apr25 ? 00:00:38 /usr/bin/perl /root/sbin/getauth.pl

5. Test login to server and check e-mail


.
.
.

Enjoy...

SQL> alter table "OWNER"."XXX" modify partition "XXX_PART02" shrink space;

ORA-08102: index key not found, obj# 10837, file 23, block 5548 (2)

ORA-08102: index key not found, obj# string, file string, block string (string)
Cause: Internal error: possible inconsistency in index
Action: Send trace file to your customer support representative, along with information on reproducing the error


SQL> select tablespace_name from dba_data_files where file_id=23;

TABLESPACE_NAME
------------------------------
XXX_INDX02

SQL> select index_name, partition_name from dba_ind_partitions where tablespace_name='XXX_INDX02';

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
XXX_INDX03 XXX_INDX01_PART02
XXX_INDX01 XXX_INDX01_PART02
XXX_INDX02 XXX_INDX01_PART02


So, I rebuilt every indexes on XXX_INDX02 tablespace:

SQL> ALTER INDEX "OWNER"."XXX_INDX01" REBUILD PARTITION "XXX_INDX01_PART02" online;

After rebuild indexes, I can shrink tablespace....no problem:

SQL> alter table "OWNER"."XXX" modify partition "XXX_PART02" shrink space;


Enjoy...

Today ,I think about the key length to create index on Oracle.

So I look for the information on Internet.

Error: ORA 1450

Text: maximum key length exceeded
-------------------------------------------------------------------------------

Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length.
The maximum index length varies by operating system. The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns.
Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22.Numeric length = (precision/2) + 1. If negative, add +1.

Action: Select columns to be indexed so the total index length does not exceed
the maximum index length for the operating system.
See also your operating system-specific Oracle documentation.


Oracle Reference =>

>>>

How it is Calculated
------------------------------------------------------
When creating an Index, the total length of the index cannot exceed a certain value. This value depends primarily on the DB_BLOCK_SIZE. If an attempt is made to create an index larger than the Maximum value, an ORA-1450 is raised:

ORA-01450 maximum key length (758) exceeded ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

The number in parends is the maximum allowable length of the index key for that particular system.

So, how is this number calculated?

The maximum key size means:

The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) + the length of the rowid (1 byte).

The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

For Funtion-based indexes, we must calculate the length of the return type.

This index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block.

Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. The Oracle 8i Administrator's Guide states that the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the block according to PCTFREE, INITRANS, and space for block overhead (Block Header, ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.


Using default values for these storage options, the maximum length for indexes is as follows for different block sizes:

DB_BLOCK_SIZE: Maximum Index Key Length:
============== =========================

2K (2048) 758 Bytes
4K (4096) 1578 Bytes
8K (8192) 3218 Bytes
16K (16384) 6498 Bytes


If you hit a maximum key length in an index according to the DB_BLOCK_SIZE, you may need to recreate the database with a larger block size. The other alternative is to limit the size of the index. This is slightly more difficult with a Function-based index, when the return type is a varchar or RAW.

To limit the size of a function-based index you should consider using the SUBSTR or SUBSTRB function, to limit the number of Characters or Bytes returned. For more information on SUBTR and SUBSTRB, refer to the Oracle8i SQL Reference Guide.

>>>

NOTE: In Oracle9i or above, the maximum key length is larger

Example: on Oracle 11G

SQL> create table temp01 (data1 varchar2(4000), data2 varchar2(4000));

Table created.

SQL>
SQL>
SQL> create index idx_temp01 on temp01(data1,data2);

create index idx_temp01 on temp01(data1,data2)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SQL> show parameter db_block_size;

NAME TYPE VALUE

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

db_block_size integer 8192


Enjoy...

ORACE 10.2.0.3.0 RAC linux
.... AGENT Oracle Grid 10.2.0.4.0

Oracle RAC rebooted, After that I check system log:

Jun 15 16:02:58 db01 last message repeated 6 times
Jun 15 16:02:58 db01 kernel: VFS: file-max limit 65536 reached
Jun 15 16:02:58 db01 last message repeated 6 times
Jun 15 16:02:58 db01 kernel: VFS: file-max limit 65536 reached
Jun 15 16:02:58 db01 last message repeated 3 times
Jun 15 16:02:58 db01 kernel: VFS: file-max limit 65536 reached
Jun 15 16:02:58 db01 last message repeated 4 times
Jun 15 16:02:58 db01 kernel: VFS: file-max limit 65536 reached
Jun 15 16:02:58 db01 last message repeated 3 times
Jun 15 16:02:58 db01 logger: Oracle CSSD shell script failure. Duplicate CSSD.
Jun 15 16:02:58 db01 kernel: md: stopping all md devices.
Jun 15 16:02:58 db01 kernel: md: md0 switched to read-only mode.
Jun 15 16:02:58 db01 kernel: VFS: file-max limit 65536 reached
-----------------------------------------------------------------

I don't know something wrong [who had used many files......], So
I have to check on other nodes.

check open files in system by root user:

# lsof | awk '{print $2}' | sort -n | uniq -c | sort -n
.
.
.
64 28513
67 25226
45225 17956

# ps -aef | grep 17956

oracle 17956 17938 0 16:12 pts/2 00:00:03 /oracle/.../bin/emagent


Actually, that can check file use by:

$ cat /proc/sys/fs/file-nr

63345 0 65536


So, I restarted [Oracle Enterprise Manager 10g Release 4 Grid Control ] Agent:

$ /oracle/.../bin/emctl stop agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.Stopping agent .... stopped.

$ cat /proc/sys/fs/file-nr

18120 0 65536

$ /oracle/.../bin/emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent .......... started.

$ cat /proc/sys/fs/file-nr

18120 0 65536

>>>

This problem is about Agent defferenct Version...... with Oracle Database Software.
>>>

I don't know, It's a BUG..... or NOT

I think I should ==> reinstall agent [because can not rollback AGENT]
Or..... Upgrade Oracle Software [Database + Cluster]


Enjoy///

When we create materialized view log using primary key,
that oracle creates TEMPORARY TABLE => RUPD$_ :

They created for Java RepAPI.

Example:
> CREATE MATERIALIZED VIEW LOG ON TABLE_NAME WITH PRIMARY KEY;

> SELECT * FROM TAB;

TABLE_NAME TABLE
MLOG$_TABLE_NAME TABLE
RUPD$_TABLE_NAME TABLE

DESC MLOG$_TABLE_NAME

Name Null? Type
----------------------------------------------------------------- --------
ID NUMBER(10)
SNAPTIME$ DATE
DMLTYPE$ VARCHAR2(1)
OLD_NEW$ VARCHAR2(1)
CHANGE_VECTOR$ RAW(255)


DESC RUPD$_TABLE_NAME

Name Null? Type
----------------------------------------------------------------- --------
ID NUMBER(10)
DMLTYPE$ VARCHAR2(1)
SNAPID NUMBER(38)
CHANGE_VECTOR$ RAW(255)


...

"Just one year after launching the iPhone, we’re launching the new iPhone 3G that is twice as fast at half the price," said Steve Jobs, Apple’s CEO. "iPhone 3G supports Microsoft Exchange ActiveSync right out of the box, runs the incredible third party apps created with the iPhone SDK, and will be available in more than 70 countries around the world this year."


Features

- 3G-capable. 2.8 times faster than EDGE.
- Simultaneous voice and data conections
- Microsoft Exchange AcctiveSync
- GPS built-in
- Better battery life - 300 hours of standby, Talk-time 10 hours (as opposed to 5) on 2G networks, 5 hours of 3G talk-time (competition is 3 hour 3G talk time), 5 to 6 hours of high-speed browsing, 7 hours of video, 24 hours of audio.
- flush headphone jack

Pricing and Availability

The iPhone 3G will be available July 11th in 22 countries for $199 for 8 GB and $299 for 16 GB. The 16 GB model also comes in white. Apple has posted details on their website, but it is currently overwhelmed.

...

At today's Worldwide Developer Conference Keynote address, Apple previewed iPhone Firmware 2.0 + additional features of the developer SDK.

New Features of Firmware 2.0 + the SDK, including:

- contact searching
- complete iWork document support
- complete Office document support (now includes PowerPoint)
- bulk delete and move for Mail
- save images you receive
- scientific calculator in landscape mode
- parental controls
- language

Availability

iPhone Firmware 2.0 will be available for free to existing iPhone customers in "early July". The firmware will also be available for iPod Touch users for $9.95.

...

KGAS = > is a component in the server (wait event), that handles TCP/IP sockets on Oracle 10.2+; (packages -> UTL_TCP, UTP_SMTP, UTP_HTTP, ...)

Statement to see which sessions:

SQL> SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='TCP Socket (KGAS)' and total_waits > 0 ORDER BY 3,2;


SID TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
303 6 2
280 18 5
285 37 9
279 37 10
281 37 10
290 37 11

Reducing Waits:
- Check the current SQL / module / action of v$session, after that check your network from that program.

Example:
Check trace file from "TCP Socket (KGAS)" waits:

alter session set events '10046 trace name context forever, level 8';
select utl_http.request('http://10.10.10.5') from dual;

=>
SQL>
SQL>
SQL> set lines 100
SQL> set pages 1000
SQL> set serverout on
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select distinct par.value '/' lower(instance_name) '_ora_' spid '.trc' "Trace file 2 name" 3 from v$instance i, v$process p, v$mystat m, v$session s, v$parameter par 4 where s.paddr = p.addr 5 and s.sid = m.sid 6 and par.name='user_dump_dest';


Trace filename
------------------------------------------------------------------------------

/oracle/.../db_ora_9713.trc

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select utl_http.request('http://10.10.10.5') from dual;

$ tkprof /oracle/.../testdb2_ora_9713.trc file.out
.
.
.
Event waited on Times Max. Wait Total Waited
----------------------------------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 3 195.89 204.96
TCP Socket (KGAS) 1 188.99 188.99
SQL*Net break/reset to client 2 0.00 0.00
.
.
.

or you can check by (telnet)

$ telnet 10.10.10.5 80

Trying 10.10.10.5...telnet:
connect to address 10.10.10.5:
Connection timed outtelnet: Unable to connect to remote host: Connection timed out

...