One of the statements that is not well known, but introduced in Oracle 11gR2 is the flush of the redo. For Oracle Data Guard a flush of the redo in case of a failover can be very useful. If this statement is successful executed the last redo is applied on the standby and there will be no data loss.
To execute the statement the Redo apply on standby must be active and the primary database where you execute the statement must be mounted and not open. Default confirm apply is part of the statement and will wait until all the redo is applied.
If the primary database is open read/write you will receive an error message:
SQL> alter system flush redo to ‘proda’;
alter system flush redo to ‘proda’
*
ERROR at line 1:
ORA-16445: Database has to be mounted and cannot be open
Restart the database in mount mode and execute the statement.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1345604 bytes
Variable Size 444598204 bytes
Database Buffers 4194304 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> ALTER SYSTEM FLUSH REDO to ‘PRODA’;
System altered.
Now you can execute the failover statement using sqlplus or the data guard broker.
In the alert.log of the primary database you will notice the End-of-Redo is send, this is indicating no data is lost and a failover can be executed.
ALTER SYSTEM FLUSH REDO TO ‘PRODA’ CONFIRM APPLY
ALTER SYSTEM FLUSH REDO TO PRODA CONFIRM APPLY [Process Id: 27807] (proda)
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=24, OS id=27885
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARC1 started with pid=25, OS id=27887
ARC2 started with pid=26, OS id=27889
ARC3 started with pid=27, OS id=27891
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the ‘no FAL’ ARCH
ARC1: Becoming the ‘no SRL’ ARCH
Flush redo: No wait for non-current ORLs to be archived
Waiting for all FAL entries to be archived…
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized…
ARC2: Becoming the heartbeat ARCH
2012-02-07 10:39:44.948000 +01:00
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Active, synchronized flush redo target has been identified
Managed Real Time Apply recovery running at physical standby ‘LOG_ARCHIVE_DEST_2′
Flush End-Of-Redo Log thread 1 sequence 607 has been fixed
Flush Redo: Primary highest seen SCN set to 0×0.0x9d05c1
ARCH: Noswitch archival of thread 1, sequence 607
ARCH: End-Of-Redo Branch archival of thread 1 sequence 607
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 607 for destination LOG_ARCHIVE_DEST_2
Flush End-Of-Redo Log thread 1 sequence 607
Archived Log entry 1038 added for thread 1 sequence 607 ID 0x7943e1d0 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will wait for PRODA standby to have applied all redo
Final check for a target standby that has recovered all redo. Check will be made a few times.
LOG_ARCHIVE_DEST_2 is a potential flush redo target
LOG_ARCHIVE_DEST_2 has also applied all redo from primary
Active, synchronized target has been identified that has applied all the redo from the primary.
Flush Redo: Primary redo moved to standby
Architecture Cache Fusion