Uniqueness using Non-Unique Index

How to add Primary Key index on table having duplicate data.

Many a times at our work location(s) it has been argued that only Unique Index can support Uniqueness in Table column.

Well! That’s not the case. A Non-Unique Index can also support Uniqueness in Column of Table.

I have demonstrated this in following statements.

 A table with Unique values.

SQL> Create table scott.test2 as select level id1 from dual connect by level<12;

  2

SQL> /

Table created.

Updated table to contain some duplicate values in ID1 Column.

SQL> Update scott.test2 set id1=3 where id1 in (1,2,4,5);

  2

SQL> /

4 rows updated.

SQL> commit;

Commit complete.

Now! Table Scott.TEST2 contains duplicate data in its ID1 Columns.

SQL> select * from scott.test2;

       ID1

----------

         3

         3              (Duplicate Data)

         3

         3

         3

         6

         7

         8

         9

        10

        11

11 rows selected.

Created a non-Unique(Default! If Create Statement is given by User) Index on SCOTT.TEST2.ID1 Column.

SQL> Create index t2ix on scott.test2(id1) compute statistics

  2  ;

Index created.

SQL>  select Index_name,Table_name,Table_owner,UNIQUENESS from dba_indexes where index_name='T2IX';

INDEX_NAME                     TABLE_NAME             TABLE_OWNER                    UNIQUENES

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

T2IX                                        TEST2                                   SCOTT                          NONUNIQUE

Trying to add Primary Key on SCOTT.TEST2.ID1 column but getting obvious error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1);

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1)

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

Same error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX;

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

However! Trick lies in ENABLE and NOVALIDATE clauses.

Enable:- New Constraint is implemented. New Data will only be inserted if its Unique.

NoValidate:- Old data not to be  checked. Just ignore the old duplicate data.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX ENABLE NOVALIDATE;

Table altered.

SQL> Insert into scott.test2 values(3)

  2  ;

Insert into scott.test2 values(3)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.T2_PK) violated

SQL> Select Owner,Constraint_name,Constraint_type,Table_name,Index_name from dba_constraints where table_name='TEST2'

  2  ;

OWNER                          CONSTRAINT_NAME                C TABLE_NAME                     INDEX_NAME

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

SCOTT                                            T2_PK                                               P TEST2                          T2IX

This is easy and Simple.

Any suggestion/Correction are always most welcome.

Database File recovery

Data File recovery is normal scenario of Dba Activities. Many a times it happens that we take any Data File Offline and when I try to makeit Online we get following error:

ALTER DATABASE DATAFILE 4 ONLINE;

Error starting at line 27 in command:

ALTER DATABASE DATAFILE 4 ONLINE

Error report:

SQL Error: ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF'

ORA-01200: actual file size of 640 is smaller than correct size of 960 blocks

01122. 00000 -  "database file %s failed verification check"

*Cause:    The information in this file is inconsistent with information

           from the control file. See accompanying message for reason.

*Action:   Make certain that the db files and control files are the correct

           files for this database.

In above scenario , My Database is in Archive log and it is 10.2 release.   Problem is once I take any DBF offline and try to make it Online again, It asks for Media Recovery. The reason for asking MR is the change in SCN. SCN is changed between time of Offline and time to take DBF Online again. And once the SCN is changed, to bring effected DBF we need to provide Archived Changed Vector and any changes residing in Online REDO log files.

However! In this case nothing to worry about. Because I have my database backup intact. All I need to do is Restore and Recover Data file number 4. 

Before we proceed further take Datafile 4 Offline and remove it from HDD or take it to another  part of HDD.

Although! Datafile number 4 was offline, I could not cut/paste it from its location to any other location. If we miss to do this, than while restoring the DBF, we get “DBF already in use error and Restore operation fails”. In case you face  error on deleting it from OS level than you have to take DB down for just to remover DBF from its default location. Once we are done with these steps then remaining part of Restore/Recover is just the same.

RMAN> RUN

2> {

3> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT 'D:\BKUP\DG_FILES\SEED_%U.BKP';

4> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

5> RESTORE DATAFILE 4;

6> }

allocated channel: CH1

channel CH1: sid=137 devtype=DISK

sql statement: ALTER DATABASE DATAFILE 4 OFFLINE

Starting restore at 01-AUG-11

channel CH1: starting datafile backupset restore

channel CH1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF

channel CH1: reading from backup piece D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP

channel CH1: restored backup piece 1

piece handle=D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP tag=TAG20110725T141711

channel CH1: restore complete, elapsed time: 00:00:04

Finished restore at 01-AUG-11

released channel: CH1

RMAN> Recover datafile 4;

Starting recover at 01-AUG-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=137 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=135 devtype=DISK

starting media recovery

archive log thread 1 sequence 13 is already on disk as file D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC

archive log thread 1 sequence 14 is already on disk as file D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC

archive log thread 1 sequence 15 is already on disk as file D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC

archive log thread 1 sequence 16 is already on disk as file D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC

archive log thread 1 sequence 17 is already on disk as file D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC

archive log thread 1 sequence 18 is already on disk as file D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC

archive log thread 1 sequence 19 is already on disk as file D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC

archive log thread 1 sequence 20 is already on disk as file D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC

archive log thread 1 sequence 21 is already on disk as file D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC

archive log thread 1 sequence 22 is already on disk as file D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC

archive log thread 1 sequence 23 is already on disk as file D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC

archive log thread 1 sequence 24 is already on disk as file D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC

archive log thread 1 sequence 25 is already on disk as file D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC

archive log thread 1 sequence 26 is already on disk as file D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC

archive log thread 1 sequence 27 is already on disk as file D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC

archive log thread 1 sequence 28 is already on disk as file D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC

archive log thread 1 sequence 29 is already on disk as file D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC

archive log thread 1 sequence 30 is already on disk as file D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC

archive log thread 1 sequence 31 is already on disk as file D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC

archive log thread 1 sequence 32 is already on disk as file D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC

archive log thread 1 sequence 33 is already on disk as file D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC

archive log thread 1 sequence 34 is already on disk as file D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC

archive log thread 1 sequence 35 is already on disk as file D:\BKUP\SEED\SEED_ARC_00035_0756558498_001.ARC

archive log thread 1 sequence 36 is already on disk as file D:\BKUP\SEED\SEED_ARC_00036_0756558498_001.ARC

archive log filename=D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC thread=1 sequence=13

archive log filename=D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC thread=1 sequence=14

archive log filename=D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC thread=1 sequence=15

archive log filename=D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC thread=1 sequence=16

archive log filename=D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC thread=1 sequence=17

archive log filename=D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC thread=1 sequence=18

archive log filename=D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC thread=1 sequence=19

archive log filename=D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC thread=1 sequence=20

archive log filename=D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC thread=1 sequence=21

archive log filename=D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC thread=1 sequence=22

archive log filename=D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC thread=1 sequence=23

archive log filename=D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC thread=1 sequence=24

archive log filename=D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC thread=1 sequence=25

archive log filename=D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC thread=1 sequence=26

archive log filename=D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC thread=1 sequence=27

archive log filename=D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC thread=1 sequence=28

archive log filename=D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC thread=1 sequence=29

archive log filename=D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC thread=1 sequence=30

archive log filename=D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC thread=1 sequence=31

archive log filename=D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC thread=1 sequence=32

archive log filename=D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC thread=1 sequence=33

archive log filename=D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC thread=1 sequence=34

media recovery complete, elapsed time: 00:00:14

Finished recover at 01-AUG-11

RMAN>

Alter database data ile 4 online;

After this just take backup of Data File or if possible full database backup.