My Oracle Support Banner

V$RECOVERY_FILE_DEST.NUMBER_OF_FILES Shows Incorrect Value after Restoring Controlfile At Standby. (Doc ID 2564464.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

V$RECOVERY_FILE_DEST.NUMBER_OF_FILES shows incorrect value after restoring controlfile at standby.

< Before restoring standby controlfile >

SQL> select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE                      PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE                                    0                         0               0          0
REDO LOG                                        0                         0               0          0
ARCHIVED LOG                                    0                         0               0          0
BACKUP PIECE                                    0                         0               0          0
IMAGE COPY                                      0                         0               0          0
FLASHBACK LOG                                  .1                       .05               2          0
FOREIGN ARCHIVED LOG                            0                         0               0          0
AUXILIARY DATAFILE COPY                         0                         0               0          0

 

SQL> select * from V$RECOVERY_FILE_DEST;

NAME                             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
-------------------------------- ----------- ---------- ----------------- --------------- ----------
/home/fast_recovery_area/         1.0737E+11  104857600          52428800               2          0

 

SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;

NAME                                                                          LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE             CON_ID
----------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------- ------------ ----------
/home/fast_recovery_area/<DB UNIQUE NAME>/flashback/o1_mf_g83my5vg_.flb          1          1          1   52428800             0                     NORMAL                0
/home/fast_recovery_area/<DB UNIQUE NAME>/flashback/o1_mf_g83my95g_.flb          2          1          1   52428800             0                     RESERVED              0


< After restoring standby controlfile >

SQL> select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE                      PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
------------------------------ ------------------ ------------------------- --------------- ----------
CONTROL FILE                                    0                         0               0          0
REDO LOG                                        0                         0               0          0
ARCHIVED LOG                                    0                         0               0          0
BACKUP PIECE                                    0                         0               0          0
IMAGE COPY                                      0                         0               0          0
FLASHBACK LOG                                  .1                         0               2          0
FOREIGN ARCHIVED LOG                            0                         0               0          0
AUXILIARY DATAFILE COPY                         0                         0               0          0

 

SQL> select * from V$RECOVERY_FILE_DEST;

NAME                             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
-------------------------------- ----------- ---------- ----------------- --------------- ----------
/home/fast_recovery_area/         1.0737E+11  209715200                 0               4          0



SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;

NAME                                                                          LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE             CON_ID
----------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ------------------- ------------ ----------
/home/fast_recovery_area/<DB UNIQUE NAME>/flashback/o1_mf_g83n2qrk_.flb          1          1          1   52428800             0                     NORMAL                0
/home/fast_recovery_area/<DB UNIQUE NAME>/flashback/o1_mf_g83n2thl_.flb          2          1          1   52428800             0                     RESERVED              0


Sum of V$RECOVERY_AREA_USAGE.NUMBER_OF_FILES is 2.
However, V$RECOVERY_FILE_DEST.NUMBER_OF_FILES is 4.

Changes

 Restore standby controlfile from a standby backup controlfile taken at standby database in state of flashback database enable.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.