Log switch trigger for recovery at standby database

I have already posted blog how standby redo apply and from this thread i got a comment by soemone else from within that thread,i feel better i should post another blog in reply that comment rather replying for that comments on that thread. Comments are

Hi, So does that mean you have only partial records at STANDBY when you cancel reovery process by not allowing complete logs to be applied? you have setting of "log_archive_dest_2 string SERVICE=stby LGWR SYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)" that means it'll write directly to STANDBY DB online logs.So as soon as logswitch happens at Primary Db, it should happen on Standby DB (not necessory) as well.so every record should be applied to STANDBY DB.So why in your case Partial records are applied? just because you have canceld recovery process in middle?

Yes due to canceling recovery process at standby cause to apply partial records partial committed records those which were in standby redo log and applied at standby DB after log switched but those which were in standby redo log but not applied to standby database cause it was in redo log and didn’t applied due to not Log switches occurring). Redo logs applied to a physical standby database when a log switch occurs, they don’t Applied as they arrive on the standby site. As MRP Apply (MRP process) or SQL Apply (LSP process) applies the redo data to the Standby database using standby archived logs. See figure

Log switch trigger MRP for applying the redo data to the standby database using Archived standby log. If you just enter suffice amount of data which does not cause Log switch at primary database then this redo propagated at standby redoes log files But will not applied until or unless you or itself log switch happened at primary Database which in turn also switched standby log at standby database. Before going to demonstrate I would think better to answer yours question that yes It’s necessary a log switch at physical standby trigger to log switch at standby redo Log file.

For that you can see demonstration as i am doing within my lap top. Remember in this demo real-time apply is not enabled, which data Guard recovers redo data directly from the  current standby redo log file as it is being filled up by the RFS process.

At standby

SQL> show user
USER is "SYS"

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session
  2  /

Database altered.

SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PHYSICAL STANDBY TO PRIMARY           MAXIMUM PERFORMANCE  YES

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1         31          0          0
RFS       UNKNOWN  ATTACHED              0          0          0          0

At primary

SQL> show user
USER is "SYS"

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CLOSING               1         30      16385       1481
ARCH      ARCH     CLOSING               1         30      16385       1481


SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PRIMARY          SESSIONS ACTIVE      MAXIMUM PERFORMANCE  YES


SQL> create table test (a number)
  2  /

Table created.

SQL> begin
  2    for i in 1..10000  loop
  3      insert into lalo values (i);
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         31       4097       1256
MRP0      N/A      WAIT_FOR_LOG          1         32          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         32       9770        100
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP still waiting (wait_for_log) for sequence 32 for recovery while RFS is
writing in progress via LGWR for 32 unless you dont make log switch at primary
database then MRP will not apply redo to standby database.


Come at Primary database and make log switch 

Come at primary database

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33          1          1
RFS       UNKNOWN  RECEIVING             0          0          0          0

See MRP now waiting (wait_for_log) for sequence 33 for recovery while RFS is writing in progress via LGWR for sequence 33 unless you don’t make log switch at primary Database then MRP will not apply redo to standby database.

Come at primary database

Lets see if i create that table that may cause at primary database to make log switch 

SQL> create table test_1 (a number)
  2  /

Table created.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33        265          2
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

No, no log switch occur at primary database still MRP waiting (wait_for_log) For sequence 33 for recovery while RFS is writing in progress via LGWR for sequence 33, unless you don’t make log switch implicitly/explicitly at primary database then MRP will not apply redo to standby database.

Let’s see if I cancel recovery and see table test_1 redo applied to physical standby Database

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
ERROR:
ORA-04043: object test_1 does not exist

Nop..

Put again it in recovery mode

SQL> alter database recover managed standby database disconnect from session
  2  /

Database altered.

Come at Primary database and make log switch 

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         33          1        455
MRP0      N/A      WAIT_FOR_LOG          1         34          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         34          7          3
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP now waiting (wait_for_log) for sequence 34 for recovery while RFS is writing in progress via LGWR for sequence 34, sequence 33 has been applied it applied redo via MRP so fast which you cannot see right now but you can observe if you make huge transaction at primary database and monitor physical standby database you will see MRP0 with status APPLYING_LOG.

Cancel recovery and see test_1 table redo applied via MRP after making log switch

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER