adop phase=fs_clone failed for new patch fs clone using adcfgclone

When running adop phase=fs_clone after cloning from run fs using adcfgclone.

ERROR: The following required ports are in use:
----------------------------- 70X0 : WLS Admin Server Port

Fix:
Using patch environment, run adstpall.sh forcepatchfs

Nodes x are listed in the ADOP_VALID_NODES table but not in the FND_NODES table

When running adop phase=prepare, this error is shown:
[UNEXPECTED]Nodes “xxxxxx” are listed in the ADOP_VALID_NODES table but not in the FND_NODES table.

This is how to fix it:

1. Logon to sqlplus as applsys: sqlplus applsys
2. On sqlplus:
1. truncate table fnd_oam_context_files;
2. truncate table fnd_nodes;
3. truncate table adop_valid_nodes;
3. Run autoConfig on the DB tier
4. Run autoconfig on the run file system
5. Logon to sqlplus as system: alter trigger ebs_logon disable;
6. Run Autoconfig on the patch file system
7. Logon to sqlplus as system: alter trigger ebs_logon disable;

OBIEE 12c error export to excel ReadOverTheLimitException

When attempting to export an Analysis to excel the following error occurs (using OBIEE 12c):

There was an error processing your download. Please check with your administrator.

The OBIEE Server log $OBIEE_Domain/servers/obips1/logs/(saw#.log) generated the following exception:

com.siebel.analytics.utils.InputStreamWithLimit$ReadOverTheLimitException.

Solution:

1. Increase the InputStreamLimitInKB limit:
   1. Browse to $OBIEE_Domain/config/fmwconfig/biconfig/OBIJH
   2. Open the config.xml in a text editor
   3. Find the following lines:
      • 
           8192
           true
        

Change the InputStreamLimitInKB value from 8192 to 2048000.

• Example:
• 
     2048000
     true
  

Save and close the file.

Increase the JavaHost maximum heap size to assist in exports to excel:

1. Browse to $Middleware_Home/bi/modules/oracle.bi.cam.obijh/env
2. Open obijh.properties in a text editor
3. Find the following line:
   • OBIJH_ARGS=-server -Xmx1024M -Xrs -Djava.security.manager=default...
4. Increase the maximum heap value to 2048M:
   • Example:
   • OBIJH_ARGS=-server -Xmx2048M -Xrs -Djava.security.manager=default...
5. Save and close the file

Restart the OBIEE processes for change to take effect

Output Post Processor java.lang.OutOfMemoryError: GC overhead limit exceeded

If you receive Error like the following while Running XML Publisher/ BI Publisher Reports from Oracle E-Business Suite in OPP Log:
   

Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Error invoking 'ms_format_number':'java.lang.OutOfMemoryError: GC overhead limit exceeded'

OR 

Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1067)

Then You just need to follow the Plan as Per Oracle:



1. Determine what the heap size per OPP process is currently:

select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

2. The default should be:

 

J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

3. Increase the Heap Space per Process to 1024: ( 1024 = If you want to increase the Heap Memory to 1GB)  

 

update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');

 

4. Do not forget to bounce the application services.

 

You are done now.

Edit Linux fstab in recovery mode

In Linux recovery mode, the root filesystem is readonly, so you can not edit /etc/fstab.
You have to issue this command: mount -o remount,rw /

Locked User in EBS R12

Today one of users complaining about failed login. Using user administration I found that the user's status is 'LOCKED'. It was the first time I met this problem. The question was how to unlock the user?

After searching Oracle Support with no result, I opened the FND_USERS table and found that the value for ENCRYPTED_FOUNDATION_PASSWORD and ENCRYPTED_USER_PASSWORD for locked users are INVALID.

So I changed the password from the applications and then LOCKED status disappeared.

ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960

When creating materialized view, do not query directly from database link.
Below query will be failed:

CREATE MATERIALIZED VIEW "ABC"
TABLESPACE "XYZ"
PCTFREE 10 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
NOLOGGINGPARALLEL (DEGREE 4) CACHE USING NO INDEX
REFRESH FORCE START WITH to_date('04-11-2011 11:58:35','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1/24 AS select * from abc@dblink

You can create a view first and select from the view, or:

CREATE MATERIALIZED VIEW "ABC"
TABLESPACE "XYZ"
PCTFREE 10 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
NOLOGGINGPARALLEL (DEGREE 4) CACHE USING NO INDEX
REFRESH FORCE START WITH to_date('04-11-2011 11:58:35','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1/24 AS select * from (select * from abc@dblink)

Rupam Verma' Blog: Negative Values For Tablespace

If you see some negative value on Enterprise Manager Grid Control 11g or on Database Console 11g while listing datafiles for a tablespace then this is your answer:

Rupam Verma' Blog: Negative Values For Tablespace: "9548105"

Oracle VM: ha_check_cpu_compatibility

When you create Oracle VM server pool, it's required that all the servers in the pool have the same type and family of CPUs. Beginning in Oracle VM Server 2.2.1 (or Oracle VM Server 2.2.0 with upgraded agent ovs-agent-2.3-38), Oracle VM agent will enforce the pre-check by default when you create a server pool or add a new server to an existing server pool. The pre-check includes the server version, agent version, CPU compatibility, storage repositories, NFS, OCFS2 configuration, and so on. It is to ensure successful setup of the server pool and OCFS2 cluster integrity.
This also applies to repository initialization.
Still there might be use cases where you want to set up the testing environment where different type of servers may be formed into the server pool. For example, the testing environment may be to verify network or storage configuration of multi-node server pool, but it's not required to perform live migration or HA.

Warning: Oracle VM 2.2 requires all processors in a given pool to be of the same type. The bypass procedure provided below is available for test and experimental environments,and is not recommended or supported by Oracle.

Solution

Here we provide the steps to manually bypass certain pre-check function. The steps are provided to facilitate testing only, should not be used in any production environment:

1. Log into the Server Pool Master and verify that you are running ovs-agent-2.3-38 or later.

# rpm -q ovs-agent
ovs-agent-2.3-38


2. Edit the file /opt/ovs-agent-2.3/OVSSiteCluster.py and locate the following lines:

rs.append(("ha_check_oracle_vm_version", ha_check_oracle_vm_version(hosts)))
rs.append(("ha_check_agent_version", ha_check_agent_version(hosts)))
rs.append(("ha_check_cpu_compatibility", ha_check_cpu_compatibility(hosts)))
rs.append(("ha_check_hostname_ip", ha_check_hostname_ip(hosts)))
rs.append(("ha_check_storage_repos", ha_check_storage_repos(hosts, ha_enable)))
rs.append(("ha_check_ocfs2_config", ha_check_ocfs2_config(hosts)))

3. Comment out the specific line to bypass the pre-check. For example, to bypass the CPU compatibility check change the line as below:

# rs.append(("ha_check_cpu_compatibility", ha_check_cpu_compatibility(hosts)))

4. Restart the agent

# service ovs-agent restart
OVSAgentServer shutdown...
OVSAgentServer stopped.
OVSAgentServer is now starting...
OVSAgentServer started.

if you need to restart without having to shut down running VM's, do:

# service ovs-agent restart --disable-nowayout

5. This procedure will need to be executed on any pool server that might become a master via HA. After completing it, you will be able to add a server node that had previously failed with the ha_check_cpu_compatibility error.

Oracle VM ha_check_cpu_compability

If you received this error related to ha_check_compability on Oracle VM and you are using ovs-agent-2.3-38 or later, then you should edit /opt/ovs-agent-2.3/OVSSiteCluster.py and comment this line

rs.append(("ha_check_cpu_compatibility", ha_check_cpu_compatibility(hosts)))

Oracle sid and Linux pid

Sometimes we found Oracle process that has been running too long using top command on Linux. We know it has PID. But how to know the SID of the process?
Here is the script:

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

Select all data when parameter is null

Sometimes we need to display data based on report parameter and sometimes when user does not input anything, the default action is to display all data.
There are three ways we can use on WHERE clause on SQL:

where column_name = nvl(:parameter, column_name)

or

where column_name like nvl(:parameter, '%')

or

where (column_name = :parameter or :parameter is null)

Identify long running session and kill it

Sometimes you can find an Oracle database session that has been running for very long time. You can find it with this query:

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece

Then you can kill it with (change sid and serial# using result from query above):

ALTER SYSTEM KILL SESSION 'sid,serial#';

Delete old archivelog

To delete old archivelog, use rman and login.
use this command:

change archivelog until time 'sysdate-x' delete;

Change x with number of days (1,2,3...).

adoafmctl.sh: exiting with status 152

Unable to start oafm using adstrtal.sh. Running adoafmctl.sh ends with timeout.

You are running adoafmctl.sh version 120.6.12000000.3
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 152

Solution:

Clear the TLD cache:

- stop all middle tier services

- Delete/backup files this file:

$COMMON_TOP/_TldCache

- start all middle tier services

Database link error

I cloned my database to another machine and when I tried to use Oracle database link, this error came out:

ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from %s

I checked the connection using sqlplus, worked. Using connection description on sqlplus, also worked fine. I even recreated the database link, but still failed.
Then I cloned again to another machine, tested the database link, worked.
What was the difference between the two machine? It was the host name settings. The first one that failed had its host name only accessible locally. The second one had its host name registered on DNS server. So the database server I tried to access using database link could resolve host name of the second machine, but not of the first one.
Then I added the host name of first machine to DNS server, tested the database link, worked.

Patching OMS to 10.2.0.5 problem

When patching to OMS 10.2.0.5 on Windows, this error encountered:

ADMN-705021

The configuration files for this Oracle Application Server instance are inconsistent with the
configuration stored in the repository. In order to protect the repository,
no further configuration or deployment operations are allowed until the problem with the configuration on the filesystem is resolved. This condition arises when a prior operation was unsuccessful. The exception associated with this failed operation is:

{0}

. Please also check the logs located at
ORACLE_HOME/dcm/logs to determine why DCM was unsuccessful in updating
the configuration files on disk. Some possible causes are:

* permissions on files
* file contention issues on Windows NT
* internal Oracle error

After resolving the problem that prevented DCM from updating the configuration
files, you may use the dcmctl resyncInstance command to resolve the problem.
Alternatively, you can stop and then restart the active dcmctl or EMD
process and resyncInstance will automatically be performed.
Resolution:

Base Exception:
Transaction failed in Plugin cannot rename old configuration file:e:\OMS\oms10g\diagnostics\config\logloader.xml.
Resolution:
Transaction failed in Plugin.

Just run %ORACLE_HOME%\dcm\bin\dcmctl shutdown and retry the patch.

Install Enterprise Linux on HP dx2700

Installing Linux on HP dx2700 was a pain. On first trial, the installer was hang on loading ata_piix driver. The BIOS setting for SATA Port 0/1 Working Mode was Native.
On second trial, I set the hard drives setting on BIOS to None (Like the one I used on Windows 2003 installation). The installation went OK until reboot. The computer displayed "Error loading operating system". Then I realized that I haven't reenabled the hard disk and changed the boot sequence. Also I changed SATA Port 0/1 Working Mode to Legacy. After that everything was OK.
Of course if I set BIOS setting for SATA Port 0/1 Working Mode to Legacy before installation. The installation will be fine, but the hard drive will be detected as hda, the performance will be poor.

Remove Enterprise Manager from EBS Database

Shutdown and startup database in restrict mode.
Run this sql as sysdba: emca -deconfig dbcontrol db -repos drop

Cancel Invoice error with AP_INV_IS_CREDITED_INV

When canceling invoice, AP_INV_IS_CREDITED_INV error shown. There is a correction on the invoice. You have to fix the data directly on database. Use the following steps to fix the data:
1. Login as apps user to the database.
2. Identify the invoice_id of the invoice to be canceled using

select invoice_id from ap_invoices_all where invoice_num='your invoice number'

3. Verify that your invoice is a corrected invoice using

select invoice_id, line_number from ap_invoice_lines_all where invoice_id = corrected_inv_id

4. You should see your invoice_id on the result, if not then you are facing another problem.
5. Fix the data using this sql

update ap_invoice_lines_all set corrected_inv_id=null where invoice_id='your invoice id from step 2' and line_number='line number from step 3'

6. Don't forget to commit.
7. Cancel your invoice from application.