ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

When trying to rebuild an index, the following error occured:

SQL> ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online;
ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

If the index is based on an mview, then the following error can also occur when trying to refresh the mview

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-08102: index key not found, obj# 4145783, file 172, block 364789 (3)
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 2

This can also be related to ORA-08104: this index object 4145783 is being online built or rebuilt

Metalink gives some useful info on the ORA-01452 error – ORA-01452: Cannot Create Unique Index; Duplicate Keys Found [ID 332494.1]

Run the following to find duplicate rows

-- Script find duplicate rows
select  
--Enter name(s) of column(s) which should be unique
&&c 
from 
-- Enter name of table with duplicate rows
&&t
where rowid not in (select min(rowid) from &&t group by &&c)

ORA-08104: this index object 4145783 is being online built or rebuilt

Whilst running an online index rebuild my session terminated abnormally and the session was killed. When trying to rerun the index rebuild statement, the following error occurs:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining and 
Real Application Testing options

SQL> ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online;
ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online
*
ERROR at line 1:
ORA-08104: this index object 4145783 is being online built or rebuilt

Cause:

A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is on going when in fact it is not.

Solution:

The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. More details about the function on metalink – Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]

Run the following to resolve:

declare
isclean boolean;
begin
isclean :=false;
while isclean=false
loop
isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
dbms_lock.sleep(10);
end loop;
end;
/

Moving location of REDO in Oracle

We had an issue today whereby the filer which held the REDO files for our databases had a hardware failure. As such, the partner filer took over the load but we were seeing a lot of log file sync waits and as a result application concurrency.

It was decided to move the REDO onto a less loaded filer. This is an ONLINE operation and this is how we did it:

1) New Filer Volume
The first step was to ask our storage team for a new filer volume on a less contended filer. So we had the following created.

Volume: DB_REDO_TEMP
Size: 75GB
Filer: FDC0777

2) Mount the volume onto the database node
Next step is to add the volume to fstab and to mount the volume and sort out the permissons

sudo su
vi /etc/fstab

Then amend fstab with a line for the new volume

FDC0777.data.dc1.mydomain.netvol/DB_REDO_TEMP      /oraredo/DB_TEMP

Next sort out the permissions so that it the folder structure is owned by oracle

mynodeoraredo : mkdir DW2_TEMP
mynodeoraredo : chown oracle:dba DW2_TEMP/
mynodeoraredo : ls -ltrh
total 8.0K
drwxr-xr-x 3 oracle dba 4.0K Mar  3  2011 DB
drwxr-xr-x 2 oracle dba 4.0K Sep 12 10:17 DB_TEMP
mynodeoraredo : mount /oraredo/DB_TEMP/
mynodeoraredo : df -h
FDC0777.data.dc1.mydomain.netvol/DB_REDO_TEMP
                       72G  128K   72G   1% /oraredo/DB_TEMP
mynodeoraredo : ls -l
total 8
drwxr-xr-x 3 oracle dba  4096 Mar  3  2011 DW2
drwxr-xr-x 3 root   root 4096 Sep 12 10:17 DW2_TEMP
mynodeoraredo : chown oracle:dba DB_TEMP/
mynodeoraredo : ls -l
total 8
drwxr-xr-x 3 oracle dba 4096 Mar  3  2011 DB
drwxr-xr-x 3 oracle dba 4096 Sep 12 10:17 DB_TEMP

The work from the O/S perspective is now done.

3) Create the new redo groups and members

The current setup was 3 groups with 2 members in each group. We wanted to maintain the same structure so the following SQL was run:

ALTER DATABASE ADD LOGFILE GROUP 4 ( '/oraredo/DB_TEMP/redo4a.log', '/oraredo/DB_TEMP/redo4b.log') SIZE 3072M;
ALTER DATABASE ADD LOGFILE GROUP 5 ( '/oraredo/DB_TEMP/redo5a.log', '/oraredo/DB_TEMP/redo5b.log') SIZE 3072M;
ALTER DATABASE ADD LOGFILE GROUP 6 ( '/oraredo/DB_TEMP/redo6a.log', '/oraredo/DB_TEMP/redo6b.log') SIZE 3072M;

This created groups 4,5 & 6, eahc with 2 members.

4) Remove the old log groups

We did this in Toad, using the Redo Log Manager.

We removed log groups 1, 2 & 3 when they became Archived (YES) and status of INACTIVE. To speed up the log switching process we did a manual log switch in Grid Control:

SQL Server Patch Levels\Build List

A couple of good sites for checking patch levels for SQL Servers

SQL Security

SQL Server Build List – SQL Server Central

Token-based server access validation failed with an infrastructure error

On one of our SQL 2008 servers we were getting the following error for any Windows based user who tried to connect to SSMS without having sysadmin privs

Login failed for users ‘xxx\xxx’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 12

We were also seeing the following error if any SQL authenticated login tried to connect to the instance without having sysadmin privs:

Error: 18456, Severity: 14, State: 11.
Login failed for user ‘<x>’.
Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

States 11 & 12 mean that SQL Server was able to authenticate you, but weren’t able to validate with the underlying Windows permissions.

The issue doesnt happen for users with sysadmin privileges as they dont need to authenticate through in the same way.

After a lot of investigation and attempting various different things including disabling UAC, disabling firewalls, Connecting to SSMS as aministrator, the solution was fairly painless:

GRANT CONNECT SQL TO [DOMAIN\firstname.lastname]
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [DOMAIN\firstname.lastname]

This solution works for both Windows accounts and SQL Logins in the same way.