Category: DataPump

DataPump “ORA-31623: a job is not attached to this session via the specified handle”

SYMPTOM

The below errors occurred while export tables by using datapump:

$expdp userid=... tables=... directory=... dumpfile=...
..

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3905
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5203
ORA-06512: at line 1

CAUSES

Stream pool size is too small, and there is no more memory for stream pool to increase.

SOLUTION

Increase streams_pool_size:

SQL> show parameter streams_pool_size

NAME              TYPE        VALUE
----------------- ----------- ---------
streams_pool_size big integer 20M

SQL> alter system set streams_pool_size=128M ;

If not enough memory available:

SQL>  alter system flush SHARED_POOL;
System altered.

-- OR 

SQL> alter system flush BUFFER_CACHE;
System altered.

SQL> alter system set streams_pool_size=128M ;

For AMM or ASMM, after data pump is complete, reset the parameter to leave system to manage this parameter again.

SQL> alter system reset streams_pool_size;

“ORA-16644: apply instance not available” While DGMGRL Switchover

For some reason, DG broker does not recognise the applying instance of a standby database.

DGMGRL> switchover to STDBYDB;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STDBYDB2" on database
 "STDBYDB"
Connecting to instance "STDBYDB2"...
Connected as SYSDBA.
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still "PRIMDB"
DGMGRL>

DIAGNOSIS

a) Check and find one of the instance STDBYDB1 is the recovery instance. Stop the standby recovery process, and then start it again.  Data Guard still cannot find it.

b) Stop whole standby database, and restart it again. Check standby applying is undergoing. Data Guard still cannot find it.

RESOLUTION

DGMGRL> edit database STDBYDB set property 'PreferredApplyInstance'
='STDBYDB1';
Property "PreferredApplyInstance" updated

$srvctl stop database -d STDBYDB;
$srvctl start database -d STDBYDB;

DGMGRL> edit database STDBYDB set property 'PreferredApplyInstance'='';
Property "PreferredApplyInstance" updated

How to Monitor DataPump

A couple of ways to monitor DataPump job

Get DataPump job details from database

SQL>select JOB_NAME,OPERATION,JOB_MODE,DEGREE,
           ATTACHED_SESSIONS,STATE 
     from dba_datapump_jobs

JOB_NAME             OPERATION JOB_MODE DEGREE ATTAC..TIONS STATE
-------------------- --------- -------- ------ ------------ ------
SYS_EXPORT_SCHEMA_01 EXPORT    SCHEMA   4       1          EXECUTING

Find job name from the DataPump log file

...
..
.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": directory= ..... PARALLEL=4

Query V$SESSION_LONGOPS

SQL> select inst_id, username,target_desc,sofar,
            totalwork,TIME_REMAINING 
      from gV$SESSION_LONGOPS 
     where TIME_REMAINING>1000;

INST_ID USERNAME TARGET_DESC SOFAR   TOTALWORK   TIME_REMAINING
------- -------- ----------- ------- ----------- --------------
 4      SYS                  248779  4294967296  28121732
 3      SYS                  467021  4294967296  15108237
 3      SYS      EXPORT      8824    21597       2335
 2      SYS                  313184  4294967296  22351992

Attach to the running DataPump job

$ expdp ATTACH=SYS_EXPORT_SCHEMA_01

Export> help
...
..
.
 ------------------------------------------------------------------------------
HELP
 Summarize interactive commands.

KILL_JOB
 Detach and delete job.

PARALLEL
 Change the number of active workers for current job.

REUSE_DUMPFILES
 Overwrite destination dump file if it exists [NO].

START_JOB
 Start or resume current job.
 Valid keyword values are: SKIP_CURRENT.

STATUS
 Frequency (secs) job status is to be monitored where
 the default [0] will show new status when available.

STOP_JOB
 Orderly shutdown of job execution and exits the client.
 Valid keyword values are: IMMEDIATE.

$status
...
..
.

Kill the running DataPump job

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

In Unicode AL32UTF8, special characters, invalid characters might cause errors like “ORA-39346: data loss in character set conversion”.

When importing a schema exported from one 12cR1 AL32UTF8 database to another database  with same AL32UTF8 characterset, this ORA -error was generated :

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

The error message clearly shows the problem data is from “SCHEMA_EXPORT/TABLE/COMMENT”.

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects, we can see the problem data is from SYS.COM$.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects again, we can see all green including SYS.COM$.

Do another export and import again, everything works fine.

PLS-00201: identifier ‘SYS.DBMS_DATAPUMP’ must be declared

DataPump impdp or expdp requires a PDB open mode.

SYMPOTMS

When trying to run datapump impdp into a PDB, the following errors occurred:

UDI-06550: operation generated ORACLE error 6550
ORA-06550: line 1, column 100:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 100:
PL/SQL: Statement ignored
ORA-06550: line 1, column 202:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 202:
PL/SQL: Statement ignored

CAUSES

The PDB database is not open.

SQL> show con_name

CON_NAME
------------------------------
pdb1

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

RESOLUTION

Open the PDB database.

SQL> show con_name

CON_NAME
------------------------------
pdb1

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE