All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite

DOYENSYS Knowledge Portal

Saturday, December 31, 2016

Find IOPS of an Oracle database

There may be a situation where we have to calculate IOPS (Input Output Per Second) of oracle database to know the performance bottleneck of an oracle database regarding IO or when planning capacity for new hardware implementation. Though we can find it from AWR report, below is the sql to calculate the IOPS of an Oracle database

IOPS for entire day:====================

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';break on reportcompute sum of value on reportselect METRIC_NAME,avg(AVERAGE) value from dba_hist_sysmetric_summarywhere begin_time between to_date('20-DEC-16 00:00:00', 'dd-MON-yy hh24:mi:ss') and to_date('20-DEC-16 23:59:59', 'dd-MON-yy hh24:mi:ss')and end_time like '%20-DEC-16%' and METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')group by METRIC_NAME;

You can change the time interval in the above sql query based on your requirement.

Some times we get a Request from customers that they are not able to cancel their request because of the following error.

"The concurrent manager process that was running this request has exited abnormally. The ICM will mark this request as completed with error".

Navigation

If we try to cancel a concurrent request using "Cancel Request" button from the Administer > Concurrent > Manager form.We will get the following message:Request xxxxxx can no longer be cancelled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with error.

Solution

This can be safely done when Concurrent Managers are up and running.1) Backup fnd_concurrent_requests table2) SQL> UPDATE fnd_concurrent_requestsSET phase_code = ‘C’, status_code = ‘X’WHERE Request_id=&request_idHere Request id is the request which we want to cancel….3) Commit

Hello Everyone,I would like to start series of write ups/post based on the experience regarding How to perform Security Audit for oracle database and applications. Hoping that it will guide/help you to ensure/be aware of the areas related to Oracle Database & Applications Security , and Data Security, and to perform frequent internal security audit for Proactive and Reactive measures.Assumption: Though most of the topic it would be in plain English / Laymen language, I assume that the reader has and understands basic RDBMS, Applications , Data and networking concepts.I don't want to rush with multiple concepts in one post. I would like to go step by step with detail analysis and description. In this series of post , im not going to talk about tools which can automate the complete audit operations, Thought process is to explain the key areas/factors related to data security, Recommendations / Guidelines related to them, How and What to audit and action to be taken over them.So Let us Begin.....What is Data Security ? : Simple Terms : Ensuring my data (sensitive/insensitive)not visible and accessible by others, available & accessible by me always.Hope i dont want to elaborate , and i have obvious assumption that the readers knows what is data security.What are areas we need to protect and monitor for Data Security ( High level ) ?(other words : Area you should plan to audit the data security)Network : Network layer plays an vital role in IT Data Security. IP Address and Port access should be perfect maintained such that other intruders cannot get into our system. Based on the Sensitivity and volume on the data , It is recommended to have an ethical hacker on board to take proactive and reactive actions.Guidelines:1. Production Servers and Non-Production Servers highly recommended to be in two different networks.2. Production Servers (file system , database and applications) are not recommended to be accessible from non-prod servers.3. Mandatory : Production Servers (file system, database and applications) should not be visible outside network ( if there is a requirement, the network security should be tightened up that the one specific applications should only be accessible ).4. Production (Database and applications) Ports should not be using the default one like 1521 for DB and 8000 for Applications. - Reason behind 1521 and 8000 are worldwide known default Oracle database and applications ports, so Intruders can easily get into system very easily.For Applications , It isrecommended to implement SSL for Oracle applications , or traffic that comes to oracle applications server should be protected by SSL , for example Hardware Load Balancer.5. Highly recommended to set TCP.INVITED_NODES in sqlnet.ora, which wont let all other IP address which was not mentioned in INVITED_NODES to access the database.6. Subscribe for Oracle OTN Notifications , through which you will get Frequest Oracle security Updates ... As well as (related to this topic), if there are any network vulnerability released like for example : Poodle vulnerability related to SSLv3 , you will get to know and you can take immediate action over them.How and What to Audit:1. Get the list of non-production servers , sort them by number of developer or users using them. though all servers needs to be controlled and checked but in general the server which was accessed by high number of users should be continously monitored and checked.2. Though there are lot of monitoring tools available, as a basic components you can use telnet.3. Check that the points mentioned in the guidelines are met. , if not work with network team to block the ports (Action to be taken).4. For Oracle Database , login to database server , do tnsping/telnet with production host ip and 1521 port ,telnet 192.168.1.2 1521you may also do netstat -anp|grep 1521It should not be successful, if it is successful then check with DBAs to change the db port to different other than 15215. For Oracle Applications, login to database server , do tnsping/telnet with production host ip and 8000 port ,telnet 192.168.1.2 8000you may also do netstat -anp|grep 8000It should not be successful, if it is successful then check with Apps DBAs to change the applications port to different other than 8000.Recommended : recommended to implement SSL for Oracle applications , or traffic that comes to oracle applications server should be protected by SSL , for example Hardware Load Balancer.Next Posts : Database and Applications Security... After that Data security.-- Narasimha Rao

This post helps or guide you in fixing all issues related to punchout.

1. When user click over the punchout link, and in the detailed exception message you see "CONNECTION REFUSED"

Solution : telnet <punchout/supplier site> 443 in oracle applications oacore & apache server, it will be unsuccessful . Check with Firewall team, is there any network port block for that site from oracle applications oacore or apache server.

2. When user click over the punchout link, and in the detailed exception message you see "No Trusted Certificate found"

Solution: 1. Check with Supplier is the certificate key got changed, get the valid certificate from supplier, export it as .base64 certificate and get it in .txt format.

The certificate file is located at tech_st directory structure: /.../apps/tech_st/10.1.2/sysman/config/b64InternetCertificate.txt

3. append the ceritificate to the beginning of the file mentioned in profile POR :CA Certificate File Name), Ensure to append it in the beginning of file. "Append only the trusted certificates".4. Bounce the apache and oacore services.--Narasimha Rao

This Post helps you to find almost straight solutions for the issues you are facing in Oracle Workflow Mailer.Assumption: It is assumed that you have basic configuration knowledge related to Oracle workflow mailer. if not refer to my previous posts in which i have explained about workflow mailer configuration.If you go through this post from start to end , you will get complete picture of workflow mailer issues and solutionsBroad Head 1 : Oracle Workflow Mailer services is down1. Login to Oracle applications --> System administrator --> oracle Applications manager --> workflow2. Click over notification mailer.

3. Click on View log button. and see is there any errors in the logfile.POSSIBLE CAUSE 1 : INCORRECT SMTP SERVER ADDRESS OR SMTP SERVER IS NOT ACCESSIBLE.1. Login to Oracle applications --> System administrator --> oracle Applications manager --> workflow3. click on view details button , ensure that the outbound server name is correct.3.1 if it is not correct, click on EDIT button, enter the valid smtp server address.3.2 if SMTP Server address is correct, do telnet <smtp server> <smtp port> from Oracle Applications server where concurrent is running. telnet mail.gmail.com 25if it not reachable, connect with exchange team and network team for either resolution or for alternate smtp address.POSSIBLE CAUSE 2 : Inbound email address is not accessible.1. Login to your workflow webmail account with the password provided in the workflow mailer setup.2. Ensure that the webmail login is successful , if successful, ensure that the inbox/mail box has suffiecient space.2.1 if it not accessible , connect with exchange team , reset the password and ensure you can login successfully , once successful, Oracle applications --> System administrator --> oracle Applications manager --> workflow --> Notification Mailer --> Edit --> Enter the new password.2.2 once accessible , and mail box found to be full , clean all old/obsolete mails from DISCARD AND PROCESS FOLDER.2.3 Once above two steps are done and success, restart the workflow mailer once.Broad Head 2 : Oracle Workflow Mailer services is up and still emails are not going.POSSIBLE CAUSE 1 : WF: Workflow Mailer Framework Web Agent profile was either set to null or not valid.1. Navigate --> System administrator -->Profiles --> system

POSSIBLE CAUSE 3 : Inbound email is full.the below points are already mentioned in this post though, but are required for check and fix the issue.1. Login to your workflow webmail account with the password provided in the workflow mailer setup.2. Ensure that the webmail login is successful , if successful, ensure that the inbox/mail box has suffiecient space.2.1 if it not accessible , connect with exchange team , reset the password and ensure you can login successfully , once successful, Oracle applications --> System administrator --> oracle Applications manager --> workflow --> Notification Mailer --> Edit --> Enter the new password.2.2 once accessible , and mail box found to be full , clean all old/obsolete mails from DISCARD AND PROCESS FOLDER.2.3 Once above two steps are done and success, restart the workflow mailer once.-- Narasimha Rao