Can oracle do horizontal scale out and read only load balancing

hi,

any horizontal scale out capability of Oracle ? e.g. has a clone of the whole database content in all other oracle DB in the farm ?

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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
0
SujithData ArchitectCommented:
>> 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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SujithData ArchitectCommented:
You should really be looking at Real Application Clusters.  RAC has its own load balancing between the instances.
0
slightwv (䄆 Netminder) Commented:
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.  Oracle can split out parts of it to other nodes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
for reporting you can use a standby database
but, to actually run a report, the database needs to be open in read-only mode

it's a paying feature offered with dataguard

you can have multiple standby's
and even a stretched standby like on the other side of the planet

the moon doesn't work yet as they haven't got an electricity plant there yet
mars ... 2035 ?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
Geert GOracle dbaCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
SujithData ArchitectCommented:
>> 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.
0
Geert GOracle dbaCommented:
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 ?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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. 

Open 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 !
0
slightwv (䄆 Netminder) Commented:
>>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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
slightwv (䄆 Netminder) Commented:
Oracle has replication if you must have exact replicas in a shared nothing environment.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
slightwv (䄆 Netminder) Commented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
slightwv (䄆 Netminder) Commented:
If your Oracle database is waiting to write or read from log files, it isn't configured properly.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
Geert GOracle dbaCommented:
not necessarily ...
i'd advise against it, but you can switch of the archive logging in oracle

has some drawbacks, like no hot backups
0
marrowyungSenior Technical architecture (Data)Author Commented:
"but you can switch of the archive logging in oracle
"

how about replication ? it has to use a lot of log operation, right ?
0
Geert GOracle dbaCommented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"If the system is configured properly this shouldn't happen.
"
any URL on this on what is good log configuration?
0
slightwv (䄆 Netminder) Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.