详解Windows环境下部署DG

weixin_40913898

已于 2022-05-18 17:12:18 修改

阅读量684

点赞数

分类专栏： Oracle 数据库文章标签： oracle 数据库 windows

于 2021-07-28 17:20:12 首次发布

本文链接：https://blog.csdn.net/weixin_40913898/article/details/119185292

版权

Oracle 同时被 2 个专栏收录

35 篇文章 2 订阅

订阅专栏

数据库

22 篇文章 1 订阅

订阅专栏

详解Windows下DG部署
一、环境
Host IP db_unique_name instance_name service_name tnsname
172.12.7.221 primary orcl prod prod
172.12.7.235 standby orcl prodstd prodstd
二、主库配置
1、开启归档日志、闪回日志、强制记录日志
C:\Users\Administrator>sqlplus / as sysdba
关闭数据库：
SQL> shutdown immediate
启动实例到mount状态：
SQL> startup mount
启用归档模式：
SQL> alter database archivelog;
启用强制记录日志模式：
SQL> alter database force logging;
开启闪回数据库功能：
SQL> alter database flashback on;
开启闪回数据库功能需要调整以下参数：
修改db_recovery_file_dest、db_recovery_file_dest_size及db_flashback_retention_target三个参数内容
1）db_recovery_file_dest、db_recovery_file_dest_size两个参数用于指定闪回日志存放位置及最大大小。可以根据具体环境做相应调整。
SYS@ora11g> show parameter recovery_file

NAME TYPE VALUE

db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 3852M

2）确认db_flashback_retention_target参数设置的内容。
db_flashback_retention_target参数限定了闪回可恢复的时间范围，默认是1440分钟，一天的时间。
SYS@ora11g> show parameter db_flashback_retention_target

NAME TYPE VALUE

db_flashback_retention_target integer 1440
查看是否开启闪回数据库功能：
SQL> select flashback_on from v$database;
SQL> alter database open;

三、调整参数
*.db_unique_name=‘prod’
*.fal_client=‘prod’
*.fal_server=‘prodstd’
*.log_archive_config=‘DG_CONFIG=(prod,prodstd)’
*.log_archive_dest_1=‘location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=prod’
*.log_archive_dest_2=‘service=prodstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prodstd’
*.standby_file_management=‘AUTO’
四、创建standby日志文件
ALTER DATABASE ADD STANDBY LOGFILE (‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\standby01.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\standby02.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\standby03.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\standby04.rdo’) SIZE 50M;
五、配置监听（主库）
D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
配置tnsnames.ora
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.12.7.221)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

prodstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.12.7.235)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodstd)
)
)
重新加载监听
六、配置监听（备库）
D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
配置tnsnames.ora
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.12.7.221)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)

prodstd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.12.7.235)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodstd)
)
)
重新加载监听

七、创建pfile文件、control文件和备份数据库
创建pfile文件：
SQL> create pfile=‘d:\pfile.bak’ from spfile;
文件已创建。

备份控制文件：
SQL> alter database create standby controlfile as ‘d:\standbycontrol.ctl’;

备份数据库：
rman target /

backup database format ‘d:\FULL_%U.bak’ ;

八、将pfile、密码文件、控制文件传到备库的D盘目录下
密码文件、spifle、pfile文件，放在%ORACLE_HOME%\database
注意：%ORACLE_HOME%\database完整路径为：
D:\app\Administrator\product\11.2.0\dbhome_1\database
九、修改备库的pfile文件
*.db_unique_name=‘prodstd’
*.fal_client=‘prodstd’
*.fal_server=‘prod’
*.log_archive_config=‘DG_CONFIG=(prodstd,prod)’
*.log_archive_dest_1=‘location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=prodstd’
*.log_archive_dest_2=‘service=prod lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prod’
*.standby_file_management=‘AUTO’
十、配置备库实例服务
oradim.exe -new -sid orcl -startmode m
oradim.exe -edit -sid orcl -startmode a
十一、恢复数据库（rman技术）
1、首先通过pfile文件启动实例到nomount：
startup pfile=‘d:\pfile.bak’ nomount
可能有些目录不存在，如果报错，创建对应的目录即可。
创建spfile文件：
create spfile=‘D:\app\Administrator\product\11.2.0\dbhome_1\database\spfileorcl.ora’ from pfile=‘d:\pfile.bak’;

2、恢复standby控制文件
rman target /
restore controlfile from ‘d:\STANDBYCONTROL.CTL’;

3、修改备库到mount状态
alter database mount;
注：只有在mount状态了，才可以利用rman进行数据库的恢复，来达到初始化的目的

4、通过rman恢复备库
rman target /
catalog start with ‘/u01/rmanbackup/’;
restore database ;

十二、启用日志应用：
alter database recover managed standby database disconnect from session;
启动日志应用后，数据库在mount状态；

如果要启用只读模式下日志实施应用，请按以下步骤操作：
先确认源端和目标端的日志是否同步：

select max(sequence#) from v$archived_log;

如果日志同步，停止standby数据库：

select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;

重启启动数据库到只读模式：
startup nomount;
alter database mount standby database;
alter database open read only;
然后切回到日志应用模式：
alter database recover managed standby database using current logfile disconnect from session;

十三、检查日志应用情况
主库：
SET LINESIZE 141
SET PAGESIZE 9999
SET VERIFY OFF
col member for a60

set pagesize 1000
SET LINESIZE 95
select a.first_time, a.thread#, a.sequence#,a.name,nvl2(b.name,‘OK’,‘NO’) dgstatus
from (select first_time, thread#, sequence#, name
from v $KaTeX parse error: Expected 'EOF', got '#' at position 83: \dotsst_time, thread#̲, sequence#, na\dots$ archived_log
where dest_id = 2) b
where a.thread# = b.thread#(+)
and a.sequence# = b.sequence#(+) and trunc(a.first_time)=trunc(sysdate) order by first_time;
查询当天
and trunc(a.first_time)=trunc(sysdate)

FIRST_TIME THREAD# SEQUENCE# NAME DGSTATUS
2020-09-28 14:00:37 1 3705 /u01/app/oracle/fast_recovery_area/OADB/archivelog/2020_09_28/o1_mf_1_3705_hq30rpq6_.arc 已发送

备库

SET LINESIZE 100
set pagesize 100
col PROCESS for a8
col CLIENT_PROCESS for a15
col STATUS for a15
select process,client_process,sequence#,status from v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS
ARCH ARCH 3711 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 3709 CLOSING
ARCH ARCH 0 CONNECTED
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 3712 IDLE
MRP0 N/A 3712 APPLYING_LOG

已选择8行。

备库存档裂缝
select * from v$archive_gap;

未选定行

01、查看库的角色
SQL> select database_role from v$database;

DATABASE_ROLE
PRIMARY
02、查看可以切换的的角色

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
TO STANDBY

03、查看切换后的状态
SQL> select open_mode,database_role,switchover_status from v$database;

十四、管理DG
1.停止Standby
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;
2.切换到只读模式
-----由shutdown模式切换到只读模式-------
startup nomount;
alter database mount standby database;
alter database open read only;
-----由应用日志模式切换到只读模式-------
alter database recover managed standby database cancel; – 取消日志应用
alter database open read only;
3.切换回管理恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; – 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;
4.主库和备库之间角色切换
4.1 主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
4.2 从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup
alter system switch logfile;
5.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;
6.更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
7.取消自动恢复模式
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;