SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter.

When performing a switchover verificaton from your primary database, in your Data Guard setup, you may see the following:

sqlplus / as sysdba

SQL>  ALTER DATABASE SWITCHOVER TO stb verify;
 ALTER DATABASE SWITCHOVER TO stb verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

Alert log reports:
2022-12-06T09:56:34.020025+01:00
ALTER DATABASE SWITCHOVER TO stb verify
2022-12-06T09:56:34.192599+01:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target STB
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 stb verify...

Solution:
Update the standby database log_archive_dest_n parameter, to prepare it for a future primary role.

In the standby database, update one of the log_archive_dest_n parameters, I picked the next available from the list, log_archive_dest_2:

alter system set log_archive_dest_2='service=primary.oric.no LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=primary';

Run the verification again:

SQL>  ALTER DATABASE SWITCHOVER TO stb verify;

Database altered.

Check the alert log and it will confirm that the database stb can now be turned into a primary database:

2022-12-06T10:03:34.605309+01:00
ALTER DATABASE SWITCHOVER TO stb verify
2022-12-06T10:03:34.773710+01:00
SWITCHOVER VERIFY: Send VERIFY request to switchover target STB
SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER
Completed: ALTER DATABASE SWITCHOVER TO stb verify

What is an Oracle snapshot standby database?

A snapshot standby database is a fully updatable standby database.

Redo data is still received from the primary database, but it is not applied immediately. Before the redo database is applied, the database will need to be converted back into a physical standby database.

Any local updates that the snapshot standby database may have received, will be deleted when the snapshot database is converted into a physical standby database and resynchronized with the primary database.

Oracle 12.2 Data Guard Concepts and administration documentation

Oracle support Using Snapshot Standby Database. (Doc ID 443720.1)

How to avoid redo logs and standby redo logs being created as OMF files during a DUPLICATE ... FOR STANDBY operation

Recently I had to create a Data Guard physical standby database, and I used RMAN active database duplication ( "duplicate target database for standby from active database" ) to create the database on the remote server.

As the remote server had the same file system layout as the primary, I used the directive

NOFILENAMECHECK

in the duplicate command.

Consequently, I expected all datafiles, including the redo and standby redo log files, to be created in exactly the same location as the target.

For some reason this did not happen; in fact, they were named with OMF file names, and placed in the db_recovery_file_dest which point to the path /recovery_data, while on the primary, the same files reside in /u02/oradata

I found the solution based on a post called "JoeLi's TechLife" and added my own experience to find a workaround.

The author points out

after the duplication, all other files seem to be fine regarding their names and locations except for redo log files and standby redo log files. They are created under the fast recovery area with OMF file names!

How come? Well here is the reason — duplicate will always re-create redo log files and standby redo log files. And because DB_RECOVERY_FILE_DEST is defined on the Primary server, redo and standby redo log files are created as OMF despite NOFILENAMECHECK is used. And they are not multiplexed as you have on the Primary database!

I was not aware of this.

The author continues to point out that he hasn't found a solution, yet.

The proposed solution according to Joe is

... to recreate redo log and standby redo log files on the newly created standby server — basically dropping the OMF ones and creating them under the correct locations with correct file names.

I did however, find a solution, which worked for me and was really quite simple, although not very intuitive: simply set the directive log_file_name_convert in your clone script, like examplified below:

connect target sys/password@primary
connect auxiliary target sys/password@stb
run{
        allocate channel c1 type disk;
        allocate channel c2 type disk;
        allocate channel c3 type disk;
        allocate channel c4 type disk;
        allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
SET DB_UNIQUE_NAME='stb'
SET FAL_CLIENT='stb'
SET FAL_SERVER='primary'
SET LOG_ARCHIVE_DEST_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stb'
SET LOG_ARCHIVE_DEST_2='' comment 'Must be set to empty string during duplication'
SET STANDBY_FILE_MANAGEMENT='AUTO'
SET DG_BROKER_CONFIG_FILE1='/u01/oracle/product/12c/dbs/dr1stb.dat'
SET DG_BROKER_CONFIG_FILE2='/u01/oracle/product/12c/dbs/dr2stb.dat'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(stb,primary)'
SET DG_BROKER_START='TRUE'
SET LOG_ARCHIVE_TRACE='0' comment 'Must be set to same value as primary during duplicaton'
SET dispatchers='(PROTOCOL=TCP) (SERVICE=stbXDB)' comment 'Must be set to unique name of stb db'
SET LOCAL_LISTENER='stb.skead.no' comment 'Must be set to unique name of stb db'
SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' comment 'Must be set to same value as primary during duplicaton'
SET LOG_FILE_NAME_CONVERT='/redodata','/redodata' comment 'Must be set during cloning to avoid OMF naming of redo log files'
NOFILENAMECHECK
USING COMPRESSED BACKUPSET;
}

Aftwards, the query

COL MEMBER FORMAT A50
SET LINES 200
SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#
FROM V$LOGFILE A INNER JOIN  V$LOG B ON A.GROUP# = B.GROUP#
ORDER BY GROUP# ASC;

set lines 200
col member format a50
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#;

confirms that the standby database was created with non-OMF datafiles:

SQL> @chk_redo.sql

    GROUP# MEMBER                                                     MB ARC STATUS            SEQUENCE#
---------- -------------------------------------------------- ---------- --- ---------------- ----------
         1 /redodata/redo01.log                            2048 NO  CURRENT                 156
         2 /redodata/redo02.log                            2048 YES UNUSED                    0
         3 /redodata/redo03.log                            2048 YES UNUSED                    0


    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1          0 YES UNASSIGNED /redodata/stb_redo01.log                  STANDBY
         5          1          0 YES UNASSIGNED /redodata/stb_redo02.log                  STANDBY
         6          1          0 YES UNASSIGNED /redodata/stb_redo03.log                  STANDBY
         7          1          0 YES UNASSIGNED /redodata/stb_redo04.log                  STANDBY

How to take a standby database out of a data guard configuration and convert it to a standalone read/write database using the Data Guard Broker

Deactive the Data Guard Broker configuration:

dgmgrl / 
show configuration
# disable log shipping
edit database 'stdb' SET STATE='APPLY-OFF';
disable configuration;

Check if the standby database is opened in READ ONLY WITH APPLY, READ ONLY or MOUNTED mode:

select open_mode from v$database;

If in READ ONLY WITH APPLY or READ ONLY mode, close the database:

 alter database close;

Often, a database that has been opened in READ ONLY mode still have active sessions. In such cases, it may be necessary to shut the database down and open it in mount-mode:

 shutdown immediate
 startup mount

Activate the standby database:

 ALTER DATABASE ACTIVATE STANDBY DATABASE;

Verify that the status of the control file has changed from "STANDBY" to "CURRENT":

 select CONTROLFILE_TYPE from v$database;
 
 CONTROL
 -------
 CURRENT

Open the database:

 
 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
 ALTER DATABASE OPEN;

If not done by the broker, reset the value of log_archive_dest_2:

 Alter system set log_archive_dest_2='';

Your database should now be stand alone and out of the data guard configuration.

To avoid errors related to redo shipping, make sure that your old primary no longer is attempting to ship redo log information to the old standby database, which is now out of the data guard configuration.
On the old primary server, set the relevant log_archive_dest_n parameter to DEFER:

 alter system set log_archive_dest_state_2=defer scope=both;

The cause for and solution to the error message "Database mount ID mismatch" in your previously configured standby database

If you have a previously configured standby database, and you have converted it to a free standing database, no longer a part of a data guard setup, you may see some errors in the alert log looking like this

2022-05-03T12:52:33.896905+02:00
RFS[1332]: Assigned to RFS process (PID:128748)
RFS[1332]: Database mount ID mismatch [0xb40d4ed8:0xb42c30e2] (3020771032:3022794978)
RFS[1332]: Not using real application clusters

Reason:
Even though you have used the data guard broker to stop log shipping, and activated the standby database (inn effect making it read writable), the broker will not stop the previously configured primary database from shipping logs to its previously configured standby destination.

Solution:
Cut off the log shipping from the previously configured primary database completely by either

1) changing the value of log_archive_dest_state_2 from enabled to defer:

alter system set log_archive_dest_state_2=defer scope=both;

or by

2) removing the value of log_archive_dest_2 altogether:

alter system set log_archive_dest_2='' scope=both;

How to solve message from broker Warning: standby redo logs not configured for thread 1 on primary

DGMGRL> validate database 'proddb01';

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    tdridbt1:  Off

  Managed by Clusterware:
    tdridbt1:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:    Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:    NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (proddb01)              (stby01)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stby01)              (proddb01)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on proddb01

The solution was found in Doc ID 1956103.1 "Warning: standby redo logs not configured for thread on "

This is how I corrected the situation:

On the primary, check the status of the standby redo log files:

set lines 200
col member format a50
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#;


    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

Indeed, there is no standby redo logs assigned to thread 1.

First, set standby_file_management to MANUAL on both primary and standby, if not already done.

alter system set standby_file_management=MANUAL;

Still on the primary, drop the standby redo logs:

alter database drop logfile group 4;

Database altered.

alter database drop logfile group 5;

Database altered.

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Still on the primary, add new standby redo logs:

alter database add standby logfile thread 1 group 4('/u03/oradata/proddb01/stb_redo01.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 5('/u03/oradata/proddb01/stb_redo02.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 6('/u03/oradata/proddb01/stb_redo03.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 7('/u03/oradata/proddb01/stb_redo04.log') size 2048M REUSE;

Run the query above again, and you should see a thread number assigned to each group:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

On the standby database, stop managed recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check the status:

     GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log                 STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo02.log                 STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log                 STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log                 STANDBY        

Drop the two standby redo log groups not having been assigned a thread number:

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Add two new groups:

alter database add standby logfile thread 1 group 6('/u03/oradata/stby01/stb_redo03.log') size 2048M REUSE;

Database altered.

alter database add standby logfile thread 1 group 7('/u03/oradata/stby01/stb_redo04.log')  size 2048M REUSE;

Database altered.

Start managed recovery again:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.

Verify by running the same query once more:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log               STANDBY
         5          1          0 NO  UNASSIGNED /u03/oradata/stby01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log               STANDBY

The warning is now gone from the output of data guard broker:

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:  Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

Finally, remember to adjust standby_file_management to AUTO on both primary and standby databases:

alter system set standby_file_management=AUTO;

How to use active database duplication for creating a physical standby

When you use active database duplication for creating a physical standby database, make sure of the following:

1. keep your pfile used to start an auxiliary instance to a minimum. Only the following lines are needed:

db_block_size=8192
db_name='proddb01'

Of course, the db_name must be identical to the db_name of the primary database.

2. In your duplication script, make sure you the spfile contains a correct value for db_unique name:

run{
        allocate channel c1 type disk;
        allocate channel c2 type disk;
        allocate channel c3 type disk;
        allocate channel c4 type disk;
        allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
SET db_unique_name='stby01'
SET db_domain='mydomain.no'
SET FAL_CLIENT='stby01'
SET FAL_SERVER='proddb01'
SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby01'
SET log_archive_dest_2=''
SET control_files='/data1/oradata/stby01/control01.ctl','/data2/fra/stby01/control02.ctl'
SET STANDBY_FILE_MANAGEMENT='MANUAL'
SET LOG_ARCHIVE_MAX_PROCESSES='2'
SET local_listener='stby01.skead.no'
SET dg_broker_config_file1='/sw/oracle/product/12201/dbs/dr1stby01.dat'
SET dg_broker_config_file2='/sw/oracle/product/12201/dbs/dr2stby01.dat'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(stby01,proddb01)'
SET diagnostic_dest='/sw/oracle'
SET db_recovery_file_dest='/data2/fra'
SET db_file_name_convert='/data1/oradata/proddb01','/data1/oradata/stby01'
SET log_file_name_convert='/data3/oradata/proddb01','/data3/oradata/stby01'
NOFILENAMECHECK;
}

Note that the directive "spfile" used right before the individual SET commands does not mean "create spfile". It means "fetch spfile from primary". The SET commands, however, will be written to your local spfile which will then be used to start the auxiliary instance once more, before the actual cloning starts.

When the duplicaton has finished, the value of "db_name" parameter will be identical to the primary database, since we are fetching the spfile from your primary database server over the network.

How to fix error message from data guard broker "Property 'DbFileNameConvert' has inconsistent values"

After a rebuild of a physical standby database, I was tailing the broker log file drcSALES.log, and noticed the following warning:

Property 'DbFileNameConvert' has inconsistent values:
METADATA='/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
SPFILE=  '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES', 
DATABASE='/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Note that the metadata and spfile is missing the trailing slash character /.

To fix this incorrect setup:

dgmgrl / as sysdba
show configuration;
Configuration - SALES

  Protection Mode: MaxPerformance
  Members:
  SALES      - Primary database
    SALES_STB  - Physical standby database
      SALES_TSTB - Physical standby database (receiving current redo)
    SALES_RO   - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Check the current setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES, /data1_tstb/oradata/SALES, /data2/oradata/SALES, /data2_tstb/oradata/SALES'

Update the property:

DGMGRL> edit database 'SALES_STB' set property DbFileNameConvert='/data1/oradata/SALES/,/data1_stb/oradata/SALES/, /data2/oradata/SALES/, /data2_stb/oradata/SALES/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "dbfilenameconvert" updated

Verify the new setting:

show database 'SALES_TSTB' DbFileNameConvert
DbFileNameConvert = '/data1/oradata/SALES/, /data1_tstb/oradata/SALES/, /data2/oradata/SALES/, /data2_tstb/oradata/SALES/'

Restart your physical database, and the issue is fixed.

How to clear a standby redo logfile and then dropping it

During set up of a physical standby database, one of the standby redo logfiles was marked as "ACTIVE" when querying the v$standby_log:

  THREAD#     GROUP#  SEQUENCE#      BYTES ARCHIVED  STATUS
---------- ---------- ---------- ---------- --------- ------------------------------
         0         12          0 2097152000 YES       UNASSIGNED
         0         13          0 2097152000 YES       UNASSIGNED
         0         14          0 2097152000 YES       UNASSIGNED
         0         15          0 2097152000 YES       UNASSIGNED
         0         16          0 2097152000 YES       UNASSIGNED
         0         17          0 2097152000 YES       UNASSIGNED
         0         18          0 2097152000 YES       UNASSIGNED
         1         11     392344 2097152000 YES       ACTIVE

The file is not even existing on my system, but the path was somehow copied from the primary database, which has a different file structure.
The query:

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

would return

  GROUP# TYPE                  MEMBER
---------- --------------------- ------------------------------------------------------------
        11 STANDBY               /backup/fast_recovery_area/PRODDB01/onlinelog/stb_redo01.log

The path /backup/fast_recovery_area/PRODDB01/onlinelog doesn't even exist on my server. This makes it impossible to drop and recreate it:

SQL> alter database drop standby logfile group 11;
alter database drop standby logfile group 11
*
ERROR at line 1:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1:
'/fra/stdb/onlinelog/stb_redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution: Clear the logfile:

SQL> alter database clear logfile group 11;

Database altered.

Then drop and recreate the standby redo logfile:

SQL> alter database drop standby logfile group 11;

Database altered.

SQL> alter database add standby logfile group 11 ('/data1/oradata/PRODDB01/stb_redo01.log') size 2000M;

How to manually register missing logsequences on a standby database

I have previously documented how to identify gaps in your standby database's log sequence. See these posts:

How to check if your physical standby database is applying logs or not
How to find out if your standby database lags behind the primary database
How to find the last archivelog received and applied in a standby database

After a successful rescue operation of my standby database, I had a 3-day lag behind the primary. It's easy to identified these using the data guard broker command below:

show database "prod_stby" RecvQEntries

Output from this command was (abbreviated):

STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID  THREAD   LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        894886266  1    372460  01/28/2021 16:46:01  01/28/2021 16:47:35        6196037493227        6196037506094            20840
         NOT_APPLIED        894886266  1    372462  01/28/2021 17:02:35  01/28/2021 18:31:30        6196037647447        6196038652945          1350187
         NOT_APPLIED        894886266  1    372463  01/28/2021 18:31:30  01/28/2021 19:09:06        6196038652945        6196039875468          1051704
         .
         .
         .

The logfiles were phyically present in the Flash Recovery Area:

cd /fra/PROD_STBY/archivelog
find . -name "*372108*"
./2021_01_25/o1_mf_1_372108_j0x8j1fc_.arc

There are two ways to inform the standby database about the presence of the logfile:

1. RMAN.
On the standby database:

rman target /

Verify that the standby database does not recognize the archivelog:

list archivelog sequence between 372106 and 372107;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PROD_STBY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
371883  1    372106  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372106_j0x6x0cj_.arc

The output above confirms that sequence 372106 exists, and that sequence 372107 does not. 

To catalog the missing file:

RMAN> catalog start with '/fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc';

using target database control file instead of recovery catalog
searching for all files that match the pattern /fra/PROD_STB//archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

List of Files Unknown to the Database
=====================================
File Name: /fra/PROD_STB/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /fra/PROD_STB/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

Confirm again, and you'll see that the new files is registered:

RMAN> list archivelog sequence between 372106 and 372107;

List of Archived Log Copies for database with db_unique_name PROD_STBY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
371883  1    372106  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372106_j0x6x0cj_.arc

371956  1    372107  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

If the number of logfiles missing is large, use a shortcut to register them all:

RMAN> catalog start with '/fra/PROD_STBY/archivelog/2021_01_25';

The above command will register all logfiles in the directory /fra/PROD_STBY/archivelog/2021_01_25

 
2. sqlplus:

SQL> alter database register logfile '/fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc';

If you tail the alert log of the database, you'll see that the standby database quickly picks up the missing logfiles.

Potential solution to dgmgrl error ORA-16665: time out waiting for the result from a member

After having added a terminal standby database to an existing configuration, the Data Guard Broker configuration seemed unhappy with communicating with the new member. The output from "show configuration" showed the following:

DGMGRL> show configuration;

Configuration - DB01

  Protection Mode: MaxPerformance
  Members:
  DB01      - Primary database
    DB01_STB  - Physical standby database
      DB01_TSTB - Physical standby database (receiving current redo)
        Error: ORA-16665: time out waiting for the result from a member

    DB01_RO   - Physical standby database

When looking at the details by using

show database verbose "DB01_TSTB"

the entire operation would take very long, and at the, the following message is displayed:

Database Status:
DGM-17016: failed to retrieve status for database "DB01_TSTB"
ORA-16665: time out waiting for the result from a member

The broker log file showed:

09/02/2020 15:08:52
Data Guard Broker Status Summary:
  Type                        Name                            Severity  Status
  Configuration               DB01                            Warning  ORA-16607
  Primary Database            DB01                            Success  ORA-0
  Physical Standby Database   DB01_STB                        Success  ORA-0
  Physical Standby Database   DB01_RO                         Success  ORA-0
  Physical Standby Database   DB01_TSTB                       Error  ORA-16665

Root cause here was firewalls. The terminal standby database could not reach the primary database. Although the terminal standby database isn't set up to receive redo data from the primary database directly, in a broker configuration all members must be able to communicate with eachother. A good tool for troubleshooting issues dealing with ports and firewalls is nmap. I installed it on the terminal server and issued:

[root@db04_server ~]# nmap -n -p 1511 db01_sever.oric.no

Starting Nmap 6.40 ( http://nmap.org ) at 2020-09-02 14:23 CEST
Nmap scan report for db01_sever.oric.no (xxx.xxx.xxx.xxx)
Host is up (0.016s latency).
PORT     STATE    SERVICE
1511/tcp filtered 3l-l1

Nmap done: 1 IP address (1 host up) scanned in 0.49 seconds

A filtered port means that it is not possible to determine whether the port is open or closed, most often due to firewalls along the way. Further checks in the firewall log files showed

action=Drop service=1511 dst=xxx.xxx.xxx.xxx scr=yyy.yyy.yyy.yyy

where xxx.xxx.xxx.xxx was matching the ipadress of the terminal standby server, while yyy.yyy.yyy.yyy was matching the ipadress of the primary server. The network admin opened the port, and the ORA-16665 immediately disappeared from the dgmgrl output.

How to set parallelism for a data guard recovery process

If you need to manually set the apply-process DOP (Degree of Parallelism), you can do this by setting the property ApplyParallel=num where num is the number of parallel processes. 

In my setup, I have three databases: 

•  A primary database (hdal) 
• A physical standby database (hdal_stb) which serves as a "cascading" database 
• A terminal standby database (hdal_tstb) which receives redo from the cascading standby database  

Logged in on any one of the participating servers of your data guard configuration, start dgmgrl: 

dgmgrl / as sysdba
Connected to "hdal_stb"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

DGMGRL> edit database 'hdal_stb' set property ApplyParallel=4;

In the database's alert log, we can se that Oracle is cancelling the ongoing recovery process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2020-08-10T09:26:24.249847+02:00
MRP0: Background Media Recovery cancelled with status 16037
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 27754456
2020-08-10T09:26:24.510262+02:00
ORA-16037: user requested cancel of managed recovery operation
2020-08-10T09:26:24.617519+02:00
MRP0: Background Media Recovery process shutdown (hdal)
2020-08-10T09:26:25.250904+02:00
Managed Standby Recovery Canceled (hdal)

And then starting it again:

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT PARALLEL 4 NODELAY
2020-08-10T09:26:25.284547+02:00
Attempt to start background Managed Standby Recovery process (hdal)
Starting background process MRP0
2020-08-10T09:26:25.306150+02:00
MRP0 started with pid=77, OS id=115854
2020-08-10T09:26:25.311038+02:00
MRP0: Background Managed Standby Recovery process started (hdal)
2020-08-10T09:26:30.339894+02:00
 Started logmerger process
2020-08-10T09:26:30.378497+02:00
Managed Standby Recovery starting Real Time Apply
2020-08-10T09:26:30.515607+02:00
Parallel Media Recovery started with 4 slaves
2020-08-10T09:26:30.741846+02:00
Media Recovery Waiting for thread 1 sequence 8299 (in transit)

Verify the new setting by looking at the properties (output truncated for brevity):

DGMGRL> show database verbose hdal_stb

Database - hdal_stb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 0 Byte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    hdal

  Properties:
    DGConnectIdentifier             = 'hdal_stb'
    LogXptMode                      = 'ASYNC'
    ApplyParallel                   = '4'

The change will be propagated to the other members of the data guard configuration. The data guard broker logfile, located in $ORACLE_BASE/diag/rdbms/$UNIQUE_NAME/$ORACLE_SID/trace, shows:

08/10/2020 09:26:24
Forwarding EDIT_RES_PROP operation to member hdal for processing
08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_stb.dat"
08/10/2020 09:42:35
Forwarding MON_PROPERTY operation to member hdal_tstb for processing

The cascading database hdal_tstb simply confirms that the broker configuration has been updated:

08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_tstb.dat"

The primary database is also verifying the change, although a bit more detailed:

08/10/2020 09:26:24
EDIT DATABASE hdal_stb SET PROPERTY applyparallel = 4
08/10/2020 09:26:31
EDIT INSTANCE hdal ON DATABASE hdal_stb SET PROPERTY applyparallel = 4 completed successfully

Documentation for dgmgrl version 19c can be found here

Potential solution to ORA-1033 during configuration of standby database in data guard broker

In my environment, the following message was displayed in dgmgrl when trying to configure a data guard environment:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

I had prior to this attempt duplicated the target database to the auxiliary using "duplicate target databas for standby from active database" - and the output looked fine.

Turns out that you cannot set the parameter redo_transport_user to any other value than SYS when cloning for standby.
In my environment, we use a dedicated user for this purpuse, as we avoid using the SYS user as much as possible.

I adjusted the parameter to read SYS on both the primary and standby instance, and reran the duplication.

Afterwards, the database could be added:

DGMGRL> add database 'hdal_stb' as  connect identifier is 'hdal_stb';
Database "hdal_stb" added

What is the difference between real-time apply and real-time query?

Real-time apply is the process of applying the redo in the current standby redo log file as it is being filled, in contrast to waiting for the standby redo log to be filled up. By default, apply services will wait for a standby redo log file to be archived, then apply the redo that it contains.


Real-time query is the process of applying redo redo while the physical standby database is open
This allows for read-only usage of the data which is identical to the primary database.
Note: Real-time query is licenced as the "Active Data Guard" option.


Real-time apply is documented here

Real-time query is documented here

Workaround for error Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Error from data guard:

oracle@oric-dbserver01:[vegdb01]# dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 12 08:02:54 2020
Version 18.9.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "vegdb01"
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - myConfig

  Protection Mode: MaxPerformance
  Members:
  vegdb01      - Primary database
    vegdb01_stby - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 10 seconds ago)

DGMGRL> show database vegdb01_stby

Database - vegdb01_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 143.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    vegdb01
      Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Database Status:
WARNING

Solved by setting the log_file_name_convert on the standby side.
Using dgmgrl:

edit DATABASE "vegdb01_stby" SET PROPERTY LogFileNameConvert =  " "," "; 

Or set it by using sqlplus on the standby site, see this post for how to do that

How an incorrect password file format can stop the redo apply process (MRP0) on standby database

Error in dgmgrl shows:

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  proddb01   - Primary database
    stby02- Physical standby database
    stby01 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 36 seconds ago)

Checking the database throwing error:

DGMGRL> show database stby01

Database - stby01

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          21 hours 43 minutes 37 seconds (computed 0 seconds ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    pipat

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Try restarting redo apply:

DGMGRL>  edit database 'stby01'  set state='APPLY-OFF';
Succeeded.
DGMGRL>  edit database 'stby01'  set state='APPLY-ON';
Succeeded.

The alert log reports:

2019-12-03T11:33:22.214114+01:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2019-12-03T11:33:22.270642+01:00
Attempt to start background Managed Standby Recovery process (proddb01)
Starting background process MRP0
2019-12-03T11:33:22.312794+01:00
MRP0 started with pid=105, OS id=20354
2019-12-03T11:33:22.333315+01:00
MRP0: Background Managed Standby Recovery process started (proddb01)
2019-12-03T11:33:27.472196+01:00
 Started logmerger process
2019-12-03T11:33:27.599549+01:00
Managed Standby Recovery starting Real Time Apply
2019-12-03T11:33:27.801888+01:00
Parallel Media Recovery started with 4 slaves
2019-12-03T11:33:28.279378+01:00
Media Recovery Log /u04/fra/STBY01/archivelog/2019_12_02/o1_mf_1_121201__y2thfwyz_.arc
2019-12-03T11:33:28.318586+01:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
MRP0: Background Media Recovery terminated with error 46952
2019-12-03T11:33:28.372536+01:00
Errors in file /orasoft/diag/rdbms/stby01/proddb01/trace/proddb01_pr00_20395.trc:
 ORA-46952: standby database format mismatch for password file '/orasoft/product/122/dbs/orapwproddb01'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 826857150
2019-12-03T11:33:28.447080+01:00
2019-12-03T11:33:28.554534+01:00
MRP0: Background Media Recovery process shutdown (proddb01)

The password file is of an older version and should be recreated in order to ressume log apply. How to do this is outlined in one of my previous posts, available here.

After you have done this, restart redo apply again with

DGMGRL>  edit database 'stby01'  set state='APPLY-ON';
Succeeded.

Put a tail on the standby database's alert log and you'll see that the outstanding logs are cherned through quicly.

How to change location for the redo log files in physical standby database

Short background:
After a cold restore of the database files from standbyserver1 to standbyserver2, the redo logfiles where incorrectly registered in the database's control file.
This resulted in an error about the missing file every time the database opened or mounted:

Errors in file /u01/oracle/diag/rdbms/prodbb01_stby2/proddb01/trace/proddb01_m000_38117.trc:
ORA-00312: online log 3 in thread 1: /fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Since this is a standby database it is not stopping the database from functioning as intended, but it was annoying nevertheless.
Here is how I fixed the problem:

Shutdown the database, cancel managed recovery and open it in mounted mode:

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount

Allow file renaming on the stadby by temporarily setting STANDBY_FILE_MANAGEMENT to MANUAL.

alter system set STANDBY_FILE_MANAGEMENT=manual scope=memory;

If this is not done, Oracle will throw error

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

Rename the file(s):

alter database rename file '/fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log' to '/fra/PRODDB01_STBY2/onlinelog/o1_mf_3__df26ddnv_.log';

Depending on your configuration, start managed reply in mounted state, or open the database in real-time query mode. I am doing the latter:

alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

How to put a mounted standby database in Real-Time Query mode

The database is currently mounted as a normal physical standby database:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Cancel managed recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-OFF';

Open the database. It will automatically open in read-only mode:

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Start managed recovery again:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

If you use the broker, the equivalent command would be

EDIT DATABASE 'proddb01_stby1' SET STATE='APPLY-ON';

Check the database status:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Before this change, the broker status showed:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 9 minutes 31 seconds (computed 1 second ago)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF

After the change:

DGMGRL> show database proddb01_stby1

Database - proddb01_stby1

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 2.55 MByte/s
Real Time Query: ON

How to check if your physical standby database is applying logs or not

For Oracle versions 12.1 and lowe, use V$MANAGED_STANDBY:

SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS
FROM V$MANAGED_STANDBY
WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'
;

CLIENT_PROCESS	PROCESS	THREAD#	SEQUENCE#	STATUS
LGWR	RFS	1	116713	RECEIVING
N/A	MRP0	1	114039	APPLYING_LOG

Important columns are

* PROCESS, which is the type of process whose information is being reported
MRP0 means the detached recovery server process. In this case, it is applying logs.

* CLIENT_PROCESS, which identifies the corresponding primary database process.
LGWR indicates the background log writer process

If there is a gap to be covered, you'll see the processes as being idle:

CLIENT_PROCESS	PROCESS	THREAD#	SEQUENCE#	STATUS
LGWR	RFS	1	116727	IDLE
N/A	MRP0	1	114115	WAIT_FOR_LOG

This is a good time to check where the gap is:

SELECT * FROM v$archive_gap;

THREAD#	LOW_SEQUENCE#	HIGH_SEQUENCE#	CON_ID
1	114120	114121	1

This matches the alert log of the standby database:

FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 114120-114121

For Oracle 12.2 and beyond, Oracle encourage DBAs to use V$DATAGUARD_PROCESS (although V$MANAGED_STANDBY is still there for backward compability).
This view "displays one row for each Oracle Data Guard process that is currently running."

According to Oracle's documentation, there should be information about a process mapped toa role called "recovery logmerger" which in the ACTION column should state "APPLYING_LOG". I have repeatedly tried to find this information without luck.

What I do find in V$DATAGUARD_PROCESS is a role called "managed recovery".
If I run these queries against my database, they seem to yield two different results, the first telling me that manged recovery is idle, the second telling me that it is applying logs:

set lines 200
select name "process name",pid "standby server PID",role,action,client_role,client_pid "primary server PID" ,task_done
from v$dataguard_process
where role like 'managed recovery'
order by role desc, action;

SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS
FROM V$MANAGED_STANDBY
WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
exit

Output:

proce standby server PID       ROLE                    ACTION       CLIENT_ROLE      primary server PID T
----- ------------------------ ----------------------- ------------ ---------------- ------------------ -
MRP0  10714                    managed recovery        IDLE         none                              0 N


CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1     116940 RECEIVING
N/A      MRP0               1     116940 APPLYING_LOG

Comments about this seemingly contradictory output would be much welcome.

How do identify a transport error in a Data Guard setup using the broker

Applicable to Oracle 12.2

If the broker has been setup, check the configuration basics:

dgmgrl /
show configuration;
Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  proddb01       - Primary database
    Error: ORA-16778: redo transport error for one or more members

    proddb01_stby1 - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

    proddb01_stby2 - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

In the configuration above, I have a primary + two physical standby databases.

Check the database:

DGMGRL> show database proddb01;

Database - proddb01

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    proddb01
      Error: ORA-16737: the redo transport service for member "proddb01_stby1" has an error

Use the 'LogXptStatus' monitorable property to check for transporation errors:

DGMGRL> show database proddb01 'LogXptStatus'
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME     STATUS                ERROR
            proddb01       proddb01_stby1      ERROR ORA-16191: Primary log shipping client not logged on standby
            proddb01       proddb01_stby2      VALID

The error ORA-16191 was in my case related to password files not being identical on the standby and the primary database.

For the standby databases, you can use the monitorable property 'RecvQEntries', which returns a table indicating all log files that were received by the standby database but have not yet been applied:

DGMGRL>  show database proddb01_stby1 'RecvQEntries'
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        894183587                1               110406  07/19/2019 10:28:10  07/19/2019 10:29:19          73008920461          73009040943           424661
         NOT_APPLIED        894183587                1               110408  07/19/2019 10:30:35  07/19/2019 10:31:50          73009169966          73009301192           442727
         NOT_APPLIED        894183587                1               110409  07/19/2019 10:31:50  07/19/2019 10:33:14          73009301192          73009443216           426723
         NOT_APPLIED        894183587                1               110410  07/19/2019 10:33:14  07/19/2019 10:34:32          73009443216  

The same information can be obtained by checking the view v$archived_log, like this:

select sequence#
from v$archived_log
where registrar = 'RFS'
and   applied = 'NO';

The actual lag that these non-applied logs causes, can be found with the query:

set lines 200
col source_db_unique_name format a20
col value format a20
SELECT source_db_unique_name,name,value,unit,time_computed,datum_time
FROM v$dataguard_stats
WHERE name = 'apply lag';

The RecvQEntries property is documented here

The LogXptStatus property is documentet here