Month: March 2012

Oracle: semget failed!

digitaleagle Oracle DBA, Uncategorized Leave a comment

I had an issue with starting a database a while back.  Basically, I ran out of resources on the server, and I had to adjust the kernel parameters.  Here are the details…

Error message:

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

Read More

Script: Check Flash Recovery Usage

digitaleagle Oracle DBA, Uncategorized Leave a comment

I’ve been trying to monitor the archive log usage for our databases, and I came up with this little script.  I thought I would share in case it would help anyone, but also, please correct me if there is a better way or you see anything wrong.

Here’s the basic version.  You can change the database names in the for loop.


tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in FN91DMO FN91CFG FN91DEV FN91TST
do
 export ORACLE_SID=$e
 sqlplus -s / as sysdba >> $tmpfile <<!
 set head off
 set feedback off
 select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
 from v\$flash_recovery_area_usage
 where PERCENT_SPACE_USED > 0;
 exit;
!
done
sed '/^$/d' $tmpfile
rm $tmpfile

This next version calls a script to get the database names.  For example, I have the databases stored in an XML file, and I built a perl script that would read the XML file and print the database names.


tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in $(buildDBList)
do
 export ORACLE_SID=$e
 sqlplus -s / as sysdba >> $tmpfile <<!
 set head off
 set feedback off
 select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
 from v\$flash_recovery_area_usage
 where PERCENT_SPACE_USED > 0;
 exit;
!
done
sed '/^$/d' $tmpfile
rm $tmpfile

Finally, this version calls a script to check the status of the database.  If the database is down or temporarily off, it skips that database.


tmpfile=/tmp/$(whoami)sql.txt
echo "DB_NAME FILE_TYPE PERCENT_SPACE_USED NUMBER_OF_FILES" > $tmpfile
echo "------- -------------------- ------------------ ---------------" >> $tmpfile
for e in $(buildDBList)
do
 export ORACLE_SID=$e
 checkDBStatus $e 2>&1 > /dev/null
 if [ $? -eq 0 ]; then
   sqlplus -s / as sysdba >> $tmpfile <<!
   set head off
   set feedback off
   select '$e' DB_NAME, FILE_TYPE, PERCENT_SPACE_USED, NUMBER_OF_FILES
   from v\$flash_recovery_area_usage
   where PERCENT_SPACE_USED > 0;
   exit;
!
 fi
done
sed '/^$/d' $tmpfile
rm $tmpfile

Resources