Oracle database DataGuard 主备切换

开启备库恢复：

数据库启动时，先启动备库，在启动主库
数据库关闭时，先关闭主库，在关闭备库
SYS@ prodstd>recover managed standbydatabase disconnect from session; --只要启动备库就使能recover功能
SYS@ prod>select max(sequence#) from v$archived_log;复制代码

服务器、数据库环境：

主库服务器hostname：prod1        备库服务器hostname：prod2
主库数据库SID：prod              备库数据库SID：prodstd复制代码

切换备库前准备，查看主库备库状态：

SYS@ prod>select tablespace_name,file_name from dba_data_files;
SYS@ prod>select max(sequence#) from v$archived_log; --对比主备库日志，同步才能做下一步
SYS@ prod>select username,sid from v$session where username is not null;  --切换时要停库，最好没有其他session链接
SYS@ prod>select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME     OPEN_MODE      DATABASE_ROLE    PROTECTION_MODE        SWITCHOVER_STATUS
--------- -------------------- ------------------------------------ --------------------
PROD      READ WRITE     PRIMARY            MAXIMUM PERFORMANCE  TO STANDBY
SYS@ prodstd>select name,database_role,protection_mode,switchover_status from v$database;
NAME      DATABASE_ROLE      PROTECTION_MODE          SWITCHOVER_STATUS
--------- ---------------- ----------------------------------------
PROD      PHYSICAL STANDBY    MAXIMUM PERFORMANCE   NOT ALLOWED复制代码

主备切换步骤：

主库操作：
SYS@ prod>alter database commit to switchover to standby;   没有其他用户登录
或
SYS@ prod>alter database commit to switchover to standby with session shutdown;   有用户登录，强制切换

SYS@ prod>shutdown --彻底关闭数据库SYS@ prod>startup mount --先启动到mount下
SYS@ prod>select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ------------------------------------ --------------------
PROD      MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE  TO PRIMARY
备库操作：SYS@ prodstd>select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
PROD      READ ONLY            PHYSICAL STANDBY MAXIMUM PERFORMANCE  TO PRIMARY
SYS@ prodstd>alter database commit to switchover to primary; --备库为主库
SYS@ prodstd>select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
PROD      MOUNTED              PRIMARY          MAXIMUM PERFORMANCE  NOT ALLOWED
SYS@ prodstd>alter database open;  --开启备库，此时备库已经成为主库复制代码

主库操作：
SYS@ prod>alter database open;SYS@ prod>select name,database_role,protection_mode,switchover_status from v$database;
NAME      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
PROD      PHYSICAL STANDBY MAXIMUM PERFORMANCE  RECOVERY NEEDED
SYS@ prod>recover managed standby database disconnect from session;  --原主库（当前备库）开启恢复。
SYS@ prod>select name,database_role,protection_mode,switchover_status from v$database;
NAME      DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
PROD      PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED复制代码

主备切换成功