Unable to create an Oracle ASM database lower than 12.1.0.2

Unable to create an Oracle ASM database lower than 12.1.0.2.17814 PSU (12.1.2.12).

Hardware Models:

Oracle Database Appliance X6-2-HA, X5-2, X4-2, X3-2, and V1.

Workaround:

There is not a workaround. If you have Oracle Database 11.2 or 12.1 that is using Oracle Automatic Storage Management (Oracle ASM) and you want to upgrade to a higher release of Oracle Database, then you must be on at least Oracle Database Appliance 12.1.2.12.0 and Database Home 12.1.0.2.170814.

The upgrade path for Oracle Database 11.2 or 12.1 Oracle ASM is as follows:

• If you are on Oracle Database Appliance version 12.1.2.6.0 or later, then upgrade to 12.1.2.12 or higher before upgrading your database.
• If you are on Oracle Database Appliance version 12.1.2.5 or earlier, then upgrade to 12.1.2.6.0, and then upgrade again to 12.1.2.12 or higher before upgrading your database.

This issue is tracked with Oracle bug 21626377, 27682997, and 21780146. The issues are fixed in Oracle Database 12.1.0.2.170814.

Error when upgrading virtualized platforms to 12.2.1.4.0 in ODA

When upgrading Oracle Database Appliance hardware models with virtualized platforms to 12.2.1.4.0, perform these manual steps before upgrading to release 12.1.2.12.

If you upgrade Oracle Database Appliance hardware models with a virtualized platform, to 12.1.2.12, then there is an error when using the driver domain functionality. To upgrade to Oracle Database Appliance release 12.2.1.4.0, follow the steps documented in the workaround section.

Hardware Models:

Oracle Database Appliance X7-2-HA, X6-2-HA, X5-2, X4-2, X3-2, and V1 with a virtualized platform that use driver domain virtual machines

Workaround:

Follow these steps to upgrade to Oracle Database Appliance release 12.2.1.4.0:

• Deploy Oracle Database Appliance release 12.1.2.11 and create multiple virtual machines with driver domain enabled.
• Shut down all running virtual machines and repository.
• Apply the release 12.1.2.12 patch bundle.
• Apply the release 12.2.1.4.0 patch bundle.
• Start the repository. If you encounter the error OAKERR:5015 Start repo operation has been disabled by flag, then run the following command: 
• "oakcli enable startrepo -node 0/1"
• Start the virtual machines and confirm that they have started.

ODA: 'oakcli update -patch --database' Does Not Run Datapatch

Upon running /opt/oracle/oak/bin/oakcli update -patch 12.1.2.6.0 -database, some of the databases do not receive the update. The database homes are patched correctly and there are no errors in the update logs.

Symptoms:
select * from dba_registry_sqlpatch;

This either shows no rows selected or some previous entry.

Cause:
There are multiple bugs around this where ODA is still trying to run catbundle instead of datapatch, and will be fixed in future release.

Fix:
Workaround
Please set your oracle home and SID for each database one at a time, cd to
$ORACLE_HOME/OPatch directory and run
./datapatch

After it is run get the following from each DB
select * from dba_registry_sqlpatch;

This should show the current output with "Success"

ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST

Below error occurred while changing the destination of archive log files in oracle database.

Error:

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;
ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Fix:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439
SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439

Errors while applying 12.2.7 RUP Patch (24690690) in Oracle E-Business Suite 12.2

System got hanged while applying 12.2.7 RUP Patch (24690690) and adop exits with the errors.

Error:

1) All workers are failed with the Java error..
2) Invalid objects count is increased to 56k.

Troubleshooting:

1) Cleared the invalid objects by using utlrp.sql
2) Run the auto config on DB node
3) Start the patching activity again.

In step (3) while running auto config in database node exists with the below error.

ORA-01400: cannot insert NULL into ("APPLSYS"."FND_NODES"."NODE_NAME")

Adop error message: 

ERRORMSG: Cannot identify any valid application tier nodes in ADOP_VALID_NODES table. Ensure AutoConfig has been run on all nodes.

To resolve this error need to follow the below steps:

Step 1:
drop synonym apps.fnd_nodes;
create synonym apps.fnd_nodes for applsys.fnd_nodes;
drop synonym apps.fnd_oam_context_files;
create synonym apps.fnd_oam_context_files for applsys.fnd_oam_context_files;

Step 2:
Change the worker status to restart using adcrtl utility.

Step 3:
Run auto config again in database node. (Auto config completed without any errors)

Step 4:
Restart the patching activity.

adop phase=apply apply_mode=downtime patches=24690690 abandon=no restart=yes workers=16

ORA-04063: package body "SYS.DBMS_STATS" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"

After applying the patch (26925311: DATABASE PATCH SET UPDATE 12.1.0.2.180116)  in Oracle Database 12.1.0.2. We have faced below errors while executing the post patch installation steps.

Error:

We try to compile the invalid objects using utlrp.sql after applying the patch got the below error.

Warning: XDB now invalid, could not find xdbconfig
ORDIM INVALID OBJECTS: ORDIMDPCALLOUTS - INVALID - PACKAGE BODY

ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at "SYS.UTL_RECOMP", line 875
ORA-06512: at line 4

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                          INVALID
Oracle Multimedia 12.1.0.2.0                                                       INVALID
Oracle XML Database 12.1.0.2.0                                                 INVALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                          INVALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                       INVALID


Fix:

SQL> shutdown immediate;
SQL> startup restrict
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

Shutdown and restart the database in normal mode.

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                         VALID
Oracle Multimedia 12.1.0.2.0                                                     VALID
Oracle XML Database 12.1.0.2.0                                               VALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                      VALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                      VALID

ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.2.0 1.6) does not match that of the oracle executable (12.1.0.2.180116 1.6)

We have faced the below error while applying the below patch in Oracle E-Business Suite 12.2.7 (Upgrade).

Patch: adop phase=apply patches=25828573 hotpatch=yes

Issue: 
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.0 1.6) does not match that of the oracle executable

(12.1.0.2.180116 1.6)

Fix:
SQL> sho user
USER is "SYS"
SQL> @?/javavm/install/update_javavm_db.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>
SQL> -- If Java is installed, do CJS.
SQL>
SQL> -- If CJS can deal with the SROs inconsistent with the new JDK,
SQL> -- the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();

Call completed.

SQL>
SQL> create or replace java system;
  2  /

Java created.

SQL>
SQL> update dependency$
  2    set p_timestamp=(select stime from obj$ where obj#=p_obj#)
  3    where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
  4          (select type# from obj$ where obj#=p_obj#)=29  and
  5          (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
--------------------------------------------------------------------------------
1.6.0_181

1 row selected.


SQL> select dbms_java.longname('TEST') from dual;

DBMS_JAVA.LONGNAME('TEST')
--------------------------------------------------------------------------------
TEST

1 row selected.

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded : bsu.sh

We have faced java memory issue while applying weblogic patches in Oracle E-Business Suite R12.2.7

Error:
[applmgr@test bsu]$ ./bsu.sh -install -patch_download_dir=/u01/app/applmgr/TEST/fs1/FMW_Home/utils/bsu/cache_dir -patchlist=EQDE -prod_dir=/u01/app/applmgr/TEST/fs1/FMW_Home/wlserver_10.3
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
        at org.apache.xmlbeans.impl.schema.SchemaTypeImpl.getProperties(SchemaTypeImpl.java:704)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.loadPropertyMap(XBeanDataHandler.java:775)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.<init>(XBeanDataHandler.java:99)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.createDataHandler(XBeanDataHandler.java:559)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.getComplexValue(XBeanDataHandler.java:455)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:442)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getPatchDependencies(PatchCatalogHelper.java:464)
        at com.bea.plateng.patch.dao.cat.PatchCatalog.getPatchDependencies(PatchCatalog.java:56)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1621)
        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:281)
[

Reason:

The default java memory arguments  provided in bsu.sh file are not sufficient for patching activity (./bsu.sh)

Fix:

Increase the Java memory values in bsu.sh file (under $MW_HOME/utils/bsu) by increasing the value MEM_ARGS values.

Default Value:

MEM_ARGS="-Xms512m -Xmx1024m"

Changed Values:

MEM_ARGS="-Xms1024m -Xmx2048m"

Save the bsu.sh file and restart the patching activity.

Kernel of proper version is not found on node "test" [Expected = "2.6.39" ; Found = "2.6.32-300.3.1.el6uek.x86_64"]

The following error occurred while installing Oracle E-Business Suite 12.2.7 (Database pre-install checks failed).

Error:

ERROR: [Result.addErrorDescription:624]  Kernel of proper version is not found on node "test" [Expected = "2.6.39" ; Found = "2.6.32-300.3.1.el6uek.x86_64"]

Fix:

The following are the steps needed to install the rpm using public-yum, with sample output from an Oracle Linux 6 installation:

1. As an authorized user (such as root), retrieve the file that configures repository locations:
# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo
For Oracle Linux 5 or 7, users should replace 'public-yum-ol6.repo' in the command above with 'public-yum-el5.repo' or 'public-yum-ol7.repo' respectively.

2. Using a text editor, change the field 'enabled=0' to 'enabled=1' for the repositories corresponding to the machine's operating system while also enabling the 'addons' channel. Here's an example of a repo file's entries:

[ol6_latest]

name=Oracle Linux $releasever Latest ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1


[ol6_addons]

name=Oracle Linux $releasever Add ons ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1



[ol6_UEK_latest]

name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/latest/$basearch/

gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

gpgcheck=1

enabled=1


3.Update all packages and then install the pre-install rpm by running the following yum commands:

# yum update


# yum install oracle-ebs-server-R12-preinstall 
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracle-ebs-server-R12-preinstall.x86_64 0:1.0-3.el6 will be installed
--> Processing Dependency: libstdc++-devel for package: oracle-ebs-server-R12-preinstall-1.0-3.el6.x86_64
--> Processing Dependency: libXp(x86-32) for package: oracle-ebs-server-R12-preinstall-1.0-3.el6.x86_64
..
Transaction Summary
================================================================================
Install     176 Package(s)
Upgrade      11 Package(s)
Total download size: 168 M
Is this ok [y/N]: y
Downloading Packages:
..

Dependency Updated:
  db4.x86_64 0:4.7.25-18.el6_4                                                 
  db4-utils.x86_64 0:4.7.25-18.el6_4                                           
  glib2.x86_64 0:2.26.1-3.el6                                                 
  glibc.x86_64 0:2.12-1.132.el6                                               
  glibc-common.x86_64 0:2.12-1.132.el6                                         
  libblkid.x86_64 0:2.17.2-12.14.el6                                           
  libgcc.x86_64 0:4.4.7-4.el6                                                 
  libstdc++.x86_64 0:4.4.7-4.el6                                               
  libuuid.x86_64 0:2.17.2-12.14.el6                                           
  nss-softokn-freebl.x86_64 0:3.14.3-9.el6                                     
  util-linux-ng.x86_64 0:2.17.2-12.14.el6                                     
Complete!
 
Versions listed above are an example - actual versions may be different based on updates to the packages.

Detailed log and backup files are available under /var/log/oracle-ebs-server-R12-preinstall.

libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory at java.lang.ClassLoader$NativeLibrary.load(Native Method)

This error occur while trying to  ./rapidwiz-version in Oracle linux 6, and resulting the Wrong package version.

Issue:

Rapid Install Wizard will now launch the Java Interface.....

Exception in thread "main" java.lang.UnsatisfiedLinkError: /u01/Stage122/startCD/Disk1/rapidwiz/jre/Linux_x64/1.6.0/lib/i386/xawt/libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.load0(Unknown Source)
at java.lang.System.load(Unknown Source)
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.<clinit>(Unknown Source)
at java.awt.Component.<clinit>(Unknown Source)
Could not find the main class: oracle.apps.ad.rapidwiz.RIWizard.  Program will exit.

Fix:

First we need to remove the old package using that resulting the issue :

rpm -e package name

[root@11111111 yum.repos.d]# rpm -qa | grep -i libaio*
libaio-devel-0.3.107-10.el6.x86_64
libaio-0.3.105-2.x86_64
libaio-0.3.107-10.el6.x86_64
[root@11111111 yum.repos.d]# rpm -e libaio-0.3.105-2.x86_64

Install the required package from the below link :

http://rpm.pbone.net/index.php3/stat/4/idpl/5543656/dir/startcom_5/com/libXi-1.0.1-3.1.i386.rpm.html

http://rpm.pbone.net/index.php3/stat/4/idpl/8118337/dir/startcom_5/com/libXi-1.0.1-3.1.x86_64.rpm.html

If Still issue is not resolved follow the below steps:

1. copy repo from https://public-yum.oracle.com/public-yum-ol6.repo to /etc/yum.repos.d/

2. enable addons channel in .repo file.

3. yum install oracle-ebs-server-R12-preinstall -y

above steps will cover dependencies and let you install EBS.

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN backup failed with the below error message and its require archive log files to complete the RMAN backup.

Error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/24/2018 03:35:09
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/arch/1_282613_769193473.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

This issue will happen If in case archive logs was deleted or corrupted.

Fix:

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

OR

If you are using catalog database to maintain RMAN repository instead of controlfile, you can try following command:

RMAN> resync catalog;

Run the backup again.

How to Rotate or Purge Listener Log Data to Avoid Large listener.log File?

How to rotate or backup or rename listener.log or purge listener log data to avoid large listener.log file?

Below are the possible ways to rotate/rename listener.log files.

1) Stop the listener on a regular basis then you can simply rename the file at the OS level, then on next listener startup, a new one will be created.

OR

2) If you have a 24X7 environment and the listener can not be stopped, then you can use the following steps to rename / move the file without having to restart it:

LSNRCTL>
LSNRCTL> set current_listener <listenername>
LSNRCTL> set log_status off

In another window rename the log file. Then return to previous window

LSNRCTL> set log_status on

which will create a new and empty listener.log file which will continue logging. 


 OR

 3) Follow the document here:

Note 135063.1   How To Change the Listener Log Filename Without Stopping the Listener

How to move RAC Database ORACLE_HOME from one location to another location

Below are the steps to move the RAC database ORACLE_HOME from one location(filesystem) to another location.

This post for database home only and does not apply to CRS or GI home.

Execute all the following as database user:

Solution 1:

1. Install a new set of RAC binaries with OUI into a new home.
2. Apply all patches that have been installed in the old home, use "opatch lsinventory" to compare the old and new home once all patches are applied.
3. Copy over init{ORACLE_SID}.ora in dbs directory from the old home to the new home on all nodes.
4. Copy over network files (listener.ora, tnsnames.ora, and sqlnet.ora) in network/admin directory from the old home to the new home on all nodes.
5. Shutdown the database with srvctl from the old database home: srvctl stop database -d <dbname>
6. Modify the database resource as with srvctl from the old database home: srvctl modify database -d -o <new-database-home>
7. Start the database with srvctl from the new database home: srvctl start database -d <dbname>


Solution 2: (Clone)

1. Copy existing RAC binaries to a new location and keep the same ownership/permission on any node: cp -rp <old-database-home> <new-database-home>
2. Execute:
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2

$ perl $ORACLE_HOME/clone/bin/clone.pl '-O"CLUSTER_NODES={<comma separated listed of hostnames>}"' '-O"LOCAL_NODE=<this host’s hostname>"' ORACLE_BASE=<your ORACLE_BASE> ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=<your inventory name> '-O-noConfig' "

Refer to: https://docs.oracle.com/cd/E11882_01/rac.112/e41960/clonerac.htm

3. Extend the new home to other nodes: <new-database-home>/oui/bin/addNode.sh
4. Go to Step3 of "Solution 1"

ORA-00304: requested INSTANCE_NUMBER is busy

ORA-00304: requested INSTANCE_NUMBER is busy

On starting upon instance  in 2 Node RAC Database,We got the below error message.

SQL> startup;
ORA-00304: requested INSTANCE_NUMBER is busy

We have identified the background sessions are still hanging at the OS level.

Solution:

Solution is to Kill the hanging sessions at the os level and restart the instance.

ps -ef | grep -i DATABASE_SID

kill -9 pid1 pid2

after killing the hanging session , we are able to startup the instance without any issues.

How to find the Weblogic Admin Console URL Location in Oracle OEM?

 We can find the details from setupinfo.txt file. This file is created during the installation time.

 Location: $ORACLE_HOME/oms/install/setupinfo.txt 

 Output: 

Use the following URL to access:

        1. Enterprise Manager Cloud Controls URL: https://test.oracle.com:7802/em
        2. Admin Server URL: https://test.oracle.com:7102/console

The following details need to be provided during the additional OMS install:

        1. Admin Server Hostname: test.oracle.com

        2. Admin Server Port: 7102

How to find the OPP log file location in oracle application 11i/R12.1 and R12.2

Below script to find the OPP log file location in oracle E-Business Suite.

Script:

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id = &&request_id;

Growth of oracle database size in day, week and month wise

Below query to get the database size growth in day, week and month wise.

Query:

set linesize 210
set linesize 210
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.K / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.K / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Growth MONTH",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.K / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Growth MONTH in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS K FROM dba_free_space) FREE
GROUP BY FREE.K;

Query to get the Custom Tables List in oracle application

Below query to get the list of custom tables in oracle application.

Query:

select object_name,
   object_type
from all_objects
where 1=1
and object_type='TABLE'
and status='VALID'
and object_name like 'XXCUST%';

Query to get list of Custom Form List in oracle application

Below query to get the list of Custom Form List in oracle application.

Query:

SELECT  DISTINCT forms.form_name,
         formstl.user_form_name,
         func.function_name,
         func.user_function_name,
         fm.menu_name,
         menu.prompt menu_prompt,
         menu.description,
         restl.responsibility_name
        -- FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions_VL FUNC,
         fnd_menu_entries_VL MENU,
         FND_MENUS FM,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE     1=1
         and forms.form_id = formstl.form_id
         and func.form_id = forms.form_id
         and menu.function_id = func.function_id
         and menu.menu_id=fm.menu_id
         and res.menu_id = menu.menu_id
         and res.responsibility_id = restl.responsibility_id
         and UPPER(forms.form_name) like 'XXCUST%'      
         order by 1;

Query to get list of Custom Concurrent Programs in oracle applications

Below query to get the list of Custom Concurrent Programs in oracle applications.

Query:

SELECT   DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",
fa.application_name,
fcp.concurrent_program_name conc_short_name,
decode( fef.execution_method_code, 'H','Host',
'I', 'PLSQL Stored Procedure',
'P', 'Report',
'L', 'SQL Loader',
'Q','SQL*Plus',
'A','Spawned',
'K','Java Concurrent Program',
'B','Request Set Stage Function',
'S','Immediate',
'J','Java Stored Procedure',
'M','Multi-Language Function',
fef.execution_method_code) "execution method",
fef.executable_name,
fcp.description "Concurrent Program Description",
fef.executable_name"Executable Name",
fef.description "Executable Description",
fef.execution_file_name ,
fcp.enabled_flag
,FCP.CREATION_DATE
FROM fnd_executables_form_v fef,
                fnd_concurrent_programs_VL fcp,
                fnd_application_tl fa
WHERE fcp.application_id=fef.application_id
and fa.application_id=fcp.application_id
AND fef.executable_id=fcp.executable_id
AND UPPER(fcp.user_concurrent_program_name) like 'XXCUST%'
ORDER BY 4;