5 Best Practices for Setting Dispatchers for Shared Connections

Hi all,

Here are 5 Best Practices / Tips for when setting dispatches with Shared Connections:

1. Set local_listener on both instances on the database

alter system set LOCAL_LISTENER=”(address=(protocol=tcp)(port=1521)(host=yourhost))” scope=both sid=’instance_name’;
Ref: Shared Server: Dispatchers Are Not Registered With Listener (Doc ID 465881.1)

2. Dispatchers parameter should be set to utilize the VIP name of the host

alter system set dispatchers='(address=(protocol=tcp)(host=node1-vip))(dispatchers=2)’ scope=both sid=’instance_name’;
Ref: How To Configure Shared Server Dispatchers For RAC Environment (Doc ID 578524.1)

3. Dispatchers count should be set appropriately considering the number of sessions expected to connect to the database

A general rule of thumb is that 1 dispatcher can handle 50 shared server connections with minimal performance impact.
Ref: Shared Server Only: TNS-12518, TNS-12564 and TNS-12602 Errors at Connect Time (Doc ID 1539104.1)

4. Arguments can be used with the dispatchers parameter for closer control of how the shared server sessions are used

SESSIONS – Determines the max sessions allowed for each dispatcher.
CONNECTIONS – The maximum number of network connections to allow for each dispatcher.
Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DISPATCHERS.html#GUID-DCBCCF94-8A73-4805-9138-412DA413FC7C

5. Shared_servers parameter can be set to control total number of shared servers spawned by the database

shared_servers set to 1 –> This will enable shared server sessions on the database.
max_shared_server  –> Specifies the maximum number of shared servers that can run simultaneously.
shared_server_sessions  –> Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables to reserve user sessions for dedicated servers.
Ref: Automatic Shared Server Configuration (Doc ID 265931.1)

See you next post!

Weekly Events #1

 

MARCH 16

 

Oracle Cloud SaaS Awareness Session

Oracle SD-WAN – ensuring application performance and reliability for real-time and other critical applications.

Oracle SD-WAN helps enterprises connect their branches, HQ, data centers, multiple clouds but also vehicles and ships with an autonomous WAN providing greater network reliability and superior application quality of experience while reducing the cost of WAN.

READ MORE

Oracle User Group Get-Together: JDE-DevOps(Blue-Green deployment) and Pluggable DB for Data refreshes

This is a webinar associated with Oracle Benelux User Group and French Oracle Users Group.

Come and join us to see our success in Oracle’s JDE internal development operations with Blue-Green deployment for the JDE Tools updates process and simplification of data refresh process using pluggable databases(PDB) of containerized database(CDB). This session showcases the benefits of Blue-Green deployment strategy, Pluggable Databases and how much Time/Effort we have saved in our internal operations by adopting the latest innovations of DevOps strategies and Pluggable Databases.

READ MORE

Cloud Applications Implementation Best Practices Webinar

ORACLE CS connectx: implementation best practices.

We invite you to attend a LIVE webinar to get started with implementation of your Oracle Cloud Applications. This is a perfect start to your Cloud journey where you will learn about:-

READ MORE

Oracle Maximize Value with Oracle Database 19c

“The Time is Now” for database modernization removing technology debt.

The new normal has accelerated digitalization of many customers across industries. Customers are facing new challenges and need to develop new business models to increase efficiency, be more agile and highly resilient.

Addressing “technology debt” enables customers to move much more rapidly in this direction.Technology debt is not only linked to hardware and software obsolescence, but leaves organisations “lagging” – unable to leverage automation and integration capabilities, lacking superior scalability and availability to be able to handle demand and offer near-zero business service disruption. To add to this, there is an inability to get insights from data in real-time, and increased vulnerability as cyber-attacks grow exponentially and data breaches are commonplace, particularly, as systems remain out-of-date.

READ MORE

DAY ONE – Hyperion on Oracle Cloud Infrastructure – Hands On Lab

See Oracle Cloud Infrastructure (OCI) in action with your Hyperion application in a live hands-on virtual lab.

In this workshop, Oracle cloud engineers will demonstrate the setting up of Oracle Hyperion Financial Management (HFM), Oracle Hyperion Foundation Services, and Oracle Hyperion Financial Data Quality Management Enterprise Edition (FDMEE) on OCI.

READ MORE

   MARCH 17

 

DAY TWO – Hyperion on Oracle Cloud Infrastructure – Hands On Lab

See Oracle Cloud Infrastructure (OCI) in action with your Hyperion application in a live hands-on virtual lab.

In this workshop, Oracle cloud engineers will demonstrate the setting up of Oracle Hyperion Financial Management (HFM), Oracle Hyperion Foundation Services, and Oracle Hyperion Financial Data Quality Management Enterprise Edition (FDMEE) on OCI.

READ MORE

DAY ONE – UTOUG – Training Days 2021

We are going VIRTUAL this year.

UTOUG Training Days brings the best speakers in the country to your doorstep! Network with people who are part of your local community – no hotel required! Explore the session schedule when it becomes available then go ahead and invest in yourself by registering today.

UTOUG is committed to ensuring that all Oracle technologists in our area can learn and grow. Our available scholarships help further that commitment by helping UTOUG members and students attend Training Days.

READ MORE

Oracle Live Event – MySQL Accelerate Live Session: GAMING

Mysql for modern & secure high availability gaming applications.

This Accelerate Live Session is designed to get you on track with the latest DBA trends and technology with real life use cases.

READ MORE

New Autonomous Data Warehouse Innovations

Featuring andrew mendelsohn, evp, oracle database server technologies.

On Wednesday, March 17, Andrew Mendelsohn will announce Oracle’s latest cloud data warehouse innovations that put the full power of your data within the reach of your entire organization. New intuitive, integrated capabilities enable analysts, data scientists, and line-of-business developers to deliver even faster results, accelerating insights and time-to-market. Together with the existing zero-admin capabilities of Oracle Autonomous Data Warehouse, organizations of all sizes can lower TCO and increase ROI while more rapidly gaining maximum value from their data.

READ MORE

Live Webinar: Simplify and Modernize Your On-Premises Cloud using Oracle Private Cloud Appliance

Cloud native technology are rapidly gaining acceptance and is simplifying the building of complex applications, while at the same time enabling organizations to build and deploy these applications faster.

Now, with Cloud native environments integrated with the latest PCA software release, organizations are further empowered to build and run scalable applications in modern, dynamic, highly available environments, which enable loosely coupled systems that are resilient, manageable, and observable.

READ MORE

ZDLRA and MAA integration to reach ZERO RPO everywhere

Zero Data Loss Recovery Appliance (ZDLRA) is an Oracle Engineering System that was it is part of the Oracle Maximum Availability Architecture (MAA) family. But it goes beyond just backup and recovery. In this webinar I will show how to integrate ZDLRA with all kinds of databases that you have, protecting from the simplest single instance to the huge multi-site DG+RAC database. Everything with ZERO Recovery Point Objective (RPO) and multi-site protection. Check in this webinar how you integrate ZDLRA features like Real-Time Redo transport, Multi ZDLRA Replication, and Tape backups with the Oracle MAA Reference Architecture. After the webinar, you will understand why you need to consider ZDLRA for your architecture design and how ZDLRA and MAA can work together to reach maximum protection for all kinds of databases.

READ MORE

   MARCH 18
DAY TWO – UTOUG – Training Days 2021

We are going VIRTUAL this year.

UTOUG Training Days brings the best speakers in the country to your doorstep! Network with people who are part of your local community – no hotel required! Explore the session schedule when it becomes available then go ahead and invest in yourself by registering today.

READ MORE

Cloud-Driven Transformation: Accelerating resiliency and business continuity with Oracle

Today’s organizations are undergoing massive transformation, ramping up digital initiatives across the enterprise. Further driven by the pandemic, according to IDC, at least 50% of organizations in Africa will launch new lines of business driven by DX investments in 2021.

READ MORE

Live Webcast: Optimizing value of your Oracle licenses – best practices for Software Asset Management

Optimizing value of your oracle licenses – best practices for software asset management.

Do you want to gain a better understanding of the various licensing and contract options that Oracle offers? Is the management of your Oracle licenses fixed in a solid and standardized Software Asset Management process? Are you wondering what methods and techniques you can use and what this means for your organization? Then join us for this upcoming webinar!

READ MORE

Meetup: Experiences with Oracle Database Migrations to the Cloud (Moved to April!)

Meetup: Experiences with Oracle Database Migrations to the cloud</strong>

Originally this Meetup was supposed to take place on March 18th. The Meetup will be postponed to a date yet to be determined in April. More information about this will follow as soon as possible.

READ MORE

APEX Office Hours
APEX + Server-side JavaScript = Awesome!
Welcome to the future! Starting with Oracle Database 21c, developers can now execute JavaScript within the database. This functionality is enabled by the Multilingual Engine (MLE), powered by GraalVM. And APEX 20.2 is the first (and only) low code framework on the planet which natively supports server-side JavaScript, out of the box!

READ MORE

   MARCH 19
A la découverte de la nouvelle machine virtuelle d’Oracle – GraalVM

Venez decouvrir graalvm lors de ce webinar.

Une toute nouvelle solution elaborée par “Oracle Lab” qui apporte une valeure ajoutee ultra rapide au Business que vous soyez une Multinational ou une PME.

READ MORE

AWS AQUA for Redshift

Hi all,

Quick one today. Did you see this new release? I’m a bit behind the schedule but trying to catch up with the news. It seems very interesting:

AQUA (Advanced Query Accelerator) for Amazon Redshift is available in preview. AQUA provides a new distributed and hardware-accelerated cache that brings compute to the storage layer for Amazon Redshift and delivers up to 10x faster query performance than other cloud data warehouses.

AQUA is a high-speed cache on top of Redshift Managed Storage that can scale out and process data in parallel across many AQUA nodes. AQUA uses AWS-designed analytics processors that dramatically accelerate data compression, encryption, and data processing on queries that scan, filter, and aggregate large data sets. With this new architecture, customers can run queries quicker than ever before, allowing them to query data directly, even at scale, and giving them more up-to-date dashboards, reducing development time, and making system maintenance easier.

It is available for preview in US East (Ohio), US East (N. Virginia), and US West (Oregon) regions at this point.

For more references:

  • There is a very tech detailed article about it HERE.
  • There is also a very nice TechTalk presenting it HERE.

Let’s keep up to date!

ORA-07445 [kxsPurgeCursor()]

Hi all,
We started getting lots of ORA-07445 errors that are being reported in the Exadata production database from a client.
Checking on the traces we can see that someone is running the below SQL and that is triggering these errors:
declare
i number := 0;
begin
for c1 in (
select address, hash_value, sql_id from v$sql where
last_active_time < sysdate - 1/24
and executions < 2 ) loop
begin
dbms_shared_pool.purge(c1.address || ' ' || to_char(c1.hash_value), 'C');
i := i + 1;
exception when others then
dbms_output.put_line(c1.sql_id);
end;
end loop;
dbms_output.put_line('Cursors purged = ' || to_char(i));
end;
/
I’m not even going to discuss the PL above, it’s purging all new SQLs executed less than 2 times in the last 24hours. I’m not really agreeing with this…
What matters is: in the end, it was being caused because hash value of non-cursor is being passed to dbms_shared_pool.purge().  This is a match to Bug 29281112 – ORA-7445: [kxspurgecursor()+517] [sigsegv] (Doc ID 29281112.8)
There is no actual workaround besides stop passing a non-cursor hash to dbms_shared_pool.purge, however we have a fix for a better addressing of those cases on:

Quick Reference: Oracle Exadata default passwords

It’s not needed to say how important this is to have all the password reset, even though on new Exa hardware the SSH is usually disabled. How ever, oftenly we need to have access in some of the consoles knowing the password was never changed but unaware on what it should be.

For those cases (until you change it, ASAP), here is a quick reference:

Database Server:

  • root/welcome1
  • oracle/welcome1
  • grid/welcome1
  • grub/sos1Exadata

Exadata Storage Servers:

  • root/welcome1
  • celladmin/welcome1
  • cellmonitor/welcome1

InfiniBand switches:

  • root/welcome1
  • nm2user/changeme

Ethernet switches:

  • admin/welcome1

Power distribution units (PDUs):

  • admin/welcome1
  • root/welcome1

Database server ILOMs:

  • root/welcome1

Exadata Storage Server ILOMs:

  • root/welcome1

InfiniBand ILOMs:

  • ilom-admin/ilom-admin
  • ilom-operator/ilom-operator

Keyboard, video, mouse (KVM):

  • admin/welcome1

Change them!

I hope this was useful for you.

Loading “Views” from One Database to Another

Hi all,

This seems to be a simple request, right?  Let’s go to the regular cases / simple answers:

  • Use DBMS_METDATA.ET_DDL to get the view code: DBMS_METADATA.get_ddl (‘VIEW’, <VIEW_NAME>, <OWNER>)
  • Use datapump with include=VIEW Or even specifying the list INCLUDE=VIEW:\”IN (\’VW_EXAMPLE1\’, \’VW_EXAMPLE2\’)\” Or part of the name: INCLUDE=VIEW:”LIKE ‘VW_%EXEMPLE%'”

However, when supporting a client with this need I see this may have some caveats. The options above load the view purely, not the “content” of the view from an application perspective.

And here let’s leave it clear, a view doesn’t have any data but instead stores a query to retrieve the data from regular tables. We could have underlying tables as support mechanisms in case of materialized views,  but in essence, they are transitory build from the actual database tables. Now how to load views to other databases without the source tables including the underlying data?

This question has 2 answers:

  • Exporting the views as tables.

In general lines I’d do it manually: Create tables from views and then export them. This can mean I would need to have space for creating those tables, which can be a lot, though.

But then I see we have VIEWS_AS_TABLES clause in Datapump which makes exactly that. As a reference: https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904

However, in this case, the import will bring the views as tables, not as views. Which will theoretically resolve the issue from a data perspective, but may not be what is required: What if I need the views as views on the destination database?

Well, in this case it’s needed to load all the underlying tables. How to extract them? The hard way is opening all the view codes and listing them. The smart way is the second option:

  • Listing the tables referenced by Views from dba_dependencies and exporting them.

select owner as schema_name,
name as view_name,
referenced_owner as referenced_schema_name,
referenced_name,
referenced_type
from sys.dba_dependencies
where type = 'VIEW'
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS',
'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by owner, name, referenced_name, referenced_owner, referenced_type;

Aaaand, that’s what I actually needed. With the list in place, it’s a matter of exporting with datapump including the tables on the list and the views (as mentioned above).

I hope it helps!

ORA-12537: TNS:connection closed – When nothing else works!

Sounds desperate?

Well, when you are sure all the TNS, listener (SID_LIST_LISTENER on this case), services, db_domain are fine but still facing this issue when trying to access remotely an open database… what to do?

[oracle@greporasrv admin]$ sqlplus sys/******@MYDB as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 3 17:45:29 2021

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

My friend here is a quick tip that, if you are an Oracle DBA for time enough you know that solves several issues, maybe affecting this case again:

[oracle@greporasrv admin]$ cd $ORACLE_HOME/bin
[oracle@greporasrv bin]$ ls -lrt oracle
-rwxr-x--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle
[oracle@greporasrv bin]$ chmod 6751 oracle
[oracle@greporasrv bin]$ ls -lrt oracle
-rwsr-s--x. 1 oracle oinstall 242993632 Dec 18 19:20 oracle

The CHMOD 6751 did it again!

It happened to me because, somehow, these permissions were wrong in my LOCAL host (the one I was trying to use the SQLPlus* from.

I hope it helps!

 

19c+ DBMS_STATS.GATHER_TABLE_STATS Memory Leaking on PGA

Hi all,

So, here is a quick post but which wasn’t so eaasy to be mapped, though.

It happens that right after a 19c upgrade from 18c, we started facing Memory Leak messages related to PGA area on a regular basis. After a while following the v$process_memory allocation, it was possible to map it to an ODI routing with DBMS_STATS.GATHER_TABLE_STATS. The PGA exceeds the limit just a few minutes after.

Nice han, so what is happening?

Long story short: Bug 30846782 : 19C+ FAST/EXCESSIVE PGA GROWTH WHEN USING DBMS_STATS.GATHER_TABLE_STATS.

According to Oracle Docs: No Workaround!

But here is the golden info I want to share after an SR:

EXECUTE IMMEDIATE 'alter session set "_fix_control"='20424684:OFF';

You are welcome!

Cheers!

Thanks GoldenTalks!

Hi all,

I want to first say thanks for Gilson and his whole team in GoldenGateBR for the very pleasant time on last Thursday’s conversation!

We spoke a bit of everything, but mostly over career, my historic on the community, and of course the grepOra blog.

Also many thanks for the ones who could connect and stay connected up to the end of the video. Here is the recording, if you will (it’s in Portuguese!).