【工作笔记】Oracle19c Non-CDB ADG搭建

1. 环境描述

2. 主库配置

2.1 开启强制日志

alter database force logging;
复制

2.2 开启归档日志

alter system set db_recovery_file_dest_size=80g scope=both;
alter system set db_recovery_file_dest='/data/fast_recovery_area';

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
show parameter db_recovery
复制

2.3 主库创建standby日志组

alter system set standby_file_management=manual;
alter database add standby logfile group 7 '/data/oradata/FADB/standby_redo07.log' size 500M ;
alter database add standby logfile group 8 '/data/oradata/FADB/standby_redo08.log' size 500M ;
alter database add standby logfile group 9 '/data/oradata/FADB/standby_redo09.log' size 500M ;
alter database add standby logfile group 10 '/data/oradata/FADB/standby_redo10.log' size 500M ;
alter database add standby logfile group 11 '/data/oradata/FADB/standby_redo11.log' size 500M ;
alter database add standby logfile group 12 '/data/oradata/FADB/standby_redo12.log' size 500M ;
alter database add standby logfile group 13 '/data/oradata/FADB/standby_redo13.log' size 500M ;
alter system set standby_file_management=auto;

col member for a60
set linesize 200
select group#,type,member from v$logfile order by group#;
复制

2.4 参数配置

fadb.__data_transfer_cache_size=0
fadb.__db_cache_size=17448304640
fadb.__inmemory_ext_roarea=0
fadb.__inmemory_ext_rwarea=0
fadb.__java_pool_size=268435456
fadb.__large_pool_size=134217728
fadb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
fadb.__pga_aggregate_target=4294967296
fadb.__sga_target=21474836480
fadb.__shared_io_pool_size=134217728
fadb.__shared_pool_size=3019898880
fadb.__streams_pool_size=134217728
fadb.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/fadb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/data/oradata/FADB/control01.ctl','/data/fast_recovery_area/FADB/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/data/oradata/FADB_STB','/data/oradata/FADB' # 新增，备库在前
*.db_name='fadb'
*.db_unique_name='fadb' # 新增，主库、备库必须不一样
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=85899345920
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fadbXDB)'
*.fal_server='fadb_stb' # 新增
*.log_archive_config='DG_CONFIG=(fadb,fadb_stb)' # 新增
*.log_archive_dest_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fadb' # 新增
*.log_archive_dest_2='service=fadb_stb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=fadb_stb' # 新增
*.log_archive_dest_state_1='ENABLE' # 新增
*.log_archive_dest_state_2='ENABLE' # 新增
*.log_file_name_convert='/data/oradata/FADB_STB','/data/oradata/FADB' # 新增
*.local_listener='LISTENER_FADB'
*.log_buffer=268435456
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=4294967296
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=21474836480
*.sga_target=21474836480
*.undo_tablespace='UNDOTBS1'
复制

主库通过静态参数文件pfile，重新生成二进制参数文件

shutdown immediate;
create spfile from pfile='/home/oracle/init.ora';
startup
复制

2.5 配置TNS

[oracle@fadb01 admin]$ cat tnsnames.ora
FADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fadb)
)
)

FADB_STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fadb_stb)
)
)

LISTENER_FADB =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.134)(PORT = 1521))
复制

2.6 主库配置监听（静态）

[oracle@fadb01 admin]$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.134)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = fadb)
(ORACLE_HOME=/u01/app/oracle/product/19c/db_1)
(SID_NAME = fadb)
)
)
复制

重启监听

lsnrctl stop
lsnrctl start
sqlplus sys/Oracle_123@fadb as sysdba
复制

3. 备库配置

3.1 备库参数配置(把主库的pfile文件拷贝到备库，直接修改)

fadb.__data_transfer_cache_size=0
fadb.__db_cache_size=17448304640
fadb.__inmemory_ext_roarea=0
fadb.__inmemory_ext_rwarea=0
fadb.__java_pool_size=268435456
fadb.__large_pool_size=134217728
fadb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
fadb.__pga_aggregate_target=4294967296
fadb.__sga_target=21474836480
fadb.__shared_io_pool_size=134217728
fadb.__shared_pool_size=3019898880
fadb.__streams_pool_size=134217728
fadb.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/fadb_stb/adump' # 修改fadb为fadb_stb，创建adump目录
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/data/oradata/FADB_STB/control01.ctl','/data/fast_recovery_area/FADB_STB/control02.ctl' # 修改FADB为FADB_STB
*.db_block_size=8192
*.db_file_name_convert='/data/oradata/FADB','/data/oradata/FADB_STB' # 新增，主库在前
*.db_name='fadb'
*.db_unique_name='fadb_stb' # 新增，主库、备库必须不一样
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=85899345920
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fadb_stbXDB)'
*.fal_server='fadb' # 新增
*.log_archive_config='DG_CONFIG=(fadb_stb,fadb)' # 新增
*.log_archive_dest_1='location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fadb_stb' # 新增
*.log_archive_dest_2='service=fadb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=fadb' # 新增
*.log_archive_dest_state_1='ENABLE' # 新增
*.log_archive_dest_state_2='ENABLE' # 新增
*.log_file_name_convert='/data/oradata/FADB','/data/oradata/FADB_STB' # 新增
*.local_listener='LISTENER_FADB'
*.log_buffer=268435456
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=4294967296
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=21474836480
*.sga_target=21474836480
*.undo_tablespace='UNDOTBS1'
复制

3.2 参数说明

VALID_FOR参数说明
VALID_FOR属性由2部分组成:archive_source(online_logfile,standby_logfile,all_logfiles)和database_role(primary_role,standby_role,all_role).
online_logfile: 表示归档联机重做日志
standby_logfile:表示归档备用数据库的重做日志/接受来自主库的重做日志
all_logfiles: online_logfile && standby_logfile

primary_role: 仅当数据库角色为主库时候生效
standby_role: 仅当数据库角色为备库时候生效
all_role: 任意角色均生效
复制

3.3 备库创建对应目录

备库创建对应目录
mkdir -p /u01/app/oracle/admin/fadb_stb/adump
mkdir -p /data/oradata/FADB_STB
复制

3.4 配置TNS

FADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fadb)
)
)

FADB_STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fadb_stb)
)
)

LISTENER_FADB =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.64.134)(PORT = 1521))

尝试在备库连接字符串登录主库
sqlplus sys/Oracle_123@fadb as sysdba
复制

3.5 备库监听配置

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.66.151)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = fadb_stb)
(ORACLE_HOME=/u01/app/oracle/product/19c/db_1)
(SID_NAME = fadb)
)
)

启动监听
lsnrctl start
复制

3.6 拷贝主库的密码文件到备库

3.7 备库创建spfile

create spfile from pfile='$ORACLE_HOME/dbs/init.ora';
startup nomount
复制

4. 通过duplicate命令复制

在主库或备库中执行都可以，开始复制，复制时需要注意是否使用nofilenamecheck参数，如果两个实例相关数据目录结构完全相同，则需要指定，否则报错。

RMAN> rman target sys/Oracle_123@fadb auxiliary sys/Oracle_123@fadb_stb
RMAN> duplicate target database for standby from active database;
（duplicate target database for standby from active database dorecover nofilenamecheck)
或者
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
复制

5. 主库归档删除策略

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored
RMAN-08591: warning: invalid archived log deletion policy #告警


CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; #
如果启用该策略且配置为APPLIED ON STANDBY子句，则所有强制standby位置被apply后，这些归档日志会被RMAN删除
SHIPPED to ALL standby; #
如果启用该策略且配置为SHIPPED ON STANDBY子句，则所有强制standby位置被成功传送后，这些归档日志会被RMAN删除
复制

6. 同步说明

备库建好后，默认状态是mount

select database_role,open_mode,protection_mode from v$database;
复制

备库开启同步

alter database recover managed standby database disconnect from session;
复制

查看主备库当前最新归档

select unique thread#,max(sequence#) over(partition by thread#) last from v$archived_log;
复制

备库取消同步

alter database recover managed standby database cancel;
复制

备库open

alter database open; #read only模式
复制

备库开启同步

alter database recover managed standby database disconnect from session; #启动mrp进程
复制

在12c之前版本中，MRP的默认配置是对备用数据库上的归档日志文件进行应用重做，从12c开始，默认配置是使用实时应用，直接从备用重做日志文件应用重做，当前版本为19c，备库开启同步后，默认是实时应用。

alter database recover managed standby database using archived logfile disconnect; #(应用归档日志，非实时同步)
复制

查询是否为实时应用

select dest_name,status,recovery_mode from v$archive_dest_status;
select dest_name,status,type,database_mode,protection_mode,destination,applied_seq#,db_unique_name from v$archive_dest_status;
复制

在12c之前，例如11g，开启实时应用，需要如下命令

alter database recover managed standby database using current logfile disconnect from session;
复制

7. 检查

检查主库
select DEST_NAME,STATUS,ERROR from v$archive_dest where DEST_ID=2;
select process,status,sequence# from v$managed_standby（v$dataguard_process ）;
检查备库
select process,status,sequence# from v$managed_standby（v$dataguard_process ）;
验证主、备库传送路径是否正常
set linesize 200 pagesize 1000
set long 99999
col dest_name for a50
col destination for a50
select dest_name,status,database_mode,destination
from v$archive_dest_status where dest_id<3;
主库端检测是否存在日志缺陷：
select status,gap_status from v$archive_dest_status where dest_id=2;

备库检查
验证接受日志以及日志应用进程是否正确
select client_process,process,thread#,sequence#,status
from v$managed_standby
where client_process='LGWR' or process='MRP0'; # 如果是waiting_log_gap，则是存在未应用的日志

备库日志接收日志以及应用日志检查
select thread#,sequence#,'Last Applied:' logs,
to_char(next_time,'DD-MON-YYYY:HH24:MI:SS') Time
from v$archived_log
where sequence#=(select max(sequence#) from v$archived_log where applied='YES')
union
select thread#,sequence#,'Last Received:' Log,
to_char(next_time,'DD-MON-YYYY:HH24:MI:SS') Time
from v$archived_log
where sequence#=(select max(sequence#) from v$archived_log);

备库端检测应用成功的最大日志序列
select max(sequence#) from v$archived_log where applied='YES' and thread#=2;

备库端删除已经应用的日志
delete archivelog sequence xxx thread 1;
复制

8. ADG切换验证

8.1 查看adg状态

select * from v$dataguard_stats;
复制

8.2 主库查看

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FORCE_LOGGING DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --------------------------------------- -------- -------
READ WRITE PRIMARY TO STANDBY YES DISABLED NONE

SQL> alter database switchover to devops verify; #要求备库必须处于日志应用状态
alter database switchover to devops verify
*
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target
复制

8.3 备库

SQL> alter database recover managed standby database disconnect from session;

Database altered.
复制

8.4 主库

SQL> alter database switchover to devopsd verify;

Database altered.

SQL> alter database switchover to devopsd;

Database altered.

SQL> select database_role,open_mode,protection_mode from v$database; #执行完上一步后，数据库就挂了，必须重启
select database_role,open_mode,protection_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 25001
Session ID: 2 Serial number: 58596

SQL> conn / as sysdba
Connected to an idle instance. #这里说明数据库实例挂了
SQL> startup
ORACLE instance started.

Total System Global Area 3607099528 bytes
Fixed Size 8902792 bytes
Variable Size 721420288 bytes
Database Buffers 2868903936 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> select database_role,open_mode,protection_mode from v$database;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE

新备库启动并开启MRP
SQL> alter database recover managed standby database disconnect from session;

Database altered.
复制

8.5 源从库

SQL> select database_role,open_mode,protection_mode from v$database; #在主库执行 switchover后，从库挂掉
select database_role,open_mode,protection_mode from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 30809
Session ID: 497 Serial number: 58174

SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED --- 实例由open only转为mounted

SQL> alter database open;

Database altered.

select * from v$archive_gap;


SQL> select database_role,open_mode,protection_mode from v$database;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- -------------------- --------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE
可以看到，源从库切换为了主库
复制

9. 备库归档删除任务

mkdir /home/oracle/scripts
cd /home/oracle/scripts
vi del_arch.sh

rman target / <<EOF >> /home/oracle/scripts/del_arch.log
delete noprompt archivelog all completed before 'sysdate - 1/24';
EOF

crontab -e
0 * * * * /bin/sh /home/oracle/scripts/del_arch.sh
复制