How To Create Excel Reports In Database

set feed off markup html on spool on
spool example.xls
select * from tab;
spool off
set markup html off spool off

Weblogic 10.3.6 Patching Error Using BSU - "java.lang.OutOfMemoryError: GC overhead limit exceeded"

Applies To:
Oracle WebLogic Server - Version 10.3.6 to 12.1.1.0 Information in this document applies to any platform.

Symptoms:
When attempting to apply a patch, the following error occurs:
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
  at java.lang.Class.getDeclaredMethods0(Native Method)
  at java.lang.Class.privateGetDeclaredMethods(Class.java:2521)
  at java.lang.Class.getMethod0(Class.java:2764)
  at java.lang.Class.getMethod(Class.java:1653)
  at com.bea.cie.common.dao.xbean.XBeanDataHandler.isValueSet(XBeanDataHandler.java:958)
 com.bea.cie.common.dao.xbean.XBeanDataHandler.getValueFromObject(XBeanDataHandler.java:589)com.bea.cie.common.dao.xbean.XBeanDataHandler.getSimpleValue(XBeanDataHandler.java:431
  at com.bea.plateng.patch.dao.cat.PatchDetail.getId(PatchDetail.java:42)
  at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDetails(PatchCatalogHelper.java:700)
  at com.bea.plateng.patch.dao.cat.Version.getPatchDetails(Version.java:41)
 com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getFilteredPatchDetailMap(PatchCatalogHelper.java:1202) com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDetails(PatchCatalogHelper.java:1398)
 com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getEveryPatchDetail(PatchCatalogHelper.java:76com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1622)  at com.bea.plateng.patch.PatchSystem.updatePatchCatalog(PatchSystem.java:436)
  at com.bea.plateng.patch.PatchSystem.refresh(PatchSystem.java:130)
  at com.bea.plateng.patch.PatchSystem.setCacheDir(PatchSystem.java:201)
  at com.bea.plateng.patch.Patch.main(Patch.java:306)

Changes:

None

Cause:

The heap size specified for BEA Smart Update (BSU) is not sufficient.

Solution:

Follow these steps to resolve the issue:
Go to $FMW_Home/utils/bsu.
Edit bsu.sh (for UNIX) or bsu.cmd (for Windows).
Here you will find the following: MEM_ARGS="-Xms256m -Xmx512m"
Increase these values as needed: for example, to "-Xms2048m -Xmx2048m".

Reference metalink Doc ID 2007492.1

What Are Options For Backing Up Prior To Using BSU To Patch WebLogic 10.3.6? 

Applies To:
Oracle WebLogic Server - Version 10.3 and later
Information in this document applies to any platform.

Goal:
Provide options for backing up prior to using bsu to patch WebLogic.

Solution:
Depending on the patch and other steps, following are some of the directories that could be modified.

WL_HOME/common/bin/...
WL_HOME/common/deployable-libraries
WL_HOME/portal/lib/modules
WL_HOME/servicebus/lib
WL_HOME/integration/common/lib/installation
MW_HOME/patch_weblogicversion/... (eg. /app/wls1036/patch_wls1036
DOMAIN_HOME/bin/... (See "Using the Start Script Editor")

For a complete backup save following directories
DOMAIN_HOME/bin
MW_HOME [patch_weblogicversion] (eg. /app/wls1036/patch_wls1036 )
WL_HOME (To be on the safe side backup all subdirs)

Reference metalink Doc ID 2276030.1

Steps to Change the APPS, APPLSYS, and APPS_NE Password Using FNDCPASS or AFPASSWD 

Applies To:

Oracle Application Object Library - Version 12.2 and later, Information in this document applies to any platform.

Goal:
What are the steps to change the APPS, APPLSYS, and APPS_NE password using FNDCPASS for E-Business Suite (EBS) 12.2?

Solution:

Whenever using FNDCPASS or AFPASSWD to change the APPS, APPLSYS, and APPS_NE password, one must also perform the following actions:

Important: These steps must be carried out on the run file system. The FND_USER and FND_ORACLE_USERID tables should be backed up before any passwords are changed.

Remove the backups after you have confirmed that the changes are successfully completed. 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 using the below script:

$INST_TOP/admin/scripts/adstpall.sh

2. Change the APPLSYS password using

A. FNDCPASS:

Use the below syntax:

FNDCPASS <logon> 0 Y <SYSTEM username>/<SYSTEM password> SYSTEM APPLSYS <new_password>
For example, the following command changes the APPLSYS password to 'WELCOME':

FNDCPASS apps/<appspwd> 0 Y system/manager SYSTEM APPLSYS WELCOME

Example:

[apps@ebs scripts]$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS <new_password>
Log filename : L471367.log
Report filename : O471367.out

[apps@ebs scripts]$ vi L471367.log

Note: Please note that you have $FND_TOP/patch/115/sql/ AFSCJAVS.pls file version 120.12.12020000.8 or above before running FNDCPASS on 12.2.X.

Else you need to apply the patch 19127427 : UNABLE TO CHANGE PASSWORD USING FNDCPASS - NO ERRORS IN THE LOG, If not FNDCPASS with fail to change the password.

 B. AFPASSWD:

AFPASSWD is an enhanced version of FNDCPASS, and includes the following features:

• AFPASSWD only prompts for passwords required for the current operation, allowing separation of duties between applications administrators and database

administrators. This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the

SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

• When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

• In Oracle E-Business Suite Release 12.2.3 and higher, you can also use the AFPASSWD utility to migrate Oracle E-Business Suite user passwords to a password hashing scheme.

Note: AFPASSWD can be run from the database tier as well as the application tier. In contrast, FNDCPASS can only be run from the application tier.

Syntax for using AFPASSWD:

AFPASSWD [-c <APPSUSER>[@<TWO_TASK>]] -s <APPLSYS>

Example:

[apps@ebs scripts]$ AFPASSWD -c apps@PROD -s APPLSYS
Enter the ORACLE password of Application Object Library 'APPSUSER': (Here it will ask old apps password)
Connected successfully to APPS.
Enter the password for your 'SYSTEM' ORACLE schema: (system password of a database user)
Connected successfully to SYSTEM.
Log file: AFPWD_PROD_454358.log ( one new logfile created )
Enter new password for user: (new password)
Verify new password for user: ( retype a new password)
AFPASSWD completed successfully.

NOTE: The steps 3 to 7 are mandatory for both FNDCPASS and AFPASSWD and are only applicable when changing the APPLSYS password. They are not applicable when changing passwords for product schemas like PO, GL etc or the SYSTEM schema.

In the next prepare phase after the password change, ADOP will invoke EBS Domain Configuration to ensure that the WLS datasource on the patch file system will be synchronized with the new APPS password.

3. Run autoconfig with the newly changed password.

4. Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.

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

a. Log in to WLS Administration Console.
b. Click Lock & Edit in Change Center.
c. In the Domain Structure tree, expand Services, then select Data Sources.
d. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
e. On the "Settings for EBSDataSource" page, select the Connection Pool tab.
f. Enter the new password in the "Password" field.
g. Enter the new password in the "Confirm Password" field.
h. Click Save.
i. Click Activate Changes in Change Center.

6. Start all the application tier services using the below script

$INST_TOP/admin/scripts/adstrtal.sh

7. Verify the WLS Datastore changes as follows:

a. Log in to WLS Administration Console.
b. In the Domain Structure tree, expand Services, then select Data Sources.
c. On the "Summary of JDBC Data Sources" page, select EBSDataSource.
d. On the "Settings for EBSDataSource" page, select Monitoring > Testing.
e. Select "oacore_server1".
f. Click Test DataSource
g. Look for the message "Test of EBSDataSource on server oacore_server1 was successful".

8. If Integrated SOA Gateway (ISG) is implemented, repeat the steps for "OAEADataSource". Verify "oafm_cluster*" managed servers are started successfully.

Reference metalink Doc ID 1674462.1

FNDCPASS Cause: AFPCOA Failed Due To ORA-28001: The Password Has Expired

Error:

FNDCPASS Cause: AFPCOA failed due to ORA-28001: the password has expired

Solution:

fndcpass or afpasswd cannot be used to change APPS password because they require login with APPS which fails because password has expired (vicious circle).

To solve the issue, please do the following:

Login as sqlplus apps/<OLD Apps Password> it prompts to enter new password so account could be opened ->>> re-enter the OLD password.

If this works the password expire issue will be fixed and then fndcpass or afpasswd can be used to change the password to a new one.

If an error is received because the same password can't be used, it's needed to check and if necessary change the following profile resources for APPS schema to be as below:

PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL

Check the current values:

select profile from dba_users where username='APPS';
SELECT * FROM dba_profiles WHERE profile = 'result_from_previous_query' AND resource_type = 'PASSWORD';

If any of the resources need to be changed, do it as in the below example:

SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null;

Then try again to set the old password via sqlplus. "

Please review this input and make any needed updates.

Reference metalink Doc ID 2051299.1