Resolve: kkjcre1p unable to spawn jobq slave process, slot 0, error 1089 in oracle database.

Root Cause: -

This situation occurs while we fire “shutdown immediate” in oracle database. Continuously error message are coming in alert log file.

kkjcre1p unable to spawn jobq slave process, slot 0, error 1089 in oracle database.

Error message: Linux-x86_64 Error: 23: Too many open files in system
Additional information: 1
Writing to the above trace file is disabled for now on..

Process J000 died, see its trace file
Process m001 died, see its trace file
Process m002 died, see its trace file
Process m001 died, see its trace file
Process m003 died, see its trace file


Most probably this will happened because of following reasons.

       ðŸ‘‰     If you have fired “SHUTDOWN IMMEDIATE” Command and press cntrl+C to cancel the session due to taking long time to shut it down.

Suppose database unable to shutdown and taking long time to shutdown  then try as below.

Check the process id as ps –ef|grep oracle and kill the session LOCAL=YES only.

 [oracle@xxxprod ~]$ ps -ef|grep oracle

 54323     5771     1  0 10:31 ?        00:00:00 oracleSAVIOR (LOCAL=NO)
54323      721     1  0 09:30 ?         00:00:06 oracleSAVIOR (LOCAL=NO)
54323     2079     1  1 09:47 ?        00:00:38 oracleSAVIOR (LOCAL=NO)
oracle    4266     1  0 Sep24 ?        00:00:13 ora_pmon_cdb1
oracle    4270     1  0 Sep24 ?        00:00:12 ora_psp0_cdb1

oracle    4274     1  0 Sep24 ?        00:00:00 ora_vktm_cdb1
oratest 31935 31930  0 09:33    00:00:27oraclePROD (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

$kill -9 31935

$ps –ef|grep oracle -à make sure no oracle process, before start it again.

$sqlplus / as sysdba

SQL> startup;

OR

    👉    If you have memory issue and you are unable to start/stop the database.

 Workaround:-

In case of memory issue, start it as force.

$sqlplus / as sysdba

SQL> startup force;

This will cause your database will be forcefully down and start it again. Shutdown abort will not work in this situation.



Other way.

If your are frequently getting kkjcre1p: unable to spawn jobq slave process and Linux-x86_64 Error: 23: Too many open files in system  and don't want to shutdown APPS Tier and DB Tier. Pls follow the below step to resolve this issue.



cd $ADMIN_SCRIPTS_HOME

[applmgr@***** scripts]$ sh adapcctl.sh status

You are running adapcctl.sh version 120.7.12010000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: ACE_samarth.samarth.ace-cranes.com
---------------------------------+--------------------+---------+---------
ias-component                          | process-type       |     pid    | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |    6632  | Alive
OC4JGroup:default_group          | OC4J:forms         |    6519  | Alive
OC4JGroup:default_group          | OC4J:oacore        |   34150 | Alive
OC4JGroup:default_group          | OC4J:oacore        |   33584 | Alive
OC4JGroup:default_group          | OC4J:oacore        |   33443 | Alive
HTTP_Server                             | HTTP_Server       |    6155  | Alive


adapcctl.sh: exiting with status 0

Kill the oacore pid. Automatically start , if not doing same. Need to be start it manually.

$ kill -9 34150 
$ kill -9 33584 


$ sh adapcctl.sh status

Here Status should be Alive.


Posted by at 9 comments:
Labels:

how to check tablespace size in oracle database

how to check freespace in tablespace in oracle 11g






https://youtu.be/1zyDJIpMgkY


Here I will explain how to check tablespace size in oracle. Every DBA checking the tablespace size on daily basis to maintain the free space in oracle database.

To check tablespace size in oracle database use below scripts.

 List all Tablespace size:-

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by 5 desc;

To Check tablespace size with 85% utilization.
SELECT
SUBSTR (a.tablespace_name, 1, 16) "TABLESPACE",
SUM
(b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024 "TOTAL_SIZE(MB)",
( SUM (b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024
)
- (ROUND (SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)))
"USED_SIZE(MB)",
ROUND
(SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)
) "FREE_SIZE(MB)",
MAX
(a.max_extents) "LARGEST_EXTENTS",
TO_CHAR
( 100
- ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
'999.99')
||
'%' "USED(%)" ,
TO_CHAR
( ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
'999.99'
)
||
'%' "FREE(%)"
FROM dba_tablespaces a, dba_data_files b, dba_free_space c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
GROUP
BY a.tablespace_name
HAVING
( 100 -( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id))>=85 ----here to give condtion
ORDER
BY 6 DESC;

As per our requirement we can schedule above query through crontab to pull out tablespace report on daily basis to monitor database .  We can fetch tablespace utilization in html format and send it on email.

 Steps to prepare shell sctip for tablespace report for alert on email.

1.       Prepare the shell sctip as tablespace_report.sh to monitor the tablespace size in oracle database on regular basis.

Copy the below as tablespace_report.sh

ORACLE_SID=PROD; export ORACLE_SID
. /oracle/home/oracle/ora10g/PROD_erp.env
/oracle/home/oracle/ora10g/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="8"'" toad/glprdtoad @/oracle/home/oracle/dba_scripts/cron_scripts/sql/tablespace.sql

cat /oracle/home/oracle/dba_scripts/cron_scripts/shell/mailheader.log TABLESPACE_REPORT.html | mailx -t;



2.       Prepare the sql script as tablespace.sql.
       
       Just copy the below scripts as tablespace.sql


set markup html on spool on entmap off

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 9999;
set pagesize 400;
COLUMN TABLESPACE HEADING 'TABLESPACE' ENTMAP OFF
COLUMN TOTAL_SIZE(MB) HEADING 'TOTAL_SIZE(MB)'
COLUMN USED_SIZE(MB)  HEADING 'USED_SIZE(MB)'
COLUMN LARGEST_EXTENTS HEADING 'LARGEST_EXTENTS'
COLUMN FREE_SIZE(MB) HEADING 'FREE_SIZE(MB)'
COLUMN USED_USAGE HEADING 'USED(%)'
COLUMN FREE_USAGE HEADING 'FREE(%)'

SPOOL TABLESPACE_REPORT.html

prompt <i>Here is the tablespace report &_DATE</i>

SELECT
SUBSTR (a.tablespace_name, 1, 16) "TABLESPACE",
SUM
(b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024 "TOTAL_SIZE(MB)",
( SUM (b.BYTES)
* COUNT (DISTINCT b.file_id)
/ COUNT (b.file_id)
/ 1024
/ 1024
)
- (ROUND (SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)))
"USED_SIZE(MB)",
ROUND
(SUM (c.BYTES) / 1024 / 1024 / COUNT (DISTINCT b.file_id)
) "FREE_SIZE(MB)",
MAX
(a.max_extents) "LARGEST_EXTENTS",
TO_CHAR
( 100
- ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
'999.99')
||
'%' "USED(%)" ,
TO_CHAR
( ( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id),
'999.99'
)
||
'%' "FREE(%)"
FROM dba_tablespaces a, dba_data_files b, dba_free_space c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
GROUP
BY a.tablespace_name
HAVING
( 100 -( SUM (c.blocks)
* 100
* COUNT (b.file_id)
/ (SUM (b.blocks) * COUNT (DISTINCT b.file_id))
)
/ COUNT (DISTINCT b.file_id))>=85 ----here to give condtion
ORDER
BY 6 DESC;

SPOOL OFF
set markup html off
exit;

3.       Prepare the file mailheader.log.

Simply copy the below to make mailheader.log to fetch the email.

From:xyz@gmail.com
To:abc@gmail.com
Cc:
Subject:Tablespace Report in PROD > 85%.
Content-Type: text/html

In last need to schedule the schell script ‘tablespace_report.sh’ for daily tablespace utilization.

Run ‘crontab –e’ to schedule it on oracle database server.
30 04 * * *  /oracle/home/oracle/dba_scripts/cron_scripts/shell/tablespace_report.shhttps://youtu.be/1zyDJIpMgkY
Posted by at 1 comment:
Labels:

Purge obsolete logs in oracle EBS r12

Delete logs in oracle apps R 12
Following command will help you in regular purging the log file of Oracle EBS R12.1 and R12.2

1. Report cache/Apache logs: Retention period 5days
 /usr/bin/find $LOG_HOME/ora/10.1.2/reports/cache/ -mtime +30 -exec rm  {} \;
/usr/bin/find $LOG_HOME/ora/10.1.3/Apache -mtime +15 -exec rm  {} \;


2. Apache logs: Retention period 7days
 /usr/bin/find $LOG_HOME/ora/10.1.3/Apache/ -mtime +30 -exec rm  {} \;

3. Concurrent manager log files : Retention period 30days
 /usr/bin/find $LOG_HOME/appl/conc/log/ -mtime +30 -exec rm  {} \;

4. Concurrent manager out files : Retention period 30days
 /usr/bin/find $LOG_HOME/appl/conc/out/ -mtime +30 -exec rm  {} \;

5. Appltmp logs: Retention period 30days
 /usr/bin/find $APPLTMP/ -mtime +30 -exec rm  {} \;

6. Opmn logs: Retention period 7days
 /usr/bin/find $LOG_HOME/ora/10.1.3/opmn/ -mtime +7 -exec rm  {} \;
  
find /oracle/PROD/inst/apps/PROD_appsprod/logs/ora/10.1.2/reports/cache  -mtime +30 -exec rm  {} \;


To automate the purging activity via crontab use the  below scripts :-

Script file:- purgingactivity.sh

 #!/bin/bash
. /oracle/PROD/apps/apps_st/appl/APPSPROD_appsprod.env

#Report cache logs: Retention period 30 days
find $LOG_HOME/ora/10.1.2/reports/cache/ -mtime +30 -exec rm  {} \;

#Apache logs: Retention period 30 days
find $LOG_HOME/ora/10.1.3/Apache/ -mtime +30 -exec rm  {} \;

#Concurrent manager log files : Retention period 7days
find $LOG_HOME/appl/conc/log/ -mtime +7 -exec rm  {} \;

#Concurrent manager out files : Retention period 7days
find $LOG_HOME/appl/conc/out/ -mtime +7 -exec rm  {} \;

#Appltmp logs: Retention period 30days
find $APPLTMP/ -mtime +15 -exec rm  {} \;

#Opmn logs: Retention period 7days
find $LOG_HOME/ora/10.1.3/opmn/*log* -mtime +7 -exec rm  {} \;


For R12.2:- 

# Remove Weblogic forms Server Logs

find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs -mtime +30 -exec rm  {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs -mtime +30 -exec rm  {} \;



find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs -mtime +30 -exec rm  {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs -mtime +30 -exec rm  {} \;



# Remove Weblogic oacore Server Logs
find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs -mtime +30 -exec rm {} \;


find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs -mtime +30 -exec rm {} \;


#Remove Weblogic oafm_server Server Logs

find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server1/logs -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server1/logs -mtime +30 -exec rm {} \;


find /oracle/DB/EBSFS/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server2/logs -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server2/logs -mtime +30 -exec rm {} \;


# Remove Weblogic OHS Logs


find /oracle/DB/EBSFS/fs1/FMW_Home/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web -mtime +30 -exec rm {} \;
find /oracle/DB/EBSFS/fs2/FMW_Home/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web -mtime +30 -exec rm {} \;



find /oracle/DB/EBSFS/fs1/FMW_Home/webtier/instances/EBS_web_OHS2/diagnostics/logs/OHS/EBS_web -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/webtier/instances/EBS_web_OHS2/diagnostics/logs/OHS/EBS_web -mtime +30 -exec rm {} \;



# Remove Weblogic wlst Server Logs


find /oracle/DB/EBSFS/fs1/FMW_Home/logs -mtime +30 -exec rm {} \;

find /oracle/DB/EBSFS/fs2/FMW_Home/logs -mtime +30 -exec rm {} \;



# Remove old outputs $APPLCSF/out 

find /oracle/DB/EBSFS/fs_ne/inst/DB_samarth/logs/appl/conc/out -mtime +30 -exec rm {} \;



# Remove old reqs $APPLCSF/log


find /oracle/DB/EBSFS/fs_ne/inst/DB_samarth/logs/appl/conc/log -mtime +30 -exec rm {} \;

Once purgingactivity.sh will schedule through crontab job,automatically it will purge the all log.


The program “PURGE SIGNON AUDIT DATA” should be scheduled to run within the next 30 days with the following parameter value:

Audit date: <30 days prior to the scheduled run date>

In addition, the “Increment date parameters each run” scheduling option should be selected for the scheduled run of the program.
You can also run the below concurrent request from Sysadmin responsibility:
Purge Concurrent Request and/or Manager Data Program


Posted by at 2 comments:
Labels: