当前位置:操作系统 > Unix/Linux >>

SWITCHOVER主库出现LOG SWITCH GAP和RESOLVABLE GAP解决一例

SWITCHOVER主库出现LOG SWITCH GAP和RESOLVABLE GAP解决一例
 
今天做switchover,环境是11.2.0.3+OEL5.7,开始时主备库状态都是正常的,符合直接切换条件:
主库:
SQL> select open_mode,database_role,switchover_status from v$database;
 
 
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY
 
 
备库:
SQL> select open_mode,database_role,switchover_status from v$database;
 
 
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
 
 
主库直接进行swichover:
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
 
 
 
 
SQL> select open_mode,database_role,switchover_status from v$database;
 
 
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          LOG SWITCH GAP
 
 
提示有日志切换GAP,于是直接重启主库:
SQL> startup force
ORACLE instance started.
 
 
Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             322964824 bytes
Database Buffers           83886080 bytes
Redo Buffers                4292608 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role,switchover_status from v$database;
 
 
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          RESOLVABLE GAP
 
 
状态由LOG SWITCH GAP变成了RESOLVABLE GAP,从字面理解是主备库之间存在GAP,于是执行:
 
 
SQL> ALTER SYSTEM FLUSH REDO TO ora11dg2;
 
 
SQL> select open_mode,database_role,switchover_status from v$database;
 
 
OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           PRIMARY          TO STANDBY
 
 
把主库REDO FLUSH到备库以后,以上状态消失,又重新回到之前的TO STANDBY状态了,可以重新进行SWITCHOVER了。
如果主库状态不是TO STANDBY,而是SESSION ACTIVE,就要加上WITH SESSION SHUTDOWN,否则不能切换成功,除此以外的其他状态,是不能直接进行转换的。备库通常的状态是NOT ALLOWED,当主库做了切换以后,会变成TO PRIMARY,所以通常是现在主库做ROLE TRANSITION,然后再在备库做。
 
 
以下是一些消除主备库之间GAP的命令和说明:
 
 
--主库,将所有未传送的redo传送给从库,target_db_name使用DB_UNIQUE_NAME 。
ALTER SYSTEM FLUSH REDO TO target_db_name;
 
 
--验证备库
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
 
 
--如果必要,拷贝归档日志到从库,并进行注册
 ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
 
 
--重复上一步,知道确认所有归档完毕。
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
 
 
--查看目标日志传输路径状态和GAP状态
SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
 
 
--在目标备库上,停止日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
 
--在目标备库上
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
 
 
--如果日志确定丢失,可以采用激活方式,但这样会有数据丢失。
--ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
 
 
--验证目标备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
 
--开始切换,如果状态为“TO PRIMARY”,则WITH SESSION SHUTDOWN从句可以去掉。
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
 
 
--打开新主库
ALTER DATABASE OPEN;
 
 
附一份DG主备库切换状态说明:
 
 
v$database
NOT ALLOWED - On a primary database, this status indicates that there are no valid and enabled standby databases. On a standby database, this status indicates that a switchover request has not been received from the primary database. 
 
SESSIONS ACTIVE - The database has active sessions. On a physical standby database, the WITH SESSION SHUTDOWN SQL clause must be specified to perform a role transition while in this state. On a logical standby database, a role transition can be performed while in this state, but the role transition will not complete until all current transactions have committed. 
 
SWITCHOVER PENDING - On a physical standby database, this status indicates that a switchover request has been received from the primary database and is being processed. A physical standby database cannot switch to the primary role while in this transient state. 
 
SWITCHOVER LATENT - On a physical standby database, this status indicates that a switchover request was pending, but the original primary database has been switched back to the primary role. 
 
TO PRIMARY - The database is ready to switch to the primary role. 
 
TO STANDBY - The database is ready to switch to either the physical or logical standby role. 
 
TO LOGICAL STANDBY - The database has received a data dictionary from a logical standby database and is ready to switch to the logical standby role. 
 
RECOVERY NEEDED - On a physical standby database, this status indicates that additional redo must be applied before the database can switch to the primary role. 
 
PREPARING SWITCHOVER - On a primary database, this status indicates that a data dictionary is being received from a logical standby database in preparation for switching to the logical standby role. On a logical standby database, this status indicates tha
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,