问题背景

数据库版本12.2.0.1，新搭建DG，源端3节点，备端2节点，备库只能接收到实例1的日志，实例2和3的日志接受不到，主库查看视图v$archive_dest_status的error字段为ORA-01033导致。

问题分析

问题只影响到实例2和实例3，对应redo是thread 2和thread 3，因此怀疑和thread有关。
查看当前thread，发现thread 2和thread 3的Enabled status为PRIVATE，而正常的RAC中，Enabled status都是PUBLIC。

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS	      ENABLED
---------- ------------------ ------------------------
	 1 OPEN 	      PUBLIC
	 2 OPEN 	      PRIVATE
	 3 OPEN 	      PRIVATE
复制

解决方案

调整thread 2和thread 3的Enabled status为PUBLIC。因为thread信息来自控制文件，因此主库调整完成后，备库需要重建控制文件进行同步。

场景模拟及解决步骤

19.12，两节点RAC模拟
需要先disable thread，disable则需要关闭实例

select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 OPEN   PUBLIC


SQL> alter database enable thread 2;
alter database enable thread 2
*
ERROR at line 1:
ORA-01612: instance TESTDB2 (thread 2) is already enabled


SQL> 

SQL> alter database disable thread 2;
alter database disable thread 2
*
ERROR at line 1:
ORA-01615: instance TESTDB2 (thread 2) is mounted - cannot disable


SQL> 
--关闭实例2
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

SQL>  select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL>  alter database disable thread 2;

Database altered.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED DISABLED

SQL> 
复制

模拟PRIVATE状态thread

SQL> alter database enable thread 2;

Database altered.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PRIVATE

SQL> 
--启动实例2
SQL> startup 
ORACLE instance started.

Total System Global Area 3221223184 bytes
Fixed Size                  9139984 bytes
Variable Size             704643072 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL> 


   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 OPEN   PRIVATE
复制

恢复PUBLIC状态thread

--关闭实例2
shutdown immediate

SQL>  select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PRIVATE

SQL> 
SQL> alter database disable thread 2;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> 
--启动实例2
startup

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 OPEN   PUBLIC
复制

订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198