oracle18C (多租户模式) ADG部署流程
卢骏俊
系列文章目录
第一章 ADG基本知识
第二章 oracle18C ADG部署流程
第三章 oracle18c OGG级联模式（ADG模式 级联 Downstream库+OGG18）
前言
oracle18c ADG
提示：以下是本篇文章正文内容，下面案例可供参考
一、环境说明
oracle版本：Version 18.3.0.0.0
| 角色 | IP | 主机名 | sid | global_names | service_names | db_unique_name |
|---|---|---|---|---|---|---|
| 主库 | 192.168.103.31 | pri | orcl | pri | pri | pri |
| 备库 | 192.168.103.32 | adg | orcl | adg | adg | pri |
二、使用步骤
1.检查数据库各个参数
要保证每个实例的db_unique_name和global_name不同
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string pri
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
pri
2.配置网络，添加tnsnames字符串
cd $ORACLE_HOME/network/admin
vim tnsnames.ora
##添加以下内容
PRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)
ADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
)
)
检查是否可达
[oracle@adg admin]$ tnsping pri
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 06-APR-2022 22:15:55
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.31)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri)))
OK (10 msec)
[oracle@adg admin]$ tnsping adg
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 06-APR-2022 22:15:57
Copyright (c) 1997, 2018, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = adg)))
OK (10 msec)
3.修改主库强制归档以及开启归档模式
开启归档模式：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1577058176 bytes
Fixed Size 8896384 bytes
Variable Size 503316480 bytes
Database Buffers 1056964608 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
开启强制归档
FORCE LOGGING模式可防止以非记录方式执行任何加载操作。这会减慢加载过程，因为加载的数据必须复制到重做日志中。
SQL> alter database force logging;
Database altered.
验证结果：
SQL> select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG YES
此外，oracle18C添加的新的data gurd特性,它们可以执行非日志记录操作，同时可以使Active Data Guard备用数据库接收到所有数据，从而防止FORCE 方式生成大量重做日志导致性能下降。
STANDBY NOLOGGING FOR DATA AVAILABILITY 即 loading 操作的 commit 会被 delay，直到所有的 standby 都 apply data 为止。模式使加载操作通过自己与备用的连接将加载的数据发送到每个备用。提交会延迟，直到所有Active Data Guard备用数据库通过 recover 方式将数据应用完成。使用以下命令启用它：
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
STANDBY NOLOGGING FOR LOAD PERFORMANCE模式与先前的模式类似，不同之处在于，如果网络无法跟上数据加载到主数据库的速度，则加载过程可以停止将数据发送到备用数据库。在此模式下，备用数据库可能缺少数据，但每个Active Data Guard备用数据库都会在recover过程中自动从主数据库中提取数据。（在 load 数据的时候，遇到网络瓶颈时，先不发送数据，这就保证了 loading 性能，但是丢失了数据，但是丢失的数据，会从 primary 中再次获取。）使用以下命令启用它：
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
此处选择第一种方式，忽略性能损失，保证数据的实时性。
SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
Database altered.
4.配置重做传输认证
①、配置主库接收重做数据
两个原则：
每个备用重做日志文件必须至少与重做源数据库的重做日志中最大的重做日志文件一样大。为便于管理，Oracle 建议重做源数据库的重做日志和重做传输目的地的备用重做日志中的所有重做日志文件大小相同。
对于重做源数据库上的每个重做线程，备用重做日志必须至少比重做源数据库上的重做日志多一个重做日志组。
确定日志组和大小：
SQL> SELECT GROUP#, BYTES FROM V$LOG;
GROUP# BYTES
---------- ----------
1 209715200
2 209715200
3 209715200
日志组数量为3，大小为200M，则备用redo日志应该至少为4个200M的日志文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
4 209715200
5 209715200
6 209715200
7 209715200
②、设置主库初始化参数
使用文章开头说明的参数
| 角色 | IP | 主机名 | sid | global_names | service_names | db_unique_name |
|---|---|---|---|---|---|---|
| 主库 | 192.168.103.31 | pri | orcl | pri | pri | pri |
| 备库 | 192.168.103.32 | adg | orcl | adg | adg | pri |
查看数据库数据文件位置
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_system_k3kfyoff_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_sysaux_k3kfzrkx_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_undotbs1_k3kg0kor_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_system_k3kg5tw5_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_sysaux_k3kg5tw4_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_users_k3kg0lro_.dbf
/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_undotbs1_k3kg5tw7_.dbf
配置主库参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,adg)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=ds ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ds' scope=both sid='*';
alter system set FAL_CLIENT='pri' scope=both sid='*';
alter system set FAL_SERVER='ds' scope=both sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ADG/onlinelog','/u01/app/oracle/oradata/orcl/ORCL/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;
--备注：
--log_archive_dest_1这里写主库arch路径
--DB_FILE_NAME_CONVERT，LOG_FILE_NAME_CONVERT参数需要已'备库路径','主库路径'一组为准则
参数解析：
LOG_ARCHIVE_CONFIG：列出主备库上的DB_UNIQUE_NAME 参数。默认情况下，定义该参数能确保主备库数据库能够互相识别对方
LOG_ARCHIVE_DEST_1：本地归档路径。Primary与Standby需要定义各自的归档路径
LOG_ARCHIVE_DEST_2：设置redo log的传输方式(sync or async)以及传输目标(即standby apply node)，当前节点设置的均为另一端数据库的db_unique_name，并且其中的service的值需要与tnsnames.ora别名相同。
FAL_CLIENT：（Fetch Archive Log）用来解决归档裂缝，定义的客户端（获取日志的客户端），参数取自Oracle Net Service Name
FAL_SERVER：当主库转为备库的时候此参数会生效。通过网络向FAL_CLIENT发送缺失的日志，参数取自Oracle Net Service Name上述例子当rac转为备库时，会向std获取redo或者归档应用。
STANDBY_FILE_MANAGEMENT：当主库转为备库的时候此参数会生效。用来控制是否自动将Primary数据库增加表空间或数据文件的改动，传播到物理Standby数据库。AUTO：如果该参数值设置为AUTO，则Primary数据库执行的表空间创建操作也会被传播到物理Standby数据库上执行。
MANUAL：如果设置为MANUAL或未设置任何值（默认值是MANUAL），需要手工复制新创建的数据文件到物理Standby服务器。
DB_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一指明对映关系。注意: primary上的该参数仅在主备switch over后生效,格式应保持一致
LOG_FILE_NAME_CONVERT：当主库转为备库的时候此参数会生效。定义主备log文件的存放路径转换
在主库上生成用于备库的参数文件
SQL> create pfile='/tmp/1.ora' from spfile;
File created.
将主库的参数文件，密码文件拷贝到备库
[oracle@pri dbs]$ cd $ORACLE_HOME/dbs/
[oracle@pri dbs]$ ls
hc_orcl.dat init.ora lkORCL lkPRI orapworcl spfileorcl.ora
[oracle@pri dbs]$ scp orapworcl 192.168.103.32:/u01/app/oracle/product/18.3.0/db_1/dbs
oracle@192.168.103.32's password:
orapworcl 100% 3584 2.2MB/s 00:00
在备库上更改参数文件，修改参数以及创建目录结构
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,adg)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=both sid='*';
alter system set FAL_CLIENT='adg' scope=both sid='*';
alter system set FAL_SERVER='pri' scope=both sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile','/u01/app/oracle/oradata/orcl/ORCL/datafile' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/ORCL/onlinelog','/u01/app/oracle/oradata/orcl/ADG/onlinelog' scope=spfile sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=8192 scope=spfile;
关闭DS，启动到nomount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1577058176 bytes
Fixed Size 8896384 bytes
Variable Size 503316480 bytes
Database Buffers 1056964608 bytes
Redo Buffers 7880704 bytes
使用rman传输备份文件（仅限小库）
[oracle@pri ~]$ rman target sys/oracle@pri auxiliary sys/oracle@adg
Recovery Manager: Release 18.0.0.0.0 - Production on Tue Apr 12 23:13:41 2022
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1628320479)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
这里需要设置备库的tnsname参数，修改备库的参数
ADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.103.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
(UR = A)
)
)
这里添加了 (UR = A)
再次尝试：
[oracle@pri admin]$ rman target sys/oracle@pri auxiliary sys/oracle@adg
Recovery Manager: Release 18.0.0.0.0 - Production on Tue Apr 12 23:16:45 2022
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1628320479)
connected to auxiliary database: ORCL (not mounted)
RMAN>
执行脚本
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
allocate AUXILIARY channel c8 type disk;
allocate AUXILIARY channel c9 type disk;
allocate AUXILIARY channel c10 type disk;
allocate AUXILIARY channel c11 type disk;
allocate AUXILIARY channel c12 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}
duplicate之后数据库处于mount状态
启动备库
SQL> alter database open;
Database altered.
DataGuard部署完毕之后，查询备库的状态
SQL> select SWITCHOVER_STATUS,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM v$database;
SWITCHOVER_STATUS DATABASE_ROLE PROTECTION_MODE OPEN_MODE
-------------------- ---------------- -------------------- --------------------
NOT ALLOWED PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY
二、DataGuard 数据库应用日志模式
Active DataGuard模式
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> SELECT NAME,DATABASE_ROLE,PROTECTION_MODE,OPEN_MODE FROM V$DATABASE;
NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE
--------- ---------------- -------------------- --------------------
ORCL PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> select status from v$instance;
STATUS ------------------------------------ MOUNTED
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
总结
类似资料：