EBS Compliance Checker

Fix data dictionary or time stamp mismatch

Apply the correct Online Patching Readiness and GSCC Report Patch for the code level from which you are upgrading, if not already applied. (Follow first section of the same document for this)

Step 1

1. Run the file $AD_TOP/patch/115/sql/adzddtsfix.sql (Following the usage instructions provided in it).
2. Run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether the data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade.
b. If corruption is still present, follow Step 2 below.

Step 2

Fix Logical data dictionary corruption (missing-parent)

Follow this step only when logical data-dictionary corruption is present.
1. Connect to database as "SYSDBA". 2. Run the $AD_TOP/patch/115/sql/adzddmpfix.sql 3. Run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether the logical data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade or adop patching-cycle.
b. If corruption is still present, contact Oracle Support and log a bug.

Step 3

Follow this step only when data dictionary corruption is still present after following Step 1 above.
1. On database node, go to $ORACLE_HOME/rdbms/admin directory.
2. Run utlirp.sql (following the usage instructions provided).
3. Run utlrp.sql (following the usage instructions provided).
4. Run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether the data dictionary corruption is still present.
a. If no corruption is found, proceed with the upgrade.
b. If corruption is still present, contact Oracle Support and log a bug.

adop phase=cleanup loosing columns on a table.

When doing an adop cycle users complain that a table has lost its columns

Solution:

exec ad_zd_table.upgrade(<TABLE NAME>);



SQL> exec ad_zd_table.upgrade('MONT','MONT_EXPLODED_BOM');

SQL> exec ad_zd_table.patch('MONT','MONT_EXPLODED_BOM');

PL/SQL procedure successfully completed.

R12.2 Apache and Weblogic log file locations

R12.2 Apache and Weblogic log file locations

Apache Logs

$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OHS/EBS_web_*/*log

OPMN Log
$IAS_ORACLE_HOME/instances/*/diagnostics/logs/OPMN/opmn/*

Weblogic Logs

$IAS_ORACLE_HOME/../wlserver_10.3/common/nodemanager $EBS_DOMAIN_HOME/servers/oa*/logs/*
$EBS_DOMAIN_HOME/servers/forms*/logs/*
$EBS_DOMAIN_HOME/servers/AdminServer/logs/*
$EBS_DOMAIN_HOME/sysman/log/*

Credits to http://oracleappstechnology.blogspot.hk/2013/12/r122-apache-and-weblogic-log-file.html

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP  -contextfile=$CONTEXT_FILE -appspass=r12t1fnd  -outfile=$APPLTMP/Report_App_Inventory.html

Views relevant to Archivelog

There are several system views that can provide us with information reguarding archives, such as:

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.

Using these tables we can verify that we are infact in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

Learn more about managing archive redo logs in the Oracle Database Administrator's Guide:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10739/archredo.htm

ORA-39065: unexpected master process exception in DISPATCH during expdp

Solution:

@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Get a list of scheduled concurrent jobs

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',
'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR
HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id =
cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) =
cr.release_class_app_id

How will i know which responsibility do i need to schedule a concurrent job

 SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4

Error 500--Internal Server Error + EBS

Just to put it on a context my developer had me deploy his java file to prod. Of course being a good DBA never promote anything on PROD that you do not understand. I asked him how he did it on dev and he said that the other DBA should know. I asked the other DBA what did he do. He sent me a couple of commands that he did not understand to. So putting faith on this guy I go ahead and test in on the test environment.

I followed the steps and bounced the middle tier. There was no errors on the logs and all the steps returned success. When I was about to call it a day I tried to logon to EBS. There it is, Error 500, which by itself was useless. I tried to bounce it a couple more times, sometimes that work. Unfortunately it did not this time. I tried to retrace my steps and revert all the files that I backed up. Yes BACKUP everything!!! I cannot emphasize it more, it saved me from tough spot. Still it did not work. I was really close on cloning it from another environment.

During my research I found this.

A quick test is to use this 

http://host.domain:port/OA_HTML/jsp/fnd/aoljtest.jsp 

example 

http://rend.ecp.priv:8010/OA_HTML/jsp/fnd/aoljtest.jsp 

For my case this solved it
 cd $FND_TOP/patch/115/bin
     ojspCompile.pl --compile --flush

See this note.
R12: Troubleshooting 500 Internal Server Error in Oracle E-Business suite (Doc ID 813523.1)

Login attempts on EBS

/*
Copyright (c) 2015 Oracle Corporation  - All rights reserved.

Query showing unsuccessful logins for local users in E-Business Suite
*/

set pagesize 1000
set linesize 200

select u.user_name,
ful.user_id,
to_char(attempt_time,'DD-MON-RRRR HH24:MI:SS') attempt_time
from fnd_unsuccessful_logins ful,
fnd_user u
where ful.user_id = u.user_id (+)
order by attempt_time;

Relevant Tables for ORACLE dataguard

View	Database	Description
DBA_LOGSTDBY_EVENTS	Logical only	Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.
DBA_LOGSTDBY_HISTORY	Logical only	Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)
DBA_LOGSTDBY_LOG	Logical only	Shows the log files registered for logical standby databases.
DBA_LOGSTDBY_NOT_UNIQUE	Logical only	Identifies tables that have no primary and no non-null unique indexes.
DBA_LOGSTDBY_PARAMETERS	Logical only	Contains the list of parameters used by SQL Apply.
DBA_LOGSTDBY_SKIP	Logical only	Lists the tables that will be skipped by SQL Apply.
DBA_LOGSTDBY_SKIP_TRANSACTION	Logical only	Lists the skip settings chosen.
DBA_LOGSTDBY_UNSUPPORTED	Logical only	Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.
V$ARCHIVE_DEST	Primary, physical, and logical	Describes all of the destinations in the Data Guard configuration, including each destination's current value, mode, and status. Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_DEST_STATUS	Primary, physical, and logical	Displays runtime and configuration information for the archived redo log destinations. Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP	Physical and logical	Displays information to help you identify a gap in the archived redo log files.
V$ARCHIVED_LOG	Primary, physical, and logical	Displays archive redo log information from the control file, including names of the archived redo log files.
V$DATABASE	Primary, physical, and logical	Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).
V$DATABASE_INCARNATION	Primary, physical, and logical	Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGSoption. Records about the current and the previous incarnation are also contained in the V$DATABASEview.
V$DATAFILE	Primary, physical, and logical	Provides datafile information from the control file.
V$DATAGUARD_CONFIG	Primary, physical, and logical	Lists the unique database names defined with the DB_UNIQUE_NAMEand LOG_ARCHIVE_CONFIGinitialization parameters.
V$DATAGUARD_STATS	Primary, physical, and logical	Displays how much redo data generated by the primary database is not yet available on the standby database, showing how much redo data could be lost if the primary database were to crash at the time you queried this view. You can query this view on any instance of a standby database in a Data Guard configuration. If you query this view on a primary database, then the column values are cleared.
V$DATAGUARD_STATUS	Primary, physical, and logical	Displays and records events that would typically be triggered by any message to the alert log or server process trace files.
V$LOG	Primary, physical, and logical	Contains log file information from the online redo log files.
V$LOGFILE	Primary, physical, and logical	Contains information about the online redo log files and standby redo log files.
V$LOG_HISTORY	Primary, physical, and logical	Contains log history information from the control file.
V$LOGSTDBY_PROCESS	Logical only	Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.
V$LOGSTDBY_PROGRESS	Logical only	Displays the progress of SQL Apply on the logical standby database.
V$LOGSTDBY_STATE	Logical only	Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS views about the running state of SQL Apply and the logical standby database.
V$LOGSTDBY_STATS	Logical only	Displays LogMiner statistics, current state, and status information for a logical standby database during SQL Apply. If SQL Apply is not running, the values for the statistics are cleared.
V$LOGSTDBY_TRANSACTION	Logical only	Displays information about all active transactions being processed by SQL Apply on the logical standby database.
V$MANAGED_STANDBY	Physical only	Displays current status information for Oracle database processes related to physical standby databases. Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_LOG	Physical and logical	Contains log file information from the standby redo log files.