The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. After resolving the gap, query the V$ARCHIVE_GAP view again on the physical standby database to determine if there is another gap sequence. The asynchronous redo transport mode transmits redo data asynchronously with respect to transaction commitment. (Real-time cascading is supported on all destinations.). Forced logging prevents unlogged direct writes in a database. Create a physical standby database at each of your European sites. Oracle recommends that the NET_TIMEOUT attribute be specified whenever the synchronous redo transport mode is used, so that the maximum duration of a redo source database stall caused by a redo transport fault can be precisely controlled. Perform the following query on a redo source database to display the response time histogram for destination 2: Perform the following query on a redo source database to display the slowest response time for destination 2: Perform the following query on a redo source database to display the fastest response time for destination 2: The highest observed response time for a destination cannot exceed the highest specified NET_TIMEOUT value specified for that destination, because synchronous redo transport mode sessions are terminated if a redo transport destination does not respond to a redo transport message within NET_TIMEOUT seconds. You can use Oracle wait events to track redo transport wait time on a redo source database. Redo transport compression is a feature of the Oracle Advanced Compression option. The connect descriptor must specify that a dedicated server connection be used, unless that is the default connection type for the redo transport destination. The view displays the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters. Zero Data Loss Recovery Appliance (Recovery Appliance). For administrative ease, Oracle recommends that the REDO_TRANSPORT_USER parameter be set to the same value on the redo source database and at each redo transport destination. It must have been mounted at least once in read-write mode. Each redo transport destination is individually configured to receive redo data via one of two redo transport modes: The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. SSL is automatically used for redo transport authentication between two Oracle databases if: The databases are members of the same Oracle Internet Directory (OID) enterprise domain and that domain allows the use of current user database links. In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database. The following example uses all of the LOG_ARCHIVE_DEST_n attributes described in this section. An archive log repository does not contain data files, so it cannot support role transitions. This destination type is used for temporary offsite storage of archived redo log files. A Data Guard configuration can be set up to cascade to a single physical standby or to multiple physical standbys. For example: A query based on the DBA_LOGSTDBY_LOG view on a logical standby database, as specified above, only returns the gap that is currently blocking SQL Apply from continuing. Redo transport destination must be configured to receive and to archive redo data from a redo source database. Copy these log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE. When a password file is used for redo transport authentication, the password of the user account used for redo transport authentication is compared between the database initiating a redo transport session and the target database. Real-time cascade enables a cascaded standby database to provide nearly the same level of data protection as any standby database that receives redo directly from a primary database using asynchronous redo transport. When a log switch occurs on the redo source database, incoming redo is then written to the next standby redo log group, and the previously used standby redo log group is archived by an ARCn background process. We checked and found this file is and next 40 files are missing from primary as well as standby site. See LOG_ARCHIVE_DEST_n Parameter Attributes for a full description of all LOG_ARCHIVE_DEST_n parameter attributes. Although there is no limit on the distance between a primary database and a SYNC redo transport destination, transaction commit latency increases as network latency increases between a primary database and a SYNC redo transport destination. There is a LOG_ARCHIVE_DEST_STATE_n database initialization parameter (where n is an integer from 1 to 31) that corresponds to each LOG_ARCHIVE_DEST_n parameter. We tried to restart the database but it did not help. The AFFIRM attribute is used to specify that redo received from a redo source database is not acknowledged until it has been written to the standby redo log. For cost and performance reasons, you do not want to maintain network links from North America to each of your European sites.
The most recently archived redo log file should be the same for each destination. Normal Standby Recovery scenario- Due to Some network glitch connectivity between primary and standby was broken and frequest alerts were received for standby gap issue. Data Protection Considerations for Cascading Standbys. The procedure used to create an archive log repository is identical to the procedure used to create a physical standby database, except for the copying of data files. Configure the other two physical standby databases as terminal destinations of the cascading standby database configured in step 4. Configure the local standby database as a. Configure the remote physical standby database as a terminal destination of the local standby database. Monitoring Synchronous Redo Transport Response Time. To validate an Oracle Data Guard configuration after you create it, query the V$DATAGUARD_CONFIG view from any database in the configuration. The query also does not identify the gap that may exist at the tail end for a given thread. The DB_UNIQUE_NAME attribute is used to specify the DB_UNIQUE_NAME of a redo transport destination. See Oracle Streams Concepts and Administration for more information about Oracle Streams downstream capture databases. Perform the following query on a redo source database to determine the size of each log file and the number of log groups in the redo log: Perform the following query on a redo destination database to determine the size of each log file and the number of log groups in the standby redo log: If the redo source database is an Oracle Real Applications Cluster (Oracle RAC) or Oracle Real Application Clusters One Node (Oracle RAC One Node) database, query the V$LOG view at the redo source database to determine how many redo threads exist and specify the corresponding thread numbers when adding redo log groups to the standby redo log. Oracle recommends that the VALID_FOR attribute be specified for each redo transport destination at every site in an Oracle Data Guard configuration so that redo transport services continue to send redo data to all standby databases after a role transition, regardless of which standby database assumes the primary role. In this scenario, you have a mission-critical primary database. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every enabled redo transport destination that uses the synchronous redo transport mode. After resolving the gap, query the DBA_LOGSTDBY_LOG view again on the logical standby database to determine if there is another gap sequence. Once the password file is up-to-date at the far sync instance the redo containing the password update at the primary is automatically propagated to any standby databases that are set up to receive redo from that far sync instance. It was showing wait_for gap-, We used following query on primary database to find what is the archive file name with this log sequence-. Create a physical standby database at a site that is sufficiently remote to provide protection against regional disasters at the primary and local standby database sites. In both cases the primary remains in zero data loss protection mode because one synchronous standby has acknowledged receipt of the redo. A redo gap occurs whenever redo transmission is interrupted. An Oracle Data Guard configuration requires that Oracle redo transport services be configured and monitored. See Before You Patch or Upgrade the Oracle Database Software for information about how to handle cascaded redo transport destinations during an Oracle Database upgrade. The data for each destination consists of a series of rows, with one row for each response time. The REOPEN attribute is used to specify the minimum number of seconds between automatic reconnect attempts to a redo transport destination that is inactive because of a previous error. Table 7-2 lists several of these Oracle wait events, which are found in the V$SYSTEM_EVENT dynamic performance view.
Cascading has the following restrictions: Only physical standby databases can cascade redo. Redo transport services performs the automated transfer of redo data between members of an Oracle Data Guard configuration. So in the preceding example, because Chicago is an ASYNC destination, it could be either a standby database or a Recovery Appliance. It must also match the value of the DB_UNIQUE_NAME database initialization parameter at the redo transport destination. Each row contains four columns: FREQUENCY, DURATION, DEST_ID, and TIME. For instance, if the primary database has generated archived logs up to sequence 100 for thread 1, and the latest archived log that the logical standby database has received for the given thread is the one associated with sequence 77, then this query does not return any rows, although there is a gap for the archived logs associated with sequences 78 to 100. In that case, there is no actual gap, but the associated thread was disabled and enabled within the time period of generating these two archived logs. Select a physical standby database to configure as a cascading standby database. Any failed synchronous standbys are reconnected as normal after the number of seconds specified for the REOPEN attribute have passed. Oracle Streams downstream capture databases. This database has stringent performance and data protection requirements, so you have decided to deploy a local physical standby database to provide zero data loss protection and a remote, cascaded physical standby database to protect against regional disasters at the primary and local standby database sites. See Monitoring Synchronous Redo Transport Response Time for information about monitoring synchronous redo transport mode response time. The ASYNC attribute specifies that the asynchronous redo transport mode be used to send redo data to a redo transport destination. 
If an acknowledgement is not received within NET_TIMEOUT seconds, the redo transport connection is terminated and an error is logged. For a complete list of the Oracle wait events used by redo transport, see the Oracle Data Guard Redo Transport and Network Best Practices white paper on the Oracle Maximum Availability Architecture (MAA) home page at: Total time spent waiting for redo transport sessions to be established to all ASYNC and SYNC redo transport destinations, Total time spent waiting for redo data to be written to all ASYNC and SYNC redo transport destinations, Total time spent waiting for redo transport connections to be terminated to all ASYNC and SYNC redo transport destinations. If it is not, a status other than VALID may identify an error encountered during the archival operation to that destination. (If there are no gaps, then the query shows only one file for each thread.) A physical standby database that cascades primary database redo to one or more terminal destinations at the same time it is applying changes to its local database files is known as a cascading standby database. Each database has an Oracle wallet or a supported hardware security module that contains a user certificate with a distinguished name (DN) that matches the DN in the OID entry for the database. 
See the Oracle Data Guard Redo Transport and Network Configuration Best Practices white paper available on the Oracle Maximum Availability Architecture (MAA) home page at: Oracle Streams Concepts and Administration, Oracle Database Enterprise User Security Administrator's Guide, Oracle Label Security Administrator's Guide, Oracle Database Net Services Administrator's Guide. This is the default. This section briefly describes the most commonly used attributes. The MAX_CONNECTIONS attribute of the LOG_ARCHIVE_DEST_n parameter can be used to specify that more than one network session be used to send the redo needed to resolve a redo gap. The following sample SQL statements create a standby redo log at a database that is to receive redo from a redo source database that has two redo threads: Whenever a redo log group is added to a primary database, a log group must also be added to the standby redo log of each standby database in the configuration. The password must be the same at both databases to create a redo transport session. How to Integrate HashiCorp Vault with Jenkins to secure yoursecrets, CLUSTERWARE PROCESSES in 11g RAC R2Environment, Oracle RAC 11gR2 Voting Disk & OCRBackup, Automatic Segment Space Management andLMT, Datapatch fails with ORA-01017, as it only connects to the database with OSAuthentication, Perl lib version v5.8.3 doesnt match executable versionv5.10.0, ORA-00054 resource busy and acquire with NOWAIT specified or timeoutexpired, How to change the Redo Log File size in OracleDatabase, Applying PSU patch in an Oracle 12c DataguardEnvironment, Duplicate Database and the location of thedatafiles, Get lsinventory with SQL statement in12c, Patching Oracle 12c Multi-tenant to latest PSU now known asDPBP, Create CDB Common User Without Using C##Prefix, Monitoring Wait Statistics in Oracledatabase, How to find Table Fragmentation in Oracle Database, SQL script to check available space in your recoveryarea (db_recovery_file_dest_size), ORA-09945: Unable to initialize the audit trail file. Then we transferred the files manually to standby site as it was not picking the file automatically-, Once the files got transferred to standby site we manually registered the archive files to standby site-, Standby database started applying the log files but it got stuck at particular log seq and was not moving forward-. The TIME column contains a timestamp taken when the row was last updated. Perform the following query at the redo source database to find out if any log files are missing at the redo transport destination: The V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination. The DURATION column corresponds to the response time. Oracle Database Security Guide for more information about SSL, Oracle Database Enterprise User Security Administrator's Guide for more information about administering enterprise domains, Oracle Label Security Administrator's Guide for information about administering Oracle Internet Directory. The SERVICE attribute is used to specify the Oracle Net service name used to connect to the redo transport destination. Repeat this process until there are no more gaps. Perform the following query on the redo source database to determine the most recently archived sequence number for each thread: Perform the following query on the redo source database to determine the most recently archived redo log file at each redo transport destination: Perform a query at a redo source database to find out if an archived redo log file has been received at a particular redo transport destination. To simplify record keeping, response times are rounded up to the nearest whole second for response times less than 300 seconds. Non-real-time cascading is supported on destinations 1 through 10 only. The next three examples show example queries for destination 2, which corresponds to the LOG_ARCHIVE_DEST_2 parameter. The time needed to resolve a redo gap is directly proportional to the size of the gap and inversely proportional to the effective throughput of the network link between the redo source database and the redo transport destination. The database must be mounted, but not open. For example, suppose you have three synchronous standby destinations and you set DATA_GUARD_SYNC_LATENCY to a value of 2. Redo received from another Oracle database via redo transport is written to the current standby redo log group by a remote file server (RFS) foreground process. However, although redo is forwarded in real-time, the fact that there is a second network hop creates the potential for additional data loss if an outage prevents all redo from reaching the terminal destination. Redo received by a standby database is written directly to an archived redo log file if a standby redo log group is not available or if the redo was sent to resolve a redo gap. The V$ARCHIVE_DEST view can be queried to see the current settings and status for each redo transport destination. Response times greater than 300 seconds are round up to 600, 1200, 2400, 4800, or 9600 seconds. If database compatibility is set to 12.2 or higher on both the source and target database, then the copy of the password file is automatically refreshed whenever an administrative privilege (SYSDG, SYSOPER, SYSDBA, and so on) is granted or revoked, and after the password of any user with administrative privileges is changed. Destinations 11 through 31 operate only in ASYNC (real-time) transport mode. On the cascading standby database, configure the, On the cascading standby database, configure a, At the terminal destination, configure the. When your configuration includes cascading standbys, each destination should have a LOG_ARCHIVE_DEST_n parameter defined that points back to its source for use during a failover. The password file is updated on the standby when the redo is applied. Configuring an Oracle Database to Send Redo Data, Configuring an Oracle Database to Receive Redo Data. Redo transport compression can significantly improve redo transport performance on network links with low bandwidth and high latency. When redo transmission resumes, redo transport services automatically detects the redo gap and resolves it by sending the missing redo to the destination. The VALID_FOR attribute is used to specify when redo transport services transmits redo data to a redo transport destination. The synchronous and asynchronous redo transport modes require that a redo transport destination have a standby redo log. Cases Where Redo Is Written Directly To an Archived Redo Log File. A cascading standby database can cascade primary database redo to up to 30 terminal destinations. Perform the following query at the physical standby database to determine if there is redo gap on a physical standby database: The output from the previous example indicates that the physical standby database is currently missing log files from sequence 7 to sequence 10 for thread 1. By default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used.
This transport mode is used by the Maximum Performance data protection mode described in Oracle Data Guard Protection Modes . If you do not specify a transport mode or you specify SYNC on destinations 1 through 10, then redo is shipped in non-real-time. Take the following steps to monitor redo transport status on a redo source database. The LOG_ARCHIVE_DEST_n, and FAL_SERVER database initialization parameters that correspond to the databases use Oracle Net connect descriptors configured for SSL.
Each destination has an ID number associated with it. If one or both respond within 2 seconds, then they are maintained as active destinations. This parameter is used to enable or disable the corresponding redo destination. This clause puts the database in the (limited) upgrade/migrate mode in which the some functionality is missing, such as, alter database start logical standby apply immediate, alter database recover managed standby database.
These redo transport sessions are authenticated using either the Secure Socket Layer (SSL) protocol or a remote login password file. The response time data is maintained for redo transport messages sent via the synchronous redo transport mode. You can achieve the objectives described above by performing the following steps: These steps provide an example of cascading to multiple physical standby databases. You can optimize redo transport for best performance. When this occurs, redo is written to the location specified by the LOCATION attribute of one LOG_ARCHIVE_DEST_n parameter that is valid for archiving redo received from another database. If the DB_UNIQUE_NAME attribute is specified, its value must match one of the DB_UNIQUE_NAME values in the DG_CONFIG list. Oracle databases must be configured before they can send and receive redo data. The SYNC attribute specifies that the synchronous redo transport mode be used to send redo data to a redo transport destination. We logged in the database and verified the current status of the standby database.
The output shows that the highest registered file is sequence number 10, but there is a gap at the file shown as sequence number 6: Copy the missing log files, with sequence numbers 7, 8, and 9, to the logical standby system and register them using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement.
The NET_TIMEOUT attribute specifies how long the LGWR process waits for an acknowledgement that redo data has been successfully received by a destination that uses the synchronous redo transport mode. If the SSL authentication requirements are not met, then each database must use a remote login password file. The NOAFFIRM attribute is used to specify that received redo is acknowledged without waiting for received redo to be written to the standby redo log. Oracle Database Administrator's Guide for more information creating and maintaining remote login password files. Table 7-1 shows the valid values that can be assigned to this parameter. This destination becomes enabled if communication to its associated destination fails. The process of sequentially filling and then archiving redo log file groups at a redo source database is mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups. This would allow redo cascading to database denver to continue if a switchover is performed between database boston and database boston2. This means we have to restore all the missing files from tape. The DB_UNIQUE_NAME attribute must be specified if the LOG_ARCHIVE_CONFIG database initialization parameter has been defined and its value includes a DG_CONFIG list. Assume that destination 1 points to the local archived redo log and that destination 2 points to a redo transport destination. You must purchase a license for this option before using the redo transport compression feature.