ISG Issue faced in EBS R12.2

Issue 1:  When clicking on view WADL http 500 is returned

• cd $COMMON_TOP/java/lib
• Check  isgagent.zip  exists. If yes, give it full permissions (777).
• Recompile jar files using adadmin.
• Bounce the Oracle Application Framework server and retest

Issue 2: Modifying Existing REST Service, not reflected.

• cd $INST_TOP/soa/isgagent.properties   
• ##Add the below parameter
• ISG_CLEAR_JPUB_CACHE=YES
• Bounce all the appsTier services

Issue 3: ISG REST deployment - failed to be authenticated.

• Check the UMX|APPS_SCHEMA_CONNECT role has been assigned to ASADMIN user:

select role_name from wf_user_roles where user_name='ASADMIN';

select role_name from wf_user_role_assignments where user_name='ASADMIN';

If missing, run "Workflow Directory Services User/Role Validation" Program 

• Ensure you can log into EBS front end using ASADMIN user.
• Ensure the the following SQL returns the proper value:

select fnd_vault.get('ISG','ASADMIN') from dual;

• If this value is incorrect / not the same a login password run & then re-check this is correct password:

@$FND_TOP/sql/afvltput.sql ISG ASADMIN <password>

• Ensure the following SQL returns a 'Y".

select fnd_web_sec.validate_login('ASADMIN','<password>') from dual;

Error - irep_parser.pl - Can't locate Class/MethodMaker.pm

$ cd $APPL_TOP_NE

$ mkdir perl

Download patch 13602850. Unzip the file & it contains the following perl modules:

   Compress-Raw-Zlib-2.009

   Compress-Zlib-2.009

   Class-MethodMaker-1.12

Compress-Raw-Zlib-2.009]$ perl Makefile.PL
Compress-Raw-Zlib-2.009]$ make
Compress-Raw-Zlib-2.009]$ make install

Compress-Zlib-2.009]$ perl Makefile.PL
Compress-Zlib-2.009]$ make
Compress-Zlib-2.009]$ make install

Class-MethodMaker-1.12]$ perl Makefile.PL
Class-MethodMaker-1.12]$ make
Class-MethodMaker-1.12]$ make install

Reference: 1529412.1

Steps to create APPSRO schema in Oracle EBS R12.2

sqlplus / as sysdba
create user appsro identified by <pwd>;
grant connect to appsro;
grant create synonym to appsro;
commit;

sqlplus apps/apps
set head off
set newpage none
set linesize 150
set pagesize 9999
spool create_synonyms.sql
select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE');
spool off

set head off
set newpage none
set linesize 150
set pagesize 9999
spool grant_select.sql
select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE');
spool off
exit

sqlplus / as sysdba
@grant_select.sql
exit;

sqlplus SYSTEM/<PWD>
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

sqlplus appsro/<pwd>
@create_synonyms.sql
exit;

-- After completion of above steps validate the invalid objects count.

Maintenance Scripts - OS Scripts/Commands

  ### Script to extract files changed last 1 Day

############ Script STARTS Here ##############
echo " "
echo " system_time $HOSTNAME "
echo "++++++++++++++++++++++++++++++"
date
echo "-----------------------------------------------------------"
echo " users currently logged in terminal "
echo "++++++++++++++++++++++++++++++++++++"
who
echo "-----------------------------------------------------------"
echo " File from OA_HTML "
echo "+++++++++++++++++++"
cd $OA_HTML
find . -type f -mtime -1 -ls
echo "-----------------------------------------------------------"
echo " file from common_top "
echo "++++++++++++++++++++++"
cd $COMMON_TOP
find . -type f -mtime -1 -ls
echo "-----------------------------------------------------------"
echo " files from appl_top "
echo "+++++++++++++++++++++"
cd $APPL_TOP
find . -type f -mtime -1 -ls
echo "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
############ Script ENDS Here ##############


### List File modified on Particular Date
find . -type f -newermt 2019-03-12 ! -newermt 2019-03-13 -ls
 
### List File modified from Specified Date
find . -type f -newermt 2019-03-12 -ls

### Script to compile invalid objects based on count
### It will spool the output for every run time
### Crontab Entry 
### */30 5-17 * * 0-4 $HOME/compile_object.sh > /dev/null 2>&1

############ Script STARTS Here ##############
. /u01/test/db_home/12.1.0/TEST_testdb.env

sqlplus -s / as sysdba <<EOF
set serveroutput on;
spool compile_object.log
DECLARE
invd_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO invd_count FROM dba_objects WHERE status='INVALID';
IF invd_count > 0 THEN
  dbms_output.put_line ('Invalid Object Count is :'||invd_count );
  dbms_output.put_line ('LOOP Started' );
  execute immediate 'ALTER MATERIALIZED VIEW apps.XX_OBJECT_mv COMPILE';
  utl_recomp.recomp_parallel(2);
  dbms_output.put_line ('LOOP Ended' );
END IF;
 SELECT COUNT(*) INTO invd_count FROM dba_objects WHERE status='INVALID';
 dbms_output.put_line ('Invalid Object Count is :'||invd_count );
END;
/
spool off;
EOF
############ Script ENDS Here ##############

 

### Generate tar script for run filesystem

select 'tar -cvzf /u02/EBS_BKP/R122_PROD_'|| SUBSTR(SUBSTR(PATH,0,INSTR(PATH,'/inst',1)),-4,3)
|| TO_CHAR(SYSDATE, '_DD_MON_YYYY')
||'.tar.gz '||
SUBSTR(PATH,0,INSTR(PATH,'/inst',1))||'EBSapps' tar_cmd from fnd_oam_context_files
where
NAME NOT IN ('TEMPLATE','METADATA')
and CTX_TYPE='A'
and status='S'
and text like '%<file_edition_type oa_var="s_file_edition_type">run</file_edition_type>%';


### Update the password for EBS user from backend
declare
cursor c1 is
Select * from fnd_user where user_id=8774;
begin
for cr1 in c1 loop
BEGIN
  FND_USER_PKG.UPDATEUSER(x_user_name             => cr1.user_name
                         ,x_owner                 => NULL
                         ,x_unencrypted_password  => 'welcome'
                         ,x_password_date         => SYSDATE
                         );
END;
end loop;
end;
/
commit; 

### OS Commands

$ find /backup/hot_bkp/rman_bkp -mtime +3 -name "*.log" -exec rm "{}" ";"

$ grep 34188770 *OPP* (or) find . -name "*FNDOPP*" -exec grep -H '34191620' {} + 

$ tail -5000f file_name > new_file_name.log  

$ cat /dev/null > logfile 

$ find ./test -name "*.log" -exec zip -m /home/TESTbac.zip '{}' ';'

$ zip -rm filename.zip <source_file> 

$ netstat -ntpl | grep port_no

$ lsof -i -n -P |grep <name> 

$ lsof -i :22 [22-30] {-i = protcol -P=port -u=user -a=and -p=PID +D/+d=Directory -c=command}

$ kill -9 `ps -ef | grep ora | awk '{print $2}'`

$ vi Editior change & replace >> :%s:old_hostname:new_hostname:gc

### SSL cert

# nmap --script ssl-enum-ciphers -p 443 test.orcl1.com

# curl --silent --output /dev/null --cookie-jar - 'http://test.orcl1.com:8000'

# curl -c /tmp/cookies.txt http://test.orcl1.com:8000

Oracle Standby Configuration - Quick Reference

 Primary Database Parameters:

db_unique_name = PRIM

db_recovery_file_dest_size = 200g

db_recovery_file_dest = '/u01/app/oracle/oradata/RECO_DEST'

log_archive_dest_1= 'location=USE_DB_RECOVERY_FILE_DEST'
log_archive_dest_2 = "SERVICE=STBY LGWR ASYNC=20480 

DB_UNIQUE_NAME = STBY OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30"

log_archive_config = 'dg_config=(PRIM,STBY)'
log_archive_min_succeed_dest = 1
standby_file_management = AUTO
Remote_login_passwordfile = exclusive
log_archive_format = 'PRIM_%s_%t_%r.arc'
db_file_name_convert = '/u01/app/oracle/oradata/STBY','/u01/app/oracle/oradata/PRIM'
log_file_name_convert = '/u01/app/oracle/oradata/STBY','/u01/app/oracle/oradata/PRIM'
fal_server = STBY
fal_client = PRIM

Standby Database Parameters:

db_unique_name = STBY

db_recovery_file_dest_size = 200g

db_recovery_file_dest = '/u01/app/oracle/oradata/RECO_DEST'

db_flashback_retention_target = 2880

log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST'

log_archive_dest_2 = "SERVICE=PRIM LGWR ASYNC=20480 DB_UNIQUE_NAME=PRIM OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30"

log_archive_config = 'dg_config=(STBY,PRIM)'

log_archive_min_succeed_dest = 1

standby_file_management = AUTO

Remote_login_passwordfile = exclusive

log_archive_format = 'STBY_%s_%t_%r.arc'

db_file_name_convert ='/u01/app/oracle/oradata/PRIM','/u01/app/oracle/oradata/STBY'

log_file_name_convert = '/u01/app/oracle/oradata/PRIM','/u01/app/oracle/oradata/STBY'

fal_server = PRIM

service_names = STBY

fal_client = STBY

Standy Database Using RMAN:

 

$ rman TARGET sys/PASSWD@PRIM AUXILIARY sys/PASSWD@STBY

 

RMAN> DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER nofilenamecheck;

Manage Physical Standby:

SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

SQL> select sequence#, applied, to_char(first_time, 'mm/dd/yy hh24:mi:ss') first  from v$archived_log  order by first_time;

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Convert to Snapshot Standby [READ/WRITE] Validation:

SQL> alter database recover managed standby database cancel;
SQL> alter database flashback on;
SQL> select database_role, flashback_on from v$database;
SQL> alter database convert to snapshot standby;
SQL> shutdown immediate;
SQL> startup
SQL> select database_role, flashback_on from v$database;

set linesize 150
col name format a50
col scn format 99999999
col time format a32
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/(1024*1024) storage_size_MB FROM V$RESTORE_POINT;
 (OR)

RMAN> LIST RESTORE POINT ALL;

SQL> startup mount force;
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

SQL> select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb", retention_target/60 "Hours of flback logs", flashback_size/1024/1024 "Current Flbacklog Size mb"

from v$flashback_database_log;

SQL> select oldest_flashback_scn "oldest flback SCN",
oldest_flashback_time "oldest flback time"
from v$flashback_database_log;

set lines 120
col NAME format a30
SQL> select CON_ID,CON_UID,GUID,NAME,OPEN_MODE from v$pdbs;
SQL> select DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ from PDB_SPFILE$;

Oracle EBS R12.2 with 19c Database - Overview

Oracle Database 19.3 was certified with Oracle E-Business Suite Release 12.2 on-premises.

As part of upgrading Database to 19c, we will convert EBS database to the CDB architecture with a single pluggable database (PDB).

Limitations:

• A CDB with one PDB (single tenant) is currently the only certified deployment for Oracle E-Business Suite with Database 19c. i.e. A CDB with multiple PDBs (multitenant) is not currently certified.
• A non-CDB architecture is not planned to be certified or supported for EBS with Database 19c.
• UTL_FILE_DIR database initialization parameter has been de-supported. txkCfgUtlfileDir.pl to migrate your previous UTL_FILE_DIR database initialization parameter settings to the new parameter when you upgrade your database.

Following are the new database context variables:

s_pluggable_database

s_pdb_name

s_cdb_name

s_cdb_unique_name

s_dbService

s_db_tenancy

s_cdb_tnsadmin

Environment Files: 

• Two environments are created in 19c EBS Database -- cdb & pdb respectively
• we can also set ORACLE_PDB_SID=<PDB_NAME> to connect PDB instance directly.
• PDB Environment are sourced for tasks like - autoconfig, ETCC validation. 
• CDB environments are sourced for tasks like - Oracle Home patching.

High Level Steps in EBS configuration:

Database Tier:

Database Listener should have below entry, to successfully connect PDB to generate context file.

USE_SID_AS_SERVICE_<CDBNAME>=ON

Following step creates context file and updates PDB configuration,

$ cd $ORACLE_HOME

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0.0/dbhome_1

$ cd bin

$ perl txkPostPDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/19.3.0.0/dbhome_1 -outdir=/u01/app/oracle/product/19.3.0.0/dbhome_1/appsutil/log -cdbsid=TESTCDB -pdbsid=TEST -appsuser=apps -dbport=1521 -servicetype=[dbsystem|exadatadbsystem|onpremise]

Application Tier:

Update the tnsnames.ora in below format and run autoconfig

<TWO_TASK> =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=1521))

  (CONNECT_DATA = (SERVICE_NAME=TEST)(INSTANCE_NAME=TESTCDB))

 )