What are the differences between SQL database and oracle database images?

Oracle databases differ significantly from MSSQL data operations, but there is an analogy in image operations. This article describes how MSSQL database images are implemented in Oracle databases. What are the differences between them.

First, the image database in Microsoft SQL database is similar to the backup database in Oracle Database. I'm just talking about something similar. Specifically, we need to consider the differences between different databases in our system. MSSQL acts as an instance. An Instance contains several databases. You must first log on to an instance and then select the database to act on. In Oracle databases, the simple mode (ignoring RAC) only has one database associated with one instance. Therefore, in Oracle databases, the standby database is a snapshot of the primary database. In MSSQL, the image database is only the backup of the selected database, but does not include the proxy, login, task (these or more database projects need to be created or copied separately on the database image) these external data items.

In terms of the number of servers, the configuration of the Oracle master database and the backup database requires at least two. In MSSQL, the minimum data size is 2 or 3, depending on the high availability, high security, and high performance mode you choose.

High Availability mode: this operation mode allows you to synchronize transaction writes on two servers and supports automatic error recovery. To use this option, you must also use a witness server.

High protection mode: This option allows you to synchronize transaction writes on two servers, but error recovery is manual. Because automatic error recovery is not part of this option, it does not use the witness server.

High Performance Mode: This option does not care whether the write operations on the two servers are synchronized, so the performance is improved. When using this option, you can only assume that everything on the backup storage is successfully completed. This option only allows manual error recovery, so it does not use the witness server.

To ensure automatic fault recovery, you need a third server, which can be called an witness (the other two are the primary database and the Image Database). You can treat this witness as a member of the cluster. It provides a 2-to-1 voting capability. When one of my components cannot be reached, it requires error recovery. The witness server must be used only when you want to enable automatic error recovery.

In a transaction of Oracle data, the log buffer is refreshed or written to the redo log before the waste data is written to the data file (ignore write-ahead. This refresh or write to the redo log is necessary, such as when an event such as an instance failure (using the rollback and Rollback Recovery processes) occurs. MSSQL also acknowledges the importance of writing the log buffer to the disk. However, this is called hardening ). First, write the transaction log buffer information to the disk or harden, and then send the log record block to the image database. After the image database receives the log record block, it stores it in a buffer, and then harden the log block in sequence.

When data changes, how does the MSSQL database maintain consistency between the primary database and the backup database?

Oracle users are very familiar with SCN, while MSSQL users use the mongoing_failover_lsn mechanism (roughly speaking, it is a log serial number ). Unlike Oracle, MSSQL separates transactions (two transactions are on two machines), rather than a distributed transaction (you need to wait for the transaction to be committed remotely before committing itself ).

Another similarity, but slightly distorted reflection is the redo log and transaction log. In Oracle, the completed redo log is sent to a remote server and applied to the backup data. In MSSQL, transaction logs are not transmitted, but as I mentioned above, the log buffer data is sent to the network. This leads to another image reflection: backup and recovery modes.

In Oracle, when you are in archive or non-archive mode, these operations are internal. If the archived redo logs are transmitted or submitted to a remote server, the files in the master database are generated in archive mode. In this mode, a small amount of data is allowed to be lost, because recovery can be executed on any point before a fault (no matter what kind of fault) occurs. It is similar in MSSQL, but three statuses need to be selected.
SQL Server books online, like many other online resources, describes the differences between the three recovery modes when using MSSQL. Quick comparison: MSSQL full mode corresponds to archive mode in Oracle; simple mode corresponds to non-archive mode; bulk mode corresponds to direct path insertion, add a prompt, or similar to the nologging mode operation.

According to the description of the above three modes (these three modes are easy to convert and do not need to be shut down or restarted) and the discussion of log buffers and archived redo logs, it is easy to determine that the data recovery mode needs to be set to full model for database images in MSSQL ). The Simple model may also work, but in this mode, a small part of the transaction log is maintained. If the log is deleted in the backup, the entire image process will be broken, because when you send a transaction to an image database, if the log is deleted, this process cannot be completed.

What should I do if the database is damaged?

This is the main purpose of mirroring (or backup): When the primary database is disconnected or encounters a fault, we hope the system can return to the status before or before the backup. How can this be achieved? We can implement it automatically or manually. To achieve this, you need some completed settings. In MSSQL, automatic fault recovery is required to return to the original state in HA mode. The transaction security is full, the data transmission is synchronous, and the server is witnessed. In this mode, the Enterprise Edition database system is also required for running. High Security and high performance can also be achieved in the Standard Edition.
Other versions of MSSQL are available, but these are not as clean as Oracle reflection. These versions include Developer, Workgroup, and SQL Express. For example, the witness server can be any version, but if you want to create a snapshot for the backup server, you need the enterprise or development version.

In the process of setting partners (usually composed of a primary database and an image database), their recovery status starts to take effect. By using the same name, the image is created on the remote/backup storage (the easiest way to configure the database image security wizard), and the image database is set to NORECOVERY, usually it is restored. In MSSQL, database restoration is not available. Therefore, the above settings are not performed and cannot be used as a read-only database by other users.

To avoid this defect, you can create a snapshot for the image so that the image is visible to the user. As I mentioned above, this requires that your database version be enterprise (or development. This means that you need to have knowledge of the snapshot database, know how to store it, and tell the application which database to use. Traditionally, in the. NET environment used by the configuration file, you can create a primary database and a secondary database for fault rollback. If you have configured a backup database in Oracle, you will think it is similar.

Conclusion

This article covers how to better understand how to execute an image or copy on another mainstream RDBMS according to the Oracle method ,. Try to learn and explain how your RDBMS works. getting your attention from another mode helps you understand the operating principles of your current database system. For example, I found that what is very useful is the relationship between the Oracle archive mode and the MSSQL restoration mode. Using some MSSQL terms (partner, master database, witness, image) helps you to construct and identify Oracle database image operations.
To better evaluate how database images work and how they are implemented, you can run two separate MSSQL instances. The operating system XP or 2003 does not matter. Follow the steps in the MSDN books online series. Download or select the AdventureWorks database (similar to the HR/SH database of Oracle. These are not pre-installed), mirror them to the host server. What is presented to you is not only the functional features of another data, but you will also see the operations performed by MSSQL and get correct comments (I usually use the Oracle database, MSSQL is used for testing. In other words, you usually use MSSQL. If you use Oracle for testing, new discoveries will be made ).