APP-FND-02704: Unable to alter user APPS to change password

Hi All,

Changing Apps password using FNDCPASS.

Trying to password using FNDCPASS and it errored as below
bash-4.1$ FNDCPASS apps/apps123 0 Y system/system123 SYSTEM APPLSYS welcome

bash-4.1$ cat L8041552.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 module:
+---------------------------------------------------------------------------+
Current system time is 30-MAY-2014 15:19:18
+---------------------------------------------------------------------------+
Working...
APP-FND-02704: Unable to alter user APPS_MRC to change password.
APP-FND-02704: Unable to alter user APPS to change password.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 30-MAY-2014 15:19:19
+---------------------------------------------------------------------------+

Issue 1:
APP-FND-02704: Unable to alter user APPS_MRC to change password.
As APPS_MRC is no longer in use from 11.5.10.2 We can delete this entry
Solution
*********
You need to do the following steps:

1.) Stop all application services.
2.) Start only database and database listener.
3.) Take backup of FND_USER and FND_ORACLE_USERID table first.
4.) Connect to SQLPLUS as APPS user and delete the row from FND_ORACLE_USERID table for "APPS_MRC".
     SQL> delete from FND_ORACLE_USERID where ORACLE_USERNAME='APPS_MRC';
     SQL> commit;
5.) Change the APPS password using FNDCPASS.
6.) Check the FNDCPASS logfile for any error. If the password has changed successfully then run
autconfig in application tier. Otherwise do not run autconfig.
7.) Start the application and let me know the outcome.

Issue 2:APP-FND-02704: Unable to alter user APPS to change password.
It throwed error stating apps password couldn;t be changed but it was able to make change to applsys password.

1) This failed because the default profile assigned to APPS schema is EXEMPT_POLICY(custom profile which is preventing the passowrd to be chnaged) as below
SQL> select profile from dba_users where username='APPS';
PROFILE                      
------------------------------
EXEMPT_POLICY

2) Change the profile to DEFAULT
SQL> alter user apps profile default;

then now reset password again using FNDCPASS.. It works

Enable SSL for EBS R12

Hi All,

This post assists in configuring SSL for EBS R12 from command line using orapki rather than owm utility. This illustrates example for self signed certificates.

This involves below series of steps to setup SSL.

1. Create New Wallet
2. Create a Certificate Request
3. Upload Certificate to Wallet
4. Modify the OPMN wallet
5. Import certs to cacerts
6. Update the Context File
7. Settings for DB Tier

. 6Create New Wallet

Login EBS Application server and source using environment file (APPS<sid_machine>.env) located in the APPL_TOP directory.

1. Navigate to the $INST_TOP/ora/10.1.3 and source the env file to set your 10.1.3 ORACLE_HOME variables.
2. Navigate to the $INST_TOP/certs/Apache directory.
3. Take backup of existing wallet files to a backup directory.
4. Create New wallet using orapki utility as below

bash-4.1$ cd $INST_TOP/Apache

bash-4.1$ orapki wallet create -wallet . -auto_login -pwd apps123

bash-4.1$ ls -ltr

total 16

-rw------- 1 oracle dba 7912 Apr 15 13:33 ewallet.p12

-rw------- 1 oracle dba 7940 Apr 15 13:33 cwallet.sso

Create a Certificate Request
Create new certificate request file by passing appropriate details as input which is used for generating a SSL Certificate.

1. Below are the details required to be passed to generate csr file.

Common Name name of server with domain, e.g. mylinux.domain.com
Organizational Unit: The unit within your organization, e.g. HR
Organization: is the name of your organization, e.g. Home
Locality/City: is your locality or city, e.g. New York
State/Province: is the full name of your State or Province - do not abbreviate,

Country: Select country from drop down list, e.g. USA

Keysize: Encryption level and min is 1024, recommended value - 2048

3. Use command as below to generate certificate request file and export it.

$ orapki wallet add -wallet . -dn "CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA" -keysize 2048 -pwd apps123

 

$ orapki wallet add -wallet . -dn "CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA" -keysize 2048 -request server.txt -pwd apps123

4. Verify the certificate request file from wallet as below.

 

$ cd $INST_TOP/certs/Apache/

$ orapki wallet display -wallet .

Requested Certificates:

Subject:        CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA  ß File requested

User Certificates:

Trusted Certificates:

Subject:        CN=GTE CyberTrust Root,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

Then submit the generated request file(server.txt) to Certifying authority to request a self-signed certificate.

Upload Certificate to Wallet

1. As the certificate provided is self signed, once Certifying authority provides Self-signed Server Certificate we need additional certificates to import them to the wallet. We need to download root certificate, intermediate certificates to import server certigficate to wallet.

Note: Save certificate in base-64 format.

2. Once certificate is received save the file as server.crt in ($INST_TOP/certs/Apache). Also import all the root certificates of this certificate and save certificates with necessary conventions as below.

Root Certifciate - ca.crt

Intermediate Certificate - intermediate_base64.cer

Server.crt - server_base64.cer

 

Root & Intermediate certificates can be obtained from Internal Certifying authority if you are using self signed certificates.

 

3. Ftp the certificate files to the server and place them in $INST_TOP/certs/Apache directory.
4. Upload these certificates to Wallet using commands as below.

bash-4.1$ cd $INST_TOP/certs/Apache

 

$ orapki wallet add -wallet . -trusted_cert -cert SSL/ca.crt -pwd apps123

$ orapki wallet add -wallet . -trusted_cert -cert SSL/intermediate_base64.cer -pwd apps123

$ orapki wallet add -wallet . -user_cert -cert SSL/123080_base64_certificate.cer -pwd apps123

5. Display Wallet contents and you should see all the certificates uploaded to wallet.

bash-4.1$ orapki wallet display -wallet .

Requested Certificates:

User Certificates:

Subject:        CN=mylinux.domain.com,OU=HR,O=Home,L=New york,ST=Newyork,C=USA

Trusted Certificates:

Subject:        CN=GTE CyberTrust Root,O=GTE Corporation,C=US

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Intermediate Authority,O=Home,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Subject:        CN=Home Root Certificate Authority,O=Home

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

6. We need to import root & intermediate certificates to b64InternetCertificate.txt file located in the 10.1.2 ORACLE_HOME/sysman/config directory.

$ cd $INST_TOP/certs/Apache/

$ cat SSL/ca.crt >>$ORACLE_HOME/sysman/config/b64InternetCertificate.txt ß root cert

$ cat SSL/infra_intermediate_base64.cer >>$ORACLE_HOME/sysman/config/b64InternetCertificate.txt  ß Inter cert

$ cat SSL/Infra_East_Base64.cer  >> $ORACLE_HOME/sysman/config/b64InternetCertificate.txt  ßInter cert

Modify the OPMN wallet

As default opmn wallet comes with demo wallet files, these should be now replace with newly created wallets as below.

1. Navigate to the $INST_TOP/certs/opmn directory.
2. Create a new directory named BAK
3. Move the ewallet.p12 and cwallet.sso files to the BAK directory just created.
4. Copy the ewallet.p12 and cwallet.sso files from the $INST_TOP/certs/Apache directory to the $INST_TOP/certs/opmn directory.

$ cd $INST_TOP/certs/opmn

$ mkdir BAK

$ mv * BAK/

$ cp ../Apache/ewallet.p12 .

$ cp ../Apache/cwallet.sso .

Import certs to cacerts
Now the new certificates files needs to be uploaded to cacerts under $OA_JRE_TOP/security/lib for proper functioning of XML Piublisher, OPP, etc.,

1. Navigate to the $OA_JRE_TOP/lib/security directory
2. Backup the existing cacerts file.

cd $OA_JRE_TOP/lib/security

cp cacerts cacerts.orig

4. Copy your ca.crt and server.crt files to this directory and issue the following command to insure that cacerts has write permissions:

$ chmod u+w cacerts

5.     Add your Apache ca.crt and server.crt to cacerts as below.

bash-4.1$ cd -

/oracle/orainst1/d112uk/inst/apps/d112uk_camcfnldap01/certs

bash-4.1$ cd Apache/SSL/

bash-4.1$ ls

Server_base64_certificate.cer  ca.crt  intermediate_base64.cer

bash-4.1$ cat intermediate_base64.cer >> ca.crt

bash-4.1$ cp -pr ca.crt $OA_JRE_TOP/lib/security

bash-4.1$ cp -pr Server_base64_certificate.cer $OA_JRE_TOP/lib/security

 

bash-4.1$ cd $OA_JRE_TOP/lib/security

 

$ keytool -import -alias ApacheRootCA_2048 -file ca.crt -trustcacerts -v -keystore cacerts

When prompted enter the keystore password (default password is changeit).

$ keytool -import -alias ApacheServer_2048 -file 123080_base64_certificate.cer -trustcacerts -v -keystore cacerts

Trust this certificate? [no]:  Yes

Certificate was added to keystore

 

Update the Context File

1. Backup existing context file under $INST_TOP/appl/admin/<db>_<server>.xml
2. Modify all below variables in the context file and save it.

SSL Related Variables in the Context File
Variable	Non-SSL Value	SSL Value
s_url_protocol	http	https
s_local_url_protocol	http	https
s_webentryurlprotocol	http	https
s_active_webport	same as s_webport	same as s_webssl_port
s_webssl_port	not applicable	4443 (port pool 0)
s_https_listen_parameter	not applicable	same as s_webssl_port
s_login_page	http://mylinux.domain.com:8000/OA_HTML/AppsLogin	https://mylinux.domain.com:4443/OA_HTML/AppsLogin
s_external_url	http://mylinux.domain.com:8009	https://mylinux.domain.com:4443

3. Run autoconfig using adautocfg.sh file.
4. Restart the middle tier services.

Settings for DB Tier

To enable SSL on the Database Tier, We need to import server certificate,ca.crt (and intca.crt if it exists) by creating an empty wallet.

1. Connect to database and find the wallet location as below.

SQL> select fnd_profile.value('FND_DB_WALLET_DIR') from dual

SQL> /

 

FND_PROFILE.VALUE('FND_DB_WALLET_DIR')

--------------------------------------------------------------------------------

/oracle/u011/mydb/db/tech_st/11.2.0/appsutil/wallet

3. After setting your environment for the database tier, navigate to the $ORACLE_HOME/appsutil directory.
4. Verify for directory waller, if It doesn’t exist create now.
5. Navigate to the newly created wallet directory.
6. Create a new empty wallet in this directory

orapki wallet create -wallet $ORACLE_HOME/appsutil/wallet -auto_login -pwd apps123

7. FTP the certificate files from application server to database server and import them into this newly created wallet as below.

bash-4.1$ pwd

/oracle/u011/mydb/db/tech_st/11.2.0/appsutil/wallet

 

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert root_base64.cer -pwd apps123

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert intermediate_base64.cer -pwd apps123

 

$ orapki wallet add -wallet $ORACLE_HOME/appsutil/wallet -trusted_cert -cert 123080_base64_certificate.cer -pwd apps123

8. Test the wallet created from above steps as below and it should return page content.

SQL> select UTL_HTTP.REQUEST

( url => fnd_profile.value('APPS_FRAMEWORK_AGENT')||'/OA_HTML/IRCRESUMEUK1.xsl',

  proxy => hr_util_web.proxyForURL(fnd_profile.value('APPS_FRAMEWORK_AGENT')),

  wallet_path=>'file:'||fnd_profile.value('FND_DB_WALLET_DIR'),  wallet_password=>fnd_preference.eget('#INTERNAL','WF_WEBSERVICES','EWALLETPWD','WFWS_PWD'))

from dual;

 

The above query should return HTML output.

 -- This completed SSL Setup

Import of Partitioned tables running for Long time

Hi,

Recently we did full import of our Database from 10g to 11g. There are couple issues found during import.
1) The export activity took around 3 hrs to complete but Import took around 20hrs to complete.
2) Database size has grown large than it is expected.

I tried to debug the issue and find the solution and below are the details.

 

Issue1:

Tablespaces with partitioned objects have grown from 20GB to 100GB.

This is issue is due to new hidden parameter _partition_large_extents introduced in 11gR2 specifically.

 

Solution:

Before start of import set the parameter _partition_large_extents to FALSE

 

Issue2:

Import during TUP1:

  full import of 10g database is done into 11g database as part of platform migration. This activity took around 20 hrs to complete.

Identified Time consuming tasks:

1)    We have partitoined table with around 4000+ partitions – 10 hrs – Due to large number of partitions and many partitions have data in them.

2)      Table Statistics import – 6 hrs

3)      Index statistics - 2 hr

 

Solution:

To minimize the downtime, the full database import is split and performed. Below is the complete set of activities tested with timing details captured. This total activity has brought down the total time to 4 hrs.

 1) Import Full database Import of full database excluding some objects & statistics.
Excluded partition tab;e and objects statistics
  e.g.: Impdp full=y exclude=TABLE:<TABLE NAME>
2) Imp excluded objects into a different table Import excluded Partitioned object sepeartely with merge partition option
 A separate table is created without any partitions using partitions=merge and remap_table options.
 
  e.g.: impdp TABLES=<TABLE_NAME> REMAP_TABLE= SOURCENAME:TARGETNAME 

3) Import Metadata Only Import of metadata only for excluded object into database
Imported only metadata only of excluded source table along with partitions
  e.g.: impdp CONTENT=METADATA_ONLY 

4) Insert data  Insert data from table created in step 2 to Table created in Step 3 using sqlplus
#Import data from non partitioned table to partitioned table

#5) Gather stats Gather statistics of all objects
This is estimated time and needs to be tested again.