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 May 24, 2007

Oracle Secure Backup, Part 3: Conclusion

By Jim Czuprynski

Synopsis. With Oracle Secure Backup, Oracle now offers the capability to insure that all Recovery Manager (RMAN) backups are created and maintained in a secure fashion, all without the need for a potentially expensive and cumbersome media management layer (MML). This article – the final one in this series – shows how to use an Oracle Secure Backup (OSB) configuration to perform Oracle 10gR2 Recovery Manager (RMAN) restore and recovery operations against previously-created RMAN backups stored on simulated OSB virtual libraries and tape devices. It also demonstrates how OSB can be used for backup and restoration of operating system files.

The previous article in this series provided a simple example of how to set up the Oracle Secure Backup (OSB) environment, how to create Oracle 10gR2 Recovery Manager (RMAN) backup sets directly on an OSB virtual tape drive, and how to use the RMAN BACKUP RECOVERY AREA; command to back up the contents of an Oracle 10gR2 database’s Flash Recovery Area directly to an OSB virtual tape library.

Of course, preparing an alternate media backup set is just half the battle! As those of us who have participated in a “cold metal” disaster recovery exercise can attest, tape backups are useless unless they can be restored directly from an alternate media source and then used to recover an Oracle 10gR2 database’s control files, archived redo logs, and datafiles. I’ll next demonstrate how simple it is to restore files from OSB backups to their appropriate destinations as part of a simulated RMAN restore and recovery operation.

Performing RMAN Restore and Recovery Operations with OSB Backups

I’ll use the database image copies that were created directly on simulated OSB alternate media during the demonstrations in the prior article as the source for my datafile restoration attempts. Since I also created OSB backups of the image copies in my database’s Flash Recovery Area (FRA), I’ve first deleted those backups from the FRA so that RMAN would not consider them as candidates for restoration. Listing 3.1 shows the commands I executed to clean up these backup sets.

Now that RMAN will correctly recognize just the backup sets on the OSB virtual tape drive, I’ll simulate the cold-metal restoration of two datasets. First, I’ll prepare for the simulation by deleting the corresponding datasets for my database’s SYSTEM and USERS tablespaces, and then force a shutdown of the database by killing its SMON background process. As Listing 3.2 shows, the database cannot be opened until both datasets have been restored and recovered.

Since the SYSTEM tablespace’s datafile has been destroyed, I’ll need to restore and recover it first. (Remember that the corruption or destruction of either the SYSTEM or UNDO tablespaces requires a shutdown and restart of the database before restoration and recovery of these tablespaces may proceed.) Once that’s complete, I’ll proceed to restore and recover the USERS tablespace. Listing 3.3 shows the results of these successful RMAN operations. Note that RMAN immediately identifies the required datafiles on the virtual OSB tape drive and performs their restoration without any additional DBA interaction.

Creating OSB Backups of Operating System Files

While these RMAN backup and restore features are already quite impressive, OSB also offers the capability to back up operating system files stored anywhere on an identified host server to alternate media devices. Though DBAs are typically much more interested in creating tape backups of their Oracle 10gR2 database files, the array of file types that need to be backed up (and restored!) has expanded to include DataPump Import and Export dump sets and the “flat files” that comprise the raw data that’s read by Oracle external tables. Moreover, beginning in Oracle 10gR2, it’s now possible to create external tables and their corresponding datasets via the CREATE TABLE … AS SELECT command (normally assigned the affectionate acronym of CTAS) when it’s used with the ORACLE_DATAPUMP access method.

To prepare for this demonstration, I’ve used Oracle DataPump Export to create a dumpset that contains approximately 45MB of exported data in a directory named /home/oracle/expfiles. I’ve also invoked a CTAS command against the SH.SALES sample table. Since I’ve specified the ORACLE_DATAPUMP access method, Oracle 10gR2 will write out the external table’s datasets and generate logging files in a directory named /home/oracle/extfiles. Listing 3.4 shows the code I executed to complete these tasks.

Creating an OSB Dataset. Before I can create an OSB backup of OS files, I’ll need to define an OSB dataset. I’ll use obtool’s mkds command set:

#> obtool --user admin --password oracle < /home/oracle/mkds.obp

Here’s the contents of mkds.obp:

mkds --input exportdir.ds
include host 10gBUR {
    include path /home/oracle/expfiles
    include path /home/oracle/extfiles
}
.
yes

To break down this command and its corresponding parameter file’s syntax:

  • --user and --password: This directory is created for the OSB admin user with the appropriate password. Note that the admin account is specific to OSB – it is not the same as a user account that might exist in a corresponding Oracle database.
  • --input <dataset name>: Creates an OSB dataset named exportdir.ds that describes what should be included within, or excluded from, the resulting OS file backup.
  • exclude name: Specifies which OS files should be excluded from the OSB backup set. In this example, any file that has an extension of .log will be excluded.
  • include host <hostname>: Tells OSB which host is the target of the backup operation.
  • include path <pathname>: Tells OSB which OS file paths (i.e. directories or folders) should be included in the OSB backup set.

(By the way, the last two lines of the parameter file – the period (.) and the word yes – are used to reply to prompts that the OSB mkds command set signals to the console.)

Creating an OSB OS File System Backup. Now that the OSB dataset is created, I’m ready to create an OSB backup set that contains all of the files specified within the OSB dataset. To accomplish this, I’ll use obtool’s backup command set:

#> obtool --user admin --password oracle < /home/oracle/bkupds.obp

Here’s the contents of bkupds.obp:

backup -l full -p 100 -g -D exportdir.ds
backup --go

Here’s a breakdown of the first line in this parameter file, which actually schedules this backup task as a one-time OSB job:

  • -l full: Creates a complete OSB backup of the file(s) specified by the dataset. This is the default behavior; however, this parameter can also be used to specify different levels of incremental backups, from one (1) to nine (9). Incremental backups are differential by nature, i.e. a Level 2 OSB backup will back up only those OS files that have been modified since the last OSB backup made at Level 2 or lower.
  • -p 100: Specifies that OSB should assign this operation a priority of 100, the highest priority (and the default).
  • -D <dataset name>: Tells OSB which dataset(s) should be backed up during this operation.
  • -g: Tells OSB that the backup should be created by a privileged user. In this case, this means that the OSB admin user account will be responsible for performing the operation.

The second line in this parameter file actually starts the OSB file system backup because it contains the --go: directive, which tells OSB to start any scheduled backups.

Viewing the Results of OSB Backup and Restore Operations

Once I’ve scheduled and initiated the one-time backup of these OS files, what’s the best way to determine if the backup operation has been successful? The good news is that OSB provides several simple methods to list what backup and restore tasks have completed, as well as detailed transcripts for each OSB operation:

  • When issued from within obtool, the lsjob –c command shows the status of every OSB job that has been scheduled and has already completed executing. The –c directive tells OSB to list all jobs regardless of whether the job has completed successfully.
  • The catxcr <job name> command displays a detailed transcript for the specified OSB job, regardless of the job’s successful completion or utter failure. This command can also be used to pipe a job’s transcript directly to an operating system file for further review; by default, the transcript is written directly to the console.

I’ve provided samples of these two commands, as well as the output they produce, in Listing 3.5.

Restoring OSB Backups of Operating System Files

Now that I’ve completed backing up the contents of the two export file directories listed in the exportfiles.ds OSB dataset specification, I’ll simulate the loss of these files (by deleting them from each directory with the rm –f command) and then I’ll attempt to restore them to their original location on the OS file system. Note that since I excluded any files that had a file extension of *.log in the /home/oracle/extfiles directory, I expect that only two of the original five files in that directory should be restored.

Restoring OS files from OSB Backup Sets. To perform a one-time restore of the files from the most recent OSB backup, I’ll use the OSB restore command set. This time, I’ll actually invoke the commands one by one from within the obtool command console:

ob> restore -h 10gBUR -d vt -G -e -p 100 -s latest /home/oracle/expfiles
ob> restore -h 10gBUR -d vt -G -e -p 100 -s latest /home/oracle/extfiles

Here’s how OSB interprets the parameters in these two commands:

  • -h <hostname>: Tells OSB that the files should be restored to the specified host.
  • -d <device>: Directs OSB to use the vt virtual tape device to locate the appropriate backup set.
  • -G: Tells OSB that the backup files may be restored by a non-privileged user.
  • -e: Informs OSB that existing files may be overwritten.
  • -p 100: Specifies that OSB should assign this operation a priority of 100, the highest priority (and the default).
  • -s <backupset>: Tells OSB to select from the specified backup set – in this case, to use the most recent (i.e. latest) available copy of these OS files.
  • <directory>: Tells OSB which directory should be used as a target on specified host for a recovery operation.

To see what restoration jobs have been submitted, I’ll issue the lsrestore command:

ob> lsrestore
Item     Restore data saved from...          To...
  #      Host        Path                    Host        Path
1        10gBUR      /home/oracle/expfiles   10gBUR      (original location)
2        10gBUR      /home/oracle/extfiles   10gBUR      (original location)

Finally, I’ll start the OSB file system restoration activities with the restore –go command:

ob> restore -go
Info: 2 catalog restore request items submitted; job id is admin/9.

To prove that the files have been successfully restored, I’ve listed the contents of the directories after the restoration job has completed in Listing 3.6. Note that as expected, the /home/oracle/extfiles directory now omits all but two of the original five files in that directory.

Conclusion

Oracle Secure Backup (OSB) is relatively simple to configure via either the obtool command line tool, the OSB web tool, or Enterprise Manager. OSB permits the creation of virtual tape libraries and virtual tape drives so that tape backup methods and backup scripts can be evaluated without an initial purchase of expensive tape libraries or network appliances. Once it’s initially configured, OSB integrates seamlessly into existing Oracle 10gR2 Recovery Manager (RMAN) backup scripts that require alternate media targets to implement a comprehensive disaster recovery scheme for Oracle 10gR2 database files. Finally, OSB provides robust backup and recovery methods for operating system datasets, including the capability to create delta versions of these datasets.

References and Additional Reading

Even though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:

B14194-03 Oracle Backup and Recovery Reference

B14234-02 Oracle Secure Backup Administrator’s Guide

B14235-05 Oracle Secure Backup Installation Guide

B14236-02 Oracle Secure Backup Reference

B25049-01 Oracle Secure Backup Migration Guide

B32120-01 Oracle Secure Backup ReadMe

And don’t forget that the Oracle Technology Network (OTN) Oracle Secure Backup home page is an excellent source of valuable (and constantly updated!) information.

» See All Articles by Columnist Jim Czuprynski



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


















Thanks for your registration, follow us on our social networks to keep up-to-date