Data Dump Import Missing NOT NULL Constraints

Hi All, I like to share my experience with recent database migration to Exadata Machine using Oracle Export / Import utility. Many of us used Oracle Export / Import utility for database upgrades and migrations. If you are migrating data using Data Pump with all the constraints and indexes, you will not face this issue. This issue is only related to Data Pump option Exclude / Include = Constraints.

Let me start with the reason that why would we need to copy data using data pump without constraints or indexes. When you are migration few GB of data it will not matter that you are creating and build indexes during data import. But if you are planning to migrate 10+ TB database using data pump, you want to separate data copy from indexes and constraints. Creating indexes and constraints on terabyte tables can take days since they will run without any parallelism. Hence many DBA’s and migration experts uses Data Pump SQLFILE option for indexes and constraints creation. This way we are able to create indexes in parallel mode and create constraints without validate.

Now with 12c Oracle has change exclude option and it will not import NOT NULL constraints if you exclude constraints during data import. Please review following Oracle Support note and make sure to bring NOT NULL constraints manually after the data import. Fortunately, data import with exclude constraints option still bring CHECK constraints for now.

Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)

After doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in target tables. Sequence of steps leading to the problem:
– Data Pump export of tables with NOT NULL and referential integrity constraints
– Data Pump import with EXCLUDE=CONSTRAINT

For example:

==> In source database a table has the following structure:

Name Null? Type
—————————————– ——– —————————-
ID NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(38)
ADDRESS CHAR(25)
SALARY NUMBER(18,2)

=> After import, the table structure:

Name Null? Type
—————————————– ——– —————————-
ID NUMBER(38) ———————> NOT NULL Constraints ignored
NAME VARCHAR2(20)———————> NOT NULL Constraints ignored
AGE NUMBER(38)———————> NOT NULL Constraints ignored
ADDRESS CHAR(25)
SALARY NUMBER(18,2)

Also, if using Data Pump import (impdp) with INCLUDE=CONSTRAINT parameter, NOT NULL constraints are not imported.

Perform Table Recovery With Oracle 12c Using RMAN

Import Consideration:

  • An rman backup containing the missing table must exist; it cannot rely on a backup from before the table was created alongside the application of archived redo.
  • The table cannot belong to SYS or SYSTEM and cannot reside in SYSTEM or SYSAUX.

Create Test Tables

SQL> create table test1 as select * from dba_tables;

Table created.

SQL> select count(*) from test1;

  COUNT(*)

———-

      6352

Make sure to perform Incremental or Full Backup

Drop test Tables

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> drop table umair.test1;

Table dropped.

Set Recovery Destination Area

> Mkdir –p /zfssa/dbm01/backup1/fra

> cd /zfssa/dbm01/backup1/fra

oracle@exadbadm01.gain.tcprod.local:dbm01:/zfssa/dbm01/backup1/fra

Recover table using time or SCN

RMAN> recover table umair.test1 until time “to_date(’09/16/2017 21:01:15′,’mm/dd/yyyy hh24:mi:ss’)” auxiliary destination ‘/zfssa/r360pdimg/imgbackup1/fra’;

Starting recover at 17-SEP-17
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=509 instance=dbm01 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Tablespace UNDOTBS2
Tablespace UNDOTBS3
Tablespace UNDOTBS4
Tablespace UNDOTBS5
Tablespace UNDOTBS6

auxiliary instance file tspitr_plti_80445.dmp deleted
Finished recover at 18-SEP-17

Validate Table Recovery

SQL> select count(*) from test1;

  COUNT(*)

———-

      6352