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
17 comments:
Thanks for the Demonstration.
Yes. I was very much sure about this but was doubting b'coz i read somewhere that a logswitch on Primary only will apply data to STANDBY.
but anyways Thanks for clearing my doubt.
Regards!
Perfect demonstration ...!! Bingo.
I would like to know the demo on the Data Guard Setup on Single Independent System...
Looking forward for the postings...
Pavan Kumar N
pavan i think there is no different steps for DG implementation within the same node or diffrent node,excpet in 9i there you have to specify lock_name_space parameter when you want to open a copy database with the same DB_NAME on the same machine as the source database.
but in 10g new DB_UNIQUE_NAME attribute is introduced to clearly identify the relationship between a primary and standby databases.
I think there is no need to put that demo here its same :).
perfect thanks brother , can we have some blogs on RAC please
sure i will , but pray for that i will get some free time
Dear khurram ,
we r fetching problem in switchover in oracle 10g of os level AIX and unix.
i wanted to know for the switchover what changes we suppose to do on primary and standby database including os level changes i.e structure of database and os level should be same for every parameter or something else changes we need because we have done 3 times switchover activity and it has failed due to scn number was not matching or some other problem.
so please advice us as earlieast if we need to do some changes.
please give us solution on above id or call:9869220487
Mr Khurram;
Suppose I have standby redologs in standy database, after some transaction in production, production crashed,Now standby redolog is not full (I have half data in it)
Do I need to force logswitch to apply the data in standby or does oracle apply it automatically prior to opening the database?
@pascal
if you are not using real time recovery then you will to take an effect for MRP by using log switch at standby database.
If you are using real time recovery then needn't to switch log file , as i already stated that this demonstration for not real time recovery mode standby database.
sorry ravind
i was damn busy with my office project and could not update my blog reviewer.
as you have error like
--------------------------------------
scn number was not matching or some other problem.
so please advice us as earliest if we need to do some changes.
-------------------------------------
it seems to me that do you have standby redo logs at both end primary and standby database.
further what protection mode do you have in standby database?
please paset the exact scenario with error.
Mr Khurram.
suppose I am using real time recovery.
If I perform failover, Does oracle automatically performs logswith to apply the data in redologs?
what do you mean by logswith???
Respected Khurram,
What I meant was:
Suppose I am using maximum performace.
In the primary database, the redolog is half full.
I need to perform failover.
I dont wanna lose data in this scenerio.
If I copy redologs from primay to standby,Does finish recovery automatically apply the half fulled redologs?
Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!
This is really interesting demo and useful as well.
i just want to make things more interesting here and i dont know if you face this scenario before or not.
have you ever heard abuot Cascading standby?
let's say there is one primary database, 1 stnadby database and other stnadby database.
now if the production ship archives to standby database like you wrote demo and the standby database 1 also ship archives to standby 2, how the MPR act here, and how the log switch happens in teh standby.
I create architecture like that, and i found that the MRP stuck in the middle, and the RFS is IDLE all the time in the standby 1 and 2.
the log switch happens ok when i shutdown standby 2, then the MRP and RFS works just fine on Standby1.
if you want more info, send me email and i will show you the results.
Thanks again for this nice DEMO.
Regards,
Hello, that is a great demo. Just as what you have configured, data from the primary server is shipped to standby but not applied until a log switch happens. I am wondering if the primary server is suddenly explosed, will a log switch triggered at the priamry server? If not can you manually issue the log switch command on the standby server to apply the data?
-----------------------------------
Hello, that is a great demo. Just as what you have configured, data from the primary server is shipped to standby but not applied until a log switch happens. I am wondering if the primary server is suddenly explosed, will a log switch triggered at the priamry server? If not can you manually issue the log switch command on the standby server to apply the data?
-----------------------------------
there is two recovery mode at standby database.
1) recover database using online log file disconnect from session.
2) another one is recover database
disonnect from session.
First one implies to recover standby database using current log file which can be shipped from primary database via in the form of archivelog or online redo vectors.
Khurram
-----------------------------------
Hello, that is a great demo. Just as what you have configured, data from the primary server is shipped to standby but not applied until a log switch happens. I am wondering if the primary server is suddenly explosed, will a log switch triggered at the priamry server? If not can you manually issue the log switch command on the standby server to apply the data?
-----------------------------------
there is two recovery mode at standby database.
1) recover database using online log file disconnect from session.
2) another one is recover database
disonnect from session.
First one implies to recover standby database using current log file which can be shipped from primary database via in the form of archivelog or online redo vectors.
Khurram
Post a Comment