I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Saturday, October 11, 2008

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

17 comments:

Anonymous said...

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!

Anonymous said...

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

Khurram Siddiqui said...

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 :).

Anonymous said...

perfect thanks brother , can we have some blogs on RAC please

Khurram Siddiqui said...

sure i will , but pray for that i will get some free time

ravindrakumar said...

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

Pascal said...

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?

Khurram Siddiqui said...

@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.

Khurram Siddiqui said...

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.

Pascal said...

Mr Khurram.
suppose I am using real time recovery.
If I perform failover, Does oracle automatically performs logswith to apply the data in redologs?

Khurram Siddiqui said...

what do you mean by logswith???

Pascal said...

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?

Anonymous said...

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!

sadiq said...

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,

Anonymous said...

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?

Khurram Siddiqui said...

-----------------------------------
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

Khurram Siddiqui said...

-----------------------------------
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

Followers

About Me

My photo
Melbourne, Victoria, Australia