Thursday, July 25, 2013

cannot open shared object file: No such file or directory

This is very generic error and you will see this error with many situations.

I have received this error when connecting to Mysql using isql

$ isql -v MySQL test_user testpass
[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/' : /usr/lib/ cannot open shared object file: No such file or directory
[ISQL]ERROR: Could not SQLConnect 

Cause: The Mysql cannot find the required library

Possible Solution: Locate the file, if you don’t find the file then you might be using a different version. Find the lib file that you are using ‘libmyodbc*’ and create a link.

     For ex:- ln –s <Path to file> < Link Name>
     $ ln –s /home/mysql/lib/ /usr/lib/

In many causes users will see this error, if they don’t include the library path in LD_LIBRARY_PATH

Users might receive below error, when connecting using “odbcinst”. This is also one of the common error.

$ odbcinst –q -s
odbcinst: SQLGetPrivateProfileString failed with . 

Cause: The environment variables are not set properly

Solution: For Linux, edit .bash_profile and add following environment variables 

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

In caseof CSH,

setenv ODBCSYSINI /etc
setenv ODBCINI /etc/odbc.ini


Configure a Connector/ODBC DNS on Unix
Problems with connecting other platforms to MySQL with ODBC
Download MySQL Connector/ODBC

Satishbabu Gunukula, Oracle ACE

Monday, July 22, 2013

Thread 1 cannot allocate new log & Checkpoint not complete

Users will see these messages when Oracle wants to reuse the redolog file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

Cause: In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Mon Apr 15 07:20:42 2013
Thread 1 advanced to log sequence 5021
Current log# 1 seq# 5021 mem# 0: /oracle/ORCL/redoA/redo01.log
Mon Apr 15 07:21:15 2013
Thread 1 cannot allocate new log, sequence 5022
Checkpoint not complete

If you have many updates in the system, you might need more redo groups. If you have fewer redo groups then adding more redo groups will help.

Use below syntax to add more redo groups

ALTER DATABASE ADD LOGFILE GROUP <Group No> ('<Redo log member 1 path’ ,'<redo log member 2 path>') size 500M;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switch logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

---------- ----------------

Step2:- Drop the log group1 which is ‘INACTIVE’ and recreate with bigger size.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 ('/db01/ORCL/redoA/log1_m1.dbf',' /db01/ORCL/redoB/log1_m2.dbf') size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

set lines 120;
set pages 999;
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
GROUP by to_char(first_time,'YYYY-MON-DD');

Click here to learn about "Private Strand Flush Not Complete"  message in alert.log

Satishbabu Gunukula, Oracle ACE

Move Datafile between DikGroups in ASM

This is one of the common issues where users make mistakes while adding new data file or tablespace and creates in different disk group.

Follow the steps to move data file between disk groups

Here we are copying a Datafile from FLASH to DATA1 Disk Group.

1. Use RMAN to copy the data file from FLASH to DATA disk group.

RMAN> BACKUP AS COPY DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ FORMAT ‘+DATA1’;

Or you can also specify the data file number instead of full path


2. Offline the datafile that you want to move to new disk group.

SQL> ALTER DATABASE DATAFILE ‘+FLASH/datafile/usertbs7.588.475363994’ OFFLINE;

3. Switch the data file to copy

RMAN> switch datafile ‘+FLASH/datafile/usertbs7.588.475363994’ to copy;

4. Recovery the datafile

RMAN> RECOVER DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ;

5. Bring the data file online.

SQL> ALTER DATABASE DATAFILE ‘+DATA1/datafile/usertbs7.588.475363994’ ONLINE;

6. Delete old datafile from ASM disk group.

RMAN> DELETE DATAFILECOPY ‘+FLASH/datafile/usertbs7.588.475363994’;

7. Check the Datafile Status

Check the status of data file by running the below query, you should be able to see the status as "AVAILABLE".


Satishbabu Gunukula, Oracle ACE

“Private Strand Flush Not Complete” in Oracle

The below error message is very common and users see in alert<SID> .log file. You will see this error message when writing the redo info is not completed, when the log switch happens.

Note that the log switch cannot happen until all of the redo has been written.

Users might also notice these error messages when there is an issue with network or storage where you have archive log destination, this is due to delay or hand in LGWR switch .

Thread 1 cannot allocate new log, sequence 5929
Private strand flush not complete
Current log# 4 seq# 5928 mem# 0: /oracle/ORAC/origlogB/log_g14m1.dbf
Current log# 4 seq# 5928 mem# 1: /oracle/ORAC/mirrlogB/log_g14m2.dbf
Beginning log switch checkpoint up to RBA [0x10189.2.10], SCN: 6845363020
Thread 1 advanced to log sequence 5929 (LGWR switch)

This message related to internal Cache Redo File management and safe to discard these messages.

These messages are not cause for concern unless there is a significant time gap between “cannot allow new log” and “advanced to log sequence” message. In some cases users benefited with db_writer_processes and this help to avoid message from being generated.

Click here to learn about "Cannot allocate new log & Checkpoint not complete"
message in alert.log

Satishbabu Gunukula, Oracle ACE