Infrastructure at your Service

Oracle Team

12c nologging and Data Guard

By Franck Pachot

.
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

Nologging Data Pump

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:y
Don’t forget to re-enable force_logging at the end and to re-synchronize the standby.

nonlogged (aka unrecoverable)

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 14

and 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.

So what?

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.

4 Comments

  • Freek says:

    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

      • Freek says:

        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

  • Chris Taylor says:

    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:

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 04/09/2018 09:47:31
    ORA-17629: Cannot connect to the remote database server
    ORA-17627: ORA-01017: invalid username/password; logon denied
    ORA-17629: Cannot connect to the remote database server

    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)

    ORA-19849: error while reading backup piece from service dwp01_mia
    ORA-19573: cannot obtain exclusive enqueue for datafile 1
    ORA-19660: some files in the backup set could not be verified
    ORA-19661: datafile 1 could not be verified
    ORA-19849: error while reading backup piece from service dwp01_mia
    ORA-19573: cannot obtain exclusive enqueue for datafile 1

    After fixing the above it seems to be restoring correctly.

Leave a Reply

Oracle Team
Oracle Team