Basic configuration of Oracle 10g Dataguard notebooks

Dataguard provides support for high availability of enterprise data, data security, and disaster recovery, typically consisting of a dataguard configuration of a primary db with several physical or logical standby db.

System environment

Operating system for Windows Server 2003 R2 Enterprise x64 Edition Service Pack 2,database for Oracle 10g 10.2.0.4 Enterprise x64 Edition. The server is AMD64 architecture, host RAID5 local hard disk plus RAID1 fiber optic disk array.

Primary library:
IP 10.0.0.1
$ORACLE _base E:\ORACLE
$ORACLE _home E:\ORACLE\PRODUCT\10.2.0\DB_1
$ORACLE _sid ORCL

Standby Library Standby01:
IP 10.0.0.2
$ORACLE _base E:\ORACLE
$ORACLE _home E:\ORACLE\PRODUCT\10.2.0\DB_1
$ORACLE _sid ORCL

The Oracle version of the main library primary and (first) physical standby standby01 is exactly the same as the physical structure, and the paths for all Oracle files are the same on both servers.

Scheme

The goal of configuration Dataguard is to ensure the highest availability of business system data and to quickly recover databases from hardware failures, data corruption, or disasters. Physical standby performance and stability are better than logical standby, and since the repository does not need to be used for querying, the standby library uses a physical standby mode.

The Dataguard has three modes of protection, maximum performance mode, maximum available mode and maximum protection mode.

The maximum protection mode ensures that no data loss is available. This mode requires that all transactions be committed before committing their redo data to the local online redo log, and to standby standby redo log at the same time, confirming that redo data is available at least one standby, The transaction is then committed on the primary. When a failure causes no standby to be available, primary will shutdown until at least one standby is restored.
The maximum performance mode is the Dataguard default data protection mode. In this mode, the transaction can be committed as long as the redo data is written to the local online redo log. Primary still writes redo logs to standby, but this write is asynchronous and has no effect on transactions that produce redo data. The maximum performance mode has minimal impact on the system, but there is a risk of data loss.
The maximum available mode is the tradeoff between the two modes, under normal circumstances, the maximum available mode is the same as the maximum protection mode, and it is also required that redo data written to the local online redo log must be written to at least one standby standby redo log before the transaction commits. However, when a standby failure is not available, the maximum available mode is automatically reduced to maximum performance mode. When the fault is eliminated and the standby redo log is fully synchronized with the primary, primary will automatically revert to the maximum available mode operation. In this case, the Dataguard redo log gaps is used for both Fal_server and fal_client parameters. So standby failures do not cause Primay to be unavailable. As long as there is at least one standby available, even if primary is down, the data is guaranteed to be not lost.
Because the system environment is good, you can configure multiple physical standby, the system availability is high, and can tolerate a very small amount of data loss, so use the highest available mode.

Dataguard Basic Configuration

Main Library (primary) side configuration:

1. Open Force logging mode
Sql>alter database force logging;
and then query
Sql>select force_logging from V$database;
For
—
YES
Indicates that force logging mode is turned on

2. Create a password file
If the password file does not exist to create a password file, each database in the Dataguard configuration must use a password file, and the SYS user password must be the same for all data in order to successfully transfer redo LOGS. The password file was created automatically when the main library was installed, and the same sys password was used to install and automatically create the password file when the repository was installed, so there is no need to re-create the password file at this time.

3. Configure Standby Redo Log

The maximum protected mode and maximum available mode must use standby redo log, and it is recommended that all databases use the LGRW async log transfer mode. When creating the standby database, plan standby redo log configuration and create all required log groups and group members. To increase usability, you can standby redo log files, just like the multi-path online redo log file. All log file contents in each log group are the same when multiplexed, and can be dispersed across different drives to improve availability and IO performance.

The steps to create the standby redo log are as follows:

1) Ensure that the size of the log file is consistent with the size of the primary, standby online redo log file. This makes the log transfer and application more convenient.

2) Determine the appropriate number of standby redo log logging groups
Standby Redo Log Group is at least one more than the online redo log log group. However, the official recommendation is to calculate the number of standby redo log log groups based on the number of threads in the primary database, as follows

(Number of log groups per thread +1) * Number of threads

This reduces the likelihood that the primary library LGRW process will be blocked.
For example, primary has two threads and two log groups per thread, so it is recommended to configure 6 standby redo log log groups.
A single-instance database has only one thread, so the configuration is more than the default group of 3 online redo logs, which is 4 groups of standby redo log.

3) Create standby redo log Journal Group

Under normal circumstances, the standby Redo log log group only needs to be configured in the standby library, which is also configured on the primary side, considering the primary and standby switchover.
Check the size of the online redo log file first
Sql>select group#,thread#,archived,status,bytes/1024/1024 from V$log;
group# thread# ARC STATUS bytes/1024/1024
—— ———- — —————- —————
1 1 YES INACTIVE 50
2 1 NO Current 50
3 1 YES INACTIVE 50
The online redo log log file size is 50M and the group number is 1-3, so the standby redo log Group has a group number of 4-7, and the standby redo log log group is created below

Sql>alter Database Add standby LogFile Group 4 (' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STDBYREDO01. LOG ') size 50M;
Database altered.
Sql>alter Database Add standby LogFile Group 5 (' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STDBYREDO02. LOG ') size 50M;
Database altered.
Sql>alter Database Add standby logfile Group 6 (' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STDBYREDO03. LOG ') size 50M;
Database altered.
Sql>alter Database Add standby LogFile Group 7 (' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STDBYREDO04. LOG ') size 50M;
Database altered.

Verify standby Redo log Log group is created successfully
Sql>select group#,thread#,sequence#,archived,status from V$standby_log;
group# sequence# ARC STATUS
———- ———- — ———-
4 0 YES UNASSIGNED
5 0 YES UNASSIGNED
6 0 YES UNASSIGNED
7 0 YES UNASSIGNED

4. Set up Oracle Net service names

Add the following ORACLE Net Service name,primary Identity Master Library and Standby01 identity (first) physical standby in the $oracle_home/network/admin/tnsname.ora file of the main library primary
Primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)

Standby01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.2) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)

5. Set Primary initialization parameters
For primary databases, a few initialization parameters need to be defined to control the Redo transport service as a primary foot color. There are several additional initialization parameters that need to be defined to control the receive and log application services of the redo data, which are used when the primary library is converted to the standby role, facilitating the conversion of the primary and standby roles.

Dataguard related initialization Parameters detailed explanation see here

Because there are many initialization parameters that need to be modified, export pfile from SPFile, then edit pfile, and then Pfile rebuild SPFile

Sql>create pfile from SPFile;

Will generate Initorcl.ora in the $oracle_home/database/directory

Here are the initialization parameters that the primary library needs to modify or add:
Db_name= ' ORCL '
Db_unique_name= ' primary '
Log_archive_config= ' dg_config= (primary,standby01) '
Log_archive_dest_1= ' location=d:\archived_log\ valid_for= (all_logfiles,all_roles) DB_UNIQUE_NAME=primary '
Log_archive_dest_2= ' service=standby01 lgwr ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=standby01 ′
Log_archive_dest_state_1=enable
Log_archive_dest_state_2=enable

Note: Because the log_archive_dest_n is incompatible with the Log_archive_dest (log_archive_duplex_dest) parameter, it is necessary to log_archive_dest (Log_archive_duplex _dest) parameter reset is empty, the archive log path problem is described in the Oracle 10g Redo Log Archive Path parameter.

Note: The local archive directory specified by log_archive_dest_1 must already exist before the parameter takes effect, or the following error will be reported when starting the database:
Ora-16032:parameter log_archive_dest_1 Destination string cannot be translated
Ora-09291:sksachk:invalid device specified for archive destination
Osd-04018:unable to access the specified directory or device.
O/s-error: (OS 2) xxxxxxxxxxxxxxxxxxxx

Here are the initialization parameters required for the standby foot color, set these parameters for primary to facilitate conversion between the primary and standby foot colors
Fal_server=standby01
Fal_client=primary
Standby_file_management=auto

Create SPFile with Pfile after the modification is complete

Sql>shutdown Immediate
Sql>create spfile from pfile= ' Initorcl.ora '
Sql>startup

6. Ensure that the primary is in archive mode

Sql>archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled

If archive mode is not turned on, execute the following command to place the database in archive mode
Sql>shutdown immediate;
Sql>startup Mount;
Sql>alter database Archivelog;
Sql>alter database open;

7. Generate a control file for the physical standby library

Sql>alter Database Create standby controlfile as ' \path\to\control_file ';

Physical standby (STANDBY01) side configuration

1. Create a standby library
There are several ways to create physical repositories, using cold backups or Rman or other methods, and Oracle recommends using Rman. Because the primary is fully consistent with the STANDBY01 structure, and primary has the opportunity to stop the library, the simplest cold backup is used to create the physical standby standby01, as follows:

Need to copy the password files, standby control files, data files, online log files, initialization parameters files. Initialize the parameters file copy the main library to generate and modify the good pfile Initorcl.ora, and then according to the actual situation of the physical standby to make corresponding changes to generate SPFile can be.

Start by querying the database to find out where these files are located
Sql>select name from V$datafile; Data files
Sql>select name from V$logfile; Online log files
Sql>show parameter log_archive_dest; Archive log files

Online redo Log,standby Redo log where the path is $oracle_base\product\10.2.0\oradata\orcl\, password file Pwdorcl.ora, initialize parameter file Initorcl.ora , the normal user data file path is $oracle_home\database\, the system User data file path is $oracle_base\product\10.2.0\oradata\orcl\, the archive log file is located on the path of D:\ Archived_log

For the control file slightly different, standby library can not directly use the primary library control file, or standby will be reported "Ora-01665:control file is not a standby control file" error, Need to generate control file from primary library for standby, execute command on primary library side

Sql>alter Database Create standby controlfile as ' d:\control01.ctl ';

Then shut down primary and STANDBY01, respectively.
Sql>shutdown Immediate

Copy the above files to the corresponding directory in the STANDBY01 library, because the library structure is identical on both sides, so copy the $ORACLE_BASE\PRODUCT\10.2.0\ORADATA\ORCL and $oracle_home\ directly from the primary Database\ These two directories can be overridden in the standby corresponding directory.

Finally, copy the generated control file to the path of the STANDBY01 library initialization parameter Contro_files setting, which is used as the default value, which is the $ORACLE_BASE\PRODUCT\10.2.0\ORADATA\ORCL of the Standby01 library. \ directory. Note that the control file is redundant, copy Control01.ctl to Control02.ctl and Control03.ctl, respectively, covering standby01 original three control files, the three control files are exactly the same. To be safe and reliable, you can also modify the initialization parameter Control_files to place three control files on different drives. See Oracle 10g Control File redundancy for control files.

2. Set up Oracle Net service names

Add the following ORACLE Net Service name,primary Identity Master Library and Standby01 identity (first) in the $oracle_home/network/admin/tnsname.ora file of the repository Standby01 Physical Standby Library
Primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)

Standby01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.2) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)

3. Set STANDBY01 initialization parameters

Modify the Initorcl.ora copied from the primary library directly according to the Stanby role, the following are the initialization parameters that the STANDBY01 library needs to modify or add:

Db_name= ' ORCL '
Db_unique_name= ' standby01′
Log_archive_config= ' dg_config= (primary,standby01) '
Log_archive_dest_1= ' location=d:\archived_log\ valid_for= (all_logfiles,all_roles) DB_UNIQUE_NAME=standby01′
Log_archive_dest_state_1=enable
Fal_server=primary
Fal_client=standby01
Standby_file_management=auto

The following parameters are used for STANDBY01 from standby to main library role conversions

Log_archive_dest_2= ' service=primary lgwr ASYNC valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=primary '
Log_archive_dest_state_2=enable

Create SPFile with Pfile after the modification is complete

Sql>create spfile from pfile= ' Initorcl.ora '

4. Start the physical standby standby and open the Redo application

Sql>startup Mount
Sql>alter database recover managed standby database disconnect from session;

Use the following statement to stop the Redo app
Sql>alter database recover managed standby database cancel;

5, check the physical standby standby01 is correct synchronization

Manually force archiving and query archive logs on the primary library
Sql>alter system switch logfile;
Sql>select Max (sequence#) from V$archived_log;
MAX (sequence#)
————–
486

Querying the archive log on Standby01
Sql>select Max (sequence#) from V$archived_log;
MAX (sequence#)
————–
486

If you make the following query from primary, you can see the following records
Sql>select name,sequence# from V$archived_log order by sequence#;
NAME sequence#
———— ————-
STANDBY01 486
D:\ARCHIVED_LOG\ARC00486_0765555401.001 486

The same archive file is written to the local archive path and the Standby01 repository, respectively.

This indicates that Dataguard data synchronization is correct.

6. Open real-time redo application

The redo application of the 4th step above is not real-time, only when the main library's online redo archives and triggers the standby redo archive of the repository to apply the archived log data to the repository, there will be a large delay, resulting in large differences in the main repository over time.
Dataguard provides a way to apply redo logs in real time, and if log real-time applications are turned on, the Log app service will immediately apply redo data received from primary to the standby library without waiting for the current standby redo Log logs are archived before the redo data is applied. The live Log app must be configured standby redo log file in the standby library.

Open Redo real-time applications
Sql>alter database RECOVER MANAGED STANDBY database USING current LOGFILE DISCONNECT from SESSION;

The default Dataguard runs in maximum performance mode, and how to upgrade to other modes is also described.

P.S.: Creating more physical standby repositories is nothing special, just add more network archive paths in primary pfile, such as Log_archive_dest_3, Log_archive_dest_4, and of course Log_ Archive_dest_state_3, Log_archive_dest_state_4 also to be set to enable, there are parameters log_archive_config= ' dg_config= (PRIMARY,STANDBY01, Standby02,...) ', and then the appropriate settings Fal_server and fal_client on it.

Basic configuration of Oracle 10g Dataguard notebooks