【DG部署】【RAC+ DG(Single DB & ASM)】

博主： lo8ol
发布时间：2021 年 08 月 02 日
2920 次浏览
暂无评论
57330字数
分类： DG

# 一、Dataguard 介绍

## 1.1 概念

> Data Guard是保证企业数据的高可用性(high availability)、数据保护(data protection)以及灾难恢复(disaster recovery)的集成化灾难恢复解决方案。该技术可以维护生产数据库一个或多个同步备份，有一个生产数据库和若干个备用数据库组成，并形成一个独立的、易于管理的数据保护方案。

> Data Guard备用数据库可以与生产系统位于相同的数据中心，也可以是在地理位置上分布较远的的远程灾难备份中心。

## 1.2 原理

> Data Guard的基本原理是：当某次事务处理对生产数据库中的数据作出更改时，Oracle将在联机重做日志文件中记录此次更改。在Data Guard中，除了把日志记录到本地的联机日志文件和归档日志文件中外，还通过网络，把日志信息发送到远程的备用数据库服务器上。

> 这个备用日志文件写入过程可以是实时、同步的，以实现零数据丢失(最大保护模式)；
> 也可以是异步的，以减少对网络带宽的压力(最大可用性模式)；
> 或者是通过归档日志文件的批量传输模式，以减少对生产系统的性能影响(最大性能模式)。
> 当备份数据库接收到日志信息时，Data Guard可以自动利用日志信息实现数据的同步。
> 当主数据库打开并处于活动状态时，备份数据库可以执行恢复操作；
> 如果主数据出现了故障，备用数据库即可以被激活并接管生产数据库的工作。

## 1.3 特点

```bash
需要冗余的服务器设备		该模式需要有冗余的服务器硬件，硬件成本较高。
需要冗余的存储设备		主机和备机都需要同样的存储空间，成本较高。
安装配置比较复杂			该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。
管理维护成本高			该模式对维护人员的要求较高，维护成本高。
具备一定的容灾特性		当主机数据库不可用，可以把数据库系统切换到备机上，具备容灾的功能。
备机可以用作只读查询		备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。
```

## 1.4 搭建DG方式

> Data Guard备库按照从生产数据库传输过来的日志处理方法的不同分为物理备库、逻辑备库和快照备库。

### 1.4.1物理备库

> (三种方法：1、RMAN的Duplicate; 2、RMAN的备份恢复; 3、DBCA )
> 备份数据库处于mount状态下，直接利用数据恢复技术，把日志文件中记录的数据变更应用在备份数据库中，从而实现与生产数据库的数据同步，在进行数据同步的时候，物理备份数据库是不能打开的、也无法提供数据查询等服务；物理备用数据库也可以通过只读的方式打开，此时就只能接收日志文件，而无法进行数据的同步。

### 1.4.2逻辑备库

> 数据库是处于正常打开状态，当它接收到新的日志信息后，利用日志挖掘器的功能，把日志中记录的变更信息转换成具体的SQL语句，并在逻辑备用数据库上执行这些SQL语句，从而实现与生产数据库的数据同步。逻辑备份数据支持在数据同步时，进行数据的查询、报表等操作。Oracle从9i R2开始支持逻辑备份数据库。

### 1.4.3快照备库

> 可以暂时将物理备份数据库转换为可更新的数据库，快照备份数据不会立马应用接收到的日志文件，设一个时间点，一次性转换。

## 1.5 备库数据保护级别

> Oracle Data Guard 支持多种级别的数据保护模式：最大性能模式，最大可用性模式，最大保护模式。
> 分别对应于“重要信息系统灾难恢复指南”中的5级，5级6级自适应，6级的数据保护级别。
> 其中对应6级的最大保护模式可以实现实时数据实时同步和0数据丢失。另外，Oracle Data Guard 可以设置延时应用时间窗口，从而防范错误操作、黑客攻击等人为错误导致的数据损坏。

### 1.5.1 保护模式和重做传输

> 要确定适当的保护模式，企业需要根据用户对系统响应时间的要求来估量它们对数据保护的业务要求。下表从数据丢失风险的角度概述了各种模式的适用性。

| 保护模式 | **在灾难出现时数据丢失的风险** | **重做传输机制** |
| ---------- | -------------------------------- | ------------------ |
| 最大保护 | 零数据丢失                     | LGWR SYNC        |
| 最大可用 | 零数据丢失                     | LGWR SYNC        |
| 最高性能 | 最小数据丢失                   | LGWR SYNC或ARCH  |

### 1.5.2 最大保护模式

> 最大保护模式为主数据库提供了最高水平的数据保护，从而确保一个全面的零数据丢失的灾难恢复解决方案。当在最大保护模式下运行时，重做记录由日志写入器 (LGWR) 进程从主数据库同步地传输到备用数据库，并且直到确认事务数据在至少一个备用服务器上的磁盘上可用时，才在主数据库上提交事务。

> 强烈建议，这种模式应至少配置两个备用数据库。当最后参与的备用数据库不可用时，主数据库上的处理将停止。这就确保了当主数据库与其所有备用数据库失去联系时，不会丢失事务。

> 由于重做传输的同步特性，这种最大保护模式可能潜在地影响主数据库响应时间。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。需要这种最大保护模式的企业有股票交易所、货币交易所、金融机构等。

### 1.5.3 最大可用性模式

> 最高可用性模式拥有仅次于最高水平的主数据库数据可用性。如同最大保护模式一样，重做数据由 LGWR 从主数据库同步地传输到备用数据库，直到确认事务数据在备用服务器的磁盘上可用时，事务才在主数据库上完成。不过，在这种模式下(与最大保护模式不同)，如果最后参与的备用数据库变为不可用，例如由于网络连接问题，处理将在主数据库上继续进行。备用数据库与主数据库相比，可能暂时落在后面，但当它再次变为可用时，备用数据库将使用主数据库上累积的归档日志自动同步，而不会丢失数据。

> 由于同步重做传输，这种保护模式可潜在地影响响应时间和吞吐量。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。

> 最高可用性模式适用于想要确保获得零数据丢失保护，但不想让生产数据库受网络/备用服务器故障影响的企业。如果又一个故障随后影响了生产数据库，然后最初的网络/备用服务器故障得到解决，那么这些企业将接受数据丢失的可能性。

### 1.5.4 最高性能模式

> 最高性能模式是默认的保护模式。它与最高可用性模式相比，提供了稍微少一些的主数据库数据保护，但提供了更高的性能。在这种模式下，当主数据库处理事务时，重做数据由 LGWR 进程异步传输到备用数据库上。另外，也可以将主数据库上的归档器进程 (ARCH) 配置为在这种模式下传输重做数据。在任何情况下，均先完成主数据库上的写操作，主数据库的提交操作不等待备用数据库确认接收。如果任意备用目标数据库变为不可用，则处理将在主数据库上继续进行，这对性能只有很小的影响或没有影响。

> 在主数据库出现故障的情况下，尚未被发送到备用数据库的重做数据会丢失。但是，如果网络有足够的吞吐量来跟上重做流量高峰，并且使用了 LGWR 进程来将重做流量传输到备用服务器，则丢失的事务将非常少或者为零。

> 当主数据库上的可用性和性能比丢失少量数据的风险更重要时，应该使用最高性能模式。这种模式还适合于 WAN 上的 Data Guard 部署，在 WAN 中，网络的内在延迟可能限制同步重做传输的适用性。

# 二、环境准备

## 2.1环境规划：RAC+DG

> 实验基于RAC+DG(ASM)，即Primary2.1.1+DG2.1.3配置
> 注：备库中的实例名是参数设置的，针对备库不同环境的配置均有说明

> 如果使用VM搭建RAC，需要设置ASM磁盘组永久+独立，VM不会对这些磁盘组做快照。所以使用不同方式搭建备库时，RAC环境的磁盘信息是独立的，无法通过虚拟机恢复到某个点。

### 2.1.1 Primary Database

|                    | Primary(Node1)                          | Primary(Node2) |
| -------------------- | ----------------------------------------- | ---------------- |
| **#主机和IP信息**  |                                         |                |
| Hostname           | rac01                                   | rac02          |
| DB Type            | RAC                                     |                |
| OS                 | Centos 7.9                              |                |
| DB_Version         | 12.1.0.2.0                              |                |
| ORACLE_BASE        | /u01/app/oracle                         |                |
| ORACLE_HOME        | /u01/app/oracle/product/12.1.0/dbhome_1 |                |
| Public IP          | 192.168.10.101                          | 192.168.10.102 |
| Private IP         | 192.168.20.101                          | 192.168.20.102 |
| Virtual IP         | 192.168.10.201                          | 192.168.10.202 |
| Scan IP            | 192.168.10.100                          |                |
| **# 存储信息**     |                                         |                |
| 磁盘组             | +DATA                                   |                |
| 归档               | +FRA                                    |                |
| OCR                | +OCR                                    |                |
| **# 数据库名信息** |                                         |                |
| DB_NAME            | orcl                                    |                |
| Instance_Name      | orcl1                                   | orcl2          |
| ORACLE_SID         | orcl1                                   | orcl2          |
| DB_Unique_Name     | orcl                                    |                |
| service_names      | orcl                                    |                |
| TNS_Name           |                                         |                |
| 字符集             |                                         |                |

### 2.1.2 Standby Database：Single DB

| **Single  Database** |                                         |
| ---------------------- | ----------------------------------------- |
| **#主机和IP信息**    |                                         |
| Hostname             | orclasm                                 |
| DB Type              | DB                                      |
| OS                   | Centos 7.9                              |
| DB_Version           | 12.1.0.2.0                              |
| ORACLE_BASE          | /u01/app/oracle                         |
| ORACLE_HOME          | /u01/app/oracle/product/12.1.0/dbhome_1 |
| IP                   | 192.168.10.22                           |
| **# 存储信息**       |                                         |
|                      | 文件系统                                |
| **# 数据库名信息**   |                                         |
| DB_NAME              | orcl                                    |
| Instance_Name        | orcldg                                  |
| ORACLE_SID           | orcldg                                  |
| DB_Unique_Name       | orcldg                                  |
| service_names        | orcldg                                  |
| TNS_Name             | orcldg                                  |
| 字符集               |                                         |

### 2.1.3 Standby Database：ASM+DB

> 本文将DATA和OCR放在同一磁盘组

| **ASM Database** | |
| -------------------- | ----------------------------------------- |
| **#主机和IP信息** | |
| Hostname | orclasm |
| DB Type | ASM |
| OS | Centos 7.9 |
| DB_Version | 12.1.0.2.0 |
| ORACLE_BASE | /u01/app/oracle |
| ORACLE_HOME | /u01/app/oracle/product/12.1.0/dbhome_1 |
| IP | 192.168.10.22 |
| **# 存储信息** | |
| 磁盘组 | +DATA |
| | |
| 归档 | +FRA |
| | |
| **# 数据库名信息** | |
| DB_NAME | orcl |
| Instance_Name | orcldg |
| ORACLE_SID | orcldg |
| DB_Unique_Name | orcldg |
| service_names | orcldg |
| TNS_Name | orcldg |
| 字符集 | |

### 2.1.4 Standby Database：RAC

## 2.2数据库安装(略)

> 在主库上安装RAC，并建监听和实例
> 如果备库上装有GI，要创建ASM实例，DB选择只安装软件即可，或者drop现有DB实例

# 三、主库配置

## 3.1CDB和Non CDB环境下

> 如果实例处于多租户架构中，设置操作和Non-CDB方法相同，但要在CDB下完成；
> 如果实验中有两个PDB1和PDB2，在创建备库后，默认两个PDB都会同步到备库，也可以通过参数指定只同步某个PDB；
> 也可以设置完同步的备库后，主库中再添加的PDB3也会同步到备库中。
> CDB环境下的实验配置中的主库实例也是orcl,备库是orcldg，需要设置主备不同的DB_UNIQUE_NAME

## 3.2开启归档

```sql_more
# 如果是CDB环境，先检查处于CDB根容器中，PDB下是不允许的
SQL> show con_name
CON_NAME
------------------
CDB$ROOT
# 查看归档是否Enable
SYS@orcl1> archive log list
Database log mode				No Archive Mode
Automatic archival			Disabled
Archive destination			USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence	15
Current log sequence			16
# 这里的归档路径是默认的 USE_DB_RECOVERY_FILE_DEST
# 如果是Disabled没有开启
# ===Single数据库中开启归档的步骤=======
shutdown immediate
startup mount
alter database archivelog;
alter database open;
SYS@orcl1> alter system switch logfile;
# ===RAC中开启归档的步骤===============
# 1、停止所有节点数据库
[grid@rac01 ~]$  srvctl stop database -d orcl -o immediate
[grid@rac01 ~]$  crsctl stat res -t
[grid@rac01 ~]$  srvctl start database -d orcl -o mount
# 2、开启归档后，开启所有节点数据库
[grid@rac01 ~]$  su - oracle
[oracle@rac01 ~]$  sqlplus / as sysdba
SYS@orcl1> alter database archivelog;
SYS@orcl1> alter database open;
SYS@orcl1> alter system switch logfile;
[oracle@rac02 ~]$  sqlplus / as sysdba
SYS@orcl2> alter database open;
# 3、各个节点归档日志序号不用相同
SYS@orcl2> archive log list
```

## 3.3开启闪回

> 闪回区的管理，及闪回日志管理，数据库能闪回到过去的多久时间点，这个由闪回区大小以db_flashback_retention_target 参数控制，在闪回区大小足够的情况，下默认能闪回1440秒也就是一天的数据。

```sql_more
SYS@orcl1> show parameter flashback
NAME                            TYPE        	VALUE
------------------------------	--------	---------------------------
db_flashback_retention_target	integer    	1440
# 1、查看闪回未开启
SYS@orcl1> select flashback_on from v$database;
FLASHBACK_ON
------------
NO
# 2、查看db_recovery_file_dest
SYS@orcl1>show parameter recovery
NAME                         		TYPE        	VALUE
-----------------------------		--------	-----------------------
db_recovery_file_dest			string      	+FRA
db_recovery_file_dest_size		big integer 	4785M
recovery_parallelism			integer    	0
# 一般安装RAC都会指定闪回区size和闪回目录路径+FRA
# (单实例中，顺序必须先设置闪回区大小，才能指定闪回目录，否则报错)
# 3、直接开启闪回
SYS@orcl1> alter database flashback on;
Database altered.
# 检查其他节点同步开启
SYS@orcl2> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
```

## 3.4设置数据库强制归档

> 有一些DDL语句可以通过指定NOLOGGING子句的方式避免写REDO(目的是提高速度，某些时候确实有效)。指定数据库为Force Logging模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作，而忽略类似NOLOGGING之类的指定参数。

> 如果在执行Force Logging时有NOLOGGING之类的语句在执行，那么Force Logging会等待，直到这类语句全部执行。
> Force Logging是作为固定参数保存在控制文件中，因此其不受重启之类操作的影响(只执行一次即可)，如果想取消，可以通过ALTER DATABASE NO FORCE LOGGING语句关闭强制记录。

```sql_more
SYS@orcl1> SELECT NAME, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
NAME      OPEN_MODE            FORCE_LOGGING
--------- -------------------- ---------------------------------------
ORCL      READ WRITE           NO
# 节点1开启
SYS@orcl1> alter database force logging;
Database altered.
# 节点2查看
SYS@orcl2> SELECT NAME, OPEN_MODE, FORCE_LOGGING FROM V$DATABASE;
NAME      OPEN_MODE            FORCE_LOGGING
--------- -------------------- ---------------------------------------
ORCL      READ WRITE           YES
```

## 3.5添加Standby redo log

#### 3.5.1 说明：

> 为主库添加standby redo log后，备库自动同步，所以备库不用再创建standby redo log Data Guard在最大保护和最高可用性模式下，Standby数据库必须配置standby redo log

#### 3.5.2 作用：

> 实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将主库接收到的重做日志按顺序导入到standby  logfile 在主库创建Standby logfile是便于发生角色转换后备用

#### 3.5.3 创建原则：

> 确保Standby redo log的大小与主库online redo log的大小一致
> 如果主库为单实例数据库：Standby redo log组数=主库日志总数+1
> 如果主库是RAC数据库：Standby redo log组数=(每个线程的日志数+1)*最大线程数,线程数即thread#
> 不建议复用Standby redo log，避免增加额外的I/O以及延缓重做传输

```sql_more
# 如果是CDB环境，先检查处于CDB根容器中，PDB下是不允许的；
# 在Oracle 12c的架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件；  
# 所以如果是CDB下，就在CDB中加 Standby redo log。
# 1、查看组数=2,每组2个线程THREAD  
SYS@orcl1> select count(group#),thread# from v$log group by thread#;
COUNT(GROUP#)    THREAD#
------------- ----------
            2          1
            2          2  
# 2、大小=50M
SYS@orcl1> select group#,bytes/1024/1024 from v$log;
    GROUP# BYTES/1024/1024
---------- ---------------
         1              50
         2              50
         3              50
         4              50
# 3、创建standby logfile(3+1组、每组50M)
    # 视图v$standby_log还没有创建standby log
col MEMBER for a50
SYS@orcl1> select * from v$standby_log;
no rows selected
    # 查看logfile，将standby log也放在该目录
SYS@orcl1> select group#,status,type,member from v$logfile order by member;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         1         ONLINE  +DATA/ORCL/ONLINELOG/group_1.262.1062286541
         2         ONLINE  +DATA/ORCL/ONLINELOG/group_2.263.1062286543
         3         ONLINE  +DATA/ORCL/ONLINELOG/group_3.266.1062286795
         4         ONLINE  +DATA/ORCL/ONLINELOG/group_4.267.1062286795
         1         ONLINE  +FRA/ORCL/ONLINELOG/group_1.257.1062286543
         2         ONLINE  +FRA/ORCL/ONLINELOG/group_2.258.1062286543
         3         ONLINE  +FRA/ORCL/ONLINELOG/group_3.259.1062286795
         4         ONLINE  +FRA/ORCL/ONLINELOG/group_4.260.1062286795
    # === RAC下添加standby log =====================
    # 上面查出组数=2,每组2个THREAD，根据规则需要(2+1)*2=6组【(每组线程数2+1)最大线程数2】，大小50M
alter database add standby logfile thread 1 group 5 '+DATA' size 50m;
alter database add standby logfile thread 1 group 6 '+DATA' size 50m;
alter database add standby logfile thread 1 group 7 '+DATA' size 50m;
alter database add standby logfile thread 2 group 8 '+DATA' size 50m;
alter database add standby logfile thread 2 group 9 '+DATA' size 50m;
alter database add standby logfile thread 2 group 10 '+DATA' size 50m;
 # 4、节点2上验证查看
SYS@orcl2> select group#,bytes/1024/1024 from v$standby_log;
    GROUP# BYTES/1024/1024
---------- ---------------
         5              50
         6              50
         7              50
         8              50
         9              50
        10              50
6 rows selected.
SYS@orcl2> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         2         ONLINE  +DATA/ORCL/ONLINELOG/group_2.263.1062286543
         2         ONLINE  +FRA/ORCL/ONLINELOG/group_2.258.1062286543
         1         ONLINE  +DATA/ORCL/ONLINELOG/group_1.262.1062286541
         1         ONLINE  +FRA/ORCL/ONLINELOG/group_1.257.1062286543
         3         ONLINE  +DATA/ORCL/ONLINELOG/group_3.266.1062286795
         3         ONLINE  +FRA/ORCL/ONLINELOG/group_3.259.1062286795
         4         ONLINE  +DATA/ORCL/ONLINELOG/group_4.267.1062286795
         4         ONLINE  +FRA/ORCL/ONLINELOG/group_4.260.1062286795
         5         STANDBY +DATA/ORCL/ONLINELOG/group_5.269.1081897411
         6         STANDBY +DATA/ORCL/ONLINELOG/group_6.270.1081897411
         7         STANDBY +DATA/ORCL/ONLINELOG/group_7.271.1081897411
         8         STANDBY +DATA/ORCL/ONLINELOG/group_8.272.1081897411
         9         STANDBY +DATA/ORCL/ONLINELOG/group_9.273.1081897411
        10         STANDBY +DATA/ORCL/ONLINELOG/group_10.274.1081897411
14 rows selected.
```

## 3.6修改参数文件

> 参数文件要对应备库环境修改，主要是存储路径不同，装有GI的对应ASM存储，只有DB的是文件系统路径

### 3.6.1 设置DB唯一名称

> 因为是rac，sid有orcl1和orcl2，所以设置参数后面需要添加sid='*'
> 通常主库的DB名和唯一名相同,不需要修改，修改相同的话会提示已经存在，show参数查看;
> alter system set db_unique_name='orcl' scope=spfile sid='*';

```sql_more
其中dg_config填写的是主备库的db_unique_name
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=spfile sid='*';
```

### 3.6.2 设置归档日志的路径

> 第一个ORCLDG是备库tnsname.ora的连接名(最开头名称)
> 第二个orcldg是DB_UNIQUE_NAME
> 本地的archive路径没有修改，本文使用的默认的USE_DB_RECOVERY_FILE_DEST

```sql_more
#  =前后不能有空格
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile sid='*';
alter system set log_archive_dest_2='SERVICE=ORCLDG ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcldg' scope=spfile sid='*';
```

### 3.6.3 启用设置的日志路径

```sql_more
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
```

### 3.6.4 设置归档日志进程的最大数量

> (视实际情况调整)

```sql_more
alter system set log_archive_max_processes=30 scope=both sid='*';
```

### 3.6.5 设置备库从哪个数据库获取归档日志

> 只对standby库有效，在主库上设置是为了在故障切换后，主库可以成为备库使用，值就是TNSNAME
> fal表示fetch archive log
> fal_client用于发送日志，fal_server用于接受日志。也即无论是主库或备库，fal_server=对方，fal_client=自己

```sql_more
alter system set fal_server=orcldg;
alter system set fal_client=orcl;
```

### 3.6.6 设置文件管理模式

> 表示如果Primary数据库数据文件发生修改(如新建、重命名等)则按照本参数的设置在Standby数据库中作相应修改。
> 设为AUTO表示自动管理。设为MANUAL表示需要手工管理
> 此项设置为自动，不然在主库创建数据文件后，备库不会自动创建
> 需要重启数据库生效

```sql_more
alter system set standby_file_management=auto scope=spfile sid='*';
```

### 3.6.7 主备文件路径

> 如果主备库文件的存放路径不同，还需要设置以下两个参数(需要重启数据库生效)
> 如果备库备库环境只有DB和装有ASM备库存储的路径不一样

```sql_more
# 根据备库存储系统不同，注意路径修改
# 使用的ASM可以直接写'+DATA'等
# ===装有ASM的修改===这里主备ASM存储路径相同
alter system set db_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA','+DATA' scope=spfile sid='*';
# ===只有DB的修改====
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcldg','+DATA' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcldg','+DATA' scope=spfile sid='*';
```

### 3.6.8 设置数据库口令文件的使用模式

> 默认也是EXCLUSIVE

```sql_more
SYS@orcl1> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile sid='*';
SYS@orcl1> show parameter remote_login_passwordfile
NAME                       	TYPE		VALUE
--------------------------	----------	-------
remote_login_passwordfile    	string		EXCLUSIVE
```

### 3.6.9 设置默认监听

> 此处直接让监听为空即可保持后面创建的默认静态监听，否则备库无法从参数文件启动或者如果想要设置监听值，也可以如下设置
> alter system set local_listener='(DESCRIPTION (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.2)(PORT=1521)))';

```sql_more
# =====之前主库单实例的情况下======
SQL> show parameter local_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string     LISTENER_ORCL
alter system set local_listener='';
SQL> show parameter local_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
# ======查看RAC下，无需修改======
SYS@orcl1> show parameter local_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 192.168.10.201)(PORT=1521))
SYS@orcl2> show parameter local_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 192.168.10.202)(PORT=1521))
```

# 四、备库配置

## 4.1 变量环境

> 单实例DB，根据实际配置环境变量

```bash
[oracle@orclasm ~]$  cat .bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export EDITOR=vi
export ORACLE_SID=orcldg
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:/bin:/u
```

> 装有GI，Grid用户环境配置环境变量

```bash
[grid@orclasm ~]$  cat .bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export EDITOR=vi
export ORACLE_SID=+ASM

export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/grid
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export PATH=$ORACLE_HOME/bin:$PATH

```

> 使变量生效

```bash
source~/.bash_profile
```

## 4.2 修改参数文件

### 4.2.1 复制主库参数文件

> 备库的参数文件根据主库参数进行修改主库上创建pfile，然后拷贝给备库
> 存在ASM存储的静参文件也是放在家目录的dbs下

```sql_more
# 主库创建静参文件，拷贝给备库
SYS@orcl1> create pfile from spfile;
File created.
# 拷贝后的静态参数文件格式：init+sid.ora
$  scp $ORACLE_HOME/dbs/initorcl1.ora  oracle@192.168.10.22:$ORACLE_HOME/dbs/initorcldg.ora
```

### 4.2.2 原initorcldg.ora内容

> 如果备库也是ASM自动管理内存，标黄的参数可以删掉，无需配置；
> 备库也是单实例，rac中需要的参数都删除；
> 其他按照和主库修改反着修改，标绿的参数

[oracle@orclasm dbs]$ cat initorcldg.ora
orcl2.__data_transfer_cache_size=0
orcl1.__data_transfer_cache_size=0
orcl1.__db_cache_size=1308622848
orcl2.__db_cache_size=1275068416
orcl2.__java_pool_size=16777216
orcl1.__java_pool_size=16777216
orcl2.__large_pool_size=33554432
orcl1.__large_pool_size=33554432
orcl1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=1308622848
orcl1.__pga_aggregate_target=1308622848
orcl2.__sga_target=1962934272
orcl1.__sga_target=1962934272
orcl2.__shared_io_pool_size=67108864
orcl1.__shared_io_pool_size=67108864
orcl1.__shared_pool_size=520093696
orcl2.__shared_pool_size=553648128
orcl2.__streams_pool_size=0
orcl1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/ORCL/CONTROLFILE/current.261.1062286539','+FRA/ORCL/CONTROLFILE/current.256.1062286539'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA','+DATA'
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCL'
*.fal_server='ORCLDG'
orcl1.instance_number=1
orcl2.instance_number=2
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name='orcl'
*.log_archive_dest_2='SERVICE=ORCLDG ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name='orcldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA','+DATA'
*.memory_target=3120m
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
orcl2.thread=2
orcl1.thread=1
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'

### 4.2.3 修改后的参数文件

> 当前是备库在ASM实例下，对应+DATA、FRA等ASM存储路径
> 如果是只有DB的单实例，这里根据主库修改的参数更换备库的文件系统路径即可

```bash
[oracle@orclasm dbs]$  cat initorcldg.ora 
# 修改为备库的orcldg
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='12.1.0.2.0'
# 备库控制文件路径
*.control_files='+DATA/ORCLDG/CONTROLFILE/current.261.1062286539','+FRA/ORCLDG/CONTROLFILE/current.256.1062286539'
*.db_block_size=8192
# 通过 show parameter db_
# 在rac里，参数db_create_file_dest默认已经被指定好了，创建表空间时不需要写目录和名字。
# 单实例中，参数db_create_file_dest默认空值要手动指定的，此处也可以删除参数配置，或指定到数据文件路径
*.db_create_file_dest='+DATA'
*.db_domain=''
# 主备的文件对应位置
*.db_file_name_convert='+DATA','+DATA'
*.db_name='orcl'
# 主库中默认使用和原DB相同的唯一名，没有该参数，这里要添加备库的唯一名
*.db_unique_name='orcldg'
# 备库恢复区
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
# 备库
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'
# 主备切换时设置
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcldg,orcl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='SERVICE=ORCL ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
# 主备的文件对应位置
*.log_file_name_convert='+DATA','+DATA'
*.memory_target=3120m
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
```

## 4.3 创建备库所需路径

> 每个人的环境不同，根据备库的参数信息创建所需目录
> 备库是ASM环境，更具参数文件创建相应的文件系统的路径和ASM存储路径

```bash
mkdir -p /u01/app/oracle/admin/orcldg/adump
# 当前备库只有+DATA存储
[grid@orclasm ~]$  asmcmd
ASMCMD> ls
DATA/
# 根据具主库ASM存储路径创在备库上创建相应的路径
# +DATA下
ASMCMD> cd DATA
ASMCMD> mkdir ORCLDG
ASMCMD> cd ORCLDG
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PASSWORD
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
# +FRA下
ASMCMD> cd ../..
ASMCMD> cd FRA
ASMCMD> mkdir ARCHIVELOG
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir FLASHBACK
ASMCMD> mkdir ONLINELOG
```

> 如果是单机DB文件存储需要的路径

```bash
mkdir -p /u01/app/oracle/admin/orcldg/adump
mkdir -p /u01/app/oracle/oradata/orcldg
mkdir -p /u01/app/oracle/fast_recovery_area
```

## 4.4 备库创建spfile

```sql_more
# 12c的ASM之后，动态参数文件存放在ASM存储中，静态参数还是家目录的dbs下，创建动态参数要指定到ASM中
SYS@orcldg> create spfile='+DATA/ORCLDG/PARAMETERFILE/spfileorcldg.ora' from pfile;
File created.
# 如果是只有单实例DB，照常创建即可
SYS@orcldg> create spfile from pfile;
File created.
```

## 4.5 备库密码文件

> Data Guard环境中，数据库的sys用户名密码要相同，可直接将主库复制密码文件复制到备库
> 家目录拷贝后备库的密码文件格式：orapw+sid，Windows下格式为：PWD[sid].ora
> ASM存储的密码文件格式：pwd+sid

```bash
# 11g的RAC环境密码文件依旧放在$ORACLE_HOME/dbs/目录下
[oracle@rac01 ~]$  cd $ORACLE_HOME/dbs/
[oracle@rac01 dbs]$  ls
hc_orcl1.dat  id_orcl1.dat  init.ora  initorcl1.ora orapworcl1
[oracle@rac02 ~]$  cd $ORACLE_HOME/dbs/
[oracle@rac02 dbs]$  ls
hc_orcl1.dat  id_orcl1.dat  init.ora  initorcl1.ora orapworcl2
# 12c以后的RAC环境$ORACLE_HOME/dbs/下是没有密码文件的，存放在ASM中
[oracle@rac01 ~]$  cd $ORACLE_HOME/dbs/
[oracle@rac01 dbs]$  ls
hc_orcl1.dat  id_orcl1.dat  init.ora  initorcl1.ora
#==如果备库是单实例DB环境，拷贝密码文件=============
#1、通过srvctl config database -d db_name查看密码文件
#2、主库从ASM中拷贝密码文件到系统文件中，如/tmp下
#3、再将密码文件scp到备库的 $ORACLE_HOME/dbs下即可
[grid@rac01 ~]$  asmcmd
ASMCMD> cd DATA/ORCL/PASSWORD
ASMCMD> ls
pwdorcl.256.1062286347
ASMCMD> cp +DATA/ORCL/PASSWORD/pwdorcl.256.1062286347 /tmp/orapworcldg
[grid@rac01 ~]$ su - oracle
[grid@rac01 ~]$ scp /tmp/orapworcldg oracle@192.168.10.22:$ORACLE_HOME/dbs/orapworcldg
#==如果备库是存在GI(ASM)的环境，拷贝密码文件===
#1、通过srvctl config database -d db_name查看密码文件
#2、主库从ASM中拷贝密码文件到系统文件中，如/tmp下
#3、再从主库拷贝到备库的/tmp下
#4、在备库ASM中创建密码文件(oracle用户)
#5、拷贝密码文件到备库家目录dbs下
[grid@rac01 ~]$  asmcmd
ASMCMD> cd DATA/ORCL/PASSWORD
ASMCMD> ls
pwdorcl.256.1062286347
ASMCMD> cp +DATA/ORCL/PASSWORD/pwdorcl.256.1062286347 /tmp
su - oracle
[oracle@rac01 ~]$ scp /tmp/pwdorcl.256.1062286347 192.168.10.22:/tmp/pwdorcldg
[oracle@rac01 ~]$ scp /tmp/pwdorcl.256.1062286347 192.168.10.22:$ORACLE_HOME/dbs/orapworcldg
[oracle@orclasm ~]$  orapwd input_file='/tmp/pwdorcldg' file='+DATA/ORCLDG/PASSWORD/pwdorcldg' dbuniquename='orcldg'
```

## 4.6添加数据库+注册密码和动参

```bash
# 添加数据库orcldg
[oracle@orclasm ~]$ srvctl add database -db orcldg -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1
# 注册文件
[oracle@orclasm ~]$ srvctl modify database -db orcldg -spfile '+DATA/ORCLDG/PARAMETERFILE/spfileorcldg.ora' -pwfile '+DATA/ORCLDG/PASSWORD/pwdorcldg'
# 查看
[oracle@orclasm ~]$  srvctl config database -d orcldg
Database unique name: orcldg
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCLDG/PARAMETERFILE/spfileorcldg.ora
Password file: +DATA/ORCLDG/PASSWORD/orapworcldg
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services: 
OSDBA group: 
OSOPER group: 
Database instance: orcldg
```

# 五、主备库监听

```bash
# 开机自启动监听
# 设置主备库自动开启监听 不建议设置自动开启数据库，因为DG开关有先后顺序，要手动开启
 ## 修改/etc/rc.d/rc.local文件
 su - root
 vi /etc/rc.d/rc.local # 增加一行(/etc/rc.local是/etc/rc.d/rc.local的软连接)
 su - oracle -c 'lsnrctl start'
 ## 授权rc.local文件可执行权限
 chmod +x /etc/rc.d/rc.local
```

## 5.1 备库注册静态监听

> 备库注册静态监听，备库是ASM实例的话，监听文件在grid用户下，配置文件写oracle的家目录
> 可以在hosts写所有节点信息，监听文件用主机名代替IP

```bash
[grid@orclasm admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/grid/product/12.1.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = orcldg)
    )
  )
VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.22)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
```

## 5.2 主备库TNS监听相同

```bash
[oracle@orclasm admin]$  cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.22)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldg)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
```

## 5.3 主备连接测试

```bash
# 主备库监听重启，有asm实例要在grid用户下
lsnrctl stop/start
# 主备测试
tnsping orcl/orcldg
```

# 六、【方式一：物理备库之RMAN Duplicate】

> Duplicate 方式创建物理备库；通过这种方式直接在线从主库搭建物理备库。

## 6.1 备库启动到nomount状态

```sql
SYS@orcldg>startup nomount
```

## 6.2 登陆RMAN连接主备库

> 主库target/orcl 备库auxiliary/orcldg
> 主备库DB_NAME必须一致，主库是open状态，备库是nomount状态

```sql_more
SYS@orcldg>exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@orclasm ~]$ rman target sys/Oracle#2020@orcl auxiliary sys/Oracle#2020@orcldg

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 1 11:21:40 2021

connected to target database: ORCL (DBID=1590067275)
connected to auxiliary database: ORCL (not mounted)

RMAN>
```

## 6.3 开始 Duplicate

```sql_more
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

Starting Duplicate Db at 01-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=16 device type=DISK
current log archived

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATA/ORCL/PASSWORD/pwdorcl.256.1062286347' auxiliary format 
 '+DATA/ORCLDG/PASSWORD/pwdorcldg'   ;
}
executing Memory Script

Starting backup at 01-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 instance=orcl1 device type=DISK
Finished backup at 01-SEP-21

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''+DATA/ORCLDG/CONTROLFILE/current.262.1082114625'', ''+FRA/ORCLDG/CONTROLFILE/current.256.1082114625'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone from service  'orcl' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/ORCLDG/CONTROLFILE/current.262.1082114625'', ''+FRA/ORCLDG/CONTROLFILE/current.256.1082114625'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/ORCLDG/CONTROLFILE/current.268.1082114627
output file name=+FRA/ORCLDG/CONTROLFILE/current.258.1082114627
Finished restore at 01-SEP-21

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+DATA";
   set newname for datafile  3 to 
 "+DATA";
   set newname for datafile  4 to 
 "+DATA";
   set newname for datafile  5 to 
 "+DATA";
   set newname for datafile  6 to 
 "+DATA";
   restore
   from service  'orcl'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:47
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:29
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:27
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 01-SEP-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'orcl' 
           archivelog from scn  3815802;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=33
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=34
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=35
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 01-SEP-21

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1082115080 file name=+DATA/ORCLDG/DATAFILE/system.267.1082114639
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1082115080 file name=+DATA/ORCLDG/DATAFILE/sysaux.266.1082114807
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1082115080 file name=+DATA/ORCLDG/DATAFILE/undotbs1.265.1082115017
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1082115080 file name=+DATA/ORCLDG/DATAFILE/undotbs2.264.1082115039
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1082115080 file name=+DATA/ORCLDG/DATAFILE/users.263.1082115055

contents of Memory Script:
{
   set until scn  3816761;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-SEP-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 39 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_39.260.1082115073
archived log for thread 1 with sequence 40 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_40.261.1082115075
archived log for thread 1 with sequence 41 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_41.262.1082115075
archived log for thread 2 with sequence 33 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_33.263.1082115077
archived log for thread 2 with sequence 34 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_34.264.1082115079
archived log for thread 2 with sequence 35 is already on disk as file +FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_35.265.1082115079
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_39.260.1082115073 thread=1 sequence=39
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_33.263.1082115077 thread=2 sequence=33
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_34.264.1082115079 thread=2 sequence=34
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_40.261.1082115075 thread=1 sequence=40
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_1_seq_41.262.1082115075 thread=1 sequence=41
archived log file name=+FRA/ORCLDG/ARCHIVELOG/2021_09_01/thread_2_seq_35.265.1082115079 thread=2 sequence=35
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-SEP-21
Finished Duplicate Db at 01-SEP-21
```

# 七、【方式二：物理备库之RMAN备份还原】

> 如果使用VM搭建RAC，需要设置ASM磁盘组永久+独立，VM不会对这些磁盘组做快照。所以实验中使用不同方式搭建备库时，RAC环境的ASM磁盘信息是独立的，无法通过虚拟机恢复到某个点。 ORACLE VIRTUAL BOX的共享存储也一样道理。
> 使用 RMAN 备份恢复方法，将备份的文件和控制文件传到备库恢复

## 7.1 主库RMAN备库

```sql_more
# 任意节点RMAN备份数据文件
# 这里使用默认备份路径
# RMAN备份
$  rman target /
RMAN> backup database;
Starting backup at 01-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1177 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.257.1062286391
input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.258.1062286435
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.260.1062286493
input datafile file number=00005 name=+DATA/ORCL/DATAFILE/undotbs2.265.1062286749
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.259.1062286491
channel ORA_DISK_1: starting piece 1 at 01-SEP-21
channel ORA_DISK_1: finished piece 1 at 01-SEP-21
piece handle=+FRA/ORCL/BACKUPSET/2021_09_01/nnndf0_tag20210901t183302_0.368.1082140383 tag=TAG20210901T183302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 01-SEP-21
channel ORA_DISK_1: finished piece 1 at 01-SEP-21
piece handle=+FRA/ORCL/BACKUPSET/2021_09_01/ncsnf0_tag20210901t183302_0.369.1082140701 tag=TAG20210901T183302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-SEP-21
```

## 7.2 主库备份控制文件

```sql_more
RMAN> backup current controlfile for standby;

Starting backup at 01-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 01-SEP-21
channel ORA_DISK_1: finished piece 1 at 01-SEP-21
piece handle=+FRA/ORCL/BACKUPSET/2021_09_01/ncnnf0_tag20210901t185112_0.370.1082141473 tag=TAG20210901T185112 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-SEP-21
```

## 7.3 拷贝备份文件到备库

```sql_more
#如果主备都是ASM存储
# 可已将ASM存储的文件直接拷贝给另一台ASM存储，但是要注意如下
# 备库创建和主库相同的ASM备份目录
[grid@orclasm ~]$  asmcmd
ASMCMD> cd FRA
ASMCMD> mkdir +FRA/ORCLDG/BACKUPSET/2021_09_01
# 主库传输
ASMCMD> cd +FRA/ORCL/BACKUPSET/2021_09_01
ASMCMD> ls
ncnnf0_TAG20210901T185112_0.370.1082141473
ncsnf0_TAG20210901T183302_0.369.1082140701
nnndf0_TAG20210901T183302_0.368.1082140383
# 拷贝命令，如下命令会报错"ORA-15046: ASM file name 'XXXXX' is not in single-file creation form"
# ASMCMD> cp +FRA/ORCL/BACKUPSET/2021_09_01/ncnnf0_TAG20210901T185112_0.370.1082141473 sys@192.168.10.22.+ASM:+FRA/ORCLDG/BACKUPSET
# Solution
# ：The cp command failed because the ASM file name was not in a form that can be used to create an single file.File name should not contain the file number/incarnation
# 目标文件不能有后面的数字，这是oracle用来标识ASM文件信息的
# 如下命令，目标路径将文件名去掉数字即可
ASMCMD> 
cp +FRA/ORCL/BACKUPSET/2021_09_01/ncnnf0_TAG20210901T185112_0.370.1082141473 sys/Oracle#2020@192.168.10.22.+ASM:+FRA/ORCLDG/BACKUPSET/2021_09_01/ncnnf0_TAG20210901T185112_0
cp +FRA/ORCL/BACKUPSET/2021_09_01/ncsnf0_TAG20210901T183302_0.369.1082140701 sys/Oracle#2020@192.168.10.22.+ASM:+FRA/ORCLDG/BACKUPSET/2021_09_01/ncsnf0_TAG20210901T183302_0
cp +FRA/ORCL/BACKUPSET/2021_09_01/nnndf0_TAG20210901T183302_0.368.1082140383 sys/Oracle#2020@192.168.10.22.+ASM:+FRA/ORCLDG/BACKUPSET/2021_09_01/nnndf0_TAG20210901T183302_0
ASMCMD> 
```

## 7.4 备库启动到nomount状态

```sql_more
# 使用pfile启动到nomount
# 如果已经创建spfile可以直接启动到 nomount状态
SYS@orcldg> startup nomount
```

## 7.5 备库恢复控制文件

> ASM环境直接附上拷贝后的路径

```sql_more
RMAN> restore standby controlfile from '+FRA/ORCLDG/BACKUPSET/2021_09_01/ncnnf0_tag20210901t185112_0';

Starting restore at 01-SEP-21
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ORCLDG/CONTROLFILE/current.262.1082145813
output file name=+FRA/ORCLDG/CONTROLFILE/current.261.1082145813
Finished restore at 01-SEP-21
```

## 7.6 备库mount

```sql_more
SYS@orcldg> alter database mount;
Database altered.
```

## 7.7 还原数据库

```sql_more
[oracle@orclasm ~]$  rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 1 20:05:38 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1590067275, not open)

RMAN> restore database;

Starting restore at 01-SEP-21
Starting implicit crosscheck backup at 01-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1161 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 01-SEP-21
Starting implicit crosscheck copy at 01-SEP-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 01-SEP-21
searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/ORCLDG/BACKUPSET/2021_09_01/ncnnf0_tag20210901t185112_0
File Name: +FRA/ORCLDG/BACKUPSET/2021_09_01/ncsnf0_tag20210901t183302_0
File Name: +FRA/ORCLDG/BACKUPSET/2021_09_01/nnndf0_tag20210901t183302_0
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.258.1062286435
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/sysaux.257.1062286391
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/undotbs1.260.1062286493
channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/datafile/undotbs2.265.1062286749
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/users.259.1062286491
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDG/BACKUPSET/2021_09_01/nnndf0_tag20210901t183302_0
channel ORA_DISK_1: piece handle=+FRA/ORCLDG/BACKUPSET/2021_09_01/nnndf0_tag20210901t183302_0 tag=TAG20210901T183302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
Finished restore at 01-SEP-21
```

# 八、开启并验证DG同步

## 8.1 开启实时同步

```sql_more
SYS@orcldg> alter database open;
Database altered.
#  如果是CDB环境，需要开启pdb
#  alter pluggable database all open;
SYS@orcldg> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
#  从12C开始，RECOVER语句，不需要再指定using current logfile，Oracle会自动判断日志应用是否是实时的。
```

## 8.2 备库开启闪回

```sql_more
# 查看闪回
SYS@orcldg> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
# 取消实时同步
alter database recover managed standby database cancel;
# 关闭数据库
shutdown immediate
# 开启到mount
startup mount
# 开启闪回
alter database flashback on;
# 开启数据库
alter database open;
# 再次开启同步
alter database recover managed standby database using current logfile disconnect from session;
# 查看闪回
SYS@orcldg> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
# 查看主库
SYS@orcl1> select log_mode,open_mode ,database_role from v$database;
LOG_MODE     OPEN_MODE            DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ WRITE           PRIMARY
SYS@orcl2> select log_mode,open_mode ,database_role from v$database;
LOG_MODE     OPEN_MODE            DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ WRITE           PRIMARY
# 查看备库
SYS@orcldg> select log_mode,open_mode ,database_role from v$database;
LOG_MODE     OPEN_MODE            DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY
```

## 8.3 归档同步验证

```sql_more
# 主库切换归档, 查询主库备库最大归档序号，一致即归档同步成功
SYS@orcl2> alter system archive log current;
SYS@orcl2> select max(sequence#) from v$archived_log;
SYS@orcldg> select max(sequence#) from v$archived_log;
# 查看主备归档日志是否能正常传输(APPLIED=yes)
SYS@orcl2> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
SYS@orcldg> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG;
```

## 8.4 查看主备库状态

```sql_more
# 主库
SYS@orcl1> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO STANDBY           PRIMARY
# 备库
SYS@orcldg> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED          PHYSICAL STANDBY
```

## 8.5 主库查看备库归档路径报错

```sql_more
# 主库 查看archive_log_dest_2列是否有error
SYS@orcl1> select status,error from v$archive_dest where dest_id=2;
STATUS    ERROR
--------- ------------------------------------------------------------
VALID
```

## 8.6 主库Dataguard的状态信息

```sql_more
col message format a100
SQL> select message_num,message from v$dataguard_status;
```

## 8.7 建表测试

```sql_more
# 主库
create table test(id number);
insert into test values(1);
commit;
select * from test;
# 备库
select * from test;
```

# 九、DG切换和恢复

> 配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。
> DG的故障切换分为switchover和failover两种

## 9.1 Switchover

### 9.1.1 主库上操作

```sql_more
# 1、主库查询状态
# 注意：下面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换
SYS@orcl1> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO STANDBY           PRIMARY
# 2、主库开始切换
SYS@orcl1> alter database commit to switchover to physical standby;
Database altered.
# 主库有会话连接的时候使用如下命令
# alter database commit to switchover to physical standby with session shutdown;
# 3、主库变备库，开启到mount状态
SYS@orcl1> startup mount
ORACLE instance started.
Total System Global Area 2466250752 bytes
Fixed Size                  2927384 bytes
Variable Size             671089896 bytes
Database Buffers         1778384896 bytes
Redo Buffers               13848576 bytes
Database mounted.
SYS@orcl1> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
```

### 9.1.2 备库上操作

```sql_more
# 1、备库查询状态
# 注意：下面查询结果为TO PRIMARY 或 SESSIONS ACTIVE表明可以进行切换
SYS@orcldg> select switchover_status,database_role from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE
-------------------- ----------------
TO PRIMARY           PHYSICAL STANDBY
# 2、备库开始切换
SYS@orcldg> alter database commit to switchover to primary with session shutdown;
Database altered.
# 3、开启数据库
SYS@orcldg> alter database open;
Database altered.
# 4、查询状态，已经变成主库
SYS@orcldg> select switchover_status,database_role,open_mode from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
TO STANDBY           PRIMARY          READ WRITE
```

### 9.1.3 新备库(原主库)上的操作

```sql_more
# 切换后新的备库是mount状态
# 1、开机数据库open
SYS@orcl1> alter database open;
SYS@orcl2> alter database open;
# 如果没有开启，shutdown在startup即可
# 2、开启同步
SYS@orcl1> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
# 3、主备检查切换状态
SYS@orcldg>select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE           PRIMARY          orcldg

SYS@orcl1> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY orcl

SYS@orcl2> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY orcl
```

### 9.1.4 验证

```sql_more
# 新主库切换归档，主备查询
SYS@orcldg> alter system archive log current;
SYS@orcldg> select max(sequence#) from v$archived_log;
# 新主库表插入数据测试
insert into test values(2); 
commit;
select * from test;
```

## 9.2 Failover

> Failover是当主库真正出现严重系统故障，如数据库宕机，软硬件故障导致主库不能支持服务，从而进行的切换动作。
> 注意：为了能够在failover后能够恢复DG,需要在主库上开启flashback，否则DG就可能需要重新搭建。

> 接着上面的操作，当前新主库是orcldg，备库是orcl。
> 先把主库关机，模拟Failover 由于主库已经不可访问，下面所有的操作都在备库完成：

### 9.2.1 备库上操作

```sql_more
SYS@orcl2> select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
```

### 9.2.2 停止实时同步

```sql_more
SYS@orcl2> alter database recover managed standby database cancel;
Database altered.
SYS@orcl2> alter database recover managed standby database finish force;
Database altered.
```

### 9.2.3 状态改为主库

```sql_more
SYS@orcl2> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
# 更改状态
SYS@orcl2> alter database commit to switchover to primary;
Database altered.
# 再次查看
SYS@orcl2> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
```

### 9.2.4 开启数据库open

```sql_more
# 开启数据库open
SYS@orcl1> alter database open;
SYS@orcl1> alter database open;
# 查看状态
SQL> select switchover_status,database_role,open_mode from v$database;
SWITCHOVER_STATUS    DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
FAILED DESTINATION   PRIMARY          READ WRITE
# 当主库的SWITCHOVER_STATUS状态为FAILED DESTINATION时，是因为备库不在mount状态下,这里已经没有备库
```

## 9.3 Failover恢复

> 上面提到了failover，这种情形是当主库真正出现异常之后，才会执行的操作，那么执行过failover 之后，如何在重新构建DG，这里我们利用flashback database来重构模拟关机的orcldg，具体方法如下：

### 9.3.1 在新主库orcl上执行

```sql_more
# 查询变成新主库的scn
SYS@orcl1> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
4240448
```

### 9.3.2 在老主库上执行

```sql_more
# 要将之前出问题的老主库变成备库
# 1、启动 mount 状态下
SYS@orcldg> startup mount
# 2、闪回到新的主库查询的scn
SYS@orcldg> flashback database to scn 4240448;
Flashback complete.
# 3、转为备库
SYS@orcldg> alter database convert to physical standby;
# 4、关闭
SYS@orcldg> shutdown immediate
# 5、开启
SYS@orcldg> startup
# 6、开启同步
SYS@orcldg> alter database recover managed standby database using current logfile disconnect from session;
# 7、同步验证(略)
# 这样就恢复到最初装完DG的环境，当前实例orcl的RAC仍是主库
```

# 十、DG开关机顺序

```sql_more
# 启动顺序：先启动备库，后启动主库
# 关闭顺序：先关闭主库，后关闭备库
---------------------------------------------------------------------------------
# 1、正确开启顺序
# 备库:
SQL> STARTUP MOUNT
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION;
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        # 逻辑备库:
            SQL> STARTUP
            SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; 
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# 主库:
SQL> STARTUP
---------------------------------------------------------------------------------
# 2、正确关闭顺序
# 主库:
SQL> SHUTDOWN IMMEDIATE
# 备库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        # 逻辑备库:
        SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> SHUTDOWN IMMEDIATE
```

最后修改：2022 年 04 月 19 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

【DG部署】【RAC+ DG(Single DB & ASM)】

lo8ol • 2021 年 08 月 02 日

# 一、Dataguard 介绍

## 1.1 概念

> Data Guard备用数据库可以与生产系统位于相同的数据中心，也可以是在地理位置上分布较远的的远程灾难备份中心。

## 1.2 原理

## 1.3 特点

## 1.4 搭建DG方式

> Data Guard备库按照从生产数据库传输过来的日志处理方法的不同分为物理备库、逻辑备库和快照备库。

### 1.4.1物理备库

### 1.4.2逻辑备库

### 1.4.3快照备库

> 可以暂时将物理备份数据库转换为可更新的数据库，快照备份数据不会立马应用接收到的日志文件，设一个时间点，一次性转换。

## 1.5 备库数据保护级别

### 1.5.1 保护模式和重做传输

### 1.5.2 最大保护模式

### 1.5.3 最大可用性模式

### 1.5.4 最高性能模式

# 二、环境准备

## 2.1环境规划：RAC+DG

> 实验基于RAC+DG(ASM)，即Primary2.1.1+DG2.1.3配置
> 注：备库中的实例名是参数设置的，针对备库不同环境的配置均有说明

### 2.1.1 Primary Database

### 2.1.2 Standby Database：Single DB

### 2.1.3 Standby Database：ASM+DB

> 本文将DATA和OCR放在同一磁盘组

### 2.1.4 Standby Database：RAC

## 2.2数据库安装(略)

> 在主库上安装RAC，并建监听和实例
> 如果备库上装有GI，要创建ASM实例，DB选择只安装软件即可，或者drop现有DB实例

# 三、主库配置

## 3.1CDB和Non CDB环境下

## 3.2开启归档

## 3.3开启闪回

## 3.4设置数据库强制归档

## 3.5添加Standby redo log

#### 3.5.1 说明：

#### 3.5.2 作用：

#### 3.5.3 创建原则：

## 3.6修改参数文件

> 参数文件要对应备库环境修改，主要是存储路径不同，装有GI的对应ASM存储，只有DB的是文件系统路径

### 3.6.1 设置DB唯一名称

```sql_more
其中dg_config填写的是主备库的db_unique_name
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=spfile sid='*';
```

### 3.6.2 设置归档日志的路径

> 第一个ORCLDG是备库tnsname.ora的连接名(最开头名称)
> 第二个orcldg是DB_UNIQUE_NAME
> 本地的archive路径没有修改，本文使用的默认的USE_DB_RECOVERY_FILE_DEST

### 3.6.3 启用设置的日志路径

```sql_more
alter system set log_archive_dest_state_1=enable scope=spfile sid='*';
alter system set log_archive_dest_state_2=enable scope=spfile sid='*';
```

### 3.6.4 设置归档日志进程的最大数量

> (视实际情况调整)

```sql_more
alter system set log_archive_max_processes=30 scope=both sid='*';
```

### 3.6.5 设置备库从哪个数据库获取归档日志

```sql_more
alter system set fal_server=orcldg;
alter system set fal_client=orcl;
```

### 3.6.6 设置文件管理模式

```sql_more
alter system set standby_file_management=auto scope=spfile sid='*';
```

### 3.6.7 主备文件路径

> 如果主备库文件的存放路径不同，还需要设置以下两个参数(需要重启数据库生效)
> 如果备库备库环境只有DB和装有ASM备库存储的路径不一样

### 3.6.8 设置数据库口令文件的使用模式

> 默认也是EXCLUSIVE

### 3.6.9 设置默认监听

# 四、备库配置

## 4.1 变量环境

> 单实例DB，根据实际配置环境变量

> 装有GI，Grid用户环境配置环境变量

```

> 使变量生效

```bash
source~/.bash_profile
```

## 4.2 修改参数文件

### 4.2.1 复制主库参数文件

> 备库的参数文件根据主库参数进行修改主库上创建pfile，然后拷贝给备库
> 存在ASM存储的静参文件也是放在家目录的dbs下

### 4.2.2 原initorcldg.ora内容

### 4.2.3 修改后的参数文件

> 当前是备库在ASM实例下，对应+DATA、FRA等ASM存储路径
> 如果是只有DB的单实例，这里根据主库修改的参数更换备库的文件系统路径即可

## 4.3 创建备库所需路径

> 每个人的环境不同，根据备库的参数信息创建所需目录
> 备库是ASM环境，更具参数文件创建相应的文件系统的路径和ASM存储路径

> 如果是单机DB文件存储需要的路径

```bash
mkdir -p /u01/app/oracle/admin/orcldg/adump
mkdir -p /u01/app/oracle/oradata/orcldg
mkdir -p /u01/app/oracle/fast_recovery_area
```

## 4.4 备库创建spfile

## 4.5 备库密码文件

## 4.6添加数据库+注册密码和动参

# 五、主备库监听

## 5.1 备库注册静态监听

> 备库注册静态监听，备库是ASM实例的话，监听文件在grid用户下，配置文件写oracle的家目录
> 可以在hosts写所有节点信息，监听文件用主机名代替IP

## 5.2 主备库TNS监听相同

## 5.3 主备连接测试

```bash
# 主备库监听重启，有asm实例要在grid用户下
lsnrctl stop/start
# 主备测试
tnsping orcl/orcldg
```

# 六、【方式一：物理备库之RMAN Duplicate】

> Duplicate 方式创建物理备库；通过这种方式直接在线从主库搭建物理备库。

## 6.1 备库启动到nomount状态

```sql
SYS@orcldg>startup nomount
```

## 6.2 登陆RMAN连接主备库

> 主库target/orcl 备库auxiliary/orcldg
> 主备库DB_NAME必须一致，主库是open状态，备库是nomount状态

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 1 11:21:40 2021

connected to target database: ORCL (DBID=1590067275)
connected to auxiliary database: ORCL (not mounted)

RMAN>
```

## 6.3 开始 Duplicate

```sql_more
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATA/ORCL/PASSWORD/pwdorcl.256.1062286347' auxiliary format 
 '+DATA/ORCLDG/PASSWORD/pwdorcldg'   ;
}
executing Memory Script

Starting backup at 01-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 instance=orcl1 device type=DISK
Finished backup at 01-SEP-21

sql statement: alter system set  control_files =   ''+DATA/ORCLDG/CONTROLFILE/current.262.1082114625'', ''+FRA/ORCLDG/CONTROLFILE/current.256.1082114625'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'orcl' 
           archivelog from scn  3815802;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 01-SEP-21
using channel ORA_AUX_DISK_1

contents of Memory Script:
{
   set until scn  3816761;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-SEP-21
using channel ORA_AUX_DISK_1

starting media recovery

# 七、【方式二：物理备库之RMAN备份还原】

## 7.1 主库RMAN备库

## 7.2 主库备份控制文件

```sql_more
RMAN> backup current controlfile for standby;

## 7.3 拷贝备份文件到备库

## 7.4 备库启动到nomount状态

```sql_more
# 使用pfile启动到nomount
# 如果已经创建spfile可以直接启动到 nomount状态
SYS@orcldg> startup nomount
```

## 7.5 备库恢复控制文件

> ASM环境直接附上拷贝后的路径

```sql_more
RMAN> restore standby controlfile from '+FRA/ORCLDG/BACKUPSET/2021_09_01/ncnnf0_tag20210901t185112_0';

Starting restore at 01-SEP-21
using channel ORA_DISK_1

## 7.6 备库mount

```sql_more
SYS@orcldg> alter database mount;
Database altered.
```

## 7.7 还原数据库

RMAN> restore database;

# 八、开启并验证DG同步

## 8.1 开启实时同步

## 8.2 备库开启闪回

## 8.3 归档同步验证

## 8.4 查看主备库状态

## 8.5 主库查看备库归档路径报错

## 8.6 主库Dataguard的状态信息

```sql_more
col message format a100
SQL> select message_num,message from v$dataguard_status;
```

## 8.7 建表测试

```sql_more
# 主库
create table test(id number);
insert into test values(1);
commit;
select * from test;
# 备库
select * from test;
```

# 九、DG切换和恢复

> 配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。
> DG的故障切换分为switchover和failover两种

## 9.1 Switchover

### 9.1.1 主库上操作

### 9.1.2 备库上操作

### 9.1.3 新备库(原主库)上的操作

### 9.1.4 验证

## 9.2 Failover

> 接着上面的操作，当前新主库是orcldg，备库是orcl。
> 先把主库关机，模拟Failover 由于主库已经不可访问，下面所有的操作都在备库完成：

### 9.2.1 备库上操作

```sql_more
SYS@orcl2> select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
```

### 9.2.2 停止实时同步

```sql_more
SYS@orcl2> alter database recover managed standby database cancel;
Database altered.
SYS@orcl2> alter database recover managed standby database finish force;
Database altered.
```

### 9.2.3 状态改为主库

### 9.2.4 开启数据库open

## 9.3 Failover恢复

### 9.3.1 在新主库orcl上执行

### 9.3.2 在老主库上执行

# 十、DG开关机顺序

【DG部署】【RAC+ DG(Single DB & ASM)】

发表评论取消回复

【DG部署】【RAC+ DG(Single DB & ASM)】

【OPatch】Apply Oracle RAC 19c Patch

【RMAN】一周备份计划

【Docker】面试题

【DG部署】【Single DB+Single DG】

【DG部署】【Single DB+Single DG】

【部署】【Oracle静默建库】

【部署】【删除Oracle数据库实例的方式】

【部署】【卸载Oracle软件】

【shell】Linux中的命令之一：常用系统工作命令

【DG部署】【RAC+ DG(Single DB & ASM)】

发表评论 取消回复

【DG部署】【RAC+ DG(Single DB & ASM)】

发表评论取消回复