12c Dataguard的switch over

12c开始，相对于11g的dataguard的swtichover ，没有太多的繁琐的步骤，仅仅需要一条命令即可（实际上是两条命令，一条确认是否可以switchover,一条进行switchover）

RDBMS： 12.2.0.1

参考文档：

参考文档：
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sbydb/managing-oracle-data-guard-role-transitions.html#GUID-AAD70601-D248-4309-B8DD-F461EE31A5FF
12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)
In Data Guard Broker Configuraion, SWITCHOVER VERIFY Command via SQL*Plus Returns ORA-16475 (Doc ID 2679209.1)

-- switchover 

1 确认目标备库是否准备好了switchover   (主库 boston,备库 chicago，测试环境中，主库是test，备库是test_stdby)

alter database switchover to chicago verify ;    -- 在主库上执行 ，最佳的结果是database altered. 如果出现其他ora-或者告警，则要排查原因。

SYS@test>alter database switchover to test_stdby verify;
alter database switchover to test_stdby verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details


SYS@test>

-- alert 中的log ，主库上 

2021-04-29T10:13:11.885684+08:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target TEST_STDBY
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: alter database switchover to test_stdby verify..

-- alert 中的log， 备库上 

2021-04-29T10:13:12.083628+08:00
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE

-- 查看mos In Data Guard Broker Configuraion, SWITCHOVER VERIFY Command via SQL*Plus Returns ORA-16475 (Doc ID 2679209.1)  
这个报错，是一个预期的行为，主要是因为启用了broker 
但是在另一个mos上，说是继续设置log_archive_dest_n (实际上这里已经设置了) 
12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)
 
-- 再次切换的时候，主备库在verify的时候没有问题了 。原因原来的备库上 log_archive_dest_state_2 设置了deffer 。

2021-04-29T10:56:14.742544+08:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target TEST
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
Completed: alter database switchover to test verify

Media Recovery Waiting for thread 1 sequence 14 (in transit)
2021-04-29T10:54:58.293536+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 14 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/test/stdby_redo01.dbf
2021-04-29T10:56:14.593698+08:00
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE

2 在主库上执行切换 

alter database switchover to chicago;   -- 注意观察执行结果，适当的时候，可以进行alter database switchover to xxx force ;

SYS@test>alter database switchover to test_stdby;

Database altered.

SYS@test>

3 在新的主库上打开数据库 ，这个时候，发现新的主库，是mount状态，只需要open就可以了 

alter database open;

SYS@test_stdby>select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 7879
Session ID: 237 Serial number: 25471


SYS@test_stdby>conn / as sysdba
Connected.
SYS@test_stdby>startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@test_stdby>alter database open;

Database altered.

SYS@test_stdby>

SYS@test_stdby>select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SYS@test_stdby>

4 在新的物理备库上，mount数据库 ，或者startup  。这个时候，新的物理备库，是关闭的。需要打开。 
startup mount   -- 针对非adg
startup    -- 针对adg 

SYS@test>alter database switchover to test_stdby;

Database altered.

SYS@test>conn / as sysdba
Connected to an idle instance.
SYS@test>startup
ORACLE instance started.

Total System Global Area 1493172224 bytes
Fixed Size                  8620896 bytes
Variable Size             973079712 bytes
Database Buffers          503316480 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SYS@test>

5 在新的备库上执行redo apply 

alter database recover managed standby database disconnect from session;

 

-- failover （略，具体参考下官方文档）
大概步骤
1 将主库上没有传送到备库的redo，刷新到备库 （不希望丢失数据的话，）

alter system flush redo to target_db_name;

2 查看备库上有无gap，有的话，把redo传送到备库 

3 在备库上注册传送过来的redo 

alter database register physical logfile 'filespec1';

4 确认gap问题已经解决

5 在备库上进行failover  

alter database failover to target_db_name;
alter database failover to chicago;

6 如果步骤5没有成功，则执行以下 

alter database failvover to chicago force; 

如果步骤5 成功，直接打开新的主库就行了

alter database open ;

7 如果failover force失败了。 则需要执行一个data loss failover 。执行完毕后，步骤5中打开新的主库  。

alter database activate physical standby database;   --在原来的主库上执行 

 

END