DBA Hub

📋Steps in this guide1/1

Change Protection Mode in Oracle 12C Dataguard

Primary SideSQL> select status,instance_name,database_role,protection_mode from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE

oracle configurationintermediate
by OracleDba
13 views
1

Overview

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
Primary Side
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   prim          PRIMARY       MAXIMUM PERFORMANCE
Standby Side
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   stand         STANDBY       MAXIMUM PERFORMANCE
Verify Online logfile on Primary
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ------------------
1      50
2      50
3      50
Verify Online logfile on Standby
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
------ ----------------
1      50
3      50
2      50
Verify Standby logfile on Primary and Standby
SQL> select group#,bytes/1024/1024 from v$standby_log;
no rows selected

Add Standby logfile on Primary
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.
Cancel the MRP process on Standby.
SQL> alter database recover managed standby database cancel;
Database altered.
Add Standby logfile on Standby
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.
Verify standby logfile on primary
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4      50
5      50
6      50
7      50
Verify standby logfile on Standby
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
------ ---------------
4      50
5      50
6      50
7      50
Check if your primary and standby is in sync.Your databases should be in Sync
Primary Side
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1       40
Standby Side
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- --------------
1 40
Primary Side
Change Log_arc_dest_2 parameter to use Sync mode.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string service=stand valid_for=(online_logfiles,primary_role) db_unique_name=stand
SQL> alter system set log_archive_dest_2='service=stand LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=stand';
System altered.
Shutdown Primary database and mount

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>
Change protection mode to maximize availability or maximum protection
SQL> alter database set standby database to maximize availability;
Database altered.
Open the Primary databases
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN   prim        PRIMARY       MAXIMUM AVAILABILITY

Comments (0)

Please to add comments

No comments yet. Be the first to comment!