Migration from 10g to 18c using datapump

on new server
++++++++++++

1)change the database in no archive mode
2) drop the users
3)new_sql_objects
4)dbmon_create_initial
5)create dssadmin.sql
5-1)create user password
5-2)database link
6)impdp.par
7)compile invalid objects and stats
8)make_statements.sql
8.1)grant special
9)check constraints
10 grant omnixpress
11 check partition
12 change_user password
13)archive mode


coming soon ....

Refreshing table with one year data from production user datapume

please do refresh for below tables from production(PX_USER) to QA (DWDB) environment.


step one :


script location :  /home/oracle/work.ravi

expdp

USERID = "/ as sysdba"
DIRECTORY=EXPDP_TEST_DIR
tables=OMEDW.WPART_NUMBER_D
dumpfile=expdp_p_SRI_USER_part.dmp
logfile=expdp_pwdb_SRI_USER_part.log
parallel=1


impdp.par


USERID = "/ as sysdba"
DIRECTORY=EXPDP_DIR
dumpfile=expdp_pdb_SRI_USER_112.dmp
logfile=IMP_pdb_SRI_USER_112.log
tables=OMEDW.W_RT_NUMBER_D
table_exists_action=truncate
parallel=1

-------------------------------------------------------------------------------------------------------------------------
select count(*) from OMEDW.ENT_DETAILS_F where PLAN_DATE > '01-MAY-18';

expdp.par

USERID = "/ as sysdba"
DIRECTORY=EXPDP_TEST_DIR
tables=OMEDW.W_RP_MED_ASSIENT_DETAILS_F
QUERY=OMEDW.W_RP_MED_ASSMENT_DETAILS_F:"WHERE PLAN_DATE > '01-MAY-18'"
dumpfile=expdp_W_RP_MED_ASSINT_DETAILS_F.dmp
logfile=expdp_W_RP_MED_ASSINT_DETAILS_F.log
parallel=1
compression=all


nohup expdp parfile=exp_oneyear.par &


impdp



USERID = "/ as sysdba"
DIRECTORY=EXPDP_D
tables=OMEDW.W_RP_MED_ASSMENT_DETAILS_F
dumpfile=expdp_W_RP_MED_ASSMENT_DETAILS_F.dmp
logfile=impdp_W_RP_MED_ASSENT_DETAILS_F.log
table_exists_action=truncate
parallel=1



nohup impdp parfile=imp_oneyear.par &



11/14/2018


++++++++++++++++++++++++++++++++++++++++++
kill the job


select * from dba_directories;
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs


expdp \'/ as sysdba\' attach=" filename"


kill_job

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs

Errors:
______

1)ORA-39120: Table "OMEDW"."W_RP_MED_ASSIGNMENT_F" can't be truncated, data will be skipped. Failing error
is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00955: name is already used by an existing object
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

solution : Due to some table update by the end user , import was not happening.
reran it will run successfully after some times.

2)

error:
ORA-31693: Table data object "OMEDW"."W_OMNI_PART_NUMBER_D" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-20000: PART_NUMBER AND ORIGINAL_PART_NUMBER ARE NOT EQUAL
ORA-06512: at "OMEDW.SRI_PART_CHANGES", line 6
ORA-04088: error during execution of trigger 'OMEDW.SRI_PART_CHANGES'

solution : disable the trigger and try to import.

alter trigger OMEDW.S_PART_CHANGES disable;

alter trigger OMEDW.S_PART_CHANGES enable;