Use TRACE=1FF0300 to trace the expdp/impdp backup
expdp system/manager DIRECTORY=new_dir DUMPFILE=expdp_parallel_%U.dmp LOGFILE=expdp_full.log FULL=y PARALLEL=15 TRACE=1FF0300
Dump would be like :
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31694: master table "SYSTEM"."SYS_IMPORT_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
LPX-00210: expected '<' instead of 'ΓΏ'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8936
----- PL/SQL Call Stack -----
object line object
handle number name
0xfb472c90 20462 package body SYS.KUPW$WORKER
0xfb472c90 9028 package body SYS.KUPW$WORKER
0xfb472c90 4519 package body SYS.KUPW$WORKER
0xfb472c90 9739 package body SYS.KUPW$WORKER
0xfb472c90 1775 package body SYS.KUPW$WORKER
0xfa49efb8 2 anonymous block
1FF0300 is used for full trace(worker processes & master control processes).
400300 is to trace Worker process(es) and trace 80300 is to trace Master Control Process (MCP).
If it is Master Process trace file then generated file name is,
SID_dmNUMBER_process_id.trc
- If it is Worker Process trace file then generated file name is,
SID_dwNUMBER_process_id.trc
My experience, findings and thoughts in my daily work with Oracle products
Showing posts with label Backup. Show all posts
Showing posts with label Backup. Show all posts
Thursday, 6 December 2012
Thursday, 1 December 2011
Expdp failed with error
Issue: Error while expdp backup
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [TABLE]
TABLE:"GL"."XLA_GLT_526074"
ORA-30032: the suspended (resumable) statement has timed out
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8170
----- PL/SQL Call Stack -----
object line object
handle number name
7000000963489b8 19028 package body SYS.KUPW$WORKER
7000000963489b8 8191 package body SYS.KUPW$WORKER
7000000963489b8 7382 package body SYS.KUPW$WORKER
7000000963489b8 2629 package body SYS.KUPW$WORKER
7000000963489b8 8846 package body SYS.KUPW$WORKER
7000000963489b8 1651 package body SYS.KUPW$WORKER
7000000b340a158 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_07" stopped due to fatal error at 04:28:58
Error in Alert Log:
Thu Dec 01 02:28:53 2011
statement in resumable session 'SYSTEM.SYS_EXPORT_FULL_07.1' was suspended due to
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
Findings:
There are orphaned Data Pump jobs that have not been cleaned up where the LOB segment associated with
the SYS_EXPORT_FULL_n tables is consuming much of the space in the SYSTEM tablespace.
col owner format a10;
col table_name format a20;
select l.owner,
l.table_name,
l.segment_name,
s.bytes
from dba_lobs l,
dba_segments s
where l.table_name like 'SYS_EXPORT_FULL%' and
l.table_name = s.segment_name;
OWNER TABLE_NAME SEGMENT_NAME BYTES
---------- -------------------- ------------------------------ ----------
SYSTEM SYS_EXPORT_FULL_02 SYS_LOB0000456326C00045$$ 132726784
SYSTEM SYS_EXPORT_FULL_01 SYS_LOB0000434197C00045$$ 59056128
SYSTEM SYS_EXPORT_FULL_03 SYS_LOB0000456884C00045$$ 132726784
SYSTEM SYS_EXPORT_FULL_04 SYS_LOB0000529281C00045$$ 199041024
SYSTEM SYS_EXPORT_FULL_06 SYS_LOB0000564856C00045$$ 59039744
SYSTEM SYS_EXPORT_FULL_05 SYS_LOB0000564275C00045$$ 59039744
SYSTEM SYS_EXPORT_FULL_07 SYS_LOB0000565576C00045$$ 59039744
7 rows selected.
The results will show multiple export tables where the LOB segments associated with them are consuming the space.
Solution:
Drop the export tables by following the steps outlined in Note 336014.1.
Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:
CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_02 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_03 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_04 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_05 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_06 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_07 EXPORT FULL NOT RUNNING 0
Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.
Step 3. Drop master tables which are not running
DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_02;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_03;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_04;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_05;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_06;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_07;
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? [ID 336014.1]
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CREATE_OBJECT_ROWS [TABLE]
TABLE:"GL"."XLA_GLT_526074"
ORA-30032: the suspended (resumable) statement has timed out
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8170
----- PL/SQL Call Stack -----
object line object
handle number name
7000000963489b8 19028 package body SYS.KUPW$WORKER
7000000963489b8 8191 package body SYS.KUPW$WORKER
7000000963489b8 7382 package body SYS.KUPW$WORKER
7000000963489b8 2629 package body SYS.KUPW$WORKER
7000000963489b8 8846 package body SYS.KUPW$WORKER
7000000963489b8 1651 package body SYS.KUPW$WORKER
7000000b340a158 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_07" stopped due to fatal error at 04:28:58
Error in Alert Log:
Thu Dec 01 02:28:53 2011
statement in resumable session 'SYSTEM.SYS_EXPORT_FULL_07.1' was suspended due to
ORA-01691: unable to extend lob segment SYSTEM.SYS_LOB0000565576C00045$$ by 40964 in tablespace SYSTEM
Findings:
There are orphaned Data Pump jobs that have not been cleaned up where the LOB segment associated with
the SYS_EXPORT_FULL_n tables is consuming much of the space in the SYSTEM tablespace.
col owner format a10;
col table_name format a20;
select l.owner,
l.table_name,
l.segment_name,
s.bytes
from dba_lobs l,
dba_segments s
where l.table_name like 'SYS_EXPORT_FULL%' and
l.table_name = s.segment_name;
OWNER TABLE_NAME SEGMENT_NAME BYTES
---------- -------------------- ------------------------------ ----------
SYSTEM SYS_EXPORT_FULL_02 SYS_LOB0000456326C00045$$ 132726784
SYSTEM SYS_EXPORT_FULL_01 SYS_LOB0000434197C00045$$ 59056128
SYSTEM SYS_EXPORT_FULL_03 SYS_LOB0000456884C00045$$ 132726784
SYSTEM SYS_EXPORT_FULL_04 SYS_LOB0000529281C00045$$ 199041024
SYSTEM SYS_EXPORT_FULL_06 SYS_LOB0000564856C00045$$ 59039744
SYSTEM SYS_EXPORT_FULL_05 SYS_LOB0000564275C00045$$ 59039744
SYSTEM SYS_EXPORT_FULL_07 SYS_LOB0000565576C00045$$ 59039744
7 rows selected.
The results will show multiple export tables where the LOB segments associated with them are consuming the space.
Solution:
Drop the export tables by following the steps outlined in Note 336014.1.
Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:
CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_02 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_03 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_04 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_05 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_06 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_EXPORT_FULL_07 EXPORT FULL NOT RUNNING 0
Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'.
Step 3. Drop master tables which are not running
DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_02;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_03;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_04;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_05;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_06;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_07;
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? [ID 336014.1]
Subscribe to:
Posts (Atom)