How to change Oracle AWR snapshots generation frequency

 The default frequency (automatic) of generation of Oracle Automatic Workload Repository (AWR) is every 1 hour (60 minutes). Sometimes, an Oracle DBA might have set this to generate more frequently, say, every 15 minutes i.e 4 times an hour to troubleshoot a performance issue and later would have forgotten to set it back to 1 hour frequency. 

This will fill up the SYSAUX tablespace very quickly. Please follow the below list of instructions to reset it back to 1 hour frequency.

This is tested in an Oracle 19c database.

select snap_interval, retention from dba_hist_wr_control;

If it is run every 15 minutes, then the output will show +00000 00:15:00.0

execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

The AWR snapshots will now be generated every 1 hour instead of every 15 minutes.

select snap_interval, retention from dba_hist_wr_control;

The output should now show +00000 01:00:00.0

Disable Oracle AHF in ExaCC Exadata

Some versions of Oracle AHF (Autonomous Health Framework) utility can cause performance overhead in Exadata Cloud@Customer (ExaCC) such as consuming more memory on database server nodes and causing high load on these servers and it is not required to be running all the time in the background. Oracle AHF is a combination of mostly TFA (Trace File Analyzer), Exachk and Oracheck utilities and this tool gathers various diagnostic information.

It is advised that this tool be disabled and started only when required, say, if Oracle Support team wants to gather some diagnostic information as part of a Service Request (SR) for troubleshooting an issue. Once the gathering of the diagnostic information is completed, then stop and disable it again.

Some useful commands of Oracle Trace File Analyzer (TFA) are below.

How to check the file path of the utility

which tfactl

How to check the version of the utility

tfactl version

How to check if AHF utility is running

tfactl status

How to disable automatic startup

Login as root to the database server

tfactl disable

How to enable automatic startup

Login as root to the database server

tfactl enable

How to perform manual stop

Login as root to the database server

tfactl stop

How to perform manual startup

Login as root to the database server

tfactl start

Network connectivity between two AWS EC2 instances

Introduction

This article explains the process to establish network connection (intranode communication a.k.a network communication) between two AWS EC2 instances if both belong to the same EC2 security group.

 Using private ip addresses

Login to AWS console and choose EC2 Service.

Just put the security group name itself in the “Allow” ALL TRAFFIC with source as the security group name in the common security group.

Using public ip addresses

Add two more rows like the above shown for private ip addresses, but this time with the source as the public IP (one row for each public IP address). Do not forget to end the public ip address with /32.

adop prepare phase fails with TNS-12542 TNS-12560 TNS-00512 Address already in use

Did your adop phase=prepare fail in Oracle E-Business Suite 12.2.x version with the following error messages ?

Error listening on: (ADDRESS=(PROTOCOL=TCP)(Host=application-server)(Port=xxyy))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

Solution

Under usual circumstances, this should not be an issue at all because the Oracle EBS apps listener should be up and running all the time (The tnslsnr process) and adop phase=prepare should successfully get completed by noting that the LISTENER listener-name has already been started.

However, due to some unexplained reasons, the prepare phase may still fail. It may be an Oracle bug. But we can simply abort the online patching, do a full cleanup, stop the apps listener and retry the adop phase=prepare.

 

Abort and do a full cleanup

adop -status  (Shows that it failed at the prepare or apply phase)

 

Set the RUN file system

 
echo $FILE_EDITION (Must show run)

 
adop phase=abort

adop phase=cleanup cleanup_mode=full 

Stop the LISTENER process

netstat -nlp | grep xxyy (Replace xxyy with the actual port number reported in the error message)

ps -ef | grep tnslsnr

Make sure the process id matches with that of the id shown from the output of netstat command above.

Now terminate the process id using the kill -15 processid command. 

Wait for a minute. If it is still running, then terminate using the kill -9 processid command.

 

Retry

adop phase=prepare

 

Conclusion

This issue can be an Oracle bug. If this error occurs frequently with the adop phase=prepare, then create a service request with Oracle support at https://support.oracle.com

Oracle EBS online patching abort after adop prepare phase

If your Oracle E-Business Suite (Oracle EBS) online patching process failed only at the adop phase=prepare phase, then you can abort the online patching process by running the following commands.

 

adop -status (Shows that it failed at the prepare or apply phase)

 

Set the RUN file system

 
echo $FILE_EDITION (Must show run)

 
adop phase=abort

adop phase=cleanup cleanup_mode=full

The additional adop phase=fs_clone is required only if it failed at the actual patch apply phase i.e adop phase=apply. Otherwise fs_clone is not required.

How to share VNC Viewer screen

Launch VNC Viewer

Click on Options

Choose Misc.

Check the Shared (don't disconnect other viewers)