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)
Advertisements

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.net:/vol/DB_REDO_TEMP      /oraredo/DB_TEMP

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

mynode:/oraredo : mkdir DW2_TEMP
mynode:/oraredo : chown oracle:dba DW2_TEMP/
mynode:/oraredo : 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
mynode:/oraredo : mount /oraredo/DB_TEMP/
mynode:/oraredo : df -h
FDC0777.data.dc1.mydomain.net:/vol/DB_REDO_TEMP
                       72G  128K   72G   1% /oraredo/DB_TEMP
mynode:/oraredo : 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
mynode:/oraredo : chown oracle:dba DB_TEMP/
mynode:/oraredo : 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.

image

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:

image

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