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;