HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

Step by Step Process to Resolved gap on Standby database.

Summary
1. Check the name and status of database.
2. Check for GAP on standby
3. Check redo received on standby
4. Check redo applied on standby
5. Identify missing archive log files
6. Copy archive log files
7. Register archive log files with standby
8. Restart the managed recovery operations

******************************************************************************************************************

Step 1 : Check the status of database on both server.
On Primary Server.
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   READ WRITE PRIMARY

SQL> set sqlprompt “PRIMARY’@’_connect_identifier>”
PRIMARY@MYDB>

On Standby Server.

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   MOUNTED    PHYSICAL STANDBY

SQL> set sqlprompt “SECONDARY’@’_connect_identifier>”
STANDBY@MYDB>

Step 2 : Check for GAP on Standby
PRIMARY@MYDB>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
         76921
STANDBY@MYDB>select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
         76921

STANDBY@MYDB>SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
 FROM V$ARCHIVED_LOG
          WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
          ORDER BY 1;
    Thread Last Sequence Generated
———- ———————–
         1                   76921



Step 3 & 4: Check redo received and applied on standby.

STANDBY@MYDB> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
    Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
         1                  76922                 20931      55991

Step 5: Identify the missing archive log file.

 STANDBY@MYDB>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected

—-If found gap

Step 6: Copy missing archive log file
After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:
PRIMARY@MYDB>  SELECT name
 FROM v$archived_log
 WHERE thread# = 1
 AND dest_id = 1
 AND sequence# BETWEEN 20931 and 76922;
Output:
/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc
56027 rows selected.

Step 7: Register archive logfile with standby.
Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE … SQL statement on the physical standby database.
For example:
STANDBY@MYDB> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;

Step 8: Restart the managed recovery operations.
— After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations.
  For example, to put the physical standby database into automatic recovery managed mode:
STANDBY@MYDB> alter database recover managed standby database disconnect from session;

WHAT IS SCAN IN RAC ?

WHAT IS SCAN IN RAC ?

Single Client Access Name (SCAN) is a feature used in Oracle RAC environments that provides a single name for clients to access any Oracle Database running in a cluster.The main benefit is that the client’s connect information does not need to change if you add or remove nodes or databases in the cluster.

In versions prior to Oracle 11.2, an entry in the tnsnames.ora file for a n-node RAC database always referenced all
nodes in the ADDRESS_LIST section, as in the listing that follows:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = tcp)(HOST = rac1-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac2-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac3-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac4-vip.localdomain)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testing)
)
)

The use of a SCAN address removes this problem, instead of addressing every single node as before, the SCAN virtual IP addresses refer to the cluster. Using the SCAN, the preceding connection entry is greatly simplified. Instead of listing each node’s virtual IP address, all we need to do is enter the SCAN rac-scan.localdomain.com. Here’s the simplified version:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = rac-scan.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testing)
)
)

SCAN

Now in the following post we’ll understand how this SCAN concept manages the connections internally, for that we first need to understand the set-up of SCAN.

SCAN configuration using DNS (Domain Name Service)

For this you need to have a DNS entry with one single name that resolves to 3 IP addresses using a round robin algorithm. The IP addresses must be on the same subnet as your default public network in the cluster (In 12c R1 SCAN would support multiple subnets).

SCAN configuration using GNS (Grid Name Service)

In this case Oracle assumes you use some form of dynamic IP assignment on your public network, so you just need to mention the SCAN name and 3 IPs will be acquired from either a DHCP service or SLAAC when using IPv6 based IP address.

Round Robin Algorithm

Round-robin on DNS level allows for a connection request load balancing across SCAN listeners floating in the cluster.  If your DNS is set up to provide round-robin access to the IPs resolved by the SCAN entry, then run the “nslookup” command at least twice to see the round-robin algorithm work. The result should be that each time, the “nslookup” would return a set of three IPs in a different order.

1) nslookup rac-scan

Server: 192.168.46.1
Address: 192.168.46.1#53

Non-authoritative answer:
Name: rac-scan.localdomain
Address: 192.168.46.30
Name: rac-scan.localdomain
Address: 192.168.46.31
Name: rac-scan.localdomain
Address: 192.168.46.32

2) nslookup rac-scan

Server: 192.168.46.1
Address: 192.168.46.1#53

Non-authoritative answer:
Name: rac-scan.localdomain
Address: 192.168.46.31
Name: rac-scan.localdomain
Address: 192.168.46.32
Name: rac-scan.localdomain
Address: 192.168.46.30

Three IP addresses are recommended considering load balancing and high availability requirements regardless of the number of servers in the cluster.

SCAN (Single Client Access Name)

The CRSd process starts orarootagent & oraagent processes,  orarootagent is responsible for starting Node VIP, SCAN VIP and oraagent process is responsible for starting SCAN Listener and Node Listener.

For each of the 3 IP addresses that the SCAN resolves to, a SCAN VIP resource is created and a SCAN Listener is created. The SCAN Listener is dependent on the SCAN VIP and the 3 SCAN VIPs (along with their associated listeners) will be dispersed across the cluster.REMOTE_LISTENER parameter is set to the SCAN by default this allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load and a recommendation on how many incoming connections should be directed to the instance.

The LOCAL_LISTENER parameter is set to the node-VIP,  the node listener run out of the Oracle Grid Infrastructure home and listens on the node-VIP using the specified port (default port is 1521).

So up till now we understood, SCAN configuration, SCAN IPs, SCAN Listener & Node Listener and how the Instances provide relevant information to SCAN Listeners, now we’ll understand the Client Side Load balancing in RAC using SCAN which would further explain another major benefit of using SCAN.

Connection Load Balancing

loadbalancing

Client Side Load Balancing – For clients connecting using Oracle SQL*Net 11g Release 2 and above, 3 IP addresses will be received by the client by resolving the SCAN name through DNS. The client will then go through the list it receives from the DNS and try connecting through one of the IPs received. If the client receives an error, it will try the other addresses before returning an error to the user or application. This is similar to how client connection failover works in previous releases when an address list is provided in the client connection string.

Server Side Load Balancing – When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance providing the requested service. It will then re-direct the connection request to the local listener on the node where the least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener will finally create the connection to the database instance.

I hope the above information helps you in understanding the SCAN Concept, how it is configured and how it is used for client/server side connect time load balancing.