We're here to help!

Add table to running replication

Follow

Add table to Replication – Dbvisit Replicate 2.9.x

 

NOTE: ** On the source (optional) If this is a larger schema(s)/table(s) you are going to export it may be worth checking the below so as to not get rollback segment too old when it is running on the Source database.

-- Reset the undo low threshold or to a value that is appropriate, OEM may provide some advise in this area, You can use the UNDO advisor to find out how large this tablespace should be given a desired UNDO retention, or look online for some scripts… https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm - ADMIN11461

 

for example,

-- Guarantee the minimum threshold is maintained.

ALTER TABLESPACE <undotbs1> RETENTION GUARANTEE;

and

ALTER SYSTEM SET UNDO_RETENTION = 14400;

or larger depending on your environment

ALTER SYSTEM SET UNDO_RETENTION = 64400;

Etc..

 

1)   If the table does not have a primary or unique key run supp_logging.sql script on source as sqldba, Addendum A

SQL> @supp_logging.sql

 

Then start console On Source (after cd'ing to your replication HOME)

$ ./start_console.sh

dbvrep> ENGINE SUPPLEMENTAL LOGGING TABLE <owner.table> ENABLE PRIMARY KEY                                                                          

dbvrep> ENGINE WAIT_SCN_FLIP

dbvrep> pause apply

dbvrep> pause mine

 

Run SQL On source as sysdba lock & get an SCN:

SQL> lock table <owner.table> in share mode;

 

COLUMN instantiate_scn   FORMAT A15

COLUMN current_timestamp FORMAT A40

SELECT 'instantiateScn' as instantiate_scn, current_scn, current_timestamp

FROM v$database;

 

INSTANTIATE_SCN CURRENT_SCN CURRENT_TIMESTAMP

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

instantiateScn    137163780 12-AUG-15 05.47.19.057016 AM +12:008021 12-AUG-15 05.43.17.133180 AM +12:00

 Use this SCN from db (use this scn in below PREPARE) in datapump link

 

-- Release Lock on Source

SQL> ROLLBACK;

 

2) In Console using SCN from above query

dbvrep> PREPARE AS OF <scn> TABLE <owner.table>

ie dbvrep> PREPARE AS of 137163780 scott.dept

If they -- Release Lock on Source

SQL> ROLLBACK;

 

In Console using SCN from above query

dbvrep> PREPARE AS OF <scn> TABLE <owner.table>

 

* USE SCN from above for ea table & datapump via network using SCN from above

-- note database link created in target system <DBLINK>

OR use expdp as of flashback_scn=<snc> then impdp into target.

 

3) Export On source server not using dblink

expdp SYSTEM/xxx@<SourceDB> dumpfile=<owner_tablename>.dmp directory=DATA_PUMP_DIR flashback_scn=<scn> tables=<owner.tablename> logfile=exp_<owner_tablename>.log

JOB_NAME=DP_TEST_0001

 

Check for any export issues/errors in log file. Make sure rows were exported successfully

 

Then scp file to target & put in the DATA_PUMP_DIR

impdp SYSTEM/xxx@<@<TargetDB> dumpfile=exp_<owner_tablename>.dmp directory=DATA_PUMP_DIR   logfile=imp__<owner_tablename>.log JOB_NAME=DP_TEST_0002

 

Check for any import issues/errors in log file. Make sure rows were imported successfully

 

 

After Import on Oracle Target – SQLS/Post_Import.sql

Script performs the below

Disables any triggers that may have been imported on this table on the target as well as any delete constraints on these tables.

See addendum 2 for Sample script : post_import.sql

  • Find & disable triggers
  • Find & disable ‘DELETE CASCADE’ constraints on target
  • Find & disable any NOT NULL constraints on LOB columns on target

 

Resume APPLY process in the console after triggers & delete constraints are disabled & LOB columns defined as NOT Null are now NULL on TARGET.

 

dbvrep> resume apply

dbvrep> resume mine

 

Once MINE/APPLY are back in sync start repeat to add next table in set.

 If they 

 

*********  ADDENDUM 1 – Sample Script :  supp_logging.sql *********

supp_logging.sql

 

Note this may be adjusted to exclude the whole list & just for specific tables selected for replication see black sections

 

REM Dynamic SQL to add supplemental logging all columns with no Primary or Unique Key

REM *** Script name : supp_logging.sql

REM Run as many times as you need to for your data set as SYSDBA

REM ***

 

set linesize 400

set pagesize 20000

column owner format A40

column table_name format A60

 

purge DBA_RECYCLEBIN;

 

set echo off

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

ttitle left '***** Tables without PK or UK output to nokeys.lst *****' skip 2

btitle off

 

spool supp_logging_r.sql

 

select 'ALTER TABLE '||owner||'.'||table_name||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;'

from dba_tables

where upper(owner) = UPPER('&vschema')

and table_name = upper('&vtable')

and (owner, table_name) not in (

select owner, table_name

   from dba_constraints

   where constraint_type in ( 'P', 'U' )

   and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

   'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

   'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

   'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000')

)

order by owner, table_name

//

 

spool off

set echo on

 

REM ** Run script then exit **

@supp_logging_r.sql

commit;

 

exit

 

 

*  ADDENDUM 1b – Opt'l: Sample Script add Full Schema :  supp_logging.sql *

REM *** supp_logging.sql

REM ***

REM *** FULL SCHEMA

REM *** to be run on source BEFORE preparing table for replication

REM ***

REM *** Dynamic SQL to add supplemental logging all columns with no Primary or Unique Ke

REM *** Run as many times as you need to for your data set as SYSDBA

REM ***

 

set linesize 400

set pagesize 20000

column owner format A40

column table_name format A60

 

purge DBA_RECYCLEBIN;

 

set echo off

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

ttitle left '***** Tables without PK or UK output to nokeys.lst *****' skip 2

btitle off

 

spool supp_logging_r.sql

 

select 'ALTER TABLE '||owner||'.'||table_name||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;'

from dba_tables

where upper(owner) = UPPER('&schema')

and table_name not like 'BIN%'

and (owner, table_name) not in (

select owner, table_name

   from dba_constraints

   where constraint_type in ( 'P', 'U' )

   and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

   'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

   'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

   'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

and table_name not like 'BIN%')

order by owner, table_name

/

 

spool off

set echo on

 

REM ** Run script then exit **

@supp_logging_r.sql

commit;

 

exit

 

 ************  ADDENDUM 2 – Sample Script :post_import.sql 

post_import.sql

 

 

REM **************************************

REM **** post_import.sql

REM ****

REM **** To be run as sqldba on TARGET

REM **************************************

Prompt …Re-compile the schema for each user imported on target (uppercase)

set serveroutput on

 

prompt Schema to compile:

exec DBMS_UTILITY.COMPILE_SCHEMA(schema => upper('&&vstring'));

prompt....

prompt

 

prompt Schema to gather stats:

exec dbms_stats.gather_schema_stats('&&vstring',cascade => TRUE);

 

set echo off

Commit;

 

prompt REM Running post_import.sql ...... spool post_import_r.sql

prompt REM .......

prompt REM ***** Compiling Disable triggers for a schema on target

 

   REM ---------------------------------------------------------------

   REM Run this query on source to generate the disable_arch_triggers.sql

   REM -------------------------------

   REM Spool file : disable_arch_triggers_r.sql

   REM -------------------------------  

   REM

SET FEEDBACK OFF

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_arch_triggers_r.sql

 

prompt REM ***** Compiling Disable triggers for a schema on target

 

spool post_import_r.sql

 

select 'alter trigger '||owner||'.'||trigger_name||' disable;'

from dba_triggers

where owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

REM spool off

 

REM ***> RUN SQL

REM @disable_arch_triggers_r.sql

 

 

prompt REM ***** Compiling Find to disable ‘DELETE CASCADE’ constraints on target

 

REM Prompt Disable delete constraints

 

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_del_cons_r.sql

 

select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'

from dba_constraints

where delete_rule = 'CASCADE'

and owner not in

('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',

'FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000','APEX_030200');

 

REM ***> RUN SQL

REM @disable_del_cons_r.sql

 

 

REM *******************************************************

REM   Check other unsupported constraint types on TARGET db only

REM generates the disable constraint command for these

 

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_del_R_cons_r.sql

 

select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'

from dba_constraints

where constraint_type = 'R'

and delete_rule!='NO ACTION'

and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',

'FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

 

REM spool off

 

REM ***> RUN SQL

REM @disable_del_R_cons_r.sql

 

 

prompt REM ***** Compiling Check for LOBs that have NOT NULL constraints and disable on target

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_notnullcons_r.sql

 

select 'alter table ' ||owner||'.'||table_name||' MODIFY (' || column_name||

' NULL);'

FROM ALL_TAB_COLUMNS a

where a.DATA_TYPE in ('CLOB','BLOB','NCLOB','BFILE')

and a.owner not in ('XDB', 'OEM','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000')

and nullable = 'N'

order by owner, table_name;

 

REM spool off

 

REM ***> RUN SQL

REM @disable_notnullcons_r.sql

 

spool off

prompt

 

prompt REM RUNNING... @post_import_r.sql

! cat post_import_r.sql

@post_import_r.sql

 

alter system switch logfile;

 

exit

 

 

 

 

*** ODDS : Defining data_pump_dir in Oracle database ****

Location of data pump dir

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories

where DIRECTORY_NAME = 'DATA_PUMP_DIR';

 

 

 

to change location

SQL> CREATE DIRECTORY DATA_PUMP_DIR AS '/backup/EBSPATCH/dpdump; Directory created.  SQL> GRANT read, write ON DIRECTORY dmpdir TO scott; Grant succeeded.

 

 

* USE SCN from above for ea table & datapump via network using SCN from above

-- note database link created in target system <DBLINK>

OR use expdp as of flashback_scn=<snc> then impdp into target.

 

NOTE: ** On the source (optional) If this is a larger schema(s)/table(s) you are going to export it may be worth checking the below so as to not get rollback segment too old when it is running on the Source database.

-- Reset the undo low threshold or to a value that is appropriate, OEM may provide some advise in this area, You can use the UNDO advisor to find out how large this tablespace should be given a desired UNDO retention, or look online for some scripts… https://docs.oracle.com/cd/B28359_01/server.111/b28310/undo002.htm - ADMIN11461

 

 

for example,

-- Guarantee the minimum threshold is maintained.

ALTER TABLESPACE <undotbs1> RETENTION GUARANTEE;

 

and

ALTER SYSTEM SET UNDO_RETENTION = 14400;

or

ALTER SYSTEM SET UNDO_RETENTION = 64400;

Etc..

 

 

 

 

Export On source server not using dblink

expdp SYSTEM/xxx@<SourceDB> dumpfile=<owner_tablename>.dmp directory=DATA_PUMP_DIR flashback_scn=<scn> tables=<owner.tablename> logfile=exp_<owner_tablename>.log

JOB_NAME=DP_TEST_0001

 

Check for any export issues/errors in log file. Make sure rows were exported successfully

 

Then scp file to target & put in the DATA_PUMP_DIR

impdp SYSTEM/xxx@<@<TargetDB> dumpfile=exp_<owner_tablename>.dmp directory=DATA_PUMP_DIR   logfile=imp__<owner_tablename>.log JOB_NAME=DP_TEST_0002

 

Check for any import issues/errors in log file. Make sure rows were imported successfully

 

 

After Import on Oracle Target – SQLS/Post_Import.sql

Script performs the below

Disables any triggers that may have been imported on this table on the target as well as any delete constraints on these tables.

See addendum 1 for Sample script : post_import.sql

  • Find & disable triggers
  • Find & disable ‘DELETE CASCADE’ constraints on target
  • Find & disable any NOT NULL constraints on LOB columns on target

 

Resume APPLY process in the console after triggers & delete constraints are disabled & LOB columns defined as NOT Null are now NULL on TARGET.

 

dbvrep> resume apply

dbvrep> resume mine

 

Once MINE/APPLY are back in sync start repeat to add next table in set

 

*******************

ADDENDUM 1 – Sample Script

supp_logging.sql

 

Note this may be adjusted to exclude the whole list & just for specific tables selected for replication see black sections

 

REM Dynamic SQL to add supplemental logging all columns with no Primary or Unique Key

REM *** Script name : supp_logging.sql

REM Run as many times as you need to for your data set as SYSDBA

REM ***

 

set linesize 400

set pagesize 20000

column owner format A40

column table_name format A60

 

purge DBA_RECYCLEBIN;

 

set echo off

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

ttitle left '***** Tables without PK or UK output to nokeys.lst *****' skip 2

btitle off

 

spool supp_logging_r.sql

 

select 'ALTER TABLE '||owner||'.'||table_name||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;'

from dba_tables

where upper(owner) = UPPER('&vschema')

and table_name = upper('&vtable')

and (owner, table_name) not in (

select owner, table_name

   from dba_constraints

   where constraint_type in ( 'P', 'U' )

   and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

   'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

   'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

   'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000')

)

order by owner, table_name

//

 

spool off

set echo on

 

REM ** Run script then exit **

@supp_logging_r.sql

commit;

 

exit

 

 

 

 

 

 

REM *** supp_logging.sql

REM ***

REM *** FULL SCHEMA

REM *** to be run on source BEFORE preparing table for replication

REM ***

REM *** Dynamic SQL to add supplemental logging all columns with no Primary or Unique Ke

REM *** Run as many times as you need to for your data set as SYSDBA

REM ***

 

set linesize 400

set pagesize 20000

column owner format A40

column table_name format A60

 

purge DBA_RECYCLEBIN;

 

set echo off

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

ttitle left '***** Tables without PK or UK output to nokeys.lst *****' skip 2

btitle off

 

spool supp_logging_r.sql

 

select 'ALTER TABLE '||owner||'.'||table_name||' ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;'

from dba_tables

where upper(owner) = UPPER('&schema')

and table_name not like 'BIN%'

and (owner, table_name) not in (

select owner, table_name

   from dba_constraints

   where constraint_type in ( 'P', 'U' )

   and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

   'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

   'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

   'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

and table_name not like 'BIN%')

order by owner, table_name

/

 

spool off

set echo on

 

REM ** Run script then exit **

@supp_logging_r.sql

commit;

 

exit

 

 


 

 

ADDENDUM 2 – Sample Script

post_import.sql

 

 

REM **************************************

REM **** post_import.sql

REM ****

REM **** To be run as sqldba on TARGET

REM **************************************

Prompt …Re-compile the schema for each user imported on target (uppercase)

set serveroutput on

 

prompt Schema to compile:

exec DBMS_UTILITY.COMPILE_SCHEMA(schema => upper('&&vstring'));

prompt....

prompt

 

prompt Schema to gather stats:

exec dbms_stats.gather_schema_stats('&&vstring',cascade => TRUE);

 

set echo off

Commit;

 

prompt REM Running post_import.sql ...... spool post_import_r.sql

prompt REM .......

prompt REM ***** Compiling Disable triggers for a schema on target

 

   REM ---------------------------------------------------------------

   REM Run this query on source to generate the disable_arch_triggers.sql

   REM -------------------------------

   REM Spool file : disable_arch_triggers_r.sql

   REM -------------------------------  

   REM

SET FEEDBACK OFF

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_arch_triggers_r.sql

 

prompt REM ***** Compiling Disable triggers for a schema on target

 

spool post_import_r.sql

 

select 'alter trigger '||owner||'.'||trigger_name||' disable;'

from dba_triggers

where owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS',

'APEX_030200','FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA',

'OUTLN','DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS',

'FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

REM spool off

 

REM ***> RUN SQL

REM @disable_arch_triggers_r.sql

 

 

prompt REM ***** Compiling Find to disable ‘DELETE CASCADE’ constraints on target

 

REM Prompt Disable delete constraints

 

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_del_cons_r.sql

 

select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'

from dba_constraints

where delete_rule = 'CASCADE'

and owner not in

('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',

'FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000','APEX_030200');

 

REM ***> RUN SQL

REM @disable_del_cons_r.sql

 

 

REM *******************************************************

REM   Check other unsupported constraint types on TARGET db only

REM generates the disable constraint command for these

 

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_del_R_cons_r.sql

 

select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'

from dba_constraints

where constraint_type = 'R'

and delete_rule!='NO ACTION'

and owner not in ('OEM','XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',

'FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000');

 

REM spool off

 

REM ***> RUN SQL

REM @disable_del_R_cons_r.sql

 

 

prompt REM ***** Compiling Check for LOBs that have NOT NULL constraints and disable on target

SET HEADING OFF

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

REM spool disable_notnullcons_r.sql

 

select 'alter table ' ||owner||'.'||table_name||' MODIFY (' || column_name||

' NULL);'

FROM ALL_TAB_COLUMNS a

where a.DATA_TYPE in ('CLOB','BLOB','NCLOB','BFILE')

and a.owner not in ('XDB', 'OEM','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200','FLOWS_FILES',

'EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN','DBSNMP','ORDSYS','DBVREP',

'APEX_040200','LBACSYS','FLOWS_030000','GSMADMIN_INTERNAL','APEX_050000')

and nullable = 'N'

order by owner, table_name;

 

REM spool off

 

REM ***> RUN SQL

REM @disable_notnullcons_r.sql

 

spool off

prompt

 

prompt REM RUNNING... @post_import_r.sql

! cat post_import_r.sql

@post_import_r.sql

 

alter system switch logfile;

 

exit

 

 

 

ODDS:

Location of data pump dir

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories

where DIRECTORY_NAME = 'DATA_PUMP_DIR';

 

 

 

to change location

SQL> CREATE DIRECTORY DATA_PUMP_DIR AS '/backup/EBSPATCH/dpdump; Directory created.  SQL> GRANT read, write ON DIRECTORY dmpdir TO scott; Grant succeeded.

 

Have more questions? Submit a request

Comments