Force all Oracle EBS users to change their password

Reference:

Note: 414976.1 – How To Force All Applications Users To Change Their Password?

We might have the requirement to force all EBS users to change their passwords due to some requirement.

run sql Script like below or same you can achieve by submitting concurrent request

sqlplus -s APPS/apps; @AFCPEXPIRE.sql
or
Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords


Above script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program.  The user will need to create a new password upon the next login.

Oracle DB Parameter remove

Hi DBA,

Below is the simple method to remove the parameter from spfile

-- Check parameter
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2
orasmk@ebssitap:~$

-- remove parameter
alter system reset "optimizer_dynamic_sampling" scope=spfile sid='*';
alter system reset "__db_cache_size" scope=spfile sid='EBSSMK';


-- check in spfile
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
orasmk@ebssitap:~$


Below is the grep command to check the parameters in spfile

strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | egrep '_always_anti_join|_always_semi_join|_complex_view_merging|_index_join_enabled|_kks_use_mutex_pin|_new_initial_join_orders|_optimizer_cost_based_transformation|_optimizer_cost_model|_optimizer_mode_force|_optimizer_undo_changes|_or_expand_nvl_predicate|_ordered_nested_loop|_push_join_predicate|_push_join_union_view|_shared_pool_reserved_min_alloc|_sortmerge_inequality_join_off|_sqlexec_progression_cost|_table_scan_cost_plus_one|_unnest_subquery|_use_column_stats_for_function|always_anti_join|always_semi_join|background_dump_dest|core_dump_dest|db_block_buffers|db_cache_size|db_file_multiblock_read_count|DRS_START|enqueue_resources|event|hash_area_size|java_pool_size|job_queue_interval|large_pool_size|max_enabled_roles|nls_language|optimizer_dynamic_sampling|optimizer_features_enable|optimizer_index_caching|optimizer_index_cost_adj|optimizer_max_permutations|optimizer_mode|optimizer_percent_parallel|parallel_instance_group|instance_groups|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|query_rewrite_enabled|rollback_segments|row_locking|sort_area_size|sql_trace|SQL_VERSION|timed_statistics|undo_retention|undo_suppress_errors|user_dump_dest'

Enable Archive logs

Oracle DB, Enable archive log mode

# DB Param
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/archivelogs' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 'DEV_%t_%s_%r.dbf' SCOPE=SPFILE;

# DB server
sqlplus / as sysdba
ARCHIVE LOG LIST;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;

# Test Aarchive log
ALTER SYSTEM SWITCH LOGFILE

Linux Find Out Which Process Is Listening Upon a Port

Find Out Which Process Is Listening Upon a Port



1. Find process id

[root@node1 ~]# ps -ef| grep tfa
root      28422      1  0 08:37 ?        00:00:00 /bin/sh /etc/init.d/init.tfa run
root     101416      1 92 08:43 ?        00:09:55 /opt/oracle.ahf/jre/bin/java -server -Xms512m -Xmx1024m -Djava.awt.headless=true -Ddisable.checkForUpdate=true -XX:HeapDumpPath=/depot/TFA_DATA/oracle.ahf/data/node1/diag/tfa -XX:ParallelGCThreads=5 oracle.rat.tfa.TFAMain /opt/oracle.ahf/tfa
root     235621 204091  0 08:53 pts/8    00:00:00 grep tfa
[root@node1 ~]# netstat -tulpn | grep 292505



2. command to find port:
netstat -tulpn


e.g
[root@node1 ~]# netstat -tulpn | grep 250110
tcp        0      0 127.0.0.1:34149             0.0.0.0:*                   LISTEN      250110/java
tcp        0      0 10.118.50.98:5000           0.0.0.0:*                   LISTEN      250110/java

Oracle EBS Debug is on

If you have Debug is on at Site level then it will Impact the performance of the Instance, here is the script which will help you to quickly check your EBS Instance if ‘Debug’ is on (all level) of the EBS.


set linesize 160
col USER_PROFILE_OPTION_NAME for a30
col CONTEXT for a30
col VALUE for a30
col NAME for a30

SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.user_profile_option_name like '%Debug%'
AND pov.profile_option_value='Y'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value;

Oracle RMAN Backup and Restore

Convert Gigabits Per Second to Gigabytes Per Hour
http://www.kylesconverter.com/data-bandwidth/gigabits-per-second-to-gigabytes-per-hour

copy backup from ASM to FS and FS to ASM
http://select-star-from.blogspot.com/2013/08/how-to-copy-files-from-asm-to.html
https://dbaclass.com/article/copy-asm-file-from-one-server-to-another-server/


run {
BACKUP AS BACKUPSET SECTION SIZE 100G INCREMENTAL LEVEL 0 DATABASE FILESPERSET 1 TAG 'bkup_weekly_L0' plus ARCHIVELOG;
backup spfile format '/zfssa/proddb/backup1/spfile_%d_%s_%T_dbid%I.rman';
backup current controlfile format '/zfssa/proddb/backup1/Controlfile_%d_%T_dbid%I_s%s_p%p';
}

Oracle Database archive Alert Log

Hello DBAs,

Here is the simple script which can be used for clear archive log on every Sunday. I know we should make it smart but this is for easy setup :)

Steps 1: create a shell script to do this operation


#!/usr/bin/sh
# File name : alert_log_archive.sh
LOGFILE=/u01/oracle/12.1.1/diag/rdbms/dev/DEV/trace/alert_DEV.log
DDATE=`date +"%Y%m%d"`
cp $LOGFILE $LOGFILE.$DDATE
cat /dev/null > $LOGFILE
touch $LOGFILE


Steps 2: schedule it in cronjob

30 23 * * 0 sh /home/oracle/alert_log_archive.sh

Please put your suggestion in a comment.