Monday, 27 August 2018

ORA-39171: Job is experiencing a resumable wait | Oracle EXPDP

ORA-39171: Job is experiencing a resumable wait | ORA-01691: unable to extend lob segment in SYSTEM Tablespace


I was using the simple EXPDP command to take the backup of my Oracle Database for testing purpose and the execution started successfully but got hanged after 2 minutes. There was a message getting displayed in the Execution log stated as

ORA-39171: Job is experiencing a resumable wait.


The complete description is here,

Resumable error: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000118280C00100$$ by 1024 in tablespace SYSTEM
Resumable stmt: INSERT INTO "SYS"."SYS_EXPORT_FULL_01" (object_type_path, object_path_seqno, dump_fileid, dump_position,dump_length, dump_orig_length, dump_allocation, process_order, duplicate, object_row, object_type, object_schema, original_object_schema, object_name, object_long_name, original_object_name, partition_name, subpartition_name, object_tablespace, grantor, flags, processing_state, processing_status, base_process_order, base_object_type, base_object_schema, base_object_name, domain_process_order, xml_clob, ancestor_process_order, property, trigflag, size_estimate, creation_level, parent_process_order, value_n, object_int_oid, metadata_io, tde_rewrapped_key, option_tag, orig_base_object_schema, parallelization, unload_method) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :13, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43)
Resumable stmt status: SUSPENDED
Resumable stmt start: 08/27/18 15:27:19 stmt suspend: 08/27/18 15:27:24
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000118280C00100$$ by 1024 in tablespace SYSTEM
Resumable stmt: INSERT INTO "SYS"."SYS_EXPORT_FULL_01" (object_type_path, object_path_seqno, dump_fileid, dump_position,dump_length, dump_orig_length, dump_allocation, process_order, duplicate, object_row, object_type, object_schema, original_object_schema, object_name, object_long_name, original_object_name, partition_name, subpartition_name, object_tablespace, grantor, flags, processing_state, processing_status, base_process_order, base_object_type, base_object_schema, base_object_name, domain_process_order, xml_clob, ancestor_process_order, property, trigflag, size_estimate, creation_level, parent_process_order, value_n, object_int_oid, metadata_io, tde_rewrapped_key, option_tag, orig_base_object_schema, parallelization, unload_method) VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :13, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43)
Resumable stmt status: SUSPENDED
Resumable stmt start: 08/27/18 15:27:19 stmt suspend: 08/27/18 15:27:24

ORA-39171, as the error description says, is a resumable error which normally occurs when database is looking for some resource and is not getting the same. The lack of resource would not cause the process to be cancelled but the database will keep on waiting till the resource is provided to it.

In my case, the issue was with the availability of space in SYSTEM tablespace. There were 2 datafiles in SYSTEM tablespace and all of them were full. This I queried from DBA_DATA_FILES view. Then I added a new datafile to the same tablespace.

SQL> alter tablespace SYSTEM add datafile 'Location\system03.dbf' size 2G autoextend on;

and the backup script continued it's execution.

Resumable stmt status: SUSPENDED
Resumable stmt start: 08/27/18 15:27:19 stmt suspend: 08/27/18 15:27:24
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC

I hope this helps !!

No comments:

Post a comment