Real-Time Apply in Oracle Data Guard 10g

To use real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file. This standby logfile is written to standby log file on standby server. There is no loss of any committed transaction whatsoever in Real-Time Apply scenario.
1. Add standby logfiles on the primary server, add standby logfiles on the standby server. Ensure that the size of the standby logfile is same as online redolog file. And generally it is good practice to create standbylog files as same as number of groups. For example if your no. of online redo log group is 3 then you create 3 standby log groups.
2. alter system set LOG_ARCHIVE_DEST_2=’SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby’
3. on Primary
select * from v$standby_log;
on Standby
Select * from v$standby_log;
Check for “ACTIVE” in the status
Converting To Real time
Real-time apply : When real-time apply is enabled, the log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. In this example we are going to use LGWR on the primary for redo transport just to prove that a committed record on the primary without switching a log will show up on the standby. However real-time apply will work with both LGWR and ARCH using SRL’s.
* Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC
log_archive_dest_2=’SERVICE=to_standby LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby ‘
* shutdown and startup the primary or if done dynamically switch a log file
* You will see the following message in the alert log
*LGWR: Setting ‘active’ archival for destination LOG_ARCHIVE_DEST_2
Enable Real Time
* On the standby cancel out of the current managed recovery
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
* Place it back in recovery with Real time apply
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
Test Case
Create a table on Primary.
SQL>  create table test
2   ( name varchar2(30));
Table created.
Now Insert some records and commit.
SQL>  insert into test
2  values
3  (‘DreamzZ’);
1 row created.
SQL> commit;
Commit complete.
Now on Standby Run these commands.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>ALTER DATABASE OPEN READ ONLY;
Database altered.
Now check the table and record which you inserted.
SQL> select * from test;
NAME
——————————
DreamzZ
We can our table and record without any log switch.
After Checking your Real Time work Dont Forget to put your database back in recover mode 😉
Place the standby back in managed recover mode
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT;
Database altered.
* This will take the standby directly from read only mode and place it in managed recovery mode.
**********************
QUICK TESTING
**********************
At Standby – Put into real-time apply mode
__________
1. alter database recover managed standby database cancel;
2. alter database recover managed standby database using current logfile disconnect;
At Primary – Do inserts on test table
___________
create table test (name varchar2(30));
insert into table test(‘John’);
commit;
At Standby – Checking if txn came or not without log switching
__________
alter database recover managed standby database cancel;
alter database open read only;
#connect as the user if the table was not created under SYS user
select * from test;
#you should see the value without the log switch. This is due to standby redo log files used
Now put the database back into managed recovery mode. Do not forget this Step.
alter database recover managed standby database using current logfile diconnect;

Category: Database

Tags:

8 comments

Leave a Reply

Article by: Shadab Mohammad