transform=constraint_novalidate:y
Data Pump (impdp) parameter speeds up imports
by creating constraints asENABLE 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
Tuesday, 5 May 2026
oracle transform=constraint novalidate:y oracle >19.27
Oracle 19C : Exporting and Importing Broker Configuration
Oracle 19C : Exporting and Importing Broker Configuration
Oracle 19C : Exporting and Importing Broker Configurationdgmgrl EXPORT CONFIGURATION[oracle@oracentd19c ~]$ dgmgrl /DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue May 5 08:06:17 2026Version 19.31.0.0.0Copyright (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.txtSucceeded.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.txtDGMGRL> IMPORT CONFIGURATIONFROM 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>