sqlplus: error while loading shared libraries

September 26, 2011 1 Comment

Error Description: sqlplus: error while loading shared libraries: $ORACLE_HOME/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

One of the way to solve this error is to disable SELinux(as some people advice on the forums). But it is related to the security risks.

So I decided to use Linux provided solution, which is safer:

–After I ran the following command:

# sqlplus / as sysdba

There appeard our error and some tip on the screen, indicating the solution of this error, I want to share it with you.

The brief summery is to run the following command:

$ chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libcntsh.so.11.1'

Note: My $ORACLE_HOME=/u0/app/oracle/product/11.2.0/db_1

The whole text in the message from Linux is the following , I think it is very interesting:

Summary
SELinux is preventing sqlplus from loading /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1
which requires text relocation.

Detailed Description
The sqlplus application attempted to load /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 which requires text relocation.
This is a potential security problem. Most libraries do not need this permission.
Libraries are sometimes coded incorrectly and request this permission.
The SELinux Memory Protection Tests web page explains how to remove this requirement.
You can configure SELinux temporarily to allow /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1
to use relocation as a workaround, until the library is fixed.
Please file a bug report against this package.

Allowing Access
If you trust /u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 to run correctly,
you can change the file context to textrel_shlib_t.
 "chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'"
You must also change the default file context files on the system in order to preserve
them even on a full relabel.
"semanage fcontext -a -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'"

The following command will allow this access:
  chcon -t textrel_shlib_t '/u0/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1'

...

Filed under DB Tagged with ,

SQL Tuning(World Scenario)

September 19, 2011 Leave a comment

SQL Tuning is very big and difficult subject. Each SQL Tuning scenario is unique.

To solve the problems easily, you need a lot of experience.

I decided to write every SQL Tuning Scenario which I have had.

So, let’s start:

Look at this SQL:

SELECT
       distinct col1,
                col2,
                col3,
                col4FROM HR.Table1

where col2 = 040533424

  and col1 !=

‘Mariam’

order by col4 desc

It’s SQL plan is the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 269539686
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 134 | 2625 (5)| 00:00:1
|   1 |  SORT UNIQUE       |              |     2 |   134 |  2624   (5)| 00:00:1
|*  2 |   TABLE ACCESS FULL| TABLE1       |     2 |   134 |  2623   (5)| 00:00:1
--------------------------------------------------------------------------------

This table has index named col2_indx on col2 column.But in our plan it is not used, why??

Now let’s drill down:

SQL> desc HR.TABLE1

Name   Type         Nullable Default Comments
------ ------------ -------- ------- -------- 
col1   VARCHAR2(15) Y

col2 VARCHAR2(30) Y

col3   VARCHAR2(50) Y
col4   VARCHAR2(30) Y

As you can see col2 type is varchar2… But in our SQl statement value of this column is equal to number type…

So Oracle is trying to convert number type to varchar2 type and at this time looses the ability to use that index.

If you follow my posts on that blog, you may remember the post about FUNCTION BASED INDEXES. There is the following scenario.

If you have index on some column and in select you are using this column like that to_char(col2) or with some other function… Index on that column is missed. You can have function based index on to_char(col2) combination…

Now, let’s rewrite our SQL:

SELECT
       distinct col1,
                col2,
                col3,
                col4FROM HR.Table1

where col2 = ‘040533424’

 and col1 !=

‘Mariam’

order by col4 desc

Plan for this statement:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4243002513
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 134 | 7 (2
|   1 |  SORT ORDER BY                |              |     2 |   134 |     7  (2
|   2 |   HASH UNIQUE                 |              |     2 |   134 |     6  (1
|*  3 |    TABLE ACCESS BY INDEX ROWID| TABLE1       |     2 |   134 |     5   (

|* 4 | INDEX RANGE SCAN | COL2_INDX | 2 | | 3 (

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

And this select is faster than the previous.

I hope it was helpful for you…

Filed under DB Tagged with

Sql Sever Backup Problem

September 12, 2011 Leave a comment

If you have the following error during backup/restore of SQL Server database:

image

Just click OK, and let’s start its workaround…

In Select Backup Destination window do not browse anything, or it will arise the previous error, just type the path and name of the backup file, like that:

image

Click OK, there will appear the following window:

image

Click Yes… Your backup should work.

I have also checked my backup by restoring it and everything works fine…

Note: Support of the Microsoft recommends you the following workaround:

“To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.”

I did it without this security issueSmile

 

P.S I found another solution of it… especially by granting db_backupoperator role to that user….

NOTE: If you do not grant this role, user will be able to make a backup as I did in the previous section, but will not be able to restore database…

Filed under DB Tagged with

Failed to auto-start Oracle Net Listener

September 5, 2011 Leave a comment

Error  : Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr

Cause: This is due to a hard coded path in the dbstart script.

Solution:

In $ORACLE_HOME/bin/dbstart script replace

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

with

ORACLE_HOME_LISTNER=$ORACLE_HOME
The dbstart script shold now start the listener as expected.

Filed under DB Tagged with ,