OCI - Oralce linux - warning your password will expire in 6 days - use command "chage"

 

If OS user gets the warning message for password expiry in OCI Oracle linux, then change the password expiry, following are the 2 methods

System-wide settings (via /etc/login.defs) 

PASS_MAX_DAYS: Sets the number of days a password is valid. After this period, the user is forced to change their password.

• PASS_MIN_DAYS: Sets the minimum number of days that must pass before a user can change their password, preventing them from changing it back immediately.
• PASS_WARN_AGE: Specifies the number of days before a password expires that the user receives a warning.
• LOGIN_DEFS_FILE: The path to the login.defs file, which contains default values for password aging and other user-related settings. 
• Individual user settings (via chage) 
• chage -M <days> <username>: Sets the maximum number of days a password is valid for a specific user.
• chage -m <days> <username>: Sets the minimum number of days between password changes for a specific user.
• chage -W <days> <username>: Sets the number of days before expiration that the user is warned.
• chage -E <YYYY-MM-DD> <username>: Sets an absolute expiration date for the user's password.
• chage -l <username>: Displays the current password aging information for a specific user.
• 
  
• Example: 

[opc@testserver~]$ sudo change -M 365 testuser

sudo: change: command not found

make sure that command is "chage" and not chaNge!!

[opc@testserver ~]$ sudo chage -M 365 testuser

[opc@testserver ~]$

[opc@testserver ~]$ sudo chage -l testuser

Last password change                                    : Sep 18, 2025

Password expires                                        : Sep 18, 2026

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between passwor

Why is ADOP Status Still Showing "ACTIVE" if the Patch Application in "adop phase=apply hotpatch=yes" Completed Successfully?

 

Ref: Oracle support - (Doc ID 1928798.1)

When the patch is applied with adop hotpatch=yes mode, there are no Online Patching cycle started yet -- all is on the RUN filesystem --
adop commands status indicates what potential next adop command could be run:
NOT STARTED: both adop finalize and cleanup can be run after a apply in hotpatch mode
NOT APPLICABLE: since there were no online patching cycle prepare was skipped over hence there is no point in referring to the prepare status; and cutover is not possible after hotpatch on run fs

Hence prepare and cutover status are not applicable in regards to the last adop hotpatch active status, while finalize and cleanup could be called. 
Note that prepare can also be called to start a new patching lifecycle;
Prepare is in NOT APPLICABLE status in relation to the last hotpatch apply that is still ACTIVE until a new adop online patching cycle is started where prepare will become ACTIVE and apply will turn to NOT STARTED at that time

Example:
When one applies in hotpatch mode the APPLY phase will remain active until such time as run a PREPARE during the next patching cycle.
The APPLY phase is active because you can potentially run another hotpatch.
Once one apples a hotpatch, there are no further actions required.

When needing to apply a new patch -- this time in Online Patching mode -- run the adop phase=prepare command.
At that time, adop config change detector will find that the RUN filesystem has had patches applied in hotpatch mode and will sync the PATCH filesystem as part of the prepare phase.

remove spaces from SQL script in notepad++

 

1. Go to Search -> Replace
2. Select "Regular expression" under Search mode.
3. Use ^\s* for "Find what" and leave "Replace with" blank.
4. Click Replace all

Oracle APPS DBA - SQL query to get the print jobs from users

 Use the following query

select distinct fcr.printer, fu.user_name, 

fu.description, count(*) jobs

from apps.fnd_concurrent_requests fcr, apps.fnd_user fu

where fcr.requested_by = fu.user_id

and (fcr.printer is not null and fcr.printer not in  ('noprint','LOCAL_PRINT','LOCAL','LOCAL_PRINTTO', 'LOCAL_PREVIEW'))

group by fcr.printer, fu.user_name, fu.description;

Oracle ERP R12.2 cloned environment by EBS cloud manager - users facing "an invalid redirect has been blocked" while opening forms

 Run the following query and check if the source values are still there, if source values are there, then update with the cloned environment values or unset ICX forms launcher profile on site level.

SELECT
    p.profile_option_name        short_name,
    n.user_profile_option_name   name,
    decode(v.level_id, 10001, 'Site',
                       10002, 'Application',
                       10003, 'Responsibility',
                       10004, 'User',
                       10005, 'Server', v.level_id) level_set,
    decode(to_char(v.level_id), '10001', '',
                                '10002', app.application_short_name,
                                '10003', rsp.responsibility_key,
                                '10005', svr.node_name,
                                '10006', org.name,
                                '10004', usr.user_name, 'UnDef') "CONTEXT",
    v.profile_option_value       value
FROM
    fnd_profile_options         p,
    fnd_profile_option_values   v,
    fnd_profile_options_tl      n,
    fnd_user                    usr,
    fnd_application             app,
    fnd_responsibility          rsp,
    fnd_nodes                   svr,
    hr_operating_units          org
WHERE
    p.profile_option_id = v.profile_option_id (+)
    AND p.profile_option_name = n.profile_option_name
    AND n.language = 'US'
    AND upper(p.profile_option_name) = ('ICX_FORMS_LAUNCHER')
    AND usr.user_id (+) = v.level_value
    AND rsp.application_id (+) = v.level_value_application_id
    AND rsp.responsibility_id (+) = v.level_value
    AND app.application_id (+) = v.level_value
    AND svr.node_id (+) = v.level_value
    AND org.organization_id (+) = v.level_value
ORDER BY
    user_profile_option_name,
    level_set;

can not drop database link after changing the global_name parameter

 try the following step

1- take complete database backup

2- take the backup of table sys.link$

3- delete the row where name = db link

4- check the db link

extract DDL for database links with passwords -

 follow the link

https://forums.oracle.com/ords/apexds/post/how-to-extract-ddl-for-all-database-links-7664

set lines 200
set pages 0
set feed off
set heading off
spool get_ddl/get_dblink_ddl.run.sql
SELECT 'CREATE OR REPLACE PROCEDURE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp AS '||CHR(10)||'BEGIN'||CHR(10)||
'EXECUTE IMMEDIATE '||''''||
'create '||DECODE(U.NAME,'PUBLIC','public ',' ')||'database link '||L.name
||' connect to ' || L.USERID || ' identified by values '
||''''||''''||L.PASSWORDX||''''||''''||' using '||''''||''''|| L.host ||''''||''''||''''||';'|| CHR(10)||'END dba$sp_create_dblink_tmp; '
||CHR(10)||'/'||CHR(10)||
'EXECUTE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp;'||CHR(10)||
'DROP PROCEDURE '||DECODE(U.NAME,'PUBLIC','SYSTEM',U.NAME)||'.dba$sp_create_dblink_tmp;'||CHR(10)
||CHR(10)
TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#
;
spool off

command to kill all the processes running by a specific user

 

following command can be used to kill all the processes running by oracle user, replace oracle by any user of interest.

pkill -9 -u `id -u oracle`

SQL script to find the patches applied with patch name on ERP R12.2

 use the following SQL to get the patch name applied since certain date 

SQL query:

SELECT DISTINCT e.patch_name, to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS') End_date, e.patch_type, d.patch_abstract

FROM

ad_bugs a, ad_patch_run_bugs b, ad_patch_runs c, ad_patch_drivers d , ad_applied_patches e

WHERE

a.bug_id = b.bug_id AND

b.patch_run_id = c.patch_run_id AND

c.patch_driver_id = d.patch_driver_id AND

d.applied_patch_id = e.applied_patch_id 

AND e.creation_date > to_date ('01-JAN-23')

order by 1,3 ASC;

sample output:

PATCH_NAME END_DATE             PATCH_TYPE                     PATCH_ABSTRACT

---------- -------------------- ------------------------------ --------------------

13839836   23-APR-2024 09:26:50 ONE-OFF

34766982   22-JAN-2024 10:59:05 ONE-OFF                        ISG Consolidated Patch for 12.2 (23_1_1)

                                                                                                                                                                                                                                          

Concurrent Processing - Product Information Center (PIC) (Doc ID 1304305.1)

 huge library of Oracle support documents to deep dive into Oracle concurrent managers.

Concurrent Processing - Product Information Center (PIC) (Doc ID 1304305.1)