Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
After installing Data Guard, we may need to make some adjustments for it to work effectively.
For example,
Let’s assume that we have a network bottleneck, we can solve this problem with Redo Compression when we cannot overcome this bottleneck with the SDU, Send Buffer Size and Redo Buffer Size parameters we entered in Listener and Tns.
Or, if we have a problem accessing the location where we send the Redos, we would like to determine when the redo transmission will be triggered again.
Or, let’s assume that the number of APPLIER or PREPARER processes is not sufficient in the Logical Standby Database. In this case, we need to increase them.
Here are the optimizations that can be made in this and similar cases.
A.REOPENSECS
It determines how many seconds after the ARCHIVER process will try to send redo’s to that location in failed Redo sends.
In Default for the broker, this value is 300 seconds.
It corresponds to the REOPEN attribute in the LOG_ARCHIVE_DEST_n parameter.
The parameter is changed as follows.
1. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'ReOpenSecs'; ReopenSecs = '300' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=1 reopen=300 d b_unique_name="standby" net_ti meout=30, valid_for=(all_logfi les,primary_role) |
2. We change the parameter value.
1 2 | DGMGRL> edit database standby set property ReOpenSecs=600; Property "reopensecs" updated |
3. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'ReOpenSecs'; ReopenSecs = '600' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=1 reopen=600 d b_unique_name="standby" net_ti meout=30, valid_for=(all_logfi les,primary_role) |
B.NETTIMEOUT
It is the second value that LGWR will wait for the commit on the Primary side so that a transaction coming to the Primary Database is written to the Standby Redo Logs on the Standby side.
The broker’s default value is 30 seconds.
Corresponds to the NET_TIMEOUNT attribute in the LOG_ARCHIVE_DEST_n parameter.
The parameter is changed as follows.
1. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'NetTimeout'; NetTimeout = '30' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=1 reopen=600 d b_unique_name="standby" net_ti meout=30, valid_for=(all_logfi les,primary_role) |
2. We change the parameter value.
1 2 | DGMGRL> edit database standby set property NetTimeout=20; Property "nettimeout" updated |
3. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'NetTimeout'; NetTimeout = '20' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=1 reopen=600 d b_unique_name="standby" net_ti meout=20, valid_for=(all_logfi les,primary_role) |
C.MAXCONNECTIONS
It determines how many archiver processes will send a single archive redo log file to the Standby side in parallel at the GAP moment that will occur on the standby side.
Since Redo Transfer Rate will increase with this parameter, Redos are sent to the Standby side faster.
Default value is 1 and Maximum value is 20.
Corresponds to the MAX_CONNECTIONS attribute in the LOG_ARCHIVE_DEST_n parameter.
The value given in the LOG_ARCHIVE_MAX_PROCESSES initial parameter must be greater than and equal to the value given in MAX_CONNECTIONS. Otherwise, Data Guard chooses the most appropriate number of processes.
We change the parameter as follows.
1. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'MaxConnections'; MaxConnections = '1' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=1 reopen=600 d b_unique_name="standby" net_ti meout=20, valid_for=(all_logfi les,primary_role) |
2. The value of the parameter is changed.
a. First we query the value of log_archive_max_processes.
1 2 3 4 5 | [Primary-1] SQL> show parameter log_archive_max_proce NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_max_processes integer 8 |
b. The value of the parameter is set so that it does not exceed this value.
1 2 | DGMGRL> edit database standby set property 'MaxConnections'=8; Property "MaxConnections" updated |
3. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'MaxConnections'; MaxConnections = '8' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=8 reopen=600 d b_unique_name="standby" net_ti meout=20, valid_for=(all_logfi les,primary_role) |
D.REDOCOMPRESSION
For SYNC or ASYNC Redo Transport Services, it specifies that Redo is compressed to the Standby side.
Default value is DISABLE because it requires Advance Compression License.
Corresponds to the COMPRESSION attribute in the LOG_ARCHIVE_DEST_n parameter.
The parameter is changed as follows.
1. We query the current value of the parameter.
1 2 | DGMGRL> show database standby 'RedoCompression'; RedoCompression = 'DISABLE' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=disable max_failure=0 max_connections=8 reopen=600 d b_unique_name="standby" net_ti meout=20, valid_for=(all_logfi les,primary_role) |
2. We change the value of the parameter.
1 2 | DGMGRL> edit database standby set property RedoCompression=ENABLE; Property "redocompression" updated |
3. The current value of the parameter is queried.
1 2 | DGMGRL> show database standby 'RedoCompression'; RedoCompression = 'enable' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=enable max_failure=0 m ax_connections=8 reopen=600 db _unique_name="standby" net_tim eout=20, valid_for=(all_logfil es,primary_role) |
E.DELAYMINS
Due to user or application reasons, it may be requested to apply the data to the Standby side after a certain delay.
Zero data loss is guaranteed even if the DELAY parameter is used, as the Redos are sent SYNC to the Standby side in Maximum Protection and Maximum Availability modes.
Default value is 0.
Corresponds to the DELAY attribute in the LOG_ARCHIVE_DEST_n parameter.
We change the parameter as follows.
1. The current values of DELAY, PROTECTION_MODE, and RECOVERY_MODE parameters are queried.
1 2 | DGMGRL> show database standby 'DelayMins'; DelayMins = '0' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=0 optional comp ression=enable max_failure=0 m ax_connections=8 reopen=600 db _unique_name="standby" net_tim eout=20, valid_for=(all_logfil es,primary_role) |
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Physical-2] SQL> select open_mode, database_role, protection_mode from gv$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE [Physical-2] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY |
2. It is seen that the DML made to the Primary with the current values of the parameters is applied instantly on the Standby side.
1 2 3 4 5 6 7 8 9 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK JOBS_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 8 9 | [Physical-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK JOBS_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 8 9 10 11 12 | [Primary-1] SQL> drop table test.JOB_HISTORY_YEDEK; Table dropped. [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 8 | [Physical-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK DEPARTMENTS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
3. While applying Real-Time Redos, the DELAY attribute is set to a certain value.
1 2 3 4 | DGMGRL> edit database standby set property DelayMins=2; Property "delaymins" updated DGMGRL> show database standby 'DelayMins'; DelayMins = '2' |
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service="standby", LGWR ASYNC NOAFFIRM delay=2 optional comp ression=enable max_failure=0 m ax_connections=8 reopen=600 db _unique_name="standby" net_tim eout=20, valid_for=(all_logfil es,primary_role) |
4. A table is DROPed in Primary and it is seen whether it goes from Standby instantly.
a. Existing Archive Log Sequence numbers are queried.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 184 1 109 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 184 1 109 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 184 1 YES 109 2 YES |
b. The table is DROP.
1 2 3 | [Primary-1] SQL> drop table test.LOCATIONS_YEDEK; Table dropped. |
c. It is checked whether the table has been deleted or not.
1 2 3 4 5 6 | [Primary-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK REGIONS_YEDEK LOCATIONS_YEDEK |
It appears that the table is not deleted. The reason is that when changing the value of the parameter from the broker, it disables Real-Time Redo Apply and works with Archived Apply. Therefore, it will be applied as the archive is created.
d. An archive is created.
1 2 3 | [Primary-1] SQL> alter system switch logfile; System altered. |
[Standby-1]—–ALERT_LOG
1 2 3 4 5 6 7 8 9 10 | Tue Jan 31 11:33:48 2017 RFS[26]: Selected log 6 for thread 1 sequence 186 dbid 1769705496 branch 934378910 Tue Jan 31 11:33:48 2017 Archived Log entry 2861 added for thread 1 sequence 185 ID 0x699d7341 dest 1: ARC4: Archive log thread 1 sequence 185 available in 1 minute(s) Tue Jan 31 11:33:50 2017 Media Recovery Delayed for 1 minute(s) (thread 1 sequence 185) Tue Jan 31 11:34:50 2017 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_1_seq_185.1212.934716829 Media Recovery Waiting for thread 2 sequence 110 (in transit) |
e. It is checked whether the created archive is APPLY on the standby side.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 185 1 109 2 |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> select max(sequence#),thread#, applied from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- --------- 185 1 NO 184 1 YES 43 2 NO 109 2 YES |
f. After a certain time, we check whether the table has been dropped or not.
1 2 3 4 5 6 7 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK REGIONS_YEDEK LOCATIONS_YEDEK |
g. A few more Log Switch operations are done.
1 2 3 4 5 6 7 8 9 10 11 | [Primary-1] SQL> alter system switch logfile; System altered. [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 187 1 110 2 |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> select max(sequence#),thread#, applied from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- --------- 187 1 NO 184 1 YES 110 2 NO 109 2 YES |
h. When it is seen that archive number 185, which still contains the DROP operation, is not processed, it is followed from the logs and after a certain period of time, it is seen that the apply operation is performed.
[Standby-1]—–ALERT_LOG1 2 3 4 5 6 7 | Tue Jan 31 11:47:28 2017 Media Recovery Delayed for 1 minute(s) (thread 2 sequence 110) Tue Jan 31 11:48:28 2017 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_2_seq_110.1215.934717647 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_1_seq_186.1213.934717483 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_1_seq_187.1214.934717645 Media Recovery Waiting for thread 1 sequence 188 (in transit) |
1 2 3 4 5 6 7 8 | [Physical-1] SQL> select max(sequence#),thread#, applied from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- --------- 43 1 NO 187 1 YES 110 2 NO 109 2 YES |
1 2 3 4 5 6 | [Physical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOBS_YEDEK REGIONS_YEDEK |
1 2 | DGMGRL> edit database standby set property DelayMins=0; Property "delaymins" updated |
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 | Tue Jan 31 11:52:39 2017 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Tue Jan 31 11:52:40 2017 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u01/app/oracle/diag/rdbms/standby/primary1/trace/primary1_pr00_17469.trc: ORA-16037: user requested cancel of managed recovery operation Recovery interrupted! Tue Jan 31 11:52:40 2017 Reconfiguration started (old inc 32, new inc 34) List of instances: 1 2 (myinst: 1) Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Tue Jan 31 11:52:40 2017 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Tue Jan 31 11:52:40 2017 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Tue Jan 31 11:52:41 2017 Block change tracking service stopping. Stopping background process CTWR Tue Jan 31 11:52:42 2017 MRP0: Background Media Recovery process shutdown (primary1) Managed Standby Recovery Canceled (primary1) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (primary1) Tue Jan 31 11:52:43 2017 MRP0 started with pid=48, OS id=24228 MRP0: Background Managed Standby Recovery process started (primary1) started logmerger process Tue Jan 31 11:52:48 2017 Managed Standby Recovery starting Real Time Apply Reconfiguration started (old inc 34, new inc 36) List of instances: 1 2 (myinst: 1) Global Resource Directory frozen Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Parallel Media Recovery started with 4 slaves Tue Jan 31 11:52:50 2017 Block change tracking file is current. Starting background process CTWR Tue Jan 31 11:52:50 2017 CTWR started with pid=60, OS id=24283 Block change tracking service is active. Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 188 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 188 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683 Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683 Managed Standby Recovery started with USING CURRENT LOGFILE Ignoring previously specified DELAY 1 minutes for thread 2 sequence 110 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_2_seq_110.1215.934717647 Media Recovery Waiting for thread 2 sequence 111 (in transit) Recovery of Online Redo Log: Thread 2 Group 9 Seq 111 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_9.271.932283685 Mem# 1: +FRA/standby/onlinelog/group_9.264.932283685 Tue Jan 31 11:52:51 2017 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Tue Jan 31 11:52:51 2017 Archived Log entry 2865 added for thread 2 sequence 111 ID 0x699d7341 dest 1: ARC6: Archive log thread 2 sequence 111 available in 1 minute(s) Tue Jan 31 11:52:51 2017 Primary database is in MAXIMUM PERFORMANCE mode RFS[27]: Assigned to RFS process 24317 RFS[27]: Selected log 8 for thread 2 sequence 112 dbid 1769705496 branch 934378910 Tue Jan 31 11:52:53 2017 Archived Log entry 2866 added for thread 1 sequence 188 ID 0x699d7341 dest 1: ARC0: Archive log thread 1 sequence 188 available in 1 minute(s) Media Recovery Waiting for thread 2 sequence 112 (in transit) Recovery of Online Redo Log: Thread 2 Group 8 Seq 112 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_8.270.932283685 Mem# 1: +FRA/standby/onlinelog/group_8.263.932283685 Tue Jan 31 11:52:53 2017 Primary database is in MAXIMUM PERFORMANCE mode RFS[28]: Assigned to RFS process 24322 RFS[28]: Selected log 5 for thread 1 sequence 189 dbid 1769705496 branch 934378910 Managed Standby Recovery started with USING CURRENT LOGFILE Ignoring previously specified DELAY 1 minutes for thread 1 sequence 188 Media Recovery Log +FRA/standby/archivelog/2017_01_31/thread_1_seq_188.1217.934717973 Media Recovery Waiting for thread 1 sequence 189 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 189 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683 Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683 |
F. Optimizing SQL APPLY
SQL APPLY operation in Logical Standby Database is performed by a series of processes. In busy transactional systems, the number of these processes should be set correctly.
If no adjustment is made in Default, SQL APPLY processes running in Logical Standby Database are as follows.
Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [Logical-1] SQL> column spid format a7 [Logical-1] SQL> column type format a12 [Logical-1] SQL> column status format a57 [Logical-1] SQL> set linesize 500 [Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process; SID SERIAL# SPID TYPE STATUS ---------- ---------- ------- ------------ --------------------------------------------------------- 71 587 16201 COORDINATOR ORA-16116: no work available 134 3987 16235 ANALYZER ORA-16116: no work available 212 1637 16239 APPLIER ORA-16116: no work available 20 49 16243 APPLIER ORA-16116: no work available 146 63 16247 APPLIER ORA-16116: no work available 204 409 16251 APPLIER ORA-16116: no work available 16 281 16255 APPLIER ORA-16116: no work available 195 2129 16209 READER ORA-16242: Processing log file (thread# 2, sequence# 112) 152 275 16213 BUILDER ORA-16116: no work available 209 85 16217 PREPARER ORA-16116: no work available 10 rows selected. |
In addition, all the details about the Logical Standby Database can be seen in the figure below.
Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.
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 | [Logical-1] SQL> select max(length(name)) from v$logstdby_stats; MAX(LENGTH(NAME)) ----------------- 33 [Logical-1] SQL> select max(length(value)) from v$logstdby_stats; MAX(LENGTH(VALUE)) ------------------ 10 [Logical-1] SQL> column name format a33 [Logical-1] SQL> column value format a10 [Logical-1] SQL> set linesize 9000 [Logical-1] SQL> select * from v$logstdby_stats; NAME VALUE --------------------------------- ---------- logminer session id 1 number of preparers 1 number of appliers 5 server processes in use 9 maximum SGA for LCR cache (MB) 30 maximum events recorded 10000 preserve commit order TRUE transaction consistency FULL record skipped errors Y record skipped DDLs Y record applied DDLs N NAME VALUE --------------------------------- ---------- record unsupported operations N realtime apply Y apply delay (minutes) 0 peak apply rate (bytes/sec) 11155722 current apply rate (bytes/sec) 0 coordinator state IDLE coordinator startup time 30-JAN-17 coordinator uptime (seconds) 78257 txns received from logminer 4053 txns assigned to apply 4053 txns applied 4053 NAME VALUE --------------------------------- ---------- txns discarded during restart 0 large txns waiting to be assigned 0 session restart SCN 10880626 rolled back txns mined 2419 DDL txns mined 696 CTAS txns mined 4 bytes of redo mined 4109565544 bytes paged out 0 pageout time (seconds) 0 bytes checkpointed 880727048 checkpoint time (seconds) 0 NAME VALUE --------------------------------- ---------- system idle time (seconds) 0 standby redo logs mined 1 archived logs mined 164 gap fetched logs mined 0 standby redo log reuse detected 34 logfile open failures 0 current logfile wait (seconds) 0 total logfile wait (seconds) 0 thread enable mined 0 thread disable mined 0 distinct txns in queue 0 44 rows selected. |
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 | [Logical-1] SQL> select max(length(name)) from dba_logstdby_parameters; MAX(LENGTH(NAME)) ----------------- 29 [Logical-1] SQL> select max(length(value)) from dba_logstdby_parameters; MAX(LENGTH(VALUE)) ------------------ 17 [Logical-1] SQL> select max(length(unit)) from dba_logstdby_parameters; MAX(LENGTH(UNIT)) ----------------- 8 [Logical-1] SQL> select max(length(dynamic)) from dba_logstdby_parameters; MAX(LENGTH(DYNAMIC)) -------------------- 3 [Logical-1] SQL> column name format a29 [Logical-1] SQL> column value format a17 [Logical-1] SQL> column unit format a8 [Logical-1] SQL> column dynamic format a3 [Logical-1] SQL> select max(length(setting)) from dba_logstdby_parameters; MAX(LENGTH(SETTING)) -------------------- 6 [Logical-1] SQL> column setting format a6 [Logical-1] SQL> select * from dba_logstdby_parameters; NAME VALUE UNIT SETTIN DYN ----------------------------- ----------------- -------- ------ --- MAX_SGA 30 MEGABYTE SYSTEM YES MAX_SERVERS 9 SYSTEM YES PREPARE_SERVERS 1 SYSTEM YES APPLY_SERVERS 5 SYSTEM YES MAX_EVENTS_RECORDED 10000 SYSTEM YES RECORD_SKIP_ERRORS TRUE SYSTEM YES RECORD_SKIP_DDL TRUE SYSTEM YES RECORD_APPLIED_DDL FALSE SYSTEM YES RECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM NO EVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YES LOG_AUTO_DELETE TRUE SYSTEM YES NAME VALUE UNIT SETTIN DYN ----------------------------- ----------------- -------- ------ --- LOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YES PRESERVE_COMMIT_ORDER TRUE SYSTEM NO ALLOW_TRANSFORMATION FALSE SYSTEM NO 14 rows selected. |
The following algorithm should not be ignored when the number of processes will be changed.
APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3
For example, as can be seen in the v$logstdby_stats view above, we have 1 Preparer and 5 Applier processes. Our Max Process number is 9, so it fits the formula.
The DBMS_LOGSTDBY.APPLY_SET package is used when making changes.
When you want to change the number of APPLIER process: APPLY_SERVERS,
When you want to change the number of PREPARER process: PREPARE_SERVERS,
when you want to change the total number of processes in SQL APPLY process: MAX_SERVERS attribute is changed.
1. Changing APPLIER Process Number
a. It is questioned whether the existing APPLIER Processes are running.
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116; IDLE_APPLIER ------------ 5 |
b. If there are no IDLE processes, it is checked whether there are enough jobs to increase the number of APPLIER processes.
1 2 3 | [Logical-1] SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%'; no rows selected |
This query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.
Transactions Mined: Number of transactions ready to be implemented by APPLIER processes.
Transactions Applied: The number of transactions that have been applied.
If the difference between these values is more than 2 times the number of APPLIER processes, then it will be beneficial to increase the number of APPLIER processes.
While increasing, it should be increased according to the formula below.
1 | APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3 |
2. Changing the Number of PREPARER Processes
a. Ensure that all PREPARER processes are running.
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS IDLE_PREPARER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'PREPARER' and status_code = 16116; IDLE_PREPARER ------------- 1 |
b. The number of transactions ready to be implemented (Transactions Mined) must be less than the number of APPLIER processes.
1 2 3 | [Logical-1] SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%'; no rows selected |
This query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.
Transactions Mined: Number of transactions ready to be implemented by APPLIER processes.
Transactions Applied: The number of transactions that have been applied.
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER'; APPLIER_COUNT ------------- 5 |
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116; IDLE_APPLIER ------------ 5 |
c. If these conditions are met, the number of PREPARER processes is increased by considering the following formula.
1 | APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS – 3 |
1 2 3 4 5 6 7 8 9 10 11 | [Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 14); PL/SQL procedure successfully completed. [Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 8); PL/SQL procedure successfully completed. [Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 3); PL/SQL procedure successfully completed. |
d. We provide the controls.
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 | [Logical-1] SQL> column spid format a7 [Logical-1] SQL> column type format a12 [Logical-1] SQL> column status format a57 [Logical-1] SQL> set linesize 500 [Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process; SID SERIAL# SPID TYPE STATUS ---------- ---------- ------- ------------ --------------------------------------------------------- 71 587 16201 COORDINATOR ORA-16116: no work available 134 3987 16235 ANALYZER ORA-16116: no work available 212 1637 16239 APPLIER ORA-16116: no work available 20 49 16243 APPLIER ORA-16116: no work available 146 63 16247 APPLIER ORA-16116: no work available 204 409 16251 APPLIER ORA-16116: no work available 16 281 16255 APPLIER ORA-16116: no work available 155 61 1511 APPLIER ORA-16116: no work available 215 83 1515 APPLIER ORA-16116: no work available 29 215 1519 APPLIER ORA-16116: no work available 195 2129 16209 READER ORA-16242: Processing log file (thread# 1, sequence# 189) SID SERIAL# SPID TYPE STATUS ---------- ---------- ------- ------------ --------------------------------------------------------- 152 275 16213 BUILDER ORA-16116: no work available 209 85 16217 PREPARER ORA-16116: no work available 22 2281 1619 PREPARER ORA-16116: no work available 77 7867 1623 PREPARER ORA-16116: no work available 15 rows selected. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [Logical-1] SQL> select * from dba_logstdby_parameters; NAME VALUE UNIT SETTIN DYN ----------------------------- ----------------- -------- ------ --- MAX_SGA 30 MEGABYTE SYSTEM YES MAX_SERVERS 14 USER YES PREPARE_SERVERS 3 USER YES APPLY_SERVERS 8 USER YES MAX_EVENTS_RECORDED 10000 SYSTEM YES RECORD_SKIP_ERRORS TRUE SYSTEM YES RECORD_SKIP_DDL TRUE SYSTEM YES RECORD_APPLIED_DDL FALSE SYSTEM YES RECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM NO EVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YES LOG_AUTO_DELETE TRUE SYSTEM YES NAME VALUE UNIT SETTIN DYN ----------------------------- ----------------- -------- ------ --- LOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YES PRESERVE_COMMIT_ORDER TRUE SYSTEM NO ALLOW_TRANSFORMATION FALSE SYSTEM NO 14 rows selected. |
G. Optimizing LCR Cache Size
Redo records read by the Reader Process are converted to Logical Change Records (LCRs) by the Preparer Process and kept in the LCR Cache area within the SGA area. Therefore, the number of this field should be set correctly in a system with intensive transaction processing.
In Default, this value is 30 MB.
If LCR Cache is not enough for you, this information is stored in SPILL tables in SYSAUX, which is very costly.
We can query whether our system uses the tables in SYSAUX as follows.
1 2 3 4 5 | [Logical-1] SQL> select name,to_number(value) value from v$logstdby_stats where name='bytes paged out'; NAME VALUE ---------------------------------------------------------------- ---------- bytes paged out 0 |
0 means that SYSAUX is not used and LCR Cache Size is sufficient.
What percentage of LCR Cache Size is used can also be found in the figure below.
1 2 3 4 5 6 | [Logical-1] SQL> select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization 2 from ( select * from v$sgastat where name = 'Logminer LCR c'), (select value*(1024*1024) max_sga from dba_logstdby_parameters where name = 'MAX_SGA'); NAME PCT_UTILIZATION -------------------------- --------------- Logminer LCR c 6.58630829 |
If we see the PCT_UTILIZATION value here as 100% or very high, then we understand that the LCR Cache Size is not enough and should be increased.
LCR Cache Size is increased if desired.
1 2 3 | [Logical-1] SQL> exec dbms_logstdby.apply_set('MAX_SGA',500); PL/SQL procedure successfully completed. |
Status of Processes in Logical Standby as a result of IMPORT operation
After 74 tables are imported to the Primary, the situation in the Logical Standby Database is as follows.
1. The sequence numbers of the archives are checked in the Primary and Standby Databases.
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 286 1 143 2 |
1 2 3 4 5 6 7 8 9 | [Physical-1] SQL> select max(sequence#),thread#, applied from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#,applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- --------- 43 1 NO 285 1 YES 286 1 IN-MEMORY 43 2 NO 143 2 YES |
1 2 3 4 5 6 7 8 9 10 11 12 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 286 1 NO 219 1 CURRENT 189 1 YES 143 2 NO 111 2 YES 122 2 CURRENT 6 rows selected. |
As can be seen from here, APPLY work of approximately 67 archives is still in progress.
2. We check the status of SQL APPLY Processes running in Logical Standby Database.
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 | [Logical-1] SQL> column spid format a7 [Logical-1] SQL> column type format a12 [Logical-1] SQL> column status format a57 [Logical-1] SQL> set linesize 500 [Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process; SID SERIAL# SPID TYPE STATUS ---------- ---------- ------- ------------ --------------------------------------------------------- 71 587 16201 COORDINATOR ORA-16116: no work available 134 3987 16235 ANALYZER ORA-16116: no work available 212 1637 16239 APPLIER ORA-16116: no work available 20 49 16243 APPLIER ORA-16116: no work available 146 63 16247 APPLIER ORA-16113: applying change to table or sequence "IPTVMWC"."SEADAC_NOTIFICATION" 204 409 16251 APPLIER ORA-16116: no work available 16 281 16255 APPLIER ORA-16116: no work available 155 61 1511 APPLIER ORA-16116: no work available 215 83 1515 APPLIER ORA-16116: no work available 29 215 1519 APPLIER ORA-16116: no work available 195 2129 16209 READER ORA-16127: stalled waiting for additional transactions to be applied 152 275 16213 BUILDER ORA-16127: stalled waiting for additional transactions to be applied 209 85 16217 PREPARER ORA-16127: stalled waiting for additional transactions to be applied 22 2281 1619 PREPARER ORA-16127: stalled waiting for additional transactions to be applied 77 7867 1623 PREPARER ORA-16127: stalled waiting for additional transactions to be applied 15 rows selected. |
3. Check if there are any APPLIER processes in IDLE.
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116; IDLE_APPLIER ------------ 7 |
4. How much of the LCR Size is used is checked.
1 2 3 4 5 6 | [Logical-1] SQL> select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization 2 from ( select * from v$sgastat where name = 'Logminer LCR c'), (select value*(1024*1024) max_sga from dba_logstdby_parameters where name = 'MAX_SGA'); NAME PCT_UTILIZATION -------------------------- --------------- Logminer LCR c 100 |
5. Let’s see if there are any of the PREPARER Processes in IDLE.
1 2 3 4 5 | [Logical-1] SQL> SELECT COUNT(*) AS IDLE_PREPARER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'PREPARER' and status_code = 16116; IDLE_PREPARER ------------- 0 |
In the light of all this information, the following conclusion is reached.
The number of APPLIER processes is sufficient.
The number of preparer processes is not sufficient.
LCR Cache Size is not enough.
