September 2020

How To Assign AME[Approval Management Roles/Responsiblities} Access to Any User in Oracle Apps

Himanshu funoracleapps , Oracle Apps No comments

How To Assign AME[Approval Management Roles/Responsiblities] Access to Any User in Oracle Apps

1) Login in as "Sysadmin"

2) Navigate to User Management Responsibility > User

Find the user to whom you want to give access. Click Update.

3) Click Assign Roles

4) Query and Select

- Approvals Management Administrator

- Approvals Management Business Analyst

Provide relevant justification and Click save.

5) Now navigate to Functional Administrator Responsibility > Security > Grants > Create Grant

Enter the relevant Name and Description of Grant.

Select the User to whom you want to give a grant.

Select "AME Transaction Types" in Object

Click Next and proceed to Step 3

Select "AME Calling Applications" in Set.

Process to next step and Finish.

There we can see Approval Management responsibility on homepage.

Please make sure Workflow Background Engine Job is completed, Before you can go ahead and use.

No comments :

Post a Comment

ORA-01207: file is more recent than control file - old control file

Himanshu funoracleapps , Oracle No comments

ORA-01207: file is more recent than control file - old control file

Database showed the control file is older, When checked found some DBA has crashed the PROD box. Boom !!!

Database was mounted and when tried to start getting below error:

ORA-01122: database file 76 failed verification check

ORA-01110: data file 76:

'+PROD/prod/datafile/apps_ts_tx_idx.343.1028735351'

ORA-01207: file is more recent than control file - old control file

Check the files required recovery.

select name,open_mode from v$database;

NAME OPEN_MODE

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

PROD MOUNTED

SYS@PROD> select * from v$recover_file;

FILE# ONLINE ONLINE_

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

ERROR CHANGE#

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

TIME

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

76 ONLINE ONLINE

UNKNOWN ERROR 9.8621E+12

28-SEP-20

Execute the recover database command using controlfile. it will prompt for redo log/archived file. Provide the complete path of the redo log files

SYS@prod> recover database until cancel using backup controlfile;

ORA-00279: change 9862087393594 generated at 09/28/2020 15:42:22 needed for

thread 1

ORA-00289: suggestion : /archlog/prod/prod_1_823799849_738185.arc

ORA-00280: change 9862087393594 for thread 1 is in sequence #738185

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/oracle/prodredo/redo11a.log

Log applied.

Media recovery complete.

Please note you might need to pass multiple archives and redo logs.

Once media recovery is done open the database with resetlogs.

SQL> alter database open resetlogs;

Database altered.

Verify the status of the Database.

SQL> select status from v$instance;

STATUS

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

OPEN

The database is recovered with redo log files.

Please make sure to take a full backup after the recovery as the logs have been reset.

No comments :

Post a Comment

How to Setup Local NTP server in Linux 7

Himanshu funoracleapps , Linux No comments

How to Setup Local NTP server in Linux 7

Network Time Protocol (NTP) – This is a protocol which runs over port 123 UDP at

Transport Layer and allows computers to synchronize time over networks for an

accurate time. This is majorly used when working with the cluster system, Oracle RAC,

Master-Slave setups.

We will see in this post how to set up the NTP Server and Client

Part 1- NTP server Preparation

1) Install ntp package: We need to install the ntp if not already available.

yum install ntp

2) Edit the NTP configuration

vi /etc/ntp.conf

3) Comment pool for server sync (Used in case of internet)

and Add local server 127.127.1.0

# Use public servers from the pool.ntp.org project.
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
#server 0.rhel.pool.ntp.org iburst
#server 1.rhel.pool.ntp.org iburst
#server 2.rhel.pool.ntp.org iburst
#server 3.rhel.pool.ntp.org iburst
server 127.127.1.0

4) Restrict to a range of IP to which access is required.

Add below line in etc/ntp.conf

restrict 192.168.56.0 mask 255.255 255.0 notrap

logfile /var/log/ntpservice.log

5) Enable NTP service over the firewall

firewall-cmd --add-service=ntp --permanent

firewall-cmd --reload

6) Start NTPD service and enable it to start in case of a reboot.

systemctl start ntpd

systemctl enable ntpd

systemctl status ntpd

7) Verify the NTP is using which server ( In my case it is local)

[root@oel7 ~]# ntpq -p

remote refid st t when poll reach delay offset jitter

==============================================================================

*LOCAL(0) .LOCL. 5 l 8 64 7 0.000 0.000 0.000

Part 2- NTP Client Preparation

We can have the client as Linux, Windows, or Mac OS.

Setup client on Linux

We need to make sure NTP is installed or else we need to install it.

1) yum install ntp

2) Edit the ntp configuration file and setup to enable sync via NTP server configured earlier.

We have to add an entry of our NTP server IP/hosts from where the client would sync.

Comment the other pool entries.

vi /etc/ntp.conf

# Use public servers from the pool.ntp.org project.
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
#server 0.centos.pool.ntp.org iburst
#server 1.centos.pool.ntp.org iburst
#server 2.centos.pool.ntp.org iburst
#server 3.centos.pool.ntp.org iburst
server 192.168.56.110

4) Sync the time before starting NTP service. It is done via hostname/IP of you NTP server
ntpdate -q 192.168.56.110

5) start ntp service
systemctl ntpd start

Now client is in sync with NTP server.

Setup client on Windows

Starting with Windows, Go to the Control Panel "Date and Time" option contains

an Internet Time tab which allows choosing an NTP server and turning time

synchronization on or off.

Update the entry with host/Ip or NTP server.

No comments :

Post a Comment

Oracle Reports - Program exited with status 255 for EBS in Windows

Himanshu funoracleapps , Oracle Apps No comments

Oracle Reports - Program exited with status 255 for EBS in Windows

Issue:

When running Active Users or other reports getting below error on Windows-based EBS instance.

"Program exited with status 255

Cause: The program terminated, returning status code 255.

Action: Check your installation manual for the meaning of this code on this operating system.
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request Request ID."

Cause:

This happens when before cloning proper cleanup is not done.

Duplicate value is repeated multiple times in "REPORTS_CLASSPATH" and "REPORTS_PATH" because of multiple re-installations of "Oracle Developer Suite" in the same "ORACLE_HOME" without proper cleanup before the subsequent clone.

Solution:

1) Open regedit in Windows

2) Navigate HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_<instancename>__TOOLS

and remove duplicate values for "REPORTS_CLASSPATH" and "REPORTS_PATH".

3) Retest the report.

No comments :

Post a Comment

Query to Find High Water Mark for a Given Table/All Table in Oracle

Himanshu funoracleapps , Oracle , scripts No comments

Query to Find High Water Mark for a Given Table/All Table in Oracle

Script:

Please note

The first parameter you need to pass as a single Table name or ALL for all tables in the schema
The second Parameter would be passed as Owner.

SET SERVEROUTPUT ON

SET VERIFY OFF

DECLARE

CURSOR cu_tables IS

SELECT a.owner,

a.table_name

FROM dba_tables a

WHERE a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))

AND a.owner = Upper('&&Table_Owner')

AND a.partitioned='NO'

AND a.logging='YES'

order by table_name;

op1 NUMBER;

op2 NUMBER;

op3 NUMBER;

op4 NUMBER;

op5 NUMBER;

op6 NUMBER;

op7 NUMBER;

BEGIN

Dbms_Output.Disable;

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');

Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');

FOR cur_rec IN cu_tables LOOP

Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);

Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||

LPad(op3,15,' ') ||

LPad(op1,15,' ') ||

LPad(Trunc(op1-op3-1),15,' '));

END LOOP;

END;

SET VERIFY ON

No comments :

Post a Comment

Query to shrink Datafiles and Reclaim unused Space in Oracle

Himanshu funoracleapps , Oracle , scripts No comments

Query to shrink Datafiles and Reclaim unused Space in Oracle

Steps to Shrink the datafiles

set verify off

set pages 1000

column file_name format a50 word_wrapped

column smallest format 999,990 heading "Smallest|Size|Poss."

column currsize format 999,990 heading "Current|Size"

column savings format 999,990 heading "Poss.|Savings"

break on report

Step1: To Check Database block size

column value new_val blksize

select value from v$parameter where name = 'db_block_size';

Step2: Check how much space can be reclaimed

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+);

You can also add condition if needs to be checked for a specific tablespace

Step3: Script to reclaim unused space from the datafiles

set pages 0

set lines 300

column cmd format a300 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) -

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;

No comments :

Post a Comment

Query to find Oracle Process consuming most of Memory and CPU

Himanshu funoracleapps , Oracle , scripts No comments

Query to find Oracle Process consuming most of the Memory and CPU

Session consuming the most memory

SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc

Session Consuming Most CPU

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null

order by 3 desc

No comments :

Post a Comment

How to identify a filesystem type in Linux System

Himanshu funoracleapps , Linux No comments

How to identify a filesystem type in Linux System

There are multiple ways to determine the file system type in a Linux Based Environment

1) df -Th

for this to work the filesystem should be mounted

2) fsck -N /dev/device

Example:

fsck -N /dev/sdb2

3) lsblk -f

Displays the block device with filesystem type details

4) mount |grep -i "device"

Example:

mount| grep -i "sdb2"

5) blkid /dev/device

Example:

blkid /dev/sdb2

6) cat /etc/fstab

This would only work if the filesystem is updated in fstab.

No comments :

Post a Comment

How to perform Oracle 11g Data Guard Switchover and Switchback

Himanshu funoracleapps , Oracle No comments

How to perform Oracle 11g Data Guard Switchover and Switchback

Switchover:

Before performing switchover, please verify the state of the data guard on both the instances by following SQL queries:

SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SQL> SELECT sequence#, first_time, next_time, applied

FROM v$archived_log

ORDER BY sequence#;

SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

DEST_NAME

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

STATUS ERROR

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

LOG_ARCHIVE_DEST_2

VALID

SQL> select message from v$dataguard_status;

Note: This command will give you an appropriate message about the data guard current status.

Kick-Off switchover activity by issuing the following commands:

On Primary database:

Step-1

Connect to Primary database and convert primary database to standby.

[oracle@funoracle ~]$ sqlplus / as sysdba

SQL> alter database commit to switchover to standby;

Database altered.

Step-2

Shutdown primary database:

SQL> shutdown immediate;

Step-3

Startup nomount old primary database as new standby database:

SQL> startup nomount

ORACLE instance started.

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Verify database role on old primary database:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

PROD MOUNTED PHYSICAL STANDBY

On Standby database:

Step-4

On the original standby database, Convert the old standby database to the primary database:

SQL> alter database commit to switchover to primary;

Database altered.

Step-5

Convert old standby database as primary and shutdown database:

[oracle@funoracledr ~]$ sqlplus / as sysdba

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step-6

Startup old standby database as the primary database:

SQL> startup

ORACLE instance started.

Verify database role on old standby database:

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

PROD READ WRITE PRIMARY

Switch overactivity have completed successfully, our old primary database has become standby and old standby database has become the primary database.

Note

We can test the above switchover activity, by generating multiple archive logs on the primary database and verify those archive logs being transferred on the standby database.

Switchback:

To switchback, we have to follow the same above-mentioned steps

No comments :

Post a Comment

Cron to Schedule a Script on Last Day of Month

Himanshu funoracleapps , Linux , scripts No comments

Cron to Schedule a Script on Last Day of Month

00 01 28-31 * * [ "$(date +%d -d tomorrow)" = "01" ] && /script.sh

At 01:00 on every day-of-month from 28 through 31

00--> Minutes when you want to run
01--> Hour when the job needs to be executed
28-31--> 28,28,29 and 31’st of each month
*--> All month of year
*-->All day of week
/script.sh--> Script name which needs to be executed
[ "$(date +%d -d tomorrow)" = "01" ]--> Logic which will test if next date is 01

No comments :

Post a Comment

Query to find Plan Hash Values for a SQLID in Oracle

Himanshu funoracleapps , Oracle , scripts No comments

Query to find Plan Hash Values for a SQLID in Oracle

SELECT DISTINCT sql_id, plan_hash_value

FROM dba_hist_sqlstat dhs,

(

SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap

FROM dba_hist_snapshot ss

WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE

) s

WHERE dhs.snap_id BETWEEN s.min_snap AND s.max_snap

AND dhs.sql_id IN ( '&SQLID')

No comments :

Post a Comment

How to get Oracle SQL output in HTML format and send as a Mail Body

Himanshu funoracleapps , Linux , Oracle Apps , scripts No comments

How to get Oracle SQL output in HTML format and send as a Mail Body

SQL Script to get the output as HTML

SET MARKUP HTML ON SPOOL ON

set termout off

set pages 999

set pagesize 999

set feedback off

SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF

spool fnd_debug_report.html

set pagesize 1000

set pause off

set linesize 150

prompt

prompt FND Debug Profile Enabled Report

prompt

/* Below can be any query based on your requirement*/

SELECT upper(sys_context('USERENV','DB_NAME')) "DB_NAME",fpot.user_profile_option_name NAME,

DECODE (fpov.level_id,

10001, 'Site',

10002, 'Application',

10003, 'Responsibility',

10004, 'User',

10005, 'Server',

'UnDef')

LEVEL_SET,

DECODE (TO_CHAR (fpov.level_id),

'10001', '',

'10002', fap.application_short_name,

'10003', frsp.responsibility_key,

'10005', fnod.node_name,

'10006', hou.name,

'10004', fu.user_name,

'UnDef')

"CONTEXT_NAME",

fpov.profile_option_value VALUE

FROM apps.fnd_profile_options fpo,

apps.fnd_profile_option_values fpov,

apps.fnd_profile_options_tl fpot,

apps.fnd_user fu,

apps.fnd_application fap,

apps.fnd_responsibility frsp,

apps.fnd_nodes fnod,

apps.hr_operating_units hou

WHERE fpo.profile_option_id = fpov.profile_option_id(+)

AND fpo.profile_option_name = fpot.profile_option_name

AND fu.user_id(+) = fpov.level_value

AND frsp.application_id(+) = fpov.level_value_application_id

AND frsp.responsibility_id(+) = fpov.level_value

AND fap.application_id(+) = fpov.level_value

AND fnod.node_id(+) = fpov.level_value

AND hou.organization_id(+) = fpov.level_value

and fpot.language='US'

and fpot.user_profile_option_name like 'FND: Debug Log Enabled%'

and fpov.profile_option_value='Y';

spool off

exit

Example:

Sample script to send this as a mail body

. ~/.bash_profile

Inst_name=`echo $TWO_TASK|tr [a-z] [A-Z]`

Mail_id=himanshu@dummy.com

output_report=$HOME/scripts/fnd_debug_report.html

export content=${output_report}

export subject="FND Debug Profile is enabled in ${Inst_name}"

rm -f ${output_report}

cd $HOME/scripts

sqlplus $APPSLOGIN @fnd_debug.sql

if grep --quiet 'Log' $HOME/scripts/fnd_debug_report.html;

then

echo "Sending email"

(

echo "Subject: $subject"

echo "To: $Mail_id"

echo "MIME-version: 1.0"

echo "content-Type: text/html"

echo "content-Disposition: inline"

cat $content

) | sendmail -t

No comments :

Post a Comment

sendmail errors: Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF (HP UX)

Himanshu funoracleapps , Linux No comments

sendmail errors: Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF (HP UX)

Error:

While checking the mailq below messages are being seen.

/var/spool/mqueue (25 requests)

-----Q-ID----- --Size-- -----Q-Time----- ------------Sender/Recipient-----------

084Fe2V7000352* 229 Fri Sep 4 23:40 root

root

084FZ0EN019660 229 Fri Sep 4 23:35 root