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.查询命令

  1.  
    select name,db_unique_name from v$database;
  2.  
    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.日志应用

打开日志应用及取消日志应用语句：

  1.  
    alter database recover managed standby database using current logfile disconnect from session;
  2.  
    alter database recover managed standby database disconnect from session;
  3.  
    alter database recover managed standby database cancel;

 

3.停止Standby

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

 

4.切换到只读模式

-----由shutdown模式切换到只读模式-------

  1.  
    startup nomount;
  2.  
    alter database mount standby database;
  3.  
    alter database open read only;

-----由应用日志模式切换到只读模式-------

  1.  
    alter database recover managed standby database cancel; -- 取消日志应用
  2.  
    alter database open read only;

 

5.切换回管理恢复模式

  1.  
    startup nomount;
  2.  
    alter database mount standby database;
  3.  
    alter database recover managed standby database disconnect from session; -- 启动日志应用
  4.  
    alter database recover managed standby database using current logfile disconnect from session;

 

6.主库和备库之间角色切换

6.1 主库切换为备库

  1.  
    alter database commit to switchover to physical standby;
  2.  
    alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
  3.  
    shutdown immediate
  4.  
    startup nomount;
  5.  
    alter database mount standby database;
  6.  
    alter database recover managed standby database disconnect from session;

 

6.2 从库切换为主库

  1.  
    alter database commit to switchover to primary;
  2.  
    shutdown immediate;
  3.  
    startup
  4.  
    alter system switch logfile;

 

7.备库自动使用主库传过来的日志进行恢复

alter database recover automatic standby database;

 

8.更改保护模式

  1.  
    alter database set standby database to maximize protection;
  2.  
    alter database set standby database to maximize availability;
  3.  
    alter database set standby database to maximize performancen;

 

9.取消自动恢复模式

  1.  
    alter database recover managed standby database cancel;
  2.  
    alter database recover managed standby database finish;
  3.  
    alter database recover managed standby database finish force;

 

 

启动/关闭顺序

启动顺序

针对DataGuard采用不同的模式,主备库的启动顺序如下:

  1. max performance(最大性能):主库,备库的启动和关闭顺序没有先后
  2. max availability(最大可用): 要先启动备库,再启动主库,如果启动顺序相反,主库仍然能启动,但会在主库的alert.log文件中出现如下出错提示
  3. 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

posted @ 2023-05-24 16:18  RedArmy  阅读(102)  评论(0)  编辑  收藏  举报