Oracle standby uard: using Cold standby to create standby

DataGuard is a database-level HA solution provided by oracle. Its primary functions include redundancy, data protection, and fault recovery. Of course, depending on the configuration, data guard can also have the following features: 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 physical STANDBY applies archive logs of the master database, while logical STANDBY applies SQL statements extracted from the archive logs of the master database. The difference between the two determines that physical STANDBY is consistent with the master database regardless of the logical structure and physical structure, while logical STANDBY only needs to ensure that the logical structure is consistent, when SQL statements are applied to logical STANDBY, the database can be opened.

If the data guard protection mode is divided into three different protection modes:
Maximize performance: This is the default protection mode of data guard. You do not need to receive feedback from standby before the transaction commit on primay. This mode may lose data when the primary fails, but standby has the least impact on the performance of the primary.
Maximize availability: under normal circumstances, the maximum available mode is the same as the maximum protection mode. When standby is unavailable, the maximum available mode is automatically reduced to the maximum performance mode, therefore, the standby fault will not cause primay to be unavailable. If there is at least one standby available, even if the primary is down, data will not be lost.
Maximize protection: the highest protection mode. Transactions on primay must confirm that the redo has been passed to at least one standby before commit. If all standby is unavailable, primary will be suspended. This mode ensures zero data loss.

The following example shows how to create a standby database using cold backup.

1. Demo Environment Description
Primary server:
CentOS Linux4.4 + oracle 10.2.0.1
ORACLE_SID: orcl
Master database ip Address: 192.168.60.144
ORACLE_HOME:/free/oracle/product/10.2.0/db_1/
Standby server:
SUSE Linux9 + oracle 10.2.0.1
ORACLE_SID: orcl
Slave database ip Address: 192.168.60.253
ORACLE_HOME:/free/oracle/product/10.2.0/db_1/

Ii. configuration steps
First, install oracle on primary and standby, and create a database with the same SID as orcl. All the settings are the same. Then stop oracle on the shutdown platform and copy all data files, control files, redo files, and password files on the primary to the corresponding location of the standby machine. In this way, we have two identical oracle servers.

1. Set force logging on primary database
SQL> alter database force logging;
2. Modify the initialization parameters of primary databse. The main parameters added are as follows:
*. Control_files = '/free/oracle/oradata/orcl/control01.ctl', '/free/oracle/oradata/orcl/control02.ctl', '/free/oracle/oradata/orcl/control03.ctl'
*. Service_names = 'primary' # This parameter specifies the service name of the master database.
*. Log_archive_config = 'dg _ CONFIG = (PRIMAYR, STANDBY )'
*. DB_UNIQUE_NAME = 'primary'
*. LOG_ARCHIVE_DEST_1 = 'location =/free/oracle/orabak'
*. LOG_ARCHIVE_DEST_2 = 'service = STANDBY VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = standby lgwr sync affir' # If REOPEN is not specified, the default value is 300.
*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE
*. LOG_ARCHIVE_DEST_STATE_2 = ENABLE
*. LOG_ARCHIVE_FORMAT = 'arc % s % t % r. arc'
*. FAL_SERVER = 'standby'
*. FAL_CLIENT = 'primary'
*. STANDBY_FILE_MANAGEMENT = 'auto'
*. Standby_archive_dest = '/free/oracle/orabak'

LOCK_NAME_SPACE
--- This is a parameter. This parameter must be set when the master database and backup data are on the same server and used as the SID of the backup database.

3. Set primary database to archivelog Mode
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
4. Create a control file for standby database on primary database
SQL> shutdown immedaite;
SQL> startup mount;
SQL> alter database create standby controlfile as '/free/oracle/oradata/orcl/controlstandby. ctl'
SQL> alter database open;
Copy the controlfile to the corresponding location of the standby server.

5. Modify the initialization parameters of standby databse
*. Service_names = 'standby' # This parameter specifies the service name of the slave database.
*. Control_files = '/free/oracle/oradata/orcl/controlstandby. ctl'
*. Log_archive_config = 'dg _ CONFIG = (PRIMARY, STANDBY )'
*. LOG_ARCHIVE_DEST_1 = 'location =/free/oracle/orabak VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = standby'
*. LOG_ARCHIVE_DEST_2 = 'service = primary VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = primary lgwr sync affrem REOPEN = 10' # If REOPEN is not specified, the default value is 300.
# *. LOG_ARCHIVE_DEST_2 = 'service = standby VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = standby lgwr sync reopen = 10'
*. Db_unique_name = 'standby'
*. FAL_CLIENT = 'standby'
*. FAL_SERVER = 'primary'
*. Log_archive_dest_state_1 = 'enable'
*. Log_archive_dest_state_2 = 'enable'
*. STANDBY_ARCHIVE_DEST = '/free/oracle/orabak'
*. STANDBY_FILE_MANAGEMENT = 'auto'
DB_FILE_NAME_CONVERT ---- used when the data file paths of primary and standby are inconsistent
LOG_FILE_NAME_CONVERT ---- used when the data file paths of primary and standby are inconsistent
LOCK_NAME_SPACE ---- SID of the backup database when the master database and backup data are on the same machine
For more information about the parameters, see oracle online documentation.

6. Set tnsnames. ora for the primary database and standby database. The configuration files of tnsnames. ora for the master and slave databases are the same.
Primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.60.144) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = primary) # This primayr is the service name of the master database
)
)

Standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.60.253) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby) # This standby is the service name of the slave Database
)
)

7. Start standby database

SQL> startup nomount
SQL> alter database mount standby database; # The backup database is manually restored when this command is executed.

8. Start primary database
SQL> startup

9. Start standby database to recover manage Mode

SQL> alter database recover managed standby database disconnect;
# After executing this command, the slave database will switch to the automatic recovery mode.
If you want to start to the real-time log Application Mode
SQL> alter database recover managed standby database using current logfile; # Switch the standby database to the timely application log mode, that is, real-time apply. After executing this command, the cursor will stop there.
Or alter database recover managed standby database using current logfile disconnect from session; # After executing this command, the session will be cut off and the execution function will be the same as above.

Before creating a standby logfile, run the following command to create a standby logfile:

Alter database add standby logfile group 4
'/Free/oracle/oradata/orcl st_redo4.log' size 50 M
You also need to modify the LOG_ARCHIVE_DEST_2 parameter configuration of the master database.
Because the LOG_ARCHIVE_DEST_2 parameter of the slave database has been configured in advance, you do not need to configure it here.
Some features of the standby redo file are described as follows:
When mongouard runs in max protection or max availability, standby redo log is required because logs must be instantly transmitted to standby using LGWR log transmission.
In fact, the logs generated by the master database are synchronously written to the redo log of the master database and the standby redo log of the slave database. At the same time, the standby redo log also performs log switching when the master database logs are switched. Therefore, the information in the standby redo log is synchronized with the redo file of the master database in a timely manner. When the slave database recovers, standby redo log is used. Therefore, standby redo log is a bridge for direct synchronization between the master database and the slave database.
The size of the created standby redo log must be equal to the redo size of the master database. We recommend that you create more groups than the redo file size of the master database. This is done in the future, this is also an official oracle recommendation.
However, according to my tests, I created four sets of standby redo logs, and only two groups were actually used.
10. Switch standby database to read only mode

SQL> alter database recover managed standby database cancel; # first, cancel the automatic recovery mode of the slave database.
SQL> alter database open read only;
If you want to switch back to the recover manage Mode
SQL> alter database recover managed standby database disconnect from session;

Iii. Function Switching
Switchover status Switch

First, perform the following operations on primary:
1. Check the switch status first:
SQL> select switchover_status from v $ database
If the value of switchover_status is To standby, you can directly switchover. If it is sessions active, you need To add it after the switchover command.
2. Switch the master database to the slave database:
SQL> alter database commit to switchover to physical standby;
3. Close the database:
SQL> shutdown immediate;
4. boot to the mount status:
SQL> startup mount;
Then operate on standby
1. view the switch status:
SQL> select switchover_status from v $ database;
If the value of switchover_status is To primary, you can directly switchover. If it is sessions active, you need To add with session shutdown after the switchover command
2. Switch the slave database to the master database:
SQL> alter database commit to switchover to primary;
3. Close the slave database:
SQL> shutdown immediate;
4. Restart the slave Database
SQL> startup;
TIPS: To achieve switchover smoothly, it is best to set initialization parameters for primary and standby on each server, although some parameters only work on one of primary or standby.
Failover Status Switch
Switch on the slave database:
1. First stop the automatic recovery status of the slave database:
SQL> alter database recover managed standby database finish;
If standby redo log is not used, run the following command:
SQL> alter database recover managed standby database finish skip standby logfile;
2. Switch the slave database to the master database:
SQL> alter database commit to switchover to primary;
3. Close the database:
SQL> shutdown immediate;
4. Start the database:
SQL> startup;
Active status switch:
Active is extended from 8i, but it is not recommended to use:
Perform switchover on the slave database:
1: alter database recover managed standby database cancel;
2: alter database activate standby database;
3: shutdown immediate

To sum up the differences between Failover and Switchover:
In the 9i pluuad environment:
1: To run the Switch Over command, it must be Primary normal, and Primary must Switch to standby first. Then standby can switch to primary.
2: If you need to make a primary problem and standby can take over, you must make a failover instead of a SwitchOver.
Failover:
Offline the primary database and the standby database online. This operation is caused by system and software failure. Even if redo logs are applied to the standby database, data may be lost unless the standby database runs in the guaranteed protection mode.
The instance must be restarted when the original primary database is used again.
Other backup databases also need to restart the instance.
Switchover:
Deliberately offline the primary database and online the other standby database, which can be switched to the standby database without synchronization. For example, you can use Switchover to smoothly upgrade the system. Even if no redo logs are applied to the standby database, data will not be lost.
The database does not need to restart the instance. This allows the primary database to restore its functions almost immediately on the standby database, so regular maintenance can be performed without interrupting the operation.
The difference between Failover and Switchover is:
When a Failover occurs and the slave database switches to the master database, it loses all the capabilities of the slave database. That is to say, it cannot return to the backup mode. Switchover can, but the slave database can switch to the master database, you can switch from the primary database to the standby database.
Iv. Related views
V $ archive_dest
V $ archive_dest_status
V $ log_history
V $ archvied_log
V $ managed_standby
V $ archive_gap

5. FAQs

1. Logs cannot be transferred
SQL> select dest_name, status, error from v $ archive_dest;
Check whether the status of the corresponding archive path is valid; otherwise, process the path based on error information.
2. You cannot use alter database to rename data file.
In standby, when standby_file_management is set to auto, the following operations are not allowed
Alter database rename
Alter database add/drop logfile
Alter database add/drop standby logfile member
Alter database create datafile
3. switchover failed
SQL> alter database commit to switchover to physical standby
Error: ORA-01093: alter database close only permitted with no session connected
Check the Active session that caused the error.
SQL> select sid, process, program from v $ session where type = 'USER' and
Sid <> (select distinct sid from v $ mystat );
Then combine the detected sid with the v $ session view and use
Alter system kill session 'sid, serial 'to kill the process and disconnect the session
Or run the following command for switchover:
SQL> alter database commit to switchover to physical standby with session shutdown;
4. An error occurs during report in read only mode of standby database:
Ora-01220: file base sort illegal before database is open.
Possible cause: standby database does not have temporary tablespace
5: When the standby host and the standby host are stopped during operation, the archived logs of the standby host cannot be uploaded to the standby host. After the standby server is started, the archived logs of the host cannot be uploaded to the slave server. You need to restart all the services of the host to upload the logs.
Why do I need to restart the host once? Can I solve this problem without restarting the host?
In this case, check the following items:
(1). show parameter log_archive_dest;
(2). select REOPEN_SECS, MAX_FAILURE from V $ ARCHIVE_DEST;
Check whether the primary node fails to transmit the archived log normally because the standby machine is not started, and the maximum number of failures is allowed. In this case, you can reset the attribute value through alter system set log_archive_dest... to resume normal log transfer.
You can also manually send related logs to the backup server,
For physical standby: alter database register logfile '/xxx/arcr_xxx.arc'
For LOGICAL standby: alter database register logical logfile '/xxx/arcr_xxx.arc'

(3) execute select message from v $ DATAGUARD_STATUS to view the relevant information, and check the related view based on different types of consumer uard to determine the current status. For details, see the official documentation of consumer uard.
 
This article is from the "Technical Achievement dream" blog