Oracle Data Guard 19C 笔记

Data Guard

1. Primary database preparation

1.1 开启force logging

-- 有3中force logging方式，选用一种
-- 
-- FORCE LOGGING mode prevents any load operation from being performed in a nonlogged manner. This can slow down the load process because the loaded data must be copied into the redo logs
SQL> ALTER DATABASE FORCE LOGGING;
-- STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
-- STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment.
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

1.2 配置redo transport认证

有2中认证方式： SSL和remote login password file
一般用密码文件, 所以需要复制主库的密码文件到备库

1.3 配置主库接收Redo Data

It is a best practice to configure the primary database to receive redo if this is the first time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;

The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database.

SQL> SELECT GROUP#, BYTES FROM V$LOG;

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

1.4 设置主库初始化参数

The configuration examples use the names shown in the following table:

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	chicago	chicago
Physical standby	boston	boston

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

The following shows the additional standby role initialization parameters on the primary database. These parameters take effect when the primary database is transitioned to the standby role.

FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/' 
STANDBY_FILE_MANAGEMENT=AUTO

1.5 开启归档

If archiving is not enabled, then you must put the primary database in ARCHIVELOG mode and enable automatic archiving.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

2. 创建Physical Standby Database

流程概览：

Task	Database
Create a Backup Copy of the Primary Database Data Files	Primary
Create a Control File for the Standby Database	Primary
Create a Parameter File for the Standby Database	Primary
Copy Files from the Primary System to the Standby System	Primary
Set Up the Environment to Support the Standby Database	Standby
Start the Physical Standby Database	Standby
Verify the Physical Standby Database Is Performing Properly	Standby

2.1 创建主库数据文件备份

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).

2.2 创建standby控制文件

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';

2.3 创建参数文件

SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

修改参数文件， 给备库准备

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_FILE_NAME_CONVERT='/chicago/','/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=chicago ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=chicago'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago

Ensure the COMPATIBLE initialization parameter is set to the same value on both the primary and standby databases. If the values differ, then redo transport services may be unable to transmit redo data from the primary database to the standby databases.

2.4 Copy Files from the Primary System to the Standby System

Ensure that all required directories are created. Use an operating system copy utility to copy binary files from the primary system to their correct locations on the standby system.

2.5 设置环境 to Support the Standby Database

1. 如果standby database在Windows系统下

   oradim –NEW –SID boston –STARTMODE manual
   

2. 复制密码文件到备库
3. 配置和启动监听，建议添加静态监听
4. Create Oracle Net service names.
   On both the primary and standby systems, use Oracle Net Manager to create a network service name for the primary and standby databases that are to be used by redo transport services.
5. 备库上， 创建server parameter file

   SQL> CREATE SPFILE FROM PFILE='initboston.ora';
   

6. If the primary database has a database encryption wallet, then copy it to the standby database system and configure the standby database to use this wallet.

2.6 启动Physical Standby Database

SQL> STARTUP MOUNT;
-- Restore the backup of the data files
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

2.7 验证物理备库工作正常

SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;


ROLE                        THREAD#  SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
RFS ping                          1          9 IDLE
recovery apply slave              0          0 IDLE
recovery apply slave              0          0 IDLE
managed recovery                  0          0 IDLE
recovery logmerger                1          9 APPLYING_LOG
RFS archive                       0          0 IDLE
RFS async                         1          9 IDLE

The recovery logmerger role shows that redo is being applied at the standby.

3. Upgrade the data protection mode

1. 数据保护模式
   Required Redo Transport Attributes for Data Protection Modes

   Maximum Availability	Maximum Performance	Maximum Protection
   AFFIRM or NOAFFIRM	NOAFFIRM	AFFIRM
   SYNC	ASYNC	SYNC
   DB_UNIQUE_NAME	DB_UNIQUE_NAME	DB_UNIQUE_NAME

   SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
   

   The data protection mode can be set to MAXIMUM PROTECTION on an open database only if the current data protection mode is MAXIMUM AVAILABILITY and if there is at least one synchronized standby database.
   确认

   SELECT PROTECTION_MODE FROM V$DATABASE;
   

4. Using DBCA to Create a Data Guard Standby

The DBCA command qualifier used to create the physical standby database is createDuplicateDB.

dbca -createDuplicateDB 
    -gdbName global_database_name 
    -primaryDBConnectionString easy_connect_string_to_primary
    -sid database_system_identifier
    [-createAsStandby 
        [-dbUniqueName db_unique_name_for_standby]]
    [-customScripts scripts_list]

Example:

dbca –silent -createDuplicateDB -primaryDBConnectionString  myprimary.domain:1523/chicago.domain 
-gdbName chicago.domain -sid boston -initParams instance_name=boston –createAsStandby