oracle transform=constraint novalidate:y oracle >19.27

      


transform=constraint_novalidate:y 

Data Pump (impdp) parameter speeds up imports 
by creating constraints as ENABLE NOVALIDATE. 
This allows constraints to be created instantly without performing full table scans to 
validate existing data, making it ideal for large data loads



test case !!!!!!!!!!!!


SQL> alter session set container=vihaan ;

Session altered.

SQL> grant dba to test4 identified by test4;

Grant succeeded.


CREATE TABLE test4.DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;


CREATE TABLE test4.EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES test4.DEPT);





INSERT INTO test4.DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO test4.DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO test4.DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO test4.DEPT VALUES(40,'OPERATIONS','BOSTON');


INSERT INTO test4.EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO test4.EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO test4.EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO test4.EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO test4.EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO test4.EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO test4.EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO test4.EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO test4.EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO test4.EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO test4.EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO test4.EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO test4.EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO test4.EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);





create or replace directory data_pump_dir1 as '/home/oracle';
grant read on directory data_pump_dir1 to test4 ;
grant write on directory data_pump_dir1 to test4 ;



expdp test4/test4@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log schemas=test4 estimate=STATISTICS CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



Worker 1 Status:
  Instance ID: 1
  Instance name: orcl
  Host name: oracent19c
  Access method: direct_path
  Object start time: Tuesday, 05 May, 2026 14:58:38
  Object status at: Tuesday, 05 May, 2026 14:58:42
  Process Name: DW00
  State: WORK WAITING
Job "TEST4"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue May 5 14:58:42 2026 elapsed 0 00:01:29



[oracle@oracent19c admin]$ impdp system/sys@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



this option valid from >19.27
import with transform=constraint_novalidate:y <<<<



[oracle@oracent19c admin]$ opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch  39196236     : applied on Sat May 02 15:45:47 BST 2026
Patch  39034528     : applied on Sat May 02 14:17:50 BST 2026
Patch  29585399     : applied on Thu Apr 18 08:21:33 BST 2019

check below data patch 
[oracle@oracent19c admin]$ opatch lsinventory |grep -i "Database Release Update"
Patch description:  "Database Release Update : 19.31.0.0.260421 (39034528)"



impdp test4/test4@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



Worker 1 Status:
  Instance ID: 1
  Instance name: orcl
  Host name: oracent19c
  Object start time: Tuesday, 05 May, 2026 15:04:38
  Object status at: Tuesday, 05 May, 2026 15:04:39
  Process Name: DW00
  State: WORK WAITING
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 5 15:04:39 2026 elapsed 0 00:00:12

[oracle@oracent19c admin]$ impdp system/sys@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS




set linesize 300 
define SCHEMA_NAME='TEST4'
col owner for a20 
col constraint_name for a20
col status  for a20
col  validated  for a20
col  type for a20
col CONSTRAINT_TYPE for a14
col table_name for a20
SELECT owner, table_name ,constraint_name,
CONSTRAINT_TYPE,
 decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
status, validated FROM cdb_constraints
where 1=1
and owner = '&SCHEMA_NAME' 
; 



OWNER                TABLE_NAME           CONSTRAINT_NAME      CONSTRAINT_TYP TYPE                 STATUS            VALIDATED
-------------------- -------------------- -------------------- -------------- -------------------- -------------------- --------------------
TEST4                EMP                  FK_DEPTNO            R              Foreign Key          ENABLED           NOT VALIDATED
TEST4                DEPT                 PK_DEPT              P              Primary Key          ENABLED           NOT VALIDATED
TEST4                EMP                  PK_EMP               P              Primary Key          ENABLED           NOT VALIDATED


 alter session set container=VIHAAN ;
Session altered.


--- to ENABLE VALIDATE


SET SERVEROUTPUT ON
BEGIN
    FOR rec IN (SELECT owner, table_name, constraint_name
                FROM all_constraints
                WHERE owner = 'TEST4'
                AND validated = 'NOT VALIDATED')
    LOOP
        -- This helps you see which table is failing
        DBMS_OUTPUT.PUT_LINE('Processing: ' || rec.owner || '.' || rec.table_name);
        
        EXECUTE IMMEDIATE 'ALTER TABLE "' || rec.owner || '"."' || rec.table_name || 
                          '" MODIFY CONSTRAINT "' || rec.constraint_name || '" ENABLE VALIDATE';
    END LOOP;
END;
/



OWNER                TABLE_NAME           CONSTRAINT_NAME      CONSTRAINT_TYP TYPE                 STATUS            VALIDATED
-------------------- -------------------- -------------------- -------------- -------------------- -------------------- --------------------
TEST4                EMP                  FK_DEPTNO            R              Foreign Key          ENABLED           VALIDATED
TEST4                DEPT                 PK_DEPT              P              Primary Key          ENABLED           VALIDATED
TEST4                EMP                  PK_EMP               P              Primary Key          ENABLED           VALIDATED

Oracle 19C : Exporting and Importing Broker Configuration

Oracle 19C : Exporting and Importing Broker Configuration

Oracle 19C : Exporting and Importing Broker Configuration


dgmgrl   EXPORT CONFIGURATION


[oracle@oracentd19c ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue May 5 08:06:17 2026
Version 19.31.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "orcl_stdy"
Connected as SYSDG.
DGMGRL> EXPORT CONFIGURATION TO anujdgmgrl_config.txt
Succeeded.
DGMGRL>


in trace dir


[oracle@oracentd19c trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl_stdy/orcls/trace



[oracle@oracentd19c trace]$ ls -ltr anuj*
-rw-r--r--. 1 oracle oinstall 5490 Jan  6  2024 anuj_dg_config.txt
[oracle@oracentd19c trace]$ ls -ltr *.txt
-rw-r--r--. 1 oracle oinstall 5490 Jan  6  2024 anuj_dg_config.txt
[oracle@oracentd19c trace]$


[oracle@oracentd19c trace]$ cat anuj_dg_config.txt
<?xml version="1.0" encoding="UTF-8"?>
<DRC Version="19.3.0.0.0" CurrentPath="True" Name="DG_CONF">
  <DefaultState>ONLINE</DefaultState>
  <DRC_UNIQUE_ID>1306264893</DRC_UNIQUE_ID>
  <FastStartFailoverOBID1>688061044</FastStartFailoverOBID1>
  <FastStartFailoverOBID2>688061045</FastStartFailoverOBID2>
  <FastStartFailoverOBID3>688061046</FastStartFailoverOBID3>
  <Configuration_Name>DG_CONF</Configuration_Name>
  <Member MemberID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" Name="orcl">
    <IntendedState>PRIMARY</IntendedState>
    <DefaultState>PRIMARY</DefaultState>
    <Status>
      <Severity>Failure</Severity>
      <Error>12541</Error>
      <Timestamp>1704554000</Timestamp>
    </Status>
    <StandbyType>PhysicalStandby</StandbyType>
    <DGConnectIdentifier>orclprim</DGConnectIdentifier>
    <DbDomain/>
    <ResourceType>Database</ResourceType>
    <Instance InstanceID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True" Name="orcl">
      <PlannedState/>
      <HostName Default="True">oracent19c</HostName>
      <InstanceName>orcl</InstanceName>
      <Status>
        <Severity>Success</Severity>
        <Error>0</Error>
        <Timestamp>1696320778</Timestamp>
      </Status>
      <StaticConnectIdentifier>orclstdy</StaticConnectIdentifier>
    </Instance>
    <Role>
      <Condition>PRIMARY</Condition>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>READ-WRITE-XPTON</IntendedState>
    </Role>
    <Role>
      <Condition>STANDBY</Condition>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <DB_Unique_Name>orcl</DB_Unique_Name>
    <ClusterDatabase>FALSE</ClusterDatabase>
    <LogXptMode Default="True">ASYNC</LogXptMode>
    <IncarnationTable>2,1920977,1145033651,1*1,1,1005785759,0#</IncarnationTable>
    <SRLStatus>1</SRLStatus>
    <CurrentRole>PRIMARY</CurrentRole>
    <PlannedState/>
    <FastStartFailoverTarget>orcl_stdy</FastStartFailoverTarget>
    <DbChangeCritical>FALSE</DbChangeCritical>
    <DbIsCritical>FALSE</DbIsCritical>
    <ActualApplyInstance/>
  </Member>
  <Status>
    <Severity>Warning</Severity>
    <Error>16607</Error>
    <Timestamp>1704554057</Timestamp>
  </Status>
  <DRC_UNIQUE_ID_SEQUENCE>2</DRC_UNIQUE_ID_SEQUENCE>
  <ConfigurationWideServiceName>orcl_CFG</ConfigurationWideServiceName>
  <OVERALL_PROTECTION_MODE>3</OVERALL_PROTECTION_MODE>
  <FastStartFailoverLagLimit Default="True">30</FastStartFailoverLagLimit>
  <IntendedState>ONLINE</IntendedState>
  <Member MemberID="2" CurrentPath="True" Enabled="True" MultiInstanced="True" Name="orcl_stdy">
    <PlannedState>STANDBY</PlannedState>
    <DefaultState>STANDBY</DefaultState>
    <Status>
      <Severity>Success</Severity>
      <Error>0</Error>
      <Timestamp>1704555032</Timestamp>
    </Status>
    <StandbyType>PhysicalStandby</StandbyType>
    <DGConnectIdentifier>orcldstdy</DGConnectIdentifier>
    <DbDomain/>
    <ResourceType>Database</ResourceType>
    <Instance InstanceID="1" CurrentPath="True" Enabled="True" MultiInstanced="True" DefaultWriteOnce="True" Name="orcls">
      <PlannedState/>
      <HostName Default="True">oracentd19c</HostName>
      <InstanceName>orcls</InstanceName>
      <StaticConnectIdentifier>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.56)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcls_DGMGRL)(SID=orcls)(SERVER=DEDICATED)))</StaticConnectIdentifier>
      <Status>
        <Severity>Success</Severity>
        <Error>0</Error>
        <Timestamp>1704555032</Timestamp>
      </Status>
    </Instance>
    <Role>
      <Condition>PRIMARY</Condition>
      <DefaultState>READ-WRITE-XPTON</DefaultState>
      <IntendedState>OFFLINE</IntendedState>
    </Role>
    <Role>
      <Condition>STANDBY</Condition>
      <DefaultState>PHYSICAL-APPLY-ON</DefaultState>
      <IntendedState>PHYSICAL-APPLY-ON</IntendedState>
    </Role>
    <DB_Unique_Name>orcl_stdy</DB_Unique_Name>
    <ClusterDatabase>FALSE</ClusterDatabase>
    <LogXptMode Default="True">ASYNC</LogXptMode>
    <IncarnationTable>2,1920977,1145033651,1*1,1,1005785759,0#</IncarnationTable>
    <SRLStatus>2</SRLStatus>
    <IntendedState>STANDBY</IntendedState>
    <CurrentRole>STANDBY</CurrentRole>
    <DbIsCritical>FALSE</DbIsCritical>
    <FastStartFailoverTarget>orcl</FastStartFailoverTarget>
    <DbChangeCritical>FALSE</DbChangeCritical>
    <DelayMins>0</DelayMins>
    <ActualApplyInstance>orcls</ActualApplyInstance>
  </Member>
  <ObserverInfo1>localhost.localdomain</ObserverInfo1>
  <ObserverInfo2>oracentd19c</ObserverInfo2>
  <ObserverName1>localhost.localdomain</ObserverName1>
  <ObserverVersion1>386007040</ObserverVersion1>
  <FSFO_MIV>4</FSFO_MIV>
  <ObserverName2>oracentd19c</ObserverName2>
  <ObserverVersion2>318963712</ObserverVersion2>
  <FastStartFailoverMode>0</FastStartFailoverMode>
  <EXT_COND>7</EXT_COND>
  <RoleChangeHistory>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>orcl</OldPrimary>
      <NewPrimary>orcl_stdy</NewPrimary>
      <Status>0</Status>
      <Timestamp>1149239579</Timestamp>
    </RoleChangeRecord>
    <RoleChangeRecord>
      <Event>PhysicalSwitchover</Event>
      <OldPrimary>orcl_stdy</OldPrimary>
      <NewPrimary>orcl</NewPrimary>
      <Status>0</Status>
      <Timestamp>1149241834</Timestamp>
    </RoleChangeRecord>
  </RoleChangeHistory>
  <MIV>0</MIV>
  <PRIMARY_SITE_ID>513</PRIMARY_SITE_ID>
</DRC>
[oracle@oracentd19c trace]$




==================================



[oracle@oracentd19c trace]$ ls -ltr *.txt
-rw-r--r--. 1 oracle oinstall 5490 Jan  6  2024 anuj_dg_config.txt


DGMGRL> IMPORT CONFIGURATION FROM anuj_dg_config.txt

ORA-16629: database reports a different protection level from the protection mode

ORA-16629: database reports a different protection level from the protection mode

DGMGRL> show configuration ;

Configuration - DG_CONF

  Protection Mode: MaxAvailability  <<<<<<----
  Members:
  orcl_stdy - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    orcl      - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 55 seconds ago)


standby 

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


primary 


[-PRIMARY-]sys@orcl_stdy> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION  <<<<< 



DGMGRL> edit configuration set protection mode as MAXPERFORMANCE;
Succeeded.




DGMGRL> show configuration ;

Configuration - DG_CONF

  Protection Mode: MaxPerformance
  Members:
  orcl_stdy - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    orcl      - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 59 seconds ago)





after some time ..

DGMGRL> show configuration ;

Configuration - DG_CONF

  Protection Mode: MaxPerformance
  Members:
  orcl_stdy - Primary database
    orcl      - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

DGMGRL>