Last weekend we upgraded a database from 126.96.36.199 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
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.