Database backup in Oracle 2

[zum]

I'm very new to Oracle so forgive my stupidity. My company use Sybase is currently moving over to Oracle. With Sybase you can to a database dump. Is there something similar in Oracle? What is the command to do a database backup?

Thanks,

 

[jimirvine]

Hi,
there are various ways to backup your db with Oracle. RMAN is the main backup utility, however there is also import/export and datapump, as well as simply copying the os files (along with third party BR tools). The best piece of advice that I can give you however, is to start reading the documentation.
Go to

http://tahiti.oracle.com

and download (you'll need to register first - it's free) the appropriate documentation. I'd recommend that you download the concepts guide, the dba reference and most importantly, the backup and recovery guide. Backup and recovery is not something to approach in a haphazard manner and you really need to do some research first.

Good luck

 

[SantaMufasa]

I'm very new to Oracle so forgive my stupidity.

First, Zum, there is nothing stupid about being new to Oracle. We all were new to Oracle at one point and for a longer period than we cared for, so please don't feel uncomfortable about asking questions...That is what we are here for...to help.


Next, let's ensure that our terminology is consistent. In Sybase, when you refer to a database, we call that collection of tables and other related objects an Oracle user or a schema; In Oracle, the term database refers to the entire collection of all objects owned by all schemas.

Now, when you say...

What is the command to do a database backup?

...I presume that you are talking about backing up a single schema of data using this type of Sybase command:

dump database <database_name> to "<full path/filename>"

If this is what you wanted, this is equivalent to Oracle's "export" utility.


You invoke the Oracle export utility ("exp") from your operating system's command prompt (not from any Oracle software such as SQL*Plus). The easiest method to use Oracle's "exp" utility is to invoke the interactive mode and respond to questions that the "exp" utility poses to you. In fact, in the sample export session, below, after invoking "exp", I responded simply by accepting the default values (by pressing the [Enter] key) except for the last question, "User to be exported: (RETURN to quit) >" to which I responded "ztest":

% exp
Export: Release 9.2.0.4.0 - Production on Fri Jul 27 16:39:32 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Username: dhunt
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
User to be exported: (RETURN to quit) >

Export terminated successfully without warnings.
foster:/corp/home/oracle$ exp

Export: Release 9.2.0.4.0 - Production on Fri Jul 27 16:43:59 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Username: dhunt
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Enter array fetch buffer size: 4096 >

Export file: ./expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
User to be exported: (RETURN to quit) > ztest

User to be exported: (RETURN to quit) >

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZTEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZTEST
About to export ZTEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZTEST's tables via Conventional Path ...
. . exporting table                           DEPT       8098 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     S_CUSTOMER         15 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                         S_DEPT         12 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                          S_EMP         25 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        S_IMAGE         19 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    S_INVENTORY        114 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

Notice in the above session that there are these warnings: "EXP-00091: Exporting questionable statistics." You don't need to worry about this diagnostic.

As with Sybase, when you do this type of a dump, if you restore the dump file, the state of those objects revert back to their state at the moment that you invoked the "exp" command. So using an import ("imp") to restore the dump file is just that: a "restore"...it is not a recovery. (A recovery brings a database right up to the most currently committed transaction; a restore puts the database to its state at the time of the "exp". Database recovery depends upon the database being in ARCHIVELOG mode, and for that you can use Oracle's RMAN [Recovery MANager] facility. That is another topic for another thread.)

You also have the option of running the "exp" utility in command-line mode. A sample of an "exp" command-line that would yield the same results as the above, interactive "exp" (but without the "statistics" warning) is:

exp buffer=15000000 compress=y grants=y feedback=1000 consistent=y file=FOS9204.ZTEST.dump log=ZTEST_Exp.log statistics=none owner=ZTEST userid=ZTEST/ZTEST@FOS9204

In the above example, I specified:

[ul][li]a 15M buffer to speed the export[/li]
[li]feedback=1000, which prints a dot "." every 1000 rows[/li][li]a different output file name,[/li]
[li]statistics=none, which avoids the whole problem with the statistics diagnostic[/li][li]and I performed the export as the owner of the schema (instead of as "DHUNT")[/li][/ul]

If you want a full briefing on these parameters, you can see them on the screen by entering the command:

% exp help=y

Let us know if you have additional questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[zum]

Thanks both for the help.