HOW TO FIND APPS PASSWORD – R12

Simple way to find out apps password in R12, Please review below for the same

1) Connect to SYSTEM or SYS USER
2) Create Function for to decrypt the encrypted password
3) Query for PASSWORD
4) Query for decrypt the password
5) Test the apps user connection

STEP 1:

[oracle@localhost]$sqlplus system/system_password
[oracle@localhost]$sqlplus / as sysdba

STEP 2:

$SQL>create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
/
Function created.

STEP 3:

$SQL>select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;
ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4BB250407DF1DB9EEE786F895B78FB6F260E0101
85B830B8F49C6ECC2FAF

STEP 4:

$SQL>SELECT apps.decrypt_get_pwd(‘GUEST/ORACLE’,’ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4BB250407DF1DB9EEE786F895B78FB6F260E010185B830B8F49C6ECC2FAF
‘) from dual;
APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZG342073DB4A6F66E5ABB79D0E8BD64A5D4830ED4B
——————————————————————————–
welcome

STEP 5:

$SQL> conn apps/welcome
Connected.

                                  SQL Tuning Advisor 

1. Ran the below query.

   ---->   select * from apps.table1;
              => Query takes 5 minutes;

2. Taken SQL_ID of the above query

     --->  select sql_id , sql_fulltext from v$sql where sql_fulltext like '%table1%';

3.  Running Tuning Advisor from back-end

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
prompt 'Task_Name: '
DECLARE
ret_val VARCHAR2(4000);
BEGIN
ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);

dbms_sqltune.execute_tuning_task('&&Task_name');
END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name

Place the above query in sql file and run as sysdba

SQL > @tune.sql

Task_Name: Tune_ggz303zm7fsb4

Enter value for sql_id: ggz303zm7fsb4

Enter value for time_limit: 60

PL/SQL procedure successfully completed.

RECOMMENDATIONS

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : Tune_ggz303zm7fsb4

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : INTERRUPTED

Started at         : 09/15/2016 11:17:48

Completed at       : 09/15/2016 11:18:56

O/P :

===

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

Error: ORA-13639: The current operation was interrupted because it timed out.

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

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

Schema Name: SYS

SQL ID     : ggz303zm7fsb4

SQL Text   : select count(*) from xxfmit.XXFMAR_CFORM_INVOICES_V

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

FINDINGS SECTION (2 findings)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit<=10%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

            'Tune_ggz303zm7fsb4', task_owner => 'SYS', replace => TRUE);

2- Index Finding (see explain plans section below)

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

  The execution plan of this statement can be improved by creating one or more

  indices.

  Recommendation (estimated benefit: 98.91%)

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

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index ONT.IDX$$_D1C40003 on ONT.OE_ORDER_HEADERS_ALL(TO_CHAR("ORDER_

    NUMBER"),"ORG_ID");

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

SOLUTION :

=========

I have created an index as per recommendation see below.

create index ONT.IDX$$_D1C40003 on ONT.OE_ORDER_HEADERS_ALL(TO_CHAR("ORDER_

    NUMBER"),"ORG_ID");

Now the query ran time reduced to 2 Minutes.

:)

Steps to Recover dropped table from Recycle-Bin in Oracle

Scenario :

SQL> select count(*) from APPS.FND_EXECUTABLES;

select count(*) from APPS.FND_EXECUTABLES

                                 *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

Solution :

Whenever the object is dropped the object will be moved to recyclebin.

In our case we accidentally dropped fnd_executable .

Below steps shows how we recovered the dropped table

1. drop table applsys.fnd_executable;

     Table dropped

 When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts that may arise in the following circumstances:

• A user drops a table, creates another with the same name, then drops the second table.
• Two users have tables with the same name, and both users drop their tables.

e.g : BIN$$globalUID$version

• globalUID is a globally unique, 24 character long identifier generated for the object.
• version is a version number assigned by the database

2. 

SQL> show recyclebin;

ORIGINAL NAME                  RECYCLEBIN NAME                         TYPE         DROP TIME

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

FND_EXECUTABLES    BIN$OJ50JJ9cGO/gU6EBqMDpjw==$0   TABLE   2016-07-27:18:43:25

The database also provides two views for obtaining information about objects in the recycle bin:

USER_RECYCLEBIN               - Lets users see their own dropped objects in the recycle bin. It has                                                            a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN                  - Lets administrators see all dropped objects in the recycle bin.

3. Find whether the object can be recovered or not checking can_undrop value if it is yes we can

SQL > select owner, object_name, original_name, operation, type, can_undrop, can_purge, droptime from dba_recyclebin where original_name='FND_EXECUTABLES';

4. Performing Flash back to recover table

SQL> flashback table applsys.FND_EXECUTABLES to before drop;

Flashback complete.

5.  Check now

SQL > select count(*) from APPS.FND_EXECUTABLES;

  COUNT(*)

----------

      9400

Table Recovered Successfully

For more Info https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

Creating APPS Readonly Access schema for Oracle Application Users

step 1: Connect as sysdba and create the database user to be used for apps read only schema

bash $ sqlplus "/ as sysdba"

SQL > create user apps_readonly identified by apps_readonly default tablespace APPS_TS_MEDIA;

SQL> grant connect, resource to apps_readonly;

SQL> grant create synonym to apps_readonly;

SQL> exit;


step 2: Connect as APPS user and run the SQL commands:

bash $ sqlplus apps/apps

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

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','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_select.sql

SQL> select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to apps_readonly;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> exit;


step 3:

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

step 4:  connect as apps_readonly

bash $ sqlplus apps_readonly/apps_readonly

SQL> @create_synonyms.sql


SQL> exit;

Now users can use “apps_readonly” schema to have the read only access to Applications Data

Recover Datafile from Hot Backup - User Management - Part 2

Recover Datafile from Hot Backup - User Management - Part 2

Well,

Now I will make the recover in a different scenario.

I made the hot backup, my database is in Archive Log Mode and after the backup I had many DML(insert,update,delete) statements and I lost a non-critical datafile and my database must be set as online ASAP(As Soon As Possible).

Well how to make the hot backup using User Management check the Part 1.

Here I have the Tablespace TSCUSTOM with datafile CUSTOM.DBF

My user papa made many inserts in this tablespace that is his default tablespace and after database restart my datafile got crash, but I have 100 users needing to insert data in tablespace USERS so I can't let my database with downtime, so what I will do is:

SQL> startup
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  1220748 bytes
Variable Size             176164724 bytes
Database Buffers          411041792 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/customdb/custom.dbf'

SQL> alter database datafile '/u01/app/oracle/oradata/customdb/custom.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

Now the Users can use our database.

So Let's recover our datafile 6, but first let's see what is the problem with him.

SQL> select name,error from v$datafile join v$recover_file using(file#);

NAME    ERROR
--------------------
/u01/app/oracle/oradata/customdb/custom.dbf
FILE NOT FOUND

Let's restore the file:

[oracle@test coldbkp]$ cp custom.dbf /u01/app/oracle/oradata/customdb/

SQL> recover datafile 6;
Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> conn custom/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

Done.

Query to find users have same responsibilty from Backend

select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME

from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp

where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')

and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'

and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'

and grp.responsibility_id = res.responsibility_id

and grp.user_id = usr.user_id;

Query to find users all responsibilty from Backend

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER('ARUN')  -- <change it>

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

 ORDER BY frt.responsibility_name;

Steps to Start and Shutdown Standby :

If the automatic recovery mode is enabled use,

STARTUP :
========

SQL>startup mount

SQL> alter database mount standby database;

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

SHUTDOWN :
=========
SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate;

EBS R12 Login Page Is Not Coming Up -

 java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE 

E-Business Suite (EBS) Production Instance Login page is not coming up, Instead a blank page is being displayed.

CAUSE :

The root cause of the issue is with GUEST User and Password not in sync.

Running the below query, confirmed that GUEST user and password was not in sync

select fnd_web_sec.validate_password('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_PASSWORD('GUEST','ORACLE')
--------------------------------------------------------------------------------
N

Also, confirmed with the below error on the oacore log

javax.servlet.ServletException: java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE

SOLUTION :

Please perform the below steps

1. Shutdown the EBS services.

2. The only supported way to change the Guest user password is to update the context variable s_guest_pass and run AutoConfig, which runs the AdminAppServer utility internally.

3. Run autoconfig on DB Node and then application node.

4. Execute the below sql again:

select fnd_web_sec.validate_password('GUEST','ORACLE') from dual;

Check whether query output is showing Y.

If no,  Please check whether the below error is seen on autoconfig log:

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE - Password was not changed, this point to the DB parameter JAVA_JIT_ENABLED which is set as TRUE.

5. On 11g DB you need to have the below settings for EBS specifically, run the below sql

alter system set JAVA_JIT_ENABLED= FALSE scope = both;

6. Follow the steps 2 and 3 again.

7. Run the below command:

perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

7. Check whether the compile completed successfully.

8. Restart the application services.

9. Retest the issue.

EBS R12 Login Page Is Not Coming Up - java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE (Doc ID 1673030.1)

SMTP Mail Check to identify its triggering Mail or not :

There are 2 methods we can check SMTP.

1.Run the below procedure from SQL Prompt 

DECLARE

  v_From      VARCHAR2(80) := 'test@abc.com';

  v_Recipient VARCHAR2(80) := 'user@abc.com';

  v_Subject   VARCHAR2(80) := 'test subject';

  v_Mail_Host VARCHAR2(30) := '**.**.**.**';  --(SMTP MAIL SERVER IP)

  v_Mail_Conn utl_smtp.Connection;

  crlf        VARCHAR2(2)  := chr(13)||chr(10);

BEGIN

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

utl_smtp.Data(v_Mail_Conn,

   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||

   'From: '   || v_From || crlf ||

   'Subject: '|| v_Subject || crlf ||

   'To: '     || v_Recipient || crlf ||

   crlf ||

   'some message text'|| crlf ||      -- Message body

   'more message text'|| crlf

);

utl_smtp.Quit(v_mail_conn);

EXCEPTION

WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

END;

2. Below method using telnet

telnet **.**.**.***  **                               -- ( telnet IP port)

EHLO ***.**.7.16                    ( Server IP)

MAIL FROM: test@abc.com

RCPT TO: user@abc.com

DATA

Test mail.

Find The Maximum run time of a concurrent request in a 

particular day using FND History Table :

select DESCRIPTION, request_id, request_date, phase_code, status_code, requested_start_date, 
actual_start_date, actual_completion_date,ROUND(((nvl(actual_completion_date,sysdate) -actual_start_date) * 1440)/60,2)   "Runtime (in hours)"  ,
argument_text from apps.fnd_conc_req_history 
where 
DESCRIPTION in ('&program_name')
and actual_start_date between to_date('21-04-2015 00:00:00','DD-MM-YYYY HH24:MI:SS')
and to_date('22-04-2015 23:59:59','DD-MM-YYYY HH24:MI:SS')
order by request_date desc


Note : Change the Actual start date and to_date based on your requirement

                                  Dbconsole - Orainventory Error Manual Fix 

 

$ emca -repos create

STARTED EMCA at Apr 6, 2016 6:10:46 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:

Database SID: TEST

Listener port number: 1523

Password for SYS user:

Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 6, 2016 6:11:30 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /testdb/oracle/TEST/db/tech_st/11.2.0.3/cfgtoollogs/emca/TEST/emca_2016_04_06_18_10_46.log.

Apr 6, 2016 6:11:30 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: ORA-01031: insufficient privileges

Apr 6, 2016 6:11:30 PM oracle.sysman.emcp.EMConfig perform

SEVERE:

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

Some of the possible reasons may be:

1) Listener port 1523 provided is incorrect. Provide the correct port.

2) Listener is not up. Start the Listener.

3) Database service TEST is not registered with listener. Register the database service.

4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.

5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.

6) /etc/hosts does not have correct entry for hostname.

Refer to the log file at /testdb/oracle/TEST/db/tech_st/11.2.0.3/cfgtoollogs/emca/TEST/emca_2016_04_06_18_10_46.log for more details.

Could not complete the configuration. Refer to the log file at /testdb/oracle/TEST/db/tech_st/11.2.0.3/cfgtoollogs/emca/TEST/emca_2016_04_06_18_10_46.log for more details

ERROR in logfile :

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

Apr 6, 2016 6:10:46 PM oracle.sysman.emcp.util.OUIInventoryUtil getOUILoc

CONFIG: Error accessing inventory.

oracle.sysman.oii.oiii.OiiiInventoryDoesNotExistException: The inventory pointed at location /app04/oracle/DEVA/apps/tech_st/10.1.2/oraInventory is not valid

        at oracle.sysman.oii.oiii.OiiiInstallAreaControl.initAreaControl(OiiiInstallAreaControl.java:1910)

        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:301)

        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:240)

        at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:189)

        at oracle.sysman.emcp.util.OUIInventoryUtil.getOUILoc(OUIInventoryUtil.java:135)

        at oracle.sysman.emcp.util.ClusterUtil.isHASInstalled(ClusterUtil.java:279)

        at oracle.sysman.emcp.EMConfig.<clinit>(EMConfig.java:159)

        at java.lang.J9VMInternals.initializeImpl(Native Method)

        at java.lang.J9VMInternals.initialize(J9VMInternals.java:196)

        at oracle.sysman.emcp.EMConfigAssistant.<clinit>(EMConfigAssistant.java:211)

        at java.lang.J9VMInternals.initializeImpl(Native Method)

        at java.lang.J9VMInternals.initialize(J9VMInternals.java:196)

Apr 6, 2016 6:10:46 PM oracle.sysman.emcp.util.ClusterUtil isHASInstalled

CONFIG: Inventory not found

SOLUTION :

 ==========

Enter the inventory path in /etc/oraInst.loc as below

change the oraInst.loc permission to 644

# cat oraInst.loc

inventory_loc=/testdb/oracle/TEST/db/tech_st/11.2.0.3/oraInventory

inst_group=dba