ORA-00274 after upgrade to 19c and trying to enable a delay on recovery for standby database.

Introduction

Last weekend we upgraded a database from 11.2.0.4 to 19.10 for one of our clients. This database is running on Exadata HW with 2 standby databases configured. As part of planning this upgrade, we removed one of the standby databases from the data guard configuration.

This week when I was rebuilding it now with 19c binaries, after successful cloning and standby configuration I got the below error when trying to start recovery with a delay.

For 11g, I used to start recovery for this standby as follows:

SQL> alter database recover managed standby database delay 1440 disconnect from session;

But now on 19c when I run the same, I am getting the below error:

SQL> alter database recover managed standby database  disconnect from session delay 1440;
alter database recover managed standby database  disconnect from session delay 1440
*
ERROR at line 1:
ORA-00274: illegal recovery option DELAY

Analysis

So, I went to documentation and found this behavior has been changed starting on 12c, and to have this working now we need to configure delay on ARCHIVE_LOG_DEST_N. Below there is an example. This has to be done on the primary side, or if a cascade redo has been configured, on the standby shipping logs to the second standby:

Alter system set log_archive_dest_3='SERVICE=DELAYSTB NOAFFIRM ASYNC VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELAYSTB DELAY=1440';

After this has been done on the shipping database, we execute the below command to start recovery on the standby database:

SQL>  alter database recover managed standby database using archived logfile disconnect;

Note, if we don’t use the clause “using archive logfile” the delay may be ignored.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?