DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, November 27, 2014

ASM Diskgroup free space check script

#!/bin/bash
###############################################################################
# File Name   : asmdiskgroup_free_space.sh
# Author      : Vimal Raju
# Date        : 27/11/2014
# Description : asmdiskgroup size check
#
###############################################################################
#############################################################
#
# Display usage infommation
#
usage () {

  echo " "
  echo " Checking the asmdiskgroup free space "
  echo "       -h Display this message"
  echo "       -e specify the environment file with location (Mandatory) (Example : /home/oracle/db.env ) "
  echo "       -m specify the email address (Mandatory) (Example : test@gmail.com ) "
  echo "       -p Specify the Percentage of the asmdiskgroup size check (default : 80 )"
  echo "       -l Specify the Log file location (default : /tmp )  "
  echo "     Example : asmdiskgroup_free_space.sh -e /home/oracle/db.env -m support@doyensys.com "

  exit 1

}

validate() {

INDFILE=${LOG_LOC}/tablepace_size_check.ind
  # To check if the indicator file is there
  if [ -f ${INDFILE} ]; then
    echo "Indicator file ${INDFILE} exists!"
    exit 1
  else
    touch ${INDFILE}
  fi

  if [ ${#ENV_FILE} -eq 0 ]; then
    echo "No Environment file name specified!"
    usage
  fi

  if [ ${#MAIL_TO} -eq 0 ]; then
    echo "No Mail address specified!"
    usage
  fi

  if [ -f ${LOG_LOC}/asmdiskgroup_trigger_tmp.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/asmdiskgroup_trigger_tmp.log
  fi

  if [ -f ${LOG_LOC}/asmdiskgroup_trigger.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/asmdiskgroup_trigger.log
  fi

  if [ -f ${LOG_LOC}/asmdiskgroup_check.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/asmdiskgroup_check.log
  fi

}

asmdiskgroup() {

sqlplus -s / as sysdba <<EOF
set feed off
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
spool ${LOG_LOC}/asmdiskgroup_check.log
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
Select * from (SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v\$asm_diskgroup
ORDER BY
    name
) where pct_used > ${PERCENTAGE}  ;
spool off
EOF

trig=`cat ${LOG_LOC}/asmdiskgroup_check.log|wc -l`
check_tmp=`cat ${LOG_LOC}/asmdiskgroup_trigger_tmp.log`

if [ "${trig}" != 0 ]
then
echo C > ${LOG_LOC}/asmdiskgroup_trigger.log
elif [ "${check_tmp}" = 'T' ]
then
echo N > ${LOG_LOC}/asmdiskgroup_trigger.log
fi

check_crit=`cat ${LOG_LOC}/asmdiskgroup_trigger.log `
if [ "${check_crit}" = 'C' ]
then
cat ${LOG_LOC}/asmdiskgroup_check.log|mailx -s "Critical : ASM DISK GROUP Total allocated > ${PERCENTAGE}" $MAIL_TO
echo T > ${LOG_LOC}/asmdiskgroup_trigger_tmp.log
elif [ "${check_crit}" = 'N' ]
then
cat ${LOG_LOC}/asmdiskgroup_check.log|mailx -s "Normal : ASM DISK GROUP Total allocated < ${PERCENTAGE}" $MAIL_TO
echo "" > ${LOG_LOC}/asmdiskgroup_trigger.log
echo "" > ${LOG_LOC}/asmdiskgroup_trigger_tmp.log
fi

}


PERCENTAGE=80
LOG_LOC=/tmp
ENV_FILE=
MAIL_TO=

while getopts :hp:l:e:m: opt $*
    do
      case $opt in
        h) usage
           ;;
        p) PERCENTAGE=${OPTARG}
           echo PERCENTAGE set to ${PERCENTAGE}
           ;;
        l) LOG_LOC=${OPTARG}
           echo LOG_LOC set to ${LOG_LOC}
           ;;
        e) ENV_FILE=${OPTARG}
           echo ENV_FILE set to ${ENV_FILE}
           ;;
        m) MAIL_TO=${OPTARG}
           echo MAIL_TO set to ${MAIL_TO}
           ;;
        *) echo "******************************************************************"
           echo "${JOBNAME}: Invalid option found on command line: ${OPTARG}"
           echo "******************************************************************"
           usage
           ;;
      esac
    done


validate

. $ENV_FILE

asmdiskgroup

if [ -f ${INDFILE} ]; then
      rm -f ${INDFILE}
fi

Tablespace free space check script

#!/bin/bash
###############################################################################
# File Name   : tablespace_free_space.sh
# Author      : Vimal Raju
# Date        : 27/11/2014
# Description : Tablespace size check
#
###############################################################################
#############################################################
#
# Display usage infommation
#
usage () {

  echo " "
  echo " Checking the Tablespace free space "
  echo "       -h Display this message"
  echo "       -e specify the environment file with location (Mandatory) (Example : /home/oracle/db.env ) "
  echo "       -m specify the email address (Mandatory) (Example : test@gmail.com,test1@gmail.com ) "
  echo "       -p Specify the Percentage of the tablespace size check (default : 80 )"
  echo "       -l Specify the Log file location (default : /tmp )  "
  echo "     Example : tablespace_free_space.sh -e /home/oracle/db.env -m support@doyensys.com "

  exit 1

}

validate() {

  INDFILE=${LOG_LOC}/tablepace_size_check.ind
  # To check if the indicator file is there
  if [ -f ${INDFILE} ]; then
    echo "Indicator file ${INDFILE} exists!"
    exit 1
  else
    touch ${INDFILE}
  fi

  if [ ${#ENV_FILE} -eq 0 ]; then
    echo "No Environment file name specified!"
    usage
  fi

  if [ ${#MAIL_TO} -eq 0 ]; then
    echo "No Mail address specified!"
    usage
  fi

  if [ -f ${LOG_LOC}/tablespace_trigger_tmp.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_trigger_tmp.log
  fi

  if [ -f ${LOG_LOC}/tablespace_trigger.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_trigger.log
  fi

  if [ -f ${LOG_LOC}/tablespace_check.log ]; then
    echo ""
  else
    touch ${LOG_LOC}/tablespace_check.log
  fi

}


tablespace() {

sqlplus -s / as sysdba <<EOF
set feed off
spool ${LOG_LOC}/tablespace_check.log
col tablespace_name for a20
Select * from (select  tbs.tablespace_name,
        tot.bytes/(1024*1024) "Total Space in MB",
        round(tot.bytes/(1024*1024)-  sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
        round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
        round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) Pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1) where Pct > ${PERCENTAGE}  ;
spool off
EOF

trig=`cat ${LOG_LOC}/tablespace_check.log|wc -l`
check_tmp=`cat ${LOG_LOC}/tablespace_trigger_tmp.log`

if [ "${trig}" != 0 ]
then
echo C > ${LOG_LOC}/tablespace_trigger.log
elif [ "${check_tmp}" = 'T' ]
then
echo N > ${LOG_LOC}/tablespace_trigger.log
fi

check_crit=`cat ${LOG_LOC}/tablespace_trigger.log `
if [ "${check_crit}" = 'C' ]
then
cat ${LOG_LOC}/tablespace_check.log|mailx -s "Critical : Tablespace Total allocated > ${PERCENTAGE}" $MAIL_TO
echo T > ${LOG_LOC}/tablespace_trigger_tmp.log
elif [ "${check_crit}" = 'N' ]
then
cat ${LOG_LOC}/tablespace_check.log|mailx -s "Normal : Tablespace Total allocated < ${PERCENTAGE}" $MAIL_TO
echo "" > ${LOG_LOC}/tablespace_trigger.log
echo "" > ${LOG_LOC}/tablespace_trigger_tmp.log
fi

}


PERCENTAGE=80
LOG_LOC=/tmp
ENV_FILE=
MAIL_TO=

while getopts :hp:l:e:m: opt $*
    do
      case $opt in
        h) usage
           ;;
        p) PERCENTAGE=${OPTARG}
           echo PERCENTAGE set to ${PERCENTAGE}
           ;;
        l) LOG_LOC=${OPTARG}
           echo LOG_LOC set to ${LOG_LOC}
           ;;
        e) ENV_FILE=${OPTARG}
           echo ENV_FILE set to ${ENV_FILE}
           ;;
        m) MAIL_TO=${OPTARG}
           echo MAIL_TO set to ${MAIL_TO}
           ;;
        *) echo "******************************************************************"
           echo "${JOBNAME}: Invalid option found on command line: ${OPTARG}"
           echo "******************************************************************"
           usage
           ;;
      esac
    done


validate

. $ENV_FILE

tablespace

if [ -f ${INDFILE} ]; then
      rm -f ${INDFILE}
fi

Tuesday, November 25, 2014

Query String Parse Error


Issue:

We had a request to refresh FND_TOP alone. We stopped the application process and refreshed the FND_TOP from production.  As expected forms were not opened up so we have to relink FND module using adadmin.  After that it works perfectly fine.
Now the Issue was if we try opening any concurrent request log file using “Tools-> copy file” option it gives an error saying
Query String Parse Error.
temp_id=3114441888 
Solution:
We have to forcefully relink FNDWRR.exe executable to make it work. 
$cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDWRR.exe" 
Remarks:
If the above solution didn't work, you may refer the below Metalink Note. 
Note 112934.1 Query String Parse Error When Opening a Log File 
Note 157578.1 Query String Parse Error when Viewing Request Out or Log Files
Note 743888.1 ERROR IN CONCURRENT REQUEST AT 'QUERY STRING PARSE ERROR.TEMP_ID 

Monday, November 3, 2014

Commands to rebuild Index, Partitioned Index, LOB Index, IOT (Index Organized Table)

Find the below commands to rebuild Index, Partitioned Index, LOB Index, IOT (Index Organized Table),

To rebuild the index:

set pagesize 1000

SELECT 'ALTER INDEX ' || OWNER ||'.'||index_name ||' REBUILD tablespace TARGET_TABLESPACE PARALLEL 8 ONLINE COMPUTE STATISTICS;'
from dba_indexes where tablespace_name='SOURCE_TABLESPACE';

To rebuild the partitioned index:

set pagesize 1000

SELECT 'ALTER INDEX ' || INDEX_OWNER ||'.'||index_name ||' REBUILD PARTITION '||PARTITION_NAME||' tablespace TARGET_TABLESPACE Parallel;' from dba_ind_partitions where tablespace_name='SOURCE_TABLESPACE';

To rebuild the LOB index:


select 'alter table '||owner||'.'||table_name||' move tablespace TARGET_TABLESPACE '||chr(10)||
'LOB ('||'"'||column_name||'"'||') store as '||segment_name||chr(10)||
'(tablespace TARGET_TABLESPACE);'
from dba_lobs
where owner in ('<owner_name>')
and tablespace_name='SOURCE_TABLESPACE';


To rebuild the index organized table index (IOT):


1.       Find the table name for IOT index

select owner,table_name,index_name,tablespace_name from dba_indexes where index_name='IOT_INDEX_NAME';


2.       Move that table to reubuild index

alter table OWNER.TABLE_NAME move tablespace TARGET_TABLESPACE;

EXPDP/IMPDP failing with UDE-31623: operation generated ORACLE error 31623

While exporting we got the following error,


UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1



The solution for the above error is to increase the stream pool size.
We have changed the stream pool size to 128M from 64M and started the export process which completed successfully.


SYS> show parameter stream

NAME                  TYPE             VALUE
------------------ ------------- ---------
streams_pool_size  big integer      64M


SYS> show parameter stream

NAME                  TYPE             VALUE
------------------ ------------- ---------
streams_pool_size  big integer      128M

Database is getting failed with ORA-27300: OS system dependent operation:semget failed with status: 28 while starting up the database

We got the following error while starting up the database,

SYS> startup mount pfile=initabc.ora
ORA-32006: PARALLEL_SERVER initialization parameter has been deprecated
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: sskgpbitsper


The solution is to increase the semaphore values in the kernel parameter.

We have increased the semaphore values in the kernel parameter as below and were able to startup the database after the change,


From:

[oracle@abc] cat /etc/sysctl.conf | grep -i sem
kernel.sem = 250 32000 100  128

To:

[oracle@abc] cat /etc/sysctl.conf | grep -i sem
kernel.sem = 250 32000 100  200

Enterprise Manager Cloud Control 12c Agent Keeps Going Down With java.lang.OutOfMemoryError: unable to create new native thread

If your are getting the below error while starting the 12c OEM agent,

 java.lang.OutOfMemoryError: unable to create new native thread

Then check for the below logfiles of 12c OEM agent,

$AGENT_HOME/sysman/log/emagent.nohup

emagent.nohup:


Agent is going down due to an OutOfMemoryError

----- Sat Sep  6 01:28:02 2014::31358::Checking status of EMAgent : 1685 -----
----- Sat Sep  6 01:28:02 2014::31358::EMAgent exited at Sat Sep  6 01:28:02 2014 with return value 57. -----
----- Sat Sep  6 01:28:02 2014::31358::EMAgent will be restarted because of an Out of Memory Exception. -----
----- Sat Sep  6 01:28:02 2014::31358::EMAgent is Thrashing. Exiting loop. -----
----- Sat Sep  6 01:28:02 2014::31358::Commiting Process death. -----
----- Sat Sep  6 01:28:02 2014::31358::writeAbnormalExitTimestampToAgntStmp: exitCause=OOM : restartRequired=0 -----
----- Sat Sep  6 01:28:02 2014::31358::Exited due to Thrash. -----


gcagent.log


2014-09-06 01:27:55,220 [57:GC.Executor.0 (oracle_database:abc.oracle.com7:problemTbsp_10i_Dct)] ERROR - Critical error:
java.lang.OutOfMemoryError: unable to create new native thread
        at java.lang.Thread.start0(Native Method)
        at java.lang.Thread.start(Thread.java:640)
        at java.lang.UNIXProcess$1.run(UNIXProcess.java:141)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.lang.UNIXProcess.<init>(UNIXProcess.java:103)
        at java.lang.ProcessImpl.start(ProcessImpl.java:65)
        at java.lang.ProcessBuilder.start(ProcessBuilder.java:453)
        at oracle.sysman.gcagent.common.SafeProcessBuilder.start(SafeProcessBuilder.java:120)
       at oracle.sysman.gcagent.util.channel.OSChannel.runStreamingCommand(OSChannel.java:414)
        at oracle.sysman.gcagent.util.channel.OSChannel.runCommand(OSChannel.java:449)

We are getting the above error because,

The kernel nproc limit is set too low and needs to be increased.
The more targets the agent is monitoring, the higher the nproc limit must be. We have more than 20 instance running in the server. So we have increased the nproc limit to unlimited as below and started the agent without any issues,

From:

[oracle@abc] ulimit -Su
1024

[oracle@abc] ulimit -Hu
16384


To:

[oracle@abc] ulimit -Su
unlimited

[oracle@abc] ulimit -Hu
unlimited