[译] Data Guard：Oracle Database 21c 中的 PREPARE DATABASE FOR DATA GUARD 命令

原文地址：https://oracle-base.com/articles/21c/data-guard-prepare-database-for-data-guard-21c 原文作者：Tim Hall

在 Oracle 数据库 21c 中配置 Data Guard 时，使用 PREPARE DATABASE FOR DATA GUARD 命令可以简化主数据库的设置。

介绍

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

在配置 Data Guard 之前，主数据库的准备需要执行许多步骤。对于一个简单的单实例主数据库，可能需要以下步骤：

设置 DB_RECOVERY_FILE_DEST_SIZE 和 DB_RECOVERY_FILE_DEST 初始化参数：

SQL

1alter system set db_recovery_file_dest_size=400g;
2alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

启用归档日志模式并强制记录：

SQL

1shutdown immediate;
2startup mount;
3alter database archivelog;
4alter database open;
5
6alter database force logging;

添加一些备用日志：

SQL

1alter database add standby logfile thread 1 group 10 size 50m;
2alter database add standby logfile thread 1 group 11 size 50m;
3alter database add standby logfile thread 1 group 12 size 50m;
4alter database add standby logfile thread 1 group 13 size 50m;

启用闪回数据库：

SQL

1alter database flashback on;

设置 STANDBY_FILE_MANAGEMENT 初始化参数并启用代理：

SQL

1alter system set standby_file_management=auto;
2alter system set dg_broker_start=true;

在 Oracle 21c 中，可以使用 PREPARE DATABASE FOR DATA GUARD 命令执行以上这些步骤。

PREPARE DATABASE FOR DATA GUARD

我们通过 dgmgrl 与数据库建立连接，并且执行 HELP PREPARE 命令来查看语法：

Bash

1$ dgmgrl /
2DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Feb 6 13:11:59 2022
3Version 21.3.0.0.0
4
5Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
6
7Welcome to DGMGRL, type "help" for information.
8Connected to "cdb1"
9Connected as SYSDG.
10DGMGRL> help prepare
11
12Prepare a primary database for a Data Guard environment.
13
14Syntax:
15
16  PREPARE DATABASE FOR DATA GUARD
17    [WITH [DB_UNIQUE_NAME IS ]
18          [DB_RECOVERY_FILE_DEST IS ]
19          [DB_RECOVERY_FILE_DEST_SIZE IS ]
20          [BROKER_CONFIG_FILE_1 IS ]
21          [BROKER_CONFIG_FILE_2 IS ]];
22
23
24DGMGRL>

这是我在 Vagrant 21c Data Guard 构建期间使用的命令示例：

Bash

1dgmgrl / <<EOF
2prepare database for data guard
3  with db_unique_name is ${NODE1_DB_UNIQUE_NAME}
4  db_recovery_file_dest is "${ORACLE_BASE}/fast_recovery_area"
5  db_recovery_file_dest_size is 20g;
6exit;
7EOF

代入环境变量如下：

Bash

1dgmgrl / <<EOF
2prepare database for data guard
3	with db_unique_name is cdb1
4	db_recovery_file_dest is '/u01/app/oracle/fast_recovery_area'
5	db_recovery_file_dest_size is 20G;
6exit;
7EOF

下面是命令执行创建的输出示例：

Bash

1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Feb 6 14:03:10 2022
2Version 21.3.0.0.0
3
4Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
5
6Welcome to DGMGRL, type "help" for information.
7Connected to "cdb1"
8Connected as SYSDG.
9DGMGRL> > > > Preparing database "cdb1" for Data Guard.
10Initialization parameter DB_FILES set to 1024.
11Initialization parameter LOG_BUFFER set to 268435456.
12Primary database must be restarted after setting static initialization parameters.
13Primary database must be restarted to enable archivelog mode.
14Shutting down database "cdb1".
15Database closed.
16Database dismounted.
17ORACLE instance shut down.
18Starting database "cdb1" to mounted mode.
19ORACLE instance started.
20Database mounted.
21Initialization parameter DB_FLASHBACK_RETENTION_TARGET set to 120.
22Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.
23RMAN configuration archivelog deletion policy set to SHIPPED TO ALL STANDBY.
24Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '20g'.
25Initialization parameter DB_RECOVERY_FILE_DEST set to '/u01/app/oracle/fast_recovery_area'.
26LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.
27Initialization parameter LOG_ARCHIVE_DEST_2 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.
28Initialization parameter LOG_ARCHIVE_DEST_STATE_2 set to 'Enable'.
29Adding standby log group size 52428800 and assigning it to thread 1.
30Adding standby log group size 52428800 and assigning it to thread 1.
31Adding standby log group size 52428800 and assigning it to thread 1.
32Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.
33Initialization parameter DG_BROKER_START set to TRUE.
34Database set to FORCE LOGGING.
35Database set to ARCHIVELOG.
36Database set to FLASHBACK ON.
37Database opened.
38DGMGRL>

通过以上输出结果，我们可以看到执行了常见的设置任务，并且设置了一些额外的初始化参数：

Bash

1DB_FILES=1024
2LOG_BUFFER=256M
3DB_BLOCK_CHECKSUM=TYPICAL          # Unchanged if FULL
4DB_LOST_WRITE_PROTECT=TYPICAL      # Unchanged if FULL
5DB_FLASHBACK_RETENTION_TARGET=120  # Unchanged if non-default
6PARALLEL_THREADS_PER_CPU=1
7DG_BROKER_START=TRUE

当然了，还有一些其他的事项需要注意：

如果单个实例的 SPFILE 不存在，则会创建一个。
它将 RMAN 归档日志删除策略设置为 SHIPPED TO ALL STANDBY。
它不仅会添加丢失的备用日志，而且如果某些配置错误，它会删除并重新配置它们。

参考官方文档：PREPARE DATABASE FOR DATA GUARD

译者备注：整体看起来并不实用，并且需要配合使用 DGMGRL 来使用，期待后续优化，当然以上这些命令也完全可以自行写脚本来实现。

SQL

1alter system set db_recovery_file_dest_size=400g;
2alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

SQL

1shutdown immediate;
2startup mount;
3alter database archivelog;
4alter database open;
5
6alter database force logging;

SQL

1alter database add standby logfile thread 1 group 10 size 50m;
2alter database add standby logfile thread 1 group 11 size 50m;
3alter database add standby logfile thread 1 group 12 size 50m;
4alter database add standby logfile thread 1 group 13 size 50m;

SQL

1alter database flashback on;

SQL

1alter system set standby_file_management=auto;
2alter system set dg_broker_start=true;

Bash

1$ dgmgrl /
2DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Feb 6 13:11:59 2022
3Version 21.3.0.0.0
4
5Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
6
7Welcome to DGMGRL, type "help" for information.
8Connected to "cdb1"
9Connected as SYSDG.
10DGMGRL> help prepare
11
12Prepare a primary database for a Data Guard environment.
13
14Syntax:
15
16  PREPARE DATABASE FOR DATA GUARD
17    [WITH [DB_UNIQUE_NAME IS ]
18          [DB_RECOVERY_FILE_DEST IS ]
19          [DB_RECOVERY_FILE_DEST_SIZE IS ]
20          [BROKER_CONFIG_FILE_1 IS ]
21          [BROKER_CONFIG_FILE_2 IS ]];
22
23
24DGMGRL>

Bash

1dgmgrl / <<EOF
2prepare database for data guard
3  with db_unique_name is ${NODE1_DB_UNIQUE_NAME}
4  db_recovery_file_dest is "${ORACLE_BASE}/fast_recovery_area"
5  db_recovery_file_dest_size is 20g;
6exit;
7EOF

Bash

1dgmgrl / <<EOF
2prepare database for data guard
3	with db_unique_name is cdb1
4	db_recovery_file_dest is '/u01/app/oracle/fast_recovery_area'
5	db_recovery_file_dest_size is 20G;
6exit;
7EOF

Bash

1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Feb 6 14:03:10 2022
2Version 21.3.0.0.0
3
4Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.
5
6Welcome to DGMGRL, type "help" for information.
7Connected to "cdb1"
8Connected as SYSDG.
9DGMGRL> > > > Preparing database "cdb1" for Data Guard.
10Initialization parameter DB_FILES set to 1024.
11Initialization parameter LOG_BUFFER set to 268435456.
12Primary database must be restarted after setting static initialization parameters.
13Primary database must be restarted to enable archivelog mode.
14Shutting down database "cdb1".
15Database closed.
16Database dismounted.
17ORACLE instance shut down.
18Starting database "cdb1" to mounted mode.
19ORACLE instance started.
20Database mounted.
21Initialization parameter DB_FLASHBACK_RETENTION_TARGET set to 120.
22Initialization parameter DB_LOST_WRITE_PROTECT set to 'TYPICAL'.
23RMAN configuration archivelog deletion policy set to SHIPPED TO ALL STANDBY.
24Initialization parameter DB_RECOVERY_FILE_DEST_SIZE set to '20g'.
25Initialization parameter DB_RECOVERY_FILE_DEST set to '/u01/app/oracle/fast_recovery_area'.
26LOG_ARCHIVE_DEST_n initialization parameter already set for local archival.
27Initialization parameter LOG_ARCHIVE_DEST_2 set to 'location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles)'.
28Initialization parameter LOG_ARCHIVE_DEST_STATE_2 set to 'Enable'.
29Adding standby log group size 52428800 and assigning it to thread 1.
30Adding standby log group size 52428800 and assigning it to thread 1.
31Adding standby log group size 52428800 and assigning it to thread 1.
32Initialization parameter STANDBY_FILE_MANAGEMENT set to 'AUTO'.
33Initialization parameter DG_BROKER_START set to TRUE.
34Database set to FORCE LOGGING.
35Database set to ARCHIVELOG.
36Database set to FLASHBACK ON.
37Database opened.
38DGMGRL>

Bash

1DB_FILES=1024
2LOG_BUFFER=256M
3DB_BLOCK_CHECKSUM=TYPICAL          # Unchanged if FULL
4DB_LOST_WRITE_PROTECT=TYPICAL      # Unchanged if FULL
5DB_FLASHBACK_RETENTION_TARGET=120  # Unchanged if non-default
6PARALLEL_THREADS_PER_CPU=1
7DG_BROKER_START=TRUE