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.
Metalink id: =========== R12: Request Logs Contain the Message “REP-0004: Warning: Unable to open user preference file” [ID 1120529.1]
Symptoms: ============ All concurrent requests that run REPORTS are printing the following warning in the request log.
REP-0004: Warning: Unable to open user preference file.
Cause: ==========
This message occurs when the concurrent process attempts to open the Oracle Reports executable and your local preference file cannot be found or opened. This is just a warning, so the request will continue to run even if this occurs.
Solution: ===========
To resolve the warning, copy the prefs.ora file from your Reports Builder $ORACLE_HOME/tools/admin/ directory into the Applications $HOME directory.
Example :
Copy of the file prefs.ora in the directory /u01/prod/apps/tech_st/10.1.2/tools/admin to the directory /home/appprod/.
Enter Password:
REP-0004: Warning: Unable to open user preference file.
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided
Cause :
Your local (customized) Oracle Reports preference file could not be opened. This is just a warning, so the product will continue to run even if this occurs. The possible causes of this error include the following:
Case 1: The file was not found under the specified name in the specified location. Case 2: You lacked the necessary privileges to open the file. Case 3: A system error made it impossible to open the file.
Action :
You can take the following actions to rectify this error:
Case 1: Make sure the file prefs.ora is located in your “home” directory. Case 2: Make sure that you have the privileges necessary to access the file. If you don’t, change your privileges accordingly. Case 3: Consult your operating system documentation or contact your system administrator.
The preferences file referred to above is:
Windows ORACLE_HOME\CAUPREFS.ORA (user preferences) ORACLE_HOME\CAGPREFS.ORA (global preferences)
Check whether u have prefs.ora file in your application user’s home eg: prefs file should be in /home/applmg/prefs.ora if not please copy prefs.ora file from $ORACLE_HOME/tools/admin/prefs.ora to /home/applmg/prefs.ora location. Run your concurrent request/program.
To see how full tablespaces are in an Oracle database, you might need to add a datafile. If the used size of a tablespace is more than 80% of its maximum size, consider adding a datafile. The script recommends adding a datafile when the tablespace is 80% full.
Query return the result as :
Query to suggest which datafiles to add if they are 75% full.
Explain the following query columns :
Name
Name of Tablespace
Maxsize(GB)
If autoextensible of datafile is on then it will pick sum of maxsize otherwise sum of bytes column.
Used(GB)
sum of bytes column of all datafiles
Free(GB)
space free in datafiles
USED%
check with used(GB) and free(GB) column — no n
Suggestion
Give suggesion on tablespace which space is utilized above 80%
Script to check tablespace usage and suggest adding files to the database.
set colsep |
set linesize 200 pages 100 trimspool on numwidth 14
col name format a15
col owner format a15
col "Used(GB)" format a10
col "Free(GB)" format a10
col "(Used)%" format a10
col "Size(GB)" format a10
col "MaxSize(GB)" format a11
col "(Used)%" format a10
col Suggestion format a26
select Name,"MaxSize(GB)","Size(GB)","Used(GB)","Free(GB)","(Used)%",
Case when AcctoMaxSizeUsed >= 80 then 'NeedtoAddDatafile' else '' end as Suggestion
from
(
SELECT d.status "Status", d.tablespace_name as Name,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024 /1024, 0),'999999.90') "MaxSize(GB)",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'999999.90') "Size(GB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'999999.90') "Used(GB)",
TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'999999.90') "Free(GB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used)%",
TO_CHAR(NVL( (NVL(a.bytes, 0)) / a.maxbytes * 100, 0), '990.00') as AcctoMaxSizeUsed
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes, SUM( CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END ) as maxbytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
"Status", d.tablespace_name as Name,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024 /1024, 0),'999999.90') "MaxSize(GB)",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'999999.90') "Size(GB)",
TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'999999.90') "Used(GB)",
TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'999999.90') "Free(GB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used)%" ,
TO_CHAR(NVL( NVL(a.bytes, 0) / a.maxbytes * 100, 0), '990.00') as AcctoMaxSizeUsed
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes,SUM( CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END ) as maxbytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
) k order by suggestion
After getting the recommendation of adding datafile in tablespace.
Check the datafiles present in the tablespace :
select file_name from dba_Data_Files where tablespace_name = 'TEST';
Or
set line 999 pages 999 col FILE_NAME format a50 col tablespace_name format a15 Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB" from dba_data_files
If you need to add datafiles, use the following commands:
alter tablespace USERS add datafile 'D:\ORACLE11204\ORADATA\PEGA\USERS02.DBF' size 1G autoextend on next 500M maxsize 16G;
