Database Refresh using datapump

Database Refresh :export and import using datapump

Compare columns between schemas before import if any difference then fix it before import

select table_name, column_name
from dba_tab_columns@dabaselink
where owner = 'OWNER'
and table_name not like 'BIN$%'
minus
select table_name, column_name
from dba_tab_columns
where owner = 'OWNER'
and table_name not like 'BIN$%

compare tablespace usage between source and target database tablespace usage

copy and paste the script and add dblink name in the script

ttitle 'Version Check'

select case substr(name,1,2)
         when 'FN' then 'I reckon you want to run the fn version of this script'
         when 'HR' then 'Version checked okay'
         else 'What database are you in? '||name
         end check_db
from v$database;

ttitle 'Data Tablespace Comparison'
set lines 150
set pages 300

select prod.tablespace_name,
       prod.TOTAL-prod.free prod_used,
       local.total local_available,
       case when local.total - (prod.total - prod.free) < 0
           then ((prod.total - prod.free) - local.total)
           else 0
           end MB_Required
       ,
       case
       when local.total - (prod.total - prod.free) < 0 then 'Aaargh'
       else 'Okay'
       end "OOGY"
from
(select  'PROD',
        df.tablespace_name,
        df.MB TOTAL,
        decode(fs.MB,null,0,fs.MB) FREE,
        to_number(to_char((decode(fs.MB,null,0,fs.MB)
                             *100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files@dblinkname
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space@dblinkname
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
) prod
left join
(select  'LOCAL',
        df.tablespace_name,
        df.MB TOTAL,
        decode(fs.MB,null,0,fs.MB) FREE,
        to_number(to_char((decode(fs.MB,null,0,fs.MB)
                             *100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
) local
on local.tablespace_name = prod.tablespace_name
order by 4 desc;

title 'temp space comparison'

select prod.tablespace_name, (local.MB - prod.MB) spare_mb,
       case
          when (nvl(local.mb,0) - prod.mb) < 0 then 'Aaargh'
          else 'Whew!'
       end oogy
from
(
select 'PROD' host, tablespace_name, sum(bytes)/1024/1024 MB
from dba_temp_files@dblinkname
group by 'PROD',tablespace_name) prod
left join
(select 'LOCAL' host, tablespace_name, sum(bytes)/1024/1024 MB
from dba_temp_files
group by 'LOCAL',tablespace_name) local
on prod.tablespace_name = local.tablespace_name;

1. Export the dumpfile from source

select * from dba_directories;

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';

CREATE DIRECTORY datapump1 AS '/u01/app/XXXX/admin/TRAINING/dpdump';

GRANT READ, WRITE ON DIRECTORY datapump1 to system;

expdp system/password@training FULL=y DIRECTORY=datapump1 DUMPFILE=training1.dmp LOGFILE=training1.log

or

expdp username/password parfile=export.par

 par fle information

directory=data_pump_dir
logfile=FullUAT.log
dumpfile=FullUAT.dmp
SCHEMAS=schemaname
CONTENT=ALL
PARALLEL=16

Kill  Datapump job (Document ID=336014.1)

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

SQL> select job_name, state from dba_datapump_jobs;

UNIX\> impdp \’sys/aa as sysdba\’ attach=SYS_IMPORT_SCHEMA_01

IMPORT> STATUS –> This shows the status of your job

IMPORT> KILL_JOB –> This will kill the datapump job and remove the underlying base tables

START_JOB, STOP_JOB, EXIT_CLIENT are few other options while using datapump.

impdp help=y or expdp help=y lists all of them

Due to some locking conditions, if you are not able to kill the job using above method, you can also drop the datapump master table – pls make sure you are dropping the right table as this is irreversible.

UNIX\> sqlplus ‘/as sysdba’
SQL> drop table SYS_IMPORT_SCHEMA_01;
SQL> exit

Before import Disable triggers

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool triggerdisable.sql
select 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' disable;' from dba_triggers where TABLE_OWNER='schemaname';
spool off

Import

impdp system/password parfile=import.par

where import.par is

directory=data_pump_dir
dumpfile=file.dmp
logfile=filetst.log
schemas=schemaname
parallel=16
table_exists_action=truncate

After  Import comapre soure and target schema objects

select object_name, object_type from dba_objects@dblinkname where owner='xxxxx'and (object_name, object_type) not in (select object_name, object_type from dba_objects where owner='xxxxx')

select index_name from dba_indexes@dblink where owner ='xxxxx' minus select index_name from dba_indexes where owner = 'xxxx'

select TABLE_NAME from dba_tables@dblink where owner ='xxxxx' minus select TABLE_NAME from dba_tables where owner = 'xxxxx'

enable triggers

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool triggerenable.sql
select 'alter trigger '||OWNER||'.'||TRIGGER_NAME||' enable;' from dba_triggers where TABLE_OWNER='schema name';
spool off