EXPDP/IMPDP fails with ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037 in RAC

While doing import using impdp i am encountering the below issue.

Error Details:
*************
ORA-31693: Table data object "sivaram"."TITAN":"SYS_P20689" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "/u01/refresh/expdp_TABLES_First_Set_03.dmp" for read
ORA-19505: failed to identify file "/u01/refresh/expdp_TABLES_First_Set_03.dmp"
ORA-27037: unable to obtain file status

But i have checked the file status in  server level it is present. But impmort unble to identify the file status. Also checked the permissions on directory, it looks fine.

[oracle@prod02 refresh]$ ls -lhrt /u01/refresh/expdp_TABLES_First_Set_03.dmp
-rw-r----- 1 oracle dba 5.6G Apr 15 20:42 /u01/refresh/expdp_TABLES_First_Set_03.dmp

Parfile for Import:
*******************
$ more impdp_TABLES.par
directory=refresh
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

The problem is, impdp process is looking for the dump file on the other node in the cluster, eventhough i started from the local node only due to i mentioned parallel in the par file. Prior to 11.2, once you establish the connection to the database, an instance selected and the master process and all the worker process run on that instance only.
From 11.2, CLUSTER parameter introduced

CLUSTER=Y|N (Default: Y)
Purpose: Whether Datapump can use RAC resources and start workers on other instances in cluster.

Solution:
*********
1. Add the CLUSTER=N in parfile, so all the worker process will start in local instance only
2. Remove the parallel option in parfile

I have followed here 1st Solution. After changes done to the parfile import completed successfully.

Parfile After Changes:
***********************
$ more impdp_TABLES.par
directory=refresh
CLUSTER=N
dumpfile=expdp_TABLES_First_Set_%U.dmp
logfile=impdp_TABLES.log
parallel=6
tables=sivaram.TITAN

Reference: MOS 1173765.1

Happy Learning :)

SQLT Installation and Report Generation

SQLT Installation:
*******************
1. Download the sqlt zip file from MOS: 215187.1
2. Copy to the server using scp/winscp
3. Unzip the sqlt zip file to the $ORACLE_HOME/bin directory
$ cp /home/oracle/sqlt_latest.zip /u01/app/oracle/product/12.1.0.2/db_1/bin/
$ unzip sqlt_latest.zip
Archive:  sqlt_latest.zip

4. After unzip completed it will creates the sqlt directory. Go to the sqlt directory
$ cd sqlt/
$ ls -lhrt
total 116K
drwxr-xr-x 3 oracle oinstall 4.0K Nov 13 12:21 input
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 install
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 run
drwxr-xr-x 7 oracle oinstall 4.0K Nov 20 16:57 utl
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 doc
-rw-r--r-- 1 oracle oinstall  56K Dec  6 19:26 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall  38K Dec  6 19:27 sqlt_instructions.txt

5. Connect to the database as sysdba and run the sqcreate.sql
$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> start install/sqcreate.sql
It will prompt for the below options just give the respective values

Optional Connect Identifier (ie: @PROD): just type enter don't give any value in case of local server

Password for user SQLTXPLAIN: Proivde the new password for the SQLTXPLAIN user that must satisfy the password verify function otherwise you installation will fail
Re-enter password: Proivde the same above password

Default tablespace [UNKNOWN]: USERS  Provide the tablespace on which you want to install the SQLTXPLAIN user
Temporary tablespace [UNKNOWN]: TEMP Provide the temp tablespace
Main application user of SQLT: SYS
Oracle Pack license [T]: T

Once everything is completed then it will creates the below 2 users. And provide the necessary grants.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where trunc(CREATED)=trunc(sysdate);
USERNAME                                   ACCOUNT_STATUS
------------------------------------------ --------------------------------
SQLTXADMIN                                 EXPIRED & LOCKED
SQLTXPLAIN                                 OPEN

SQL> alter user SQLTXADMIN identified by SQLTXADMIN account unlock;

User altered.

SQL> grant create connect,resource,SQLT_USER_ROLE to SQLTXPLAIN,SQLTXADMIN;

Grant succeeded.
Note: All SQLT users must be granted the SQLT_USER_ROLE before they can use any of the main methods. If It's not possible to use SYS as SQLT user, SQLTXADMIN needs to be granded INHERIT PRIVILEGES on SYS.

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;

Grant succeeded.

SQLT report Generation for the specific sql_id:
***********************************************
1. Identify the which sql_id you want to generate the sqlt report
2.
SQL> ho pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin/sqlt
SQL> conn SQLTXPLAIN/SQLTXPLAIN
Connected.
SQL> start run/sqltxtract.sql

The script will prompt for the sql_id and SQLTXPLAIN password please provide the same.
............
............
SQLTXTRACT completed.
SQL>exit
The script will generate the zip with the sql_id.
$ ls -lhrt|tail
-rw-r--r-- 1 oracle oinstall  29M Apr 22 03:59 sqlt_s62619_xtract_5xagduq334p7t.zip

Happy Learning :)