Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 26, 2006

Controlling Backups with Database Control

By Steve Callan

The newer version of Oracle Enterprise Manager, aka Database Control, offers an easy way to learn RMAN, Oracle's server managed physical backup utility. The basic or Oracle recommended version of an RMAN backup is useful, but it is the custom backup which exposes more of RMAN's capabilities. The combination of RMAN, use of a flash recovery area, and Database Control makes having a high availability, easy to recover database a breeze to configure and use. Let's take a look at each component and see what role they play in this arrangement.

Database Control

Oracle's Web-based tool for performing administration, tuning, and maintenance, in addition to many other areas, is getting better and better. Consider where Oracle Enterprise Manager (OEM) came from and how database control has evolved. When using OEM, the target audience (in Oracle Corporation's mind) must have been considered to be somewhat dense. Is it really necessary to display the same boilerplate text on 80% of the interface each and every time you start the application? If there were Razzies for the worst user interface, OEM would have to be considered a strong contender. In Oracle 10g, at least on the database "DB_home" installation side of things, the Web interface is much better. Unfortunately, the "Client_home" installation still uses the clunky, slow-starting Java-based OEM.

In line with Application Server and Oracle Applications, the RDBMS interface has adopted the same look and feel. If you have any experience with Application Server, you will feel right at home with Database Control. Some of the navigation and button layout leaves a bit to be desired, and constant displaying of the same text is pointless after you have become familiar with the tool. At least Microsoft allows you to turn off the dog and paperclip, so why can't Oracle include an "expert" version of the interface? Another area to keep an eye on has to do with where links/features exist version to version. The layout between 10.1.0.4 and 10.2.0.1 has several changes.

Recovery Manager

RMAN: it is here and it is now. If you have used Oracle for any length of time and have yet to start using RMAN, you are behind the power curve. If you are new to Oracle, learning RMAN and being proficient in its use will pay high dividends not only on the job, but in terms of getting a job. RMAN is becoming one of the skill sets that you are expected to have. It has been available since 8.0, and it became much better in 9i, and even more so in 10g. The time for resistance is over. As a flagship feature of Oracle, not knowing how to use RMAN is analogous to being an American history major that skipped over learning anything about the Civil War. In other words, you are deficient in your education. That may sound harsh, but it is true.

Running a database in archivelog mode is a prerequisite, and Oracle allows multiple destinations for redo logs to be archived to. Of the ten destinations, and this leads into the next area of flash recovery, RMAN under Database Control uses a location identified by the USE_DB_RECOVERY_FILE_DEST parameter.

Click for larger image

Figure 1. Recovery Area Settings

The Flash Recovery Area

The flash recovery area is defined by the USE_DB_RECOVERY_FILE_DEST parameter, and this area serves a dual purpose. The first is to store a readily available set of "recovery files" used for flashback operations (particularly flashback database). The second is to serve as one of the ten possible LOG_ARCHIVE_DEST_n locations, specifically as LOG_ARCHIVE_DEST_10. "Show parameters dest" in SQL*Plus may show destination 10 as being blank, but it uses the "db_file_recovery_dest" value.

Using flashback is optional, but to use it means being in archivelog mode, and if you are in that mode, you may as well be using RMAN. Two usage notes regarding this arrangement have to do with permissions and space allocation.

The bottom line is that you must get the Database Preferred Credentials settings in order. If on Windows, you may have to grant "Logon as a batch job" (if not already in place) for your user. Running an RMAN script via a scheduled job via Database Control, implying an unattended backup, also implies being able to logon as a batch job, and that ability is verified by Database Preferred Credentials.


Figure 2. Successful Setting of Preferred Credentials

The second cautionary type of tip concerns disk space usage. The flash recovery area can quickly fill up, and when that happens, the instance hangs because Oracle cannot write out an archived redo log to the recovery area location. Figure 3 shows the components of the flash recovery area.


Figure 3. Flash Recovery Area Components

Enable archiving if not already done. Note that some parameters will be flagged as alertable messages during startup, particularly that of LOG_ARCHIVE_START. The simple explanation of enabling archiving in 10g is to startup in a mounted state and issue an alter database archivelog statement. Open the database, and confirm the log mode status:


Figure 4. Database is in Archive Mode

Schedule an Oracle-suggest backup job

From the home page, follow the path from Maintenance to Schedule Backup. On the right side of the page, note the differences between Oracle-Suggested and a customized backup.


Figure 5. Backup Strategies

Accept all of the defaults through the four steps, and at the last page, the RMAN script is presented to you.


Figure 6. An Oracle-suggested RMAN Script

As a comparison, go back (Cancel) and schedule a customized job. One huge difference between the Oracle-suggested script and one you can configure concerns the no-longer needed archived log files. Just like in the command line version of RMAN, you can delete obsolete items, including archived log files.


Figure 7. Setting Customized Options

The resultant RMAN script with these two changes is shown below.


Figure 8. RMAN Script Based on Customized Options

In Closing

From a simple "RMAN> backup database;" command line version, to two Database Control-generated versions of RMAN scripts, you can see the range of options of what RMAN has to offer in terms of script complexity. The GUI interface is a benefit and definitely makes scheduling backups easier to perform. The "backup database" command abstracts much of what is shown below.


Figure 9. Typical RMAN Settings

That abstraction, also performed by Database Control, is a double-edged sword. If the OEM agent is working, Database Control is likely to work, and if your credentials are in order, you can let Database Control do the walking for you. If the Web interface is not available, it is back to the dark ages and doing everything by hand. It really isn't the dark ages, but rather, a necessary skill or set of knowledge to have. If you need to recover using RMAN, and Database Control is not available, will you be left scouring the Internet for examples of how to perform an RMAN-based recovery, or will you know what to do (minus a few easily correctable syntax mistakes)?

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM