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.