Data Guard switchover切换(Physical Standby Databases)
一、主库
--查看当前数据库状态
select database_role,switchover_status from v$database;
-switchover_status为'SESSIONS ACTIVE'执行;
alter database commit to switchover to physical standby with session shutdown;
-switchover_status为 'TO STANDBY' 执行;
alter database commit to switchover to physical standby;
-switchover_status为 'RESOLVABLE GAP' ;
- 说明standby和主库存在 日志gap, 比如 standby库的 归档路径空间不足, 监听关闭等问题.
处理好就自动变为 'TO STANDBY'.
- 主库执行完 switchover to physical standby后, 主库会被关闭.
-- 切换后重新open数据库
10g;
startup mount;
11g&12c;
startup; --数据库会以 read only 模式open;
--开启实时应用
- 实时应用恢复模式;
alter database recover managed standby database using current logfile disconnect from session;
- 非实时的,归档恢复模式
alter database recover managed standby database disconnect from session;
二、备库
查看当前数据库状态
select database_role,switchover_status from v$database;
-- 在主库切换为 standby后, 备库的 switchover_status 会从 'NOT ALLOWED' 变为 'TO PRIMARY'
switchover_status为 'TO PRIMARY' 切换数据库
alter database commit to switchover to primary with session shutdown;
-- switchover 后 数据库为 mount状态
如果是switchover_status为recovery needed或switchover latent 恢复数据库
alter database recover managed standby database disconnect from session;
开启数据库
shut immediate;
startup
三, 12C 重新打开 pdb
默认情况下, 12c的pdb在 经过 switchover 后是 mount状态.
-- 方法1,手工打开 pdb
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB MOUNTED
SQL>
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB MOUNTED
SQL>
-- 备库
SQL> alter pluggable database POC_PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ ONLY NO
-- 主库
SQL> alter pluggable database POC_PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ WRITE NO
SQL>
-- 方法2; 设置pdb随cdb启动而启动到open状态
通过在cdb中设置触发器实现;
使用SYS用户创建如下触发器即可:
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
把以上触发器,在DG的主的cbd的sys下都设置上. 备库会自动通过DG同步生存.
建立这个触发器后,switchover后,pdb自动的在主库以 read write打开, 备库以 read only打开.
一、主库
--查看当前数据库状态
select database_role,switchover_status from v$database;
-switchover_status为'SESSIONS ACTIVE'执行;
alter database commit to switchover to physical standby with session shutdown;
-switchover_status为 'TO STANDBY' 执行;
alter database commit to switchover to physical standby;
-switchover_status为 'RESOLVABLE GAP' ;
- 说明standby和主库存在 日志gap, 比如 standby库的 归档路径空间不足, 监听关闭等问题.
处理好就自动变为 'TO STANDBY'.
- 主库执行完 switchover to physical standby后, 主库会被关闭.
-- 切换后重新open数据库
10g;
startup mount;
11g&12c;
startup; --数据库会以 read only 模式open;
--开启实时应用
- 实时应用恢复模式;
alter database recover managed standby database using current logfile disconnect from session;
- 非实时的,归档恢复模式
alter database recover managed standby database disconnect from session;
二、备库
查看当前数据库状态
select database_role,switchover_status from v$database;
-- 在主库切换为 standby后, 备库的 switchover_status 会从 'NOT ALLOWED' 变为 'TO PRIMARY'
switchover_status为 'TO PRIMARY' 切换数据库
alter database commit to switchover to primary with session shutdown;
-- switchover 后 数据库为 mount状态
如果是switchover_status为recovery needed或switchover latent 恢复数据库
alter database recover managed standby database disconnect from session;
开启数据库
shut immediate;
startup
三, 12C 重新打开 pdb
默认情况下, 12c的pdb在 经过 switchover 后是 mount状态.
-- 方法1,手工打开 pdb
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB MOUNTED
SQL>
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB MOUNTED
SQL>
-- 备库
SQL> alter pluggable database POC_PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ ONLY NO
-- 主库
SQL> alter pluggable database POC_PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 POC_PDB READ WRITE NO
SQL>
-- 方法2; 设置pdb随cdb启动而启动到open状态
通过在cdb中设置触发器实现;
使用SYS用户创建如下触发器即可:
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
把以上触发器,在DG的主的cbd的sys下都设置上. 备库会自动通过DG同步生存.
建立这个触发器后,switchover后,pdb自动的在主库以 read write打开, 备库以 read only打开.