Skip to Main Content
  • Questions

  • Switch from nologging to force logging

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sysman.

Asked: February 28, 2018 - 3:52 pm UTC

Last updated: March 03, 2018 - 5:21 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I have inherited a 12c database with data guard and was shocked to see that the database was set to no logging on the primary and standby. I checked the scn number and they were roughly matching on both primary and standby. This has been this way for about a year. I have since set both to force logging but, I see in the the v$datafile view that there is still a unrecoverable change# and time 26-FEB-18. My question is, I see the unrecoverable change number on the primary is 155905528. Does this mean that I have to do an incremental backup from 155905528 and restore it on the standby database? BTW, this database is being regularly being backed up.

Thanks!

and Connor said...

There's a couple of things you can do to verify the recovery that you (will most likely) need:

1) Check FIRST_NONLOGGED_SCN/FIRST_NONLOGGED_TIME on v$datafile on the standby. Non-zero means you've got work to do
2) Run 'validate database' on the standby. This will populate V$NONLOGGED_BLOCK

On the assumption that you have some fixup work to do, the easiest way is just connecting to the standby and running:

RECOVER DATABASE NONLOGGED BLOCK;

If that doesn't work (ie, you are not on the right version of 12c), then its the usual backup/recover of the datafiles impacted. MOS notes 958181.1/1987763.1 covers those steps in detail, but tl;dr

alter database recover managed standby database cancel;
alter database datafile n offline for drop;
alter database recover managed standby database using current logfile disconnect;
backup incremental from scn ssss datafile n format '/bkp/...' tag 'SBY';


catalog start with '/bkp';
alter database recover managed standby database cancel;
alter database datafile n online;
recover datafile n noredo;
delete backup tag 'SBY';
alter database recover managed standby database using current logfile disconnect;

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sysman

sysman, March 01, 2018 - 3:39 pm UTC

Awesome information. Thanks Connor!

So since I was waiting for a reply I already restored just the datafile that was reporting the unrecoverable_change# from the primary database to the standby. I also restored a new standby control file. Now, both nonrecoverable_change#'s are matching the same both on the primary and standby database and it appears that the mrpo is working correclty:

From Primary:
select file#, name, unrecoverable_change#, UNRECOVERABLE_TIME from v$datafile;
7 /u01/shared_data/oradata/TEST/datafile/o1_mf_test__86067255476_.dbf 271460028 27-FEB-18

From Standby:
select file#, name, unrecoverable_change#, UNRECOVERABLE_TIME from v$datafile;
7 /u01/shared_data/oradata/TEST/datafile/o1_mf_test__86067255476_.dbf 271460028 27-FEB-18

Since I don't have a mos account I will not be able to view that document. My question is, and I am assuming, if the unrecoverable_change#'s both on the primary and the standby database are matching then the standby database is good-to-go even though it may seem to be misleading that there is still a non zero value for the unrecoverable_change# in the control file which does not reset to zero...? Are there any views to verify the integrity of the standby database? I already used RMAN validate database.

Thanks!
Connor McDonald
March 03, 2018 - 5:21 am UTC

If V$NONLOGGED_BLOCK is empty I'd contend you're good.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database