Datapump ORA-31626 ORA-31638 ORA-31632 ORA-00942

Datapump expdp finished, but generated errors :

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user TETSUSER
ORA-31632: master table "TESTUSER.SYS_EXPORT_SCHEMA_01" not found, 
           invalid, or inaccessible
ORA-00942: table or view does not exist
SQL> SELECT owner_name, job_name, operation, job_mode,
            state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE STATE      ATTACHED_SESSIONS
---------- -------------------- ---------- -------- ---------- -----
TESTUSER      SYS_EXPORT_SCHEMA_01  EXPORT    SCHEMA   COMPLETING 0

Subscribe to get access

Read more of this content when you subscribe today.

Asmcmd Pwcopy With PRCD-1163 PRCR-1071 CRS-0245 ASMCMD-9453

After standby database rebuilt, then trying to copy database password file from primary to standby ASM disk, the below errors occurred:

ASMCMD> pwcopy --dbuniquename RACTEST '/tmp/orapwractest' '+DATA/RACTEST/orapwractest'
copying /tmp/orapwractest -> +DATA/RACTEST/orapwractest
PRCD-1163 : Failed to modify database RACTEST
PRCR-1071 : Failed to register or update resource ora.ractest.db
CRS-0245: User doesn't have enough privilege to perform the operation
ASMCMD-9453: failed to register password file as a CRS resource

The password file was copied to ASM diskgroup ‘+DATA’ successfully, but the configuring  of database failed .

WORKAROUND

Rerun the same command without “–dbuniquename RACTEST” option.

ASMCMD> pwcopy /tmp/orapwractest +DATA/RACTEST/orapwractest
copying /tmp/orapwractest -> +DATA/RACTEST/orapwractest

Then use ‘srvctl’ to re-configure database as user ‘oracle’ if needed:

$srvctl modify database -d RACTEST -pwfile '+DATA/RACTEST/orapwractest';

$srvctl config database -d RACTEST

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