Get Oracle and Server Information

===========================
General
===========================
Get Oracle and OS information

===========================
Oracle
===========================

-- Instance Name
SELECT instance_name FROM V$INSTANCE;

-- Instance Number
SELECT instance_number FROM V$INSTANCE;

-- Oracle Version
SELECT version from V$INSTANCE;

-- Oracle Edition
SELECT banner FROM V$VERSION;

--Total Size of Datafiles
SELECT ROUND(SUM(maxbytes) /1024/1024/1024) AS GB 
  FROM DBA_DATA_FILES;

--Total Size of used space
SELECT ROUND(SUM(user_bytes) /1024/1024/1024) AS GB 
  FROM DBA_DATA_FILES;

--Memory settings
COL name FOR A30

COL value FOR A30

SELECT name, value FROM V$PARAMETER WHERE name like '%memo%';

--SGA Size
SELECT name, value FROM V$PARAMETER WHERE name = 'sga_max_size';

-- Location of oracle alert.log
SELECT name, value FROM V$PARAMETER 
 WHERE name like 'background_dump_dest'; 

-- Location of archive files
SELECT name, value FROM V$PARAMETER 
 WHERE name like 'log_archive_dest_1'; 

--ARCHIVEMODE/NONARCHIVEMOD
SELECT log_mode from V$DATABASE;

--To get Host OS Details from DB
SELECT DBMS_UTILITY.port_string from DUAL; 
x86_64/Linux 2.4

===========================
OS Server
===========================

--To get Host OS Details from Linux

less /etc/redhat-release
Red Hat Enterprise Linux Server release 5.10 (Tikanga)

less /proc/version 
Linux version 2.6.18-371.3.1.el5 (mockbuild@x86-028.build.eng.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-54)) #1 SMP Mon Nov 11 03:23:58 EST 2013

uname  -r
2.6.18-371.3.1.el5

name -a
Linux idn-pte-2-aps-1 2.6.18-371.3.1.el5 #1 SMP Mon Nov 11 03:23:58 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

--To get CPU info
-- processor model
less /proc/cpuinfo | grep vendor | uniq
GenuineIntel

--Number of processors
less /proc/cpuinfo | grep processor | wc -l
16

--Number of cores
less /proc/cpuinfo | grep "cpu core"
4*16

SQL Server code example: Block UPDATEs to a table using INSTEAD TRIGGER

=========================
General
=========================
Block all updates to a table via INSTEAD OF Triggers.

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG01 ON user.SitesServiceManager
INSTEAD OF UPDATE AS
BEGIN
  RETURN
END

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG02 ON user.SitesServiceManager
INSTEAD OF DELETE AS
BEGIN
  RETURN
END

GO

use my_database
GO
CREATE TRIGGER SitesServiceManager_TG03 ON user.SitesServiceManager
INSTEAD OF INSERT AS
BEGIN
  RETURN
END

GO


Procedures to Enable/Disable triggers:
CREATE PROCEDURE [dbo].[block_updates_on_SitesServiceManager]  AS
BEGIN
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG01 
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG02
  ALTER TABLE user.SitesServiceManager ENABLE TRIGGER SitesServiceManager_TG03 
END

CREATE PROCEDURE [dbo].[allow_updates_on_SitesServiceManager]  AS

BEGIN

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG01 

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG02

  ALTER TABLE user.SitesServiceManager DISABLE TRIGGER SitesServiceManager_TG03

END

Execute procedures to Enable/Disable triggers:

[my_database].[dbo].[allow_updates_on_SitesServiceManager] 

[my_database].[dbo].[block_updates_on_SitesServiceManager] 

Troubleshoot operation timed out errors.

Below is based upon excellent comment from Ask Tom
Database connection closed on port

========================
General
========================
Application recieve time out errors.
When checking Oracle alert.log, there are following messages
Step 1. - check alert.log

less alert.log

***********************************************************************
Archived Log entry 160083 added for thread 1 sequence 160094 ID 0x36162634 dest 1:
Wed Sep 06 05:12:16 2017
***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.1.0.7.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
  Time: 06-SEP-2017 05:12:16
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=111.222.555.200)(PORT=43680))
Wed Sep 06 05:32:39 2017
Thread 1 advanced to log sequence 160096 (LGWR switch)

Step 2. - get listener log details
Search for server 111.222.555.200 in listener logs
 
oracle@my_server:/software/oracle/diag/rdbms/igt/igt/trace>% lsnrctl status LSNR_IGT

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 06-SEP-2017 06:34:43

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora_server)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     lsnr_igt
Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date                18-JUL-2016 10:52:34
Uptime                    414 days 19 hr. 42 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /software/oracle/111/network/admin/listener.ora
Listener Log File         /software/oracle/diag/tnslsnr/my_server/lsnr_igt/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=100.000.555.125)(PORT=1521)))
Services Summary...
Service "igt" has 1 instance(s).
  Instance "igt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


 
Step 3. - in listener log, look to  IP 10.128.156.200:
less /software/oracle/diag/tnslsnr/my_server/lsnr_igt/alert/log.xml


<msg time='2017-09-06T04:16:39.191+00:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='my_server'
 host_addr='10.0.156.126'>
 <txt>06-SEP-2017 04:16:39 * (CONNECT_DATA=(SID=igt)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=iu))) * (ADDRESS=(PROTOCOL=tcp)(HOST=111.222.555.200)(PORT=45413)) * establish * igt * 0
 </txt>
</msg>

========================
Additional info, per "Ask Tom" post:
========================
Basically it means that there is a network issue between client and the server. 
It could be client itself, or a firewall timeout, or a timeout on some network hardware between the two devices. 

These time out related messages are mostly informational in nature. 
The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  
The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:

Oracle Clean up. Delete old Oracle trace files. Delete old dmp files.

=========================
General
=========================

Oracle creates trc files that are not being cleaned up from server.
Here is an example of delete unwanted files, using crontab task.

=========================
Evidences
=========================

crontab

1 6 * * * /software/oracle/oracle/scripts/scripts/delete_trace_files.sh

/software/oracle/oracle/scripts/delete_trace_files.sh

#!/bin/bash

ORACLE_SID=igt

#===============
#Listener Logs
#===============
SERVER_NAME=`hostname`
LISTENER_NAME=lsnr_igt
LISTENER_HOME=/software/oracle/diag/tnslsnr
LISTENER_PATH=${LISTENER_HOME}/${SERVER_NAME}/${LISTENER_NAME}/trace

mv ${LISTENER_PATH}/${LISTENER_NAME}.log_9  ${LISTENER_PATH}/${LISTENER_NAME}.log_10
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_8  ${LISTENER_PATH}/${LISTENER_NAME}.log_9
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_7  ${LISTENER_PATH}/${LISTENER_NAME}.log_8
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_6  ${LISTENER_PATH}/${LISTENER_NAME}.log_7
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_5  ${LISTENER_PATH}/${LISTENER_NAME}.log_6
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_4  ${LISTENER_PATH}/${LISTENER_NAME}.log_5
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_3  ${LISTENER_PATH}/${LISTENER_NAME}.log_4
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_2  ${LISTENER_PATH}/${LISTENER_NAME}.log_3
mv ${LISTENER_PATH}/${LISTENER_NAME}.log_1  ${LISTENER_PATH}/${LISTENER_NAME}.log_2
mv ${LISTENER_PATH}/${LISTENER_NAME}.log  ${LISTENER_PATH}/${LISTENER_NAME}.log_1

DAYS_TO_KEEP=14

LISTENER_PATH=${LISTENER_HOME}/${SERVER_NAME}/${LISTENER_NAME}/alert/

find ${LISTENER_PATH} -type f -name "log*.xml" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

#=======================
#delete old trace files
#=======================
LOG_DIR=/software/oracle/oracle/scripts/logs
if [[ ! -d $LOG_DIR ]]; then
  mkdir $LOG_DIR
fi

LOG_FILE=${LOG_DIR}/delete_trace.log
touch $LOG_FILE

ORA_INST=$ORACLE_SID
DAYS_TO_KEEP=10
RUN_DATE=`date +"%Y%m%d_%H%M%S"`
ORA_TRACE_HOME=/software/oracle/diag/rdbms
TRACE_PATH=${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace


echo "========================================" >> $LOG_FILE
echo "Start Deleting Files at $RUN_DATE       " >> $LOG_FILE
echo "========================================" >> $LOG_FILE


echo Starting Delete trc files older than $DAYS_TO_KEEP days: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
echo " " >> $LOG_FILE

echo Starting Delete trm files older than $DAYS_TO_KEEP days: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trm" -mtime +${DAYS_TO_KEEP} -exec rm {} \;
echo " " >> $LOG_FILE


echo Starting Delete files bigger than 1Gb: >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" -size +1G -exec rm {} \;
echo " " >> $LOG_FILE

echo "Starting Delete files with text kcbzib: dump suspect buffer:"  >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "kcbzib: dump suspect buffer"  >> $LOG_FILE
find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "kcbzib: dump suspect buffer" | xargs rm -f
echo " " >> $LOG_FILE

echo "Starting Delete files with text kkpamMapRow under incident folder:" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/incident -type f -name "*.trc" | xargs grep -l "kkpamMapRow" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/incident -type f -name "*.trc" | xargs grep -l "kkpamMapRow" | xargs rm -f
echo " " >> $LOG_FILE

echo "Starting Delete cdump folders under trace folder:" >>  $LOG_FILE
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace -type d -name "cdmp*" >>  $LOG_FILE 
find ${ORA_TRACE_HOME}/${ORA_INST}/${ORA_INST}/trace -type d -name "cdmp*" | xargs rm -rf
echo " " >> $LOG_FILE

echo "========================================" >> $LOG_FILE
echo "Delete and Archive Files                " >> $LOG_FILE
echo "========================================" >> $LOG_FILE

SEARCH_STING="DUMP REDO"

TRACE_FILES_LIST="$(find ${TRACE_PATH} -type f -name "*.trc" | xargs grep -l "${SEARCH_STING}") " 

for f in $TRACE_FILES_LIST
do
  echo "handling trace file $f" >> $LOG_FILE
  NEW_TRACE_NAME=/software/oracle/oracle/scripts/`basename $f`
  head -1000 $f > ${NEW_TRACE_NAME}
  tail -1000 $f >> ${NEW_TRACE_NAME}
  rm -f $f
done


echo " " >> $LOG_FILE
RUN_DATE=`date +"%Y%m%d_%H%M%S"`

echo "========================================" >> $LOG_FILE
echo "Finished Deleting Files at $RUN_DATE    " >> $LOG_FILE
echo "========================================" >> $LOG_FILE

exit 0

as shdaemon

1 6 * * * /home/shdaemon/scripts/move_old_log_files.sh

/home/shdaemon/scripts/move_old_log_files.sh

#!/bin/bash

#=============================

#move logs to old_logs folder

#=============================

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

for f in ${FOR_BACKUP_HOME}/export_igt*log 

do

  mv $f ${LOG_HOME}/

done

#=====================

#delete old dmp files

#=====================

BACKUP_HOME=/backup/ora_exp

DAYS_TO_KEEP=4

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

=======================

delete_old_exp_files.sh

=======================

1 6 * * * /home/shdaemon/scripts/delete_old_exp_files.sh

/home/shdaemon/scripts/delete_old_exp_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

#=============================

#move logs to old_logs folder

#=============================

for f in ${FOR_BACKUP_HOME}/export_igt*log do

  mv -f ${FOR_BACKUP_HOME}/export_igt*log ${LOG_HOME}/

done

exit 0

#=====================

#delete old dmp files

#=====================

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

=====================

move_old_log_files.sh

=====================

1 6 * * * /home/shdaemon/scripts/move_old_log_files.sh

/home/shdaemon/scripts/move_old_log_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

====================

delete_old_exp_files.sh

====================

1 6 * * * /home/shdaemon/scripts/delete_old_exp_files.sh

/home/shdaemon/scripts/delete_old_exp_files.sh

#!/bin/bash

BACKUP_HOME=/backup/ora_exp

FOR_BACKUP_HOME=/backup/ora_exp/for_backup

LOG_HOME=/backup/ora_exp/for_backup/old_log

DAYS_TO_KEEP=4

#================================================

#delete old dmp files

#================================================

find ${BACKUP_HOME} -type f -name "export*.dmp" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

exit 0

====================

SQL Cleanup

====================

0 23 * * * /software/oracle/oracle/scripts/sql_cleanup.sh

sql_cleanup.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

WORK_DIR=/software/oracle/oracle/scripts

LOG_FILE=${WORK_DIR}/sql_cleanup.log

RUN_DATE=`date +"%Y%m%d_%H%M%S"`

echo "Logfile : $LOG_FILE"

echo "" >> ${LOG_FILE}

echo "Starting Truncate at $RUN_DATE" >> ${LOG_FILE}

cd /software/oracle/oracle/scripts

sqlplus system/XXXXXX@igt @sql_cleanup.sql | tee $LOGFILE

echo "Finished Truncate at $RUN_DATE" >> ${LOG_FILE}

sql_cleanup.sql

TRUNCATE TABLE MY_USER.MY_TABLE;

EXIT;

Many trace files with ORA-00600 and error message Action: 'kpuActionDefault'

===========================
General
===========================
There are many *.trc files and incident/incident_<pid> files generated.
When checking alert.log, there are many error messages with error ORA-00600:

===========================
Evidences
===========================

alert.log

Sun Aug 20 07:14:18 2017
ORA-00600: internal error code, arguments: [kkpamRGet], [PTI 8.1], [0], [], [], [], [], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_246165/igt_ora_2263_i246165.trc
Sun Aug 20 07:14:18 2017
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_5575.trc  (incident=246525):
ORA-00600: internal error code, arguments: [kkpamDGSPam2], [0], [0], [], [], [], [], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_246525/igt_ora_5575_i246525.trc

trace files

/software/oracle/diag/rdbms/igt/igt/trace/igt_ora_31903.trc

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      deu-vod-2-dbu-1

Release:        2.6.18-371.3.1.el5

Version:        #1 SMP Mon Nov 11 03:23:58 EST 2013

Machine:        x86_64

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 455

Unix process pid: 31903, image: oracle@deu-vod-2-dbu-1

*** 2017-08-20 12:59:18.420

*** SESSION ID:(2544.29033) 2017-08-20 12:59:18.420

*** CLIENT ID:() 2017-08-20 12:59:18.420

*** SERVICE NAME:(SYS$USERS) 2017-08-20 12:59:18.420

*** MODULE NAME:(JDBC Thin Client) 2017-08-20 12:59:18.420

*** ACTION NAME:() 2017-08-20 12:59:18.420

 

DDE: Problem Key 'ORA 600 [kkpamRGet]' was flood controlled (0x4) (incident: 246112)

ORA-00600: internal error code, arguments: [kkpamRGet], [PTI 8.1], [0], [], [], [], [], [], [], [], [], []

incident file /software/oracle/diag/rdbms/igt/igt/incident/incdir_243949/igt_ora_13764_i243949.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      deu-vod-2-dbu-1

Release:        2.6.18-371.3.1.el5

Version:        #1 SMP Mon Nov 11 03:23:58 EST 2013

Machine:        x86_64

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 185

Unix process pid: 13764, image: oracle@deu-vod-2-dbu-1

*** 2017-08-04 08:59:25.201

*** SESSION ID:(3259.3287) 2017-08-04 08:59:25.201

*** CLIENT ID:() 2017-08-04 08:59:25.201

*** SERVICE NAME:(SYS$USERS) 2017-08-04 08:59:25.201

*** MODULE NAME:(JDBC Thin Client) 2017-08-04 08:59:25.201

*** ACTION NAME:() 2017-08-04 08:59:25.201

 

Dump continued from file: /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_13764.trc

ORA-00600: internal error code, arguments: [kdtgsp-inc], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 243949 (ORA 600 [kdtgsp-inc]) ========

*** 2017-08-04 08:59:25.201

----- Current SQL Statement for this session (sql_id=ancznzs51sycm) -----

INSERT INTO GA_W_COUNTERS_HISTORY (STATIC_ID , DYN1 , DYN2 , DYN3 , DYN4 , DYN5, DYN_S1, COUNTER_SUM , COUNTER_DELTA, TS_LAST_MODIFIED, NODE_ID, DAY) values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 )

Begin OCI Call Context Dump

End OCI Call Context Dump

===========================

Oracle Metalink

===========================

Per Oracle document:

Bug 9399991 - Assorted Internal Errors and Dumps (mostly under kkpa*/kcb*) from SQL against partitioned tables (Doc ID 9399991.8):

Versions confirmed as being affected:
11.1.0.7
11.2.0.1
  
Description

Various dumps and / or ORA-600 errors can occur if a partitioned table has lots of dependent references in the shared pool.
eg: lots of different literal SQLs reference the same partitioned table.

The problem is that an internal field can overflow when the number of dependencies exceeds 65536 which can then allow the MVOB of the partitioned table tohave data freed leaving stale references in the SGA.

Rediscovery notes:
 If the problem is present the a library cache dump will show one or more MVOB entries which have a KGHP flag but no KEP flag. 

 A SQL like the following may help show the MVOB entries in the library cache: 
  select KGLHDADR, KGLNAHSH, KGLNAOWN, KGLFNOBJ, KGLHDNSP, KGLOBTYP, 
         Decode(bitand(KGLHDFLG,4),4,'YES','NO') "KPR",
         case when kglhdamk < 0 then (65536 + kglhdamk)
              else kglhdamk end "KDP"
   from x$kglob 
   where KGLOBTYP=70;

 In this select:
  "KPR" is the flag we are interested in. YES=flag set, NO=not set
  "KDP" is the dependency count. 
  You should see this increase if new SQL statements are parsed that reference the partitioned object. 
  If you see KDP>0 and KPR=NO then there is a problem in the SGA and it may be worth flushing.

 If you see KDP increasing above about 50000 for a specific entry then it may also be worth flushing the shared pool as the problem  could occur if the dependency count increases much further.

 Solution

 Per above: "If you see KDP>0 and KPR=NO then there is a problem in the SGA and it may be worth flushing."

===========================

In the specific case

===========================

Failing SQL was:

INSERT INTO GA_W_COUNTERS_HISTORY (STATIC_ID , DYN1 , DYN2 , DYN3 , DYN4 , DYN5, DYN_S1, COUNTER_SUM , COUNTER_DELTA, TS_LAST_MODIFIED, NODE_ID, DAY) 

values (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 )

GA_W_COUNTERS_HISTORY table is indeed partitioned.

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME 

  FROM DBA_TAB_PARTITIONS 

 WHERE table_name = 'GA_W_COUNTERS_HISTORY' 

   AND TABLE_OWNER = 'MY_USER'

TABLE_OWNER        TABLE_NAME                     PARTITION_NAME

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

MY_USER            GA_W_COUNTERS_HISTORY          P_1

MY_USER            GA_W_COUNTERS_HISTORY          P_2

MY_USER            GA_W_COUNTERS_HISTORY          P_3

MY_USER            GA_W_COUNTERS_HISTORY          P_4

MY_USER            GA_W_COUNTERS_HISTORY          P_5

MY_USER            GA_W_COUNTERS_HISTORY          P_6

MY_USER            GA_W_COUNTERS_HISTORY          P_7

MY_USER            GA_W_COUNTERS_HISTORY          P_8

MY_USER            GA_W_COUNTERS_HISTORY          P_9

MY_USER            GA_W_COUNTERS_HISTORY          P_10

MY_USER            GA_W_COUNTERS_HISTORY          P_11

MY_USER            GA_W_COUNTERS_HISTORY          P_12

MY_USER            GA_W_COUNTERS_HISTORY          P_13

MY_USER            GA_W_COUNTERS_HISTORY          P_14

MY_USER            GA_W_COUNTERS_HISTORY          P_15

MY_USER            GA_W_COUNTERS_HISTORY          P_16

MY_USER            GA_W_COUNTERS_HISTORY          P_17

MY_USER            GA_W_COUNTERS_HISTORY          P_18

MY_USER            GA_W_COUNTERS_HISTORY          P_19

MY_USER            GA_W_COUNTERS_HISTORY          P_20

MY_USER            GA_W_COUNTERS_HISTORY          P_21

MY_USER            GA_W_COUNTERS_HISTORY          P_22

MY_USER            GA_W_COUNTERS_HISTORY          P_23

MY_USER            GA_W_COUNTERS_HISTORY          P_24

MY_USER            GA_W_COUNTERS_HISTORY          P_25

MY_USER            GA_W_COUNTERS_HISTORY          P_26

MY_USER            GA_W_COUNTERS_HISTORY          P_27

MY_USER            GA_W_COUNTERS_HISTORY          P_28

MY_USER            GA_W_COUNTERS_HISTORY          P_29

MY_USER            GA_W_COUNTERS_HISTORY          P_30

MY_USER            GA_W_COUNTERS_HISTORY          P_31

When running the SQL, from the above technote:

 select KGLHDADR, KGLNAHSH, KGLNAOWN, KGLFNOBJ, KGLHDNSP, KGLOBTYP, 

         Decode(bitand(KGLHDFLG,4),4,'YES','NO') "KPR",

         case when kglhdamk < 0 then (65536 + kglhdamk)

              else kglhdamk end "KDP"

   from x$kglob 

   where KGLOBTYP=70;

KGLHDADR         KGLNAHSH   KGLNAOWN  KGLFNOBJ          KGLHDNSP KGLOBTYP KPR   KDP

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

000000011D531030 1057352192 MY_USER_C GA_W_COUNTERS_ORY 45       70       NO    2

0000000156411590 1057352192 MY_USER_C GA_W_COUNTERS_ORY 45       70       YES   0

000000011980EBC0 4229035152 MY_USER_F GA_W_COUNTERS_ORY 45       70       YES   2

000000015699DCD0 2014443530 MY_USER_J GA_W_COUNTERS_ORY 45       70       YES   1

...

...

260 rows selected.

"If you see KDP>0 and KPR=NO then there is a problem in the SGA and it may be worth flushing."

So indeed, there is a corruption in memory in shared pool.

After flushing shared pool, the issue was resolved

ALTER SYSTEM FLUSH SHARED_POOL;

expdp process generates trace file with error ORA-31673

=========================
General
=========================

Oracle expdp process completes successfully, but upon completion, <pid>_dw01*.trc trace file is generated.

=========================
Evidences
=========================
Trace file contains following text:

Trace file /software/oracle/diag/rdbms/igt/igt/trace/igt_dw01_32631.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name:    Linux
Node name:      aut-mob-5-aps-1
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine:        x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 458
Unix process pid: 32631, image: oracle@aut-mob-5-aps-1 (DW01)


*** 2017-08-20 02:01:09.514
*** SESSION ID:(268.30456) 2017-08-20 02:01:09.514
*** CLIENT ID:() 2017-08-20 02:01:09.514
*** SERVICE NAME:(SYS$USERS) 2017-08-20 02:01:09.514
*** MODULE NAME:(Data Pump Worker) 2017-08-20 02:01:09.514
*** ACTION NAME:(SYS_EXPORT_FULL_01) 2017-08-20 02:01:09.514

DM-DBMS_DM_MODEL_EXP:schema_callout: schema=SYSTEM, pre_schema=NOOP for 11g

*** 2017-08-20 02:01:09.514
DM-DBMS_DM_MODEL_EXP:schema_callout: schema=OUTLN, pre_schema=NOOP for 11g

*** 2017-08-20 02:01:09.514
DM-DBMS_DM_MODEL_EXP:schema_callout: schema=TSMSYS, pre_schema=NOOP for 11g

*** 2017-08-20 02:01:09.515
DM-DBMS_DM_MODEL_EXP:schema_callout: schema=SHDAEMON, pre_schema=NOOP for 11g



*** 2017-08-20 02:14:43.329
DM-DBMS_DM_MODEL_EXP:schema_callout: schema=HRV_VIPNE_OVMDQ, pre_schema=NOOP for
 11g

*** 2017-08-20 02:18:53.785
KUPP: Error 31673 detected in worker process DW01, worker id=1
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-31673: worker process interrupt for normal exit by master process
ORA-06512: at "SYS.KUPF$FILE_INT", line 972
ORA-06512: at "SYS.KUPF$FILE", line 7126
ORA-06512: at "SYS.KUPW$WORKER", line 4661
ORA-06512: at "SYS.KUPW$WORKER", line 8624
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "SYS.KUPW$WORKER", line 8342
ORA-06512: at "SYS.KUPW$WORKER", line 1545
ORA-06512: at line 2


=========================
Solution
=========================

This is a known issue, documented in Oracle technote:

 Data Pump Export Completed Successfully With Errors ORA-447 & ORA-31673 (Doc ID 1323792.1)

CAUSE
This is discussed in 
Bug 9962464 - ORA-447 AND ORA-31673 OCCURS IN THE WORKER PROCESS WHEN EXPDP IS EXECUTED.
which has been closed with status 33 - Suspended, Req'd Info not Avail

SOLUTION
Ignore the error as the export has already completed and so this is just an unnecessary trace file being generated on exit of the Data Pump Worker process and so has no impact on the database or export. 

In case you would run into the same issue, you could provide a working test case to restart to bug investigation.

=========================
Workaround
=========================

To avoid trace file occupied space, delete *.trc files with below crontab task


delete_expdp_trace_files.sh
#!/bin/bash

HOME_DIR=/software/oracle/diag/rdbms/igt/igt/trace
WORK_DIR=`pwd`
cd ${HOME_DIR}
ls -lt ${HOME_DIR} | grep dw01 | grep trc | awk '{print $9}' | xargs grep -l 00447 | xargs rm -f
cd $WORK_DIR

crontab -l

0 6 * * * /software/oracle/oracle/scripts/delete_expdp_trace_files.sh


=========================
A Similar Issue:

=========================
1956899.1
Lots of Trace Files Reporting "Kcbzib: Dump Suspect Buffer, Err2=1410" 

As a temporary workaround, you can simply delete the files as these should not have been generated for the non-user-visible errors in the first place.