oracle DG 主备切换语句整理

今日花了一下午时间进行了Oracle DataGuard的切换练习,参考了网上好多文章,最后将一些语句进行摘录,以备以后查询使用。之后有时间会带来Oracle DG的搭建和切换全过程文章。

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;

上一篇:使用Oracle 11g新特性 Active Database Duplication 搭建Dataguard环境


下一篇:CefSharp初识--把网页移到桌面