Summary of implementation and maintenance of Oracle 9i dataguard

1. dataguard Principle
Once standby is created, mongouard will pass the redo of the primary database to the standby database, and then apply redo in standby to synchronize the database.

There are two types of standby: Physical standby and logical standby
Physical standby provides the same copy (Block-to-block) as the primary database, database schema, and indexes. It directly applies redo to implement synchronization.
This is not true for logical standby. In logical standby, the logical information is the same, but the physical organization and data structure can be different, it is synchronized with the master database by converting the received redo statements into SQL statements, and then executing SQL statements on standby. In addition to disaster recovery, logical standby has other functions, such as query and report.
Dataguard contains three services (log transmission, log application, and role conversion)
The log Transmission Service controls the transmission of redo data (the log is transmitted and the database protection mode is implemented) ------------ the redo data is received through the RFS process on standby.
Log application service automatically applies logs on the one hand, and detects missing redo in standby on the other hand, and automatically queries the lost redo from the master database or other standby.
Several protection modes of dataguard: maximum protection, maximum availability, and maximum performance
The maximum protection means that transactions cannot be committed unless redo is available in at least one standby. If it is unavailable in a standby, the operation of the primary database is stopped.
The maximum availability means that if standby is unavailable, the master database can still process transactions, but after the problem is corrected, standby and the master database will be re-synchronized. The problem is that some data may be lost when a failover is necessary before synchronization.
The maximum performance is that the master database does not wait for standby to submit the operation.
Possible physical standby modes: Read-only mode (Open readonly) and recovery mode (mananged recovery)
2. Physical Volume uard implementation
Master database preparation: Force logging, enable archiving, a local archiving destination.
Create a standby database:
A. Close the master database, cold back up the data files, log files, and password files of the master database, start the master database, and create the standby control file on the master database: Alter database create standby controlfile as 'file'
B. Prepare the parameter file and copy the parameter file, backup master database file, and standby control file to the standby system.
The parameters to be changed are: standby_archive_dest ----- the archived log storage location received
Db_file_name_convert and log_file_name_convert ----- used only when the master database and standby database are in the same system
Log_archive_dest_1 ----- local archiving destination
Log_archive_dest_2 = 'service = standby' ----- archive to the destination of standby
Standby_file_management = auto
Remote_archive_enable = true
Fal_server
Fal_client
Instance_name ----- when the master database and standby database are in the same system, this parameter is used to differentiate the master database
Lock_name_space ----- when the master database and standby database are in the same system, the Instance name and instance_name of standby are specified to be the same.
C. If it is a Windows system, you need to create a Windows service.
D. Configure tnsnames. ora for the two machines. Both parties can use tnsping.
E. Configure the listener on the master database and standby database.
F. Detect dead connections on the standby system: Set sqlnet. expire_time = 2 in sqlnet. ora
G. Create spfile on standby
H. Start Standby:
Startup nomount
Alter database Mount standby Database
I. initialize log application service
Alter database recover managed standby database disconnect from session;
3. Maintenance of dataguard
A. Log shipper Service
In some cases, you may need to have a time interval between the archive log and the application log. You can specify the delay = minutes attribute in the log_archive_dest_n parameter on standby.
The Log Size on standby should be the same as that on the master database. In addition, there should be more log groups on standby than on the master database, because the archiving operation can be completed in time. That is, the RFS (log receiving) process does not have to wait for the log to be archived before using the standby log. The simplest way to determine whether a log group is sufficient is to view the warning log and RFS trace files.
To add a standby log file, follow these steps:
Use the Add standby logfile clause in the alter database statement. Related views v $ standby_log, V $ logfile
To add a standby log group, follow these steps:
Alter database add standby logfile group 10 ('file name 1', 'file name 2') size m for multiple standby shared archiving log file destinations, in some cases, you must specify the dependency attribute of the log_archive_dest_n parameter. This attribute indicates that the destination depends on the successful archiving of the parent destination.
The log_archive_dest_n parameter can also specify the reopen, max_failures, sync, and async attributes. Specify the lgwr or arch attribute for this parameter to select whether to use the lgwr or arch process to send logs.
The processes used to receive logs are lgwr, arch, RFS, And FAL. The FAL process is used to fix log seams.
Statement for setting data protection mode: Alter database set standby database to maximun (Protection | availability | performance)
B. Log Application Service
For physical standby, the log application service involves the following processes: RFS, arc, and MRP. MRP is used to manage the recovery process.
Several commands for starting Standby Recovery: Alter database recover managed standby database (start foreground session); Alter database recover managed standby database disconnect from SESSION (start background session, that is to say, the session can continue to do other things); Alter database recover managed standby database cancel (stop the log application ).
You can query view v $ managed_standby to view the log application status.
C. Data File Management
When a new data file is created in the master database, you can define the parameter standby_file_management as auto to allow standby to automatically create the data file. If the directory structure of the data file in the master database and standby is different, you can set db_file_name_convert to convert the file name in the master database to the file name in standby. If standby_file_management is set to auto, data files and log files cannot be renamed or created on standby.
Every minute, the master database will ask if standby has a gap. This behavior is called heartbeat.
You can query the view v $ archived_gap. If a gap exists, you can copy the log file to standby from the master database and then register the relevant file to standby. The specific method is as follows: alter database register logfile 'file '. Of course, you can also set the fal_server and fal_client parameters to enable log application service to automatically handle the gap problem.
For the standby log application service, the following views help to monitor: V $ managed_standby, V $ archived_dest_status, V $ archived_log, V $ log_status, V $ dataguard_status.
You can set the log_archive_trace parameter to trace archives at different levels.

D. role conversion
Oracle supports two forms of role conversion: switchover and Failover
Switchover consists of two steps: first, the master database is converted to standby, and then the standby is converted to the master database.
Switchover preparation: parameters to be changed to complete role conversion (log_archive_dest_n and log_archive_dest_state_n on all standby must be changed); Ensure that the master database and all standby databases are connected; Confirm that no user is connected to the database; in the RAC environment, make sure that only one instance is active. Before the role is switched, the master database instance should be open and the standby instance should be in the Mount status, during the conversion process, the Standby database can also apply archive logs. If standby is readonly, the switchover operation can still be performed, but it takes a little extra time. If the switchover operation contains the logical standby, both the master database instance and the standby instance must be in the open state. The standby mode to be converted to the archive mode for the master database is switched. The log delay on the standby database is canceled.
Switchover step: on the current master database, first confirm that you can perform the switchover operation (select switchover_status from V $ database, if the value is to standby, you can switch, otherwise, you need to check whether the current mongouard configuration is running normally), and then switch the master database to standby (execute alter database commit to switchover to physical standby; in this way, the original control file is backed up and a new standby control file is generated. Then, you only need to restart the database in standby mode.) After the preceding operations are completed, on the original standby database, you need to check whether the switch information of the original master database is received by the target standby (select switchover_status from V $ database; if the result is switchover pending, switching standby to primary is feasible, otherwise you need to check Dataguard configuration is running normally), then execute alter database commit to switchover to primary; Convert standby to primary, and restart the database. It should be noted that if the online redo logs of the database do not exist, the switch statement will automatically create them. However, this will take a long time. Therefore, Oracle recommends that you manually add online redo logs. There are two ways to manually add online redo logs: the first is to copy the logs from the original master database to the original standby, and define the parameter log_file_name_convert to associate standby with the new log. The second method is to drop all logs on the target standby and use the alter database add standby logfile command to create new logs. The last step of switchover is to convert the newly generated standby to the recovery mode and archive the new primary.

Failover preparation: parameters to be changed to complete role conversion (log_archive_dest_n and log_archive_dest_state_n on all standby must be changed); Ensure that the master database is connected to all standby; ensure that only one instance in the RAC environment is active. If you want to perform the failover operation, standby is currently running in the maximum protection mode, it should be converted to the maximum performance mode (run the alter database set standby database to maximize performance command ;)

Failover step: first discover and solve the log gap problem, and then copy the logs whose log number is higher than the current database from other libraries and apply the logs, if you have not manually registered new logs, You can execute the following statement alter database recover managed standby database finish; otherwise, the statement to be executed is alter database recover managed standby database finish Skip standby logfile; next, execute alter database commit to switchover to primary; complete the switchover and restart the new master database. If possible, back up the master database.

E. Start standby
Startup nomount;
Alter database Mount standby database;
Alter database recover managed standby database disconnect from session;
F. Disable standby.
First, check whether the instance is in the recovery status: Select process, status from V $ managed_standby;
Cancel Restoration: Alter database recover managed standby database cancel;
Shutdown immediate;
G. Run standby in read-only access mode
Start standby in read-only mode:
Startup nomount;
Alter database Mount standby database;
Alter database open read only;
Convert standby in recovery mode to read only mode:
Alter database recover managed standby database cancel;
Alter database open read only;
H. Change standby from read only to recovery mode
Alter database recover managed standby database disconnect from session;
I. Notes for enabling standby in read-only mode:
Non-temporary tablespaces cannot be used for sorting operations. Temporary tablespaces must be locally managed and only contain temporary files.
If no temporary tablespace exists in the master database when standby is created, create a temporary tablespace in the master database and execute alter system switch logfile to pass redo to standby. If you want to add temporary files to the temporary tablespace of standby, you must first convert standby to read only mode, run the alter tablespace temp1 add tempfile '/disk1/Oracle/dbs/s_temp1.dbf' size 10 m reuse command to add temporary files.

J. You can back up the database through standby backup.
K. Actions on the master database and standby responses:
If you execute alter database clear Unarchived logfile or use resetlogs to open the database, you must recreate standby.
If you execute alter database enable | disable on the primary database, if you change the tablespace status, if you set the parameter standby_file_management to auto and create a tablespace or add data files, you do not need to operate standby.
If you have deleted tablespaces or data files from the master database, you need to delete the relevant data files on the operating system after applying the standby logs.
If you rename the data file on the master database, you also need to rename it on standby (because it is a change to the control file, so there is no log passed in, therefore, both sides must perform the same operation)
If you change the control file on the master database, you need to recreate the standby control file or the standby database.
If you add or delete log files on the master database, you also need to synchronize the changes on standby.
The specific method is: cancel the restoration first. If standby_file_management is auto, change it to manual, and then run the alter database add standby logfile 'prmy3. log 'size 100 K; add a log file or use the alter database drop standby logfile' prmy3 command. log 'Drop the log file and restore the value of standby_file_management.
If you perform nologging, unrecoverable, and other operations on the master database, copy the tablespaces that contain these changes to standby.
If you change the parameter file of the master database, you should also change the parameter file of standby.
L. monitoring process
Select process, client_process, sequence #, status from V $ managed_standby;
M. Monitor the recovery process
Select archived_thread #, archived_seq #, applied_thread #, applied_seq # from V $ archive_dest_status;