Problems discovering Infiniband Switches in Cloud Control 13c

We you buy an Exadata machine, you will probably use Oracle Could Control to manage the system and all the databases you are going to have inside.

In order to use it, you need to discover the Exadata Rack in Cloud Control.

The best approach to do this is to make a prerequisites validation using exadataDiscoveryPreCheck.pl script.

I have a customer trying to discover an Exadata Rack in Cloud Control 13c. Process was failing because an issue trying to validate the infiniband switches.

 

Trying to execute the prerequisites validation using exadataDiscoveryPreCheck.pl script, we found this issue:

 

 

Verifying Infiniband Switch version...
--------------------------------------
  Verifying version for xfpnhiddb001-iba1.uk.tsb infiniband switch...
  Could not invoke command version using SSH ===> Not ok
   * Please check the password and host status.
     Additionally please check that SSH is not blocked by a firewall.
  Verifying version for xfpnhiddb001-ibb1.uk.tsb infiniband switch...
   Could not invoke command version using SSH ===> Not ok
   * Please check the password and host status.
     Additionally please check that SSH is not blocked by a firewall.

When we tried to execute the version command manually from this host, it seems to work correctly

[oracle@xfpnhiddb001dbadm01:aci02pro ~]$ ssh nm2user@xfpnhiddb001-ibb1.uk.tsbversion
Password:
SUN DCS 36p version: 2.2.4-3
Build time: Dec  6 2016 13:08:04
SP board info:
Manufacturing Date: 2015.03.26
Serial Number: "NCDIW0345"
Hardware Revision: 0x0200
Firmware Revision: 0x0000
BIOS version: SUN0R100
BIOS date: 06/22/2010

[oracle@xfpnhiddb001dbadm01:aci02pro ~]$ ssh nm2user@xfpnhiddb001-iba1.uk.tsbversion
Password:
SUN DCS 36p version: 2.2.4-3
Build time: Dec  6 2016 13:08:04
SP board info:
Manufacturing Date: 2015.03.27
Serial Number: "NCDIW0176"
Hardware Revision: 0x0200
Firmware Revision: 0x0000
BIOS version: SUN0R100
BIOS date: 06/22/2010

We were trying to execute the validation in the same subnet and I set the password in a parameter file to avoid mistyping…, but the issue was still there.

Reviewing the exadataDiscoveryPreCheck.pl I saw that it is using ssh command in this way:

/usr/bin/ssh -o StrictHostKeyChecking=no -o ConnectTimeout=30  -o PreferredAuthentications=password -o NumberOfPasswordPrompts=1 nm2user@xfpnhiddb001-ibb1.uk.tsb version

 

Debugging this command I found that PreferredAuthentications=password was not a valid Authentication method for ssh in for the Infiniband …

[oracle@xfpnhiddb001dbadm01:aci02pro ~]$ /usr/bin/ssh -v -o StrictHostKeyChecking=no -o ConnectTimeout=30  -o PreferredAuthentications=password -o NumberOfPasswordPrompts=1 nm2user@xfpnhiddb001-ibb1.uk.tsb version
OpenSSH_5.3p1, OpenSSL 1.0.1e-fips 11 Feb 2013
debug1: Connecting to xfpnhiddb001-ibb1.uk.tsb [10.184.196.19] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /home/oracle/.ssh/identity type -1
debug1: identity file /home/oracle/.ssh/identity-cert type -1
debug1: identity file /home/oracle/.ssh/id_rsa type 1
debug1: identity file /home/oracle/.ssh/id_rsa-cert type -1
debug1: identity file /home/oracle/.ssh/id_dsa type -1
debug1: identity file /home/oracle/.ssh/id_dsa-cert type -1
debug1: identity file /home/oracle/.ssh/id_ecdsa type -1
debug1: identity file /home/oracle/.ssh/id_ecdsa-cert type -1
debug1: Remote protocol version 2.0, remote software version OpenSSH_5.3
debug1: match: OpenSSH_5.3 pat OpenSSH*
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_5.3
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-sha1 none
debug1: kex: client->server aes128-ctr hmac-sha1 none
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<2048<8192) sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_GROUP
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: expecting SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host 'xfpnhiddb001-ibb1.uk.tsb' is known and matches the RSA host key.
debug1: Found key in /home/oracle/.ssh/known_hosts:19
debug1: ssh_rsa_verify: signature correct
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_REQUEST sent
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,keyboard-interactive
debug1: No more authentication methods to try.
Permission denied (publickey,keyboard-interactive).

 

This is caused due to the setting PasswordAuthentication no in the IB Switch’s /etc/ssh/sshd_config file, which disables clear text password authentication:

 

#cat /etc/ssh/sshd_config

# To disable tunneled clear text passwords, change to no here!
 #PasswordAuthentication yes
 # NM2 change - set PermitEmptyPasswords to no
 PermitEmptyPasswords no
 # NM2 change - set PasswordAuthentication to no
 PasswordAuthentication no

 

After changing this value to PasswordAuthentication yes it worked correctly the Exadata could be discovered.

Truths and Lies about DB_LOST_WRITE_PROTECT

The name of this parameter always makes me think that if it is set, the system will be protected against lost writes, so it will prevent occurring.

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage, but knowing that I/O subsystem is not part of the Oracle Database code, you may  wonder, “how Oracle is able to protect against a lost write when it is not responsible for writing“. Well, in my opinion, the name of this parameter is misguided, because it is not preventing lost writes.

So why use it? When you have a lost write situation, the sooner you detect it, the better, and this parameter is very useful to detect lost write as soon as possible and will help you to, under some circumstances, be able to repair the lost write and at the end, avoid loosing any data, but it will not protect your system against it.

In order to understand how this parameter works, we need to understand first how redo works.

When a database block change is generated by any DML operation, Oracle makes this change in two ways:

  • Logical: Oracle makes a description of the change that is going to be performed including in this description which block version is changing (original scn) when the change happens (scn of the change) and what changes are performed (the changes themself). This information is stored in the redo log files and allow Oracle to re-execute the change when a recover process happens.
  • Physical: The changes are performed in the block itself.

Using the logical description of the change stored in the redo log files, during recover process and before applying the change in the block, Oracle verifies if the block that is going to be modified is the right one, or, to be more precise, if it is from the right version.

Now, what happens if it is not. Lets see an example in a Dataguard configuration: I’m going to simulate a lost write and we can see what happens when a new change for the block that suffered the lost write is applied in standby database.

First, we identify a row and we take a copy of the block before applying any change

SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      12345

SYS@db121rac1> @rowid
Enter value for rid: 'AAAWZpAAKAAAADPAAA'
old  10:   dbms_rowid.rowid_info(&rid,ridtyp,objnum,relfno,blno,rowno,'SMALLFILE');
new  10:   dbms_rowid.rowid_info('AAAWZpAAKAAAADPAAA',ridtyp,objnum,relfno,blno,rowno,'SMALLFILE');
Row Typ-1
Obj No-91753
RFNO-10
Block No-207
Row No-0 

PL/SQL procedure successfully completed.

SYS@db121rac1> select file_name from dba_data_files where relative_fno=10;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/db121/example01.dbf

SYS@db121rac1>
SYS@db121rac1> exit
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=bloque_antes count=1 skip=207
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000167299 s, 49.0 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=block_before_change.blk count=1 skip=207
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0001396 s, 58.7 MB/s

Now, we update the row and propagate the change to our standby database

[oracle@oel7-rac1 lost_write]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 11 09:50:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Session altered.

SYS@db121rac1> update hr.employees set salary=54321 where employee_id=198;

1 row updated.

SYS@db121rac1> commit;

Commit complete.

SYS@db121rac1> alter system switch logfile;

System altered.
SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      54321

After that, using the copy of the block before the change, we overwrite the datafile simulating a lost write.

SYS@db121rac1> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@db121rac1> exit
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=first_piece.dbf count=207
207+0 records in
207+0 records out
1695744 bytes (1.7 MB) copied, 0.0106201 s, 160 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=second_piece.dbf skip=208
158993+0 records in
158993+0 records out
1302470656 bytes (1.3 GB) copied, 6.25686 s, 208 MB/s
[oracle@oel7-rac1 lost_write]$ cat first_piece.dbf block_before_change.blk second_piece.dbf >example.dbf
[oracle@oel7-rac1 lost_write]$ ls -l example.dbf
-rw-rw-r-- 1 oracle oracle 1304174592 Apr 11 10:06 example.dbf
[oracle@oel7-rac1 lost_write]$ ls -l /u01/app/oracle/oradata/db121/example01.dbf
-rw-r----- 1 oracle oracle 1304174592 Apr 11 09:54 /u01/app/oracle/oradata/db121/example01.dbf
SYS@db121rac1> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SYS@db121rac1> select rowid, employee_id, salary from hr.employees where employee_id=198;

ROWID              EMPLOYEE_ID     SALARY
------------------ ----------- ----------
AAAWZpAAKAAAADPAAA         198      12345    <-----Change is now lost!

 

So now, when we modify the block, Standby Database will realize that the source version of the block is not matching with the version it is trying to modify.

SYS@db121rac1> update hr.employees set salary=salary+1 where employee_id=198;

1 row updated.

SYS@db121rac1> commit;

Commit complete.      <----Now ORA-600 [3020] is reported in standby
Mon Apr 11 10:13:47 2016
ERROR: ORA-00600: internal error code, arguments: [3020] recovery detected a data block with invalid SCN raised at location:kcbr_media_ap_1
Mon Apr 11 10:13:47 2016
Checker run found 1 new persistent data failures
Errors in file /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/trace/db121rac2_pr04_2897.trc  (incident=24505):
ORA-00600: internal error code, arguments: [3020], [5], [207], [41943247], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 207, file offset is 1695744 bytes)
ORA-10564: tablespace EXAMPLE
ORA-01110: data file 5: '/u01/app/oracle/oradata/db121/example01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 91753
Incident details in: /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/incident/incdir_24505/db121rac2_pr04_2897_i24505.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Slave exiting with ORA-600 exception

 

This means that because of the way the software is designed, lost writes detection is implicit in the code, but it is done only when a new change is performed on the block that suffered the issue.

So, what is the difference when DB_LOST_WRITE_PROTECT is set?

Well, the difference is that when it is set, every SELECT statement will generate and small redo change saying “I’ve read this block and it is this version” and will propagate this information to Standby Databases allowing it to detect the lost write when the block is read. If this behavior happens, you can restore and recover the block in Primary database and this will fix the lost write.

Lets see it in action. We repeat exactly the same steps as before but now after setting the parameter to typical.

ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SYS@db121rac1> alter system set db_lost_write_protect=typical;

System altered.

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;

ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      12346

SYS@db121rac1> update hr.employees set salary=54321 where employee_id=198;             

1 row updated.
SYS@db121rac1> commit;

Commit complete.

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;
ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      54321

Now we introduce the lost write as we did before

[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=first_piece.dbf count=207
207+0 records in
207+0 records out
1695744 bytes (1.7 MB) copied, 0.0105966 s, 160 MB/s
[oracle@oel7-rac1 lost_write]$ dd bs=8192 if=/u01/app/oracle/oradata/db121/example01.dbf of=second_piece.dbf skip=208
158993+0 records in
158993+0 records out
1302470656 bytes (1.3 GB) copied, 2.4282 s, 536 MB/s
[oracle@oel7-rac1 lost_write]$ cat first_piece.dbf block_before_change.blk second_piece.dbf >example.dbf
[oracle@oel7-rac1 lost_write]$ cp example.dbf /u01/app/oracle/oradata/db121/example01.dbf
[oracle@oel7-rac1 lost_write]$ sqlplus / as sysdba
SYS@db121rac1> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size            1157631520 bytes
Database Buffers          469762048 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Now lets read the block

SYS@db121rac1> select rowid, salary from  hr.employees where employee_id=198;

ROWID                  SALARY
------------------ ----------
AAAWZpAAKAAAADPAAA      12345   <----Now standby database reports ORA-752

And this is what we see in the alert.log for the Standby Database

Standby redo application has detected that the primary database lost a disk write.
No redo at or after SCN 2004484 can be used for recovery.
BLOCK THAT LOST WRITE 207, FILE 5, TABLESPACE# 6
The block read during the normal successful database operation had SCN 1995598 (0x0000.001e734e) seq 1 (0x01)
ERROR: ORA-00752 detected lost write on primary
Slave exiting with ORA-752 exception
Mon Apr 11 10:31:51 2016
Errors in file /u01/app/oracle/diag/rdbms/db121rac2/db121rac2/trace/db121rac2_pr04_4812.trc:
ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 5, block# 207, file offset is 1695744 bytes)
ORA-10564: tablespace EXAMPLE
ORA-01110: data file 5: '/u01/app/oracle/oradata/db121/example01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 91753

In summary, this parameter is a must if you have a Dataguard configuration, but it is important to understand its limitations.