Steps to change Apps password for EBS 12.2

Follow all the below steps to change apps password in 12.2 instance. Passwords for all the 3 schemas APPS, APPLSYS and APPS_NE will be changed together while changing APPLSYS password

1. Shut down the application tier services

2. Change the APPLSYS password

FNDCPASS apps/<apps_pw> 0 Y SYSTEM/<system_pw> SYSTEM APPLSYS <new_password>

3.Run Autoconfig on Application Tier with newly changed password

4. Start the Adminserver

cd $ADMIN_SCRIPTS_HOME
./adadminsrvctl.sh start

5. Change the "apps" password in WLS Datasource as follows

• Log in to WLS Administration Console.

•  Click Lock & Edit in Change Center

•  In the Domain Structure tree, expand Services, then select Data Sources

•  On the "Summary of JDBC Data Sources" page, select EBSDataSource

•  On the "Settings for EBSDataSource" page, select the Connection Pool tab.

• Enter the new password in the "Password" and "Confirm Password" field
•  Click Save
                                                                                                               
•  Click Activate Changes in Change Center

Reference
Steps to change the APPS, APPLSYS, and APPS_NE password using FNDCPASS or AFPASSWD for EBS 12.2 (Doc ID 1674462.1)

How to find Oracle EBS Weblogic Server Admin Port and URL

Weblogic admin port

Method 1

Open the Application tier context file
vi $CONTEXTFILE

Check the value of 'WLS Admin Server Port' from "s_wls_adminport" parameter


Method 2

Open the EBS domain config file
vi $EBS_DOMAIN_HOME/config/config.xml

Check the 'listen-port' value of the 'AdminServer' 


Weblogic console URL

http://<server name>. <domain name> : <weblogic Admin Port>/console 

Ex: http://oracle.test.com:7002/console

Gather Schema Statistics Completed with ORA-20001 Error in R12

Problem

Gather Schema statistics concurrent program completed with following error

+-----------------------------------------------------------+
Start of log messages from FND_FILE
+-----------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked 
stats on table WF_CONTROL is locked 
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
+-----------------------------------------------------------+
End of log messages from FND_FILE
+-----------------------------------------------------------+

Solution

There are duplicate rows on FND_HISTOGRAM_COLS. Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user.

1. Identify Duplicate Rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

2. Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;

You can get the updated query by running following.

select 'delete from FND_HISTOGRAM_COLS where table_name = '''||table_name||''' and column_name = '''||column_name||''' and rownum=1;' from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

3. Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in 
  (
   select hc.table_name, hc.column_name
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
   where hc.table_name  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );

commit;

Reference:

11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)