shrink Datafiles and Reclaim unused Space in Oracle

Space issue:-

Found space utilization at data file mount point on prod.

/dev/mapper/datavg-lvol0 404G  365G   19G  96% /saprod_data01   

Avail Use% Mounted on

/dev/mapper/datavg-lvol1 404G  372G   12G  97% /saprod_data02

Solution:-

Step1:- verify 

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Step 2:- Output of the script

--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       624    584       40
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     18704  17799      905
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        64     16       48
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32704  32697        7
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32704  32641       63
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m       100     18       82
                ds.dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d      2048    878     1170
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32767  32726       41
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0       100     81       19
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      3072   2120      952
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip        64      2       62
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds       100      6       94
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      3072   1591     1481
                .dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      5120   3264     1856
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     23020  22273      747
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

By the output i can reclaim space around  7.783g of space

Step3:-Run below query 

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

Step4:- Output

SQL

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

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwstage.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwindex.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index02.dbf' resize 5M;

/* reclaim    2043M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index01.dbf' resize 5M;

/* reclaim    1857M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/system01.dbf' resize 3264M;

/* reclaim    1482M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/sysaux01.dbf' resize 1591M;

/* reclaim    1171M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dacrep_data01.dbf' resize 878M;

/* reclaim     953M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/infarep_data01.dbf' resize 2120M;

/* reclaim     905M from   18704M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_odi.dbf' resize 17800M;

/* reclaim     748M from   23020M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/undotbs01.dbf' resize 22273M;

/* reclaim     152M from   31744M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/dwh_data05.dbf */

SQL

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

/* reclaim      95M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_mds.dbf' resize 6M;

/* reclaim      83M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_mds.dbf' resize 18M;

/* reclaim      64M from   10240M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwdata5.dbf */

/* reclaim      64M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata2.dbf' resize 32641M;

/* reclaim      63M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_biplatform.dbf' resize 2M;

/* reclaim      48M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biplatform.dbf' resize 17M;

/* reclaim      42M from   32767M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_data07.dbf' resize 32726M;

/* reclaim      41M from     624M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biacomp.dbf' resize 584M;

/* reclaim      19M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/example01.dbf' resize 82M;

/* reclaim       8M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata1.dbf' resize 32697M;

21 rows selected.

SQL>

Step5:- Resize all datafile using above output.

Step6:-Execute step1 query to check changes after resizing of datafiles.

Tablespace      FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       584    584        0
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     17800  17799        1
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        17     16        1
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32761  32697       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32705  32641       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m        18     18        0
                ds.dbf
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d       878    878        0
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32726  32726        0
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0        82     81        1
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      2120   2120        0
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip         2      2        0
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds         6      6        0
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      1731   1591      140
                .dbf
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      3264   3264        0
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     22273  22273        0
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

In earlier output possible savings is 7.783g , Now possible savings is 356mb, So we reclaimed 7gb of space at OS level

Step6:- check space at os level

/dev/mapper/datavg-lvol0

                      404G  341G   43G  89% /saprod_data01

/dev/mapper/datavg-lvol1

                      404G  360G   24G  94% /saprod_data02

/dev/mapper/datavg-lvol2

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized.

 Error:-

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 8388608 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

 select total_size,awr_flush_emergency_count from v$ash_info;

Solution:-

The current ASH size is displayed in the message in the alert log, or can be found using the following SQL statement.

SQL>  select total_size,awr_flush_emergency_count from v$ash_info;
TOTAL_SIZE AWR_FLUSH_EMERGENCY_COUNT
---------- -------------------------
   8388608                         1

Then increase the value for _ash_size by some value, like 50% more than what is currently allocated.  For example if total_size = 16MB, then an increase of 50% more would be (16MB + (16MB * 50%)) = 24MB.

SQL> alter system set "_ash_size"=12450000;

System altered.

SQL> select total_size,awr_flush_emergency_count from v$ash_info;

TOTAL_SIZE AWR_FLUSH_EMERGENCY_COUNT

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

  12450000                         2

ADUTILITIES & AUTOCONFIG

 

OPATCH_JAVA_ERROR : An exception of type "OPatchException" has occurred - 10.1.2 Home in EBS R12

 Error:-

[applmgr@ebslearning appl]$ opatch lsinventory
Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..
Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)
Oracle Home           : /u02/R12APP/fs1/EBSapps/10.1.2
Oracle Home Inventory : /u02/R12APP/fs1/EBSapps/10.1.2/inventory
Central Inventory     : /u01/clonedb/database/PROD/db/tech_st/11.2.0.4/oraInvent                                                      ory
   from               : /etc/oraInst.loc
OUI location          : /u02/R12APP/fs1/EBSapps/10.1.2/oui
OUI shared library    : /u02/R12APP/fs1/EBSapps/10.1.2/oui/lib/linux/liboraInsta                                                      ller.so
Java location         : /u02/R12APP/fs1/EBSapps/10.1.2/jdk/jre/bin/java
Log file location     : /u02/R12APP/fs1/EBSapps/10.1.2/.patch_storage/<patch ID>                                                      /*.log
Creating log file "/u02/R12APP/fs1/EBSapps/10.1.2/.patch_storage/LsInventory__07                                                      -01-2018_16-28-31.log"
Result:
LsInventory: OPatch Exception while accessing O2O

OPATCH_JAVA_ERROR  : An exception of type "OPatchException" has occurred:
OPatch Exception:  OUI found no such ORACLE_HOME set in the environment
  Can not get details for given Oracle Home
  An exception occurs
  null

There is no Interim Patch
OPATCH_JAVA_ERROR  : An exception of type "OPatchException" has occurred:
Can not get a list of inventory on this home.
ERROR: OPatch failed because of Inventory problem.

Solution:-

[applmgr@ebslearning appl]$ cd $ORACLE_HOME

[applmgr@ebslearning 10.1.2]$ cat oraInst.loc

inventory_loc=/u01/clonedb/database/PROD/db/tech_st/11.2.0.4/oraInventory

inst_group=dba

[applmgr@ebslearning 10.1.2]$ ll /u01/clonedb/database/PROD/db/tech_st/11.2.0.4/oraInventory

total 8

drwxrwx--- 2 oracle dba 4096 Jul  1 16:19 ContentsXML

drwxrwx--- 2 oracle dba 4096 Jul  1 16:19 logs

[applmgr@ebslearning 10.1.2]$ cd oui/

[applmgr@ebslearning oui]$ ls

bin                   guide  oraparam.ini

clusterparam.ini      jlib   oraparam.ini.bak

clusterparam.ini.bak  lib    ouica.properties

[applmgr@ebslearning oui]$ cd bin/

[applmgr@ebslearning bin]$ ls

addNode.sh      attachHome.sh.bak  ouica.sh.bak  runInstaller.sh

addNode.sh.bak  lsnodes            resource      runInstaller.sh.bak

attachHome.sh   ouica.sh           runInstaller

[applmgr@ebslearning bin]$ ./attachHome.sh

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-07-01_04-32-19PM. 

Please wait ...

[applmgr@ebslearning bin]$ Java HotSpot(TM) Server VM warning: You have loaded library /tmp/OraInstall2018-07-01_04-32-19PM/oui/lib/liux/liboraInstaller.so which might have disabled stack guard. 

The VM will try tofix the stack guard now.

It's highly recommended that you fix the library with 'execstack -c <libfile>',or link it with '-z noexecstack'.

'AttachHome' was successful.

[applmgr@ebslearning bin]

Exception In Thread “Main” Java.Lang.OutOfMemoryError: GC Overhead Limit Exceeded With Bsu.sh

Error:-

[applmgr@ebslearning bsu]$ sh bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=QMJP -prod_dir=$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:707)

        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)

Solution:-

[applmgr@ebslearning bsu]$ cat bsu.sh

#!/bin/sh

JAVA_HOME="/u02/R12APP/fs1/EBSapps/comn/util/jdk64"

MEM_ARGS="-Xms512m -Xmx1024m -XX:+UseParallelGC"

"$JAVA_HOME/bin/java" ${MEM_ARGS} -jar patch-client.jar $*

[applmgr@ebslearning bsu]$ vi bsu.sh

[applmgr@ebslearning bsu]$ cat bsu.sh

#!/bin/sh

JAVA_HOME="/u02/R12APP/fs1/EBSapps/comn/util/jdk64"

MEM_ARGS="-Xms1024m -Xmx2048m -XX:+UseParallelGC"

"$JAVA_HOME/bin/java" ${MEM_ARGS} -jar patch-client.jar $*

The Applications System names per the APPL_TOP and the database are different

 Adpatch Fails : 'The Applications System names per the APPL_TOP and the database are different.' (Doc ID 213339.1)

Unable to Patch a Cloned Environment Because of Wrong Applications System Names (Doc ID 202483.1)

Error:-

We recently faced an issue after development instance cloned from production.

adadmin Failed with the below errors.

The Applications System names per the APPL_TOP and the database

are different.

    Applications System name per the APPL_TOP: uaterp

    Applications System name per the database: THRMS

If you continue, the Applications System name per the APPL_TOP will

be ignored.

Do you wish to continue [No] ?

Solution:-

SQL> desc FND_PRODUCT_GROUPS
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PRODUCT_GROUP_ID                          NOT NULL NUMBER(15)
LAST_UPDATE_DATE                          NOT NULL DATE
LAST_UPDATED_BY                           NOT NULL NUMBER(15)
CREATION_DATE                             NOT NULL DATE
CREATED_BY                                NOT NULL NUMBER(15)
LAST_UPDATE_LOGIN                         NOT NULL NUMBER(15)
PRODUCT_GROUP_NAME                        NOT NULL VARCHAR2(60)
RELEASE_NAME                              NOT NULL VARCHAR2(50)
PRODUCT_GROUP_TYPE                        NOT NULL VARCHAR2(30)
ARGUMENT1                                          VARCHAR2(30)
MULTI_ORG_FLAG                                     VARCHAR2(1)
MULTI_LINGUAL_FLAG                                 VARCHAR2(1)
MULTI_CURRENCY_FLAG                                VARCHAR2(1)
APPLICATIONS_SYSTEM_NAME                  NOT NULL VARCHAR2(30)
IS_NEW_TS_MODE                                     VARCHAR2(1)
ARU_RELEASE_NAME                                   VARCHAR2(50)

SQL> select APPLICATIONS_SYSTEM_NAME from FND_PRODUCT_GROUPS;
APPLICATIONS_SYSTEM_NAME
------------------------------
THRMS

SQL> update FND_PRODUCT_GROUPS
  2  set APPLICATIONS_SYSTEM_NAME='uaterp'
  3  ;
1 row updated.

SQL> commit;

Commit complete.

SQL> select APPLICATIONS_SYSTEM_NAME from FND_PRODUCT_GROUPS;
APPLICATIONS_SYSTEM_NAME
------------------------------
uaterp
SQL>

oracle.apps.fnd.framework.OAException: No data found for region

 Issue-

When user  trying to login in application he is not able to see the home page and there is an error.

oracle.apps.fnd.framework.OAException: No data found for region (/oracle/apps/fnd/framework/navigate/webui/NewHomePG).         at oracle.apps.fnd.framework.webui.JRAD2AKMapper.loadPage(JRAD2AKMapper.java:784)         at oracle.apps.fnd.framework.webui.OAPageBean.initializeCustomizationsMDS(OAPageBean.java:7394)         at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(OAPageBean.java:2525)         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:2066)         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:585)         at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:473)         at _pages.__oa._jspService(__oa.java:233)         at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)         at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)         at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)         at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)         at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)         at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)         at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:149)         at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)         at weblogic.servlet.internal.RequestDispatcherImpl.invokeServlet(RequestDispatcherImpl.java:527)         at weblogic.servlet.internal.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:253)         at weblogic.servlet.jsp.PageContextImpl.forward(PageContextImpl.java:159)         at _pages.__oa._jspService(__oa.java:246)         Solution:- 1- check Personalization.  SELECT  jdr_mds_internal.getDocumentName(path_docid) as component       ,       path_type       ,       path_seq       ,       creation_date       ,       last_update_date       FROM  ( SELECT  path_docid               ,       path_type               ,       path_seq               ,       creation_date               ,       last_update_date               FROM    jdr_paths               START WITH path_owner_docid = jdr_mds_internal.getDocumentID(('/'))               CONNECT BY PRIOR path_docid = path_owner_docid) paths       WHERE ( path_type = 'DOCUMENT' AND path_seq = -1)         OR  ( path_type = 'PACKAGE'  AND path_seq = 0)         OR  ( path_type = 'PACKAGE'  AND path_seq = -1           AND NOT EXISTS (                 SELECT * FROM jdr_paths                 WHERE path_owner_docid = paths.path_docid))                 order by last_update_date desc; 2- Delete Personalization from user level, exec jdr_utils.deletedocument('/oracle/apps/fnd/framework/navigate/webui/customizations/user/7071/NewHomePG'); 3- User able to login

x

ORACLE HANG ANALYZE

 Below given are the steps to generate Hanganalyze and Systemstate 

Hanganalyze: 

===================== 

sqlplus '/ as sysdba' 

oradebug setmypid; 

oradebug unlimit; 

oradebug hanganalyze 3; 

-- Wait upto 1 minute before getting the second hanganalyze 

oradebug hanganalyze 3; 

-- Wait upto 1 minute before getting the second hanganalyze 

oradebug hanganalyze 3; 

oradebug tracefile_name; 

exit 

Systemstate Dump: 

================ 

sqlplus '/ as sysdba' 

oradebug setmypid; 

oradebug unlimit; 

oradebug dump systemstate 258; 

-- Wait upto 1 minute before getting the second system state 

oradebug dump systemstate 258; 

-- Wait upto 1 minute before getting the second system state 

oradebug dump systemstate 258; 

oradebug tracefile_name; 

exit 

Reset a Purchase Order From In Process To Incomplete

 How to Set to po incomplete - EBS.

SQL> @poxrespo.sql

Please enter the PO number to reset : 563407

Please enter the organization id to which the PO belongs (Default NULL) : 81

Do you want to delete the action history since the last approval ? (Y/N) Y

Processing STANDARD PO Number: 563407

......................................

Updating PO Status..

Done Approval Processing.

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[ebs@SOUL sql]$

ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version

 

Control file version 11.2.0.1.0 incompatible with ORACLE version 10.2.0.3.0

 

SQL> startup;

ORACLE instance started.

Total System Global Area 2004340736 bytes

Fixed Size                  2256544 bytes

Variable Size            1006633312 bytes

Database Buffers          973078528 bytes

Redo Buffers               22372352 bytes

ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version

10.2.0.0.0

ORA-00202: control file: 'D:\ORACLE\GPQ\ORIGLOGA\CNTRL\CNTRLGPQ.DBF'

SQL> show parameter compatible

NAME                                 TYPE

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

VALUE

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

compatible                           string

10.2.0

SQL> alter system set compatible='11.2.0.0.0' scope=spfile;

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2004340736 bytes

Fixed Size                  2256544 bytes

Variable Size            1006633312 bytes

Database Buffers          973078528 bytes

Redo Buffers               22372352 bytes

Database mounted.

 

SQL> show parameter compatible

NAME                                 TYPE        VALUE

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

compatible                           string      11.2.0.0.0

SQL>

Drop TEMP File in the Oracle Database

select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

 select * from v$tempfile';

SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME                                                                                                                                                               TABLESPACE_NAME

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

/u01/database/data/temp01.dbf                                                                                                                                           TEMP1

/u01/database/data/temp02.dbf                                                                                                                                           TEMP2

/u01/database/data/temp0001.dbf                                                                                                                                         TEMP1

/u01/database/data/temp002.dbf                                                                                                                                          TEMP1

SQL>    alter database tempfile '/u01/database/data/temp002.dbf' drop including datafiles;

Database altered.

SQL>

SQL>

SQL>

SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME                                                                                                                                                               TABLESPACE_NAME

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

/u01/database/data/temp01.dbf                                                                                                                                           TEMP1

/u01/database/data/temp02.dbf                                                                                                                                           TEMP2

/u01/database/data/temp0001.dbf                                                                                                                                         TEMP1

SQL>

UTL_HTTP and SSL (HTTPS) using Oracle Wallets

 

1- Install Wallets in Oracle

In order to make connections to a secured resource, we need to get the necessary certificate.

Using the browser, go to the URL you are attempting to access from PL/SQL.

In this case "https://allotaxi.com.lb/".

Click the lock icon in the URL bar to display the certificate menu and click on the "Connection" tab.

Click the "Certificate information" link and click the "Certification Path" tab on the resulting dialog.

For the root node in the "Certification path", highlight the node and click the "View Certificate" button.

On the resulting dialog, click the "Details" tab and click the "Copy to File..." button to save the certificate information.

On the resulting wizard, do the following.

●     Click the "Next" button on the welcome screen.

●     Select the "wallet (.CER)" option and click the "Next" button. Other formats work, but I've found this to be the most consistent.

●     Enter suitable file name and click the "Next" button.

●     Click the "Finish" button.

2- Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/12c/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/12c/wallet -pwd WalletPasswd123 -auto_login

Test Secured Connection.

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

3- Authentication

If you are accessing a site that requires authentication, you will need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

If the page uses digest authentication, then you will need to will need to install the digest_auth_api package.

Test code.

SET SERVEROUTPUT ON

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password', '/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

4- Firewall port 443 needs to be opened for outbound.

Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer

 

Customer Recommended How to Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer Using Extended Log Format (Doc ID 1602379.1)

1- Log into your WLS machine, and run the $WL_HOME/server/bin/setWLSEnv.sh script to initialize the necessary environment variables, such as location of java, and javac (compiler) that you will need.

In a text editor, create a file called, for example, "GWXFF.java" with the following:

[oracle@test~]$ cd /u01/install/PANSOFT/abd/scripts/
[oracle@test~]$ vi GWXFF.java
[oracle@test scripts]$ cat GWXFF.java

import weblogic.servlet.logging.CustomELFLogger;
import weblogic.servlet.logging.FormatStringBuffer;
import weblogic.servlet.logging.HttpAccountingInfo;
 
/* This example outputs the X-Forwarded-For
 * field into a custom field called GWXFF
 * */
public class GWXFF implements CustomELFLogger{
 public void logField(HttpAccountingInfo metrics, FormatStringBuffer buff) {
  buff.appendValueOrDash(metrics.getHeader("X-Forwarded-For"));
 }
}

2-Save this file. Notice the relation between the class name and the name of the file.

[oracle@test bin]$ cd /u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin

[oracle@test bin]$ . setWLSEnv.sh

CLASSPATH=/u01/install/APPS/fs2/FMW_Home/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/lib/tools.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic_sp.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/u01/install/APPS/fs2/FMW_Home/modules/features/weblogic.server.modules_10.3.6.0.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/webservices.jar:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/u01/install/APPS/fs2/FMW_Home/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/dt.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/tools.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/lib/rt.jar:/u01/install/APPS/fs2/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/install/APPS/fs2/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/install/APPS/fs2/EBSapps/comn/java/classes:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java/frmall.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/jlib/ewt3.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar

 

PATH=/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/bin:/u01/install/APPS/fs_ne/EBSapps/appl/ad/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/inst/apps/QA_ebsqaapp01/admin/scripts:/u01/install/APPS/fs2/FMW_Home/webtier/perl/bin:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/u01/install/APPS/fs2/EBSapps/appl/fnd/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/appl/ad/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/bin::/u01/install/APPS/fs2/EBSapps/10.1.2/OPatch:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/usr/bin:/usr/sbin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

 

Your environment has been set.

3- Compile this file as follows

[oracle@test scripts]$ javac GWXFF.java

You will now have a file called GWXFF.class.

[oracle@test scripts]$ ll

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

4- Next, create a jar package out of the GWXFF.class file.

[oracle@test scripts]$ jar cvf GWXFF.jar GWXFF.class

added manifest

adding: GWXFF.class(in = 675) (out= 370)(deflated 45%)

[oracle@test scripts]$ ll

total 28

-rwxr-xr-x. 1 oracle oinstall 4866 May 29 09:58 cmclean.sql

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  825 Jun 11 13:49 GWXFF.jar

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

-rwxr-xr-x. 1 oracle oinstall  266 May 29 10:12 startprimary.sh

-rwxr-xr-x. 1 oracle oinstall  445 May 29 10:09 stopprimary.sh

Place this jar file at a location that is visible to the managed servers.

/u01/install/PANSOFT/abd/scripts/GWXFF.jar

5- Add the Custom jar File to the CLASSPATH.

If you are starting managed servers from the admin console, and you do NOT have start scripts enabled for Node Manager, follow these steps:

1. Log into the Admin Console as the WebLogic Admin user. Navigate to Environment -> Servers -> Click on name of managed server. Then select Configuration and Server Start.
2. Under "Class Path", add the absolute path to the GWXFF.jar file from above.
3. Click Save, you will be prompted to restart the server. But do not restart it yet.

If you are starting managed servers separately from the admin console or have start scripts enabled, add the absolute path to the GWXFF.jar to the classpath in the managed server start script.

6- Configuring Extended Log Format.

1. While still logged into the Admin console, browse to Servers -> Managed Server -> Logging -> HTTP -> Advanced and select Format.
2. Change from "Common" to "Extended."
3. In the Extended Logging Fields, add "x-GWXFF" to the fields already there.
4. Click save.
5. You may be informed that these changes do not require a restart, when in fact they do.
6. Restart the managed server.

7- Bounce the managed server to which changes have made and kindly test if Ip tracing is enabled.