After installing Data Guard, we may need to make some adjustments for it to work effectively.<\/p>\n
For example,<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Here are the optimizations that can be made in this and similar cases.<\/p>\n
It determines how many seconds after the ARCHIVER process will try to send redo’s to that location in failed Redo sends.<\/p>\n
In Default for the broker, this value is 300 seconds.<\/p>\n
It corresponds to the REOPEN attribute in the LOG_ARCHIVE_DEST_n parameter.<\/p>\n
The parameter is changed as follows.<\/p>\n
1. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'ReOpenSecs';\r\n ReopenSecs = '300'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=1 reopen=300 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=30, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\n2. We change the parameter value.<\/p>\n
DGMGRL> edit database standby set property ReOpenSecs=600;\r\nProperty \"reopensecs\" updated\r\n<\/pre>\n3. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'ReOpenSecs';\r\n ReopenSecs = '600'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=1 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=30, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\nB.NETTIMEOUT<\/span><\/h4>\n
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.<\/p>\n
The broker’s default value is 30 seconds.<\/p>\n
Corresponds to the NET_TIMEOUNT attribute in the LOG_ARCHIVE_DEST_n parameter.<\/p>\n
The parameter is changed as follows.<\/p>\n
1. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'NetTimeout';\r\n NetTimeout = '30'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=1 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=30, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\n2. We change the parameter value.<\/p>\n
DGMGRL> edit database standby set property NetTimeout=20;\r\nProperty \"nettimeout\" updated\r\n<\/pre>\n3. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'NetTimeout';\r\n NetTimeout = '20'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=1 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=20, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\nC.MAXCONNECTIONS<\/span><\/h4>\n
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.<\/p>\n
Since Redo Transfer Rate will increase with this parameter, Redos are sent to the Standby side faster.<\/p>\n
Default value is 1 and Maximum value is 20.<\/p>\n
Corresponds to the MAX_CONNECTIONS attribute in the LOG_ARCHIVE_DEST_n parameter.<\/p>\n
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.<\/p>\n
We change the parameter as follows.<\/p>\n
1. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'MaxConnections';\r\n MaxConnections = '1'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=1 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=20, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\n2. The value of the parameter is changed.<\/p>\n
a. First we query the value of log_archive_max_processes.<\/p>\n
[Primary-1] SQL> show parameter log_archive_max_proce\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_max_processes integer 8\r\n<\/pre>\nb. The value of the parameter is set so that it does not exceed this value.<\/p>\n
DGMGRL> edit database standby set property 'MaxConnections'=8;\r\nProperty \"MaxConnections\" updated\r\n<\/pre>\n3. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'MaxConnections';\r\n MaxConnections = '8'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=8 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=20, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\nD.REDOCOMPRESSION<\/span><\/h4>\n
For SYNC or ASYNC Redo Transport Services, it specifies that Redo is compressed to the Standby side.<\/p>\n
Default value is DISABLE because it requires Advance Compression License.<\/p>\n
Corresponds to the COMPRESSION attribute in the LOG_ARCHIVE_DEST_n parameter.<\/p>\n
The parameter is changed as follows.<\/p>\n
1. We query the current value of the parameter.<\/p>\n
DGMGRL> show database standby 'RedoCompression';\r\n RedoCompression = 'DISABLE'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=disable max_failure=0\r\n max_connections=8 reopen=600 d\r\n b_unique_name=\"standby\" net_ti\r\n meout=20, valid_for=(all_logfi\r\n les,primary_role)\r\n<\/pre>\n2. We change the value of the parameter.<\/p>\n
DGMGRL> edit database standby set property RedoCompression=ENABLE;\r\nProperty \"redocompression\" updated\r\n<\/pre>\n3. The current value of the parameter is queried.<\/p>\n
DGMGRL> show database standby 'RedoCompression';\r\n RedoCompression = 'enable'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=enable max_failure=0 m\r\n ax_connections=8 reopen=600 db\r\n _unique_name=\"standby\" net_tim\r\n eout=20, valid_for=(all_logfil\r\n es,primary_role)\r\n<\/pre>\nE.DELAYMINS<\/span><\/h4>\n
Due to user or application reasons, it may be requested to apply the data to the Standby side after a certain delay.<\/p>\n
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.<\/p>\n
Default value is 0.<\/p>\n
Corresponds to the DELAY attribute in the LOG_ARCHIVE_DEST_n parameter.<\/p>\n
We change the parameter as follows.<\/p>\n
1. The current values of DELAY, PROTECTION_MODE, and RECOVERY_MODE parameters are queried.<\/p>\n
DGMGRL> show database standby 'DelayMins';\r\n DelayMins = '0'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=0 optional comp\r\n ression=enable max_failure=0 m\r\n ax_connections=8 reopen=600 db\r\n _unique_name=\"standby\" net_tim\r\n eout=20, valid_for=(all_logfil\r\n es,primary_role)\r\n<\/pre>\n[Physical-2] SQL> select open_mode, database_role, protection_mode from gv$database;\r\n\r\nOPEN_MODE DATABASE_ROLE PROTECTION_MODE\r\n-------------------- ---------------- --------------------\r\nREAD ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE\r\nREAD ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE\r\n\r\n\r\n[Physical-2] SQL> select recovery_mode from v$archive_dest_status where dest_id < 2;\r\n\r\nRECOVERY_MODE\r\n-----------------------\r\nMANAGED REAL TIME APPLY\r\n<\/pre>\n2. It is seen that the DML made to the Primary with the current values of the parameters is applied instantly on the Standby side.<\/p>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nJOBS_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n[Physical-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nJOBS_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n[Primary-1] SQL> drop table test.JOB_HISTORY_YEDEK;\r\n\r\nTable dropped.\r\n\r\n[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n[Physical-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nDEPARTMENTS_YEDEK\r\nEMPLOYEES_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n3. While applying Real-Time Redos, the DELAY attribute is set to a certain value.<\/p>\n
DGMGRL> edit database standby set property DelayMins=2;\r\nProperty \"delaymins\" updated\r\nDGMGRL> show database standby 'DelayMins';\r\n DelayMins = '2'\r\n<\/pre>\n[Primary-1] SQL> show parameter log_archive_dest_2\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlog_archive_dest_2 string service=\"standby\", LGWR ASYNC\r\n NOAFFIRM delay=2 optional comp\r\n ression=enable max_failure=0 m\r\n ax_connections=8 reopen=600 db\r\n _unique_name=\"standby\" net_tim\r\n eout=20, valid_for=(all_logfil\r\n es,primary_role)\r\n<\/pre>\n4. A table is DROPed in Primary and it is seen whether it goes from Standby instantly.<\/p>\n
a. Existing Archive Log Sequence numbers are queried.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 184 1\r\n 109 2\r\n<\/pre>\n[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#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 184 1\r\n 109 2\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- --------\r\n 184 1 YES\r\n 109 2 YES\r\n<\/pre>\nb. The table is DROP.<\/p>\n
[Primary-1] SQL> drop table test.LOCATIONS_YEDEK;\r\n\r\nTable dropped.\r\n<\/pre>\nc. It is checked whether the table has been deleted or not.<\/p>\n
[Primary-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nREGIONS_YEDEK\r\nLOCATIONS_YEDEK\r\n<\/pre>\nIt 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.<\/p>\n
d. An archive is created.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n<\/pre>\n[Standby-1]—–ALERT_LOG<\/p>\nTue Jan 31 11:33:48 2017\r\nRFS[26]: Selected log 6 for thread 1 sequence 186 dbid 1769705496 branch 934378910\r\nTue Jan 31 11:33:48 2017\r\nArchived Log entry 2861 added for thread 1 sequence 185 ID 0x699d7341 dest 1:\r\nARC4: Archive log thread 1 sequence 185 available in 1 minute(s)\r\nTue Jan 31 11:33:50 2017\r\nMedia Recovery Delayed for 1 minute(s) (thread 1 sequence 185)\r\nTue Jan 31 11:34:50 2017\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_1_seq_185.1212.934716829\r\nMedia Recovery Waiting for thread 2 sequence 110 (in transit)\r\n<\/pre>\ne. It is checked whether the created archive is APPLY on the standby side.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 185 1\r\n 109 2\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- ---------\r\n 185 1 NO\r\n 184 1 YES\r\n 43 2 NO\r\n 109 2 YES\r\n<\/pre>\nf. After a certain time, we check whether the table has been dropped or not.<\/p>\n
[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nREGIONS_YEDEK\r\nLOCATIONS_YEDEK\r\n<\/pre>\ng. A few more Log Switch operations are done.<\/p>\n
[Primary-1] SQL> alter system switch logfile;\r\n\r\nSystem altered.\r\n\r\n\r\n[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#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 187 1\r\n 110 2\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- ---------\r\n 187 1 NO\r\n 184 1 YES\r\n 110 2 NO\r\n 109 2 YES\r\n<\/pre>\nh. 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.<\/p>\n[Standby-1]—–ALERT_LOG<\/p>\n
Tue Jan 31 11:47:28 2017\r\nMedia Recovery Delayed for 1 minute(s) (thread 2 sequence 110)\r\nTue Jan 31 11:48:28 2017\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_2_seq_110.1215.934717647\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_1_seq_186.1213.934717483\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_1_seq_187.1214.934717645\r\nMedia Recovery Waiting for thread 1 sequence 188 (in transit)\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- ---------\r\n 43 1 NO\r\n 187 1 YES\r\n 110 2 NO\r\n 109 2 YES\r\n<\/pre>\n[Physical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOBS_YEDEK\r\nREGIONS_YEDEK\r\n<\/pre>\n5. The property is set back to its old value.<\/p>\n
DGMGRL> edit database standby set property DelayMins=0;\r\nProperty \"delaymins\" updated\r\n<\/pre>\n[Standby-1]—–>ALERT_LOG<\/p>\nTue Jan 31 11:52:39 2017\r\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL\r\nTue Jan 31 11:52:40 2017\r\nMRP0: Background Media Recovery cancelled with status 16037\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/standby\/primary1\/trace\/primary1_pr00_17469.trc:\r\nORA-16037: user requested cancel of managed recovery operation\r\nRecovery interrupted!\r\nTue Jan 31 11:52:40 2017\r\nReconfiguration started (old inc 32, new inc 34)\r\nList of instances:\r\n 1 2 (myinst: 1) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\nTue Jan 31 11:52:40 2017\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\nTue Jan 31 11:52:40 2017\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nTue Jan 31 11:52:41 2017\r\nBlock change tracking service stopping.\r\nStopping background process CTWR\r\nTue Jan 31 11:52:42 2017\r\nMRP0: Background Media Recovery process shutdown (primary1)\r\nManaged Standby Recovery Canceled (primary1)\r\nCompleted: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL\r\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE\r\nAttempt to start background Managed Standby Recovery process (primary1)\r\nTue Jan 31 11:52:43 2017\r\nMRP0 started with pid=48, OS id=24228 \r\nMRP0: Background Managed Standby Recovery process started (primary1)\r\n started logmerger process\r\nTue Jan 31 11:52:48 2017\r\nManaged Standby Recovery starting Real Time Apply\r\nReconfiguration started (old inc 34, new inc 36)\r\nList of instances:\r\n 1 2 (myinst: 1) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nParallel Media Recovery started with 4 slaves\r\nTue Jan 31 11:52:50 2017\r\nBlock change tracking file is current.\r\nStarting background process CTWR\r\nTue Jan 31 11:52:50 2017\r\nCTWR started with pid=60, OS id=24283 \r\nBlock change tracking service is active.\r\nWaiting for all non-current ORLs to be archived...\r\nAll non-current ORLs have been archived.\r\nMedia Recovery Waiting for thread 1 sequence 188 (in transit)\r\nRecovery of Online Redo Log: Thread 1 Group 5 Seq 188 Reading mem 0\r\n Mem# 0: +DATA\/standby\/onlinelog\/group_5.267.932283683\r\n Mem# 1: +FRA\/standby\/onlinelog\/group_5.260.932283683\r\nManaged Standby Recovery started with USING CURRENT LOGFILE\r\nIgnoring previously specified DELAY 1 minutes for thread 2 sequence 110\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_2_seq_110.1215.934717647\r\nMedia Recovery Waiting for thread 2 sequence 111 (in transit)\r\nRecovery of Online Redo Log: Thread 2 Group 9 Seq 111 Reading mem 0\r\n Mem# 0: +DATA\/standby\/onlinelog\/group_9.271.932283685\r\n Mem# 1: +FRA\/standby\/onlinelog\/group_9.264.932283685\r\nTue Jan 31 11:52:51 2017\r\nCompleted: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE\r\nTue Jan 31 11:52:51 2017\r\nArchived Log entry 2865 added for thread 2 sequence 111 ID 0x699d7341 dest 1:\r\nARC6: Archive log thread 2 sequence 111 available in 1 minute(s)\r\nTue Jan 31 11:52:51 2017\r\nPrimary database is in MAXIMUM PERFORMANCE mode\r\nRFS[27]: Assigned to RFS process 24317\r\nRFS[27]: Selected log 8 for thread 2 sequence 112 dbid 1769705496 branch 934378910\r\nTue Jan 31 11:52:53 2017\r\nArchived Log entry 2866 added for thread 1 sequence 188 ID 0x699d7341 dest 1:\r\nARC0: Archive log thread 1 sequence 188 available in 1 minute(s)\r\nMedia Recovery Waiting for thread 2 sequence 112 (in transit)\r\nRecovery of Online Redo Log: Thread 2 Group 8 Seq 112 Reading mem 0\r\n Mem# 0: +DATA\/standby\/onlinelog\/group_8.270.932283685\r\n Mem# 1: +FRA\/standby\/onlinelog\/group_8.263.932283685\r\nTue Jan 31 11:52:53 2017\r\nPrimary database is in MAXIMUM PERFORMANCE mode\r\nRFS[28]: Assigned to RFS process 24322\r\nRFS[28]: Selected log 5 for thread 1 sequence 189 dbid 1769705496 branch 934378910\r\nManaged Standby Recovery started with USING CURRENT LOGFILE\r\nIgnoring previously specified DELAY 1 minutes for thread 1 sequence 188\r\nMedia Recovery Log +FRA\/standby\/archivelog\/2017_01_31\/thread_1_seq_188.1217.934717973\r\nMedia Recovery Waiting for thread 1 sequence 189 (in transit)\r\nRecovery of Online Redo Log: Thread 1 Group 5 Seq 189 Reading mem 0\r\n Mem# 0: +DATA\/standby\/onlinelog\/group_5.267.932283683\r\n Mem# 1: +FRA\/standby\/onlinelog\/group_5.260.932283683\r\n<\/pre>\nF. Optimizing SQL APPLY<\/h4>\n
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.<\/p>\n
If no adjustment is made in Default, SQL APPLY processes running in Logical Standby Database are as follows.<\/p>\n
Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.<\/p>\n
[Logical-1] SQL> column spid format a7\r\n[Logical-1] SQL> column type format a12\r\n[Logical-1] SQL> column status format a57\r\n[Logical-1] SQL> set linesize 500\r\n[Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process;\r\n\r\n SID SERIAL# SPID TYPE STATUS\r\n---------- ---------- ------- ------------ ---------------------------------------------------------\r\n 71 587 16201 COORDINATOR ORA-16116: no work available\r\n 134 3987 16235 ANALYZER ORA-16116: no work available\r\n 212 1637 16239 APPLIER ORA-16116: no work available\r\n 20 49 16243 APPLIER ORA-16116: no work available\r\n 146 63 16247 APPLIER ORA-16116: no work available\r\n 204 409 16251 APPLIER ORA-16116: no work available\r\n 16 281 16255 APPLIER ORA-16116: no work available\r\n 195 2129 16209 READER ORA-16242: Processing log file (thread# 2, sequence# 112)\r\n 152 275 16213 BUILDER ORA-16116: no work available\r\n 209 85 16217 PREPARER ORA-16116: no work available\r\n\r\n10 rows selected.\r\n<\/pre>\nIn addition, all the details about the Logical Standby Database can be seen in the figure below.<\/p>\n
Whichever Apply Instance is in the RAC structure, the following query should be run on that instance.<\/p>\n
[Logical-1] SQL> select max(length(name)) from v$logstdby_stats;\r\n\r\nMAX(LENGTH(NAME))\r\n-----------------\r\n 33\r\n\r\n[Logical-1] SQL> select max(length(value)) from v$logstdby_stats;\r\n\r\nMAX(LENGTH(VALUE))\r\n------------------\r\n 10\r\n\r\n[Logical-1] SQL> column name format a33\r\n[Logical-1] SQL> column value format a10\r\n[Logical-1] SQL> set linesize 9000\r\n[Logical-1] SQL> select * from v$logstdby_stats;\r\n\r\nNAME VALUE\r\n--------------------------------- ----------\r\nlogminer session id 1\r\nnumber of preparers 1\r\nnumber of appliers 5\r\nserver processes in use 9\r\nmaximum SGA for LCR cache (MB) 30\r\nmaximum events recorded 10000\r\npreserve commit order TRUE\r\ntransaction consistency FULL\r\nrecord skipped errors Y\r\nrecord skipped DDLs Y\r\nrecord applied DDLs N\r\n\r\nNAME VALUE\r\n--------------------------------- ----------\r\nrecord unsupported operations N\r\nrealtime apply Y\r\napply delay (minutes) 0\r\npeak apply rate (bytes\/sec) 11155722\r\ncurrent apply rate (bytes\/sec) 0\r\ncoordinator state IDLE\r\ncoordinator startup time 30-JAN-17\r\ncoordinator uptime (seconds) 78257\r\ntxns received from logminer 4053\r\ntxns assigned to apply 4053\r\ntxns applied 4053\r\n\r\nNAME VALUE\r\n--------------------------------- ----------\r\ntxns discarded during restart 0\r\nlarge txns waiting to be assigned 0\r\nsession restart SCN 10880626\r\nrolled back txns mined 2419\r\nDDL txns mined 696\r\nCTAS txns mined 4\r\nbytes of redo mined 4109565544\r\nbytes paged out 0\r\npageout time (seconds) 0\r\nbytes checkpointed 880727048\r\ncheckpoint time (seconds) 0\r\n\r\nNAME VALUE\r\n--------------------------------- ----------\r\nsystem idle time (seconds) 0\r\nstandby redo logs mined 1\r\narchived logs mined 164\r\ngap fetched logs mined 0\r\nstandby redo log reuse detected 34\r\nlogfile open failures 0\r\ncurrent logfile wait (seconds) 0\r\ntotal logfile wait (seconds) 0\r\nthread enable mined 0\r\nthread disable mined 0\r\ndistinct txns in queue 0\r\n\r\n44 rows selected.\r\n<\/pre>\n[Logical-1] SQL> select max(length(name)) from dba_logstdby_parameters;\r\n\r\nMAX(LENGTH(NAME))\r\n-----------------\r\n 29\r\n\r\n[Logical-1] SQL> select max(length(value)) from dba_logstdby_parameters;\r\n\r\nMAX(LENGTH(VALUE))\r\n------------------\r\n 17\r\n\r\n[Logical-1] SQL> select max(length(unit)) from dba_logstdby_parameters;\r\n\r\nMAX(LENGTH(UNIT))\r\n-----------------\r\n 8\r\n\r\n[Logical-1] SQL> select max(length(dynamic)) from dba_logstdby_parameters;\r\n\r\nMAX(LENGTH(DYNAMIC))\r\n--------------------\r\n 3\r\n\r\n[Logical-1] SQL> column name format a29\r\n[Logical-1] SQL> column value format a17\r\n[Logical-1] SQL> column unit format a8\r\n[Logical-1] SQL> column dynamic format a3 \r\n[Logical-1] SQL> select max(length(setting)) from dba_logstdby_parameters;\r\n\r\nMAX(LENGTH(SETTING))\r\n--------------------\r\n 6\r\n\r\n[Logical-1] SQL> column setting format a6\r\n[Logical-1] SQL> select * from dba_logstdby_parameters;\r\n\r\nNAME VALUE UNIT SETTIN DYN\r\n----------------------------- ----------------- -------- ------ ---\r\nMAX_SGA 30 MEGABYTE SYSTEM YES\r\nMAX_SERVERS 9 SYSTEM YES\r\nPREPARE_SERVERS 1 SYSTEM YES\r\nAPPLY_SERVERS 5 SYSTEM YES\r\nMAX_EVENTS_RECORDED 10000 SYSTEM YES\r\nRECORD_SKIP_ERRORS TRUE SYSTEM YES\r\nRECORD_SKIP_DDL TRUE SYSTEM YES\r\nRECORD_APPLIED_DDL FALSE SYSTEM YES\r\nRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM NO\r\nEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YES\r\nLOG_AUTO_DELETE TRUE SYSTEM YES\r\n\r\nNAME VALUE UNIT SETTIN DYN\r\n----------------------------- ----------------- -------- ------ ---\r\nLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YES\r\nPRESERVE_COMMIT_ORDER TRUE SYSTEM NO\r\nALLOW_TRANSFORMATION FALSE SYSTEM NO\r\n\r\n14 rows selected.\r\n<\/pre>\nThe following algorithm should not be ignored when the number of processes will be changed.<\/p>\n
APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS \u2013 3<\/p>\n
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.<\/p>\n
The DBMS_LOGSTDBY.APPLY_SET package is used when making changes.
\nWhen you want to change the number of APPLIER process: APPLY_SERVERS,<\/p>\nWhen you want to change the number of PREPARER process: PREPARE_SERVERS,<\/p>\n
when you want to change the total number of processes in SQL APPLY process: MAX_SERVERS attribute is changed.<\/p>\n
1. Changing APPLIER Process Number<\/p>\n
a. It is questioned whether the existing APPLIER Processes are running.<\/p>\n
[Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116;\r\n\r\nIDLE_APPLIER\r\n------------\r\n 5\r\n<\/pre>\nb. If there are no IDLE processes, it is checked whether there are enough jobs to increase the number of APPLIER processes.<\/p>\n
[Logical-1] SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';\r\n\r\nno rows selected\r\n<\/pre>\nThis query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.<\/p>\n
Transactions Mined:<\/strong> Number of transactions ready to be implemented by APPLIER processes.<\/p>\n
Transactions Applied:<\/strong> The number of transactions that have been applied.<\/p>\n
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.<\/p>\n
While increasing, it should be increased according to the formula below.<\/p>\n
APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS \u2013 3\r\n<\/pre>\n2. Changing the Number of PREPARER Processes<\/p>\n
a. Ensure that all PREPARER processes are running.<\/p>\n
[Logical-1] SQL> SELECT COUNT(*) AS IDLE_PREPARER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'PREPARER' and status_code = 16116;\r\n\r\nIDLE_PREPARER\r\n-------------\r\n 1\r\n<\/pre>\nb. The number of transactions ready to be implemented (Transactions Mined) must be less than the number of APPLIER processes.<\/p>\n
[Logical-1] SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';\r\n\r\nno rows selected\r\n<\/pre>\nThis query did not return any results for us, but in a busy transactional system, 2 values are returned as a result of this query.<\/p>\n
Transactions Mined:<\/strong> Number of transactions ready to be implemented by APPLIER processes.<\/p>\n
Transactions Applied:<\/strong> The number of transactions that have been applied.<\/p>\n
[Logical-1] SQL> SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';\r\n\r\nAPPLIER_COUNT\r\n-------------\r\n 5\r\n<\/pre>\n[Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116;\r\n\r\nIDLE_APPLIER\r\n------------\r\n 5\r\n<\/pre>\nc. If these conditions are met, the number of PREPARER processes is increased by considering the following formula.<\/p>\n
APPLY_SERVERS + PREPARE_SERVERS = MAX_SERVERS \u2013 3\r\n<\/pre>\n[Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS', 14);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n[Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 8);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\n[Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 3);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\nd. We provide the controls.<\/p>\n
[Logical-1] SQL> column spid format a7\r\n[Logical-1] SQL> column type format a12\r\n[Logical-1] SQL> column status format a57\r\n[Logical-1] SQL> set linesize 500\r\n[Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process;\r\n\r\n SID SERIAL# SPID TYPE STATUS\r\n---------- ---------- ------- ------------ ---------------------------------------------------------\r\n 71 587 16201 COORDINATOR ORA-16116: no work available\r\n 134 3987 16235 ANALYZER ORA-16116: no work available\r\n 212 1637 16239 APPLIER ORA-16116: no work available\r\n 20 49 16243 APPLIER ORA-16116: no work available\r\n 146 63 16247 APPLIER ORA-16116: no work available\r\n 204 409 16251 APPLIER ORA-16116: no work available\r\n 16 281 16255 APPLIER ORA-16116: no work available\r\n 155 61 1511 APPLIER ORA-16116: no work available\r\n 215 83 1515 APPLIER ORA-16116: no work available\r\n 29 215 1519 APPLIER ORA-16116: no work available\r\n 195 2129 16209 READER ORA-16242: Processing log file (thread# 1, sequence# 189)\r\n\r\n SID SERIAL# SPID TYPE STATUS\r\n---------- ---------- ------- ------------ ---------------------------------------------------------\r\n 152 275 16213 BUILDER ORA-16116: no work available\r\n 209 85 16217 PREPARER ORA-16116: no work available\r\n 22 2281 1619 PREPARER ORA-16116: no work available\r\n 77 7867 1623 PREPARER ORA-16116: no work available\r\n\r\n15 rows selected.\r\n<\/pre>\n[Logical-1] SQL> select * from dba_logstdby_parameters;\r\n\r\nNAME VALUE UNIT SETTIN DYN\r\n----------------------------- ----------------- -------- ------ ---\r\nMAX_SGA 30 MEGABYTE SYSTEM YES\r\nMAX_SERVERS 14 USER YES\r\nPREPARE_SERVERS 3 USER YES\r\nAPPLY_SERVERS 8 USER YES\r\nMAX_EVENTS_RECORDED 10000 SYSTEM YES\r\nRECORD_SKIP_ERRORS TRUE SYSTEM YES\r\nRECORD_SKIP_DDL TRUE SYSTEM YES\r\nRECORD_APPLIED_DDL FALSE SYSTEM YES\r\nRECORD_UNSUPPORTED_OPERATIONS FALSE SYSTEM NO\r\nEVENT_LOG_DEST DEST_EVENTS_TABLE SYSTEM YES\r\nLOG_AUTO_DELETE TRUE SYSTEM YES\r\n\r\nNAME VALUE UNIT SETTIN DYN\r\n----------------------------- ----------------- -------- ------ ---\r\nLOG_AUTO_DEL_RETENTION_TARGET 1440 MINUTE SYSTEM YES\r\nPRESERVE_COMMIT_ORDER TRUE SYSTEM NO\r\nALLOW_TRANSFORMATION FALSE SYSTEM NO\r\n\r\n14 rows selected.\r\n<\/pre>\nG. Optimizing LCR Cache Size<\/h4>\n
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.<\/p>\n
In Default, this value is 30 MB.<\/p>\n
If LCR Cache is not enough for you, this information is stored in SPILL tables in SYSAUX, which is very costly.<\/p>\n
We can query whether our system uses the tables in SYSAUX as follows.<\/p>\n
[Logical-1] SQL> select name,to_number(value) value from v$logstdby_stats where name='bytes paged out';\r\n\r\nNAME VALUE\r\n---------------------------------------------------------------- ----------\r\nbytes paged out 0\r\n<\/pre>\n0 means that SYSAUX is not used and LCR Cache Size is sufficient.<\/p>\n
What percentage of LCR Cache Size is used can also be found in the figure below.<\/p>\n
[Logical-1] SQL> select name,(least(max_sga,bytes)\/max_sga) * 100 pct_utilization \r\n 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');\r\n\r\nNAME PCT_UTILIZATION\r\n-------------------------- ---------------\r\nLogminer LCR c 6.58630829\r\n<\/pre>\nIf 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.<\/p>\n
LCR Cache Size is increased if desired.<\/p>\n
[Logical-1] SQL> exec dbms_logstdby.apply_set('MAX_SGA',500);\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\nStatus of Processes in Logical Standby as a result of IMPORT operation<\/strong><\/p>\n
After 74 tables are imported to the Primary, the situation in the Logical Standby Database is as follows.<\/p>\n
1. The sequence numbers of the archives are checked in the Primary and Standby Databases.<\/p>\n
[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#;\r\n\r\nMAX(SEQUENCE#) THREAD#\r\n-------------- ----------\r\n 286 1\r\n 143 2\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- ---------\r\n 43 1 NO\r\n 285 1 YES\r\n 286 1 IN-MEMORY\r\n 43 2 NO\r\n 143 2 YES\r\n<\/pre>\n[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;\r\n\r\nMAX(SEQUENCE#) THREAD# APPLIED\r\n-------------- ---------- --------\r\n 286 1 NO\r\n 219 1 CURRENT\r\n 189 1 YES\r\n 143 2 NO\r\n 111 2 YES\r\n 122 2 CURRENT\r\n\r\n6 rows selected.<\/pre>\nAs can be seen from here, APPLY work of approximately 67 archives is still in progress.<\/p>\n
2. We check the status of SQL APPLY Processes running in Logical Standby Database.<\/p>\n
[Logical-1] SQL> column spid format a7\r\n[Logical-1] SQL> column type format a12\r\n[Logical-1] SQL> column status format a57\r\n[Logical-1] SQL> set linesize 500\r\n[Logical-1] SQL> select sid, serial#, spid, type, status from v$logstdby_process;\r\n\r\n SID SERIAL# SPID TYPE STATUS\r\n---------- ---------- ------- ------------ ---------------------------------------------------------\r\n 71 587 16201 COORDINATOR ORA-16116: no work available\r\n 134 3987 16235 ANALYZER ORA-16116: no work available\r\n 212 1637 16239 APPLIER ORA-16116: no work available\r\n 20 49 16243 APPLIER ORA-16116: no work available\r\n 146 63 16247 APPLIER ORA-16113: applying change to table or sequence \"IPTVMWC\".\"SEADAC_NOTIFICATION\"\r\n 204 409 16251 APPLIER ORA-16116: no work available\r\n 16 281 16255 APPLIER ORA-16116: no work available\r\n 155 61 1511 APPLIER ORA-16116: no work available\r\n 215 83 1515 APPLIER ORA-16116: no work available\r\n 29 215 1519 APPLIER ORA-16116: no work available\r\n 195 2129 16209 READER ORA-16127: stalled waiting for additional transactions to be applied\r\n 152 275 16213 BUILDER ORA-16127: stalled waiting for additional transactions to be applied\r\n 209 85 16217 PREPARER ORA-16127: stalled waiting for additional transactions to be applied\r\n 22 2281 1619 PREPARER ORA-16127: stalled waiting for additional transactions to be applied\r\n 77 7867 1623 PREPARER ORA-16127: stalled waiting for additional transactions to be applied\r\n\r\n15 rows selected.\r\n<\/pre>\n3. Check if there are any APPLIER processes in IDLE.<\/p>\n
[Logical-1] SQL> SELECT COUNT(*) AS IDLE_APPLIER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16116;\r\n\r\nIDLE_APPLIER\r\n------------\r\n 7\r\n<\/pre>\n4. How much of the LCR Size is used is checked.<\/p>\n
[Logical-1] SQL> select name,(least(max_sga,bytes)\/max_sga) * 100 pct_utilization \r\n 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');\r\n\r\nNAME PCT_UTILIZATION\r\n-------------------------- ---------------\r\nLogminer LCR c 100\r\n<\/pre>\n5. Let’s see if there are any of the PREPARER Processes in IDLE.<\/p>\n
[Logical-1] SQL> SELECT COUNT(*) AS IDLE_PREPARER FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'PREPARER' and status_code = 16116;\r\n\r\nIDLE_PREPARER\r\n-------------\r\n 0\r\n<\/pre>\nIn the light of all this information, the following conclusion is reached.<\/p>\n
The number of APPLIER processes is sufficient.<\/p>\n
The number of preparer processes is not sufficient.<\/p>\n
LCR Cache Size is not enough.<\/p>\n
<\/p>\n
<\/div>\n