Shulou(Shulou.com)06/01 Report--

Recently, an exception occurred in the expdp backup of an integrated network management system. The error message is as follows:

Export: Release 10.2.0.4.0-64bit Production on Tuesday, December 4, 2016 11:30:00

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Start "SYSTEM". "SYS_EXPORT_SCHEMA_07": system/* schemas=ultra directory=backup_expdp dumpfile=expnms_201604121.dmp,expnms_201604122.dmp,expnms_201604123.dmp,exp_nms201604124.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160412.log

Estimation is being made using the BLOCKS method.

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimate using the BLOCKS method: 197.1 GB

Dealing with object type SCHEMA_EXPORT/USER

Dealing with object type SCHEMA_EXPORT/SYSTEM_GRANT

Dealing with object type SCHEMA_EXPORT/ROLE_GRANT

Dealing with object type SCHEMA_EXPORT/DEFAULT_ROLE

Dealing with object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Dealing with object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Dealing with object type SCHEMA_EXPORT/DB_LINK

Dealing with object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE

Dealing with object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/COMMENT

Dealing with object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Dealing with object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Dealing with object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Dealing with object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Dealing with object type SCHEMA_EXPORT/VIEW/VIEW

Dealing with object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/TRIGGER

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Dealing with object type SCHEMA_EXPORT/JOB

Dealing with object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

ORA-31693: the table data object "ULTRA". "DAY_META_TASK_RESULT_B" cannot be loaded / unloaded and is skipped with the following error:

ORA-02354: error exporting / importing data

ORA-01555: snapshot is too old: fallback segment number 24 (name "_ SYSSMU24 $") is too small

. . Exported "ULTRA". "ZXMG_UDPATE_BACKUP" 18.36 GB 536021168 lines

Next is the process of dealing with the problem. When you see Ora-01555, the first thing that comes to mind is to view and adjust the undo_retention parameters.

SQL > show parameter undo_r

NAME TYPE VALUE

-

Undo_retention integer 3600

SQL > select tablespace_name,sum (bytes) / 1024 gb from dba_data_files where tablespace_name like'% UNDO%' group by tablespace_name

TABLESPACE_NAME GB

UNDOTBS1 106.992172

Then check the utilization rate of undo table space, and its utilization rate is only 1%, so consider adjusting the undo_retention parameter to 5400, the problem remains after adjustment, and then to 7200, the problem still exists, but the utilization rate of undo table space is up to 30%. Now, it can be judged that it is not a simple problem of the undo tablespace parameter undo_retention, but needs to be analyzed in detail. Looking at the structure of the problem table, it is found that the table has two large fields:

SQL > desc "ULTRA". "DAY_META_TASK_RESULT_B"

Is the name empty? Types

-

META_TASK_RESULT_ID NOT NULL VARCHAR2 (50)

SCHEDULER_ID VARCHAR2 (50)

META_TASK_ID VARCHAR2 (50)

TIME NUMBER (20)

ALARM NUMBER (5)

META_TASK_STATE NUMBER (5)

INSTANCE VARCHAR2 (200)

UNIT VARCHAR2 (10)

VALUE_CHECK VARCHAR2 (5)

SHELL_RESULT CLOB

ADVICE VARCHAR2 (4000)

OPINION VARCHAR2 (100)

ALARM_INFO VARCHAR2 (200)

VALUE CLOB

VTABLE VARCHAR2 (30)

REFLAG VARCHAR2 (50)

By querying the official oracle website, the lob big field of oracle has its own retention parameter. If only undo_retention is adjusted, but not synchronized to the lob big field, this parameter is still 900s by default. Make sure that the query result is as follows:

SQL > select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'

TABLE_NAME COLUMN_NAME PCTVERSION RETENTION

-

DAY_META_TASK_RESULT_B SHELL_RESULT 900

DAY_META_TASK_RESULT_B VALUE 900

After clarifying the cause of the problem, the next step is to modify the retention setting of the table that contains large lob fields.

SQL > ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB (SHELL_RESULT) (retention)

Table altered.

SQL > ALTER TABLE DAY_META_TASK_RESULT_B MODIFY LOB (VALUE) (retention)

Table altered.

SQL > select table_name,column_name,pctversion,retention from dba_lobs where table_name='DAY_META_TASK_RESULT_B'

TABLE_NAME COLUMN_NAME PCTVERSION RETENTION

-

DAY_META_TASK_RESULT_B SHELL_RESULT 7200

DAY_META_TASK_RESULT_B VALUE 7200

After the adjustment, the expdp of the platform returns to normal:

Export: Release 10.2.0.4.0-64bit Production on Thursday, 21 April, 2016 11:30:00

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Start "SYSTEM". "SYS_EXPORT_SCHEMA_02": system/* schemas=ultra directory=backup_expdp dumpfile=expnms_201604211.dmp,expnms_201604212.dmp,expnms_201604213.dmp,exp_nms201604214.dmp filesize=40g VERSION=10.2.0.2 exclude=statistics logfile=exp_20160421.log

Estimation is being made using the BLOCKS method.

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimate using the BLOCKS method: 199.8 GB

Dealing with object type SCHEMA_EXPORT/USER

Dealing with object type SCHEMA_EXPORT/SYSTEM_GRANT

Dealing with object type SCHEMA_EXPORT/ROLE_GRANT

Dealing with object type SCHEMA_EXPORT/DEFAULT_ROLE

Dealing with object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Dealing with object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Dealing with object type SCHEMA_EXPORT/DB_LINK

Dealing with object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Dealing with object type SCHEMA_EXPORT/TABLE/TABLE

Dealing with object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/COMMENT

Dealing with object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Dealing with object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Dealing with object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Dealing with object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Dealing with object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Dealing with object type SCHEMA_EXPORT/VIEW/VIEW

Dealing with object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Dealing with object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type SCHEMA_EXPORT/TABLE/TRIGGER

Dealing with object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Dealing with object type SCHEMA_EXPORT/JOB

Dealing with object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

. . Exported "ULTRA". "DAY_META_TASK_RESULT_B" 39.40 GB 20009768 lines

. . Exported "ULTRA". "ZXMG_UDPATE_BACKUP" 18.87 GB 550823549 lines

.

.

.

. . The line "ULTRA". "WLANUPDATESCHEDULE" 0 KB 0 is exported

. . The line "ULTRA". "WORKFLOW_STAT" 0 KB 0 is exported

The primary table "SYSTEM". "SYS_EXPORT_SCHEMA_02" has been successfully loaded / unloaded

*

The dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:

/ opt/backup/expnms_201604211.dmp

/ opt/backup/expnms_201604212.dmp

/ opt/backup/expnms_201604213.dmp

/ opt/backup/exp_nms201604214.dmp

Assignment "SYSTEM". "SYS_EXPORT_SCHEMA_02" completed successfully at 18:12:35

So far, the fault has been dealt with!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.