RAC instance startup failed with : ORA-27154: post/wait create failed

Today, one of the client database was down. Trying to start with

$ srvctl start instance -d TEST -i TEST3

PRCR-1013 : Failed to start resource ora.test.db

PRCR-1064 : Failed to start resource ora.test.db on node test-rac03

CRS-5017: The resource action "ora.test.db start" encountered the following

error:ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpcreates

CRS-2674: Start of 'ora.test.db' on 'test-rac03' failed

Tried through SQL plus,

SQL> startupORA-27154: post/wait create failedORA-27300: OS system dependent operation:semget failed with status: 28ORA-27301: OS failure message: No space left on deviceORA-27302: failure occurred at: sskgpcreates

checked in trace fileStarting ORACLE instance (normal)Errors in file /u01/app/oracle/TEST/diag/rdbms/test/TEST3/trace/TEST3_ora_10781.trc:ORA-27154: post/wait create failedORA-27300: OS system dependent operation:semget failed with status: 28ORA-27301: OS failure message: No space left on deviceORA-27302: failure occurred at: sskgpcreatesWed Nov 09 15:57:53 2011

From the trace filedbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)----- Error Stack Dump -----

ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpcreates

$sudo su -

# /sbin/sysctl -a | grep semkernel.sem = 250 32000 100 142

Increased the semophores value to 180

This is a linux host, so I changed the value and re-initialize immediately.

cd /etc

vi sysctl.conf

look for kernel.sem

change the last value from 140 to 180

[root@npe-ora-rac03 etc]# /sbin/sysctl -p

net.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 1net.ipv4.conf.default.accept_source_route = 0kernel.sysrq = 1kernel.core_uses_pid = 1net.ipv4.tcp_syncookies = 1kernel.msgmnb = 65536kernel.msgmax = 8192kernel.shmmax = 4398046511104kernel.shmall = 1073741824fs.file-max = 6815744kernel.msgmni = 2878kernel.sem = 250 32000 100 180kernel.shmmni = 4096net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576fs.aio-max-nr = 3145728net.ipv4.ip_local_port_range = 9000 65500vm.min_free_kbytes = 51200

srvctl start instance -d TEST -i TEST3

**Issue resolved**

Oracle Streams ORA-01403: no data found...

Today one of our client reporting database has got 73 - ORA-01403: no data found errors. And their Business objects reports were out-of-sync.

I will explain here, how I resolved it using packages and package bodies....very interesting...

ORA-00257 archiver error

User reported that they are getting ORA-00257 error, when they trying to access the production database.

ORA-00257 commonly related to Archiver not able to write archive logs into File system/ asm disk.

After checking, I came to find out it is caused by Flash Recovery area run out space. How did I find out

SQL> select space_limit,space_used from v$recovery_file_dest;

SPACE_LIMIT SPACE_USED

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

10737418240 10737418240

I cleaned up the old archive logs using the famous RMAN command after backing up,

RMAN> crosscheck archivelog all;

Found some old archive logs, which is already backup was there in the Flash recovery area. I used the following command to delete archivelog older than 7 days.

RMAN> delete archivelog completed before 'sysdate -7';

Then users can continue with their work. Another option is, if you have space on the disk, on the fly you can increase the db_recovery_file_dest_size with addition space

sqlplus / as sysdba

SQL> alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

Why simple Oracle DBA

On a day to day activity of a Oracle DBA, you will encounter lot of small and large challenging work to resolve. This blog will record all solutions implemented and tested on production, development and test environments Databases.