Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arangaperumal.

Asked: November 18, 2016 - 12:49 am UTC

Last updated: September 01, 2022 - 4:15 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

My Prod environments is like this.

Three Node RAC, Active Data guard enabled.

There is a partitioned table, month based range partition, every month data will be in one partition. Each partition size is 180G to 200G.

I have another table for archival purpose. We will move 3 partition to archival table from main table every quarter.

After moving a data to archival table, the index rebuild will take longer time.if drop and recreate index will also be like that and take very longer time.This archival table is also used by application.so index is must. It is global index.

To move a data from main to archival table and index rebuild on archival, i am afraid to go for NOLOGGING option. If would use this,whay will happen to my SECONDARY db. Will it be sync because no logging and no redo.

My window to do this activity is 6hrs( i can avoid application connection).

What is the best method, kindly advise.


and Connor said...

Typically databases with a DataGuard setup have the 'force logging' mode set, so nologging requests are done with logging anyway. So that will be the first thing you would to check.

You *can* do nologging operations, but once completed, the tablespace/datafiles affected will need to be re-copied to the data guard node(s) because thats the only way the changed data can be seen (because it is not in the redo logs).

See http://docs.oracle.com/database/121/SBYDB/scenarios.htm#SBYDB00920 for the steps on what to do. Does that 6 hrs apply to your active DG database as well ? Because if it does, it might take longer than that to re-copy those datafiles.

Rating

  (3 ratings)

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

Comments

Active Data Guard without force logging

A reader, August 25, 2022 - 5:30 am UTC

Hi Connor,

Just want to take your feed and opinion on the issue/approach on Data Guard without force logging.

We have force_logging enabled in our production database and it has active Standby database for supporting read-only queries.

We have many materialized view refresh running every 15 min which is complete refresh with direct loads and it generates almost 10TB of redo and archive logs per day. We wanted to look at option of disabling FORCE_LOGGING on Primary but the problem is active data guard will go out of sync due to non-logged direct path operations.

Could you please your opinion/thoughts on this approach given our environment is Active Standby ?

1) in 12.2. we have RMAN command

Oracle document Note: 2378500.1

RMAN> RECOVER DATABASE NONLOGGED BLOCK;


Given we have multi TB of redo being generated, how fast the standby will catch with "NONLOGGED BLOCK" command..
Does it go serially or we have any Parallel operation available with this command ?

Thank you for your feedback/suggestions/better approach!



Connor McDonald
August 26, 2022 - 5:30 am UTC

Check out this video which might be useful in showing some options.

You need to be on 19c


Active DataGuard without force logging

A reader, August 31, 2022 - 3:05 am UTC

Hi Connor,

Thanks a lot for sharing the video on 'best effort standby" available in 19c.

I have few questions. Can you please clarify :

1) Given our environment have slower network bandwidth between Production site and Standby Site, mostly we will go with "STANDBY NOLOGGING for load performance" option.

Is this one time execution "alter database standby nologging" on Primary database ?

Do we need to execute the same command on Active Data Guard database as well ?

2) When Data Guard role switch happens between primary and standby, Do we need to re-execute it or it will stay for ever after 1st time execution ?

3) Is my understanding correct ? that, after the command once executed Oracle automatically manages fetching non-logged operation/direct writes blocks from Primary over to Standby and sync it up. No manual intervention needed, correct ?

4) What happens to user queries (read-only query)on Active Data Guard environment when non-logged recovery operations is performed by oracle under the covers. ? Will it throw any errors ?


Thanks much!

Connor McDonald
August 31, 2022 - 6:06 am UTC

1) Just on the primary, and hust once
2) Stays forever to my knowledge
3) Correct
4) Our normal read consistency model applies (just like if you ran a query on your primary node whilst nologging operations were occuring - you don't see them).

In all cases, I'd be testing thoroughly to ensure you don't hit any edge cases.

Note - i should have also mentioned some license implications here.

https://docs.oracle.com/en/database/oracle/oracle-database/21/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87

Active data guard nologging

A reader, September 01, 2022 - 12:24 am UTC

Hi Connor,

Thank you for the quick response. It’s very helpful.
Connor McDonald
September 01, 2022 - 4:15 am UTC

glad to help
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.