Monday, March 25, 2013

validate backup copy with check logical

According to one case study, this is a best pracitice. So I give it try, although there is minal overhead from "check logical".

This will report
– For any inconsistent data, index, or other type of blocks.
– Number of total and empty blocks examined.
– Highest change number of each datafile copy.

From the following experiment, I also find one error of the orginal command "backup check logical validate datafilecopy all filesperset 1;"

1. Create datafile copy
RMAN> show all ;

...
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;


RMAN> configure device type disk backup type to copy;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY PARALLELISM 1;
new RMAN configuration parameters are successfully stored


SYS@DGS> alter system set db_recovery_file_dest_size=4000m;

System altered.

SYS@DGS> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
orarac2poc:DGS:/home/oracle/scripts/performance> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Mar 25 15:44:26 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DG (DBID=1716975309, not open)

RMAN> backup database;

Starting backup at 25-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/oradata/DG/system01.dbf
..
output file name=/u01/oradata/DG/FRA/DGS/datafile/o1_mf_users_8o0060og_.dbf tag=TAG20130325T154431 RECID=29 ST                                                          AMP=811007168
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 25-MAR-13

Starting Control File and SPFILE Autobackup at 25-MAR-13
piece handle=/u01/oradata/DG/FRA/DGS/autobackup/2013_03_25/o1_mf_s_810991505_8o0063dg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-13

2. Now validate the copy.

RMAN> backup check logical validate datafilecopy all filesperset 1;

Starting backup at 25-MAR-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/25/2013 15:46:40
RMAN-06580: the FPERSET option cannot be used with AS COPY

RMAN> backup check logical validate datafilecopy all;

Starting backup at 25-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00001: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_system_8o002zs2_.dbf
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
List of Datafile Copies
=======================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              70536        131072          12432133
  File Name: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_system_8o002zs2_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              48059
  Index      0              9775
  Other      0              2702
...

channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00003: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_undotbs1_8nzzygoc_.dbf
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
List of Datafile Copies
=======================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              22273        64000           12431983
  File Name: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_undotbs1_8nzzygoc_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              41727

channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00004: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_users_8o0060og_.dbf
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
List of Datafile Copies
=======================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              497          640             9865692
  File Name: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_users_8o0060og_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5
  Index      0              2
  Other      0              136

channel ORA_DISK_1: starting datafile copy
input is copy of datafile 00004: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_users_8nzzz77o_.dbf
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
List of Datafile Copies
=======================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              497          640             9865692
  File Name: /u01/oradata/DG/FRA/DGS/datafile/o1_mf_users_8nzzz77o_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5
  Index      0              2
  Other      0              136

Finished backup at 25-MAR-13