Archive for the ‘Oracle Queries’ Category

Steps to Kill Long Running Concurrent Job in R12

July 8th, 2024, posted in Oracle Queries
Share
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where request_id=2063673;

commit;

update fnd_concurrent_requests
set status_code='E', phase_code='C'
where request_id=2063673;

commit;
Share

ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP’

April 15th, 2024, posted in Oracle Queries
Share
$ sqlplus / as sysdba
alter system set undo_management=auto scope=spfile;
2) Restart the database.
SQL> Shutdown immediate;
SQL> startup
Share

ORA-27037: Unable To Obtain File Status

February 4th, 2024, posted in Oracle Queries
Share
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 12/24/2023 09:23:19
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u03/archive2/1_4134_983485279.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

This ORA-19625 errors are related with file specified as input to a copy or backup operation, or as the target for an incremental restore, could not be identified as an Oracle file. An OS-specific error accompanies this error to help pinpoint the problem.
To solve this error, Specify an different file and retry the operation.
Try to run the following commands.

RMAN> crosscheck copy of archivelog all
RMAN> crosscheck archivelog all
RMAN> resync catalog
RMAN> delete force obsolete;
RMAN> delete expired archivelog all ;

If the problem is not solved,then Files were deleted at OS level and Archive log files were deleted at OS level. To solve this error, Run the following commands.

RMAN> Change archivelog '' UNCATALOG ;

Please note the first archive log name would be present in the error message ORA-19625:
For example :-

RMAN-06059: expected archived log not found, lost of archived log
ORA-19625: error identifying file /var/opt/arch_oltp28_1_744738.arc
ORA-27037: unable to obtain file status
RMAN> Change archivelog '/var/opt/arch_oltp28_1_744738.arc' uncatalog;

Run the archive log backup command check if you still get the error
Keeping specify the archive log file name reported in ORA-19625 till backup of archive log goes fine
Or

RMAN> Change Archivelog all Uncatalog ;

Please note the above command will uncatalog the information about the Archive log from catalog database.

Share

Oracle APPS User Password From Backend

November 19th, 2023, posted in Oracle Queries
Share

Below steps to get the forgotten apps user password in oracle apps R12.

Step 1:

Connect to sys user

SQL> show user USER is “SYS”

Step 2:

Create function to know the encrypted password

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.

Step 3:

Query for password
SQL> set linesize 200 long 300
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’; ENCRYPTED_FOUNDATION_PASSWORD
—————————————————————————————————- ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391

Step 4:

Apps password using encrypted guest password

SQL> SELECT
apps.decrypt_pin_func
(‘GUEST/ORACLE’,
‘ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391’)
from dual;
APPS.DECRYPT_PIN_FUNC
(‘GUEST/ORACLE’,
‘ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391’)
———————————————————————————————————————————————
APPS

Step 5:

Test the password is working fine or not

SQL> conn apps/APPS123;
Connected.
Share

Change Values Of Decrypt Password by SQL

April 23rd, 2023, posted in Oracle Queries
Share
SQL> create user test identified by test;
User created.

SQL> grant connect,resource to test;
Grant succeeded.

SQL> select username,password from dba_users where username='TEST';
USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67

SQL> conn test/test;
Connected.

SQL> conn sys/oracle as sysdba;
Connected.

SQL> alter user test identified by newpwd;
User altered.

SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn test/newpwd;
Connected.

SQL> show user
USER is "TEST"

SQL> conn sys/oracle as sysdba;
Connected.

SQL> alter user test identified by values  '7A0F2B316C212D67';
User altered.

SQL> conn test/newpwd;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn test/test;
Connected.

SQL> show user;
USER is "TEST"

SQL>
Share