How to solve – ORA-38788: More standby database recovery is needed

When doing a duplicate of database, restore, or simply convert to snapshot – we can get the error ORA-38788. See below:

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point ‘SNAPSHOT_STANDBY_REQUIRED_10/17/2020 10:21:50’.
ORA-38788: More standby database recovery is needed

############################
Solution:
############################

If I recovered using rman -> recover database – will not stop until the last archivelog.
I tried to recover until sequence but the problem was the same. Requesting more recover.
This approach can be tried – but, if you start to recover database you will use the option REAL APPLY.
Please don’t use this approach if you are not totally fine with the terms used here.
Real apply is an option – and is very expensive. To use open database read only and start recover of archivelogs.
Only opening database for read only without recover process active, is fine.

Check I am stopping the recovery processes first:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.

My first shot: Tried to put flashback on: Not working.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point ‘SNAPSHOT_STANDBY_REQUIRED_10/17/2020 10:30:04’.
ORA-38788: More standby database recovery is needed

My solution: starting to open read only, database will apply all necessary logs and will open database read only.
This means, will not apply all archivelogs present in recovery area, will apply only necessary to the recover database.

SQL> alter database open read only;

##################################################
Alert log file:
##################################################
alter database open read only
2020-10-17T10:30:56.729708-03:00
Ping without log force is disabled:
instance mounted in exclusive mode.
Signalling error 1152 for datafile 1!
2020-10-17T10:30:56.804252-03:00
Beginning Standby Crash Recovery.
Started logmerger process
2020-10-17T10:30:57.262836-03:00
Managed Standby Recovery starting Real Time Apply
2020-10-17T10:31:00.947756-03:00
Parallel Media Recovery started with 8 slaves
2020-10-17T10:31:02.173885-03:00
Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_1_seq_60765.508.1054028951
2020-10-17T10:31:02.355754-03:00
Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_2_seq_57944.399.1054029017
2020-10-17T10:32:04.559638-03:00

And more archivelogs .. will stop when oracle database stay consistent.

Media Recovery Log +RECOC2/CDBX/ARCHIVELOG/2020_10_17/thread_2_seq_57979.366.1054029013
2020-10-17T10:44:11.183620-03:00
Incomplete Recovery applied until change 89911707313 time 10/16/2020 09:08:00
2020-10-17T10:44:12.665363-03:00
Completed Standby Crash Recovery.
2020-10-17T10:44:14.195065-03:00
Endian type of dictionary set to little
2020-10-17T10:44:21.001041-03:00
Undo initialization finished serial:0 start:2974475204 end:2974475204 diff:0 ms (0.0 seconds)
Dictionary check beginning
2020-10-17T10:44:23.588573-03:00

##################################################
Database was open
##################################################

Pluggable database XXXXX opened read only
2020-10-17T10:47:26.812990-03:00
Completed: ALTER PLUGGABLE DATABASE ALL OPEN
Completed: alter database open read only

DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE VERSION COMPATIBLE STATUS
—————- —————- ——————– ———- ———- ——–
CDBX PHYSICAL STANDBY READ ONLY 12.2.0.1.0 12.2.0.0.0 OPEN

HOST_NAME INSTANCE_NAME STATUS Startup time LOGINS CONNECTED
———————————- ————– ——– ————————————————————————— ——– ————
exacc CDBX OPEN 17-OCT-2020 08:03 ALLOWED CURRENT
oraclecloudatcustomer.com

##################################################
Some errors can be found in alert log and need be checked.
##################################################

cannot identify/lock data file 1100 – see DBWR trace file
ORA-01110: data file 1100: ‘+DATAC2’
2020-10-17T10:45:59.653763-03:00
File 1100 not verified due to error ORA-01157
2020-10-17T10:45:59.743584-03:00
XXXXXPDB(67):Pluggable Database XXXXXPDB Dictionary check complete
2020-10-17T10:45:59.859089-03:00
Errors in file /u02/app/oracle/diag/rdbms/CDBX/CDBX/trace/CDBX_dbw0_246152.trc:
ORA-01186: file 1062 failed

File 1094 not verified due to error ORA-01157
2020-10-17T10:46:17.516127-03:00
J5CEN1RMP(55):Re-creating tempfile +DATAC2 as +DATAC2/CDBX/999B4F7580E46340E053023013ACC277/TEMPFILE/rm_tmp.1263.1054032377

/u02/app/oracle/diag/rdbms/CDBX/CDBX/trace/CDBX_dbw0_246152.trc