Archive for the ‘Oracle Queries’ Category

« Older Entries

Obsolete Backups Vs RMAN Expired

August 30th, 2026, posted in Oracle EBS Application, Oracle Queries
Share

Obsolete Backups Vs RMAN Expired

Understand and manage expired and obsolete backups in RMAN.

Two terms that are very important when it comes to RMAN backups validation they are

  • Expired Backups
  • Obsolete Backups

Expired backups

Let’s take you trigger an RMAN backup and someone deleted backup set or backup pieces at OS level. The database CONTROFILE has the details of the backup on disk but at OS level the backup file does not exists.
We can run RMAN crosscheck command to check if backup files exists at OS level or not. If the backup files are not found, RMAN will mark it as EXPIRED.

RMAN> crosscheck backup;

DBA Action

The first action a DBA must take is to find out where the backup pieces are. Did a junior DBA move them to different location or different disk due to space issues.
If you can find the backup pieces, put them back into the original location and run crosscheck backup command again via RMAN.
If you are not able to find backup pieces, then you must clear the expired backup entries from RMAN (controlfile). Use below command to delete expired backups

RMAN> delete expired backup;

Obsolete backups

The general meaning of OBSOLETE is no longer used or required.
RMAN considers backups as OBSOLETE when they are no longer required for database recovery. This is done by one of the RMAN CONFIGURATION parameters.
For example, we set RMAN retention policy to redundancy 4. This means that if we have 5 database backups, the first one will be marked as OBSOLETE because we want to keep 4 copies of RMAN backups.
We use below command to list all the obsolete backups inside RMAN

RMAN> report obsolete;

DBA Action

IF the FRA location is full, then do not worry, RMAN is intelligent, it will overwrite the obsolete backups.
If you find obsolete backups, you can simply delete the backups (as they are no longer needed for recovery)

RMAN> delete obsolete;
Share
read more no comments

Oracle EBS Close Payable Period By Query

July 14th, 2026, posted in Oracle EBS Application, Oracle Queries
Share
oracle,oracle ebs suit,

First find the GL Ledger ID : 

select * from Gl_Ledgers_v

Now select and find the period to confirm : 

SELECT * FROM GL_PERIOD_STATUSES GLP
WHERE GLP.APPLICATION_ID = '200'/* PAYABLES */
AND GLP.SET_OF_BOOKS_ID = '2149'
AND GLP.CLOSING_STATUS = 'O'
AND GLP.PERIOD_NAME = 'SEP-22'

Once done update the period : 

UPDATE GL_PERIOD_STATUSES GLP
SET GLP.CLOSING_STATUS = 'C'
WHERE GLP.PERIOD_NAME = 'SEP-22'
AND GLP.APPLICATION_ID = '200'/* PAYABLES */
AND GLP.SET_OF_BOOKS_ID = '2149';
Share
read more no comments

ORA-32001: Write To SPFILE Requested But No SPFILE Is In Use

June 3rd, 2026, posted in Oracle Queries
Share

PROBLEM :

While altering parameter in parameter file got below error :

ALTER SYSTEM SET processes = 800 SCOPE=SPFILE;
ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use

SOLUTION :

This error comes, if the database is running with pfile instead of spfile.

1. Check whether DB is running with pfile or spfile:

SQL> show parameter pfile
NAME TYPE VALUE
------ -------- ------------------------------
spfile string

Here value is showing BLANK, Means database is running with pfile. So with pfile , if we are trying to alter any init parameter in database, it will throw error.
To fix it, create an spfile from the pfile and restart the database.

2. Create spfile from pfile ;

create spfile from pfile;

3. Check whether spfile has been created in $ORACLE_HOME/dbs location :

cd $ORACLE_HOME/dbs
ls -ltr spfile*

4. Restart the database :

shutdown immediate;
startup

When we start the database, if both pfile(init$ORACLE_SID.ora) and spfile(spfile$ORACLE_SID.ora) are present at dbs location, Then bydefault spfile will be used for db startup.

5. Check whether spfile is used or not :

show parameter pfile :

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/12chome/spfiledbaclass.ora

6. Now run alter statement :

ALTER SYSTEM SET processes = 800 SCOPE=SPFILE
System altered.
Share
read more no comments

Oracle EBS Reset Oracle Application User Password By Query

May 30th, 2026, posted in Oracle EBS Application, Oracle Queries, Uncategorized
Share

To reset an Oracle EBS application user password, use the FNDCPASS utility via command line to change it instantly, or use the System Administrator responsibility in the GUI for individual users. For APPS/APPLSYS passwords in R12.2, specialized steps involving adstpall.sh and WLS datasource updates are required

Methods to Reset Oracle EBS Passwords :

FNDCPASS Utility (Command Line – Recommended for Admins):
Use this method to change APPS or specific user passwords from the server terminal :

FNDCPASS apps/<apps_pwd> 0 Y SYSTEM/<system_pwd> USER <username> <new_password>
Example to change user “OPERATOR” password: FNDCPASS apps/apps 0 Y system/manager USER OPERATOR welcome1.

System Administrator Responsibility (GUI)
Log in to EBS as a user with System Administrator privileges (e.g., SYSADMIN).

Navigate to Security > User > Define.
Query the user, enter the new password in the Password field, and save.

PL/SQL Method (Back-end):

Use FND_USER_PKG.ChangePassword to change passwords using SQL*Plus, logged in as the APPS user.

R12.2 APPS/APPLSYS Password Change :

– Shut down services using $INST_TOP/admin/scripts/adstpall.sh.
– Run FNDCPASS to change the APPLSYS password.
– Run AutoConfig.
– Update WLS Data Source in WebLogic Console and restart services.

Share
read more no comments

Checking Tablespace Utilization In Oracle

April 4th, 2026, posted in Oracle Queries
Share

These queries provide insights into tablespace utilization, helping you identify potential storage issues :

Query 1: Check the tablespace utilization :

SELECT TABLESPACE_NAME, USED_SPACE,TABLESPACE_SIZE,USED_PERCENT FROM DBA_TABLESPACE_USAGE_METRICS ORDER BY USED_PERCENT DESC;

Query 2: Check the detail of tablespaces used, free and percentage :

Select f.tablespace_name , to_char(t.total_space,'9999,9999') "TOTAL(MB)" , to_char((t.total_space-f.free_space),'9999,9999')"USED(MB)", to_char(f.free_space,'999,999') "FREE(MB)", to_char((round(((t.total_space-f.free_space)/t.total_space)*100)),'999')||'%' PER_USED, to_char((round((f.free_space/t.total_space)*100)),'999')||'%'PER_FREE from (select tablespace_name , round (sum(blocks * ( select value/1024 from v$parameter where name='db_block_size')/1024)) free_space from dba_free_space group by tablespace_name)f , (select tablespace_name , round(sum(bytes/1048576)) total_space from dba_data_files group by tablespace_name ) t where f.tablespace_name=t.tablespace_name ;

Query 3: Give space utilization of datafiles present in tablespaces :

select tablespace_name , file_name, autoextensible , bytes/1024/1024 "USED SPACE(MB)", maxbytes/1024/1024 " MAX SIZE(MB) " from dba_data_files order by tablespace_name,file_name ;
Share
read more no comments
« Older Entries