12 Replies Latest reply: Apr 9, 2012 10:43 AM by AAG RSS

    How to create the same tablespaces in test database as in production

    Marco V.
      I've used the following commands:
      (from 10gR2)
      expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=MDLOG EXCLUDE=STATISTICS DUMPFILE=mdlogMETADATA.dmp include=tablespace content=metadata_only

      Then:
      (into 11gR2)
      impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=mdlogMETADATA.dmp include=TABLESPACE sqlfile=c.sql

      According to the following posts it should work
      how to find username and tablespace name indatapump import

      [http://www.rampant-books.com/art_nanda_datapump.htm|http://www.rampant-books.com/art_nanda_datapump.htm]

      instead I obtain
      ORA-39002: invalid operation
      ORA-39168: Object path TABLESPACE was not found.

      Now I'm searching for incompatible options between expdp (10gr2) and impdp (11gr2).. and if include=TABLESPACE should be replaced with other option...

      meanwhile... is there something that can tell me if I'm making any mistakes?

      Tanks
        • 1. Re: How to create the same tablespaces in test database as in production
          Srini Chavali-Oracle
          I do not believe "INCLUDE=TABLESPACE" is valid syntax in impdp

          http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/dp_import.htm#SUTIL916

          Are you, perhaps, thinking of REMAP_TABLESPACE ? http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/dp_import.htm#SUTIL929

          HTH
          Srini
          • 2. Re: How to create the same tablespaces in test database as in production
            Marco V.
            I've pasted a command line I used to perform other test... but as suggested from the link posted... initially I used the following command line:
            expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=MDLOG EXCLUDE=STATISTICS DUMPFILE=mdlogMETADATA.dmp content=metadata_only


            Anyway I'm not talking about REMAP_TABLESPACE.. because I don't want to write every tablespaces (several)...
            Any ideas?
            • 3. Re: How to create the same tablespaces in test database as in production
              Dean Gagne-Oracle
              Hi,

              The expdp command you listed in your first post can not work. You can't have exclude and include in the same command.

              exclude says exclude these objects but include everything else.

              Include says include only these objects and exclude everything else.

              If you use the expdp command from your second post, there are not tablespace objects in a schema export. The include=tablespace will only recreate the tablespaces and not the objects in the tablespaces. If you want to do that, then you need to remove the schema= MDLOG and add in FULL=Y. The tablespace definitions are only included in a full export.

              What is your ultimate goal? Do you want just the tablespace definitions moved? If so

              expdp full=y include=tablespace directory=...

              If you want all of the tablespaces and the objects in those tablespaces, then I think it would be 2 steps:

              expdp full=y include=tablespace directory...
              expdp tablespaces=tbs1,tbs2,... content=metadata_only ...

              You don't need the content=data_only, but you had it on both of your expdp commands so I assumed that is what you wanted so I added it.

              If you have a different goal, then post it and I'll see what I can come up with.

              Dean

              p.s. If you want to see what objects are included in a particular mode you can query sys.datapump_paths. Het_type is the mode

              full=y look at het_type DATABASE_EXPORT
              schemas=... look at het_type SCHEMA_EXPORT
              tables=... look at het_type TABLE_EXPORT
              tablespaces=... look at het_type TABLE_EXPORT
              transport look at het_type TRANSPORTABLE_EXPORT

              Edited by: Dean Gagne on Feb 2, 2010 9:18 AM
              • 4. Re: How to create the same tablespaces in test database as in production
                Dean Gagne-Oracle
                Just to clear things up...

                INCLUDE=TABLESPACE is a valid parameter for both expdp and impdp.

                Dean
                • 5. Re: How to create the same tablespaces in test database as in production
                  Srini Chavali-Oracle
                  Dean - thanks - I stand corrected

                  Srini
                  • 6. Re: How to create the same tablespaces in test database as in production
                    Marco V.
                    Thanks Dean for you answer.
                    You're right.. if I want to include tablespaces definitions I need to use a full export.
                    My needs were to recreate one schema and its tablespaces on another database (two different machines, I'm not saying two databases on one machines), so I thougth I could use SCHEMAS option while exporting and INCLUDE=TABLESPACE while importing.

                    I used SQLFILE to view what the impdp did: I'm using ASM on both databases, but different DISK GROUP NAME so I'd like to see and eventually edit the script file.

                    The problem is I have several schemas on the first dataabse and I don't want to export all data.. so if exporting full database is the only option I think I need first to use:
                    expdp full=y content=metadata_only dumpfile=mdlog_metadata.dmp
                    impdp dumpfile=mdlog_metadata.dmp include=tablespace sqlfile=c.sql

                    then
                    expdp schemas=mdlog dumpfile=mdlog.dmp
                    impdp dumpfile=mdlog.dmp
                    • 7. Re: How to create the same tablespaces in test database as in production
                      Marco V.
                      Ok.. it works.. but I need to edit all DISK GROUP NAME associated to datafiles.. because they are different...

                      Is there any option to REMAP "DISK GROUP NAME" ?

                      I'm unsuccessfully trying...
                      impdp PARFILE=parameter_file.par

                      where parameter_file.par is:
                      DIRECTORY=DATA_PUMP_DIR
                      DUMPFILE=all_schemas.dmp
                      REMAP_DATAFILE="'+DATA_PROD':'+DATA_DEV'"
                      INCLUDE=TABLESPACE
                      SQLFILE=remap_datafile.sql

                      no error is reported.

                      The following is one of my tablespace:
                      CREATE TABLESPACE "TBL_BILLING" DATAFILE
                      '+DATA_PROD/rac1/datafile/tbl_billing.273.661541263' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_PROD/rac1/datafile/tbl_billing.274.661541267' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_PROD/rac1/datafile/tbl_billing.275.661541273' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_PROD/rac1/datafile/tbl_billing.276.661541279' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_PROD/rac1/datafile/tbl_billing.277.661541287' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M
                      LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                      EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

                      I'd like to have something like...
                      CREATE TABLESPACE "TBL_BILLING" DATAFILE
                      '+DATA_DEV/rac1/datafile/tbl_billing.273.661541263' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_DEV/rac1/datafile/tbl_billing.274.661541267' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_DEV/rac1/datafile/tbl_billing.275.661541273' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_DEV/rac1/datafile/tbl_billing.276.661541279' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M,
                      '+DATA_DEV/rac1/datafile/tbl_billing.277.661541287' SIZE 1073741824
                      AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M
                      LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                      EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
                      • 8. Re: How to create the same tablespaces in test database as in production
                        Dean Gagne-Oracle
                        The problem is I have several schemas on the first dataabse and I don't want to export all data.. so if exporting full database is the only option I think I need first >to use:
                        expdp full=y content=metadata_only dumpfile=mdlog_metadata.dmp
                        impdp dumpfile=mdlog_metadata.dmp include=tablespace sqlfile=c.sql
                        Hi,

                        Based on what I see, this is what you want:

                        expdp full=y include=tablespace dumpfile=tbs_def.dmp ... this will export just the tablespace definitions. It will not export any objects in those tablespaces or any data in those tablespaces. If you want to verify, do this export command and then run

                        impdp full=y dumpfile=tbs_def.dmp sqlfile=tbs_def.sql ... then look at tbs.def.sql. The only thing in there will be the create tablespace ddl statements. Nothing else.

                        if you have lots of tablespaces and you only want the definitions of a subset, then use this include filter instead:

                        include=tablespace"in ('tablespacea','tablespaceb',...)"

                        This will only get the tablespace definitons for the tablespace name you list in the fitler.

                        To import this dumpfile just use:

                        impdp full=y dumpfile=tbs_def.dmp ... you don't need to say content=metadata_only because the only thing in the dumpfile is metadata. You don't need an include filter if you want all of the objects exported to be imported. So, if your expdp command gets just the things that you want, then your import can just import all of them.
                        then
                        expdp schemas=mdlog dumpfile=mdlog.dmp
                        impdp dumpfile=mdlog.dmp
                        These 2 command look right.

                        Dean
                        • 9. Re: How to create the same tablespaces in test database as in production
                          Dean Gagne-Oracle
                          You need to specify the complete datafile path, not just the part you want renamed:

                          remap_datafile="'+DATA_PROD/rac1/datafile/tbl_billing.273.661541263':+DATA_DEV/rac1/datafile/tbl_billing.273.661541263'"

                          I think you want to make sure the casing is correct.

                          Dean
                          • 10. Re: How to create the same tablespaces in test database as in production
                            Marco V.
                            Thanks for your answer...

                            So I'll proceed with SQLFILE option.. and just the editor VI to replace some strings...
                            • 11. Re: How to create the same tablespaces in test database as in production
                              929146
                              Can I export all objects(only metadata) from a tablespace ?
                              • 12. Re: How to create the same tablespaces in test database as in production
                                AAG
                                Yes we can,

                                use the these clause in export command.
                                full=y include=tablespace"in ('name1')" content=metadata_only ...

                                I am not sure of this though. Please correct me if i am wrong.

                                Regards,