（基础知识）DataGuard相关指令整理

日常管理

备库备份前启动 CREATE SPFILE FROM PFILE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE;

在备库上,启动REDO APPLY，非实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

在备库上取消应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

以只读方式打开数据库 ALTER DATABASE OPEN READ ONLY;

在备库11G开启实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

查询是否实时应用 SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

查看当前角色 SELECT SWITCHOVER_STATUS,DATABASE_ROLE FROM V$DATABASE;

查看各个在用归档路径的状态 SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> ‘DEFERRED’ AND STATUS <> ‘INACTIVE’;

查看归档情况 SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在备库查询连接是否正常 SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;

主库查询备库是否存在GAP SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

switchover

查询是否可以切换到主库 SELECT SWITCHOVER_STATUS FROM V$DATABASE;

主库 SHUTDOWN ABORT; STARTUP MOUNT;

主库启动REDO APPLY，非实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

备库 SELECT SWITCHOVER_STATUS FROM V$DATABASE;

备库切换为主库 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; ALTER DATABASE OPEN;

主库启动REDO APPLY，非实时应用 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

备库开启实时查询+实时查询 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE OPEN; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

主备拆分

拆分DG（在备库上操作） ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE ACTIVATE STANDBY DATABASE; SHUTDOWN IMMEDIATE; STARTUP OPEN;

拆分时备库SCN SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; START REP1, ATCSN 1271196

实时查询

查看数据库模式（只读、可读可写、实时查询等模式） SELECT OPEN_MODE FROM V$DATABASE;

查看实时查询的时差 SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like ‘apply lag’;

查看实时查询的统计信息 SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = ‘apply lag’ AND COUNT > 0;