Oracle 12c Dataguard RAC版部署-CSDN博客

本文链接：https://blog.csdn.net/qq942451993/article/details/134361800

欢迎关注公众号：一介IT
本站博文抢先发布在公众号。

摘自个人网站，文章原文地址 https://l080l.com/oracle/deploy/racdg12c.html

本文 4.5万 字，有不同方式部署DG，如果是实验，每做完一个方式建议做好快照，请按照目录阅读。
相关推荐【Oracle 12c Dataguard 单机版部署】

文章目录

1. Dataguard介绍

(略)。理论介绍部分参考上一篇内容 Oracle 12c Dataguard 单机版部署，这里直接实战安装部署。

2. 环境准备

2.1 环境规划：RAC+DG

本小结列出了四个数据库方案，但实验基于RAC+DG(ASM存储)，即Primary是本文2.1.1章节配置和DG是本文2.1.3章节配置。注：本实验备库中的实例名是参数设置的，针对备库不同环境的配置均有说明。如果使用VM搭建RAC，需要设置ASM磁盘组永久+独立，VM不会对这些磁盘组做快照。所以使用不同方式搭建备库时，RAC环境的磁盘信息是独立的，无法通过虚拟机恢复到某个点。(后续会出使用openfiler做ASM存储的文章，到时候就可以给磁盘做快照，可以随时还原快照做各种实验了)。

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. 主库配置

3.1 CDB和Non CDB环境下

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

3.2 开启归档

# 如果是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秒也就是一天的数据。

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语句关闭强制记录。

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以及延缓重做传输。

# 如果是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='*';

-- 其中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。

#  =前后不能有空格
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 启用设置的日志路径

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 设置归档日志进程的最大数量

# (视实际情况调整)。
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=自己
alter system set fal_server=orcldg;
alter system set fal_client=orcl;

3.6.6 设置文件管理模式

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

3.6.7 主备文件路径

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

# 根据备库存储系统不同，注意路径修改
# 使用的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。

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> 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. 备库配置

4.1 变量环境

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

[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用户环境配置环境变量。

[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

# 使变量生效。
source~/.bash_profile

4.2 修改参数文件

4.2.1 复制主库参数文件

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

# 主库创建静参文件，拷贝给备库
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的单实例，这里根据主库修改的参数更换备库的文件系统路径即可。

[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存储路径。

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文件存储需要的路径。

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

# 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。

# 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添加数据库+注册密码和动参

# 添加数据库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

5. 主备库监听

# 开机自启动监听
# 设置主备库自动开启监听 不建议设置自动开启数据库，因为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。

[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监听相同

[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 主备连接测试

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

6. 方式一：物理备库之RMAN Duplicate

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

6.1 备库启动到nomount状态

SYS@orcldg> startup nomount

6.2 登陆RMAN连接主备库

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

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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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

RMAN>

6.3 开始 Duplicate

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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

7. 方式二：物理备库之RMAN备份还原

使用 RMAN 备份恢复方法，将备份的文件和控制文件传到备库恢复。

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

7.1 主库RMAN备库

# 任意节点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 主库备份控制文件

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 拷贝备份文件到备库

#如果主备都是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状态

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

7.5 备库恢复控制文件

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

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

SYS@orcldg> alter database mount;
Database altered.

7.7 还原数据库

[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

8. 开启并验证DG同步

8.1 开启实时同步

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 备库开启闪回

# 查看闪回
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 归档同步验证

# 主库切换归档, 查询主库备库最大归档序号，一致即归档同步成功
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 查看主备库状态

# 主库
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 主库查看备库归档路径报错

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

8.6 主库Dataguard的状态信息

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

8.7 建表测试

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

9. DG切换和恢复

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

9.1 Switchover

9.1.1 主库上操作

# 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 备库上操作

# 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 新备库(原主库)上的操作

# 切换后新的备库是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 验证

# 新主库切换归档，主备查询
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 备库上操作

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

9.2.2 停止实时同步

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 状态改为主库

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

# 开启数据库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上执行

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

9.3.2 在老主库上执行

# 要将之前出问题的老主库变成备库
# 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仍是主库

10. DG维护相关

10.1 DG开关机顺序

# 启动顺序：先启动备库，后启动主库
# 关闭顺序：先关闭主库，后关闭备库
---------------------------------------------------------------------------------
# 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

10.2 重建DG案例 ORA-19573\ORA-19573

Standby DG重建时遇到的小问题。

# restore database报错
ORA-19573 : cannot obtain exclusive enqueue for datafile 4
# 数据文件离线
SQL> alter database datafile 4 offline drop;
# 确保处于mount状态再执行restore database
RMAN> startup mount
# restore database报新的错误
ORA-19573: recovery or flashback in progress may need access to files
# 解决方法：
# 关闭备库日志应用进程
SQL> alter database recover managed standby database cancel;
RMAN> restore database

10.3 ORA-10456

ORA-10456:cannot open standby database; media recovery session may be in progress

SQL> select * from test;
select * from test;
                 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select name,open_mode from v$database;
NAME           OPEN_MODE
---------      --------------------
NOIDA          MOUNTED
-- 报错
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
-- solution
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect ; 
Database altered.