How to check AD to OID Synch is happening fine

How to check AD to OID Synch is happening fine

Run this command for 2-3 Hours intervals:

ldapsearch -h mkktestserver1.unixdomain.local -p 3060 -D cn=orcladmin -w MalayFalsePass -s sub -b "" -T -createtimestamp "objectclass=orcluserv2" createtimestamp > createlist1.txt

ldapsearch -h mkktestserver1.unixdomain.local -p 3060 -D cn=orcladmin -w MalayFalsePass -s sub -b "" -T -createtimestamp "objectclass=orcluserv2" createtimestamp > createlist2.txt

ldapsearch -h mkktestserver1.unixdomain.local -p 3060 -D cn=orcladmin -w MalayFalsePass -s sub -b "" -T -createtimestamp "objectclass=orcluserv2" createtimestamp > createlist3.txt

ldapsearch -h mkktestserver1.unixdomain.local -p 3060 -D cn=orcladmin -w MalayFalsePass -s sub -b "" -T -createtimestamp "objectclass=orcluserv2" createtimestamp > createlist4.txt

Open the createlist?.txt file and make sure at the end of the file you see new entries are getting created.

ASM Related steps for 11gR2 Grid Infrastructure

ASM Related steps for 11gR2 Grid Infrastructure

I am putting this ASM related steps only, on request of a friend. In two projects where I handled HP EVA Storage also, I setup 2 node RAC for eBusiness Suite. If you require any other part of the implementation experience, would be happy to share.

This was done for Oracle Enterprise Linux

Prepare Storage and Disks

1. Run fdisk for RDBMS and GRID Binaries Multipathing Disk(Both Node)
2. Run partprobe for RDBMS and GRID Binaries Multipathing Disk(Both Node)
3. Run fdisk for ASM Related Multipathing Disk(Database Node1)
4. Run partprobe for ASM Related Multipathing Disk(Both Node)
5. Run mkfs.ext3 for RDBMS and GRID Binaries Multipathing Disk(Database Node1)

Multipathing Disks Used for ASM:

mpath2 - PRODDATA
mpath6 - PRODFRA
mpath5 - PRODOCR1
mpath4 - PRODOCR2
mpath3 - PRODOCR3

As an example fdisk and partprobe is shown for mapth2 disk, same needs to be done for others:

[root@mkkracdbserver1 ~]# fdisk /dev/mapper/mpath2
[root@mkkracdbserver1 ~]# partprobe /dev/mapper/mpath2

[root@mkkracdbserver1 ~]# ls -ltr /dev/mapper/mpath2*
brw-rw---- 1 root disk 253,  0 Aug  9 00:02 /dev/mapper/mpath2
brw-rw---- 1 root disk 253, 10 Aug  9 00:02 /dev/mapper/mpath2p1

[root@mkkracdbserver2 ~]# partprobe /dev/mapper/mpath2

[root@mkkracdbserver2 ~]# ls -ltr /dev/mapper/mpath2*
brw-rw---- 1 root disk 253, 0 Aug  6 18:07 /dev/mapper/mpath2
brw-rw---- 1 root disk 253, 9 Aug  9 00:06 /dev/mapper/mpath2p1



Install Oracle ASM Libraries RPM

# rpm -ivh oracleasm-support-2.1.4-1.el5.x86_64.rpm
# rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
# rpm -ivh oracleasm-2.6.18-238.el5-2.0.5-1.el5.x86_64.rpm

# /etc/init.d/oracleasm init
# /etc/init.d/oracleasm exit
# /etc/init.d/oracleasm status

To Load and initialize the ASMLib driver issue init
To Stop the ASMLib driver issue exit
To Display the status of the Oracle ASMLib driver issue status




Configure ASMLib Driver

This needs to be done on both the Database Node

# /etc/init.d/oracleasm configure

[root@mkkracdbserver1 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

Default user to own the driver interface []: oraprod
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@mkkracdbserver2 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

Default user to own the driver interface []: oraprod
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]




Create ASM Disks

1. Use Oracleasm Createdisk Command on Database Node1
2. Use Oracleasm Listdisks Command on Database Node1
2. Use Oracleasm Scandisks Command on Database Node2
2. Use Oracleasm Listdisks Command on Database Node2

Important Note: Do not deviate from the above order, or else you may end up with non-visible ASM disks while doing the installation. You will have to change the diskstring parameter to asm_diskstring='/dev/oracleasm/disks/*' to make the disks visible. To overcome the issue, reboot both the database node and disks will be visible.


[root@mkkracdbserver1 ~]# oracleasm createdisk ASMDATA01 /dev/mapper/mpath2p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMFRA01 /dev/mapper/mpath6p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR01 /dev/mapper/mpath5p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR02 /dev/mapper/mpath4p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR03 /dev/mapper/mpath3p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm listdisks
ASMDATA01
ASMFRA01
ASMOCR01
ASMOCR02
ASMOCR03

[root@mkkracdbserver2 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDATA01"
Instantiating disk "ASMOCR03"
Instantiating disk "ASMOCR02"
Instantiating disk "ASMOCR01"
Instantiating disk "ASMFRA01"

[root@mkkracdbserver2 ~]# oracleasm listdisks
ASMDATA01
ASMFRA01
ASMOCR01
ASMOCR02
ASMOCR03


Implement Metalink Note 1059847.1 for Multipathing Disks

This needs to be done on both the Database Nodes

Important Note: If this is not done, then root.sh will fail in Database Node2.

11GR2 GRID INFRASTRUCTURE INSTALLATION FAILS WHEN RUNNING ROOT.SH ON NODE 2 OF RAC USING ASMLIB [ID 1059847.1]

1. Modify the /etc/sysconfig/oracleasm with:

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

# /etc/init.d/oracleasm restart

[root@mkkracdbserver1 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@mkkracdbserver1 ~]#

[root@mkkracdbserver2 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@mkkracdbserver2 ~]#

Cheers !!!!
Malay Khawas
Oracle Apps/Fusion DBA

How to do test of ar package, Installed on OS | Exception String: Error in invoking target 'nnfgt.o' of makefile

How to do test of ar package, Installed on OS | Exception String: Error in invoking target 'nnfgt.o' of makefile

a) create the following program called test.c :

main()
{
printf("hello\n");
}

b) compile it to a .o file :

gcc -c test.c

This generates an object file called test.o

c) Now test if basic ar functionality works;

ar cr myarch.a test.o

--> does the myarch.a file get created ok ?
--> if so can you query it with ar -t myarch.a ?

d) now test ar rv myarch2.a test.o 

Spool:

$ gcc -c test.c
$ ls -ltr
total 25970
-rw-r--r-- 1 apppoc dba 174 Apr 8 16:09 local.profile
-rw-r--r-- 1 apppoc dba 157 Apr 8 16:09 local.login
-rw-r--r-- 1 apppoc dba 136 Apr 8 16:09 local.cshrc
drwxr-xr-x 2 apppoc dba 96 May 2 12:18 bea
-rw------- 1 apppoc dba 13004722 May 5 02:03 core
-rw-r--r-- 1 apppoc dba 449 May 5 10:49 oamenv2.sh
-rw-r--r-- 1 apppoc dba 283726 May 6 08:07 core_strings.txt
-rw-r--r-- 1 apppoc dba 486 May 7 11:32 oamenv.sh
-rw-r--r-- 1 apppoc dba 30 May 8 12:07 test.c
-rw-r--r-- 1 apppoc dba 732 May 8 12:07 test.o
$ ar cr myarch.a test.o
Illegal Instruction(coredump)
$ ar rv myarch2.a test.o
a - test.o
Illegal Instruction(coredump)
$ cat test.c
main()
{
printf("hello\n");
}
$


Lesson Learned:

While doing OID Installation, config.sh was failing with following to execute the make command.
Exception String: Error in invoking target 'nnfgt.o' of makefile in OID Installation

Issue with opmnctl Startup or Status - Swap Issue

Issue with opmnctl Startup or Status - Swap Issue

applive WEBTIERLIVE /export/home/applive $ opmnctl status
Error occurred during initialization of VM
Could not reserve enough space for object heap
applive WEBTIERLIVE /export/home/applive $

Increase the OS Level Swap Space. It work fine for you.

Connecting to EM Fails With Error "503 Service Unavailable" after OID Installation

Connecting to EM Fails With Error "503 Service Unavailable" after OID Installation

After Installation of Oracle Internet Directory, I was unable to connect to EM and getting an error 503 Service Unavailable.

When I cheched the targets.xml file, I noticed OID EM Farm entry were not present.

$DOMAIN_HOME/sysman/state/targets.xml

I put the following entries immediately after the very first <Targets> line:

<Target TYPE="oracle_ias_farm" NAME="Farm_IDMDomain" DISPLAY_NAME="Farm_IDMDomain">
<Property NAME="MachineName" VALUE="mkkoidserver1.mkkdomain.local"/>
<Property NAME="Port" VALUE="7001"/>
<Property NAME="Protocol" VALUE="t3"/>
<Property NAME="isLocal" VALUE="true"/>
<Property NAME="serviceURL" VALUE="service:jmx:t3://mkkoidserver1.mkkdomain.local:7001/jndi/weblogic.management.mbeanservers.domainruntime"/>
<Property NAME="WebLogicHome" VALUE="/opt/oracle/IDMLIVE_MW_HOME/WebLogic/wlserver_10.3"/>
<Property NAME="DomainHome" VALUE="/opt/oracle/IDMLIVE_MW_HOME/WebLogic/user_projects/domains/IDMDomain"/>
</Target>

Restart the OID Stack.

ldap_bind: UnKnown Error Encountered for Java External Authentication Plugin

ldap_bind: UnKnown Error Encountered for Java External Authentication Plugin

applive IDMLIVE /export/home/applive $ ldapcompare -h mkkoidserver1 -p 3060 -D "cn=orcladmin" -w ************* -b "cn=adtooidsyncuser generic,ou=information technology,ou=india,cn=users,dc=mkk,dc=ad,dc=local" -a userPassword -v "*************"
ldap_compare_s: UnKnown Error Encountered

applive IDMLIVE /export/home/applive $

applive IDMLIVE /export/home/applive $ ldapbind -h mkkoidserver1 -p 3060 -D "cn=adtooidsyncuser generic,ou=information technology,ou=india,cn=users,dc=mkk,dc=ad,dc=local" -w "*************"
ldap_bind: UnKnown Error Encountered


There is a bug with OID 11.1.1.6.

Due to issues to login to ODSM, I changes orcljvmoptions from 64M to 512M using LDAPMODIFY command.

<Jun 18, 2013 4:58:33 PM BST> <Error> <oracle.adfinternal.view.faces.config.rich.RegistrationConfigurator> <BEA-000000> <ADF_FACES-60096:Server Exception during PPR, #8

If you face this issue you would have to increase orcljvm value to 512 or something more than 64M.

●● Use ldapmodify to update heap size for dsaconfig:

ldapmodify -h mkkoidserver1 -p 3060 -D cn=orcladmin -w ************* << eof
dn: cn=dsaconfig,cn=configsets,cn=oracle internet directory
changetype: modify
replace: orcljvmoptions
orcljvmoptions: -Xmx512M
eof

●● Restart Complete OID Stack.

If you try to change this using /em, instead of -Xmx512M, it would be saved as -xmx512m. Even LDAPMODIFY also saved the entry in small letter only. Note x and m are in small letter.

applive IDMLIVE /export/home/applive $ ldapsearch -h mkkoidserver1 -p 3060 -D cn=orcladmin -w ************* -b "cn=dsaconfig,cn=configsets,cn=oracle internet directory" -s base "objectclass=*"
cn=dsaconfig,cn=configsets,cn=oracle internet directory
orclallattrstodn=NOT ASCII
orclecachemaxentries=100000
orclecachemaxsize=209715200
orclecacheenabled=1
orclautocatalog=1
orcljvmoptions=-xmx512m
orclrscacheattr=uid
orclrscacheattr=mail
orclrscacheattr=cn

For two of my instances it saved correctly with capital X and M. But for live instance it created a hell for me.

As of now, I directly updated the database. Make sure you take a proper backup. Hope issues from /em or ldapmodify would be resolved soon by Oracle.

select attrval from ds_attrstore where attrname = 'orcljvmoptions';

update ds_attrstore  set attrval='-Xmx64M' where attrname = 'orcljvmoptions';
commit;

I reverted back the value to 64M which comes as default during installation.


Cheers !!!!!
Malay Khawas
Oracle Apps/Fusion DBA

How to Monitor JDBC and JVM in eBusiness Suite

How to Monitor JDBC and JVM in eBusiness Suite

Use Oracle Support Note for complete script:
monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite (Doc ID 557194.1)

JDBC Connection Usage Per JVM Process:
select machine, process, count(*) from gv$session
where program like '%JDBC%'
group by machine, process
order by 1 asc;

Connection Usage Per Module
select count(*), module
from gv$session
where program like '%JDBC%'
group by module
order by 1 asc;

Idle connections for more than 3 hours:
select count(*),machine, program
from gv$session
where program like '%JDBC%'
and  last_call_et > 3600 *3
group by machine, program;

Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd:
select s.sql_hash_value, t.sql_text, s.last_call_et
from gv$session s , gv$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like  '%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%';

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://oacore (no island or jgroup).

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://oacore (no island or jgroup).

<<<<<<<<<<<<<<<Explanation>>>>>>>>>>>>>>>

Islands are no longer used within iAS version 10.1.3 in EBS R12, the new term is "cluster". A cluster is stated as "two or more OC4J server nodes hosting the same set of applications". A standard instance of Applications Release12 has some aspects of clustering enabled by default. There are many components to a cluster and all of them have not been configured in Oracle E-Business suite R12. So the error may change as following :

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://form (no cluster or jgroup).

<<<<<<<<<<<<<<<Reason>>>>>>>>>>>>>>>

??? Load: e.g. Too many clients connecting to the application, The number of requests are high enough that the process cannot respond to new requests prior to reaching the timeout period.

??? Performance: Heavily loaded JVM or Lack of enough memory for JVM

??? Code: Internal to Application Server, one of the components are not operating correctly or efficiently. External to ApplicationServer, E-Business code (seeded or custom code) causing memory leaks.

??? Lack of free ports for AJP communication

<<<<<<<<<<<<<<<Solution Approach#1: Resize oc4j oacore jvm heap size as well as number of oacore processes in context file>>>>>>>>>>>>>>>


The number of jvms (oc4j in R12) is configured by the autoconfig variables s_oacore_nprocs, s_disco_nprocs, s_forms_servlet_nprocs (11i), s_forms_nprocs (R12) and s_xmlsvcs_nprocs.

In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

Thumb Rule: 1 active JVM/OC4J instance per CPU core.  So if you have 8 CUPs of dual core, then you can configure upto 16 JVM. 1 JVM handles 100 Users, so based on the user load configure JVMs accordingly.


<<<<<Solution Approach#2: Proper Heap Configuration >>>>>

The heap is configured by s_oacore_jvm_start_option, s_forms_jvm_start_options in R12. For 12.1 and higher start with the following and increase as per the user load:
-Xmx1024M -Xms512M -XX:MaxPermSize=256M
      -XX:NewRatio=2 -XX:+PrintGCTimeStamps

Also, Add the following parameter to the DBC file:
JDBC\:oracle.jdbc.maxCachedBufferSize=262144

In JDK 1.6, the JVM detects that you have a server class machine (2 or more CPUs with 2GB or more memory), and will automatically enable Parallel Throughput Garbage Collector. The number of GC threads defaults to the number of CPUs on the machine. If you are running multiple JVMs on the same machine, or if your machine has more than 2 CPUs, to avoid the GC threads to be overly active you should reduce the number of GC threads by using:

-XX:+UseParallelGC -XX:ParallelGCThreads=2

Note: Although the setup of those parameters can be temporarily done manually changing (jserv.conf and jserv.properties in 11i; opmn.xml in R12), the correct way to update those parameters is using the context editor, update the variables s_oacore_nprocs and s_forms_servlet_nprocs, and then run autoconfig.

<<<<<Solution Approach#3: Long Running JVM >>>>>

Make sure to have the following setting in the jserv.properties file:
wrapper.bin.parameters=-DLONG_RUNNING_JVM=true

<<<<< Solution Approach#4: Java Cache Port Value >>>>>

The port value for java cache (s_java_object_cache_port) in context file should match profile option value JTF_DIST_CACHE value. 

 + The value for java cache port can be check in Context File -> parameter "s_java_object_cache_port" 
 + The value of the profile option JTF_DIST_CACHE can be found using the sql : 
      select fnd_profile.value('JTF_DIST_CACHE_PORT') from dual

<<<<< Solution Approach#5: Same Server Multiple EBS Instances >>>>>>

If the E-Business suite environment has multiple application servers then ensure that the following Autoconfig variables point to a "local disk". Hence this must be checked and changed on each application tier context file. 
     s_lock_pid_dir 
     s_pids_dir 
     s_web_pid_file

If the above variables are reset then run autoconfig for the changes to be effective.

<<<<< Solution Approach#6: EBS Instances with Load Balancer >>>>>>

Refer Oracle Support Note: Using Load-Balancers with Oracle E-Business Suite Release 12 (Doc ID 380489.1)

1. Make sure that the load balancer always implements Session Stickiness (also named Session Binding). This ensures that every time a request with an existing session is received it will be sent to the HTTP Server which created the session and therefore the routingID will always be able to be correctly decoded.

<<<<<<<<<<<<<<<Solution Approach#7: AJP Ports>>>>>>>>>>>>>>>

Ensure the port range used by AJP protocol by various middle tier components are not occupied by any other service. Run the following command to find the AJP port range.

$grep -i ajp $CONTEXT_FILE 

<ajp_protocol oa_var="s_ajp_protocol">ajp</ajp_protocol> 
<oacore_ajp_portrange oa_var="s_oacore_ajp_portrange" oa_type="PORT" base="21500" step="5" range="5" 
label="OC4J AJP Port Range for Oacore">21500-21504</oacore_ajp_portrange> 
<forms_ajp_portrange oa_var="s_forms_ajp_portrange" oa_type="PORT" base="22000" step="5" range="5" 
label="OC4J AJP Port Range for Forms">22000-22004</forms_ajp_portrange> 
<oafm_ajp_portrange oa_var="s_oafm_ajp_portrange" oa_type="PORT" base="25000" step="5" range="5" 
label="OC4J AJP Port Range for Oafm">25000-25004</oafm_ajp_portrange>

<<<<< Solution Approach#8: Firewall Between EBS Application Servers >>>>>

If any firewall exists between application server (Like firewall between forms/web tier & Concurrent tier etc) then refer the following workaround.

To improve performance, the mod_oc4j component in Oracle HTTP Server(OHS) process maintains open TCP connections to the AJP port within each OC4J components. In situations where a firewall exists between OHS and OC4J, packages sent via AJP are rejected if the connections can be idle for periods in excess of the inactivity timeout of stateful firewalls. However, the AJP socket is not closed as long as the socket remains open, the worker thread is tied to it and is never returned to the thread pool. OC4J will continue to create more threads, and will eventually exhaust system resources. 

Set the following parameters in the mod_oc4j.conf (Present in the directory $IAS_ORACLE_HOME/Apache/Apache/conf) configuration file. The value of Oc4jConnTimeout sets the length of inactivity in seconds, before the session is considered inactive. 

Oc4jUserKeepalive on 
Oc4jConnTimeout 12000 (or a similar value)

Also read, how to monitor JDBC Connections in my blog.
http://mkkoracleapps.blogspot.co.uk/2013/07/how-to-monitor-jdbc-and-jvm-in.html

Cheers!!!
Malay Khawas
Oracle Apps/Fusion DBA

Oracle Database 12c: Interactive Quick Reference

Oracle Database 12c: Interactive Quick Reference is now available from the Oracle Learning Library! 

With this interactive poster, you can find descriptions of database architectural components, DBA view information, performance view information, background process information, as well as references to relevant documentation.

Enjoy!!! reading the following link:
http://pub.vitrue.com/Rmh2

New features of 12c Database: 
http://docs.oracle.com/cd/E24628_01/doc.121/e25353/whats_new.htm

Pin Sequence: Automatic Asset Numbering

There are two methods of doing Automatic Asser Numbering:

Method#1: Make the Sequence No Cache

1. The first and most obvious option would be not to cache the sequence values at all.  But the result of this could be a moderate to severe degradation of the application's performance.
SQL> alter sequence fa_additions_s nocache;

Method#2: Pin the Sequence:

2. A better solution is to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.  Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:
SQL> exec dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q')
 When you shut down a database normally, either "SHUTDOWN NORMAL" or "SHUTDOWN IMMEDIATE", the database takes care of making sure all sequences are "in sequence".  All cached sequence values are verified, and the last_number column in dba_sequences is updated accordingly.  Thus, under normal circumstances and assuming that your sequence has been pinned, you shouldn't lose cached sequence values during a normal shutdown.
 If the database experiences instance failure or a "SHUTDOWN ABORT" statement is issued, you lose any unused cached sequence values.  Oracle may also skip sequence values after  an export and Import and rollbacks.

To achive this either you have to modify the $ORACLE_HOME/bin/dbstart script and incorporate the pinning or you can create after database startup trigger.

Create a Startup Trigger:
create table AGCST.AG_FA_DBSTRT_PIN (
execution_date date
);
CREATE OR REPLACE TRIGGER DB_STRT_FA_PINING AFTER STARTUP ON DATABASE BEGIN
dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q');
INSERT INTO AGCST.AG_FA_DBSTRT_PIN (execution_date) select sysdate from dual;
END;
/

This is the trigger I have used for Pinning the object while doing Automatic Gapless Asset Numbering Implementation. Table is created to know that the trigger has fired after every startup

Log and Output file Reference in Conc Request Tables

If Request out and logfile needs to be viewed after cloning and site migration update the following:

UPDATE FND_CONCURRENT_REQUESTS
SET LOGFILE_NAME = REPLACE(LOGFILE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET OUTFILE_NAME = REPLACE(OUTFILE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET LOGFILE_NODE_NAME = REPLACE(LOGFILE_NODE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


UPDATE FND_CONCURRENT_REQUESTS
SET OUTFILE_NODE_NAME = REPLACE(OUTFILE_NODE_NAME,'<<<<old_string>>>>','<<<<new_string>>>>')
WHERE request_id=11797166;


Also update apps.fnd_conc_req_outputs table:

update apps.fnd_conc_req_outputs set FILE_NAME='<<<correct_out_path>>>/LMAPCHKRNA_11797166_1.PDF' where CONCURRENT_REQUEST_ID='11797166';
update apps.fnd_conc_req_outputs set FILE_NODE_NAME='<<<<correct_conc_processing_host_name>>>>' where CONCURRENT_REQUEST_ID='11797166';
You should be able to view

Profile option FND_OBIEE_URL is null

Issue:

Profile option FND_OBIEE_URL is null. Please contact your System Administrator
java.lang.RuntimeException: Profile Option FND_OBIEE_URL is nullat _oa__html._OracleOasis._jspService(_OracleOasis.java:1487)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java(Compiled Code))
at oracle.jsp.JspServlet.doDispatch(JspServlet.java(Compiled Code))
at oracle.jsp.JspServlet.internalService(JspServlet.java(Compiled Code))
at oracle.jsp.JspServlet.service(JspServlet.java)

Solution:

Set the e-Business Suite profile option "FND: Oracle Business Intelligence Suite Enterprise Edition base URL Value" (short name = FND_OBIEE_URL) to the location of the Siebel Analytics Web file 'saw.dll' , which is required for the integration of e-Business Suite with OBIEE.

URL Should be someting like in this in the Profile Option Value:http://<hostname>:9704/analytics/saw.dll?Dashboard

Restriction on Workflow Mailer HTML-Formatted Notifications

You can optionally set the internal mailer parameter named HTML_DELIMITER to specify which characters the notification mailer uses to delimit response values in response templates for HTML-formatted e-mail notifications. Valid values for the HTML_DELIMITER parameter are:

DEFAULT - The notification mailer uses the default delimiters, currently set as the single quote (') for both the opening and the closing delimiter. The notification mailer also uses the default delimiters if the HTML_DELIMITER parameter value is left null.

APOS - The notification mailer uses the single quote, or apostrophe (') , as both the opening and the closing delimiter. This setting is currently the same as the default.

QUOTE - The notification mailer uses the double quote (") as both the opening and the closing delimiter.

BRACKET - The notification mailer uses the left bracket ([) as the opening delimiter and the right bracket (]) as the closing delimiter.

Note: If the HTML_DELIMITER parameter is set to an invalid value, the notification mailer throws an exception at startup. Any notifications created during this time are rendered with the default delimiters instead.

By default, the HTML_DELIMITER parameter is set to the value DEFAULT. Use the afsvcpup.sql script to change the parameter value to specify the delimiters you want to use.

If a particular notification message has the special #WFM_HTML_DELIMITER message attribute defined, however, the notification mailer will use the #WFM_HTML_DELIMITER attribute value to determine which delimiters to use for that notification, instead of using the HTML_DELIMITER parameter value.

Note: The HTML_DELIMITER parameter only controls the response templates for HTML-formatted notifications. This parameter does not apply to plain text notifications.

Sending Mails using Sendmail with Subject and Attachment and Mail Body in Unix

Suppose you have a list of 10-15(or more) files to zip together and send to a mail. Take the filesnames to filelist.txt and all the mail addresses to listofmailids.txt file.

$ cat xxecms_sendmail_decrypt.sh
#!/bin/sh
while read FILE; do cp $FILE ZIP_HOLDER/ ; done < filelist.txt
ls -ltr ZIP_HOLDER/*; sleep 2
zip ZIP_HOLDER.zip ZIP_HOLDER/*
ls -ltr ZIP_HOLDER.zip; sleep 2
FILE="ZIP_HOLDER.zip"
echo "From: malay.khawas@mailaddress.local" > "MESSAGE_FILE"
echo "To: `cat listofmailids.txt`" >> "MESSAGE_FILE"
echo "Subject: Mail Attachment - From ECMS System" >> "MESSAGE_FILE"
echo "Content-type: multipart/mixed >/dev/null"
echo "Please find all the files in a Zipped Folder" >> "MESSAGE_FILE"
echo "Please contact sysadmin team if you need any help" >> "MESSAGE_FILE"
uuencode ${FILE} ${FILE} >> MESSAGE_FILE
cat MESSAGE_FILE | /usr/sbin/sendmail -t
rm -f ZIP_HOLDER/*
rm -f ZIP_HOLDER.zip
$


Sendmail with Subject and Attachment

FILE="SCM.ldt"
echo "From: malay.khawas@mailaddress.local" > "MESSAGE_FILE"
echo "To: malay.khawas@mailaddress.local" >> "MESSAGE_FILE"
echo "Subject: Report for Testing Bangladesh" >> "MESSAGE_FILE"
echo "Content-type: multipart/mixed >/dev/null"
echo "Hello How Are you" >> "MESSAGE_FILE"
echo "I have sent this as test mail" >> "MESSAGE_FILE"
uuencode ${FILE} ${FILE} >> MESSAGE_FILE
cat MESSAGE_FILE | /usr/sbin/sendmail -t

How to setup Passwordless SSH

Suppose I want to login from oradev1 to orapsup, without giving password, so I need to generate the key in oradev1 node.

In oradev1 Node:
ssh-keygen -t rsa
cat $HOME/.ssh/id_rsa.pub
Path of ssh-keygen: /usr/local/bin/ssh-keygen
/usr/local/bin/ssh-keygen -t rsa

In orapsup Node:
vi $HOME/.ssh/authorized_keys
And, put the contents of id_rsa.pub taken from oradev1 in the file
If .ssh directory doesnot exist, then create it by running the following

command:
ssh-keygen -t rsa

[oradev1@mkkdevserver1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oradev1/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oradev1/.ssh/id_rsa.
Your public key has been saved in /home/oradev1/.ssh/id_rsa.pub.
The key fingerprint is:
74:b3:cd:c9:8d:1c:40:ca:3c:ce:82:ab:04:7f:25:7f oradev1@mkkdevserver1.mkkdomain.local
[oradev1@mkkdevserver1 ~]$
[oradev1@mkkdevserver1 ~]$ cd .ssh/
[oradev1@mkkdevserver1 .ssh]$ ls
id_rsa  id_rsa.pub  known_hosts
[oradev1@mkkdevserver1 .ssh]$ cat id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAtBBAsSSfzZC7Zu3fFlO8xCUaO1jzuXnPe+Z38nNboZLspjFkNaEkdYd9Ej6ZS+uBfTjrCVgCTBEdb0wLzAQlvLVLVro/M9gwjhoNGQyBKJyDQYmkUy7RSFuUdFupioT+YIOGfdQBbpn8YFbfeTWd23Khcaxkpdt/n4hlBY/PdAYUPxNvypnp49jtbideF6yiSqXF1gBDhWefh9Zd9KlIInKY6k0MNqTfn6WY57/PuSiWq+i4WKs/whZWOqnTKlulHVhBSSEjFaYKyPtPvgZbl13cbIQsvcB3TUMnYJT1JylmwMCw6ZGJIATLJMTA4WpNhHDO07e+6a7RtroyY63z5w== oradev1@mkkdevserver1.mkkdomain.local
[oradev1@mkkdevserver1 .ssh]$

Now whatever contents you have on file id_rsa.pub, you have to put that in authorized_keys file of orapsup.

[orapsup@mkkdevserver2 .ssh]$ ls -ltr /home/orapsup/.ssh/authorized_keys
-rw-r--r-- 1 orapsup dba 1374 Apr  5 13:52 /home/orapsup/.ssh/authorized_keys
[orapsup@mkkdevserver2 .ssh]$
View the /home/orapsup/.ssh/authorized_keys file and paste at the end of the file the above public key content.
Now you are ready to login from oradev1 to orapsup,  without giving any password.

Cheers !!!!!!!!!!!

RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/30/2012 10:52:56
RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

Bug 11715084 - Active duplicate should work when connected to Standby as source DB [ID 11715084.8]

RMAN active duplicate does not currently allow a standby in MRP to be cloned. Backup-based duplicate was altered in 11.1 so that a standby could be used as the source DB, but active duplicate does not work.

This is going to be fixed in RDBMS 12.1(future release) or 11.2.0.4(future patchset).(As on 30-Dec-2012)

The workaround is to Open the DataGuard in Read/Write Mode and Start the Active Duplicate. As it is 11.2.0.3, I opened the DataGuard in Read Only and Stopped the MRP. In 11gR2, you can have the Standby in Read Only, while the MRP Continues.

FORMS Customization - changes required in adovars.env and formservlet.ini

Customization Entry in $APPL_TOP/admin/adovars.env

# Begin customizations
export APPLLCSP=ON
export XXCSTAP_TOP="/pa01/oracle/mkkdevappl/xxcstap/11.5.0"
export XXCSTAR_TOP="/pa01/oracle/mkkdevappl/xxcstar/11.5.0"
export XXCSTGL_TOP="/pa01/oracle/mkkdevappl/xxcstgl/11.5.0"
export XXCSTPO_TOP="/pa01/oracle/mkkdevappl/xxcstpo/11.5.0"
# End customizations

Customization Entry in $IAS_ORACLE_HOME/Apache/Jserv/etc/formservlet.ini

# Begin customizations
XXCSTAP_TOP=/pa01/oracle/mkkdevappl/xxcstap/11.5.0
XXCSTAR_TOP=/pa01/oracle/mkkdevappl/xxcstar/11.5.0
XXCSTGL_TOP=/pa01/oracle/mkkdevappl/xxcstgl/11.5.0
XXCSTPO_TOP=/pa01/oracle/mkkdevappl/xxcstpo/11.5.0
# End customizations

In R12 Custom forms will not open after upgrade, because file formservlet.ini used in 11i has been replaced by default.env in R12. Put entry for the custom top in $ORA_CONFIG_HOME/10.1.2/forms/server/default.env file.
Note: If you have the custom top entries in $ORA_TOP/iAS/Apache/Jserv/etc/formservlet.ini in correct format, then autoconfig will preserve the customization.

SQL_Query - Find the Schedule Concurrent Requests and Request Sets

SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%'
ORDER BY CONC_PROG_NAME;

Workflow Related Scheduled Concurrent Program for DBAs

Purge Obsolete Workflow Runtime Data

Oracle Workflow accesses several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes. The size of these tables and indexes can adversely affect performance. You should purge these tables on a regular basis using FNDWFPR.
This program calls database package WF_Purge.Total.  This program deletes old Workflow runtime data for completed Workflows. It is recommended that this program or package is run regularly as required.

From Workflow Administrator's Guide PDF Document:
The Oracle Applications Manager console helps you easily maintain the Oracle Workflow and Oracle XML Gateway database tables. Oracle Workflow and Oracle XML Gateway access several tables that can grow quite large with obsolete workflow information that is stored for all completed workflow processes, as well as obsolete information for XML transactions. The size of these tables and indexes can adversely affect performance. These tables should be purged on a regular basis, using the Purge Obsolete Workflow Runtime Data concurrent program.

Parameter Explanation:
Item Type: The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
Item Key: The item key to purge. Leaving is field blank defaults to purging the runtime data for all item keys.
Age: Minimum age of data to purge, in days.
Persistence Type: The persistence type to be purged, either for Temporary or for Permanent. The default is temp.
Core Workflow Only: Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
Commit Frequency: Enter the number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
Signed Notifications: Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.

Workflow Control Queue Cleanup

Parameters: NONE
Schedule:
Frequency:    Run every 12 Hours
Responsibility:  System Administrator
Please tick the checkbox “From the start of the prior run”

Workflow Background Process

This program needs to be submitted in various permutation and combination.

Type-1: which Runs Every 3 Mins for Parameters:
Process Deferred: Yes
Process Timeout:  No
Process Stuck:  No

Type-2: which Runs Every 30 Mins for Parameters:
Process Deferred:  No
Process Timeout:  Yes
Process Stuck:  No

Type-3: which Runs Every 1 Hours for Parameters:
Process Deferred:  No
"Process Timeout:  No
Process Stuck:  Yes"

Type-4: which Runs Every 2 Hours for Parameters:
Process Deferred:  Yes
"Process Timeout:  Yes
Process Stuck:  Yes"

How to Submit a Workflow Background Process Engine [ID 182936.1], Follow the Note mentioned at the End of this Metalink Note

Workflow Mailer Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Work Items Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Workflow Agent Activity Statistics Concurrent Program

Parameters: NONE
Schedule:
Frequency:    Run every 1 Day
Responsibility:  System Administrator
Please tick the checkbox “Increment Date Parameters Each Run”
Please tick the checkbox “From the Completion of the Prior Run”

Gather Schema Statistics Schedule:

Make sure Gather Shema Stats is running on periodical basis, for XXXXX we have scheduled to run every 15 Days.
Following points needs to be noted for the Gather Schema Statistics:
●● Do not gather statistics excessively on entire schemas or the entire database such as nightly or weekly.
●● Do not gather statistics on permanent objects during peak intervals.
●● Gathering statistics invalidates cursors .Unless you use the ‘No Invalidate’ option.
●● Gathering statistics requires dictionary and object level locks.
●● The option 'GATHER_AUTO' can be used, to gather statistics only on objects that have changes above the specified 'Modification Threshold' (percentage of DML compared to the number of rows of the table).
●● Plans are not likely to change if the data distribution has not changed.
●● Use only FND_STATS or the Gather Schema and Gather Table Statistics Concurrent Programs.
●● Do NOT USE the analyze or dbms_stats command directly. It is not supported, and results in sub-optimal plans.

Following CONCSUB Command can be used to schedule the Gather Schema Statistics concurrent Program:
"CONCSUB apps/${xx_appspassword} SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDGSCST START='""01-MAR-2012 02:00:00""'
REPEAT_DAYS=10 'ALL','10','18','NOBACKUP','','LASTRUN','GATHER','','Y'"

To Submit the Program Manually from SQLPlus following commands can be used:
For One Schema:
SQL> exec fnd_stats.gather_schema_statistics('MRP');
For All Schema:
SQL> exec fnd_stats.gather_schema_statistics('ALL');
For One Table:
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES');

To Submit the Gather Schema Statistics from EBS Front End:
●● Make sure you are logged in as SYSADMIN User
●● Use the System Administrator Responsibility to Submit the Concurrent Program.

DROP function was hanging for ever, even if you try to recompile the objects it throws ORA-04021

ORA-04021 timeout occurred while waiting to lock object... Same is applicable if the package status is getting invalidated.

drop function AGCST.AG_ASG_CHG_MAIL_PROG;

The background behind it was, this function is used in workflow and the initiated workflow was still accesing the objects.

Get the SID Information which are accessing this object and kill the same:
select * from v$access where owner='AGCST';

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' from gv$session where INST_ID=2 and SID IN(select SID from gv$access
where object like '%AR%CASH%REC%' and type='TABLE'
and INST_ID=1);

Once you complete killing all the SIDs, it will allow you to drop the object.