Windows环境下部署DG教程
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 a r c h i v e d l o g ; 如 果 日 志 同 步 ， 停 止 s t a n d b y 数 据 库 ： s e l e c t p r o c e s s , s t a t u s f r o m v archived\_log; 如果日志同步，停止standby数据库： select process, status from v archivedlog;如果日志同步，停止standby数据库：selectprocess,statusfromvmanaged\_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 vKaTeX parse error: Expected 'EOF', got '#' at position 83: …st\_time, thread#̲, sequence#, na…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;