6 Replies Latest reply: Apr 3, 2013 11:20 AM by jgarry RSS

    Can you please suggest me the steps to take a full database backup

    999417
      Hi,

      I want to take a complete bakup of a databe and move it to another server.
      the size of the database to be backed up is

      Tablespace Size (GB) Free (GB)
      UNDOTBS1 ,625 ,600280762
      USERS 29,3017578 5,17828369
      SYSAUX ,634765625 ,039672852
      TEMP 2,02636719 0
      SYSTEM 2,08007813 ,001220703

      would the below comands be sufficient?
      exp system/manager@xyz FULL=Y FILE=FULL.DMP log=full_log.log and to import
      imp system/manager@abc FULL=Y FILE=FULL.DMP log=full_log.log

      i just wanted to know is there any other things that should be taken care of.
      while exporting and importing schema level for the previous migration that i did i created the schema in the new database, and had to give permissions for tht and also gave the assigned the tablespaces to those.
      " create user USERNAME identified by USERNAME
      grant connect, resource, create view to USERNAME
      alter user USERNAME default tablespace users temporary tablespace temp; "

      Is there anything like this to be done while doing a full database export/import?

      Thanks,


      Edited by: 996414 on Mar 26, 2013 11:02 PM

      Edited by: 996414 on Mar 28, 2013 4:12 AM
        • 1. Re: Can you please suggest me the steps to take a full database backup
          Srini Chavali-Oracle
          Pl post exact database version and source/target OS versions. Is there a reason you cannot use RMAN ? If the OS versions are identical, you could also simply shut down the database and copy over all the database related files and start up the database on the target

          HTH
          Srini
          • 2. Re: Can you please suggest me the steps to take a full database backup
            ragu.dba.in
            Hi,

            why not datapump? it is faster...
            expdp system/manager@xyz FULL=Y DUMPFILE=FULL.DMP log=full_log.log

            FULL.DMP is stored in default directory data_pump_dir location.copy to target server's data_pump_dir location .
            CREATE FOLDER STRUCTURE IN THE TARGET SERVER SAME AS IN SOURCE SERVER.

            impdp system/manager@abc FULL=Y DUMPFILE=FULL.DMP log=full_log.log

            all would be taken care.no need to create schema,tablespace,grants etc...

            regards,
            ragunath

            Edited by: ragu.dba.in on Mar 27, 2013 10:45 PM
            • 3. Re: Can you please suggest me the steps to take a full database backup
              999417
              Hi,

              Thanx for your reply..

              The source version of oracle is 10.2.0.3.0, the target version of oracle is 11.2.0.2.0
              The target server is a fresh one and oracle client is yet to be installed.
              RMAN is not used since it has not been configuerd yet, it would be implemented in the near soon.

              Thanks,

              Edited by: 996414 on Mar 28, 2013 4:12 AM

              Edited by: 996414 on Mar 28, 2013 4:13 AM
              • 4. Re: Can you please suggest me the steps to take a full database backup
                999417
                Hi ragu,

                Thanx fro your response.

                I am using windows platform.
                i tried the expdp command but it wasant workin in the cmd prompt.
                I could take the backups only with exp command.. not remembering what the error i got was.

                Could u please explain if possible on wht u meant by "creating the folder structure in the target server same as in sourse server"
                I am actually new to DBA field, so just trying to pick up things only.. :(

                Thanks in advance,

                Edited by: 996414 on Mar 28, 2013 4:13 AM
                • 5. Re: Can you please suggest me the steps to take a full database backup
                  Dean Gagne
                  I'm not trying to be rude here, but if you are asking questions, it would be nice if you knew some information.

                  The right way to backup a database is with rman. Neither export tool is the right tool for backing up. If you are migrating, then export/import may be the right tool. You also said that Data PUmp didn't work, but you said you don't have the answer why. The original, old, and deprecated exp toll may get some of your objects, but may not. Support for new features may or may not be supported by the exp tool. The right tool to use would be expdp.

                  You also say that the oracle client is not installed. Do you mean rman client, Data Pump client? or something else. If you are missing the appropriate client for the tool you use to extract data, then my guess is that you won't be able to load the data.

                  I think there are just too many unknowns for a good answer here.

                  My suggestion for a backup would be to use rman. This is probably the most complete way to back up a database.

                  If you are doing a database migration, then you can look at many things, but since I am partial to Data Pump, I would look at using the Data Pump tools expdp and impdp.

                  Other people may suggest other solutions.

                  Hope this helps.

                  Dean
                  • 6. Re: Can you please suggest me the steps to take a full database backup
                    jgarry
                    ragu.dba.in wrote:
                    Hi,

                    why not datapump? it is faster...
                    Just a nitpick, on my system expdp takes 1 hour 15 minutes, while exp DIRECT=Y takes 1 hour 3 minutes. YMMV. On some systems and versions, metadata processing can make a big difference.
                    expdp system/manager@xyz FULL=Y DUMPFILE=FULL.DMP log=full_log.log

                    FULL.DMP is stored in default directory data_pump_dir location.copy to target server's data_pump_dir location .
                    CREATE FOLDER STRUCTURE IN THE TARGET SERVER SAME AS IN SOURCE SERVER.

                    impdp system/manager@abc FULL=Y DUMPFILE=FULL.DMP log=full_log.log

                    all would be taken care.no need to create schema,tablespace,grants etc...
                    Maybe. Maybe not. There are some dependencies which sometimes come up. It's worth a try, needs to be tested.