oracle 11g physical standby switchover
简介
SWITCHOVERS主要是在计划停机维护时用来降低DOWNTIME，如硬件维护、操作系统升级或是
数据库rolling upgrade,
也可用来进行特殊情况下的数据库迁移。
SWITCHOVERS主要分为两个阶段，阶段1：主库转换为备库角色；阶段2：备库转换为主库角色
环境：
switchover 前 db_unique_name : ogg: primary db_unique_name : tgg: physical standby
前提条件检查
1. standby log /redo log 在主备库都存在 select * from V$STANDBY_LOG; select * from v$log 2. 确认主备处于归档模式 archive log list 3. 确认redo传输无错误、无GAP SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; STATUS GAP_STATUS --------- ------------------------ VALID NO GAP 4.确认主备库temprory file一样 SQL> select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------- /u01/oradata/ogg/temp01.dbf 5.确认log_archive_dest_n参数设置正确 show parameter log_archive_dest_2 主库 SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=tgg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tgg 备库 SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service=ogg ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg 开始SWITCHOVER 将主库ogg switchover to 备库 检查主库是否准备好进行角色转换 SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- SESSIONS ACTIVE SWITCHOVER_STATUS为SESSIONS ACTIVE，是因为还有有SESSION 连接到数据库，断开连接SESSION在查询如下 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY 状态在SESSIONS ACTIVE 或者 TO STANDBY 都可以进行switchover操作 进行switchover SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered. 将数据库重启到mount状态 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. SQL> alter database open; Database altered. SQL> select database_role,db_unique_name,open_mode from v$database; DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE ---------------- ------------------------------ -------------------- PRIMARY tgg READ WRITE SQL> alter database open; Database altered. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select database_role,db_unique_name,open_mode from v$database; DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE ---------------- ------------------------------ -------------------- PHYSICAL STANDBY ogg READ ONLY WITH APPLY 将备库tgg switchover to 主库 检查主库是否准备好进行角色转换 SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY 进行switchover 操作 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered. 开启新主库 SQL> alter database open; Database altered. 检查角色转换结果 SQL> select database_role,db_unique_name,open_mode from v$database; DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE ---------