Learn data guard step by step

I. Preparations
The initial creation of physical standby or logical standby depends on the primary database, because the most important part of this preparation is the configuration of the primary database.
1. Enable the forced logging mode.
Set the primary database to force logging mode. Use the following statements:
SQL> alter database force logging;

Tip: about force Logging
You must know that some DDL statements can avoid writing redo logs by specifying the nologging clause (the purpose is to increase the speed and sometimes it works). After specifying the database as the Force logging mode, the database records all operations except the temporary tablespace or temporary rollback segment and ignores specified parameters such as nologging. If a statement such as nologging is executed while executing force logging, force logging waits until all such statements are executed. Force logging is saved as a fixed parameter in the control file, so it is not affected by restart and other operations (only once). If you want to cancel, you can use the alter database no force logging statement to disable forced logging.

2. Create a password file (if it does not exist)
Note that all databases in the same data guard configuration must have independent password files, in addition, the Sys users of all database servers in the same data guard configuration must have the same password to ensure smooth transmission of redo data, because the redo transmission service transmits redo data through authenticated network sessions, the session uses the Sys User Password contained in the password file for authentication.

3. Configure standby redo log
For the maximum protection and maximum availability modes, standby redo log must be configured for the standby database, and Oracle recommends that all databases use the lgwr async mode for transmission, of course, you may not know what lgwr async is. It doesn't matter. You will soon know.

Oracle recommends that you consider the standby redolog configuration when creating standby. Standby redologs is very similar to online redologs. It should be said that the two are just different service objects, and the command formats of other parameter attributes and even operations are almost the same, you can use the idea of creating online redologs when designing standby redologs, such as multiple file groups and multiple file redundancy in each group. In addition, Oracle provides some standard suggestions as follows:
Make sure that the file size of standby redo log is the same as that of the online redo log file of the primary database.
This is easy to understand, just for the convenience of reception and application.
Create an appropriate log Group
Generally, the number of standby redo log files is at least one more than the number of online redo log files in the primary database. The recommended number of standby redo log groups is based on the number of threads in the primary database (the number of threads here can be understood as the number of RAC nodes in the RAC structure ).
There is a recommended formula for reference: (number of log groups per Thread + 1) * Maximum number of threads
For example, the primary database has two threads, and each thread is allocated with two groups of logs, the number of standby log groups is recommended to be six groups. This formula can be used to reduce the possibility of lgwr process lock on the primary database instance.
Tip: the logical standby database may need to add more standby redo log files (or archive processes) depending on the workload, because the logical standby needs to write online redo log files at the same time.

The standby redo log operation method is almost the same as the online redo log operation method, except that a standby keyword needs to be specified during Creation or Deletion, for example, adding:
SQL> alter database add standby logfile Group 4 ('e: \ ora10g \ oradata \ jsspdg \ standbyrd01.log') Size 20 m;

Deletion is also simple:
SQL> alter database drop standby logfile group 4;

In terms of reliability, it is recommended that standby redologs be created in the primary database, so that the normal operation of primary as standby will not be affected once the switchover occurs.

Verify that the standby redo log file group is successfully created
For example:
SQL> select group #, thread #, sequence #, archived, status from V $ standby_log;

4. Set initialization parameters
For the primary database, you need to define several initialization parameters for the primary role to control the redo transmission service, there are also several additional standby role parameters that need to be added to control the acceptance of redo databases and applications (switchover/failover after primary/standby roles may be exchanged, therefore, we recommend that you configure initialization parameters related to the two types of roles ).

 

The following parameters are initialization parameters related to the primary role:
Db_name	Make sure that db_name is the same for all databases in the same data guard. Example: db_name = jssweb
Db_unique_name	Specify a unique name for each database. Once specified, this parameter will not change unless you modify it. Example: db_unique_name = jssweb
Log_archive_config	This parameter lists all db_unique_name (including primary dB and standby dB) in the same data guard using the dg_config attribute and is separated by commas (,). For example, log_archive_config = 'db _ Config = (jssweb, jsspdg )'
Control_files	The path of the control file.
Log_archive_dest_n	Path for generating the archive file. This parameter is very important, and there are a lot of attributes and sub-parameters (not listed here. I will explain it separately later. If you are curious, we recommend that you directly query the official Oracle documentation. Chapter 14th of the Data guard White Paper specifically introduces the attributes of this parameter and the functions and settings of sub-parameters ). For example: log_archive_dest_1 = 'location = E: \ ora10g \ oradata \ jssweb valid_for = (all_logfiles, all_roles) db_unique_name = jssweb'
Log_archive_dest_state_n	The specified parameter value is enable, which allows the redo transmission service to transmit redo data to the specified path. This parameter has a total of four attribute values, with different functions.
Remote_login_passwordfile	We recommend that you set the parameter value to exclusive or shared. Ensure that all dB servers in the same data guard configuration have the same sys password.
Log_archive_format	Specifies the format of the archive file.
Log_archive_max_producesses	Specifies the number of archiving processes (1-30). The default value is usually 4.
The following parameters are related to the standby role. We recommend that you set them in the initialization parameters of the primary database so that after role transition (primary is converted to standby) the system can run properly:
Fal_server	Specifies a database Sid, which is usually the primary role. Example: fal_server = jssweb
Fal_client	Specifies a database Sid. Generally, this database is a standby role. For example: fal_client = jsspdg prompt: FAL is the abbreviation of fetch archived log
Db_file_name_convert	This type of parameter has been described many times during duplicate replication and table space transmission. This parameter has the same function and format as the parameter of the same name in the preceding content.
Log_file_name_convert	Same as above
Standby_file_management	If the data file of the primary database is modified (such as new database creation or renaming), modify it in standby according to the settings of this parameter. Auto indicates automatic management. If it is set to manual, manual management is required. Example: standby_file_management = auto

 

Note: The parameters listed above are only parameters that may be related to the primary/standby roles, and some basic parameters such as * _ DEST, * _ size and other database-related parameters also need to be modified according to the actual situation during specific configuration.

5. Make sure the database is in archive mode.
SQL> archive log list;
Database Log mode archiving Mode
Enable automatic archiving
.......

If the current primary database is not in archive mode, you can use the following command to set the database to archive mode:
SQL> startup Mount;
SQL> alter database archivelog;
SQL> alter database open;

2. manually create physical standby
1. Create a backup (manually copy the data file or use RMAN) --- The primary database operation

2. Create a control file-primary database operation
Use the following statement to create a control file for the standby database:
SQL> alter database create standby controlfile as 'd: \ backup \ jsspdg01.ctl ';
Note: The control file usually requires multiple copies. You can either manually copy the above files or create several more files by using the command. In addition, make sure that the primary database does not have any structural changes (such as adding tablespaces) during the period when the standby database is created after the control file is created ), otherwise, problems may occur during synchronization between primary and standby.

3. Create an initialization parameter file
Create a client initialization parameter file
For example:
SQL> Create pfile = 'd: \ backup \ initjsspdg. ora 'from spfile;

Modify parameters in the initialization parameter file
Modify the settings according to the actual situation. Pay attention to the attribute configuration of the primary and standby roles, and pay attention to the file path.

4. copy the file to the standby server
At least three parts: data files, control files, modified initialization parameter files. Note the path.

5. Configure the standby Database
If you have read the "Learn RMAN step by step" series before think twice, read the "Duplicate copy Database" series, or read the "transfer tablespace copy data" series, you should be familiar with creating a new database. The following describes the procedure:
1). Create a New oracleservice (required in Windows ).
2) create a password file. Ensure that the password is consistent with that of the primary database.
3) configure the listener and start
4) Modify tnsnames. ora of primary and standby and add the corresponding net service name respectively.
5). Create the server-side Initialization File

6. Start standby
Note: As we have mentioned earlier, physical standby can be opened in read-write mode and in read-only mode in some cases. Therefore, by default, load it to the Mount status.
SQL> startup Mount;

Start the redo Application
SQL> alter database recover managed standby database disconnect from session;

Start real-time applications
SQL> alter database recover managed standby database using current logfile disconnect from session;

Tip: the disconnect from session clause is not mandatory. This clause is used to specify to automatically exit before the command operator after the application is started. If this clause is not specified, the current session will remain in the redo application, if you want to perform other operations, you can create only one connection.

7. Stop standby
Under normal circumstances, we should stop the redo application first. You can use the following statement:
SQL> alter database recover managed standby database calcel;

Then stop the standby database.
SQL> shutdown immediate;

Of course, it's okay if you have to shut down the system directly. The DG is originally used for Disaster Tolerance. Don't say that you have stopped standby, that is, you are not afraid to directly unplug the power source.

Physical standby creation example
To minimize hardware requirements, the data guard created here is on the same machine, but its creation process is no different from that of multiple machines. The example for demonstration is sufficient. We configure the primary database and standby database in two phases, as shown below:

1. Primary Database Configuration and related operations

1. confirm that the master database is in archive Mode
SQL> archive log list;
Database Log mode archiving Mode
Enable automatic archiving
Archive end point E: \ ora10g \ oradata \ jssweb
Oldest online log sequence 148
Next archive log sequence 150
Current Log sequence 150
SQL>

2. Set the primary database to force logging mode. Use the following statements:
SQL> alter database force logging;

The database has been changed.

3. Create a standby Database Control File
SQL> alter database create standby controlfile as 'd: \ backup \ jsspdg01.ctl ';

The database has been changed.

4. Create a primary database client initialization parameter file
Note: There are many modifications here. For convenience, we first create and modify pfile, and then re-create spfile through pfile. Of course, you can also directly modify the spfile content through the alter system set command.
SQL> Create pfile from spfile;

The file has been created.

Copy the initialization parameter file as the client initialization parameter file of the standby database.
SQL> host copy E: \ ora10g \ product \ 10.2.0 \ db_1 \ database \ initjssweb. ora D: \ backup \ initjsspdg. ora
1 file has been copied.

SQL>

Modify the client initialization parameter file and add the following content:
Db_unique_name = jssweb
Log_archive_config = 'dg _ Config = (jssweb, jsspdg )'
Log_archive_dest_1 = 'location = E: \ ora10g \ oradata \ jssweb \ valid_for = (all_logfiles, all_roles) db_unique_name = jssweb'
Log_archive_dest_2 = 'service = jsspdg lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = jsspdg'
Log_archive_dest_state_1 = Enable
Log_archive_dest_state_2 = Enable
Remote_login_passwordfile = exclusive

# -------- Configure the parameters of the standby role for role conversion
Fal_server = jsspdg
Fal_client = jssweb
Db_file_name_convert = 'oradata \ jsspdg ', 'oradata \ jssweb'
Log_file_name_convert = 'oradata \ jsspdg ', 'oradata \ jssweb'
Standby_file_management = auto

Rebuilding spfile through pfile
SQL> shutdown immediate
...
SQL> Create spfile from pfile = 'initjssweb. ora ';

The file has been created.

5. Copy data files to the standby server (Diverse methods, not detailed)
Note that you need to copy all data files, backup control files, and client initialization parameter files.
6. Configure listener and net service names ).
Restart listener after completion:
E: \ ora10g> LSNRCTL stop
E: \ ora10g> LSNRCTL start

Use tnsping to test whether tnsnames is correct and valid:
E: \ ora10g> tnsping jssweb
...
Attempting to contact (description = (address = (Protocol = TCP) (host = JSS) (Port = 1521) (connect _
Data = (Server = dedicated) (SERVICE_NAME = jssweb )))
OK (30 ms)

E: \ ora10g> tnsping jsspdg
...
Attempting to contact (description = (address = (Protocol = TCP) (host = JSS) (Port = 1521) (connect _
Data = (Server = dedicated) (SERVICE_NAME = jsspdg )))
OK (10 ms)

Ii. Standby Database Configuration and related operations

1. Create a New oracleservice through oradim
2. Create a password file and ensure that the password of sys is consistent with that of the primary database.
E: \ ora10g> orapwd file = E: \ ora10g \ product \ 10.2.0 \ db_1 \ database \ pwdjsspdg
. Ora Password = verysafe entries = 30

3. Create a directory
E: \ ora10g \ product \ 10.2.0 \ admin \ jsspdg> mkdir adump

4. copy the file and do not describe it too much.

5. Modify the initialization parameter file
Add the following parameters
Db_unique_name = jsspdg
Log_archive_config = 'dg _ Config = (jssweb, jsspdg )'
Db_file_name_convert = 'oradata \ jssweb', 'oradata \ jsspdg'
Log_file_name_convert = 'oradata \ jssweb', 'oradata \ jsspdg'
Log_archive_format = Log % T _ % S _ % R. Arc
Log_archive_dest_1 = 'location = E: \ ora10g \ oradata \ jsspdg \ valid_for = (all_logfiles, all_roles) db_unique_name = jsspdg'
Log_archive_dest_state_1 = Enable

# --- The following parameters are used for Role Switching
Log_archive_dest_2 = 'service = jssweb lgwr async valid_for = (online_logfiles, primary_role) B _unique_name = jssweb'
Log_archive_dest_state_2 = Enable
Remote_login_passwordfile = exclusive
Fal_server = jssweb
Fal_client = jsspdg
Standby_file_management = auto
Change the * _ DEST path at the same time.

Use this pfile to create a spfile
SQL> Create spfile from pfile = 'd: \ backup \ initjsspdg. ora ';

The file has been created.

6. Start standby to mount
SQL> startup Mount;
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1289484 bytes
Variable Size 62915316 bytes
Database buffers 96468992 bytes
Redo buffers 7098368 bytes
The database has been loaded.

7. Start the redo Application
SQL> alter database recover managed standby database disconnect from session;

The database has been changed.

8. View synchronization status
Connect to the primary database first
SQL> show parameter instance_name;

Name type value
-----------------------------------------------------------------------------
Instance_name string jssweb
SQL> alter system switch logfile;

The system has been changed.

SQL> select max (sequence #) from V $ archived_log;

Max (sequence #)
--------------
51

Connect to the standby Database
SQL> show parameter instance_name;

Name type value
-----------------------------------------------------------------------------
Instance_name string jsspdg
SQL> select max (sequence #) from V $ archived_log;

Max (sequence #)
--------------
51

9. suspend the application
Use the following statement to suspend the redo application.
SQL> alter database recover managed standby database cancel;

The database has been changed.
Note: At this time, only the redo application is temporarily used. Instead of stopping the standby database, standby will still keep receiving the archive that the application will not receive until you start the redo application again.
Physical standby role conversion
In section 1st, we mentioned role switching. We also heard that it is simple but widely used, and we also guess that it belongs to the interaction between primary and standby, therefore, operations are required on both the primary and standby databases (one of them), and the switchover is divided into switchover and failover. The former is lossless switching and data will not be lost, the latter may lose data, and the original primary database is no longer part of the data guard configuration after the switchover. different standby (logical or physical) processing methods are also different. En, the content is also quite a lot. We should first understand the concept and then confirm it through actual practice.

Preparations before role conversion
Check the initialization parameters of each database. Check that the initialization parameters related to different roles are correctly configured.
Make sure that the standby server of the primary database is in archivelog mode.
Make sure that the temporary files of the standby database exist and match the temporary files of the primary database.
Make sure that only one RAC instance of the standby database is open. (For standby databases with RAC structures, only one instance can be started during role conversion. All other RAC instances must be shut down and restarted after role conversion)

Switchover:
Lossless conversion is usually triggered manually or scheduled by users, such as hardware upgrades and software upgrades. Generally, the workload it brings to you is very small and predictable. Its execution is divided into two stages. The first step is to convert the primary database to the standby role. The second step is to convert the standby database (one) to the primary role. The primary and standby are only simple role swaps, this also confirms our previous speculation that role conversion is a primary/standby interaction.

Failover:
Unpredictable causes cause the primary database to fail and cannot be recovered in a short period of time. If this is the switch, you should be careful. It may be just a false shock, or even estimate the number of brain cells that you may lose, however, if you are not lucky and do not have a complete Backup recovery policy and the primary data is not in the maximum data protection or highest availability mode, black and Black will be useless, and the table will be too sad, let sansigg comfort you. In this case, data loss may be inevitable, and if the fault fails to be repaired, it even lost the chance of quickly restoring to standby. Sorry, how does your brain seem to be taking off water? Is it a powerful skin cleanup liquid, your face suddenly becomes white ~~~~

Before executing failover, try to copy the available redo of the original primary database to the standby database.
Note: If you want to change the role's standby mode to maximum protection mode, you need to first switch it to maximum performance mode (what, you don't know how to convert the mode? Oooo, right. We haven't performed any operations yet. This is not complicated and will be discussed in special chapters later). Here, we will first provide a description to convert the standby database to maximize performance and execute the following SQL:
SQL> alter database set standby database to maximize performance;
After standby switches to the new primary, you can change the protection mode of the database at will.

Do you have any questions about why standby cannot be in maximum protection mode? This is actually a good understanding. We introduced the features of the three Protection modes in the first section. The students with good brains should be impressed. The maximum protection mode must ensure that no data is lost, therefore, it requires a high level of redo data consistency for committed transactions. In addition, if the primary database in maximum protection mode still has data transmission with the standby database, in this case, the alter database statement fails to change the standby database protection mode, which is also determined by the maximum protection mode feature.

The following describes the processes of switchover and failover:

1. switchover of physical standby
Note the steps.
1. Check whether switchover operations are supported-primary database operations
Log on to the primary database and query the switchover_status column in the V $ database view.
E: \ ora10g> set oracle_sid = jssweb
E: \ ora10g> sqlplus "/As sysdba"
SQL * Plus: Release 10.2.0.3.0-production on Thursday December 13 09:41:29 2007
Copyright (c) 1982,200 6, Oracle. All rights reserved.

Connected.

SQL> select switchover_status from V $ database;
Switchover_status
--------------------
To standby

If the column value is "to standby", it indicates that the primary database supports switching to the standby role. Otherwise, you need to re-check the data guard configuration, for example, check whether the parameter values such as log_archive_dest_n are correct and valid.

2. Start the switchover-primary database operation.
First, convert the primary role to the standby role and use the following statements:
SQL> alter database commit to switchover to physical standby;

The database has been changed.
After the statement is executed, the primary database is converted to the standby database, and the control file is automatically backed up to the trace.

3. Restart to mount-the original primary database operation
SQL> shutdown immediate
ORA-01507: Database not loaded

The Oracle routine has been disabled.
SQL> startup Mount
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1289484 bytes
Variable Size 104858356 bytes
Database buffers 54525952 bytes
Redo buffers 7098368 bytes
The database has been loaded.

4. Check whether switchover operations are supported-standby database operations to be switched
After the original primary is switched to the standby role, check the switchover_status column of the standby database to be converted to see if role conversion is supported.
E: \ ora10g> set oracle_sid = jsspdg
E: \ ora10g> sqlplus "/As sysdba"
SQL * Plus: Release 10.2.0.3.0-production on Thursday December 13 10:08:15 2007
Copyright (c) 1982,200 6, Oracle. All rights reserved.

Connected.

SQL> select switchover_status from V $ database;
Switchover_status
--------------------
To primary

SQL>
In this case, the value of the switchover_status column of the standby database should be "to_primary". If not, check the settings in the initialization parameter file and prompt you to change the value compared to the initialization parameter of the original primary database.

5. Convert the role to primary-to be converted to the standby database operation
Use the following statement to convert a standby role to a primary role:
SQL> alter database commit to switchover to primary;

The database has been changed.
Note: The physical standby to be converted can be in Mount or open read only mode, but not open read write mode.

6. Complete the conversion and open the new primary database.
SQL> alter database open;

The database has been changed.
Note: If the database is in open read-only mode, shutdown and start up directly.

7. verify it.
New primary database
SQL> show parameter db_unique

Name type value
-----------------------------------------------------------------------------
Db_unique_name string jsspdg
SQL> select max (sequence #) from V $ archived_log;

Max (sequence #)
--------------
67

SQL> alter system switch logfile;

The system has been changed.

SQL> select max (sequence #) from V $ archived_log;

Max (sequence #)
--------------
68
New standby Database
SQL> show parameter db_unique

Name type value
-----------------------------------------------------------------------------
Db_unique_name string jssweb
SQL> select max (sequence #) from V $ archived_log;

Max (sequence #)
--------------
68

Conversion successful.

Ii. Failover of physical standby
Notes:
After failover, the original primary database is no longer part of the data guard configuration by default.
In most cases, other logical/physical standby databases are not directly involved in the Failover process, so these databases do not need to perform any operations.
In some cases, after the new primary database configuration, you need to re-create all other standby databases.
In addition, if the standby of the role to be converted is in maximum protection or maximum availability mode, archive logs should exist continuously. In this case, you can directly execute the archive logs in step 1, otherwise, we recommend that you start from step 1.
Generally, failover means that the primary database is paralyzed and cannot be started at least. Therefore, this type of switchover basically does not require any operation on the primary database. Therefore, if primary and standby are mentioned in the following steps, it is recommended that you execute primary if it can be used again. Even if it can be used, it does not matter, does not affect standby database switching :)

1. Check whether the archive files are continuous
Query the V $ archive_gap view of the standby database to be converted and check whether the archive file is connected:
SQL> select thread #, low_sequence #, high_sequence # from V $ archive_gap;

Unselected row
If a record is returned, copy the corresponding archive file to the standby server to be converted according to the listed record number. This step is very important. You must ensure that all generated archive files exist on the standby server. Otherwise, an error may occur when the data is inconsistent. After the file is copied, use the following command to add it to the data dictionary:
SQL> alter database register physical logfile 'filespec1 ';

2. Check whether the archive file is complete.
Run the following statements in Primary/Standby:
SQL> select distinct thread #, max (sequence #) over (partition by thread #) A from V $ archived_log;

This statement obtains the maximum number of files archived by each thread in the current database. If the maximum number of files archived by primary is different from that of standby, you must copy the archive file corresponding to the number to be converted to the standby server. However, since it is a failover, it is possible that the primary database cannot be opened or even Accessed at this time, so you have to listen to your destiny. Here, think twice for you: Heaven, Earth, which kind of fairy elder sister can bless us without losing data!

3. Start failover
Run the following statements:
SQL> alter database recover managed standby database finish force;

The database has been changed.
The Force keyword stops the RFS process of the current activity so that the Failover can be executed immediately.

The remaining steps are very similar to the previous switchover.
4. Switch the physical standby role to primary.
SQL> alter database commit to switchover to primary;

The database has been changed.

5. Start a new primary database.
If the current database has been mounted, open it directly. If the database is in read-only mode, shutdown immediate first and then start up directly.
SQL> alter database open;

The database has been changed.