Archive for the ‘Oracle Queries’ Category

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

Retrieve Forgotten Apps Password in Oracle EBS R12

February 20th, 2023, posted in Oracle Queries
Share
I am going to share steps to retrieve the password in Oracle Apps.
But no guarantee that it will work on all EBS version. It was tested on R12.2.3.
 

Steps:

 
1: log in to the database server with sys user
 sqlplus / as sysdba
 
2: Create Function to decrypt 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’;
 /
 
3 : Query for Encrypted password
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

Output

 ENCRYPTED_FOUNDATION_PASSWORD
 ——————————————————————————–  ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A
 
4: Past the Encrypted password from the above query output into the below query and execute
SELECT apps.decrypt_pin_func
(‘GUEST/ORACLE’,’ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A’)
from dual;

Output

 APPS.DECRYPT_PIN_FUNC
(‘GUEST/ORACLE’,’ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A’)
 ——————————————————————————–
 oracle123
 
5: Test apps password is working or not
 SQL> conn apps/oralce123;
Connected.
Share

How To Get Last Record From The Table In Oracle Sql

October 30th, 2022, posted in Oracle Queries
Share

select * from (
  select * from oe_order_headers_all
   order by creation_date desc
  )
 where rownum = 1;

Share