0

I would like to know your opinion and experience in a specific setup I want to apply to a Production Database that is configured in Oracle Data Guard with one Standby. It is not an active data guard environment. Since the beginning we have been using FORCE LOGGING, as recommended, but I started to realize that there are many operatives in nologging mode which are creating a lot of redo and archive files, with the performance downtime associated.

Given that this Data Guard has no automatic failover and that the RTO is of 4 hours ( database is not very big either ) I came up with a document in Oracle Support regarding scenarios where its possible to disable force logging on the primary ( 12.2 onwards )

How to Resolve ORA-1578 /ORA-26040 Nologging operation in 12.2 on Standby database (Doc ID 2378500.1)

My scenario is as follows:

  • There is a process running every five minutes that refreshes a set of tables using CTAS in nologging.
  • As a consequence, the process is generation huge amounts of redo and archive logs.
  • Those tables which are recreated every five minutes are stored in an independent tablespace where no other schema contains any segment.

My idea is to disable FORCE LOGGING on Primary, and in case of failover to the standby, which is not automatic, then perform

RMAN>RECOVER DATABASE NONLOGGED BLOCK ;

Do you consider something wrong with this approach ? Any other point should I be aware of ?

Thank you in advance for your suggestions and ideas.

Improve this question
3
  • 1
    You could probably solve multiple problems at once by making this table refresh process incremental instead of a full load - improves the performance of this business process, DG won't have to deal with massive redo spikes, your table remains online all the time, DG remains working. It's probably worthy of another question but I would recommend you start by looking at the requirements for fast refresh materialized views.
    – Andrew Sayer
    Jun 24, 2021 at 12:01
  • I tried the solution with RTMV and it did not work. My point is that the document states that in some cases when you don't need to make a failover, you can revover by rman the blocks marked with nologging corruption ( starting in 12.2 )
    – Roberto Hernandez
    Jun 24, 2021 at 16:18
  • @AndrewSayer, I also tried incremental refresh and it takes more than a full refresh. Assuming the lost of primary, I can open the standby and recreate the datafile/tablespace with the nologging corruption, as the data there is coming from other datafile/tablespace.
    – Roberto Hernandez
    Jun 24, 2021 at 18:34

1 Answer 1

Reset to default
1

Maybe you missed this part from the referenced note:

This command fetches the required blocks from Primary.

Meaning if you have to perform a failover becasue the primary becomes inaccesible, this will not work.

Improve this answer
4
  • From the official note: This Article is Useful for Customer who don't want the Primary database to be in Force logging mode in Primary- Standby configuration and would want to have to their application perform certain No logging operation to have some performance benefit at the same time ensure that Standby database doesn't get affected By No logging Corruption Message.
    – Roberto Hernandez
    Jun 24, 2021 at 16:17
  • 12.2 new Feature allows us to recover such no logging operations using Rman command ,without having to restore a fresh copy of datafile from primary or Enabling Force logging on Primary database. I don't want to do a failover, as I have a RTO of 4 hours and there is no requirement to have it
    – Roberto Hernandez
    Jun 24, 2021 at 16:19
  • @RobertoHernandez Then once again: this feature relies on the accessibility of the datafile on the primary database. It can copy blocks from the primary database instead of whole datafiles, that is all to it. If you lose the primary database, you lose the data as well because it can not be recovered in the standby database without the primary database as these changes are not included in the archivelogs, they are included only in the datafile backups, which you can not rely on, given the frequency of these changes. If this is acceptable, because the data can be reproduced, then go ahead.
    – Balazs Papp
    Jun 24, 2021 at 16:41
  • Thank you for your comments. Indeed, I can recover the data from the other datafiles, as the data that is refreshed every five minutes is generated from other tablespace. Assuming this, I believe it might work. In case of primary disaster, I would need to start the standby and recreate the datafile with the nologging corruption.
    – Roberto Hernandez
    Jun 24, 2021 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.