Trick to trace or Dig export/Datapump slow job ...

Here is some trick to trace or Dig export/Dp slow job ...

For a detailed way to trace the DP processes and see what is going on behind the scenes follow the below steps :

-----------------------
1. Gather statistics for the dictionary and fixed objects. See MOS note:
Note 473423.1 Ext/Pub Poor Performance With DataPump Export on Large Databases

2. We will need to trace Data Pump export this way:

++ before restarting the expdp check if there are orphan Data Pump jobs left in database and remove them.
Use Note 336014.1 - "How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?"

++ Add/Replace the following parameters: METRICS=Y TRACE=480300 in expdp command line

3.
After expdp job started:

-- In SQL*Plus, obtain Data Pump process info:
CONNECT / as sysdba

ALTER SYSTEM SET MAX_DUMP_FILE_SIZE=UNLIMITED;

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

-- Get the sid and serial# for DM00 and DW00 and execute :
exec DBMS_SYSTEM.SET_EV([SID],[SERIAL#],10046,12 ,''); !!!!!!! Execute this for each process : DM00, DW00

If you reach the point where it is hanging let it for a couple of minutes and kill the process.

++ create standard tkprof output files for Data Pump Master and Worker SQL traces generated in bdump directory. For example:

% tkprof orcl_dm00_17292.trc tkprof_orcl_dm00_17292.out waits=y sort=exeela
% tkprof orcl_dw01_17294.trc tkprof_orcl_dw00_17294.out waits=y sort=exeela

------------------------
Check DM0, DW0 traces the TKPROFs obtained, this will give you the exact SQL that makes datapump to hang. The TKPROF outputs are ordered by the wait time.

High level step from upgrading database from 11.2.0.2 to 11.2.0,4 .

Below are simple high level steps  from upgrading database , Using below steps i upgraded 08 Prod database from 11.2.02 to 11.2.0.4 . here my source and target home is Some location .
 
1.- New Oracle home need to be install on server.
2.- run this command in new home  to pre check error
  NEW_ORACLE_HOME/db/rdbms/admin/utlu112i.sql
  
3-check spfile in common location
4-then run this command 
SQL> alter system set cluster_database=false scope=spfile sid='*';
5-Copy pfile in new home
6-export new oracle home :to make sure db home is new home
7- startup upgrade
8-@?/rdbms/admin/catupgrd.sql
9- shutdown immediate
10-startup
11-@?/rdbms/admin/catupgrd.sql
12@?/rdbms/admin/utlrp.sql
13-Change env file new home ~/bin/sid
14-Copy listener and tns new home  , please ignore if  listener is running from GI Home. 
15- copy password file into new HO .
16- now let upgrade new RDBMS Home(from where Oracle running )
srvctl upgrade database -d db_unique_name -o Oracle_home 
 (change above to db_unique_name and Oracle_home)
17- Now lets  make cluster_database=true in parameter file
alter system set cluster_database=true sid='*' scope=spfile; 
18- Change or update new HO in env file and pfile on all rac node.
19 -Using srvctl start your db.
20- check if all component are upgraded to new version.
SELECT comp_name || ' : '||version|| ' : '|| status FROM dba_registry;

Creating Standby Database using RMAN -

Creating Physical Standby Database using RMAN -

db name is -sync
Primary Db instance/db_unique_name name is  sync
Standby Db instance/db_unique_name name is  syncxx

Some pre_check - please make sure your Primary db is archive log mode , and  forced Logging is enable before moving to next step.

Step_1 -TAKE A LEVEL O BACKUP @Primary database .

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

Step_2 -TAKE CONTROL FILE BACKUP and parameter file for standby db.@Primary database .

 backup current controlfile for standby format '/shared/sync/8aug_backup/CTRL.bck';

Step_3- scp * standby_hostname:/backup/8aug_backup  @Primary database .
---- Come out from Primary db here --- all below command we will run into standby db server.-----

Step_4- Copy the primary database parameter file and make necessary changes :@Standby database .


sync.__db_cache_size=1610612736
sync.__java_pool_size=16777216
sync.__large_pool_size=16777216
sync.__shared_pool_size=452984832
sync.__streams_pool_size=33554432
*.background_dump_dest='/backup/syncxx/bdump/'
*.control_files='/backup/syncxx/data/control01.ctl'
*.core_dump_dest='/backup/syncxx/cdump/'
*.db_block_size=8192
*.db_name='sync'
instance_name = syncxx
service_names = syncxx
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
db_unique_name=syncxx
#*log_archive_dest_1='/backup/syncxx/arch'
*.processes=100
*.sessions=100
*.sga_target=2G
*.undo_management='AUTO'
standby_file_management = auto
*.user_dump_dest='/backup/syncxx/udump/'
-- create above folder -- like ( adump,bdump,data etc...)

Step_5 Startup nomount in standby server.@Standby database .

Step_6 Open one more standby db into putty** session and tail alerts logfile of standby db . .and keep eye on standby db .@Standby database
Step_7 restore control_file in standby.@Standby database .
      "restore standby controlfile from '/backup/8aug_backup/CTRL.bck'"

Step_8- Keep database into mount state now@Standby database .
      "Alter database Open"    
Step_9- Catalog the backup pieces@Standby database .
    "CATALOG START WITH '/backup/8aug_backup/';"

Step_10-Restore database in new location.@Standby database .

run
{
SET NEWNAME FOR DATAFILE 1 TO '/backup/syncxx/data/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/backup/syncxx/data/undo01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/backup/syncxx/data/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/backup/syncxx/data/users.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/backup/syncxx/data/users01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/backup/syncxx/data/users02.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/backup/syncxx/data/system04.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/backup/syncxx/data/tmp.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}

---- Since my db is smalll after taking backup only 25 arch files was created so i am not going to take another L1 backup .
if your db is big and more arch file was created aftre taking backup let's tale another L1 backup and appy that on standby db (via rman recover command)

Here your standby database is created , :) but archive logfile will not apply Automatically  on standby side.

Step_11-Create Listner on standby Db and  add new db detail into both Primary and standby db tns file @Standby database

Step_12-logfile into Primary db and copy passowrd file into standby Db , plz do not forget to change password file name and standb db.and update log_archive_dest_2/DG_CONFIG into Primary db server.@Primary database .

log_archive_dest_2 = 'SERVICE=STANDBY LGWR ASYNC NOAFFIRM'
DG_CONFIG=(syncxx,sync)';
Step_13- Put standby database in recovery mode@Standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step_14. swicth some arch file mannuly via ("Alter database swicth logilfe")

Step_15- Check if those file are moving are applying to standy db . @Standby database
 a -below step my help you to find standby side arch number -
 1- Archive log list
 2-SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

after 15- 20 mints compare arch file primary and standby side make sure those are same _
 b- On the primary database issue the following to find out recent archive number .
SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
 or -
 Archive log list

Differential INCREMENTAL and CUMULATIVE INCREMENTAL backup Command

Below command to take Rman different type of backup.

Differential INCREMENTAL level 0 BackuP

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 0 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}


Differential INCREMENTAL level 1 Backup

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
BACKUP INCREMENTAL LEVEL 1 DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

---CUMULATIVE LEVEL O BACKUP

run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
INCREMENTAL LEVEL 0 CUMULATIVE DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}

CUMULATIVE LEVEL 1 BACKUP
run
{
allocate channel d1 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d2 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d3 device type disk format '/shared/sync/8aug_backup/%U.bkp';
allocate channel d4 device type disk format '/shared/sync/8aug_backup/%U.bkp';
INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}