Oracle 19C Data Guard 单实例1+1部署（Duplicate方式）

环境描述

说明

• 主库和备库建议采用相同服务器配置。
• 主库和备库建议采用相同操作系统版本。
• 主库和备库需要采用相同数据库版本（含 PSU）。

注：本次环境中主库和备库文件路径是保持一致的，如果不一致请注意修改。

主库配置

1. 查看是否归档模式

[oracle@hisdb01 dbs]$ sqlplus / as sysdba
SQL> archive log list;

如果没有归档，则执行以下步骤切换为归档模式：

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. 启动 FORCE_LOGGING 模式

SQL> alter database FORCE LOGGING;
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES

3. 创建参数文件

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

4. 拷贝口令文件到备库

scp -P2222 orapwhis 10.172.1.102:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/

5. 修改 /etc/hosts

主备库都进行配置：

cat >> /etc/hosts <<EOF
10.172.1.101 hisdb01
10.172.1.102 hisdb02
EOF

6. 修改主库参数文件

修改生成的参数文件 /home/oracle/pfile.ora 文件，参考添加以下内容：

*.db_name='his'
*.db_unique_name='his'
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='dg_config=(his,hisdg)' 
*.log_archive_DEST_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdg'
*.db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='enable'
*.standby_file_management='AUTO' 

如果不停机在线修改主库参数，参考如下：

alter system set db_unique_name='his' scope=spfile;
alter system set FAL_SERVER='standby' scope=both;
alter system set FAL_CLIENT='primary' scope=both;
alter system set db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(his,hisdg)' scope=both;
alter system set log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=his' scope=both;
alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hisdg' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='enable' scope=both;
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;

7. 修改监听文件

主库编辑 listener.ora 文件，添加以下内容：

vi /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = his)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = his)
    )
  )

8. 修改 TNS文件

主库修改tnsnames.ora 追加以下内容：

primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = his)
    )
  )

standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = his)
    )
  )

测试TNS:

tnsping primary
tnsping standby
sqlplus sys/oracle@primary as sysdba
sqlplus sys/oracle@standby as sysdba

备库配置

1. 创建必要的文件目录

手动建立以下目录，保持与主库目录一致：

$ cd $ORACLE_BASE
$ mkdir -p /u01/app/oracle/oradata/his
$ mkdir -p /u01/app/oracle/admin/his/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area

2. 创建密码文件

主库和备库的 SYS 密码一致(前面已经从主库拷贝了)：

# 方式 1：使用 orapwd 设置相同密码：
orapwd file=orapwhis password=<password>

# 方式 2：将主库密码文件复制到备库 $ORACLE_HOME/dbs 目录下，并修改密码文件名为 orapwhis：
scp <主库密码文件> <备库用户>@<备库IP>:/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwhis

注：参考主库配置中 4.拷贝口令文件到备库

3. 修改监听文件

编辑 /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora 文件，添加以下内容：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = his)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = his)
    )
  )

5. 修改 tnsnames.ora 文件

追加以下内容：

primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = his)
    )
  )

standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = his)
    )
  )

测试TNS:

tnsping primary
tnsping standby
sqlplus sys/oracle@primary as sysdba
sqlplus sys/oracle@standby as sysdba

6. 修改备库参数文件并生成 spfile文件

通过从主库拷贝过来的参数文件，修改成备库参数文件，参考如下：

*.audit_file_dest='/u01/app/oracle/admin/his/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/his/control01.ctl','/u01/app/oracle/oradata/his/control02.ctl'
*.db_block_size=8192
*.db_name='his'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=7851m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisXDB)'
*.log_archive_dest_1='LOCATION=/u01/archivelog'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=9639m
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=28917m
*.undo_tablespace='UNDOTBS1'

# 添加如下内容
*.db_unique_name='hisdg'
*.service_names='his'
*.log_archive_config='dg_config=(his,hisdg)' 
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
*.log_archive_DEST_1='location=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hisdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=his'
*.db_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.log_file_name_convert='/u01/app/oracle/oradata/his','/u01/app/oracle/oradata/his'
*.LOG_ARCHIVE_DEST_STATE_1='enable'
*.LOG_ARCHIVE_DEST_STATE_2='enable'
*.standby_file_management='AUTO' 

通过新创建修改的pfile文件生成spfile文件

SQL> startup pfile='/home/oracle/pfile.ora' nomount;
SQL> create spfile from pfile='/home/oracle/pfile.ora';
SQL> shutdown immediate
-- 最后再重新启动到nomount
SQL> startup nomount;

7. 使用 Duplicate 创建物理 Standby

-- 接 RMAN 并连接辅助（auxiliary）实例，备库目前是 nomount 状态：
rman target sys/His#2021@primary auxiliary sys/His#2021@standby

-- 执行复制：
duplicate target database for standby from active database nofilenamecheck DORECOVER;

8. 查询备库数据库所有文件

SQL> select status from v$instance;
SQL> select member from v$logfile;
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> show parameter control

添加standby日志文件

1. 添加主库standby日志文件

新建4个日志组作为standby redolog 日志组，大小与原来的日志组一致：

alter database add standby logfile group 11 '/u01/app/oracle/oradata/his/standby_redo11.log' size 500m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/his/standby_redo12.log' size 500m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/his/standby_redo13.log' size 500m;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/his/standby_redo14.log' size 500m;

2. 添加备库standby日志文件

alter database add standby logfile group 11 '/u01/app/oracle/oradata/his/standby_redo11.log' size 500m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/his/standby_redo12.log' size 500m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/his/standby_redo13.log' size 500m;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/his/standby_redo14.log' size 500m;

在备库执行

备库打开数据库并实时应用

alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect; -- 简写

查看未应用的归档日志

-- 查询未应用的归档日志的最小和最大序列号，以及每个线程未应用的日志数量
select thread#, min(sequence#) no_applied_min, max(sequence#) no_applied_max, count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;

-- 查询所有未应用的归档日志的详细信息
select thread#, sequence#, applied from v$archived_log where applied='NO' order by thread#, sequence#;

查看进程状态

select process, pid, status, sequence# from v$managed_standby;

select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v$database;

验证主备同步

在主库执行生成一张表：

create table test(a varchar2(2), b date);
insert into test values('1', sysdate);
commit;

在备库查询：

select * from test;

切回主库删除测试表：

drop table test;

然后在主备库执行：

select * from test; -- 应报错则OK.

自动启动DataGuard脚本

创建 /etc/init.d/dataguard 启动脚本

脚本内容如下：

#!/bin/bash
# chkconfig: 2345 98 01
# description: Oracle database dataguard server
# Starts the oracle database dataguard server
# If more than four archive no application will be under the mout synchronization
# make SHELL: MUXINQNG
case $1 in
'start')
  if [ ! -f /var/lock/subsys/oracle ]; then
    prog="listener"
    echo -n $"Starting $prog: "
    su - oracle -c "lsnrctl start" >> /var/log/oracle.log
    RETVAL=$?
    [ $RETVAL -eq 0 ] && echo "success" || echo "failed"
    prog="oracle dataguard"
    echo -n $"Starting $prog: "
    su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
    connect / as sysdba
    startup mount
    ! sleep 3
    select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;
    select thread#, min(sequence#) no_applied_min, max(sequence#) no_applied_max, count(1) no_applied_all from v\$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
    declare wyyn number;
    begin
      select count(1) into wyyn from v\$archived_log where applied='NO' and standby_dest='NO';
      if wyyn < 5 then
        EXECUTE IMMEDIATE 'alter database open';
        EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';
      else
        EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';
      end if;
    end;
    /
    select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;
    select applied, count(1) from v\$archived_log where applied='NO' group by applied;
    exit
EOF
    RETVAL=$?
    [ $RETVAL -eq 0 ] && echo "success" || echo "failed"
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle || RETVAL=1
  fi
  ;;
'stop')
  prog="listener"
  echo -n $"Stopping $prog: "
  su - oracle -c "lsnrctl stop" >> /var/log/oracle.log
  RETVAL=$?
  [ $RETVAL -eq 0 ] && echo "success" || echo "failed"
  echo
  prog="oracle dataguard"
  echo -n $"Stopping $prog: "
  su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
  connect / as sysdba
  alter database recover managed standby database cancel;
  shutdown immediate
  exit
EOF
  RETVAL=$?
  [ $RETVAL -eq 0 ] && echo "success" || echo "failed"
  echo
  [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle
  ;;
'restart')
  prog="oracle dataguard"
  echo -n $"Stopping $prog: "
  $0 stop
  prog="oracle dataguard"
  echo -n $"Starting $prog: "
  $0 start
  ;;
'status')
  su - oracle -c "lsnrctl status"
  su - oracle -c "sqlplus /nolog" << EOF
  connect / as sysdba
  select name, open_mode, PROTECTION_MODE, DATABASE_ROLE from v\$database;
  select process, status from v\$managed_standby;
  exit
EOF
  ;;
*)
  echo $"Usage: $0 {start|stop|restart|status}"
  ;;
esac

设置脚本权限：

chmod 775 /etc/init.d/dataguard

将脚本转换为 systemd 服务单元文件

创建一个 systemd 服务单元文件 /etc/systemd/system/dataguard.service，内容如下：

[Unit]
Description=Oracle database dataguard server
After=network.target

[Service]
Type=forking
ExecStart=/etc/init.d/dataguard start
ExecStop=/etc/init.d/dataguard stop
User=root
Group=root
Restart=on-failure

[Install]
WantedBy=multi-user.target

重新加载 systemd 配置：

systemctl daemon-reload

启用服务（开机自启）：

systemctl enable dataguard

手动启停dataguard服务：

systemctl start dataguard
systemctl stop dataguard
systemctl status dataguard

删除应用归档日志

创建定时任务和脚本：

crontab -l
00 01 * * * /u01/script/del_dg_arch.sh 

del_dg_arch.sh脚本内容：

#!/bin/bash
# delete dataguard applied archive log
# use database v$archived_log get applied archive log in os rm

archive_name=$(su - oracle -c "sqlplus -silent / as sysdba" <<EOF
set pagesize 0 feedback off verify off heading off echo off numwidth 4
select NAME from V\$ARCHIVED_LOG WHERE STATUS='A' AND APPLIED='YES' and registrar='RFS' and name is not null;
exit
EOF
)
result=$?
if [ $result -eq 0 ]; then
  record=`echo $archive_name | wc -w`
  if [  $record -gt 0 ]; then
    for arch_list in $archive_name
    do
      echo $arch_list
      rm -f $arch_list && echo 'delete archive succeed' || echo 'delete archive Failure'
    done
  fi
fi
su - oracle -c "rman target /" <<EOF
crosscheck archivelog all; 
delete noprompt expired archivelog all;
exit;
EOF