Backup and Recovery

Backing up and recovering data are perhaps the most unrecognized functions of a DBA. These tasks are done daily, quietly , with lots of shuffling of tapes, paper, logs, and so forth. But let the database take a hit, and suddenly the entire world wants to know how good the backup and recovery are: Do we have the latest copy? Can the database be restored? The moral here is never to skip doing your backups ” never, never, never .

You can tackle this very necessary task in several different ways:

• Oracle Enterprise Manager tools , using the Backup and Recovery wizards. These are great tools.

• Recovery Manager ( RMAN ) ” another very good tool, but without the graphical user interface.

• Third-party products , such as Legato, which is actually shipped with Oracle.

• OCOPY , another Oracle utility that is used when you have to quickly back up a tablespace. Oracle provides good documentation on OCOPY; I suggest you refer to it.

Let me give you the lowdown here. Oracle has just begun to ship a relatively decent backup system with 9 i , the new and improved RMAN (Recovery Manager). Up to now, the backup/recovery function has not been one of Oracle's strongest points. Most shops use third-party products or develop an in-house guru who can run RMAN. However, fortunately Oracle products have been evolving. RMAN, for example, is a true command-line utility in 8 and 8 i , but it has become much more streamlined in 9 i . Given this constant flux, I'm going to severely limit our discussion of backup/recovery. My guess is that years from now, historians will call this confusing time the Period of Too Many Backups.

I will quickly cover only one of the major backup and recovery processes: import/export. I'll give you enough information to gain some familiarity with both of these, while not burying you with the incredible number of details that both utilities offer. As you get used to them, I encourage you to look through their manuals and refine your processes. It is amazing how much capability Oracle has built into these processes. And stay tuned ” Oracle is constantly improving its backup and recovery offerings!

Import and Export

IMPORT and EXPORT are a nicely balanced, matched pair of Oracle utilities that let you scoop up an entire database, a table or two, or all objects belonging to a user, and then use what you have to restore objects or create an entirely new database. Hence, IMPORT and EXPORT are used to:

• Provide full backup and recovery capabilities

• Provide the capability to restore a table or other object that is accidentally deleted

• Create test databases

• Move objects from one environment to another

• Defragment tablespaces by exporting them and then importing them back

Just be aware up front that you have to use both of these utilities; the file created by EXPORT can be read only by IMPORT. Otherwise all you need to worry about is whether you have enough space to store the backup file from EXPORT and whether you know the syntax.

(To get a good idea of the size of the database you will be exporting, use the command >select sum(bytes) from user_segments where segment_type = 'TABLE'; and then translate the number of bytes into megabytes to see how much space you'll need.)

The Export Step

As you have probably already guessed, the export step is pretty easy. You start it from your favorite place, the DOS command line. Yes, you can create a parameter file, and we suggest you do so, after you become familiar with the basic commands.

One neat thing about the EXPORT utility is that most of the time it prompts you for the variables if you don't enter them on the first line. The IMPORT utility is not this friendly, as you'll see.

To start, just type in "EXP < user-id/password@database to be copied >" at the C: prompt, as shown here:

This command starts the process.

In the following screen I want to show you that EXPORT cannot create a folder; it must already exist. We told it to put the .dmp file in C:\CWE1P_BACKUP\ , but the folder did not exist, so we had to create it and then come back. Again, I suggest that you be careful in naming your backup files, preferably putting them in separate folders. You also may want to add a date to the file name. In this example we are exporting the database CWE1P only, and we are creating an output file by the same name .

The next prompts are for the entire database, export grants, and export table data. Take the defaults:

Once the export starts, you will see screen after screen showing you the files, objects, and so forth that are being exported to the backup file:

Ultimately what you want to see is the success message at the end:

Best of all, the export file ends up where it is supposed to be:

I imagine you're thinking, "That was easy!" and you're right, it was. All you really need to know is the syntax , and the basic syntax is clear. There is a lot more to the EXPORT utility, though, as you will find out if you go to the Oracle documentation.

The Import Step

As you have probably been thinking, the export step goes pretty easily. But you may also be thinking, "When I go to import the database, will I run into the same constraint considerations that we talked about in Chapters 3, 5, and 9? If I can't control how everything is loaded into the database, and the system tries to load a table that is dependent on another table that has not been loaded yet, will I have a problem?" Yes and No. When you load an entire database, you won't run into integrity constraint problems, because Oracle loads all the tables before loading the integrity constraints. But you're on the right track.

If you're loading data into existing tables, you can run into problems with constraints, and the import will result in rows not being loaded. This can happen because the tables aren't being loaded in the right sequence, so referential integrity kicks in and drops rows. Or you may run into duplicate keys in tables, and these rows will also be dropped.

So if you're loading into existing tables, it is advisable to disable constraints. You can do this simply by adding the CONSTRAINTS = N parameter when you run IMPORT. Once the database has been imported, you can reenable the constraints. To reenable , just use the following command:

 SQL> alter table students   2 enable primary key;  Table altered.  

When would you load just one or several tables? When a table is damaged, or a programmer has deleted items that should not have been, or when an application runs wild and somehow posts incorrectly. Most of the time it will be the programming staff who will call and ask for a restore. Again, it will all come back to your backups!

Again, let's start learning how to use the IMPORT utility by running it from the command line. Once you're good at this, go ahead and create a parameter file, as we did with the EXPORT utility. Here's the basic syntax; it should look familiar:

 >IMP <username/password>@INSTANCE FILE=<export file> FULL=Y 

The following screen shows how to start IMPORT, and it also shows an error because we didn't specify "FULL":

I'm showing you this error to make a point: IMPORT is not as forgiving as EXPORT, and you are better off using a parameter file once you're comfortable with this utility. One good reason to use a parameter file to avoid mistakes is that IMPORT, like EXPORT, cannot create a folder:

Note that if you specify IGNORE = Y , you won't see the dozens of screens with creation warnings such as "already exists." You can go to the log file for those. However, you will see SQL and Oracle errors:

Use DESTROY to replace objects. DESTROY gets rid of any existing objects and is good for "refreshing" a database that will be used for testing and so forth. The danger with DESTROY is that the IMPORT utility will finish successfully even if it doesn't import all rows . It is important to go to the log!

The DESTROY startup may take a couple of minutes because we specified IGNORE = Y , so any CREATE TYPE messages won't show. Notice that any system-created constraints are not ignored:

Here's the last screen:

Finally, always check the log, just in case.

Note

If you are importing into existing tables, turn the constraints off. When you import into a new set of tables, there is no problem because the tables are imported first, then the constraints. If the tables already exist, however, the load can run into problems if the tables are not imported in the proper sequence. That is, if the ANSWERS table comes before the QUESTIONS table in the export, there is the risk that a row or more of data in the ANSWERS table will be rejected because their matching referential integrity constraints in the QUESTIONS table haven't been loaded yet.

Note that the import will not overlie system-owned objects. Here's a quick refresher on how to look up constraints on a table:

 select constraint_name, from all_constraints where table_name like 'QUEST%' CONSTRAINT_NAME             C ----------------------------- SYS_C0021676                C SYS_C0021677                P SYS_C0021679                C SYS_C0021680                C SYS_C0021681                P SYS_C0021682                R SYS_C0021683                R SQL> desc all_constraints; Name                        Null?    Type ------------------------------------------ OWNER                       NOT NULL VARCHAR2(30) CONSTRAINT_NAME             NOT NULL VARCHAR2(30) CONSTRAINT_TYPE             VARCHAR2(1) TABLE_NAME                  NOT NULL VARCHAR2(30) SEARCH_CONDITION                     LONG R_OWNER                              VARCHAR2(30) R_CONSTRAINT_NAME                    VARCHAR2(30) DELETE_RULE                          VARCHAR2(9) STATUS                               VARCHAR2(8) SQL> select * from all_constraints where constraint_name = 'SYS_C0021676'; OWNER             CONSTRAINT_NAME      C TABLE_NAME --------------------------------------------------- SEARCH_CONDITION --------------------------------------------------- R_OWNER           R_CONSTRAINT_NAME      DELETE_RU STATUS ---------------------------------------------------------- SYSTEM            SYS_C0021676         C QUESTIONS_TYPE_DESC QUESTIONS_TYPE_DESC IS NOT NULL ENABLED