DB : OPatch cannot find a valid oraInst.loc file to locate Central Inventory OPatch failed with error code 104

Error:

[oradev@tsebsdb01 OPatch]$ opatch lsinventory /
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /d01/oracle/DEV01/db/tech_st/11.2.0.3
Central Inventory : n/a
   from           :
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : n/a

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.

OPatch failed with error code 104
[oradev@tsebsdb01 OPatch]$

Solution:

Use -invPtrLoc with the oraInst.loc full path as highlighted below in green

[oradev@tsebsdb01 OPatch]$ opatch lsinventory -invPtrLoc /d01/oracle/DEV01/db/tech_st/11.2.0.3/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /d01/oracle/DEV01/db/tech_st/11.2.0.3
Central Inventory : /d01/oracle/DEV01/db/tech_st/11.2.0.3/admin/oui/DEV_tsebsdb01/oraInventory
   from           : /d01/oracle/DEV01/db/tech_st/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /d01/oracle/DEV01/db/tech_st/11.2.0.3/cfgtoollogs/opatch/opatch2014-11-30_10-31-30AM.log

Lsinventory Output file location : /d01/oracle/DEV01/db/tech_st/11.2.0.3/cfgtoollogs/opatch/lsinv/lsinventory2014-11-30_10-31-30AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (10) :

Patch  13366268     : applied on Thu Feb 20 15:58:22 GST 2014
Unique Patch ID:  14394336
   Created on 21 Dec 2011, 08:14:06 hrs PST8PDT
   Bugs fixed:
     13366268

EBS DB : SIMPLE RMAN BACKUP BASED DUPLICATION (SAME & DIFFERENT DIRECTORY STRUCTURE)

PROD --> Source
TEST --> Target

SAME DIRECTORY STRUCTURE:
1. Copy the ORACLE_HOME and RMAN backup to the Target server

2. Configure EBS Oracle Home using perl adcfgclone.pl dbTechStack
3. Start the database in nomount state
    sqlplus / as sysdba
    startup nomount

4. Connect to RMAN in the target instance as auxiliary
    rman auxiliary /

5. Run the below RMAN command
    duplicate database to "<target_Sid>" BACKUP LOCATION '<rman_bkP_copied_path>' nofilenamecheck;
Example:
    duplicate database to "TEST" BACKUP LOCATION '/backup/RMANBKP' nofilenamecheck;


DIFFERENT DIRECTORY STRUCTURE:
1. Copy the ORACLE_HOME and RMAN backup to the Target server

2. Configure EBS Oracle Home using perl adcfgclone.pl dbTechStack
3. Start the database in nomount state
    sqlplus / as sysdba
    startup nomount

4. Connect to RMAN in the target instance as auxiliary
    rman auxiliary /

5. Run the below RMAN command, only change between the same and different Directory structure is to add the following lines in the RMAN command

run
{
SET DB_FILE_NAME_CONVERT=’/d01/oradata/proddata’,’/d01/oradata/clonedata′
SET LOG_FILE_NAME_CONVERT=’/d01/oradata/proddata’,’/d01/oradata/clonedata′
duplicate database to "TEST" BACKUP LOCATION '/backup/RMANBKP' nofilenamecheck;
}

EBS : Check which files will be affected when you Run autoconfig in appsTier

Solution:

Run $AD_TOP/bin/adchkcfg.sh by giving full $CONTEXT_FILE path and APPS pwd, it will generate a HTML file with which file will be changed

Example:

[applprod@driebsapp01 ~]$ sh $AD_TOP/bin/adchkcfg.sh
Enter the full path to the Applications Context file:
/appl/inst/apps/TEST_driebsapp01/appl/admin/TEST_driebsapp01.xml
Enter the APPS password:

The log file for this session is located at: /appl/inst/apps/TEST_driebsapp01/admin/log/01221047/adconfig.log

AutoConfig is running in test mode and building diffs...

AutoConfig will consider the custom templates if present.
        Using CONFIG_HOME location     : /appl/inst/apps/TEST_driebsapp01
        Classpath                   : /appl/apps/apps_st/comn/java/lib/appsborg2.zip:/appl/apps/apps_st/comn/java/classes

        Using Context file          : /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/TEST_driebsapp01.xml

Context Value Management will now update the test Context file

        Updating test Context file...COMPLETED

        [ Test mode ]
        No uploading of Context File and its templates to database.

Testing templates from all of the product tops...
        Testing AD_TOP........COMPLETED
        Testing FND_TOP.......COMPLETED
        Testing ICX_TOP.......COMPLETED
        Testing MSC_TOP.......COMPLETED
        Testing IEO_TOP.......COMPLETED
        Testing BIS_TOP.......COMPLETED
        Testing AMS_TOP.......COMPLETED
        Testing CCT_TOP.......COMPLETED
        Testing WSH_TOP.......COMPLETED
        Testing CLN_TOP.......COMPLETED
        Testing OKE_TOP.......COMPLETED
        Testing OKL_TOP.......COMPLETED
        Testing OKS_TOP.......COMPLETED
        Testing CSF_TOP.......COMPLETED
        Testing IGS_TOP.......COMPLETED
        Testing IBY_TOP.......COMPLETED
        Testing JTF_TOP.......COMPLETED
        Testing MWA_TOP.......COMPLETED
        Testing CN_TOP........COMPLETED
        Testing CSI_TOP.......COMPLETED
        Testing WIP_TOP.......COMPLETED
        Testing CSE_TOP.......COMPLETED
        Testing EAM_TOP.......COMPLETED
        Testing FTE_TOP.......COMPLETED
        Testing ONT_TOP.......COMPLETED
        Testing AR_TOP........COMPLETED
        Testing AHL_TOP.......COMPLETED
        Testing OZF_TOP.......COMPLETED
        Testing IES_TOP.......COMPLETED
        Testing CSD_TOP.......COMPLETED
        Testing IGC_TOP.......COMPLETED

Differences text report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/cfgcheck.txt

        Generating Profile Option differences report...COMPLETED
Differences text report for the Database is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/ProfileReport.txt
        Generating File System differences report......COMPLETED
Differences html report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/cfgcheck.html

Differences Zip report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/ADXcfgcheck.zip

AutoConfig completed successfully.

EBS : RMAN backup fails with "ORA-19566: exceeded limit of 0 corrupt blocks for file"

Error:

Starting backup at 29-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00352 name=/d01/UAT/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/29/2014 12:25:04
ORA-19566: exceeded limit of 0 corrupt blocks for file /d01/UAT/db/apps_st/data/system09.dbf

Solution:

1. Check the free space and used space of datafile system09.dbf
 
   SET PAGESIZE 60
   SET LINESIZE 300
   COLUMN "Tablespace Name" FORMAT A22
   COLUMN "File Name" FORMAT A82
   SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
           Substr(df.file_name,1,80) "File Name",
           Round(df.bytes/1024/1024,0) "Size (M)",
           decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used Space(M)",
           decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free Space(M)",
           decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
   FROM    DBA_DATA_FILES DF,
          (SELECT file_id,
                  sum(bytes) used_bytes
           FROM dba_extents
           GROUP by file_id) E,
          (SELECT Max(bytes) free_bytes,
                  file_id
           FROM dba_free_space
           GROUP BY file_id) f
   WHERE    e.file_id (+) = df.file_id
   AND      df.file_id  = f.file_id (+)
   AND      df.file_name like '%system09.dbf'
   /

   If the space used is less than 1000 MB,

2. Resize the datafile system09.dbf
     alter database datafile '/d01/UAT/db/apps_st/data/system09.dbf' resixe 1000M;
     alter database datafile '/d01/UAT/db/apps_st/data/system09.dbf' resixe 1500M;

3. Now re-run the RMAN backup.

EBS : Restrict Specific users to access E-Business Suit R12

Solution:

1. Edit file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/custom.conf and add a list of ip addresses for the users that you want to allow access to the system. When autoconfig is run the values in the custom.conf is preserved.

Example:
<Location ~ "/OA_HTML">
Order deny,allow
Deny from all
Allow from test0.local.com
Allow from test1.local.com
Allow from test2.local.com
Allow from test3.local.com
Allow from localhost
Allow from <appsTier Full hostname>
Allow from <appTier host>
</Location>

Include localhost and your apps tier server name. Its better to use the PC name instead of IP addresses as the IP address may change

3. Restart Apache server

4.Users whose IP addresses are assigned will have access. All other users will get a forbidden error message as below when they attempt to login.

ERROR Message:

Forbidden

You don't have permission to access /OA_HTML/RF.jsp on this server.

EBS : The Workflow notification mailer is stuck and emails are not going out

Solution:

1. Log into applications as the SYSADMIN user.

2. In the upper right hand corner of the screen, click on Preferences.

3. Within the Notifications section for Email Style, select a valid option other than: Do not send me email or Disabled.

4. Now run the Synchronize WF LOCAL tables Concurrent request.

5. Retest the issue after the Synchronize WF LOCAL tables request is complete.

6. Migrate the solution as appropriate to other environments.

Reference:

Workflow Notifications are Stuck (Doc ID 577234.1)

LINUX : Sync Time between Two servers

Solution:
date --set="$(ssh user@server date)"

Eg:

[root@driebsapp01 ~]# date
Mon Feb 23 12:08:22 GST 2015
[root@driebsapp01 ~]# date --set="$(ssh root@10.101.40.10 date)"
The authenticity of host '10.101.40.10 (10.101.40.10)' can't be established.
RSA key fingerprint is 29:94:78:7d:13:92:8b:b2:4c:02:e2:76:68:56:f4:8d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.101.40.10' (RSA) to the list of known hosts.
root@10.101.40.10's password:
Mon Feb 23 11:10:30 GST 2015
[root@driebsapp01 ~]#

EBS : Gather Schema Statistics Error - ORA-20001: invalid column name or duplicate columns/column

Error:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Solution: Refrence note ID 781813.1

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
SOURCE                                254
SOURCE                                254

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LOGS'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
DECLARATION_TYPE_CODE                 254
DECLARATION_TYPE_CODE                 254

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
LINE_TYPE                             254
LINE_TYPE                             254
SOURCE                                254
SOURCE                                254
VAT_REPORT_BOX                        254
VAT_REPORT_BOX                        254

6 rows selected.

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

TABLE_NAME                     COLUMN_NAME                      COUNT(*)
------------------------------ ------------------------------ ----------
JE_FR_DAS_010                  TYPE_ENREG                              2
JE_FR_DAS_010_NEW              TYPE_ENREG                              2
JE_BE_LINE_TYPE_MAP            SOURCE                                  2
JE_BE_LOGS                     DECLARATION_TYPE_CODE                   2
JG_ZZ_SYS_FORMATS_ALL_B        JGZZ_EFT_TYPE                           2
JE_BE_VAT_REP_RULES            LINE_TYPE                               2
JE_BE_VAT_REP_RULES            SOURCE                                  2
JE_BE_VAT_REP_RULES            VAT_REPORT_BOX                          2

Run the following as APPS user queries and run the delete quries, commit  and re-run the Gather Schema Statistics Concurrent program:

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

select 'delete from FND_HISTOGRAM_COLS where (table_name, column_name) in (select hc.table_name, hc.column_name from FND_HISTOGRAM_COLS hc , dba_tab_columns tc where hc.table_name  ='''||TABLE_NAME||''' and hc.table_name= tc.table_name (+) and hc.column_name = tc.column_name (+) and tc.column_name is null);' from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

commit;

DB : Tablespace Free Size + Total Size + Datafiles in TBS + add datafile

Solution:

------ * FREE SIZE *---------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_free_space where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * TOTAL SIZE *--------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_data_files where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * DATAFILES *--------
column name format a40

SELECT name, bytes/1024/1024 FROM v$datafile WHERE ts# = (SELECT ts# FROM v$tablespace WHERE name = 'APPS_TS_SEED');

------ * Resize DATAFILE *--------

alter database datafile '/files/VIS/db/apps_st/data/reference1.dbf' resize 1024M;

------ * ADD DATAFILE *--------

alter tablespace SYSAUX add datafile '/files/VIS/db/apps_st/data/sysaux01.dbf' size 2000M;

------ * Tablespace free + used + total Size *--------
set lines 200
set pages 100
column "Tablespace" format a20
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999

select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
((df.totalspace - fs.freespace)*100/ df.totalspace)"Used Percentage",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name
order by 6;


------ * Database Used Space + DB Size + Free Size *--------

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
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 p
     from dba_free_space) free
group by free.p
/

EBS : Configuring Printer

Soultion:

1. Printer must be registered in OS level by server admin
2. Printer name and IP address need to be added in hosts file
3. System Administrator -> Install -> Printer -> Register
                 Register the printer with the Printer name on the hosts file and choose the type of driver and save it
4. Bounce the concurrent manager
5. Now test the printer by giving print from any concurrent request.