ORA-38500 While Enabling Real-Time Redo Apply

If you are trying to enable real time redo apply whereby LGWR process of primary database writes directly to the standby redo log file of the standby database, you might face ORA-38500 error message. Following is an example of this error message.

SQL> ALTER DATABASE RECOVER MANAGED STAND BY DATABASE USING CURRENT LOGFILE DISCONNECT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT * ERROR at line 1: ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs”

 The reason for this error message is related to log applies service. “USING CURRENT LOGFILE” option means we want to have “real time apply”, and “real time apply” needs standby redo log files to be created in the standby database before we can use this feature.

To avoid/solve this error, we need to add standby redo log groups in standby database because real time apply needs LGWR to write redo log data from primary; directly to the standby redo log groups at standby site. Oracle recommends to create “total number of primary redo log file group + 1” standby redo log groups in standby database. Formula to calculate the standby redo log groups is as follows

(maximum number of logfiles +1) * maximum number of threads

Size of standby redo log groups should be same as primary redo log groups. Following command can be used to add standby redo log groups in standby database.

ALTER DATABASE ADD STANDBY LOGFILE GROUP <group #> <’logfile path’>
SIZE <size in megabytes>M;

The reason to have one extra standby redo log group per thread is to avoid blockage of LGWR process of primary database in case IO on standby database is slow and that is causing release of standby redo log groups (so that these can be reused on log switch in primary) delayed. More redo log groups on standby would avoid this blockage.  

If primary database has 10 redo log groups in total with a size of 512 MB each, we should create total 11 standby redo log groups.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 19 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 20 ‘+ORAREDO’ SIZE 512m; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 21 ‘+ORAREDO’ SIZE 512m;