Can oracle do horizontal scale out and read only load balancing

Oracle has Real Application Clusters (RAC).  There is a TON of information on RAC on the web.  It is really more than can be talked about on a Q&A site like this.

There is also Goldengate, that you have asked about before, that does replication.

>> how about read only load balance? like for any reporting query, please route to other DB other than the primary to offload the expensive report operation ?

This is not really load balancing. It is an configuration in your application that tells what connection strings  to be used for what purpose.

Ideally you should be using database services. This is particularly useful in a RAC configuration to direct your requests to dedicated instances.

More details on services at the link here -
https://docs.oracle.com/database/121/NETAG/concepts.htm#NETAG175

"This is not really load balancing."

this is the SQL routing.

but after that what if we have more than one read only database farm, do oracle has the round robin feature to share the load between Oracle DB?

"It is an configuration in your application that tells what connection strings  to be used for what purpose."

if I tell a connection string, will oracle only route to a DB read only server pool and round robin there ?

>>it's a paying feature offered with dataguard

Going from memory here:  I believe some versions of Data Guard can be open read-only without the Active Dataguard license.  I think Logical Dataguard can be readonly and still have changes applied.

Sujith,

" RAC has its own load balancing between the instances."

but this only for write operation ,right ? how about read? how about read scale out between individual Oracle servers in a read only DB farm ?

slightwv,

"RAC also has the ability to split load across all nodes so just because you start something on one node doesn't mean it runs on that node."

for both read and write ? but read scale out means adding more standalone DB server can share the read loading ?

Geert G,

"it's a paying feature offered with dataguard"

yes, I knew that, but that one is not read only scale out and load balancing, it is at most SQL read only routing. read operation route to passive node.

and as far as I knew, both primary and secondary Oracle DB need a data guard license with an additional active data guard license, am I right ? so in total 3 x data guard license for 2 x oracle dB, right?

slightwv,

Logical Dataguard  shouldn't need any extra license, right?

Performance trouble ?
ITSS: It's The Stupid Sql's

Tune the querries for the reporting
> Less need for load balancing

And put a whip in the office corner, for those who wrote all those badly performing querries

"Performance trouble ?
ITSS: It's The Stupid Sql's"

I am not sure what is that mean..

"Less need for load balancing"

I just need to see if Oracle can offer that.

>> but this only for write operation ,right ? how about read? how about read scale out between individual Oracle servers in a read only DB farm ?

Can you explain what you mean by "individual Oracle Servers" and "DB Farm"
are you talking about multiple databases here? If yes, I don't understand how that will make any sense for your application to connect to multiple databases for the load balancing purpose.

It is make sense only for your application to connect to multiple instances of the same database. Otherwise you are reading different things. Unless it is setup in as multiple read only standbys. which is not really essential.

As far as i see it, oracle offers everything the combined competition does
standby, sharding, rac, stretched standby, ...
http://www.oracle.com/technetwork/database/database-technologies/sharding/overview/index.html

you're asking for features, price i haven't seen yet

what competition are you comparing oracle to ?

>>for both read and write ? but read scale out means adding more standalone DB server can share the read loading ?

Yes, RAC is open read and write on ALL nodes.  A single query can run on several nodes at the same time.  The problem with it is distance between servers.  RAC needs a very high speed interconnect between all the nodes.

>>Logical Dataguard  shouldn't need any extra license, right?

All this assumes Enterprise Edition (EE) of Oracle:  You will need to license the Oracle server software.  Regular Data Guard is included in the EE license.

Sujith,

"Can you explain what you mean by "individual Oracle Servers" and "DB Farm"
are you talking about multiple databases here? If yes, I don't understand how that will make any sense for your application to connect to multiple databases for the load balancing purpose."

same DB content but more than one DB server host it, all replication between each other and data sync usually finish within 2-3 sec.

It is make sense only for your application to connect to multiple instances of the same database. 

Select allOpen in new window

just like MySQL multi master replication, difference/same application write to diff Oracle nodes to separate workload.

"Unless it is setup in as multiple read only standbys. which is not really essential.
Report Comment"

can we have more than one read-only standby share the world load , load balancing ?

Geert G,

"what competition are you comparing oracle to ?"

MS SQL AlwaysOn , MYSQL, MariaDB.

"you're asking for features, price i haven't seen yet
"

MS SQL and MariaDB all has it .

slightwv,

"Yes, RAC is open read and write on ALL nodes.  A single query can run on several nodes at the same time. "

tks and it is powerful. I knew, that's why it is expensive. a single WRITE query can run on several nodes at the same time ? or only READ ?

" RAC needs a very high speed interconnect between all the nodes."

I knew it, that's why it is expensive too ! but nowaday should be ok and network is cheap, between building, just use cat6a, etc.

" Regular Data Guard is included in the EE license."

yeah, but not active data guard.

Geert G,

this one:
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=724&get_params=streamId:21,p_loId:17210#

is  more on data partitioning. I watch that demo, it is funny that it said if we lost one shard, ONLY data on that shard is not available, in the horizontal scale out model for MS SQL, MySQL and Maria! lost one server DO NOT impact the operation at all! the data are clone across ALL NODES, so data will die if all database is dead ! which is almost not possible at all ! especially if all DB is not in single location.



one thing, usually you all use oracle on oracle hardware or you install on any machine you like ?  they claim like apple that their system run under their HW is the best !

>>a single WRITE query can run on several nodes at the same time ? or only READ ?
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf

Parallel Execution in Oracle The Oracle Database provides functionality to perform complex tasks in parallel, without manual intervention. Operations that can be executed in parallel include but are not limited to:

» Data loads
» Queries
» DML statements
» RMAN backups
» Object creation, e.g. index or table creation
» Optimizer statistics collection

"http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf
"

I don't think it only limited to 18c only, right?

so you are saying as it can do this "  DML statements " , it can parallel execute write operation too. right?

but it say:

"Oracle Database relies on a shared everything architecture. This architecture does not require any pre-defined
data partitioning to enable parallelism; all of the data is accessible from all processing units without limitations; the
degree of parallelism for an operation is decoupled from the actual data storage. "

so it is still a shared everything architecture and not shared nothing ? which is the heart of horizontal scale out, which is not affect by if the shared storage is dead ! this is what I like about horizontal scale out.

so finally conclusion is oracle has read load balancing but not horizontal scale out ?

Oracle has replication if you must have exact replicas in a shared nothing environment.

yes, you are right, but which is goldengate,  still need cost, right ?


all replication has a problem, which is log lagging, any log lagging by goldengate ?

We've covered Goldengate in a few of your previous questions.

>>all replication has a problem, which is log lagging,

I'm not sure exactly what you mean by "log lagging" but I'm going to guess it is about potential data loss if the logs don't replicate a row before the primary fail?

It is all about compromise.  Most replication products have a method to ensure 100% data integrity across the sites.  The issue with most of them is that ALL Sites have to be up 100% of the time for the data to replicate.  If any one cannot accept the record, the entire transaction fails.

Check out this concept:
https://en.wikipedia.org/wiki/Two-phase_commit_protocol

"I'm not sure exactly what you mean by "log lagging" but I'm going to guess it is about potential data loss if the logs don't replicate a row before the primary fail?"

Log lagging word also appear in MySQL replication, it means when replication is running, it read log and from time to time transaction all lock the log files, so the busy the system is, the high level of log file logging and it make the whole system slow.

so every thing is WAITING to write and reading log ! so log lagging.

If your Oracle database is waiting to write or read from log files, it isn't configured properly.

all tranditional RDBMS will write to log and commit to data file, right?

so log operation is normal, agree? but replication will use huge amount of log, right?

not necessarily ...
i'd advise against it, but you can switch of the archive logging in oracle

has some drawbacks, like no hot backups

"but you can switch of the archive logging in oracle
"

how about replication ? it has to use a lot of log operation, right ?

a lot ?
if you have a lot to replicate, you'll have a lot of log

if the table doesn't change, there is no logging

what logging is required, depends on the capture and apply modes:
https://docs.oracle.com/goldengate/c1230/gg-winux/GGODB/choosing-capture-and-apply-modes.htm#GGODB-GUID-7F85EBDE-B718-43A8-8F5D-F90521CFC845

I don't believe you'll have any more logging using replication than you have normally.  Granted, you MUST run in archive log mode when replicating since most replication products can get data from them but I don't believe replicating generates "MORE" logs.

Geert G,

"if you have a lot to replicate, you'll have a lot of log"

this is what we do before, it will LOCK the log a lot of time.

slightwv,

tks. has to benchmarkt that then.

any tools in Oracle allows us to check how busy the log is ?

>>it will LOCK the log a lot of time.

Logs don't lock per say.  What will lock the database is if there is if the logs rotate back around before the oldest one can be archived off.  If the system is configured properly this shouldn't happen.

>>any tools in Oracle allows us to check how busy the log is ?

The alert log tracks log switches.  I'm sure there is a view or three inside the system where you can query the information as well but I've never had to go looking for them.

"If the system is configured properly this shouldn't happen.
"
any URL on this on what is good log configuration?

I would start by understanding what the REDO logs are and how they are used:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-the-redo-log.html#GUID-4625A35C-EF8A-4A9E-8D19-829C1A665A34

There is a little information in the Tuning Guide:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/configuring-database-for-performance.html#GUID-BF462098-04C0-42E0-8FE4-8B6659A6A6F9

As far as reducing "locking", you just need enough of them to keep them from wrapping back around before the last one was archived.  The rest of things are about the time to recover in a disaster.

tks.