Go Discussion on the similarities and differences between Oracle and DB2 in database high availability technology

Original: http://www.talkwithtrend.com/Article/178339

In the process of database construction, high availability is one of the most important concerns in the construction of data Center database in every enterprise, which is directly related to business continuity and stability. To do this job well, we must learn from its underlying principles, mechanisms, architecture and other aspects of deep understanding, in-depth analysis, in-depth comparison to know how we should practice. The following key points are not just what every DBA should be thinking about, but also what people in the enterprise IT architecture planning and building must know and know.

Here are some typical questions and puzzles about the similarities and differences between Oracle and DB2 in database high-availability technologies to help you better understand the differences between the two.

Specific as follows:

1. Differences in database object configuration.
2. High-availability configuration of the database.
3. Differences in database storage mechanisms.
4. The difference of database disaster-tolerant technology.
5. Differences in database lock mechanisms.

First, about the database object concept and other aspects of the difference?

Point One,
DB2 similar to the concept of management containers, is an instance can have multiple databases, the libraries are independent of each other. Oracle is an instance where only one database can run, and a database runs under multiple instances in a clustered environment, similar to the concept of a running machine.

Point two,
DB2 's instance and database are a one-to-many relationship, that is, there can be multiple databases under an instance; Oracle's instance and database are a single-to-one relationship.

Second, about the database arbitration mechanism and principle differences? Oracle Quorum algorithm:

There are two very important rules: 1. Ensures that the subset of nodes in the cluster sub-set that is isolated has the largest number surviving. 2. When the number of quorum votes obtained by a subset of the isolated cluster is equal, the small number of instances is guaranteed to survive.

MySQL Galera arbitration mechanism:

When the cluster fails, Galera cluster will start a special quorum algorithm to elect a node as the primary node, the number of members in the cluster determines the number of votes in the quorum quorum (preferably singular), when a node failure no longer belongs to the cluster, Galera will start an arbitration election. The default setting is 5 seconds. Galera has an independent process called GARBD to be the arbiter arbitrator
The Galera arbitrator is a member of the cluster that participates in the voting but does not really participate in the duplication.
The establishment of the Galera arbitrator is for the following two purposes:
1, even nodes, the arbiter as a node to make the cluster an odd number, so as to avoid brain crack
2, it can request a continuous application state snapshot, can be used to do the backup
Although the arbiter does not save data, it must be able to flow through all the replication streams, so placing the arbiter in a network environment with poor network connectivity to other nodes can lead to poor performance across the cluster. The arbitrator is inverted and does not affect the operation of the cluster, can be hung at any time a new node up

DB2 Purescale arbitration mechanism:

The use of node QUORUM + tiebreaker way to arbitrate, for the cluster node <=2, the case of a node outage, as long as the quorum disk state is normal and can work normally.

Thirdly, how to view the principle and mechanism of each relational database for storage utilization?

View One (Oracle),
ASM has the ability to automatically stripe and mirror, reduce administrative burdens, and store operations without having to create a LV with the system administrator about time! Performance does not feel much better than bare devices, mainly usability and compatibility with the cluster.

Opinion II (DB2),
1. File system is managed on AIX based on JFS or JFS2, and performance is limited by the chunking structure of the file system itself. 2. Bare devices are referred to the storage for management, performance is mainly by the storage cache and communication interfaces such as fiber interface, switches, as well as server interface restrictions. Another important point is that Oracle ASM's failure group mechanism is doing very well, guaranteeing a flexible and highly available mechanism. DB2 combined with GPFS is a great solution, but after all, GPFS is a container for DB2 after file system mapping, and performance is not as straightforward as ASM. Personal understanding.

View Three (Oracle),
ASM realizes the automatic management and optimization of the host level file system, bare devices and other storage resources, which reduces the DBA's cost of management and performance tuning of LVM. Direct LVM Management is the design of a DBA-customized object such as FS,LV,VG and a disk array for the lowest storage.

Iv. the principle of data replication in database disaster recovery?

Oracle
There are two types of Dataguard synchronization in Oracle, one synchronous and one asynchronous. Here's how DG Works first:
When the user submits the data in the main library, the redo information is first recorded in the redo buffer of the SGA, and when the operation is mentioned LGWR the redo data is written to the redo data file, then the LNS process will transfer redo data from the redo buffer of the main library to the repository in real time. Use RFS to accept data in the standby, pass in standby logfile, and then apply redo data (SQL apply). After the application is finished, RFS returns the information to the main library process, informing the redo that the entry has been completed in the repository and LGWR receiving a confirmation message from the LNS, prompting the submission to succeed.
In the highest availability, if the main library does not receive the confirmation message from the standby application, the Net_timeout value time-out will continue to complete the operation, then the LNS process will not obtain the data in the SGA, only the next day log switch to actively try to obtain the LNS data, If there is still no communication with the standby, it will continue to stop when the Net_timeout parameter is exceeded, and the host transaction continues to complete, but when it is in maximum protected mode, the database operation will be stopped until the Redo acknowledgement message is applied to the standby.

DB2:
The DB2 Hadr with non-purescale environment has four modes of replication Sync,nearsync,async,supersync; Oracle supports up to three modes with maximum performance, maximum protection, maximum availability, and can be summed up in two modes sync,async, I think DB2 in this piece is more finely divided. The replication principle of both databases is based on the process of capture log--->tcp transmission---->redo log.
Best reference articles:
https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1010baosf/

Five, about the database lock mechanism?

Point One,
Oracle implements multiple versions of concurrency control through the SCN, and is based on page granularity.
DB2, the old version seems to have a read consistency lock exists, and is managed by locklist to implement the lock. The later version seems to be MVCC.
Oracle:
1 Write Redo.
2 Write Undo.
3 Modify the data.

At this point, the read request actually reads the historical version from Undo.

Point two,
The concurrency mechanism of Oracle uses different types of locks to control.
There are data locks like tm,tx.
There are memory locks, like Latch,mutex,lock.
In addition TM,TX is not the real lock, inside there is a lock mode is the real lock, null,x,s
Tm,tx and other are the structures of the queuing mechanism.
But in layman's name, everyone called Tm,tx a lock. I'm not correcting it.
So Oracle lock is not a burden, no corresponding management costs! At this point, MS SQL Server is less than Oracle

Minsheng Bank Bull General's article quote:
https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0512niuxzh/

Go Discussion on the similarities and differences between Oracle and DB2 in database high availability technology