Expdp fails with ORA-31626, ORA-31633, ORA-00955

Full database backup using expdp was failing with following error.
I have checked with the following command.

expdp userid=\'/ as sysdba\' directory=bk_dir full=y job_name=dfull_backup dumpfile=test.dmp logfile=testlog

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.DFULL_BACKUP"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-00955: name is already used by an existing object


Issue:
select * from DBA_DATAPUMP_JOBS;
showing there have a job name DFULL_BACKUP which status is not running.
select table_name from dba_tables where table_name='DFULL_BACKUP';
shows the master table created under SYS schema.

Resolution:

Connect as sysdba.

SYS@shamim AS SYSDBA>drop table DFULL_BACKUP;

Table dropped.

Now we can run the expdp again without error.

Online table reorg

DBMS_REDEFINITION package is a very helpful tool to achieve high availability at database level. I always use it to change table properties for my 24/7 databases without downtime. Here is a example of move tablespace of partitioned table with indexes.

AS SYSDBA

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ODITIO','ABC_HASH');

SQL> grant execute on dbms_redefinition to ODITIO;

Create interim table and indexes

My original table structure was like bellow:

CREATE TABLE ODITIO.ABC_HASH

(id1 number,

id2 number)

PARTITION BY HASH (id1)

( PARTITION p1

TABLESPACE T1,

PARTITION p2

TABLESPACE T2);

CREATE UNIQUE INDEX ODITIO.ABC_HASH_PK

ON ODITIO.ABC_HASH(ID1)

LOCAL (

PARTITION P1

TABLESPACE I1,

PARTITION P2

TABLESPACE I2

);

I wanted to move the table and index to T3 T4 and I3,I4 tablespace.So I Connected as ODITIO user and created interim table and index first with new tablespace.

SQL> set time on

SQL> set timi on

SQL> CREATE TABLE CREATE TABLE ODITIO.ABC_HASH_INTERIME

(id1 number,

id2 number)

PARTITION BY HASH (id1)

( PARTITION p1

TABLESPACE T3,

PARTITION p2

TABLESPACE t4);

SQL> CREATE UNIQUE INDEX ODITIO.ABC_HASH_PK_INTERIME

ON ODITIO.ABC_HASH(ID1)

LOCAL (

PARTITION P1

TABLESPACE I3,

PARTITION P2

TABLESPACE I4);

Start the process

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE('ODITIO','ABC_HASH','ABC_HASH_INTERIM',NULL);

END;

Registering dependency

SQL> Begin

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(

uname=>'ODITIO',

orig_table=>'ABC_HASH',

int_table=>'ABC_HASH_INTERIM',

dep_type=>dbms_redefinition.cons_index,

dep_owner=>'ODITIO',

dep_orig_name=>'ABC_HASH_PK',

dep_int_name=>'ABC_HASH_PK_INTERIM'

);

End;

Copy dependency

SQL> DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

('ODITIO','ABC_HASH','ABC_HASH_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);

END;

Sync changes

SQL> BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ODITIO', 'ABC_HASH','ABC_HASH_INTERIM');

END;

Finish step of the process

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE('ODITIO', 'ABC_HASH','ABC_HASH_INTERIM');

END;

If something goes wrong then we can rollback it like bellow.

SQL> Begin

DBMS_REDEFINITION.ABORT_REDEF_TABLE('ODITIO','ABC_HASH','ABC_HASH_INTERIM');

End;

Find the interim constraints

SQL> select 'alter table tmx.'|| table_name ||' drop constraint ' || constraint_name ||'; ' owner,constraint_name,status, constraint_type,table_name,r_owner,r_constraint_name

from all_constraints

where constraint_type='R'

and r_constraint_name in (select constraint_name from all_constraints

where constraint_type in ('P','U') and table_name like 'ABC_HASH_IN%');

Drop interim table

SQL> drop table ABC_HASH_INTERIM;

ORA-10388: parallel query server interrupt (failure)

ORA-10388: parallel query server interrupt (failure) is a error that may occur with parallel query server. But we have got this error one day when SMON was trying to do parallel recovery but failed. We face a instance crash for this error. We figure out this from trace file output which is like bellow.

*** 2008-10-24 08:56:26.057
*** SERVICE NAME:(SYS$BACKGROUND) 2008-10-24 08:56:26.054
*** SESSION ID:(1094.336) 2008-10-24 08:56:26.054
Parallel Transaction recovery server caught exception 10388

Then I used following query to find out all the parent and related child processes running and found all the child processes SMON spawns at that time. This sql is very helpful to find parallel query process and their slaves.


select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
gv$px_session px,
gv$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc ;

So take SID from the output of the query and find out from gv$active_session_history what is the object name that child processes tryig to access. We found the object name from CURRENT_OBJ# column of gv$active_session_history which was a recylebin object.

This is wired that SMON trying to to access a recylebin object. So we decided to purge the object from recycle bin. After doing that SMON finally stop to do recover and failing with error.