Stats job fail with errors ORA-20011,ORA-29913, KUP-11024

Error Details from Alert log:

Mon Sep 04 22:00:11 2017

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.

Errors in file /u01/app/oracle/diag/rdbms/hbar/hbar2/trace/hbar2_j002_1614.trc:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

Mon Sep 04 22:01:20 2017

Cause:

1. Issue with external tables created during data pump job

2. Data pump job might running same time stats are gathering or external tables have not been cleaned up properly. 

Solution:  

1. Check if any data pump jobs are running , external tables will drop once job completes, Skip stats gather jobs during data pump running  

2. List external tables which are not cleaned up after data pump job and purge them

SQL> select OWNER_NAME,JOB_NAME,OPERATION,STATE from dba_datapump_jobs;

OWNER_NAME  JOB_NAME             OPERATION    STATE       

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

SYS        SYS_IMPORT_TABLE_01   IMPORT      EXECUTING   -- Datapump job running

SQL> select owner,object_name,object_type, status,

   to_char(created,'dd-mon-yyyy hh24:mi:ss') created ,

   to_char(last_ddl_time , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time

   from  dba_objects  where object_name like 'ET$%';

OWNER              OBJECT_NAME           OBJECT_TYPE          STATUS  CREATED                       LAST_DDL_TIME

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

SYSTEM             ET$13A3DFAD0001         TABLE                VALID   14-mar-2015 15:00:59          14-mar-2015 15:00:59

SYS                ET$00FE052F0002         TABLE                VALID   04-sep-2017 22:35:29          04-sep-2017 22:35:29 --- Table related to running job

3.  Purge external table which was not cleaned up properly

drop table SYSTEM.ET$13A3DFAD0001  purge;

PDB Auto start with CDB in 12c

[oracle@jdedb01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 9 20:27:25 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 8053063680 bytes

Fixed Size                  3729840 bytes

Variable Size            1879049808 bytes

Database Buffers         6157238272 bytes

Redo Buffers               13045760 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

JDEORCL                        MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

JDEORCL                        READ WRITE

SQL> alter pluggable database JDEORCL save state;

Pluggable database altered.

SQL> select CON_ID,CON_NAME,INSTANCE_NAME,STATE from dba_pdb_saved_states;

    CON_ID CON_NAME   INSTANCE_NAME      STATE

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

         3 JDEORCL    ORA12C             OPEN

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 8053063680 bytes

Fixed Size                  3729840 bytes

Variable Size            1879049808 bytes

Database Buffers         6157238272 bytes

Redo Buffers               13045760 bytes

Database mounted.

Database opened.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

JDEORCL                        READ WRITE

Note:

Set restart of specific database

altar pluggable database pdb_name save state;

Restart of all PDB’s :

altar pluggable database all save state;

we use this to keep the state of all but a few with the except clause:

altar pluggable database all except pdb_name1, pdb_name2 save state;

Bug 18371441 - RMAN-11003 / .. / ORA-19755 from RMAN recover of file with missing BCT file

Rman duplicate failed with below error

Errors with log +FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265

Recovery interrupted!

Recovered data files to a consistent state at change 97471797575

Media Recovery failed with error 19755

Errors in file /oracle/product/diag/rdbms/JAYTST/JAYTST/trace/JAYTST_pr00_7863.trc:

ORA-00283: recovery session canceled due to errors

ORA-19755: could not open change tracking file

ORA-19750: change tracking file: '+DATA/hr91prod/changetracking/rman_change_track.f'

ORA-17503: ksfdopn:2 Failed to open file +DATA/hr91prod/changetracking/rman_change_track.f

ORA-15173: entry 'JAYPROD' does not exist in directory '/'

ORA-10877 signalled during: alter database recover logfile '+FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265'...

Fri Jul 14 19:13:58 2017

Check any data files need recovery

select status, to_char(resetlogs_change#), resetlogs_time,   to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,

count(*) from v$datafile_header group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time

 order by status, checkpoint_change#, checkpoint_time ;

STATUS  TO_CHAR(RESETLOGS_CHANGE#)  RESETLOGS TO_CHAR(CHECKPOINT_CHANGE#)  CHECKPOINT_TIME        COUNT(*)

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

ONLINE  1                           17-MAY-12 97471797575                   13-JUL-2017 09:44:46      100 - different (scn) not consistent

ONLINE  1                           17-MAY-12 97495746853                   13-JUL-2017 17:59:21       41

Note: -- Datafile(s) are at different checkpoint_change# (scn), so not consistent and need recovery

  Recovery failed due to BCT not available at target side

  Copy bct file from source to target

· At the source, check the location of BCT file

SELECT filename bct_file, status, bytes FROM   v$block_change_tracking;

BCT_FILE                                                            STATUS       BYTES

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

+DATA/JAYPROD/changetracking/rman_change_track.f                     ENABLED    147915264

1 row selected.

· Copy the BCT file from ASM to a file system

ASMCMD> cp +DATA/JAYPROD/changetracking/rman_change_track.f /tmp/.

copying +DATA/JAYPROD/changetracking/rman_change_track.f -> /tmp/./rman_change_track.f

ASMCMD>

· Transfer the file to the auxiliary site using scp, ftp etc

· Auxiliary site, copy the file from the file system back into ASM:

ASMCMD> cd +DATA/JAYPROD/changetracking

ASMCMD> ls

ASMCMD> cp /tmp/rman_change_track.f .

copying /tmp/rman_change_track.f -> +DATA/JAYPROD/changetracking/rman_change_track.f

  Recover until data files are consistent

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 97471797575 generated at 07/13/2017 09:44:46 needed for thread 2

ORA-00289: suggestion : +FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265

ORA-00280: change 97471797575 for thread 2 is in sequence #201199

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

+FRA/JAYTST/archivelog/2017_07_14/thread_2_seq_201199.2127.949341265

ORA-00279: change 97471797575 generated at 07/13/2017 09:28:24 needed for thread 1

ORA-00289: suggestion : +FRA/JAYTST/archivelog/2017_07_14/thread_1_seq_200803.2075.949341807

ORA-00280: change 97471797575 for thread 1 is in sequence #200803

Check datafile check point status:

STATUS  TO_CHAR(RESETLOGS_CHANGE#)               RESETLOGS TO_CHAR(CHECKPOINT_CHANGE#)              CHECKPOINT_TIME        COUNT(*)

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

ONLINE  1                                        17-MAY-12 97495852724                              13-JUL-2017 18:26:31        141

   All data files are in consistent , Open database with reset logs

(control file type is in backup , recreate it to make as current  to allow open with reset logs )

SQL> create pfile='/tmp/tmp1.ora' from spfile;

File created.

Edit DB name as JAYPROD.

SQL> shutdown immediate;

ORA-01507: database not mounted

SQL> startup mount pfile=/tmp/tmp1.ora;

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database backup controlfile to trace as '/tmp/tmp2.sql'; 

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Re-control file (second case remove all the rest of the character tmp2.sql)

CREATE CONTROLFILE REUSE DATABASE "JAYPROD" RESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 4144

oracle> sqlplus "/as sysdba"  

SQL> startup nomount pfile=/tmp/tmp1.ora

ORACLE instance started.

Total System Global Area 2138521600 bytes

Fixed Size                  2161024 bytes

Variable Size             905971328 bytes

Database Buffers         1207959552 bytes

Redo Buffers               22429696 bytes

SQL> @tmp2.sql

SQL> alter database open resetlogs;

  Present database name is JAYPROD need to rename to target as JAYTST

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO JAYTST;

Database altered.

SQL> shutdown immediate ;

SQL>startup  mount pfile=/tmp/tmp1.ora

nid TARGET=SYS DBNAME=JAYTST   (Change DB name as per requirement )

 (Sys password  manager)

  Before restart  and resetlogs  rename redolog  ****

Edit db name JAYTST in tmp1.ora

SQL> startup  mount pfile=/tmp/tmp1.ora

  Rename redologos   

SQL> select 'Alter database rename file ''' || member || ''' to '''||member||' '||''';' from v$logfile where member like '%+DATA%';

SQL> select 'Alter database rename file ''' || member || ''' to '''||member||' '||''';' from v$logfile where member like '%+FRA%';

SQL> alter database open resetlogs;

  Add temporary space

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/' REUSE;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/' REUSE;