10 Replies Latest reply: Oct 5, 2012 7:18 AM by 784227 RSS

    export import tablespace

    784227
      Hi,

      DB 10.2.0.4
      AIX 5.2
      PROD

      My requirement is, I made database clone from 20 days back coldbackup There are many tablespaces in the database, In which tablespace there is one tablespace DEV_INDEX.In the Production this tablespace have 18 datafiles and in the clone it has 14 datafiles (because clone have been made from 20 days back coldbackup thats why in 20 days 4 datafiles have added in PROD). Now i want to export DEV_INDEX tablespace from PROD and import to clone. Please guide me to execute this.

      Do i need to create four datafiles on clone instance before importing?

      Please provide the correct export and import command for tablespace.

      Thanks

      Edited by: user13382934 on Oct 4, 2012 5:06 PM
        • 1. Re: export import tablespace
          sb92075
          user13382934 wrote:
          Hi,

          DB 10.2.0.4
          AIX 5.2
          PROD

          My requirement is, I made database clone from 20 days back coldbackup There are many tablespaces in the database, In which tablespace there is one tablespace DEV_INDEX.In the Production this tablespace have 18 datafiles and in the clone it has 14 datafiles (because clone have been made from 20 days back coldbackup thats why in 20 days 4 datafiles have added in PROD). Now i want to export DEV_INDEX tablespace from PROD and import to clone. Please guide me to execute this.

          Do i need to create four datafiles on clone instance before importing?
          yes

          >
          Please provice the correct export and import command for tablespace.

          Thanks
          INDEX "data" is never really exported; just metadata for the indexes.
          When importing the indexes are created from scratch based upon where actual data rows now exist
          • 2. Re: export import tablespace
            Veeresh.S
            you can use either tablespaces mode or transportable tablespace mode.

            Tablespace mode :

            Prod DB:
            expdp system/xxxxxx directory=data_pump_dir dumpfile=users.dmp logfile=DEV_INDEX.dmp.log tablespaces=DEV_INDEX

            Dev/Cloned DB:
            impdp system/xxxxxx directory=data_pump_dir dumpfile=users.dmp logfile=DEV_INDEX.dmp.log tablespaces=DEV_INDEX

            if source-destination tablespace name is different then use remap_tablespace.
            Do i need to create four datafiles on clone instance before importing?
            yes, before impdp make sure you have added more space to the tablespace by adding/resizing datafiles and if adding it need not to be exactly 4, what matters is size not no of datafiles.
            • 3. Re: export import tablespace
              784227
              Thanks,

              I cannot use transport tablespace because the size of that tablespace is 295 gb and you know i need to run " alter tablespace dev_index read only" and it will generate more redo log file so better to use exp imp as you provided.

              I am going to run as you provided.
              • 4. Re: export import tablespace
                784227
                Hi,

                I did the same but got below error please suggest.

                $ expdp system/****** directory=DIR_LOG dumpfile=DEV.dmp logfile=DEV_INDEX.log tablespaces=DEV_INDEX

                Estimate in progress using BLOCKS method...
                Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                Total estimation using BLOCKS method: 0 KB
                ORA-31655: no data or metadata objects selected for job
                Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at 03:13:23

                Thanks
                • 5. Re: export import tablespace
                  sb92075
                  user13382934 wrote:
                  Hi,

                  I did the same but got below error please suggest.

                  $ expdp system/****** directory=DIR_LOG dumpfile=DEV.dmp logfile=DEV_INDEX.log tablespaces=DEV_INDEX

                  Estimate in progress using BLOCKS method...
                  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                  Total estimation using BLOCKS method: 0 KB
                  ORA-31655: no data or metadata objects selected for job
                  Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at 03:13:23

                  Thanks
                  [oracle@localhost dbs]$ oerr ora 31655
                  31655, 00000, "no data or metadata objects selected for job"
                  // *Cause:  After the job parameters and filters were applied, 
                  //          the job specified by the user did not reference any objects.
                  // *Action: Verify that the mode of the job specified objects to be moved.
                  //          For command line clients, verify that the INCLUDE, EXCLUDE and 
                  //          CONTENT parameters were correctly set.  For DBMS_DATAPUMP API 
                  //          users, verify that the metadata filters, data filters, and
                  //          parameters that were supplied on the job were correctly set.
                  as I said, indexes do not really get exported; just metadata.

                  How do you export INDEX without having tables upon which any index is based?
                  • 6. Re: export import tablespace
                    784227
                    Yes you were correct, But i need to export this tablespace to the clone database because there need for recovery.Actually when i was trying to create control file then one index data file was missed but control file created and database is open but need recovery even SR recomend to export and import.find below what SR said.

                    The datafile 41 appears to be an index datafile. Your options would include:
                    1. create the indexes on the new database
                    2. export data ( indexes ) from the source database
                    3. use transportable tablespaces to transfer data
                    4. or use other application to transfer data to the new database
                    5. recreate the database again including all datafiles.

                    Please suggest
                    • 7. Re: export import tablespace
                      sb92075
                      user13382934 wrote:
                      Hi,

                      DB 10.2.0.4
                      AIX 5.2
                      PROD

                      My requirement is, I made database clone from 20 days back coldbackup
                      I understand above.
                      But i need to export this tablespace to the clone database because there need for recovery.
                      I do not understand why any recovery is needed.

                      I am not clear what task you are really trying to complete.
                      Do you desire to get the clone DB current with Production DB?
                      If so, what gets the tables in the clone DB populated with recent DML from Prod?
                      • 8. Re: export import tablespace
                        784227
                        I do not understand why any recovery is needed.
                        --Yesterday i made clone but it is giving below error because i missed one index datafile to restore inspite of this database is open but showing continue one file is missing in the alert log file.FYI that missing file which is showing in alert log file actually physically does not exist .Find below

                        Errors in file /path/bdump/rmsclone_smon_1540436.trc:
                        ORA-00376: file 41 cannot be read at this time
                        ORA-01111: name for data file 41 is unknown - rename to correct file
                        ORA-01110: data file 41: '/path/product/10g/dbs/MISSING00041'



                        select status from v$datafile where file#=41;

                        STATUS
                        -------
                        RECOVER

                        when i tried to create datafile

                        SQL> alter database create datafile '/path/product/10g/dbs/MISSING00041'
                        dev_index08.dbf';
                        alter database create datafile '/pathb/product/10g/dbs/MISSING00041' as '/
                        dev_index08.dbf'
                        *
                        ERROR at line 1:
                        ORA-01178: file 41 created before last CREATE CONTROLFILE, cannot recreate
                        ORA-01111: name for data file 41 is unknown - rename to correct file
                        ORA-01110: data file 41: 'path/product/10g/dbs/MISSING00041'

                        Even when i tried to offline to that tablespace which have that missing datafile.

                        SQL> alter tablespace DEV_INDEX offline immediate;
                        alter tablespace DEV_INDEX offline immediate
                        *
                        ERROR at line 1:
                        ORA-01145: offline immediate disallowed unless media recovery enabled

                        Thanks
                        • 9. Re: export import tablespace
                          sb92075
                          user13382934 wrote:
                          I do not understand why any recovery is needed.
                          Recovery is needed because you missed one index datafile
                          --Yesterday i made clone but it is giving below error because i missed one index datafile to restore inspite of this database is open but showing continue one file is missing in the alert log file.FYI that missing file which is showing in alert log file actually physically does not exist .Find below
                          The solution is to clone the DB again, again, but this time include all the datafiles.
                          • 10. Re: export import tablespace
                            784227
                            The solution is to clone the DB again, again, but this time include all the datafiles.
                            --The only solution is to clone DB again with all datafiles,No alternate solution correct? Because it is time taking to restore all the datafiles it will take almost more than two days.

                            Many Thanks