RMAN and Overall Database Integrity by the36chambers

VIEWS: 119 PAGES: 10

									RMAN AND OVERALL DATABASE INTEGRITY
Raj Pal, EchoStar Satellite LLC Abstract
Recovery Manager (RMAN) is an integral part of ensuring today's Oracle database integrity. There are many options that will restore and recover the Oracle database to a consistent state for you, but they do not offer proactive corruption checking and ease of recovery from it. This presentation tells the story of RMAN's acceptance at one location from a nearly unrecoverable disaster without RMAN to a nearly perfect recovery a year later with it. If your present, non-RMAN, backup system is not working, or budget constraints are preventing your database from being backed up in the “best” way possible, the pros and cons of an alternative using RMAN will be discussed and the simple steps reviewed.

Caveat
Although RMAN is a fantastic, important and necessary utility for corruption checking, restoring, recovering, duplicating, etc… it is not, especially for large databases, a complete solution for high-availability of high visibility/importance database instances.

Before and After RMAN Story…
Example of a Disaster and an Almost Unrecoverable Database…
  A database grows past 3 terabytes (Tb), 4000 datafiles and 200 tablespaces. The combination of high number of datafiles and/or tablespaces and/or activity makes placing a database in „hot backup mode‟ an impossibility. The result is an ORA-235 error
"controlfile fixed table inconsistent due to concurrent update" *Cause: Concurrent update activity on a controlfile caused a query on a controlfile fixed table to read inconsistent information.



The existing backup method was to place the database in „hot backup mode‟, performing a business continuance volume (BCV) split which in turn would be backed up. Due to the inability to place the database in “hot backup mode”, this backup method was no longer effective. RMAN had never been used here before. The database went without a valid backup for 3 weeks though many attempts were made. The best backup gathered was a set of individual data tablespace (not index tablespace) backups gathered by placing one tablespace in “hotbackup mode” at a time over a 3-4 day period. Ironically, the night of the first attempt at implementing RMAN as a backup solution for this database, efforts were stopped when connectivity to the instance suddenly disappeared. Apparently, at that time, a rain/lightening storm caused a leak in the roof over the datacenter causing damage to several circuit boards of the uninterruptible power supply (UPS). Since public-power and batteries/generator feed the UPS, the result was a total power loss. Over 400 production machines, some of which hosted over 30 Oracle databases, crashed. Several damaged circuit boards in the UPS were replaced and all databases were brought back without incident, just time wasted. Jokes were made about “the database without the backup” that had to undergo crash recovery but succeeded. Hours later, other circuit boards were found to be damaged and in an attempt to fix them, the UPS was accidentally taken down. Teams were called back in to ensure database integrity… again. This time, three databases would not open due to physical corruption. Two databases shared a volume group for their mountpoints. There was some sort of corruption on the volume group due to improper crash handling of a 3rd party disk level replication utility. Since the corruption‟s origin at the time was unknown, the safer decision was made to restore the entire database and recover until just prior to the second power outage. These restores were from tape and recovered successfully. The third database was “the database without a backup” and was not as fortunate. Only one block was found to be corrupt in a system datafile. This is suspected to also have been caused by the same 3rd party replication utility at crash time. Restoration of the 3 week old backup and replaying all the redo logs, although possible, was not a viable option since ~1.5Tb of redo was estimated to take over 8 days.

  

  

 

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity 

PAL

Oracle support was immediately called with a severity 1 issue and every location that we were transferred to throughout this ordeal asked repeatedly why we were not using RMAN. After many different joint attempts with Oracle support and consultants the database was forced open using the hidden init parameter, “_corrupt_blocks_on_stuck_recovery”. After the forced open, tablespaces were transported to a newly created shell database. This was not a short procedure for a >3Tb database. The procedure succeeded! However, due to limited space availability, a system datafile for the new shell database was created in a non-standard place. Minutes after completion, and before a backup of any kind could be attempted, a cleanup effort was made to free up the large amounts of disk that had to be allocated for the different restore efforts that were going on. In this cleanup effort, that new system datafile was accidentally deleted. The database was now hung and unrecoverable. Since it was a system datafile that was deleted, the database could not be forced open again (no functional system tablespace) for another transportable tablespace attempt. The database was lost. Luckily, the individual data tablespace backups were still available and with much effort, they were laid down and recovered over several days worth of archived logs. This was also a very timely process since this database generates ~200 gigabytes (Gb) of redo per day. Once complete, all the index tablespaces for these data tablespaces had to be recreated (since only the data tablespaces were backed up). The entire downtime for this database after the second power outage, working around the clock, was about 7 or 8 days! A cold file system backup to tape was taken immediately. The first successful hot RMAN backup for this database was completed within a few days after the recovery from this disaster‟s disaster. It was also implemented for other production databases soon after.

 

 



   

How RMAN could have minimized this disaster’s disaster…
There were many mistakes made in this situation and the odds were not on our side. But here are the major lessons learned: Mistake #1 Always get a backup. It is hopefully obvious, after this example, that lack of a current and reliable backup is never a good thing. In hindsight, the database should have been taken down weekly at a minimum and backed up cold. Although business pressure did not allow for this, a real-life example included in an impact analysis may not have been given to them in their terms. If you find yourself in a similar situation, take into consideration the amount of time to restore and recover from the last backup (better yet, to two backups ago in case a tape is lost, etc…) until the present point in time. Before that total time becomes unacceptable, get a backup before it‟s too late. Mistake #2 Regardless of the fact that RMAN doesn‟t require a database to be in “hot backup mode”, so initial issue of inability to take a backup would not have occurred: If the 3 week old backup had been a RMAN backup, the corrupt block (one was all it took) of the system datafile could have been recovered using block recovery. There would have been a downtime as the database could only have been mounted, but 3 weeks worth of archived logs would only have had to have been applied to one 16 kilobytes (Kb) block versus the entire ~3Tb database. Mistake #3 Whenever an issue occurs, a best-practice is to backup the affected database in its “bad state” before continuing. Not only does this prevent the DBA from making things worse, it also permits time to think out a good set of plans for action. To have taken a cold backup of this >3Tb database before attempting to transport the tablespaces would have taken over 15 hours. At the time, management considered an extra 15 hours to be a waste of time in getting the database reopened, so it was not done. If it was, the transportable tablespace effort could have been reattempted after the system datafile was deleted. Since it was not, the old, corrupt system datafile could not be reused since it no longer contained the metadata for the tablespaces. In hindsight, the 15hours (and additional 15hours to lay it back down from tape to disk) would not have been time wasted. Mistake #4

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity

PAL

Once the transportable tablespace recovery had succeeded, even if the legacy “hot backup mode” backup to tape would have worked, it was hard-coded to only backup files in the standard locations. Therefore the system datafile would not have been backed up. RMAN, on the other hand, backs up files as they appear in v$datafile. All datafiles, including the non-standard location of the new system datafile, would have been backed up in the set.

One year later…
 Over a year later on an unrelated, and larger (>4.5Tb), Siebel fronted Oracle database, a core resynchronization was accidentally executed against a null repository and subsequently began dropping objects in the database leaving it functionally corrupt to the application. A full database restore and recovery to before the start of the core resynchronization was necessary using a RMAN backup. Restoration of the 4.5 Tb of data using RMAN took 4.5hrs (~1Tb/hr), recovery for ~100Gb of RMAN backed up redo took ~11hrs (~9Gb/hr).

  

Nobody is saying that there would have been issues without the use of RMAN. But when comparing the drastic events and lessons learned from the previous year, this was a welcome relief. Conclusion RMAN fantastically minimizes the chances of being able to restore a database. It just doesn‟t do it very quickly (~15hr downtime, once DBAs were notified, in the second example). FYI The database formerly known as the “database without a backup” is still successfully being backed up today using RMAN and has grown to a size of ~6.2Tb (takes ~6.5hrs).

RMAN in a Rush
Should you find yourself in a similar situation where you cannot backup a database using your non-RMAN way for whatever reason, and/or want or need to kick one off using RMAN without much planning time, here is an overview of what you will need, and need to do. Assumptions   Business requires database instance must remain open and functional. You have a means of backing up static file system files to tape.

 9i or above (just due to the way the syntax is listed below; 8i will require some minor changes) Requirements     Front end application files, Oracle binaries, initialization files, tnsnames, etc… all are backed up already somehow. Essentially, this overview only provides details for the core database components. “sys” privileges or the rman role is granted to your user. Database instance is in archivelog mode. Sufficient amount of RMAN disk is available RMAN disk definition: Disk used to directly backup the database‟s RMAN backupsets to. Amount (sum of the following):  Datafiles (level0 RMAN backup will back up your datafiles) select sum(bytes)/1024/1024/1024 Gb from dba_data_files; This provides a little more room than you will need for one backup since RMAN only backs up previously used blocks within a datafile (better too much than too little!). Two backups are necessary at an absolute minimum since you should not delete the first backup from disk until you have successfully backed up the second. Three times the size of the database on disk would be better, allowing you to keep 2 complete backups on disk at all times. So increase the amount by factor 2 at a minimum.

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity 

PAL

Controlfiles (controlfile contains the RMAN metadata of the backups, you will need this if your current controlfile is lost). Plan on at least 2 copies of your controlfile. Size can be obtained from the operating system. Keep in mind that they will grow when RMAN metadata is included. Files can be found in the initialization file or by querying the database: select name from v$controlfile; Redo (archived redo logs are necessary to bring the database to a consistent state across all datafiles in order for you to recover to an „openable‟ state. You will need enough space for archived redo logs for the duration of the backup. In the example above, ~1Tb/hour was witnessed for backup time. Your architecture may limit or be better than this speed. Naturally… estimate up for space. If you can safely assume that your backup will take less than 1 day, this syntax will provide you the average amount of redo in Gb generated historically per day over the last 90 days: select avg(sum(blocks*block_size)/(1024*1024*1024)) Gb from v$archived_log where first_time > (sysdate - 90) group by trunc(completion_time); However, if this is more than a one time thing, you will need enough space to hold archived logs for the days between your level0 backups. Estimate up since you may have high activity days.



Tasks In the syntax examples below, substitute the <?> with values relevant to your environment. Setup environment   Mount RMAN disk for datafiles, archived logs and controlfiles (best to keep them separated). Eg.     /RMAN/<SID>_ARCH /RMAN/<SID>_DATA /RMAN/<SID>_CONTROL

Set init parameters: control_file_record_keep_time tells the instance how long (days) to keep backup information in the reusable portion of controlfile. This parameter can be set while the database is open.
alter system set control_file_record_keep_time = 30;

archive_lag_target assists in minimizing the amount of data that can be lost if recovery is necessary. Set this value to the maximum time (seconds) before a log switch will occur. Too large is ineffective, too small could affect performance. This parameter can be set while the database is open.
alter system set archive_lag_target = 900;

Backup archived logs for the first time (assumption is that the instance has passed archived log sequence #1!).   Ensure that other backup systems are not removing archived logs. Invoke RMAN utility (simplest form)
$ export ORACLE_SID=<SID> $ export ORACLE_HOME=<SID_HOME> $ rman target / nocatalog



Execute backup (this will ignore any pre-configured RMAN settings). This will remove archived logs from their archive destination. “skip inaccessible” is used here to force RMAN to ignore all archived logs that the database has created previously. Without it, you would need to restore and have RMAN backup all archived logs from sequence# 1 until present – unnecessary. But be sure that the archived logs are not being removed by another procedure or system, because RMAN will not consider it an error. If this occurs, the missing files may

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity

PAL

go unnoticed until you need them for a recovery, in which case you will have to restore them from the other procedure/system!
run { allocate channel a1 type disk format '/RMAN/<SID>_ARCH/%d_arch_%s_%p_%c_%t.rmn'; backup skip inaccessible archivelog all tag 'manual_arch' filesperset 1 delete input; }

Set regular cron job to backup archived logs (same RMAN syntax as above) until first level0 backup is complete. This is really only necessary if the database is large and/or the archive log destination space is limited.
$ vi /RMAN/SCRIPTS/arch_temp.ksh #!/bin/ksh export ORACLE_SID=<SID> export ORACLE_HOME=<SID_HOME> rman target / nocatalog <<DONE run { allocate channel a1 type disk format '/RMAN/<SID>_ARCH/%d_arch_%s_%p_%c_%t.rmn'; backup skip inaccessible archivelog all tag 'arch_skipping_inaccessible' filesperset 1 delete input; } DONE $ chmod 700 /RMAN/SCRIPTS/arch_temp.ksh $ crontab –e 0 * * * * /RMAN/SCRIPTS/arch_temp.ksh

Initiate first level0 backup – Note that the “…” and “<n>” are dependent on the size of your database. It may be easiest to simply have one channel per CPU on the host. It would be best to run this in cron or the like, so that if your session is accidentally terminated, the backup will not be affected.
$ rman target / nocatalog run { allocate channel b1 type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; allocate channel b2 type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; … allocate channel b<n> type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; sql 'alter system archive log current'; backup incremental level=0 tag ='level0' check logical database; sql 'alter system switch logfile'; copy current controlfile to '/RMAN/<SID>_CONTROL/<SID>_level0_<date>.ctl'; }

Validate archive log backups. This forces the instance to ensure that it has all the archived logs that it needs to restore the level0 backup just taken, without the need for the archived logs created before the level0 was started.
$ rman target / nocatalog run { allocate channel v1 type disk; allocate channel v2 type disk; … allocate channel v<n> type disk; change archivelog all validate; }

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity

PAL

Set regular cron jobs for level1, level0 and archive backups without skipping errors Always try to find a way to test your backup by restoring, recovering and opening the database on a different host. Along with the Oracle binaries, OS files, and directory structure, the backupsets and controlfile copies will have to be copied to the new host and appear as they did on the original host. For regular level0 backups, automate to get a before and after time stamp and include an archivelog backup following the level0‟s completion. A simple query can then be made to determine what backupset files and controlfile copies are necessary for a restore/recovery.
$ vi /RMAN/SCRIPTS/level0.ksh #!/bin/ksh export ORACLE_SID=<SID> export ORACLE_HOME=<SID_HOME> echo “before_time: `date +"%Y%m%d%H%M%S"`” rman target / nocatalog <<DONE run { allocate channel b1 type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; allocate channel b2 type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; … allocate channel b<n> type disk format '/RMAN/<SID>_DATA/%d_%s_%p_%c_%t_level0.rmn'; sql 'alter system archive log current'; backup incremental level=0 tag ='level0' check logical database; sql 'alter system switch logfile'; copy current controlfile to '/RMAN/<SID>_CONTROL/<SID>_level0_<date>.ctl'; release channel b1; release channel b2; … release channel b<n>; allocate channel a1 type disk format '/RMAN/<SID>_ARCH/%d_arch_%s_%p_%c_%t.rmn'; allocate channel a2 type disk format '/RMAN/<SID>_ARCH/%d_arch_%s_%p_%c_%t.rmn'; … allocate channel a<n> type disk format '/RMAN/<SID>_ARCH/%d_arch_%s_%p_%c_%t.rmn'; backup archivelog all tag 'arch_with_level0' filesperset 1 delete input; } DONE echo “after_time: `date +"%Y%m%d%H%M%S"`” $ /RMAN/SCRIPTS/level0.ksh > level0.log 2>&1 $ sqlplus '/as sysdba' #assuming ORACLE_HOME & ORACLE_SID are set SQL> select handle from v$backup_piece where completion_time between to_date('<before_time>','YYYYMMDDHH24MISS') and to_date('<after_time>','YYYYMMDDHH24MISS');

Take a tape backup of the static backupsets. Always keep an absolute minimum of one backup on disk at all times, therefore space for two is necessary. Don‟t delete the first until the second has completed successfully. It is much better to keep two at all times, therefore space for three level0 backups. If taking advantage of level1+ incremental backups, do not remove the level0 until you have at least one more completed successfully. Remove the oldest level0 and associated level1+ backups from disk just prior to taking a new one to maximize availability and conserve space. Pros

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity

PAL

You are backing up with RMAN!! Therefore you get all the benefits that come along with it (ie. Corruption checking, block level restorability, etc…) This implementation scheme bypasses the need for an Oracle Agent license from your tape backup vendor. Backups and restores from disk are much faster than tape backups. The tablespaces do not need to be in „hot backup mode‟ for a RMAN backup. RMAN utility is included with all standard and enterprise Oracle licenses. Cons Your controlfile (or RMAN catalog database) will not have visibility to backup-sets that are backed up to tape and then deleted from the “RMAN disk”. Should these backup-sets on tape be necessary for a restore, they will first have to be restored from tape to the exact (absolute path location) locations that RMAN backed them up to. Then RMAN will have to restore from the backup-sets to datafiles. This is essentially double the time! If this option is chosen, ideally keep backups you will need on disk so that they are readily available. Possible solutions to this include the purchase the Oracle Agent for RMAN from your tape backup vendor. With the agent you can either:  Utilize RMAN to perform the backup of backupsets from disk to tape. That way, in a restore/recovery situation, the controlfile or RMAN catalog database “knows” where the nearest copy of the backupset is – either on disk or on tape. OR (and much more efficient) change strategies and use RMAN syntax to backup directly to tape. Then use a configuration such as Veritas‟ Disk Storage Staging Unit (DSSU). RMAN will think that it is backing up to tape but control will really be given to (in this example) NetBackup to write to disk. The disk backupsets will be migrated to tape on a separate schedule. The controlfile or RMAN catalog will simply query the NetBackup catalog for restore requests. NetBackup satisfies the request from disk if the backupsets still exist there, otherwise it restores from the necessary tapes.



Proactive Corruption Management using RMAN Without Budget
Consider a database instance without RMAN disk available and no licensed tape backup vendor Oracle agent licenses available. This database is either being backed up to tape with tablespaces in „hot backup mode‟ or backed up by exports. You are worried about corruption, but aren‟t given the budget to backup to tape with RMAN or to disk using RMAN. One set of options would be to use utilities such as DBVerify, but that would have to be scripted to loop through all datafiles and scan output. However, DBVerify was more designed to validate backup images, rather than live database files. So it tends to kick out many “false positives” due to the activity on live database files. Researching many alerts, only to find them to be false, will eventually lead to a true error getting missed. The verification functionality in RMAN does not generate false positives. Or, you could use RMAN which is much more intimately tied to the database. The validate option in RMAN‟s incremental syntax allows the RMAN utility to follow through with each block‟s backup as it would in a normal RMAN backup, but does not write anywhere. Following a level0 validate, the instance can be queried for corruption.
$ vi /RMAN/SCRIPTS/level0_validate.ksh #!/bin/ksh export ORACLE_SID=<SID> export ORACLE_HOME=<SID_HOME> echo “before_time: `date +"%Y%m%d%H%M%S"`” rman target / nocatalog <<DONE run { allocate channel b1 type disk; allocate channel b2 type disk; … allocate channel b<n> type disk; backup incremental level=0 tag ='level0_validate' check logical

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity
validate database; } DONE echo “after_time: `date+"%Y%m%d%H%M%S"`” $ /RMAN/SCRIPTS/level0_validate.ksh > level0_validate.log 2>&1 $ sqlplus '/as sysdba' #assuming ORACLE_HOME & ORACLE_SID are set SQL> select distinct c.file# "File#",c.block# "Starting Block#", c.blocks "Range" from v$backup_corruption c, v$backup_set s where c.set_stamp = s.set_stamp and c.set_count = s.set_count and s.start_time > to_date('<before_time>', 'YYYYMMDDHH24MISS') order by 1,2 /

PAL

If corruption is found, this RMAN script will abort on the first block it finds. So it will be necessary to modify the script for each datafile while allowing for the script to continue with corruption. This script should ideally be dynamically generated to retrieve the file numbers.
run { allocate channel b1 type disk; allocate channel b2 type disk; … allocate channel b<n> type disk; set maxcorrupt for datafile 1 to 1000; set maxcorrupt for datafile 2 to 1000; … set maxcorrupt for datafile <n> to 1000; backup incremental level=0 tag ='level0_validate' check logical validate datafile 1 ,2 … ,<n> ; }

Pros Once again, you are backing up with RMAN!! In this system you get the benefit of proactive corruption checking. Cons If corruption is found, block recovery is not possible (assuming no previous rman level0 within a reasonable amount of time). Tablespace downtime or data loss (marking corrupt and “fixing”) will be necessary at a minimum to restore the affected datafile and recover to point in time.

Database Rate of Change
High availability technologies that track block level changes require you to be able to forecast the block level rate of change for intervals. Utilities such as Statspack can provide block write activity. However, if the same block was written to 10 times from time0 to time1, Statspack will reveal 10 writes, where the space needed for these high availability technologies for that given time period will only be the one block. To more accurately measure the block level rate of change for a particular database, RMAN can be used.

High Level
   Time0: level0 backup. Time1: Validate level1 (for change since level0). Time<n>: Validate level1 (for change since level0).

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity 

PAL

Query database for results of block change per time period.

Requirements
If RMAN is already being used, a regular level0 backup can be used for this test. If RMAN is not used, and space is not available, these instructions will use very little space and give the same metrics.    Duration between level1's must be longer than the time it takes to take the level1 backup. OS resource overhead. Space - (Max datafile size)*(4)*(#channels).     “4” ; since the script below uses 4 datafiles per set. “#channels” will determine speed… the more, the faster (with diminishing return at some point) and also more resources will get used. NOTE: This space is temporary only for the duration of the level0 backup (assuming RMAN is not commonly used for the instance). Level1's will not use any space since validate will be used.

Probability of Error
Time to take the level0 and level1 backups will not accurately identify the amount of change incurred during that backup. If your disk setup has larger track sizes than your database block size, the results need to be estimated up by the factor of (disk_track_size)/(db_block_size).

Steps
Create script to continuously delete completed RMAN sets and execute:
$ vi /RMAN/SCRIPTS/cleanup_backupsets.ksh #!/bin/ksh while [[ 1 = 1 ]] do for rman_file in `ls /tmp/rman_temp_*.rmn 2>/dev/null` do if [[ `/usr/sbin/fuser -u ${rman_file} 2>&1 | grep : | \ awk -F": " '{ print $2 }' | grep -v "^$" | wc -l` = 0 ]] ; then rm $rman_file > /dev/null 2>&1 fi done sleep 5 done $ /RMAN/SCRIPTS/cleanup_backupsets.ksh &

Run level0 backup:
vi /RMAN/SCRIPTS/level0_temp.ksh #!/bin/ksh export ORACLE_SID=<SID> export ORACLE_HOME=<SID_HOME> echo `date +"%Y%m%d%H%M%S"` rman target / nocatalog <<DONE run { allocate channel d1 type disk format '/tmp/rman_temp_%d_%s_%p_%c_%t.rmn'; allocate channel d2 type disk format '/tmp/rman_temp_%d_%s_%p_%c_%t.rmn'; … allocate channel d<n> type disk format '/tmp/rman_temp_%d_%s_%p_%c_%t.rmn'; backup incremental level=0 tag ='temp_level0' filesperset 4 database; } DONE echo `date +"%Y%m%d%H%M%S"`

www.rmoug.org

RMOUG Training Days 2006

RMAN and Overall Database Integrity

PAL

$ /RMAN/SCRIPTS/level0_temp.ksh > level0_temp.log 2>&1

Once level0 backup has completed, stop cleanup script:
$ jobs #assuming that you are using the same OS session. $ kill %<job#>

Run level1 validation backup:
vi /RMAN/SCRIPTS/level1_temp.ksh #!/bin/ksh export ORACLE_SID=<SID> export ORACLE_HOME=<SID_HOME> echo `date +"%Y%m%d%H%M%S"` rman target / nocatalog <<DONE connect catalog rman/<password> run { allocate channel ch1 type disk; allocate channel ch2 type disk; … allocate channel ch<n> type disk; backup incremental level=1 tag ='Time <n>' setsize 16777216 check logical validate database; } DONE echo `date +"%Y%m%d%H%M%S"` $ /RMAN/SCRIPTS/level1_temp_<time1>.ksh > level0_temp.log 2>&1

Repeat for each desired time interval. Change log name each time so that a record of start and stop times are available. View Results: NOTE: Each time period will display the amount of space changed since the initial level0 backup. <starttime> = noted time prior to backup. <endtime> = noted time after backup.
$ sqlplus '/as sysdba' #assuming ORACLE_HOME & ORACLE_SID are set SQL> select sum(blocks*block_size)/1024/1024 Mb from v$backup_datafile where completion_time between to_date('<level<n>_time<n>_start>','YYYYMMDDHH24MISS') and to_date('<level<n>_time<n>_start>','YYYYMMDDHH24MISS') /

Repeat for each time<n> level1 to gather change rate from the time of the level0 backup.

Summary
Oracle‟s RMAN provides functionality far beyond mere backups. It provides advanced restore/recovery functionality to handle a wide variety of situations, provides a workaround for some problems inherent to large and busy environments, provides the ability to proactively address many data-corruption issues found in no other utility and it provides functionality to clone databases. Often, RMAN implementations are seen as complex and expensive, but that is not the case. And the benefits are so wide-reaching. There is literally no excuse for not using RMAN.

About the Author
Raj Pal works at EchoStar Satellite LLC as a production database administrator. He has a B.Sc. in biology from Ontario, Canada, and 6 years of technical and managerial IT experience using Oracle, Weblogic, and webMethods.

www.rmoug.org

RMOUG Training Days 2006


								
To top