Forum Stats

  • 3,836,754 Users
  • 2,262,182 Discussions
  • 7,900,094 Comments

Discussions

I am un able to export data from database

3599275
3599275 Member Posts: 7
edited Jan 13, 2020 12:46PM in Developer Solutions

Hi,

I am not importing any data into DB, I am exporting the data from DB, While exporting the data from database, I am facing the following issue.

Export: Release 11.2.0.4.0 - Production on Sun Sep 3 07:10:35 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "OTN"."SYS_EXPORT_SCHEMA_99":  USERID=/********@otn_ALIAS SCHEMAS=SNML,WDM FLASHBACK_TIME=systimestamp DUMPFILE=dp_dump_ntwk_dir:expdat.dmp LOGFILE=dp_log_dir:export_Ntwk.log

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 18.21 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SNML"."CLOG"                               10.00 GB 8182771 rows

. . exported "WDM"."ALARMEVENT"                          1.143 GB 7071987 rows

. . exported "WDM"."ALMLOG"                              856.0 MB 3757437 rows

. . exported "WDM"."PSES"                                320.3 MB 2241391 rows

. . exported "WDM"."CIRCUITPACKADDRESS"                  6.017 MB   89375 rows

. . exported "WDM"."BASETPCOMPONENT"                     11.21 MB  328816 rows

ORA-31693: Table data object "WDM"."TP" failed to load/unload and is being skipped due to error:

ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3230445 (block size=4096)

ORA-27072: File I/O error

Additional information: 4

Additional information: 3230445

Additional information: 196608

ORA-31693: Table data object "SNML"."COUNT" failed to load/unload and is being skipped due to error:

ORA-31644: unable to position to block number 3230637 in dump file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp"

ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3230637 (block size=4096)

ORA-27072: File I/O error

Linux-x86_64 Error: 28: No space left on device

Additional information: 4

Additional information: 3230637

Additional information: -1

ORA-31693: Table data object "WDM"."PA_1830PSS" failed to load/unload and is being skipped due to error:

ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3230639 (block size=4096)

ORA-27072: File I/O error

Linux-x86_64 Error: 28: No space left on device

Additional information: 4

Additional information: 3230639

I had calculated the dump file size = 3230445 * 4096 = 12.241937876679GB. It looks dump file is corrupted. Total estimation block size is 18.21 GB

Can you please help me how to rectify / recover it?

Regards,

Varadha

«1

Answers

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 6, 2017 6:55AM
    ORA-27072: File I/O errorLinux-x86_64 Error: 28: No space left on device

    What could those error messages mean?

    Maybe your filesystem is full?

  • 3599275
    3599275 Member Posts: 7
    edited Dec 6, 2017 7:00AM

    Hi Andris,

    No, we have enough space is available.

    FYI

    omsqrsvr1otn,root,root # df -h

    Filesystem            Size  Used Avail Use% Mounted on

    /dev/mapper/VG00-lvol_sys

                           16G   13G  2.5G  84% /

    tmpfs                  12G   72K   12G   1% /dev/shm

    /dev/vda1             248M   51M  185M  22% /boot

    /dev/mapper/VG00-lvol_tmp

                          504M   19M  461M   4% /tmp

    /dev/mapper/VG00-lvol_var

                          2.5G  1.2G  1.3G  48% /var

    /dev/mapper/IW_12-lvol1

                           15G  2.7G   12G  19% /VDs/IW

    /dev/mapper/OTN_12-lvol1

                          6.9G  2.7G  3.9G  41% /VDs/OTN_12

    /dev/mapper/PLT_12-lvol1

                          7.9G  3.2G  4.4G  43% /VDs/PLT_12

    /dev/mapper/vg01-logVol03

                           95G   43G   48G  48% /usr/Systems -> affected directory.

    /dev/mapper/vg01-logVol01

                          2.0G  204M  1.7G  11% /usr/Systems/Global_Instance_12_Master

    /dev/mapper/vg01-logVol02

                          2.9G  411M  2.4G  15% /usr/Systems/Global_Instance_12_Master/maintenance

    /dev/mapper/vg01-logVol05

                           15G  192M   14G   2% /usr/Systems/OTN_2_12_Master/BackupArea

    /dev/mapper/vg01-logVol04

                           11G  2.0G  8.1G  20% /usr/Systems/OTN_2_12_Master/maintenance

    /dev/mapper/vg01-logVol06

                          9.7G  487M  8.7G   6% /usr/Systems/corefiles

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 6, 2017 7:15AM

    Could you please run the following commands:

    cd /backup/OTN_2-12/otn/Ntwk/df -h .

    Do you have any filesystem quotas defined?

    Thanks,

    Andris

  • 3599275
    3599275 Member Posts: 7
    edited Dec 6, 2017 7:21AM

    Hi Andris,

    File system quota is not defined. Actually /backup directory is soft link with the following

    omsqrsvr1otn,root,root # cd /backup/

    omsqrsvr1otn,root,root # ls -lrt

    total 0

    lrwxrwxrwx 1 root root 32 Jul 10 10:15 EML_1-12 -> /usr/Systems/EML_1-12/BackupArea

    lrwxrwxrwx 1 root root 32 Oct 13  2017 OTN_2-12 -> /usr/Systems/OTN_2-12/BackupArea

    lrwxrwxrwx 1 root root 42 Oct 13  2017 Global_Instance-12 -> /usr/Systems/Global_Instance-12/BackupArea

    omsqrsvr1otn,root,root # cd /usr/Systems/OTN_2-12/BackupArea

    omsqrsvr1otn,root,root # ls -lrt

    total 20

    drwxrwxrwx 2 root root   16384 Jul 10 09:26 lost+found

    drwxrwxrwx 3 otn  gadmin  4096 Dec  6 05:06 otn

    omsqrsvr1otn,root,root # cd otn/Ntwk/

    omsqrsvr1otn,root,root # ls -lrt

    total 8

    -rw-rw-r-- 1 root root 2742 Dec  6 05:06 tablespaces.dat

    -rw-rw-r-- 1 root root   21 Dec  6 05:06 BackupRestoreInfo.cfg

    omsqrsvr1otn,root,root # pwd

    /usr/Systems/OTN_2-12/BackupArea/otn/Ntwk

    omsqrsvr1otn,root,root # cd /backup/OTN_2-12/otn/Ntwk/

    omsqrsvr1otn,root,root # ls -lrt

    total 8

    -rw-rw-r-- 1 root root 2742 Dec  6 05:06 tablespaces.dat

    -rw-rw-r-- 1 root root   21 Dec  6 05:06 BackupRestoreInfo.cfg

    omsqrsvr1otn,root,root #

    So, we have enough file system space is available. There is no issue with space

  • 3599275
    3599275 Member Posts: 7
    edited Dec 6, 2017 7:23AM

    Hi Andris,

    Please find the requested o/p given below.

    omsqrsvr1otn,root,root # cd /backup/OTN_2-12/otn/Ntwk/

    omsqrsvr1otn,root,root # df -h .

    Filesystem            Size  Used Avail Use% Mounted on

    /dev/mapper/vg01-logVol05

                           15G  192M   14G   2% /usr/Systems/OTN_2_12_Master/BackupArea

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 6, 2017 7:40AM

    You only have 14G available in your file system (as root, less as non-root) . You need 18GB. You don't have enough space.

    Andris

  • 3599275
    3599275 Member Posts: 7
    edited Dec 6, 2017 7:54AM

    Hi Andris,

    I had extended the space and tried backup again, still facing the same issue.

    FYI

    omsqrsvr1otn,root,root # cd /usr/Systems/OTN_2_12_Master/BackupArea

    omsqrsvr1otn,root,root # df -h .

    Filesystem            Size  Used Avail Use% Mounted on

    /dev/mapper/vg01-logVol05

                           21G  196M   20G   1% /usr/Systems/OTN_2_12_Master/BackupArea

    Error message:

    ORA-31693: Table data object "WDM"."TP" failed to load/unload and is being skipped due to error:

    ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3230445 (block size=4096)

    ORA-27072: File I/O error

    Additional information: 4

    Additional information: 3230445

    Additional information: 196608

    ORA-31693: Table data object "SNML"."COUNT" failed to load/unload and is being skipped due to error:

    ORA-31644: unable to position to block number 3230637 in dump file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp"

    ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3230637 (block size=4096)

    ORA-27072: File I/O error

    Linux-x86_64 Error: 28: No space left on device

    Regards,

    Varadha

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 6, 2017 8:00AM

    I suggest you try a different file system that has sufficient space (> 25GB)

    Andris

  • 3599275
    3599275 Member Posts: 7
    edited Dec 6, 2017 8:27AM

    Hi Andris,

    File system size has been increased to 28GB

    omsqrsvr1otn,root,root # cd /usr/Systems/OTN_2_12_Master/BackupArea

    omsqrsvr1otn,root,root # df -h .

    Filesystem            Size  Used Avail Use% Mounted on

    /dev/mapper/vg01-logVol05

                           28G  196M   26G   1% /usr/Systems/OTN_2_12_Master/BackupArea

    still facing the same issue.

    ORA-31693: Table data object "WDM"."USERSDOMAINS" failed to load/unload and is being skipped due to error:

    ORA-31644: unable to position to block number 3246095 in dump file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp"

    ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3246095 (block size=4096)

    ORA-27072: File I/O error

    Linux-x86_64 Error: 28: No space left on device

    Additional information: 4

    Additional information: 3246095

    Additional information: -1

    ORA-31694: master table "OTN"."SYS_EXPORT_SCHEMA_99" failed to load/unload

    ORA-19502: write error on file "/backup/OTN_2-12/otn/Ntwk/expdat.dmp", block number 3246097 (block size=4096)

    ORA-27072: File I/O error

    Linux-x86_64 Error: 28: No space left on device

    Additional information: 4

    Additional information: 3246097

    Additional information: -1

    Job "OTN"."SYS_EXPORT_SCHEMA_99" stopped due to fatal error at Sun Sep 3 07:20:40 2017 elapsed 0 00:10:02

    Regards,

    Varadha

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 6, 2017 8:36AM

    Maybe it's some filesystem issue. That's why I said "try a different file system".

    Anyway, if possible, you should umount and fsck the filesystem and then try again.

    Andris