DG 启停步骤
一、停机
1、先关闭主库
--登录数据库
sqlplus / as sysdba
sql>shutdown immediate
2、再关闭备库
--登录数据库
sqlplus / as sysdba
sql>alter database recover managed standby database cancel; --停止同步
sql>shutdown immediate;
二、启动
1、启动主从监听,先启动从库,再启主库
lsnrctl start
2、启动主从数据库,先启备库,再启从库
--- 先启备库
sql>startup nomount
sql>alter database mount standby database;
sql>alter database open;
sql>alter database recover managed standby database using current logfile disconnect from session;
---再启主库
sql>startup
————————————————
版权声明:本文为CSDN博主「luck_man911」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/luoqinglong850102/article/details/106911134/
DataGuard常用命令及DG主备库开关顺序
常用命令
1.查询命令
-
select name,db_unique_name from v$database;
-
select max(sequence#) from v$archived_log;
查询保护模式
select protection_mode,database_role,open_mode from v$database;
查询最近5个应用的归档
select * from (select name,recid,sequence#,status,applied from v$archived_log order by recid desc) where rownum<5;
查询归档日志号及
select sequence#,status,thread#,block#,process,status from v$managed_standby;
2.日志应用
打开日志应用及取消日志应用语句:
-
alter database recover managed standby database using current logfile disconnect from session;
-
alter database recover managed standby database disconnect from session;
-
alter database recover managed standby database cancel;
3.停止Standby
-
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
-
alter database recover managed standby database cancel;
-
shutdown immediate;
4.切换到只读模式
-----由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;
5.切换回管理恢复模式
-
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;
6.主库和备库之间角色切换
6.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;
6.2 从库切换为主库
-
alter database commit to switchover to primary;
-
shutdown immediate;
-
startup
-
alter system switch logfile;
7.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;
8.更改保护模式
-
alter database set standby database to maximize protection;
-
alter database set standby database to maximize availability;
-
alter database set standby database to maximize performancen;
9.取消自动恢复模式
-
alter database recover managed standby database cancel;
-
alter database recover managed standby database finish;
-
alter database recover managed standby database finish force;
启动/关闭顺序
启动顺序
针对DataGuard采用不同的模式,主备库的启动顺序如下:
- max performance(最大性能):主库,备库的启动和关闭顺序没有先后
- max availability(最大可用): 要先启动备库,再启动主库,如果启动顺序相反,主库仍然能启动,但会在主库的alert.log文件中出现如下出错提示
- max protection(最大保护): 先启动备库,再启动主库,如果顺序相反,主库实例会自动中断,数据库无法启动,并会在alert.log文件中留下如下的信息
关闭顺序
- 关库时,先关主库,再关备库
DataGuard关启状态
启用备用数据库
SQL > STARTUP NOMOUNMT;
SQL >alter database mount standby database;
SQL >alter database recover managed standby database disconnect from session;
启用实时的日志应用 real time apply,开启日志的实时应用需要备库有备重做日志文件的存在。
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
停止日志应用服务
SQL> alter database recover managed standby database cancel;
Database altered.
判断当前备库是否启用了日志实时应用,使用如下语句:
SQL> select RECOVERY_MODE from v$archive_dest_status;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
1 rows selected.
如果是没有启用日志的实时应用,recovery_mode显示的是MANAGED
关闭备用数据库
SQL >alter database recover managed standby database cancel;
SQL >shutdown immediate;
从关闭状态打开
SQL >startup nomount;
SQL >alter database mount standby database;
SQL >alter database open read only;
从正在恢复状态只读打开
SQL >alter database recover managed standby database cancel;
SQL >alter database open read only;
切换回到恢复状态
SQL >alter database recover managed standby database disconnect from session;
-----日志强制归档
SQL> ALTER SYSTEM SWITCH LOGFILE; --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; --对数据库中的所有实例执行日志切换
-----查看当前保护模式
SQL > select name,db_unique_name,protection_mode from v$database;
-----检查备库归档文件是否连续
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
-----检查归档文件是否完整,主备执行,看最大序号是否相同,
select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
正常切换:
主服务器
----查看主库状态
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
----进行切换
alter database commit to switchover to physical standby;
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
备用服务器
----查看备库状态
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
----进行切换
alter database commit to switchover to primary;
shutdown immediate;
startup;
非正常切换:(即主服务器当机的情况)启动failover
备服务器
alter database recover managed standby database finish;
alter database commit to switchover to primary;
shutdown immediate;
startup;
-----------------------------------
©著作权归作者所有:来自51CTO博客作者小刘变老刘的原创作品,请联系作者获取转载授权,否则将追究法律责任
oracle DG 主备切换语句整理
https://blog.51cto.com/xiaoliutolaoliu/1345156
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 百万级群聊的设计实践
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 全网最简单!3分钟用满血DeepSeek R1开发一款AI智能客服,零代码轻松接入微信、公众号、小程
· .NET 10 首个预览版发布,跨平台开发与性能全面提升
· 《HelloGitHub》第 107 期