How to Recover lost APPS Password in Oracle Applications R12?

Have you ever forgot or lost apps password in R12. In Oracle Applications 11i, It is easy to recover apps password as it is stored in application files appsweb.cfg and wdbsvr.app.
Apps password is not available in the application files in R12. We can still recover the lost password by creating a decrypt function.

Steps to retreive apps password in R12:

1.Create function Using below syntax

SQL> create or replace 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’;
4
5 /

Function created.


2. Find the encrypted password using below query

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A


3.Run the below query to decrypt the password


SQL> SELECT apps.decrypt_pin_func(‘GUEST/ORACLE’,’JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A') from dual;

APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZG761B2FB6A2C49BC802F57E55AC26637388B4EA99
——————————————————————————–
PASSWORD


4. Verify Database Connection using the password retreived


SQL> conn apps/PASSWORD
Connected.

How to enable Trace for Application User in Oracle Applications 11i/R12?

Enabling Tracing for a Application User makes diagnostics easier to analyze.Since only the sql operations performed by a specific User are Traced.


Steps to Enable Trace for Application User:

1.Login to Oracle Applications and select the System Administrator responsibility. 
Navigate to  Profile -->  System 

2.Search for Profile Option 'Initialization SQL Statement - Custom' .Also Specify the username whom you wish to enable tracing during Search.

3.
Under username column Update the below line

BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'USER_TRACE_IDENTIFER' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

Note : Level cane be 4,8,12 depends on your requirement 


Save the Changes done in Profile Option

4. Be careful while updating the Profile Option, Any incorrect Syntax may Prevent user from Logging in

5. Inform User to Re-login and Perform the Activity which needs to be traced.

6. Trace Files can be found in the directory specified by "user_dump_directory" parameter on the database server.
Use the identifier given in Profile Option to search the trace file in udump directory.

7. After the issue is reproduced and required trace files are collected for analysis, disable Tracing from Front End.
Its recommended to disable the trace immediately after the diagnostics are collected, as it will generate huge logfiles.

8. Navigate to system=> Profile form under System Administrator Responsibility.

Query for the username  and Profile Option name "Initialization SQL Statement - Custom"

Remove the values and make it blank.

Save the changes.

Discoverer Login Fails with ORA-1017

Logging to discoverer url as sysadmin gives the following error.

ORA-1017: Invalid username/password
A connection error.  
- Oracle BI Discoverer is unable to authenticate using the password provided. This can happen due to an invalid password or because the password was lost while using back, forward, or refresh in your browser. Enter the password again to continue.
- Failed to connect to database - Unable to connect to Oracle Applications database (afscpgcs) 

Cause:

APPL_SERVER_ID parameter in .dbc file of Applications MT node(applmgr) and discoverer user is not matched


$diff test1_disco.dbc test1_mt.dbc

4c4
< APPL_SERVER_ID=178***********
---
> APPL_SERVER_ID=1A***********



Solution:

Copy the <SID>.dbc from $FND_TOP/secure or $FND_SECURE of Applications Middle tier to Disoverer user

Bounce Discoverer Services

Oracle RMAN Inremental Backup Algorithm and Change Tracking File

During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN
of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the
parent, then RMAN copies the block.


Performance of incremental Backup can be improved by enabling block change tracking.When Change tracking is enabled, all the changes to datablocks are recored in change tracking file. RMAN can read the changed SCN's from change tracking file, instead of reading each and every input file.
One change tracking file is created for the entire database.
By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can specify the name of the block change tracking file by providing the location you choose.


SQL command to enable block change tracking

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
This creates change tracking file in "db_create_file_dest" directory


SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING  USING FILE '/test1/data/rman_change_track.f' REUSE;
This creates the change tracking file in the location specified.


SQL command to disable block change tracking

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
By Default,  block change tracking is disabled.
If the change tracking feature is disabled, any existing change tracking files will be deleted


SQL query to find change tracking file Details

select filename, status from v$block_change_tracking


How to Relocate the change tracking file


1.Shut down the database

SHUTDOWN IMMEDIATE

2.Using OS commands, move the change tracking file to a new location.

3.Mount the database and move the change tracking file to a location that has more space.

SQL query to rename change tracking file

ALTER DATABASE RENAME FILE   '/test1/data/rman_change_track.f' TO '/newtest1/data1/change_trk.f';

4.Open the database:

ALTER DATABASE OPEN;


If we cannot afford downtime for database shutdown, change tracking file can be relocated as below


ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new-location';

Contents of change tracking file will be lost if we use the above method.So its not a preferable method.
RMAN will have to scan the entire file until the next level 0 incremental backup is done