Latest Technology Blog

1. Script to find memory usage by INACTIVE Session SELECT sess.USERNAME, value/(1024*1024) "Current session memory MB", sess.sid,sess.status,p.spid  FROM v$session sess, v$sesstat stat, v$statname name,v$process p  WHERE sess.sid = stat.sid   AND sess.PADDR=p.ADDR  AND stat.statistic# = name.statistic#  AND name.name like '%memory%'  AND sess.USERNAME = 'APPS'  AND sess.STATUS='INACTIVE' order by 2,4 asc / ================================================ 2. script to check INACTIVE sessions with HIGH DISK IO select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs, s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL" from gv$session s, gv$sqlarea t,v$process p where s.sql_address =t.address and s.sql_hash_value =t.hash_value and p.addr=s.paddr and t.disk_reads > 5000 and s.status='INACTIVE' --and s.process='1234' order by S.PRO...

Issue: checking URL = http://<host>.<domain>:8000/OA_HTML/AppsLogin RW-50016: Error: - {0} was not created: Processing DriverFile = /stage/StageR12/startCD/Disk1/rapidwiz/template/adriapps.drv Executing service control script: /oracle/R12/inst/apps/TWO_oraclesvr05/admin/scripts/adoacorectl.sh start ERROR : Timed out( 100000 ): Interrupted Exception You are running adoacorectl.sh version 120.11 Starting OPMN managed OACORE OC4J instance ... Solution of this error applies to any platform. Cause: The issue is caused by a wrong setup in /etc/hosts file :  - localhost and server host are setup-ed on the same line.  Solution: To implement the solution, please execute the following steps: 1. Backup your /etc/hosts file 2. Change the hosts file as follow :        127.0.0.1 localhost        <IP> <host.domain> <host> 3. Restart all Applications services

Error: HTTP checking URL = http://xxx.yyy:zzz:8000 RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button. Help Page checking URL = http://xxx.yyy:zzz/OA_HTML/help RW-50015: Error: - Help Page is not responding. The service might not have started on the port yet. Please check the service and use the retry button. Virtual Directory RW-50015: Error: - Http Server Virtual Directories is not responding. The service might not have started on the port yet. Please check the service and use the retry button. JSP checking URL = http://xxx.yyy:zzz/OA_HTML/jtfTestCookie.jsp RW-50015: Error: - JSP is not responding. The service might not have started on the port yet. Please check the service and use the retry button. Login Page RW-50015: Error: - Login Page is not responding. The service might not have started on the port yet. Please check the service and use the retry button. RW-10001: Rapi...

Issue: TNS-12637: Packet receive failed     ns secondary err code: 12532     nt main err code: 0     nt secondary err code: 0     nt OS err code: 0 opiodr aborting process unknown ospid (16321) as a result of ORA-609 opiodr aborting process unknown ospid (16319) as a result of ORA-609 opiodr aborting process unknown ospid (16323) as a result of ORA-609 Cause: The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process before the server process was completely spawned. Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds. This is also triggered, when a DB session is killed/aborted manually from the OS prompt. Solution: Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure. If the problem  is due to connection timeouts,an increase in the following ...

Agenda: Create apps read only user for Oracle Database. Steps to create the Apps Read Only User: Step 1: SQL> create user appsro identified by appsro; User created. SQL> alter user appsro default tablespace APPS_TS_TX_DATA temporary tablespace temp; User altered. SQL> grant connect, resource to appsro; Grant succeeded. SQL> grant create synonym to appsro; Grant succeeded. Step 2: Connect as apps user  and create a spool file . as below SQL> spool create_synonyms.sql SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM'); SQL> spool off SQL> spool grant_select.sql SQL> select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to username;' from all_objects ...

Agenda: How to enable archive mode in Oracle RAC database. Steps to be followed: 1. SQL> select name from v$database;     NAME     ---------     PROD 2. SQL> alter system set cluster_database=False scope=spfile;    System altered.    SQL>exit; 3. srvctl status database -d prod 4. srvctl stop database -d prod 5. sqlplus "/as sysdba"      SQL> startup mount      ORACLE instance started. Total System Global Area 9556852736 bytes Fixed Size 2166288 bytes Variable Size 6375346672 bytes Database Buffers 3154116608 bytes Redo Buffers 25223168 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +ARCH Oldest online log sequence 380 Next log sequence to archive 382 Current log sequence 382 SQL> alter system set cluster_database=true scope=spfile; System altered. S...

Issue: Concurrent  Program was terminated by signal 25.  When attempting to run a concurrent program which is accessing file "reports.log" in directory $APPLCSF/$APPLLOG then following error occurs:  emsg:was terminated by signal 25 Cause: File size of "reports.log" has reached its maximum limit at operating system which is 2GB. If $APPLCSF/$APPLLOG/reports.log will be huge -- 2GB in size, then reports can not append anything else to it and hence concurrent program accessing reports.log for its execution will error out. Solution: 1. Stop the concurrent manager.  2. Check the size of reports.log file and if file size is more than 2 GB then rename/truncate existing "reports.log" in directory $APPLCSF/$APPLLOG and create a empty "reports.log".One can do a find command as well. $ find $INST_TOP -name reports.log cd $APPLCSF/$APPLLOG bash-3.2$ls -lrth reports.log -rw-r–r– 1 applmgr dbamgr 2.1GB Mar 05 23:00 reports.log bash-3.2$ cp -r repo...

Issue: After completing cloning procedure, you generally may across the following issue. "Node id does not exist for the current application server id". Solution: First stop the application. Run adgendbc.sh script to configure apps tier dbc file with the correct id from the database instance. Start the application and test your issue. Location of script: 11i: $COMMON_TOP/admin/install/$CONTEXT_NAME R12: $INST_TOP/admin/install [appldev@erptest install]$ sh adgendbc.sh (Note: It will prompt you to give apps username and apps password.)

Issue: While creating a database 12c using dbca the database creation GUI gives error message in a pop up window, ORA-00845: MEMORY_TARGET not supported on this system  from where you can ignore the error message and perform the solution mentioned below and retry the failed steps during installation. The similar scenario also occur whenever you try to start your database then startup shows error message like below. SQL> STARTUP ORA-00845: MEMORY_TARGET not supported on this system  Cause: 1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET. 2)If the shared memory is not mapped to /dev/shm directory. Solution: Make sure /dev/shm is properly mounted. You can see it by, #df -h or #df -k command. [root@testing shm]# df -kh Filesystem            Size  Used Avail Use% Mounted on shmfs                 5.0G  815M ...

Agenda: To start concurrent managers properly. Issue: Concurrent Managers Status shows  “System Hold, Fix Manager” in Concurrent–>Manager–>Administer Screen form. Solution: Ensure Concurrent :GSM Enabled profile is set to ‘Y’ Stop all the application services. • Run $FND_TOP/patch/115/sql/afdcm037.sql • Go to $FND_TOP/bin adrelink.sh  force=y “fnd FNDLIBR” adrelink.sh  force=y “fnd FNDSM” adrelink.sh  force=y “fnd FNDFS” adrelink.sh  force=y “fnd FNDCRM” • Run  cmclean.sql • Start Application Service ( adstrtal.sh )and test your issue.

Agenda: To start concurrent manager. Issue: Concurrent Managers Status shows  ”Target node/queue unavailable” in Concurrent–>Manager–>Administer Screen form. Solution: Ensure Database is Running and Middle Tier Services are down. Connect SQLPLUS as APPS user and run the following : EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT; Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtier to repopulate the required systemtables Run the  CMCLEAN.SQL  script from the referenced note below (don’t forget to commit). Note.134007.1 – ' CMCLEAN.SQL  – Non Destructive Script to Clean Concurrent  Manager Tables' Start the middle tier services including your concurrent manager. Retest the issue.

Agenda: Create custom top/application in E-Business Suite R12. Steps to be followed: SCHEMA NAME : XXNEW TOP NAME : XXNEW_TOP Application : XXNEW Custom Application Data Group : Standard Request Group : XXNEW Request Group Menu : XXNEW_CUSTOM_MENU Responsibility : XXNEW Custom Step-1 Create a Tablespace XXNEW in the Database for custom schema. create tablespace XXNEW datafile '/u01/oradata/XXNEW01.dbf' size 50M; Step-2 Create Schema XXNEW in the database. create user XXNEW identified by XXNEW default tablespace XXNEW temporary tablespace temp quota unlimited on XXNEW; Step-3 Grants Connect and Resource to schema XXNEW. grant connect, resource to XXNEW; Step-4 Make the directory structure for your custom application files. cd $APPL_TOP mkdir XXNEW mkdir -p XXNEW/12.0.0 mkdir -p XXNEW/12.0.0/admin mkdir -p XXNEW/12.0.0/admin/sql mkdir -p XXNEW/12.0.0/admin/odf mkdir -p XXNEW/12.0.0/sql mkdir -p XXNEW/12.0.0/bin mkdir -p XXNEW/12.0.0/reports mkdir...