DBA Hub

📋Steps in this guide1/7

How to Enable Real Time Apply

How to Enable Real Time Apply 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 … Continue reading Enable Real Time Apply →

oracle clusteringintermediate
by OracleDba
14 views
1

Overview

How to Enable Real Time Apply 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. 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. Pre-requisites: Configure Physical Standby database. Click here Pre-requisites:
2

Section 2

here 0. Enviroment 1. Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Source:
Platform
: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version:
Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
Flashback
: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
Target:
Platform
: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version
: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
Flashback:
Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
3

Section 3

2. Verify Real-Tme Apply Status on Standby MANAGED 3. Try to enable real-time apply

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL>
select name, open_mode, database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=w5005prg LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=w5005p
                                                 rg

SQL>
select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO
MANAGED
SQL>
Note: If it is not in Real-Time apply mode then SRL (Standby Redo Logs) will not be in use.
SRL=NO
RECOVERY_MODE = Managed : It means Redo-Apply only, Not Real Time Aapply.
4

Section 4

3. Try to enable real-time apply 4. Add standby redo logs On Primary

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
SQL>

/*
standby redo logs are populated with redo information as fast as the primary redo logs, 
rather than waiting for the redo log to be archived and shipped to the standby.  
This results in faster switchover and failover times because the standby redo log files have been applied 
already to the standby database by the time the failover or switchover begins.
Oracle recommends the below formula to calculate the number of Standby redo logs file as 
(maximum number of logfiles for each thread + 1) * maximum number of threads
*/

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE
PRIMARY
SQL> col member for a50
SQL> set lines 180
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA/w5005pr/onlinelog/group_3.263.900456463      NO
         2         ONLINE  +DATA/w5005pr/onlinelog/group_2.266.900456463      NO
         1         ONLINE  +DATA/w5005pr/onlinelog/group_1.267.900456461      NO

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

no rows selected

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

no rows selected

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 +DATA/w5005pr/onlinelog/group_4.259.900718771        52428800
         5 +DATA/w5005pr/onlinelog/group_5.258.900718779        52428800
         6 +DATA/w5005pr/onlinelog/group_6.257.900718785        52428800
         7 +DATA/w5005pr/onlinelog/group_7.256.900718791        52428800

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED

SQL>
5

Section 5

On Standby 5. Enable Real-Time Apply on Standby 6. Quick Testing

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SQL>
alter database add standby logfile group 4 size 50M;
alter database add standby logfile group 4 size 50M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL>
alter database recover managed standby database cancel;
Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL>

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>
SQL>
alter database recover managed standby database disconnect from session using current logfile;
Database altered.

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_1             VALID     LOCAL          NO
MANAGED REAL TIME APPLY
SQL>

On Primary

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2             VALID     PHYSICAL       YES
MANAGED REAL TIME APPLY
SQL>
6

Section 6

6. Quick Testing On Primary On Standby

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> set time on
00:03:15 SQL> create table test (name varchar2(30));

Table created.


00:03:24 SQL> insert into test values ('Rajasekhar Amudala');

1 row created.

00:05:22 SQL>  COMMIT;

Commit complete.

00:05:23 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:24 SQL>
7

Section 7

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
SQL> set time on
00:05:30 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:21 SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!