12 Replies Latest reply on Dec 6, 2017 2:14 PM by Andris Perkons-Oracle

    I am un able to export data from database

    3599275

      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. Re: I am un able to export data from database
          Andris Perkons-Oracle

          ORA-27072: File I/O error

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

          What could those error messages mean?

           

          Maybe your filesystem is full?

          • 2. Re: I am un able to export data from database
            3599275

            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

            • 3. Re: I am un able to export data from database
              Andris Perkons-Oracle

              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

              • 4. Re: I am un able to export data from database
                3599275

                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

                • 5. Re: I am un able to export data from database
                  3599275

                  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

                  • 6. Re: I am un able to export data from database
                    Andris Perkons-Oracle

                    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

                    • 7. Re: I am un able to export data from database
                      3599275

                      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

                      • 8. Re: I am un able to export data from database
                        Andris Perkons-Oracle

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

                         

                        Andris

                        • 9. Re: I am un able to export data from database
                          3599275

                          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

                          • 10. Re: I am un able to export data from database
                            Andris Perkons-Oracle

                            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

                            • 11. Re: I am un able to export data from database
                              3599275

                              Hi Andris,

                               

                              I had tried different File system also, umount the file system and mount again.

                              Still facing the same issue.

                               

                              Regards,
                              Varadha

                              • 12. Re: I am un able to export data from database
                                Andris Perkons-Oracle

                                With "umount the file system and mount again" you mean that you have or have not run a file system check?

                                 

                                Andris