Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the basic principles of ORACLE 11G DATA GUARD". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the basic principles of ORACLE 11G DATA GUARD?"

1. DATAGUARD principle

DATAGUARD establishes its reference relationship by establishing a group of PRIMARY and STANDBY.

Once STANDBY is created, DATAGUARD passes the REDO of the master database (PRIMARY) to the STANDBY database, and then uses REDO in STANDBY to synchronize the database.

There are two types of STANDBY: physical STANDBY and logical STANDBY

Physical STANDBY provides exactly the same copy (block-to-block) as the main database, and the database SCHEMA, including indexes, is the same. It directly uses REDO to achieve synchronization.

This is not the case with logical STANDBY. In logical STANDBY, the logical information is the same, but the physical organization and data structure can be different. The way to keep synchronized with the main library is to convert the received REDO into SQL statements, and then execute the SQL statements on the STANDBY. Logical STANDBY has other uses in addition to disaster recovery, such as for users to query and report.

DATAGUARD includes three services (log transfer, log application, role conversion)

The log transfer service controls the transfer of REDO data (transferring logs, implementing database protection mode)-REDO data is received on the STANDBY by initiating the RFS process.

On the one hand, the log application service automatically applies logs, on the other hand, it automatically detects the missing REDO of STANDBY, and automatically queries the missing REDO from the main database or other STANDBY.

Several protection modes of DATAGUARD: maximum protection, maximum availability, maximum performance

Maximum protection means that a transaction cannot be committed unless REDO is available in at least one STANDBY. If it is not available in a STANDBY, the operation of the primary database is stopped. It is usually subject to more constraints and is not very common in the production environment (poor performance-to-price ratio).

The maximum available is that if the STANDBY is not available, the primary database can still handle transactions, but after the problem is corrected, the STANDBY and the primary database are resynchronized. One such problem is that when there is FAILOVER before resynchronization, some data may be lost.

The maximum performance is that the commit operation of the primary database does not wait for STANDBY. PRIMARY and STANDBY are loosely coupled, and the data protection level is low.

Possible modes of physical STANDBY: read-only mode (OPEN READONLY) and recovery model (MANANGED RECOVERY)

2. Brief process of physical DATAGUARD implementation

Preparation for the master database: FORCE LOGGING,ENABLE ARCHIVING, a local archive destination.

Create a STANDBY database:

1. Close the main library, cold backup the main library data files, log files, and password files, then start the main library, and create the control file of STANDBY on the main library: alter database create standby controlfile as' filename'

two。 Prepare the parameter file and copy the parameter file, backup main library file, and STANDBY control file to the STANDBY system.

The parameters that need to be changed are: the archive log storage received by standby_archive_dest--

Db_file_name_convert and log_file_name_convert-- are used only when the main library and the STANDBY library are on the same system

Log_archive_dest_1-- local archive destination

Log_archive_dest_2='service=standby'-- archived to the destination of STANDBY

Standby_file_management=auto

Remote_archive_enable=true

Fal_server

Fal_client

Instance_name-- this parameter is used to distinguish between the main library and the STANDBY library when they are on the same system.

Lock_name_space-- specifies that the instance name of STANDBY is the same as INSTANCE_NAME when the main library and the STANDBY library are on the same system

3. If it is a WINDOWS system, you need to create a WINDOWS service.

4. Configure the tnsnames.ora of two machines, and both sides can communicate with tnsping

5. Configure snooping on the main library and STANDBY library

6. Activate dead connection detection on STANDBY systems: set sqlnet.expire_time=2 in sqlnet.ora

7. Create a SPFILE on STANDBY

8. Start STANDBY:

Startup nomount

Alter database mount standby database

9. Initialize the log application service

Alter database recover managed standby database disconnect from session

3DATAGUARD maintenance

1. Log shipping service

In some cases, you may need to have a time interval between the archive log and the application log, and you can specify the delay=minutes attribute in the parameter log_archive_dest_n on the STANDBY.

The logs on the STANDBY should be the same size as the logs on the main database, and there should be more log groups on the STANDBY than on the main database, because this will allow time for the archiving operation to complete. That is, the RFS (log receiving) process does not have to wait for the log to be archived before using the log of STANDBY. The easiest way to determine whether the log group is sufficient is to look at the warning log and RFS's TRACE file.

Ways to add STANDBY log files:

The add standby logfile clause is used in the alter database statement. Dependent view vstandbylog,vlogfile

Ways to add STANDBY log groups:

For multiple STANDBY shared archive log file destinations, it is sometimes necessary to specify the dependency attribute of the log_archive_dest_n parameter for alter database add standby logfile group 10 ('filename 1') size 100m, which is used to indicate that the destination depends on the successful archiving of the parent destination.

The parameter log_archive_dest_n can also specify reopen, max_failures, sync, async properties. Choose whether to send the log with the LGWR or ARCH process by specifying the LGWR or ARCH attribute to the parameter.

Several processes for log reception are: LGWR,ARCH,RFS,FAL. The FAL process is used to resolve log cracks.

The statement to set the data protection mode is: alter database set standby database to maximun (protection | availability | performance)

two。 Log application service

For physical STANDBY, the logging application service involves the following processes: RFS,ARC,MRP. MRP is to manage the recovery process.

Several commands to start the recovery operation of STANDBY: alter database recover managed standby database (start foreground session); alter database recover managed standby database disconnect from session (start background session, that is, the session can continue to do other things); alter database recover managed standby database cancel (stop logging application).

You can view the log application through the query view v$managed_standby.

3. Data file management

When the main library creates a new data file, you can define the parameter standby_file_management as auto, and let standby also create the data file automatically. If the directory structure of the data files of the main library is different from that of standby, you can set db_file_name_convert to convert the file name on the main library to the file name on standby. If standby_file_management is set to auto, you cannot rename or create data files or log files on standby.

Every minute the main library asks standby if there is a gap, and this behavior is called heartbeat.

You can query the view varchivedgap, and if you find GAP, you can copy the log file to standby from the main library, and then register the related file with standby by: alterdatabaseregisterlogfile' file name'. Of course, you can also set the parameter falserver,falclient to let the log application service automatically deal with gap problems. For the STANDBY logging application service, the following views are useful for monitoring: vmanaged_standby,varchiveddeststatus,varchived_log,vlogstatus,vdataguard_status.

You can set the parameter log_archive_trace to trace the archive at different levels.

4. Role conversion

ORACLE supports two forms of role transformation-- switchover and failover

Switchover consists of two steps. First, the main library is converted to STANDBY, and then STANDBY is converted into the main library.

Preparation for switchover: parameters that need to be changed to complete the role transition (log_archive_dest_n and log_archive_dest_state_n on all STANDBY must be changed); ensure that the main library is connected to all STANDBY; confirm that no users are connected to the database; ensure that only one instance is active for the RAC environment Before the role transition, the primary database instance should be in OPEN state, while the STANDBY instance should be in MOUNT state, because in this way, archive logs can also be applied to the STANDBY database during the conversion process, and if STANDBY is READONLY, the switching operation can still be carried out, but it will take a little extra time; if the switching operation includes logical STANDBY, then both the primary database instance and the STANDBY instance must be in OPEN state; the STANDBY to be converted to the primary database will become archived mode Cancel the log delay on this STANDBY library.

Steps of switchover: on the current main library, first confirm that you can perform switchover operation (select switchover_status from vdatabase, if the value is TOSTANDBY, you can switch it, otherwise you need to check whether the current DATAGUARD configuration is running properly), then switch the main library to STANDBY (execute ALTERDATABASECOMMITTOSWITCHOVERTOPHYSICALSTANDBY; so that the original control file is backed up and generate a new STANDBY control file, and then just restart the library to STANDBY mode) After the above operations are completed, on the original STANDBY database, you need to query whether the switching information of the original master database is received by the target STANDBY (if the result of SELECTSWITCHOVERSTATUSFROMVDATABASE; is SWITCHOVER PENDING, it is feasible to switch from STANDBY to PRIMARY, otherwise you need to check whether the configuration of DATAGUARD is running properly), then execute ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; to convert STANDBY to PRIMARY, and restart the library. It is important to note that if the library's online redo logs do not exist, the switch statement will automatically create them, but this will take a long time. Therefore, ORACLE recommends manually adding online redo logs. There are two ways to manually add online redo logs: the first is to copy the log from the original primary library to the original STANDBY and define the parameter log_file_name_convert to associate the standby with the new log. The second method is to DROP all the logs that exist on the target STANDBY and create a new log with the command ALTER DATABASE ADD STANDBY LOGFILE. The final step of switchover is to convert the newly generated STANDBY into a recovery model and archive the new PRIMARY.

Preparation for failover: parameters that need to be changed to complete the role transition (log_archive_dest_n and log_archive_dest_state_n on all STANDBY must be changed); ensure that the main library is connected to all STANDBY; ensure that only one instance is active for the RAC environment; if the STANDBY to be operated by failover is currently running in maximum protected mode, it should be converted to maximum performance mode (through the command ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;)

The steps of failover: first find and solve the problem of log GAP, and then copy the logs with log numbers higher than this library from other libraries and apply them. If you do not register new logs manually, you can execute the following statement, ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;, otherwise the statement that needs to be executed is ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE; and then execute ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY. Complete the switch and restart the new master library and, if possible, make a backup of the master library.

5. Start STANDBY

STARTUP NOMOUNT

ALTER DATABASE MOUNT STANDBY DATABASE

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

6. Close STANDBY

First confirm whether you are in the recovery state: SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY

Cancel restore operation: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

SHUTDOWN IMMEDIATE

7. Let standby run in read-only access mode

Start STANDBY in read-only mode:

STARTUP NOMOUNT

ALTER DATABASE MOUNT STANDBY DATABASE

ALTER DATABASE OPEN READ ONLY

Convert the STANDBY of the recovery model to read only mode:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

ALTER DATABASE OPEN READ ONLY

8. Transform STANDBY from READ ONLY to recovery model

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

i. Issues that you should pay attention to when performing a sort operation for a read-only mode STANDBY:

Sort operations cannot use non-temporary tablespaces, which must be locally managed and contain only temporary files

If the main library does not have temporary tablespaces when creating the STANDBY, you need to create temporary tablespaces on the main library and execute ALTER SYSTEM SWITCH LOGFILE; to pass the redo to STANDBY. If you want to add temporary files to the temporary tablespace of STANDBY, you need to first convert STANDBY to READ ONLY mode and execute the command ALTER TABLESPACE temp1 ADD TEMPFILE'/ disk1/oracle/dbs/s_temp1.dbf' SIZE 10m REUSE; to add temporary files.

9. You can back up the database through the backup of STANDBY.

10. The operation on the main library and the response to STANDBY:

If you use RESETLOGS to execute ALTER DATABASE CLEAR UNARCHIVED LOGFILE or open the database, you must recreate the STANDBY.

If you execute ALTER DATABASE ENABLE | DISABLE on the main library, if you change the state of the tablespace, if you set the parameter STANDBY_FILE_MANAGEMENT to AUTO and create the tablespace or add data files, you do not need to operate on STANDBY.

If you delete tablespaces or data files on the main library, you need to delete the relevant data files on the operating system after the STANDBY log application.

If you rename the data file on the main library, you should also rename it on STANDBY (because it is a change to the control file, no log is passed, so both sides have to do the same)

If you change the control file on the main library, you need to recreate the STANDBY control file or rebuild the STANDBY database.

If you add or delete log files on the main library, you also need to synchronize changes on STANDBY.

The specific method is: first cancel the recovery, if STANDBY_FILE_MANAGEMENT is AUTO, change it to MANUAL, and then use the command ALTER DATABASE ADD STANDBY LOGFILE 'prmy3.log' SIZE 100K; add the log file or drop the log file with the command ALTER DATABASE DROP STANDBY LOGFILE' prmy3.log'DROP, and finally restore the value of the parameter STANDBY_FILE_MANAGEMENT.

If you do operations such as nologging | unrecoverable on the main library, you should copy the tablespaces containing these changes to STANDBY.

If you change the parameter file of the main library, you should also change the parameter file of STANDBY.

III. Monitoring the process

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY

m. Monitor the progress of the restore operation

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS

The main function of DATA GUARD is redundancy. Of course, depending on the configuration, DATA GUARD can also have the following characteristics: high availability, performance improvement, data protection and fault recovery.

DATA GUARD can be divided into physical STANDBY and logical STANDBY. The biggest difference between the two is that the physical STANDBY applies the archive log of the master library, while the logical STANDBY applies the SQL statement extracted from the archive log of the master library. Because of the difference between the two, the physical STANDBY is consistent with the main database both in logical structure and physical structure, while the logical STANDBY only needs to ensure that the logical structure is consistent, and when the logical STANDBY applies the SQL statement, the database can be open.

If divided from the protection mode of DATA GUARD, it can be divided into three different protection modes:

Protection maximization: the configuration of this mode can ensure the synchronization of the primary database and the standby database. In any case, the damage to the primary database will not lead to the loss of submitted data. If there is a problem with the network between the main database and the standby database, or if there is a problem with the standby database itself, it will cause the main database to stop data processing.

Maximize availability: this mode is similar to the above, but also ensures the synchronization of the primary and secondary libraries, except that the primary library can continue to process when the network or standby is unavailable.

Performance maximization: the primary and standby libraries are asynchronous. This mode may lose some data when the main database is corrupted. However, this mode has the least load on the main library, so it has the best performance.

At this point, I believe you have a deeper understanding of "what is the basic principle of ORACLE 11G DATA GUARD". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.