How to find sysadmin password in oracle apps r12?

How to find sysadmin password in oracle apps r12?

1)conect to database as apps schema
2)create package and package body.
3)fire select statement you will get sysadmin passwod.
************************select statement t********
SELECT Usr.User_Name,
       Usr.Description,
       XXARTO_GET_PWD.Decrypt (
          (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
                             Fnd_Web_Sec.Get_Guest_Username_Pwd,
                             Usertable.Encrypted_Foundation_Password)
                     FROM DUAL)
                     AS Apps_Password
             FROM applsys.Fnd_User Usertable
            WHERE Usertable.User_Name =
                     (SELECT SUBSTR (
                                Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                1,
                                INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                       '/')
                                - 1)
                        FROM DUAL)),
          Usr.Encrypted_User_Password)
          Password
  FROM applsys.Fnd_User Usr
 WHERE Usr.User_Name = '&User_Name';

****************************************************

oradev@ctsst $ sqlplus apps/deal123

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jul 20 04:47:24 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE OR REPLACE PACKAGE XXARTO_GET_PWD AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END XXARTO_GET_PWD; 2 3 4
5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD AS
2 FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
3 RETURN VARCHAR2 AS
4 LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
5 (java.lang.String,java.lang.String) return java.lang.String';
6 END XXARTO_GET_PWD;
7 /

Package body created.

SQL> SELECT Usr.User_Name,
2 Usr.Description,
3 XXARTO_GET_PWD.Decrypt (
4 (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
5 Fnd_Web_Sec.Get_Guest_Username_Pwd,
6 Usertable.Encrypted_Foundation_Password)
7 FROM DUAL)
8 AS Apps_Password
9 FROM applsys.Fnd_User Usertable
10 WHERE Usertable.User_Name =
11 (SELECT SUBSTR (
12 Fnd_Web_Sec.Get_Guest_Username_Pwd,
13 1,
14 INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,
15 '/')
16 - 1)
17 FROM DUAL)),
18 Usr.Encrypted_User_Password)
19 Password
20 FROM applsys.Fnd_User Usr
21 WHERE Usr.User_Name = '&User_Name';
Enter value for user_name: SYSADMIN
old 21: WHERE Usr.User_Name = '&User_Name'
new 21: WHERE Usr.User_Name = 'SYSADMIN'

USER_NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SYSADMIN
System Administrator
welcome123

Thanks for visiting my blog .Happy learning !!!!!!!!!

How to Decrypt APPS Password in 11i/R12

STEP 1 : Login instance with sqlplus:

SQL>  CREATE FUNCTION apps.decrypt_pin_func(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.

SQL>  SELECT ENCRYPTED_FOUNDATION_PASSWORD from APPS.fnd_user where  USER_NAME='GUEST';

 ENCRYPTED_FOUNDATION_PASSWORD

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

ZHBBD9066F53BC9E88B94FA8CB63E4B78E084C83D24F3A312 ->place it in below query

STEP 2 : To Get the Apps Password 

SQL>  SELECT APPS.decrypt_pin_func ('GUEST/ORACLE','ZHBBD9066F53BC9E88B94FA8CB63E4B78E084C83D24F3A312') from  dual;

 APPS.DECRYPT_PIN_FUNC ('GUEST/ORACLE','ZHBBD9066F53BC9E88B94FA8CB63E4B78E084C83D24F3A312'

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

 APPSPASS  (this is your apps user password)

[UNEXPECTED]Data dictionary corruption - timestamp mismatch

Issue: 

Log: /obin/oracle/EBSCNV2E/fs_ne/EBSapps/log/adop/55/20200719_042953/adop.log
===========================================================================

Validating configuration on node: [appsserver1].
    Log: /obin/oracle/EBSCNV2E/fs_ne/EBSapps/log/adop/55/20200719_042953/prepare/validate/appsserver
        [WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
        [WARNING]: Either some of the required entries in /etc/hosts file might be missing (e.g. localhost or hostname) OR the file /etc/hosts could not be read.

Verifying data dictionary.
    [UNEXPECTED]Data dictionary corrupted:
    [UNEXPECTED]Data dictionary corruption - timestamp mismatch
    APPS            RCV_LCM_WEB_SERVICE            V_20190112_0223 APPS            INL_INTEGRATION_GRP            V_20190112_0223 TS mismatch: 18-MAR-19 03:10:47 07-JUL-20 23:09:30
    APPS            INV_RULE_GEN_PVT               V_20191026_0338 APPS            GMI_RESERVATION_UTIL           V_20191026_0338 TS mismatch: 26-OCT-19 06:18:01 07-JUL-20 23:11:40
    APPS            GMI_MO_PURGE                   V_20191026_0338 APPS            GMI_RESERVATION_UTIL           V_20191026_0338 TS mismatch: 26-OCT-19 06:18:01 07-JUL-20 23:11:40
    APPS            GMI_PICK_RELEASE_PUB           V_20191026_0338 APPS            GMI_RESERVATION_UTIL           V_20191026_0338 TS mismatch: 26-OCT-19 06:18:01 07-JUL-20 23:11:40
    APPS            PA_PURGE_COSTING               V_20190112_0223 APPS            PAAPIMP_PKG                    V_201


SOLUTION:

SQL> !vi $AD_TOP/patch/115/sql/adzddtsfix.sql

SQL> @$AD_TOP/patch/115/sql/adzddtsfix.sql
exec dbms_utility.invalidate(5666082,NULL,0);
exec dbms_utility.invalidate(6541212,NULL,0);

SQL> !vi adzddtsfixout.sql

SQL> @adzddtsfixout.sql

PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2020-07-19 04:43:19

and Re-check  the issue, it will be done :)  Enjoy

R12.2 Upgrade: Rapidwiz Prerequisite Check Fails With Message: DB Version Check Has Failed. Not able to check the Database version. Please make sure the Database is at 11.2.0.3 or higher

R12.2 Upgrade: Rapidwiz Prerequisite Check Fails With Message: DB Version Check Has Failed. Not able to check the Database version. Please make sure the Database is at 11.2.0.3 or higher

Solution:

To resolve the database connectivity issue, perform the following steps :

1. Review the sqlnet_ifile.ora or sqlnet.ora file and confirm the following entries are present:

  SQLNET.ALLOWED_LOGON_VERSION_SERVER

2. Update the sqlnet_ifile.ora or sqlnet.ora settings for the above parameters to the lowest version level 

that is required in your environment. 

For example:

a) If the initialization parameter SEC_CASE_SENSITIVE_LOGON is set to FALSE:

  SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8

b) If SEC_CASE_SENSITIVE_LOGON is set to TRUE

  SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

After this Database connectivity issue should be resolved.

Modify the service_names parameter to include ebs_patch as shown below and retry.

SQL> sho parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      ORCL

SQL> alter system set service_names='ORCL','ebs_patch' scope=both ;

System altered.

SQL> sho parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      ORCL, ebs_patch

SP2-0734: unknown command beginning when recreate controlfile

Create backup for controlfile

SQL> alter database backup controlfile to trace as ‘d:\control\test.trc’;

Database altered.

Recreate Controlfile, Edit the test.trc file and when i try to recreate the control file , it showing SP-0734

SQL> SHUTDOWN ABORTORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
SP2-0042: unknown command “DATAFILE” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored

This happened due to blank line before the DATAFILE clause and after — STANDBY LOGFILE and  remove that space line as well as remove line — STANDBY LOGFILE and re run the script,

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE                                                             [ REMOVE THIS SPACE LINE ]

DATAFILE  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

After removed space

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
DATAFILE
  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

SQL> shutdown abortORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes

Control file created.

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

Oracle RMAN – ORA-07446: sdnfy: bad value

Check your pfile/spfile for any misconfigured parameters.  Most of the time, it got to do with your bdump, udump or cdump.
Make sure the directory specified is valid
For me, after I created the bdump and cdump folder, it was okay.

Check your pfile/spfile for any misconfigured parameters.  Most of the time, it got to do with your bdump, udump or cdump.

Make sure the directory specified is valid

For me, after I created the bdump and cdump folder, it was okay.

1. Check your pfile/spfile for any misconfigured parameters.  Most of the time, it got to do with your bdump, udump or cdump.
2. Make sure the directory specified is valid

For me, after I created the bdump and cdump folder, it was okay.

How to trace a session when user is trying to run reports remotely or from any website for a slowness issue:

I recently faced this issue where our client is seeing some slowness issues while running reports via
from some portal kinda webiste. So, bascially we jus need to figure out the SQL_ID of the sql
queries they are running.

• When the user runs the report, fetch the sid , spid from TOAD ( easy way )  or v$session

select sid,spid from v$session where status='ACTIVE' and type!='BACKGROUND'


 connect / as sysdba
 oradebug setospid 28611 – this is os pid you want to trace
 oradebug unlimit
 oradebug event 10046 trace name context forever,level 12;
 oradebug event 10053 trace name context forever,level 1;

 oradebug tracefile_name;  -- this will give you the trace file name.

 oradebug event 10046 trace name context off;
 oradebug event 10053 trace name context off;
    

Now check your trace file:

/orahome/oracle/product/11.2.0.4/diag/rdbms/wmsprd/wmsprd/trace/wmsprd_ora_28611.trc
/orahome/oracle/product/11.2.0.4/diag/rdbms/wmsprd/wmsprd/trace/wmsprd_ora_29076.trc


Check the historical executions and PHV history for the SQL ID:

select SQL_ID,SQL_PLAN_HASH_VALUE,to_char(SQL_EXEC_START,'DD-MON-RR HH24:MI')  from dba_hist_active_sess_history where SQL_ID='93zs4x57ktash'

EXPDP/IMPDP WITHOUT TABLE DATA (META DATA ONLY)

Export Schema without data (META DATA ONLY)

expdp system/s 
directory=backup_dir 
CONTENT=METADATA_ONLY 
dumpfile=testmetaddl.dmp 
schemas=testmeta 
logfile=testmetalog.$Date.log

Import DMP File:

 
impdp system/s 
directory=backup_dir 
dumpfile=testmetaddl.dmp 
sqlfile=metaddl.sql 
logfile=imp_log_of_meta.log