oracle adg切换
1 主库转备库
注意：原则上至多只能有一个主库，但是备库可以多个，所以需要先进行主库转备库操作。
1.1 查看主库角色切换状态
SYS@proe>select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS ------------------------- ---------------- -------------------- PROE PRIMARY TO STANDBY
1.2 正常主库切换备库命令
SYS@proe>alter database commit to switchover to physical standby; Database altered.
1.2 执行切换后的操作
SYS@proe>shutdown abort ORACLE instance shut down. SYS@proe>startup mount; ORACLE instance started. # 查看此时角色切换状态 SYS@proe>select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS ------------------------- ---------------- -------------------- PROE PHYSICAL STANDBY RECOVERY NEEDED # 查看角色保护模式和级别 SYS@proe> select name,database_role,protection_mode,protection_level from v$database; NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ------------------------- ---------------- -------------------- -------------------- PROE PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
1.3 异常切换命令
"TO STANDBY"意味着主库此时可以切换为备库，如果状态为SESSION ACTIVE可以使用以下命令切换。然后执行上述切换后的操作。
SYS@proe>alter database commit to switchover to physical standby with session shutdown;
2 备库转主库
2.1 查看备库角色切换状态
SYS@stddb>select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- PROE PHYSICAL STANDBY TO PRIMARY
2.2 备库切换主库命令
SYS@stddb>alter database commit to switchover to primary; Database altered.
2.2 执行切换后的操作
# 查看此时角色切换状态 SYS@stddb>select name,database_role,switchover_status from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- PROE PRIMARY NOT ALLOWED # 查看角色保护模式和级别 SYS@stddb>select name,database_role,protection_mode,protection_level from v$database; NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL --------- ---------------- -------------------- -------------------- PROE PRIMARY MAXIMUM PERFORMANCE UNPROTECTED # 备库切换为主库后实例状态 SYS@stddb>select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ stddb MOUNTED # 将数据库启动到open SYS@stddb>alter database open; Database altered. # 再次查看转换后的角色保护模式及级别，可以看到和原来的主库一致。 SYS@stddb>select database_role,protection_mode,protection_level from v$database; DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
3 启动现在备库日志应用服务
原来的主库已经切换为备库，查看此时数据库打开模式
SYS@proe>select name,open_mode from v$database; NAME OPEN_MODE ------------------------- -------------------- PROE MOUNTED
数据库启动到open
SYS@proe>alter database open; Database altered.
启动数据库的日志应用服务
SYS@proe>alter database recover managed standby database using current logfile disconnect; Database altered. # 查看数据库打开模式 SYS@proe>select name,open_mode from v$database; NAME OPEN_MODE ------------------------- -------------------- PROE READ ONLY WITH APPLY
一次完整的主备切换完成
