...
| Note |
|---|
You should never attempt switchover with nollogging corruption detected in your standby database as it may result in ORA-26040: Data block was loaded using the NOLOGGING option and in worst case scenario you can end up with two corrupted databases (primary and standby database will contain different data). |
False Positive Nologging transactions
There are cases when nologging transactions have impossible date or SCN. For example date in the future:
| Code Block |
|---|
SQL> select file#,
cast(to_char(first_nonlogged_scn, 'FM9999999999999999999999999') as varchar2(30)),
to_char(first_nonlogged_time, 'YYYY-MM-DD:HH24:MI:SS')
from v$datafile where first_nonlogged_scn > 0
/
2 3 4 5
FILE# CAST(TO_CHAR(FIRST_NONLOGGED_S TO_CHAR(FIRST_NONLO
---------- ------------------------------ -------------------
6 2967235864 2080-04-27:00:06:32 |
or date way back in the past:
| Code Block |
|---|
First Nonlogged Scn
139698638221456
First Nonlogged Time
1999-02-10:09:25:56 |
These false positive cases are typical for 19c PDB$SEED datafiles.
In such cases, ignore the contents of v$datafile and instead verify the non-logging corruption only via v$nonlogged_block:
| Code Block |
|---|
RMAN> validate datafile 12 nonlogged block;
RMAN> select file#,block#, blocks ,object# from v$nonlogged_block; |
Or completely ignore the warnings. For example if nologging transaction has date of 2016 and the standby database was created from the scratch in 2024, it’s impossible to have any such nologging transactions on standby. If you are not sure, contact Dbvisit support.
3. Fixing nologging transaction corruption
4. Considerations
4.1 Database is in archivelog modeOnce you detect nologging transactions, you must ensure that no nologging transactions can happen on your primary database anymore. This is done by setting your primary database into force logging mode:
| Code Block |
|---|
SQL> archivealter database logforce listlogging; Database log mode Archive Mode ..altered. |
Restart is not required. We recommend to monitor closely archivelog generation rate afterwards, as setting the force logging mode can generate extra amount of archivelogs.
| Info |
|---|
Note that anybody with DBA privileges can easily change the force logging mode afterwards without your knowledge - for example application user with DBA rights. You should monitor database alert log to ensure force logging mode is preserved on your production database. |
After setting the primary database to force logging mode, you can fix the nologging transactions. This can be done in several ways:
Running RMAN incremental backup SYNC (synchronize standby):
Refreshing affected datafile:
In cases when nologging transactions are wide-spread you could even decide to recreate standby database from the scratch
Once the procedure is completed, run again following select on standby:
| Code Block |
|---|
SQL> select file#,
first_nonlogged_scn, to_char(first_nonlogged_time, 'YYYY-MM-DD:HH24:MI:SS')
from v$datafile where first_nonlogged_scn > 0
/ |
This select should return no rows.