CASE STUDIES IN RMAN METRICS
Anthony D Noriega, MSCS, MBA, OCP, BSSE, Independent Oracle Consultant, ADN
ABSTRACT
While RMAN is the most commonly used method of Oracle backup, either standalone or in conjunction with other backup and storage technologies, few studies have been made in relation to backup duration, consistency, and reliability. This study focuses on metrics and statistics in an effort to derive some consensus in relation to factors affecting RMAN backup and restore duration, reliability, and consistency, such as database size, channel speed, device type, database infrastructure and architecture, and various others associated with the concurrency of events. The study expands to standard and point-in-time recovery scenarios, with an outlook in RMAN’s positioning in the database Business Continuity (BC) and Disaster Recovery (DR) markets.
INTRODUCTION
RMAN has proven to be a consistent and resilient database backup and recovery management tool, especially, when properly used. Likewise, companies that have used RMAN from previous releases have learned enough to best utilize it, with or without the recovery catalog. The incremental enhancements provided in Oracle versions 9i and 10g. In general, it is expected that when benchmarking comparable Oracle9i and Oracle10g databases, in terms of table contents and overall density, that Oracle10g RMAN backups and related restores will run faster than Oracle9i, in particular, in 10g features have been enabled with a speed-up seeking goal. Although an actual and native Oracle10g performance improvement holds over Oracle9i releases, the customization of Oracle10g features could further enhance backup and recovery performance and reliability over previous releases.
THE RMAN METRICS PROBLEM DEFINITION
The RMAN metric problem consists of the following considerations:
• Determining the window of time and related duration to perform a backup
• Establishing a window of time and related duration to perform the corresponding restore with all the relevant scenario considerations
• Establishing a cost analysis in relation to the appropriate version for any process, namely, backup, recovery, or cloning.
• Creating a policy framework in order to consistently maintain archived redo log files in backup sets, or available accordingly in the file system. This extremely practical for recovery processes independently from whether backup set or image backups are being used. Neglecting this policy accounts for a large percent of causes for restore failures, backup invalidation, and other related inconsistencies.
• Utilizing available features and capabilities such as Block Change Tracking, control file auto-backup, native backup compression, or point-in-time recovery when appropriate.
• Establishing a historic log in connection with the above policy framework to correlate backup/restore metrics with some factorial considerations such as availability of resources, and other IT configuration management concerns.
EXPERIMENTAL PRODUCTION ENVIRONMENTS
The experimental environments discussed in this study are fundamentally Unix-based environments, yet other environments were partly considered both with recorded information and for qualitative observation in relation to the quantitative analysis involved.
ARCHITECTURE AND INFRASTRUCTURE CONSIDERATIONS
The experiments and studies presented are based on dedicated architectures using Oracle9i and 10g instances with projection factors from 9i over 10g versions time improvements. The main most important factor to be considered is block change tracking, since most performance correlations prevail. Obviously, there is a consideration inherit to Oracle10g, based on its improved performance over previous releases. This is particularly true when using incremental backups.
BACKUP AND RESTORE ISSUES
In general, the time to restore an RMAN backup varies due to various factors, including –but not limited to- device and computing resources availability, and network issues, if applicable. However, statistically the restore time is usually commensurate with the backup time, and usually longer if a backup verification policy is in practice. The RMAN restore time increment is usually from a small percent of the backup time without the recovery catalog and up to nearly doubling of that time when both the recovery catalog and verification process is put in place. However, these statements are all relative under the Oracle10g perspective when using Block Change Tracking, which can significantly minimize this overhead.
RMAN METRICS CASE STUDIES
The cases presented here include general backup with and without a recovery catalog, and a database point-in-time recovery without a catalog, with some general observations on restore cases.
GATHERED DATA, METRICS, AND STATISTICS WITHOUT A RECOVERY CATALOG
The following table summarizes a list of data related to RMAN backup sizes and durations for a set of RMAN backup on different databases without using the recovery catalog (Table 1). This information is reorganized and presented once more on Table 2.
RMAN Backup Size and Duration Statistics
Instance Name Host Name RMAN Backup Size Range Duration Range
Prd1 Host1 4.0GB-7.0GB 2 to 7 minutes
Prd2 Host2 3.5GB-4GB 2 to 4 minutes
Dev1 Host3 1.8GB-2.0GB 2 to 3 minutes
Dev2 Host4 5.0GB-6.0GB 5 to 9 minutes
QA1 Host5 23.0GB-24.0GB 28 to 29 minutes
Table 1. RMAN Backup Duration for Oracle Instances Studied (without a Recovery Catalog)
This information is realigned with further details, as presented in the next table. :
Instance Name Host Name Avg DB Size (GB) Rman Backup
Min Size (GB) Rman Backup
Max Size (GB) Min Duration Max Duration
Prod1 Host1 256 4.0 7.0 2 7
Prod2 Host2 198 3.5 4.0 2 4
Dev1 Host3 128 1.8 2.0 2 3
Dev2 Host4 248 5.0 6.0 5 9
Qa1 Host5 300 23.0 24.0 28 29
Table 2. Enhanced Detail for RMAN Backup Duration for Oracle Instances Studies (without a Recovery Catalog)
The RMAN backup restore time without a recovery catalog is about the same as the backup time itself with a slight percent trend to take longer based on the scenario where the restore takes place. Therefore, in consideration to the size of the backup, the restore table is essentially equivalent to the backup table exhibited, since duration discrepancies are as granular as fractions of a second and only up to a few seconds for this case, in particular.
Instance Name Host Name MinSize (GB) MaxSize (GB) MinTime (m) MaxTime (m)
Prod1 Host1 4.0 7.0 2 7
Prod2 Host2 3.5 4 2 4
Dev1 Host3 1.8 2.0 2 3
Dev2 Host4 5.0 6.0 5 9
Qa1 Host5 23.0 24.0 28 29
Table 3. RMAN Restore Fact Data Table (without a Recovery Catalog)
Exhibit 1. RMAN NOCATALOG Instance-Host vs. Duration Line Chart
GATHERED DATA, METRICS, AND STATISTICS USING THE RECOVERY CATALOG
The following table consolidates observations made on several database instances using the recovery catalog.
RMAN Backup Size and Duration Statistics
Instance Name Host Name Average Database Size Rman Backup
Min Size (GB) Rman Backup
Max Size (GB) Min Duration
(Minutes) Max Duration
(Minutes)
Prd1 Host1 512GB 21 81 55 88
Dv1 Host2 512GB 22 99 65 90
Risk1 Host3 256GB 12 31 45 68
Uat1 Host4 300GB 30 105 45 77
Risk2 Host5 628GB 23.0 121 65 116
Table 4. RMAN Backup Data Summary using the Recovery Catalog
RMAN Backup Size and Duration Statistics with Backup Validation
Instance Name Host Name Average Database Size Rman Backup
Min Size (GB) Rman Backup
Max Size (GB) Min Duration
(Minutes) Max Duration
(Minutes)
Prod1 Host1 512GB 21 81 85 128
Prod2 Host2 512GB 22 99 95 135
Dev1 Host3 256GB 12 31 77 102
Dev2 Host4 300GB 30 105 85 105
Qa1 Host5 628GB 23.0 121 97 159
Table 5. RMAN Backup Data Summary using the Recovery Catalog with Backup Validation
The restore table is nearly comparable to that provided by backup with validation, as follows:
RMAN Restore Statistics Using the Recovery Catalog
Instance Name Host Name Average Database Size Rman Backup Min Size (GB) Rman Backup Max Size (GB) (Minutes)
Min Duration (Minutes)
Max Duration
Prod1 Host1 512GB 21 81 81 115
Prod2 Host2 512GB 22 99 90 121
Dev1 Host3 256GB 12 31 72 89
Dev2 Host4 300GB 30 105 77 95
Qa1 Host5 628GB 23.0 121 89 125
Table 6. RMAN Restore Data Summary using the Recovery Catalog
UNIFIED METRICS ANALYSIS
From the entire set, involving both Catalog-based and Catalog-less observations, it is possible derive the following statistical analytic results using Oracle SQL:
Exhibit 2. Some Regression Information Correlating the Average Database Size and the Maximum Backup Duration.
Exhibit 3. Other Regression Metric Analysis
In addition to the observations made by the SQL regression aggregate functions, it is possible to encounter the following correlation among variable with interactions:
• Minimum Duration and RMAN Backup Maximum Size
• Maximum Duration and RMAN Backup Maximum Size
• Maximum and Minimum Duration
• Catalog Option and Minimum Duration
• Catalog Option and Maximum Duration.
Avg DB Size (GB) RMAN Backup Min Size (GB) RMAN Backup Max Size (GB) Min Duration Max Duration Cat Opt
Avg DB Size (GB) 1
RMAN Backup Min Size (GB) 0.672812809 1
RMAN Backup Max Size (GB) 0.850083343 0.856779215 1
Min Duration 0.856536148 0.834302561 0.910370414 1
Max Duration 0.873295324 0.804858853 0.942746843 0.981002475 1
Cat Opt 0.706810637 0.717007196 0.867454307 0.926638071 0.945848466 1
Table 7. Correlation Table Showing Variable Interaction
ORACLE10g ADJUSTMENTS
The following are estimated and adjusted metrics for the Oracle10g versions of this upgraded databases when Block Change Tracking is used, as follows :
• The number of blocks changed as a percent of the total backup, useful with incremental backup policies.
• The global block density in the database being backed up.
• The nature of the block changes in terms of DML transaction types.
The combined effect of these factors should count as a total discount factor in terms of time, in comparison to a conventional backup not using block change tracking.
SESSION LONG OPERATION IMPACT ANALYSES
Although RMAN backup configuration is flexible to accommodate to most scenarios, it is important to understand which parameters apply. In the cases studied, backing up first to disk was the strategy. In all cases, a copy of the database backup was available on disk, while a compressed tape backup was made of the original backup. No compressed backup sets were sent directly to tape. The duration of the overall process varied based on daily database activity and computing resources allocated to a SAN environment and other storage devices involved.
RMAN long session impact is possibly affected by settings of parameters such as FAST_START_MTTR_TARGET or its omission. Similarly, architectural considerations, namely, the usage of an inadequate sizing of the LARGE_POOL parameter or a shared architecture could lead to unconventional results. In the latter case, a dedicate session needs to be set for RMAN to work properly. Other considerations, relate to Oracle10g capabilities to run partial RMAN backup or use explicit options to either run at full speed or running in a controlled fashion within the window of time. On the other hand, in some scenarios, long waits relate to asynchronous RMAN tasks conveying bottlenecks that need to be carefully identified and fixed.
Exhibit 4. A sample RMAN Configuration Without a Recovery Catalog
Exhibit 5. Monitoring RMAN Long Running Jobs for Corrective Action
Exhibit 6. RMAN NOCATALOG Configuration and Database Incarnations
Exhibit 7. Listing RMAN Backup Sets
Exhibit 8. Listing RMAN Archived Logs in Backup Sets
RMAN NO-CATALOG DATABASE POINT-IN-TIME RECOVERY MODEL METRICS
The RMAN Database Point-in-Time Recovery without using a recovery catalog is a special case of an RMAN restore, where determining the point-of-failure is critical to utilizing any type of metrics whether estimated restore time, required resources, such as disk space, and restore strategy based on components to restore. Therefore, it is critical to determine whether the point-in-time to recover is within the backup or outside the backup to restore. If the point of time is after the entire backup set, the solution is trivial and it consists of restoring the entire backup and then applying the required archived redo logs until the desired point in time to recover. The shell provided in the appendix provides both validation and actual point-in-time recovery for the non-trivial case.
Exhibit 9. RMAN DBPITR Duration and Cost Analysis.
Although the study only contemplated the recovery without a catalog, the decision tree provided in the exhibit above does consistently advise about the appropriate way to account for the duration and related costs of the point-in-time recovery. Furthermore, when the point-in-time to recover is found within the backup file, the reduction in time is proportional to the location within the backup. Therefore, point in time locations with older SCNs or timestamps, will have greater reduction than those closer to the last SCN available in the backup. Besides, the DBPITR is not necessarily constrained by the all conditions applying to TSPITR recovery, such as support of snapshot logs, which is simply relative. Also, no auxiliary database is needed for DBPITR However, there are important considerations to properly backing up the archived redo logs within the backup set and maintaining file system copies to attain the desired level of consistency.
MODEL IMPLICATIONS ON VLDB (PRODUCTION DATABASES)
Based on the consistency provided by the current medium and large database models studied, and due to the difficulties in testing these statistics and metrics on Very Large Databases (VLDBs), it is still possible to suggest that the same type of correlation among variables are valid at large scale. Similarly, VLDBs tend to have many business and operational constraints with usually reduced windows of time for backups, for which other high-availability solutions are mostly used. Nonetheless, for some of the experiments designed in these case studies, somewhat presented in this paper, the Database, System, and SAN Administrators utilized a comparable level of HARD-based high availability environment, where the SAN acted as a Maximum Availability Architecture (MAA) providing both Clusters and Data Guard capabilities, such as graceful switchover, failover, and controlled switchback. The database sizes were in the threshold of large databases, but could not compare to a VLDB range.
Consequently, this study can serve as a scalable benchmark for more comprehensive corporate studies on VLDB, as part of a disaster recovery and efficient business continuity practice.
BUSINESS ANALYSIS CONSIDERATIONS
The elicitation of requirements involving Disaster Recovery (DR) or Business Continuity (BC) practices require explicit information on backup and recovery times, in an effort to allocate windows of time to periodic utility tasks. Similarly, these durations of time can be greatly associated with an IT infrastructure’s ability to respond to any DR scenario. Therefore, educing requirements for windows of time, and estimating backup and restore durations becomes foundational to DR/BC.
Another important aspect of any RMAN business analysis focused on DR or BC practices needs to allocate the technical and human resources to perform a large number of quality assurance (QA) and tuning tasks, beyond any required coding or automation. The QA tasks can involve, but are not limited to, backup validation, backup and restore timeliness, shell scripts reliability and robustness through different platform configurations , scheduling, source saving, documentation, and other relevant tasks.
CONCLUDING REMARKS
The RMAN metrics cases discussed here corroborate a relationship between backup and database sizes and backup duration regardless of the catalog-related considerations. While this statement is nearly trivial to assert, there are many environmental factors that could partially change its validity. The most interested aspect of the variability study contemplates the minimum and maximum backup duration, whose variance point the number of a certain number of infrastructure and scenario-based factors involved. The Analysis of Variance (ANOVA) presented in the Appendix attempts to explain this variability. Likewise, a two-way ANOVA table with repetitions is presented.
Disaster Recovery and Business Continuity Teams need to strategically plan on the implementation of a comprehensive set of factors, bottlenecks, and time and resource constraints that could make the derived statistics and analysis useful in those IT administrative areas. By doing so, it is at least evident that technical stakeholders have congruently worked towards the appropriate preparedness in the event of a disaster, and are working on a daily basis with a robust DR/BC infrastructure.
Finally, the most important concluding remarks are summarized in the following table:
Feature/Issue Benefit
9i to 10g Congruent Metrics Allows for post-upgrade and multi-version DR/BC time allocation.
Configuration Bottlenecks Initialization parameter configuration as part of OFA-driven architecture can be further optimized in 10g in relation to 9i releases. The overall benefits translate into faster performance and throughput.
Planning and Risk Analysis Using historic backup/restore historic data can lead to overcoming unplanned process and limiting the risk involved.
Block Change Tracking Extremely useful with incremental backups, reduces CPU cycles, and enables faster backups and shorter recovery time.
DBPITR A parallel strategy to Flashbackup database. It is supported by RMAN with or without a recovery catalog.
Backup Time vs. Restore/Recovery Time Although backup and restore times tend to be comparable, having gather data and statistics on duration can enhance robustness and preparedness in DR/BC processes.
Table 8. Concluding Remarks
APPENDIX
Instance Name Host Name Avg DB Size (GB) Rman Backup Min Size (GB) Rman Backup Max Size (GB) Min Duration Max Duration 0 = nocat
1 = cat
Cat Opt
Prod1 Host1 256 4 7 2 7 0
Prod2 Host2 198 3.5 4 2 4 0
Dev1 Host3 128 1.8 2 2 3 0
Dev2 Host4 248 5 6 5 9 0
Qa1 Host5 300 23 24 28 29 0
Prd1 Host1 512 21 81 55 88 1
Dv1 Host2 512 22 99 65 90 1
Risk1 Host3 256 12 31 45 68 1
Uat1 Host4 300 30 105 45 77 1
Risk2 Host5 628 23 121 65 116 1
Table 9. Source Data Gathered for Statistical Analysis
Statistics Analytics
Two-way Anova with Repetitions for RMAN NOCATALOG Data
SUMMARY 256 4 7 2 7 Total
Host2
Count 2 2 2 2 2 10
Sum 326 5.3 6 4 7 348.3
Average 163 2.65 3 2 3.5 34.83
Variance 2450 1.445 2 0 0.5 4836.133
Host4
Count 2 2 2 2 2 10
Sum 548 28 30 33 38 677
Average 274 14 15 16.5 19 67.7
Variance 1352 162 162 264.5 200 12063.12
Total
Count 4 4 4 4 4
Sum 874 33.3 36 37 45
Average 218.5 8.325 9 9.25 11.25
Variance 5374.333 97.4225 102.6667 158.25 146.9167
ANOVA
Source of Variation SS df MS F P-value F crit
Sample 5402.185 1 5402.185 11.75808 0.006449 4.964591
Columns 139856.7 4 34964.18 76.10099 1.9E-07 3.47805
Interaction 7642.138 4 1910.535 4.158358 0.030764 3.47805
Within 4594.445 10 459.4445
Total 157495.5 19
Table 10. Two-Way ANOVA
Entire Set Multi-Regression Analysis for both With and Without the Recovery Catalog
(Maximum Duration is the Independent Variable (Regressand)
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9506126
R Square 0.9036643
Adjusted R Square 0.8458629
Standard Error 16.87026
Observations 9
ANOVA
df SS MS F Significance F
Regression 3 13348.53 4449.509062 15.63394 0.005662277
Residual 5 1423.028 284.605674
Total 8 14771.56
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -4.9598797 17.78092 -0.278943945 0.791457 -50.66711281 40.74735 -50.6671 40.7473533
256 0.077939 0.068482 1.138093711 0.306659 -0.098099465 0.253977 -0.0981 0.25397749
4 0.0677914 1.087778 0.062320971 0.952722 -2.728425992 2.864009 -2.72843 2.86400873
7 0.589539 0.328242 1.796050497 0.132429 -0.254232257 1.43331 -0.25423 1.4333103
RESIDUAL OUTPUT PROBABILITY OUTPUT
Observation Predicted 7 Residuals Standard Residuals Percentile 7
1 13.067471 -9.06747 -0.679867656 5.555555556 3
2 6.3174165 -3.31742 -0.248735753 16.66666667 4
3 18.245187 -9.24519 -0.693192563 27.77777778 9
4 34.129962 -5.12996 -0.384638181 38.88888889 29
5 84.121175 3.878825 0.290829482 50 68
6 94.800669 -4.80067 -0.359948139 61.11111111 77
7 34.081714 33.91829 2.543150806 72.22222222 88
8 82.357163 -5.35716 -0.401673384 83.33333333 90
9 116.87924 -0.87924 -0.065924613 94.44444444 116
Exhibit 10. Multiple Regression and ANOVA
ORACLE ANALYTIC STATISTICS
Exhibit 11. Oracle Analytic Statistics
Exhibit 12. Oracle Analytic Statistics
BACKUP LISTING
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
334 Incr 0 1G DISK 00:00:53 19-NOV-05
BP Key: 334 Status: AVAILABLE Tag: WEEKLY_BACKUP
Piece Name:
/dump1/mgnss/rmanbackup/20051119_0105/ful_alh43ia5_MGNSS
SPFILE Included: Modification time: 03-OCT-05
List of Datafiles in backup set 334
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 51691876 19-NOV-05 /data1/oradata/mgnss/system01.dbf
5 0 Incr 51691876 19-NOV-05 /data1/oradata/mgnss/perfstat01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
335 Incr 0 2G DISK 00:02:00 19-NOV-05
BP Key: 335 Status: AVAILABLE Tag: WEEKLY_BACKUP
Piece Name:
/dump1/mgnss/rmanbackup/20051119_0105/ful_amh43ia5_MGNSS
List of Datafiles in backup set 335
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 51691875 19-NOV-05 /data1/oradata/mgnss/undotbs1.dbf
3 0 Incr 51691875 19-NOV-05 /data1/oradata/mgnss/tools01.dbf
4 0 Incr 51691875 19-NOV-05 /data1/oradata/mgnss/users01.dbf
6 0 Incr 51691875 19-NOV-05 /data1/oradata/mgnss/v7data01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
336 588M DISK 00:00:31 19-NOV-05
BP Key: 336 Status: AVAILABLE Tag: TAG20051119T010713
Piece Name:
/dump1/mgnss/rmanbackup/20051119_0105/arc_anh43ie1_MGNSS
List of Archived Logs in backup set 336
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8945 50427811 18-NOV-05 50429822 18-NOV-05
1 8946 50429822 18-NOV-05 50429825 18-NOV-05
1 8947 50429825 18-NOV-05 50441825 18-NOV-05
...
...
...
1 9244 51974291 21-NOV-05 51976461 21-NOV-05
1 9245 51976461 21-NOV-05 51976464 21-NOV-05
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
350 Full 3M DISK 00:00:01 21-NOV-05
BP Key: 350 Status: AVAILABLE Tag: TAG20051121T010742
Piece Name:
/dump1/mgnss/rmanbackup/20051121_0105/ctl_b5h48r6u_MGNSS
Controlfile Included: Ckp SCN: 51976475 Ckp time: 21-NOV-05
RMAN>
SAMPLE DATABASE POINT-IN-TIME AND VALIDATION SHELL
#!/usr/bin/ksh
# set -x
## ***********************************************************
## rman sample validat script
## prepared by: Anthony D. Noriega, ADN, Oracle Consultant
## Syntax: r_rval_bkp.ksh $ORACLE_SID $R=1_OR_V=0 $TS $DBID
## Example: r_rval_bkp.ksh devtwbg 1 "'01-JAN-2006 01:01:55'"\
## 1885319376
## After recovery temp tbs file(s) must be added manually
## ***********************************************************
ORACLE_SID=$1
export ORACLE_SID
RMAN=rman
SID=$ORACLE_SID
VALOPT=$2
UNTIL_TS=$3
echo $UNTIL_TS
DBID=$4
echo '#*******************************'
echo '# Values Read: *'
echo '# $SID='$SID
echo '# $DBID='$DBID
echo '# $OPT:[1=R,0=V] => '$VALOPT
echo '# $UNTIL_TS='$UNTIL_TS
echo '# *'
echo '#******************************'
RR='R'
VV='V'
sleep 10
export ORATAB=/var/opt/oracle/oratab
i=`cat $ORATAB|grep -v "^#"|grep $ORACLE_SID`
ORACLE_HOME=`echo $i | cut -d: -f2`;
export ORACLE_HOME
DT=`date '+%Y%m%d_%H%M'`
MoDir=`date +%Y%m`
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/etc;export PATH
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
DBADMIN=/opt/oracle/admin
OD=/data1/oradata
LOGDIR=/home/dba/admin/rpt/${RMAN}.${MoDir}
LOG=${LOGDIR}/${RMAN}.rv.${DT}.log
INSTCOUNT=`ps -ef|fgrep $SID|fgrep smon|grep -v grep|wc -l`
if [[ ! -d ${LOGDIR} ]]
then
chown oracle:dba ${LOGDIR}
chmod 755 ${LOGDIR}
echo 'Directory already exists'
else
mkdir -p ${LOGDIR}
chown oracle:dba ${LOGDIR}
chmod 755 ${LOGDIR}
echo 'Validated directory '$LOGDIR
fi
if [[ $VALOPT -eq 1 ]] || [[ $INSTCOUNT -eq 0 ]]
then
$ORACLE_HOME/bin/sqlplus -s '/as sysdba' << EOSQL1 >> $LOG
set echo on
set feedback on
startup nomount force
show sga
exit;
EOSQL1
fi
#(
$ORACLE_HOME/bin/sqlplus -s '/as sysdba' << EOSQL >> $LOG
set heading off
set pagesize 0
set linesize 144
set echo off
set feedback off
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
spool /tmp/nretrodays.dat
SELECT LTRIM(RTRIM(TRUNC(SYSDATE-TO_DATE(${UNTIL_TS},'DD-MON-YYYY
HH24:MI:SS'))))
FROM dual;
spool off
spool /tmp/hr.dat
SELECT LTRIM(RTRIM(TO_CHAR(TO_DATE(${UNTIL_TS},'DD-MON-YYYY
HH24:MI:SS'),'HH24')))
FROM dual;
spool off
spool /tmp/mn.dat
SELECT LTRIM(RTRIM(TO_CHAR(TO_DATE(${UNTIL_TS},'DD-MON-YYYY
HH24:MI:SS'),'MI')))
FROM dual;
spool off
spool /tmp/sc.dat
SELECT LTRIM(RTRIM(TO_CHAR(TO_DATE($UNTIL_TS,'DD-MON-YYYY
HH24:MI:SS'),'SS')))
FROM dual;
spool off
spool /tmp/dbstatus.dat
SELECT LTRIM(RTRIM(status))
FROM v\$instance;
spool off
exit;
EOSQL
#) | tee -a $LOG
NRETRODAYS=`cat /tmp/nretrodays.dat`
HR=`cat /tmp/hr.dat`
MN=`cat /tmp/mn.dat`
SC=`cat /tmp/sc.dat`
DBST=`cat /tmp/dbstatus.dat`
#
#rm -f /tmp/*.dat
#
if [[ $VALOPT -eq 1 ]]
then
echo '#####'
echo '#############################################' >> $LOG
echo '# Using '${NLS_DATE_FORMAT}' as date format #' >> $LOG
echo '# Validating recovery started at '`date`' #' >> $LOG
echo '#############################################' >> $LOG
echo '#####'
#
#
#(
rman nocatalog << EORMAN >> $LOG
connect target /
allocate channel for maintenance device type disk;
set dbid=$DBID;
restore spfile.ora to '$DBADMIN/$SID/pfile/spfile$SID.ora' from
autobackup;
release channel;
startup force nomount;
run
{
restore controlfile to '/tmp/control01.dbf' from autobackup maxseq 120;
restore controlfile from '/tmp/control01.dbf';
}
run
{
alter database mount;
show all;
}
run
{
set until
time="trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
restore database until
time="trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
set until
time="trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
restore archivelog from time= "trunc(sysdate,'dd')-$NRETRODAYS" until
time= "trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
}
run
{
allocate channel dc1 device type disk;
set until
time="trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
recover database until
time="trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
release channel dc1;
}
run
{
sql "alter database open resetlogs";
}
exit;
EORMAN
#) | tee -a $LOG
else
#(
rman nocatalog << EORMANV >> $LOG
connect target /
allocate channel for maintenance device type disk;
set dbid=$DBID;
restore spfile.ora to '$DBADMIN/$SID/pfile/spfile$SID.ora' from
autobackup;
release channel;
startup force nomount;
run
{
restore controlfile to '/tmp/control01.dbf' from autobackup validate;
restore controlfile from '/tmp/control01.dbf' validate;
}
run
{
alter database mount;
show all;
}
run
{
set until time =
"trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
crosscheck backupset of controlfile;
alter database mount ;
crosscheck backupset of database;
crosscheck backupset of archivelog all;
crosscheck archivelog all;
}
run
{
set until time =
"trunc(sysdate,'dd')-$NRETRODAYS+$HR/24+$MN/1440+$SC/86400";
restore controlfile to 'xx' until time="trunc(sysdate,'dd')-$NRETRODAYS
+ $HR/24 + $MN/1440 + $SC/86400" validate;
restore database until time="trunc(sysdate,'dd')-$NRETRODAYS + $HR/24 +
$MN/1440 + $SC/86400" validate;
restore archivelog time between "trunc(SYSDATE-$NRETRODAYS,'dd')" and
"trunc(SYSDATE-$NRETRODAYS,'dd') + $HR/24+$MN/1440 + $SC/86400"
validate;
}
exit;
EORMANV
#) | tee $LOG
fi
mailx -s 'RMAN restore validated for '$SID anthonydnoriega1@yahoo.com \
anthonydnoriega@msn.com < $LOG
cat $LOG
EOF
*********************************************************************************************************
For the reader interested in obtaining a pdf version of my paper, please send me email at:
anthonydnoriega@gmail.com or retrieve it from the ioug.org Collaborate07 proceedings knowledgebase.
**********************************************************************************************************
