In Oracle there are (at least) two posible fixes of this issue:

• Change the ORDER BY, and use the column number instead of the column name:

  ...

  $sql = "SELECT l.*, u.firstname, u.lastname

  FROM {log} l

  JOIN {cache_flags} cf ON l.ip = cf.name

  LEFT JOIN {user} u ON l.userid = u.id

  WHERE l.module = 'login' AND l.action = 'error'

  AND l.time > ?

  AND cf.flagtype = 'login_failure_by_ip'

  UNION ALL

  SELECT l.*, u.firstname, u.lastname

  FROM {log} l

  JOIN {cache_flags} cf ON l.info = cf.name

  LEFT JOIN {user} u ON l.userid = u.id

  WHERE l.module = 'login' AND l.action = 'error'

  AND l.time > ?

  AND cf.flagtype = 'login_failure_by_info'

  ORDER BY 2 DESC";

• Group the query inside a new select:

  ...

  $sql = "SELECT * FROM (SELECT l.*, u.firstname, u.lastname

  FROM m_log l

  JOIN m_cache_flags cf ON l.ip = cf.name

  LEFT JOIN m_user u ON l.userid = u.id

  WHERE l.module = 'login' AND l.action = 'error'

  AND l.time > ?

  AND cf.flagtype = 'login_failure_by_ip'

  UNION ALL

  SELECT l.*, u.firstname, u.lastname

  FROM m_log l

  JOIN m_cache_flags cf ON l.info = cf.name

  LEFT JOIN m_user u ON l.userid = u.id

  WHERE l.module = 'login' AND l.action = 'error'

  AND l.time > ?

  AND cf.flagtype = 'login_failure_by_info') t

  ORDER BY t.time DESC";

This needs testing on all DBs. Note that if you set $CFG->divertallemailsto = 'youremailaddress@foo.com'; can be used to test emails

Note: need oracle db for testing

1. Set notifyloginfailures to something other than nobody (in Admin > Security > Notifications)
2. Attempt to login with incorrect password more than 10 (default) times
3. run cron (moodle/admin/cron.php)
4. It should run without any errors
5. Ensure that login filaures are mailed to the user specified in notifyloginfailures