配置主库
开启主库归档模式
启动数据库到Mount模式
SYS@orcl>startup mount;
设置本地归档日志的存放路径
SYS@orcl>alter system set log_archive_dest_1='LOCATION=/oraarch/orcl';
修改数据库为归档模式
SYS@orcl>alter database archivelog;
打开强制日志模式
SYS@orcl>alter database force logging;
打开数据库
SYS@orcl>alter database open;
确认归档模式
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oraarch/orcl
Oldest online log sequence 2
Next log sequence to archive 6
Current log sequence 6
确认强制日志模式
SYS@orcl>select log_mode,force_logging from v$database;
LOG_MODE FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG YES
避坑提醒:如果DBCA建库时选择了打开归档模式并且开启了快速恢复区,请调大快速恢复区的容量限制,否则会发生磁盘没写满却无法归档日志的情况!
查询现有redo日志的数量
SYS@orcl>select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- ------------------------------
1 ONLINE /oradata/ORCL/redo01.log
2 ONLINE /oradata/ORCL/redo02.log
3 ONLINE /oradata/ORCL/redo03.log
4 ONLINE /oradata/ORCL/redo04.log
5 ONLINE /oradata/ORCL/redo05.log
查询现有redo日志的容量
SYS@orcl>select group# ,bytes/1024/1024 as MB,status from v$log;
GROUP# MB STATUS
---------- ---------- ----------------
1 256 CURRENT
2 256 INACTIVE
3 256 INACTIVE
4 256 INACTIVE
5 256 INACTIVE
给主库创建standby的redo日志组,方便角色切换为备库时使用,容量要和redo相同,数量是redo当前数量+1
alter database add standby logfile group 6 ('/oradata/ORCL/standbyredo06.log') size 256m;
alter database add standby logfile group 7 ('/oradata/ORCL/standbyredo07.log') size 256m;
alter database add standby logfile group 8 ('/oradata/ORCL/standbyredo08.log') size 256m;
alter database add standby logfile group 9 ('/oradata/ORCL/standbyredo09.log') size 256m;
alter database add standby logfile group 10 ('/oradata/ORCL/standbyredo10.log') size 256m;
alter database add standby logfile group 11 ('/oradata/ORCL/standbyredo11.log') size 256m;
确认standby redo logfile创建成功
SYS@orcl>select group#,status,used from v$standby_log;
GROUP# STATUS USED
---------- ---------- ----------
6 UNASSIGNED 0
7 UNASSIGNED 0
8 UNASSIGNED 0
9 UNASSIGNED 0
10 UNASSIGNED 0
11 UNASSIGNED 0
准备密码文件
[oracle@primary ~]$ cd $ORACLE_HOME/dbs/
[oracle@primary /oracle/app/product/19c/db_1/dbs]$ cp orapworcl /tmp/orapworclstd
设置主库的参数文件
SYS@orcl>create pfile from spfile;
[oracle@primary /oracle/app/product/19c/db_1/dbs]$ vim initorcl.ora
注意:备库内容最好在末行添加,最好不要使用alter system set来设置,后期不容易维护参数文件
#数据库唯一名称
*.db_unique_name='orcl'
*.log_archive_config='dg_config=(orcl,orclstd)'
#fal_client填写本机数据库唯一名称
*.fal_client='orcl'
#fal_server填写对端的数据库唯一名称,多个备库时此处可以填写多个用逗号间隔
*.fal_server='orclstd'
#本地归档文件路径
*.log_archive_dest_1='LOCATION=/oraarch/orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
#远程归档文件传输给的服务名和数据库唯一名称
*.log_archive_dest_2='service=orclstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orclstd'
#建议归档日志改为arc结尾,避免被误删
*.log_archive_format='%t_%s_%r.arc'
#路径转换原则,本机的路径在后面,远程的路径放前面;路径映射应该成对出现,一个路径不能对应多个,要逐一进行映射;数据文件分散在多个路径时用逗号间隔;格式要严格一致,要么都加'/',要么都不加
*.db_file_name_convert='/oradata/ORCLSTD/','/oradata/ORCL/','/oratemp/ORCLSTD/','/oratemp/ORCL/','/oraundo/ORCLSTD/','/oraundo/ORCL/'
#redo日志多路复写时记得增加映射路径
*.log_file_name_convert='/oradata/ORCLSTD/','/oradata/ORCL/'
STANDBY_FILE_MANAGEMENT=AUTO
启用新的参数文件
首先备份之前的spfile
SYS@orcl>create pfile='/home/oracle/spfile1028.bak' from spfile;
关闭实例
SYS@orcl>shutdown immediate
使用设置后的pfile替换当前spfile
SYS@orcl>create spfile from pfile;
启动实例
SYS@orcl>startup
传输参数文件和密码文件到备库
[oracle@primary /orabackup]$ cd /tmp/
[oracle@primary /tmp]$ scp orapworclstd oracle@standby:/tmp
[oracle@primary /tmp]$ cd $ORACLE_HOME/dbs
[oracle@primary /oracle/app/product/19c/db_1/dbs]$ scp initorcl.ora oracle@standby:/tmp/initorclstd.ora
设置备库
设置备库的SID
[oracle@standby ~]$ export ORACLE_SID=orclstd
将密码文件和参数文件拷贝拷贝到ORACLE的家目录
[oracle@standby ~]$ cd /tmp/
[oracle@standby /tmp]$ cp orapworclstd initorclstd.ora $ORACLE_HOME/dbs/
设置备库的参数文件
[oracle@standby /tmp]$ cd $ORACLE_HOME/dbs
[oracle@standby /oracle/app/product/19c/db_1/dbs]$ vim initorclstd.ora
#需要修改的部分
*.audit_file_dest='/oracle/app/admin/orclstd/adump'
*.control_files='/oradata/ORCLSTD/control01.ctl','/oradata/ORCLSTD/control02.ctl'
*.db_unique_name='orclstd'
*.log_archive_config='dg_config=(orcl,orclstd)'
*.fal_client='orclstd'
*.fal_server='orcl'
*.log_archive_dest_1='LOCATION=/oraarch/orclstd valid_for=(all_logfiles,all_roles) db_unique_name=orclstd'
*.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_format='%t_%s_%r.arc'
*.db_file_name_convert='/oradata/ORCL/','/oradata/ORCLSTD/','/oratemp/ORCL/','/oratemp/ORCLSTD/','/oraundo/ORCL/','/oraundo/ORCLSTD/'
*.log_file_name_convert='/oradata/ORCL/','/oradata/ORCLSTD/'
创建备库相应的文件夹
mkdir -pv /oracle/app/admin/orclstd/adump
mkdir -pv /oradata/ORCLSTD
mkdir -pv /oraarch/orclstd
mkdir -pv /oratemp/ORCLSTD
mkdir -pv /oraundo/ORCLSTD
通过编辑的pfile启动备库的伪实例到nomount模式
[oracle@standby /]$ export ORACLE_SID=orclstd
[oracle@standby /]$ sqlplus / as sysdba
SYS@orclstd>create spfile from pfile;
SYS@orclstd>startup nomount;
配置网络
主库和备库做静态注册
配置主库的listener.ora文件
[oracle@primary ~]$ cd $ORACLE_HOME/network/admin/
[oracle@primary /oracle/app/product/19c/db_1/network/admin]$ vim listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 2521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /oracle/app/product/19c/db_1)
(SID_NAME = orcl)
)
)
配置主库的tnsnames.ora
[oracle@primary /oracle/app/product/19c/db_1/network/admin]$ vim tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 2521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 2521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 2521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstd)
)
)
配置备库的listener.ora文件
[oracle@standby ~]$ cd $ORACLE_HOME/network/admin
[oracle@standby /oracle/app/product/19c/db_1/network/admin]$ vim listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 2521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2521))
)
)
配置备库的tnsnames.ora
[oracle@standby /oracle/app/product/19c/db_1/network/admin]$ vim listener.ora
LISTENER_ORCLSTD =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 2521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 2521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 2521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstd)
)
)
重载主库和备库的监听器
[oracle@primary ~]$ lsnrctl reload
[oracle@standby ~]$ lsnrctl reload
静态监听注册成功之后使用lsnrctl stat查看监听器状态会显示如下内容
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orclstd", status UNKNOWN, has 1 handler(s) for this service...
网络连接测试
在主库和备库分别执行以下指令
tnsping orcl
tnsping orclstd
[oracle@primary ~]$ sqlplus sys/oracle@orclstd as sysdba
[oracle@standby ~]$ sqlplus sys/oracle@orcl as sysdba
使用Dupicate方式构建备库
[oracle@primary ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@orclstd
注意以下的connected状态,主库显示DBID,备库为not mounted状态是正确的
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Oct 29 10:02:47 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1614658538)
connected to auxiliary database: ORCL (not mounted)
在RMAN中执行以下run块内容
run {
allocate channel ch001 type disk;
allocate channel ch002 type disk;
allocate channel ch003 type disk;
allocate channel ch004 type disk;
allocate auxiliary channel ch005 type disk;
allocate auxiliary channel ch006 type disk;
duplicate target database for standby from active database nofilenamecheck using compressed backupset;
release channel ch001;
release channel ch002;
release channel ch003;
release channel ch004;
release channel ch005;
release channel ch006;
}
备库初始化过程结束之后为MOUNT模式
SYS@orclstd>select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
启用备库ADG模式
打开备库
SYS@orclstd>alter database open;
备库检查数据文件、redo log、standby redo log均生成:
set lines 222 pages 222
col name for a66
col member for a55
SYS@orclstd>select name from v$datafile;
NAME
------------------------------------------------------------------
/oradata/ORCLSTD/system01.dbf
/oradata/ORCLSTD/sysaux01.dbf
/oraundo/ORCLSTD/undotbs01.dbf
/oradata/ORCLSTD/appindex1.dbf
/oradata/ORCLSTD/appdata1.dbf
/oradata/ORCLSTD/appdata2.dbf
/oradata/ORCLSTD/appdata3.dbf
SYS@orclstd>select name from v$tempfile;
NAME
------------------------------------------------------------------
/oratemp/ORCLSTD/temp01.dbf
/oratemp/ORCLSTD/apptemp.dbf
SYS@orclstd>select GROUP#,TYPE,MEMBER from v$logfile order by 2,1;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------
1 ONLINE /oradata/ORCLSTD/redo01.log
2 ONLINE /oradata/ORCLSTD/redo02.log
3 ONLINE /oradata/ORCLSTD/redo03.log
4 ONLINE /oradata/ORCLSTD/redo04.log
5 ONLINE /oradata/ORCLSTD/redo05.log
6 STANDBY /oradata/ORCLSTD/standbyredo06.log
7 STANDBY /oradata/ORCLSTD/standbyredo07.log
8 STANDBY /oradata/ORCLSTD/standbyredo08.log
9 STANDBY /oradata/ORCLSTD/standbyredo09.log
10 STANDBY /oradata/ORCLSTD/standbyredo10.log
11 STANDBY /oradata/ORCLSTD/standbyredo11.log
开启日志应用
SYS@orclstd> alter database recover managed standby database using current logfile disconnect;
备库检查数据同步状态
col NAME for a23
col VALUE for a13
col TIME_COMPUTED for a20
col DATUM_TIME for a20
SYS@orclstd> select name,value,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;
该视图通过判断transport lag和apply lag两个参数的DATUM_TIME值来判断备库是否在应用主库的日志文件,如果多次查询DATUM_TIME值保持不变,则说明备注已经停止从主库接收数据了
NAME VALUE TIME_COMPUTED DATUM_TIME
----------------------- ------------- -------------------- --------------------
transport lag +00 00:00:00 10/29/2021 10:59:44 10/29/2021 10:59:43
apply lag +00 00:00:00 10/29/2021 10:59:44 10/29/2021 10:59:43
apply finish time (null) 10/29/2021 10:59:44 (null)
estimated startup time 8 10/29/2021 10:59:44 (null)
检查MRP进程状态为APPLYING_LOG为正常同步状态
SYS@orclstd>select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
----------- --------------- ---------
RFS IDLE 0
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 9
RFS IDLE 0
MRP0 APPLYING_LOG 9
查看数据库当前打开状态为READ ONLY WITH APPLY则代表ADG启动成功
SYS@orclstd>select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
一步一步超详细教你搭建Oracle19c ADG物理备库(Dupicate方式)
最新推荐文章于 2024-07-12 10:54:04 发布
本文详细介绍了如何配置Oracle数据库的主库和备库,包括开启主库归档模式、设置归档路径、修改数据库为归档模式、强制日志模式、创建standby redo日志组、传输参数文件和密码文件、设置备库参数、建立数据库复制以及检查数据同步状态等步骤,确保主备库间的灾难恢复和数据保护。
摘要由CSDN通过智能技术生成