Physical Standby Database Is Out Of Sync due to undo tablespace is offline at...

刘泽宇

目标：
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the
Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual
environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Tablespace Names: UNDOTBS1,UNDOTBS2,UNDOTBS3,UNDOTBS4
ASM Disk Group Name: +DATAC1

**********

Problem Description
---------------------------------------------------
After rebuilding the standby database using active cloning MRP is waiting for gap that is looking for old log sequence.

Primary:-

INSTANCE_NAME HOST_NAME DATABASE_STATUS DATABASE_ROLE DB_UNIQUE_NAME STATUS
--------------- --------------- --------------- ---------------- --------------- ------
<instance_name>  <hostname> ACTIVE PRIMARY <db_unique_name> OPEN


  THREAD# MAX(SEQUENCE#)
---------- --------------
  4 76095
  3 73570

Standby:-

INSTANCE_NAME HOST_NAME DATABASE_STATUS DATABASE_ROLE DB_UNIQUE_NAME STATUS LOGINS
--------------- --------------- --------------- ---------------- --------------- --------------- ----------
<instance_name> <hostname> ACTIVE PHYSICAL STANDBY <db_unique_name> MOUNTED ALLOWED


  THREAD# MAX(SEQUENCE#)
---------- --------------
  3 73533
  4 76056

Alert Log error:-

Wed Jan 23 13:25:32 2019
FAL[client]: Failed to request gap sequence
GAP - thread 3 sequence 66212-66311
DBID 1319279401 branch 935632637
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
----------------------------------------------------------


处理方法：
SQL> select Inst_id, hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

INST_ID FILENUMBER STATUS SCN SEQUENCE
---------------- ---------------- ---------------- ---------------- ----------------
3 1 8196 112631324213 73711
3 3 4 112631324213 73711
3 4 4 112631324213 73711
3 5 0 106825873083 68717
3 6 4 112631324213 73711
3 7 0 106825873083 68717
3 8 4 112631324213 73711
3 9 4 112631324213 73711
3 10 4 112631324213 73711
3 11 8196 112631324213 73711
3 12 4 112631324213 73711
3 13 4 112631324213 73711
3 14 4 112631324213 73711
3 15 4 112631324213 73711
3 16 4 112631324213 73711
3 17 4 112631324213 73711
3 18 4 112631324213 73711
3 19 4 112631324213 73711
3 20 4 112631324213 73711
3 21 4 112631324213 73711
3 22 4 112631324213 73711
3 23 4 112631324213 73711
3 24 0 106825663532 66212 <=================

23 rows selected.

Please find the below status of tablespace in both container and pluggable database.
Datafile 24 is present in pluggable database under tablesspace UNDOTBS4 which shows OFFLINE.

Container:-

SQL> select TABLESPACE_NAME,STATUS,BIGFILE from dba_tablespaces where TABLESPACE_NAME like 'UNDO%';

TABLESPACE_NAME STATUS BIG
------------------------------ --------- ---
UNDOTBS1 ONLINE NO
UNDOTBS2 ONLINE NO
UNDOTBS3 ONLINE NO
UNDOTBS4 ONLINE NO

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS from dba_data_files where TABLESPACE_NAME in ('UNDOTBS1','UNDOTBS2','UNDOTBS3','UNDOTBS4','UNDOTBS5');

FILE_NAME FILE_ID TABLESPACE_NAME STATUS
---------------------------------------------------------------------- ---------- ------------------------------ ---------
+DATAC1/<db_unique_name>/DATAFILE/undotbs1.2294.935632571 4 UNDOTBS1 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs2.1911.935632931 8 UNDOTBS2 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs3.2310.935632931 9 UNDOTBS3 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs4.2296.935632933 10 UNDOTBS4 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs1.3477.939915365 20 UNDOTBS1 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs4.3476.939915373 21 UNDOTBS4 AVAILABLE
+DATAC1/<db_unique_name>/DATAFILE/undotbs1.3529.939926785 22 UNDOTBS1 AVAILABLE

7 rows selected.

Pluggable:-

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PREMPP_PDB READ WRITE NO
SQL> alter session set container=<cdb name>;

Session altered.

SQL> select TABLESPACE_NAME,STATUS,BIGFILE from dba_tablespaces where TABLESPACE_NAME like 'UNDO%';

TABLESPACE_NAME STATUS BIG
------------------------------ --------- ---
UNDOTBS1 OFFLINE NO
UNDOTBS2 OFFLINE NO
UNDOTBS3 OFFLINE NO
UNDOTBS4 OFFLINE NO                   <==============
UNDOTBS5 OFFLINE NO

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS from dba_data_files where TABLESPACE_NAME in ('UNDOTBS1','UNDOTBS2','UNDOTBS3','UNDOTBS4','UNDOTBS5');

FILE_NAME FILE_ID TABLESPACE_NAME STATUS
---------------------------------------------------------------------- ---------- ------------------------------ ---------
+DATAC1/<db_unique_name>/484166D7A14C2F3EE053BD81520A9498/DATAFILE/undotbs 24 UNDOTBS4 AVAILABLE       <=============
4.4188.973451623



处理方法：

Please download and apply the fix of bug 21652138. After that, you should not see the undo tablespace at pdb level.