SDP tnsnames.ora entry for 2 node rack

Model tns entry in the first node, next node will be other way

DBM =
(DESCRIPTION =
  (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ed05-scan1)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = dbm)
   )
)

DBM_IB =
(DESCRIPTION =
  (ADDRESS_LIST =
    (LOAD_BALANCE=on)
    (ADDRESS = (PROTOCOL = SDP)(HOST = ed05db01-ibvip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = SDP)(HOST = ed05db02-ibvip)(PORT = 1522))
   )
  (CONNECT_DATA =
     (SERVICE_NAME = dbm)
   )
)

LISTENER_IBREMOTE =
(DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = SDP)(HOST = ed05db02-ibvip.oracle.com)(PORT = 1522))
   )
)

LISTENER_IBLOCAL =
(DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ed05db01-ibvip)(PORT = 1522))
      (ADDRESS = (PROTOCOL = SDP)(HOST = ed05db01-ibvip)(PORT = 1522))
   )
)

LISTENER_IPLOCAL =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ed0501-vip)(PORT = 1521))
    )
)

LISTENER_IPREMOTE =
(DESCRIPTION =
  (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ed0502-vip)(PORT = 1521))
  )
)

LFI-01518: write error

Message and corrective action provided by far looks like a serious issue. It could be in few occasion, but most common issues are

• Filesystem full
• No privilege to write.

CLSD: A file system error occurred while attempting to write to file "/u01/app/11.2.0.3/grid/log/ed05db02/alerted05db02.log" during alert write processing for process "client". Additional diagnostics: LFI-00004: Call to lfibwrt() failed.

LFI-01518: write() failed(OSD return value = 28) in slfiwl.

Common occasion are when you are trying to start ASM instance thru automatic or manual start.

How to avoid Blob corruption

Blob corruption

   Oracle database have Basic and Secure blob type. Both store images, but former one have many bugs and development stopped long time ago. So its better to convert existing Basic blob to Secure and avoid corruption.

One of the error you get when Basic blob got corrupted is

ORA-01555: snapshot too old: rollback segment 

The above error usually comes when you execute DML statements, but you'll get this for blob corruption too. This blog describes how you can avoid this. The conversion wont affect your application functionality.

Fresh creation

We can force all new blob creation in the database to secure, by executing 

alter system set db_securefile=always scope=both;

The above statement will force Secure or Basic blob to be created as Secure.

Existing Basic to Secure conversion

 This can be done in few steps using redefinition.

• create a dummy table with same table structure with securefile clause

 create table blob_tab
(eid varchar2(40), details blob, primary key(eid))LOB (details) STORE AS SECUREFILE (tablespace users)  tablespace users;

• Start the redef proces

dbms_redefinition.start_redef_table('scott', 'old_table', 'blob_tab', 'eid eid, details details');

• If you have any primary key in the original table drop it temporarily.

alter table scott.old_table drop primary key;

• Copy all relevant information, declare a variable "error" as number

dbms_redefinition.copy_table_dependents ('scott','old_table', 'blob_tab',1,true, true, true, false, error);

• Again create the primary key which was dropped before

alter table scott.old_table add primary key(eid);

• Move back all the information back to the original table

dbms_redefinition.finish_redef_table ('scott', 'old_table', 'blob_tab')

You can confim this with by,
select securefile from user_lobs where table_name='OLD_TABLE'; 

Rolling back

In case you want to rollback, create a interim table without "securefile" option and follow the same procedure. 

Remember your db_securefile parameter overrides your create table option for blob.

Troubleshooting

You could face few issues.

If you abort/error out in the middle of redef and start, you'll get the below error.

ORA-23539: table "SCOTT"."OLD_TABLE" currently being redefined

You can delete "materialized view log" and drop original table and recreate interim table.

Or, you can simply

dbms_redefinition.abort_redef_table('scott','old_table','blob_tab');
-----------------------------------------------------------------------------------------------------------------------------

If you have primary key you have to drop and recreate it, just like the above steps, else you'll get

ORA-01408: such column list already indexed

Check emkey is copied to the repository

 Emkey is kept secured,but upgrade process demand that it should be available. So we have to expose emkey to the installer. After that the key can be removed from repository and it make the emkey secured again.

So if you get an error message for the prerequisite "Check emkey is copied to the repository". For all this activity you need SYSMAN password.

Go to the OMS server and ORACLE_HOME/bin

• ./emctl config emkey -copy_to_repos

Now proceed again with the upgrade. Once the upgrade is finished, remove the key from the repository.

• ./emctl config emkey -remove_from_repos

To know the status of the emkey, execute

• ./emctl status emkey

The key can be seen in $ORACLE_HOME/sysman/config/emkey.ora

Changing owership of a Listener

This page deals with handling permission for the listener. Starting or managing a listener needs os user privilege. This works just like chmod, but executing with crsctl command. If improperly configured others can take advantage of the listener.

To check what is the current permission.
crsctl status resource ora.LISTENER_IB.lsnr -p | grep ACL=
The output will look like below..
ACL=owner:root:rwx,pgrp:root:r-x,other::r--

Here it shows who is the owner, primary group and others.
owner = root with permission rwx
pgrp = root with permission r-x
other = permission is only read.

To change the primary group
crsctl setperm resource ora.LISTENER_IB.lsnr -u 'pgrp:oinstall:rwx'

This can be seen with

crsctl status resource ora.LISTENER_IB.lsnr -p | grep ACL=

ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--

Re-configuring SDP IPs

In case of a situation where you need to re-configure the existing IPs for SDP, then these steps can be done. This includes cleanup of existing setup and re-configuring the sdp.

1. Remove your IB listener, by the grid software
/u01/app/11.2.0.4/grid/bin/srvctl remove listener -l LISTENER_IB

2. Remove your vip resources, better to use force option "-f". This has to be done each node
/u01/app/11.2.0.4/grid/bin/srvctl remove vip -i ed05db01-ibvip -f
..
..
/u01/app/11.2.0.4/grid/bin/srvctl remove vip -i ed05db08-ibvip -f

3. Remove your network, better to use force option "-f"
/u01/app/11.2.0.4/grid/bin/srvctl remove network -k 2 -f

To redo the setup, you can follow this link from "Setup"
http://ajitsolomon.blogspot.com/p/end-to-end-configuration-of-sdp-listener.html

End to end configuration of SDP listener

I have been doing few SDP listener configuration in quarter to full rack Exadata. SDP is one of the performance enhancement done by oracle in Exadata. This uses infiniband, so Exalogic racks which are connected in the same infiniband fabric make use of this SDP.
SDP is easy to set and you can do it in few steps. For easy understanding I'm using 2 node Exadata.
tnsnames.ora file is also added in this page for reference. 

Pre-setup

It is better to use dcli to propagate these changes to all the nodes.

Two files have to be edited to enable SDP setup in each node and those nodes have to be rebooted.

create a text file called node with all the node name

ed05db01

ed05db02

Check if /etc/infiniband/openib.conf
SDP_LOAD=yes

Use the below script to do presetup

#!/bin/bash
# Script name :preset.sh

sed -i "/^use both server/c\use both server * :" /etc/ofed/libsdp.conf
sed -i "/^use both client/c\use both client * :" /etc/ofed/libsdp.conf

grep sdp_zcopy_thresh /etc/modprobe.conf

if [ $? = 0 ] ; then

        sed -i "/sdp_zcopy_thresh/c\options ib_sdp sdp_zcopy_thresh=0 recv_poll=0 sdp_apm_enable=0" /etc/modprobe.conf

else

        echo "options ib_sdp sdp_zcopy_thresh=0 recv_poll=0 sdp_apm_enable=0" >> /etc/modprobe.conf

fi

ORA-16649 with Observer

When observer is running it takes full control of your db status. ORA-16649 will be given if you  start primary database after it crashed. This is not an issue when the observer is running.

Check if the observer and fast_start failover is running. If you find it as running, just wait for few minutes based on how long and how active the new primary database.
The database will be put on to standby mode, it'll get reinstated and it'll start to roll forward.

SQL> startup
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size     2934696 bytes
Variable Size  1107298392 bytes
Database Buffers  3892314112 bytes
Redo Buffers    13840384 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from being opened

Scripting oracle wallet creation - mkstore

This post is not about creating wallet manually, which I had seen in google search. One thing I didn't find is how do you script these to avoid human interaction.

In usual method, you can execute as
mkstore -wrl . -create -nologo
This will make you enter and re-enter password.

To avoid this,

echo -e "Newpass\nNewpass" | mkstore -wrl . -create -nologo

Unable to start observer due to DGM-16979 in oracle 12c

dgmgrl will allow you to do many activity thru sys user, but certain activity needs non sys user.

This error is due to non-sys user. Authentication issue is due to the account sysdg status. This account should be open in both primary and standby
Database comes with sysdg account with locked and expired, unlock and set the password.
Now the observer should start.

DGMGRL> start observer
[P003 01/14 15:53:45.14] Authentication failed.
DGM-16979: Unable to log on to the primary or standby database as SYSDBA
Failed.

DGM-16979 in oracle 11g

DGM-16979 is an common error and sometimes is misleading. One of the issue I faced in oracle 11g is, observer starts if you do manually even after login thru wallet.

dgmgrl /@wallet_sys
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>

When you try to start observer, it'll spit out errors like

Could not verify magic words.
DGM-16979: Unable to log on to the primary or standby database as SYSDBA

Even if you are using wallet successfully for other activities, you might still have to add default user and password to your existing wallet.

mkstore -wrl . -createEntry oracle.security.client.default_username sys
mkstore -wrl . -createEntry oracle.security.client.default_password PassWord

Now your observer should start.