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
