ORA-01466 unable to read data – table definition has changed

This is a time-based read consistency error, which may occur during flashbacking object.

For example, I was running the following statement:

SELECT text
FROM dba_views AS OF TIMESTAMP to_timestamp('8-SEP-2011 6:14:35','DD-MON-YYYY HH24:MI:SS')

ORA-01466: unable to read data - table definition has changed

Let’s check undo_retention parameter:

SQL>  SELECT value/60/60 as Hours
 2    FROM v$parameter
 3    WHERE name='undo_retention';

    HOURS
---------
3.02777777

So my retention period is 3hours…If I want to flashback object to before more than 3 hours I will get ORA-01466.

ORA-28221: REPLACE not specified

Very interesting situation. This error occurs, when user’s profile has limit password_verify_function, at the same time user doesn’t have ALTER USER privilege and trying to run the following statement:

ALTER USER my_user IDENTIFIED BY my_password;

* If user doesn’t have that limit in profile, he/she can change its password by this way without any problem.

* If she/he has that limit, it should have ALTER USER privilege to change password by this way without any problem.

There is also another way, if you are not able to change password by the previous command, but you must know old password:

ALTER USER my_user IDENTIFIED BY my_password REPLACE my_old_password;

Just one more trick in Oracle, that you should know…

sqlplus: error while loading shared libraries

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'

...

Failed to auto-start Oracle Net Listener

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

ORA-32589:unable to drop minimal supplemental logging

Error: ORA-32589: unable to drop minimal supplemental logging

Cause: Minimal supplemental logging cannot be dropped until one of the PRIMARY KEY, FOREIGN KEY, UNIQUE or ALL COLUMN supplemental logging is enabled.

For example: You may have enabled the following supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

And now trying to turn off supplemental logging like this:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Action: Use the following query to determine which supplemental logging is turned on:

select supplemental_log_data_min
       ,supplemental_log_data_all
       ,supplemental_log_data_pk
       ,supplemental_log_data_ui
from v$database

For example: If the output is the following:

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI
------------------------- ------------------------ ------------------------ ------------------------
IMPLICIT                  NO                       YES                      NO

Do the following:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

And then:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;