{"id":1205,"date":"2020-10-18T09:06:03","date_gmt":"2020-10-18T12:06:03","guid":{"rendered":"http:\/\/www.soudba.com.br\/?p=1205"},"modified":"2020-10-18T09:06:03","modified_gmt":"2020-10-18T12:06:03","slug":"how-to-solve-ora-38788-more-standby-database-recovery-is-needed","status":"publish","type":"post","link":"https:\/\/www.soudba.com.br\/?p=1205","title":{"rendered":"How to solve – ORA-38788: More standby database recovery is needed"},"content":{"rendered":"

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

SQL> ALTER DATABASE FLASHBACK ON;
\nALTER DATABASE FLASHBACK ON
\n*
\nERROR at line 1:
\nORA-38706: Cannot turn on FLASHBACK DATABASE logging.
\nORA-38788: More standby database recovery is needed<\/p>\n

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
\nALTER DATABASE CONVERT TO SNAPSHOT STANDBY
\n*
\nERROR at line 1:
\nORA-38784: Cannot create restore point ‘SNAPSHOT_STANDBY_REQUIRED_10\/17\/2020 10:21:50’.
\nORA-38788:<\/strong> More standby database recovery is needed<\/p>\n

############################
\nSolution:
\n############################<\/h1>\n

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

Check I am stopping the recovery processes first:<\/p>\n

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
\nDatabase altered.<\/p>\n

My first shot: Tried to put flashback on: Not working.<\/p>\n

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

My solution: starting to open read only, database will apply all necessary logs and will open database read only.
\nThis means, will not apply all archivelogs present in recovery area, will apply only necessary to the recover database.<\/h1>\n

SQL> alter database open read only;<\/p>\n

##################################################
\nAlert log file:
\n##################################################
\nalter database open read only
\n2020-10-17T10:30:56.729708-03:00
\nPing without log force is disabled:
\ninstance mounted in exclusive mode.
\nSignalling error 1152 for datafile 1!
\n2020-10-17T10:30:56.804252-03:00
\nBeginning Standby Crash Recovery.
\nStarted logmerger process
\n2020-10-17T10:30:57.262836-03:00
\nManaged Standby Recovery starting Real Time Apply
\n2020-10-17T10:31:00.947756-03:00
\nParallel Media Recovery started with 8 slaves
\n2020-10-17T10:31:02.173885-03:00
\nMedia Recovery Log +RECOC2\/CDBX\/ARCHIVELOG\/2020_10_17\/thread_1_seq_60765.508.1054028951
\n2020-10-17T10:31:02.355754-03:00
\nMedia Recovery Log +RECOC2\/CDBX\/ARCHIVELOG\/2020_10_17\/thread_2_seq_57944.399.1054029017
\n2020-10-17T10:32:04.559638-03:00<\/p>\n

And more archivelogs .. will stop when oracle database stay consistent.<\/strong><\/p>\n

Media Recovery Log +RECOC2\/CDBX\/ARCHIVELOG\/2020_10_17\/thread_2_seq_57979.366.1054029013
\n2020-10-17T10:44:11.183620-03:00
\nIncomplete Recovery applied until change 89911707313 time 10\/16\/2020 09:08:00
\n2020-10-17T10:44:12.665363-03:00
\nCompleted Standby Crash Recovery.
\n2020-10-17T10:44:14.195065-03:00
\nEndian type of dictionary set to little
\n2020-10-17T10:44:21.001041-03:00
\nUndo initialization finished serial:0 start:2974475204 end:2974475204 diff:0 ms (0.0 seconds)
\nDictionary check beginning
\n2020-10-17T10:44:23.588573-03:00<\/p>\n

##################################################
\nDatabase was open
\n##################################################<\/h1>\n

Pluggable database XXXXX opened read only
\n2020-10-17T10:47:26.812990-03:00
\nCompleted: ALTER PLUGGABLE DATABASE ALL OPEN
\nCompleted: alter database open read only<\/p>\n

DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE VERSION COMPATIBLE STATUS
\n—————- —————- ——————– ———- ———- ——–
\nCDBX PHYSICAL STANDBY READ ONLY 12.2.0.1.0 12.2.0.0.0 OPEN<\/p>\n

HOST_NAME INSTANCE_NAME STATUS Startup time LOGINS CONNECTED
\n———————————- ————– ——– ————————————————————————— ——– ————
\nexacc CDBX OPEN 17-OCT-2020 08:03 ALLOWED CURRENT
\noraclecloudatcustomer.com<\/p>\n

##################################################
\nSome errors can be found in alert log and need be checked.<\/strong>
\n##################################################<\/p>\n

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

File 1094 not verified due to error ORA-01157
\n2020-10-17T10:46:17.516127-03:00
\nJ5CEN1RMP(55):Re-creating tempfile +DATAC2 as +DATAC2\/CDBX\/999B4F7580E46340E053023013ACC277\/TEMPFILE\/rm_tmp.1263.1054032377<\/p>\n

\/u02\/app\/oracle\/diag\/rdbms\/CDBX\/CDBX\/trace\/CDBX_dbw0_246152.trc<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue lendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[64,282],"tags":[360,366,389,390,391,392,395],"class_list":["post-1205","post","type-post","status-publish","format-standard","hentry","category-dataguard","category-snapshot-standby","tag-convert-database-snapshot","tag-duplicate-fail-with-ora-38788","tag-ora-38784","tag-ora-38788","tag-ora-38788-more-standby-database-recovery-is-needed","tag-oracle-duplicate-failure","tag-recover-database"],"_links":{"self":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/1205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1205"}],"version-history":[{"count":0,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/1205\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}