This was first published on https://blog.dbi-services.com/12c-nologging-and-data-guard (2017-05-03)
Republishing here for new followers. The content is related to the the versions available at the publication date
The title sounds weird because Data Guard synchronisation is based on the redo stream, so it makes no sense to do nologging operations on the primary. And this is the reason why we set FORCE LOGGING on a Data Guard configuration. However, to lower the downtime of a migration done with Data Pump, you may want to import with minimal logging and then re-synchronize the standby. This post is about the re-synchronisation in 12.1
When you want to lower the downtime for a migration, you can disable force logging (alter database no force logging), and run impdp with the following:
transform=disable_archive_logging:yDon’t forget to re-enable force_logging at the end and to re-synchronize the standby.
So, you have nonlogged blocks, we also call that unrecoverable because it cannot be recovered with the redo stream. If you are in 12.2 then everything is easy with
recover database nonlogged block;and I explained that in a previous post: https://blog.dbi-services.com/12cr2-recover-nonlogged-blocks-after-nologging-in-data-guard/
If you are in 12.2 then it is half easy only. You can see where you have nonlogged blocks:
RMAN> select file#,reason,count(*) from v$nonlogged_block group by file#,reason; &bsp; FILE# REASON COUNT(*) ---------- ------- ---------- 5 UNKNOWN 158 6 UNKNOWN 159 7 UNKNOWN 336 8 UNKNOWN 94 9 UNKNOWN 16 10 UNKNOWN 14and this is the right way to query them. If you use RMAN ‘report unrecoverable’ it will not display the datafiles that had nologging operations on the primary.
In 12.1 you can RESTORE FROM SERVICE to recover from the primary rather than from a backup. It is straightforward. I’m just writing this blog post in case you see the following when you try to do this because the message can be misinterpreted:
RMAN> restore database from service 'MYDB_SITE1_dgmgrl'; Starting restore at 03-MAY-2017 13:22:12 using channel ORA_DISK_1 skipping datafile 1; already restored to SCN 3849354 skipping datafile 2; already restored to SCN 3849356 skipping datafile 3; already restored to SCN 3849358 skipping datafile 4; already restored to SCN 3849360 skipping datafile 5; already restored to SCN 3849365 skipping datafile 6; already restored to SCN 3849372 skipping datafile 7; already restored to SCN 3849382 skipping datafile 8; already restored to SCN 3849389 skipping datafile 9; already restored to SCN 3849395 skipping datafile 10; already restored to SCN 3849398 restore not done; all files read only, offline, or already restored Finished restore at 03-MAY-2017 13:22:12
RMAN is clever enough: the data files are ok, according to their header and it skipped the restore. But you know that they are not ok, because some blocks are marked as corrupt because of nologging operations. Then what to do? There is a FORCE option in the restore command. But you probably don’t need it. If you get the previous message, it means that the datafiles are synchronized, which means that the APPLY is running. And, anyway, in order to restore you need to stop the APPLY.
DGMGRL> edit database orclb set state=apply-off;
Of course, once you stopped the apply, you run your RESTORE DATABASE FORCE. But you probably don’t need it. Now, the datafiles are stale and RMAN will not skip them even without the FORCE keyword.
RMAN> restore database from service 'MYDB_SITE1_dgmgrl'; Starting restore at 03-MAY-2017 13:22:37 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service MYDB_SITE1_dgmgrl channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /media/raid-db/MYDB/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service MYDB_SITE1_dgmgrl ... channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 03-MAY-2017 13:25:30 RMAN> exit
Don’t forget to re-enable the Data Guard Apply at the end.
When you see all datafiles skipped, that probably means that you didn’t stop the APPLY. With APPLY stopped, and you probably stop it before the import as you plan to restore the standby later, then you probably don’t need the FORCE command. However, I’ll always recommend using the FORCE in this case because RMAN will skip the files without looking at the unlogged blocks. Imagine that you put a tablespace in read-only after the non-logged import but before stopping the apply. Then this one will be skipped.
But will this not restore the entire datafile containing the unrecoverable blocks?
It could maybe be better to use the “recover from service” to roll forward the standby. But you would also need to restore a new standby controlfile, which (if I remember correctly) could give some problems with omf when not having a rman catalog
Hi Freek, yes the goal is to restore the datafiles because you know there are some blocks marked corrupt. If you don’t sue FORCE then RMAN compares the SCN and may decide there is nothing to restore. Same with RECOVER, it will ship an incremental backup starting at the SCN from the datafiles headers. I’ve not tested (yet) whether we can use RECOVER FROM CORRUPTION LIST for that. In 12c there is the RECOVER NONLOGGED
Ah yes, of course The redo is already send to the standby in this cause.
You would need to stop the apply before the nologging operation for the recover method to work
Great, great help Franck. Here are the issues I ran into in case anyone else has the same:
1. RMAN restore from service requires a sqlnet connection (at least on 12.1.0.2) so you need to connect to target (standby) as rman sys/[password]@standby_db on standby host server. I had to specify the sys password on the command line as well as it wouldn’t connect to the source db correctly.
2. Verify orapwd files are the same between primary and standby (one of our dbas had added himself to sysdba privs on primary and the password file was out of sync). You can use md5sum on source and target to verify the pwd files are identical.
This error is returned:
3. On a busy system the standby db has to be in mount mode, otherwise you get this error during the restore (note: 1987763.1)
After fixing the above it seems to be restoring correctly.