1 2 Previous Next 15 Replies Latest reply: Aug 21, 2014 8:53 PM by Hemant K Chitale RSS

    RMAN - Oracle 11g: only import tablespace not whole database

    2699156

      Hello,

      I have a question about usage of RMAN. Or better: i have a couple of questions about RMAN.

       

      I should import a dbf file created with RMAN into a Oracle 11g database.

      I must admit that I simply have no experience with RMAN.... But as far as I could find out:
      - dbf extension is "optional" and nothing special for Oracle

      - RMAN should be easy to use ...

       

      So that dbf file I have comes along with a ctl file. It's only 100MB big - that is strange for me since other backups I needed to import where above 30GB - but I guess it is complete.

       

      All the articles I read about RMAN gave me the conclusion that RMAN is build to backup and restore complete databases.
      But that would mean for my understanding that RMAN would use the backup to overwrite the whole existing database - is that right?

      This isn't the way I can accept because I need the data in the existing database, too.

       

      So my main question is: how can I just import a tablespace into my existing database using RMAN?

       

      I found for example this tutorial: https://apex.oracle.com/pls/apex/f?p=44785:24:0::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:6289,1 But it's just not working for me since I don't even have a sysbackup user as described in the beginning.

       

      So could anyone be so kind and help me out? Thank you!

        • 1. Re: RMAN - Oracle 11g: only import tablespace not whole database
          EdStevens

          First off, you can't just import any old .dbf file into an existing database.  You need to read up on 'transportable tablespaces'.  I'm not going to explain them here.  You need to read the docs, then come back with specific questions.

           

          As far as "I don't even have a sysbackup user as described in the beginning." .... I'm afraid the tutorial was a bit misleading.  This account doesn't exist on any of my databases, and when I followed the link to the tutorial, that is the first time I've ever heard of this account.

          I just connect to rman like this:    "rman target / "   It's exactly the same as connecting with sqlplus like this "sqlplus / as sysdba".

          • 3. Re: RMAN - Oracle 11g: only import tablespace not whole database
            skahlert

            You should recover a test database with your RMAN backupset and continue by exporting the desired schemas and tables via datapump.

            Afterwards you might import that data into your target db while keeping the existing data. The feature you should use is REMAP_TABLE during import.

             

            Hope it helps.

            • 4. Re: RMAN - Oracle 11g: only import tablespace not whole database
              Hemant K Chitale

              >So that dbf file I have comes along with a ctl file. It's only 100MB big

              Ask the DBA who sent it to you *how* the DBF file nd CTL file backups were created.

              To "copy" a tablespace (one or more database files) from one database to another, one needs to follow the methods and commands for "Transportable Tablespace".  Since there are two documented methods (non-RMAN and RMAN) you'd need to know how that backup was created --- was it properly created in the first place.  Copying a tablespace doesn't require the controlfile so I am a bit sceptical about what the DBA sent you.

               

              NOTE : A transportable tablespace copy also requires a dumpfile that has the metadata for the tablespace contents.  This seems to be missing in what you have received.

               

              Hemant K Chitale

              • 5. Re: RMAN - Oracle 11g: only import tablespace not whole database
                2699156

                Sorry for asking twice but since I am not a oracle pro...

                I must set up a complete new machine with Oracle for a new Oracle database instance, right?
                So one Oracle instance is a database and one tablespace would be like a database in MySQL.

                • 6. Re: RMAN - Oracle 11g: only import tablespace not whole database
                  EdStevens

                  2699156 wrote:

                   

                  Sorry for asking twice but since I am not a oracle pro...

                  I must set up a complete new machine with Oracle for a new Oracle database instance, right?

                   

                  No, it is rather common to have multiple oracle databases on one machine, especially in development environments.

                  And to anticipate the next question from that --- no, you do not have to install a separate ORACLE_HOME for each database.  And no, you do not have to have a separate listener for each database.

                   

                  So one Oracle instance is a database and one tablespace would be like a database in MySQL.

                  I have no idea how MySQL does it.

                  Perhaps it's time for you to start digging into the Concepts Manual.  Contents

                  Very (very) breifly:

                  An "instance" is the collection of processes and memory structures that act on a database.

                  A database is a collection of data files that are managed as a whole and kept transitionally consistent by the instance.

                  A tablespace is a collection of one or more data files.  A tablespace can have multiple data files, but a given data file belongs to exactly one tablespace.

                  A tablespace is an abstraction of the data files.  The application knows nothing of the files themselves.

                  A tablespace can contain segment (tables, indexes, etc) belonging to multiple users/schemas.

                  A given user/schema can have segments in multiple tablespaces.

                  • 7. Re: RMAN - Oracle 11g: only import tablespace not whole database
                    2699156

                    Can't I just recover the database with RMAN?
                    So something like:

                    RMAN> restore datafile C:\Q.DFB ?


                    So yes, this simply doesn't work since the datafile is not in catalog... -.- I am sorry but I don't have the time to fully understand and read through Oracle Documentation. It cannot be that difficult to just restore this .dfb file, can it?
                    In addition this Oracle stuff is only for so few things needed and won't be used in future since in future and atm we are using MSSQL databases for nearly everything, only two or three Oracle instances are left over...

                    • 8. Re: RMAN - Oracle 11g: only import tablespace not whole database
                      Hemant K Chitale

                      >So that dbf file I have comes along with a ctl file. It's only 100MB big

                      You need to get more information from the DBA / Manager / Customer who sent you that file.  An Oracle databse backup is not generally a single DBF file (although it can be done so as to be a DBF file, but that would not normal).

                       

                       

                      Hemant K Chitale

                      • 9. Re: RMAN - Oracle 11g: only import tablespace not whole database
                        2699156

                        I thought DFB files were RMAN backup files?

                         

                        Installing Oracle Database and Creating a Database:

                        The data files for the seed database are stored in compressed RMAN backup format in a file with a .dfb extension. The seed database control file is stored in a file with .ctl extension. (This file is needed only when storing data files in Automatic Storage Management (ASM) disk groups or as Oracle managed files.) The .dbc file contains the location of the seed database data files and contains the source database name used to mount the control file.

                         

                        So just to clarify that: it's a DFB file - do I need all that extra information for that dfb file, too? Or just if it would be a dbf file? Thanks.

                        • 10. Re: RMAN - Oracle 11g: only import tablespace not whole database
                          Hemant K Chitale

                          Yes, that is a seed database supplied by Oracle during the software install.

                           

                          It is possible that the DBA created a backup of the database as a template using DBCA.  The template is also created in the same format as a seed database.  This is useful to create a "gold" standard of a database if exactly the same database is to be implemented on multiple servers.

                           

                          Have you asked the sender how the file was created ?

                           

                          We are all speculating here. 

                           

                          Hemant K Chitale

                          • 11. Re: RMAN - Oracle 11g: only import tablespace not whole database
                            Kent D Sorber

                            So that dbf file I have comes along with a ctl file. It's only 100MB big - that is strange for me since other backups I needed to import where above 30GB - but I guess it is complete.

                             

                            All the articles I read about RMAN gave me the conclusion that RMAN is build to backup and restore complete databases.
                            But that would mean for my understanding that RMAN would use the backup to overwrite the whole existing database - is that right?

                            This isn't the way I can accept because I need the data in the existing database, too.

                             

                            Hi,

                             

                            What do you mean the dbf comes along with a ctl file ? Control Files would not be GB in size.

                            Did you make changes to your data and do not have logs to restore/recover that data ?

                            Are you trying to restore just a Tablespace ?

                            You can restore just a datafile with RMAN but remember  the data in all Tables in the Tablespace will be change to the time use choose to restore.

                            You should configure FRA to protect your data better.

                             

                            Regards,

                            Kent D Sorber. OCP

                            • 12. Re: Re: RMAN - Oracle 11g: only import tablespace not whole database
                              EdStevens

                              2699156 wrote:

                               

                              Can't I just recover the database with RMAN?

                              That would depend on exactly how the file was created.  That's why people keep asking for that information.  So far I've not seen you respond to those requests.

                              FWIW, the extension '.dbf' (or any other) is a naming convention only.  It does NOT guarantee anything about the internal structure or nature of the file.  So at this point, without knowing the exact origin/history of this file, we can only guess that it might be a data file from some database.

                               

                              So something like:

                              RMAN> restore datafile C:\Q.DFB ?

                              Not if that datafile was not already a part of the database - not already a constituent data file of a tablespace in the database.

                               

                               

                              So yes, this simply doesn't work since the datafile is not in catalog... -.- I am sorry but I don't have the time to fully understand and read through Oracle Documentation.

                              But if you don't understand the fundamentals of how Oracle uses data files, if you don't understand the fundamentals of the relationships of data files to a database, how do you expect to understand what is needed to accomplish your task?

                               

                               

                              It cannot be that difficult to just restore this .dfb file, can it?

                              It can if we don't know the origin and nature of the file.  That's why people keep asking for that information.

                              Oracle is not a toy database.  It is very powerful and very flexible.  And with that power and flexibility comes complexity.  As I said before, it is NOT possible to just plug any old data file into any database.

                              In addition this Oracle stuff is only for so few things needed and won't be used in future since in future and atm we are using MSSQL databases for nearly everything, only two or three Oracle instances are left over...

                               

                              I understand your frustration.  I'm facing a situation right now where I'm having to take over management of a system that is foreign to me.  But a fundamental rule of DBA life is that you simply cannot apply the methods of one DB product (any db product) when you approach another DB product (any DB product). 

                               

                               

                               

                              So ... to sum up .... We probably can walk you through this, but not until you let us know exactly how this file was created and what it actually is.  That's why several people have asked for this.  Until we have that information, all we can really do is try to get you to understand the principles in the hope that you can take that information and work out the solution for yourself.

                              • 13. Re: RMAN - Oracle 11g: only import tablespace not whole database
                                2699156

                                I would love to answer this but I simply don't get any information about this...

                                I still don't know which version of Oracle it is neither do I know anything about how the original backup was created.

                                 

                                 

                                To update you on this task:
                                I now got send over .ctl files and the binar files from the Oracle Installation. So just a copy of the binar data folder. And I shall asap import these files anyhow...

                                • 14. Re: RMAN - Oracle 11g: only import tablespace not whole database
                                  EdStevens

                                  2699156 wrote:

                                   

                                  I would love to answer this but I simply don't get any information about this...

                                  I still don't know which version of Oracle it is neither do I know anything about how the original backup was created.

                                   

                                   

                                  To update you on this task:
                                  I now got send over .ctl files and the binar files from the Oracle Installation. So just a copy of the binar data folder. And I shall asap import these files anyhow...

                                  I don't recall you saying what OS this is, but in general (and definitely in Windows) you can't just copy in "the binaries".  There is also an Oracle Central Inventory file, environment settings, and in Windows there are also registry keys, all of which have to be correct.

                                   

                                   

                                   

                                  ============================================================================

                                  That's now how it works.

                                   

                                  https://www.youtube.com/watch?v=4FFG1NqKzCg

                                  ============================================================================

                                  1 2 Previous Next