Node 1 Does Not Support Connections (RAC)

This morning I have been reported a problem in a test environment. Some connections were hung when trying to enter. After connecting and taking a look, the environment, it is a cluster of two nodes version 12c, note that node one is the one that does not support connections and the connections were left is waiting for a change of log. It’s funny but it did not give any error, it’s usually this situations shows a ora-00257, but this is not the case, maybe because of the engine version.

Checking the situation at the instance level;

 
SQL> select inst_id, version, status, thread#, archiver, log_switch_wait, logins from gv$instance

   INST_ID VERSION           STATUS          THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS
---------- ----------------- ------------ ---------- ------- --------------- ----------
         2 12.1.0.2.0        OPEN                  2 STARTED                 ALLOWED
         1 12.1.0.2.0        OPEN                  1 STARTED ARCHIVE LOG     ALLOWED

 
Sessions are waiting on event “log file switch (archiving needed)” like show below, first column is node (in this case the problem is in node 1);

 
N     SID TIME    SQL_ID            EXECS EVENT                              Marker
- ------- ------- -------------- -------- ---------------------------------- --------
1      22 0       6vm7g6qj4mqhd        0  PX Deq: Execution Msg
2     327 0       6vm7g6qj4mqhd        4  PX Deq: Execution Msg
2     649 0       6vm7g6qj4mqhd        4  PX Deq: Execute Reply
1     361 1471    6228pzdt28kzd     1008  log file switch (archiving needed)      <<<
2     121 106     61tssjb6hj8x7      329  gc buffer busy acquire
1      88 1645    9zg9qd9bm4spu     7787  log file switch (archiving needed)      <<<
1     804 1704    3mptsg6h27zg9        1  log file switch (archiving needed)      <<<
1      89 1705    0bfdn75zn75pw        2  log file switch (archiving needed)      <<<
2      20 1699    6nauzjpthp1w7        3  db file sequential read
1       3 +1H     1aa2fpqtx557g     3877  log file switch (archiving needed)      <<<
1     328 1705    g8bkp70myp46t        2  log file switch (archiving needed)      <<<
1     257 +1H     dr6d1upgkc1g3        1  log file switch (archiving needed)      <<<
2     615 505     aq8yqxyyb40nn     1255  gc current request
1     800 817     aq8yqxyyb40nn     1282  buffer busy waits
1     480 817     aq8yqxyyb40nn     1282  buffer busy waits
2     342 +1H     5ms6rbzdnq16t    15644  gc buffer busy acquire
1     377 817     aq8yqxyyb40nn     1282  buffer busy waits
1     345 817     aq8yqxyyb40nn     1282  log file switch (archiving needed)      <<<
1      21 817     aq8yqxyyb40nn     1282  buffer busy waits

 
After perform archive backup (with delete option);

 
SQL> select inst_id, version, status, thread#, archiver, log_switch_wait, logins from gv$instance

   INST_ID VERSION           STATUS          THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS
---------- ----------------- ------------ ---------- ------- --------------- ----------
         2 12.1.0.2.0        OPEN                  2 STARTED                 ALLOWED
         1 12.1.0.2.0        OPEN                  1 STARTED                 ALLOWED

 
HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s