Search My Oracle Blog

Custom Search

18 June, 2012

OOW 2012 Content Catalog

07 June, 2012

CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

There was a recent forums discussion about manual controlfile backups being obsolete.  Here I show that even autobackups are obsoleted by Oracle.

First I show that CONTROLFILE AUTOBACKUP is ON an RETENTION is set to REDUNDANCY 1

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

RMAN> 
Next, I list all my controlfile backups.  This shows that I do not have manual backups but only autobackups.

RMAN> list backup of controlfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    9.33M      DISK        00:00:00     01-JAN-12      
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20120101T115229
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
  Control File Included: Ckp SCN: 4957876      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    9.33M      DISK        00:00:01     13-FEB-12      
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20120213T234617
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
  Control File Included: Ckp SCN: 5120748      Ckp time: 13-FEB-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20      Full    9.33M      DISK        00:00:01     23-MAR-12      
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T231640
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
  Control File Included: Ckp SCN: 5192490      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    9.33M      DISK        00:00:00     23-MAR-12      
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20120323T235653
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
  Control File Included: Ckp SCN: 5194773      Ckp time: 23-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001027
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
  Control File Included: Ckp SCN: 5196407      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    9.33M      DISK        00:00:00     24-MAR-12      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
  Control File Included: Ckp SCN: 5196673      Ckp time: 24-MAR-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33      Full    1.05M      DISK        00:00:00     01-JAN-12      
        BP Key: 33   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T110008
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
  Control File Included: Ckp SCN: 4955357      Ckp time: 01-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
38      Full    9.33M      DISK        00:00:01     15-MAY-12      
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20120515T002039
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
  Control File Included: Ckp SCN: 5288840      Ckp time: 15-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T230909
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
  Control File Included: Ckp SCN: 5313644      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41      Full    9.33M      DISK        00:00:00     20-MAY-12      
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: TAG20120520T232651
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
  Control File Included: Ckp SCN: 5322969      Ckp time: 20-MAY-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224133
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
  Control File Included: Ckp SCN: 5353308      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224515
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
  Control File Included: Ckp SCN: 5353844      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T224540
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
  Control File Included: Ckp SCN: 5353875      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45      Full    9.33M      DISK        00:00:01     03-JUN-12      
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230026
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp
  Control File Included: Ckp SCN: 5356135      Ckp time: 03-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Full    9.33M      DISK        00:00:00     03-JUN-12      
        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: TAG20120603T230639
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785027199_7wpzczo7_.bkp
  Control File Included: Ckp SCN: 5356475      Ckp time: 03-JUN-12

RMAN> 
So, I have controlfile autobackups going as far back as 01-Jan.  Quite obvious : I haven't been deleting "obsolete" files.  (This is a "play" environment with adequate disk space for multiple backups of a small database).

I now list the OBSOLETE Backups.

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           34     09-MAY-12         
  Backup Piece       34     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1p3b_.bkp
Backup Set           26     09-MAY-12         
  Backup Piece       26     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110005_7hzm1q6l_.bkp
Backup Set           36     09-MAY-12         
  Backup Piece       36     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T110008_7hzm1rj9_.bkp
Backup Set           33     09-MAY-12         
  Backup Piece       33     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_ncnnf_TAG20120101T110008_7hzm5dob_.bkp
Backup Set           27     09-MAY-12         
  Backup Piece       27     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T110205_7hzm5g23_.bkp
Backup Set           30     09-MAY-12         
  Backup Piece       30     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjtnr_.bkp
Backup Set           32     09-MAY-12         
  Backup Piece       32     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112514_7hznjvr1_.bkp
Backup Set           29     09-MAY-12         
  Backup Piece       29     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
Backup Set           28     09-MAY-12         
  Backup Piece       28     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznm84k_.bkp
Backup Set           35     09-MAY-12         
  Backup Piece       35     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmq8q_.bkp
Backup Set           31     09-MAY-12         
  Backup Piece       31     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznmydw_.bkp
Backup Set           37     09-MAY-12         
  Backup Piece       37     09-MAY-12          /newmountpoint/FRA/2012_01_01/o1_mf_annnn_TAG20120101T112655_7hznmzlq_.bkp
Archive Log          23     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_3_7k5vc0nf_.arc
Backup Set           13     01-JAN-12         
  Backup Piece       13     01-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_01_01/o1_mf_n_771421948_7hzp3xf6_.bkp
Archive Log          24     15-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_15/o1_mf_1_4_7k5wo9bl_.arc
Archive Log          25     22-JAN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_01_22/o1_mf_1_5_7koxgt4y_.arc
Archive Log          26     10-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_10/o1_mf_1_6_7mbfpjnf_.arc
Archive Log          27     12-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_12/o1_mf_1_7_7mhmgbrx_.arc
Archive Log          28     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mlcok0g_.arc
Archive Log          29     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mlctdf6_.arc
Archive Log          30     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_10_7mld23vp_.arc
Archive Log          32     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_9_7mld241b_.arc
Archive Log          31     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_8_7mld23yr_.arc
Archive Log          33     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_02_13/o1_mf_1_1_7mldpt71_.arc
Backup Set           14     13-FEB-12         
  Backup Piece       14     13-FEB-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_02_13/o1_mf_n_775179977_7mld2b7l_.bkp
Archive Log          34     18-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_18/o1_mf_1_2_7pcwy7lm_.arc
Archive Log          35     20-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_20/o1_mf_1_3_7pk6bsr9_.arc
Archive Log          36     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_4_7ps4pghr_.arc
Archive Log          37     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_5_7ps4tvvj_.arc
Backup Set           15     23-MAR-12         
  Backup Piece       15     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4tww3_.bkp
Backup Set           16     23-MAR-12         
  Backup Piece       16     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4txz4_.bkp
Backup Set           17     23-MAR-12         
  Backup Piece       17     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231436_7ps4vf2f_.bkp
Backup Set           18     23-MAR-12         
  Backup Piece       18     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
Archive Log          38     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_03_23/o1_mf_1_6_7ps4yqkg_.arc
Backup Set           19     23-MAR-12         
  Backup Piece       19     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_annnn_TAG20120323T231639_7ps4yqqm_.bkp
Backup Set           20     23-MAR-12         
  Backup Piece       20     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778720600_7ps4ys8j_.bkp
Backup Set           22     23-MAR-12         
  Backup Piece       22     23-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_23/o1_mf_n_778723013_7ps7b5l1_.bkp
Datafile Copy        4      24-MAR-12          /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf
Backup Set           23     24-MAR-12         
  Backup Piece       23     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778723827_7ps83mpw_.bkp
Backup Set           24     24-MAR-12         
  Backup Piece       24     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
Backup Set           25     24-MAR-12         
  Backup Piece       25     24-MAR-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_03_24/o1_mf_n_778724128_7ps8f0o8_.bkp
Backup Set           38     15-MAY-12         
  Backup Piece       38     15-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_15/o1_mf_n_783303639_7v2d6qkz_.bkp
Backup Set           39     20-MAY-12         
  Backup Piece       39     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_05_20/o1_mf_nnndf_TAG20120520T230907_7vl28n6q_.bkp
Backup Set           40     20-MAY-12         
  Backup Piece       40     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783817749_7vl28oln_.bkp
Backup Set           41     20-MAY-12         
  Backup Piece       41     20-MAY-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_05_20/o1_mf_n_783818811_7vl39vgv_.bkp
Backup Set           42     03-JUN-12         
  Backup Piece       42     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025693_7wpxwxhm_.bkp
Backup Set           43     03-JUN-12         
  Backup Piece       43     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp
Backup Set           44     03-JUN-12         
  Backup Piece       44     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp
Backup Set           45     03-JUN-12         
  Backup Piece       45     03-JUN-12          /home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp

RMAN> 
My CONTROLFILE AUTOBACKUP Pieces are shown as OBSOLETE.

Today's question : Why are all controlfile backups but the last one (I had 5 autobackups on 03-Jun and 4 of them are obsolete) reported as OBSOLETE ?
.
.
.

03 June, 2012

RMAN BACKUP AS COPY

By default the BACKUP command in RMAN creates BackupSet(s) -- each of which is one or more BackupPiece(s).  A datafile may span BackupPieces but may not span a BackupSet.

However, RMAN does allow another method -- BACKUP AS COPY.  This is akin to "User Managed Backups" created with OS commands -- except that the ALTER TABLESPACE | DATABASE BEGIN BACKUP command does not have to be issued.

BACKUP AS COPY creates a byte-for-byte copy of each datafile [except, inasmuch, blocks being modified by concurrent writes to the datafile].

If an active datafile is corrupted, the DBA can choose to SWITCH TO COPY instead of having to restore the datafile copy.  Thus, a switch can be a fast operation.  Obviously, the DBA must plan carefully where he creates such copies if he intends to SWITCH anytime later  (he wouldn't keep a datafile copy on a non-protected [RAID or ASM] storage target).

Here's a simple demo :


SQL> select file_id, file_name 
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf

SQL> alter tablespace add_tbs
  2  add datafile '/oradata/orcl/add_tbs_02.dbf' size 100M;

Tablespace altered.

SQL> 
SQL> create table hemant.add_table tablespace add_tbs as select * from dba_source;

Table created.

SQL> select file_id, count(*)
  2  from dba_Extents
  3  where owner = 'HEMANT'
  4  and segment_name = 'ADD_TABLE'
  5  group by file_id
  6  order by 1
  7  /

   FILE_ID   COUNT(*)
---------- ----------
        14         48
        15         37

SQL>  
So, I have begun with two datafiles and extents spread across both datafiles.
I now take backups of the two datafiles.

RMAN> backup as copy datafile 14 format '/tmp/ADDTBS01.DBF';

Starting backup at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
output file name=/tmp/ADDTBS01.DBF tag=TAG20120603T224508 RECID=5 STAMP=785025915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025915_7wpy3vt1_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> backup as copy datafile 15 format '/tmp/add_tbs_second.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
output file name=/tmp/add_tbs_second.dbf tag=TAG20120603T224538 RECID=6 STAMP=785025939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785025940_7wpy4n49_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> 
RMAN> list backup of tablespace add_tbs completed after "sysdate-1";

specification does not match any backup in the repository

RMAN> list backup of datafile 14;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    530.30M    DISK        00:01:44     23-MAR-12      
        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T231454
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5192430    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    530.70M    DISK        00:01:23     23-MAR-12      
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T235528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5194695    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    1.23M      DISK        00:00:00     24-MAR-12      
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001527
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5196666    24-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    487.60M    DISK        00:00:00     01-JAN-12      
        BP Key: 29   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T112516
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 4956816    01-JAN-12 /oradata/orcl/add_tbs_01.dbf

RMAN> 
Notice how a LIST BACKUP doesn't show the backups ! Why ? Because it shows only BACKUPSET Backups ! If I want to see these BACKUP AS COPY backups, I must use LIST COPY :

RMAN> list copy of datafile 14;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
5       14   A 03-JUN-12       5353830    03-JUN-12      
        Name: /tmp/ADDTBS01.DBF
        Tag: TAG20120603T224508

4       14   A 24-MAR-12       5196322    24-MAR-12      
        Name: /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf


RMAN> list copy of datafile 15;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
6       15   A 03-JUN-12       5353869    03-JUN-12      
        Name: /tmp/add_tbs_second.dbf
        Tag: TAG20120603T224538


RMAN> 
What happens when I lose the datafiles ?

SQL> !rm /oradata/orcl/add_tbs_0[1-2].dbf

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_Tbs online;
alter tablespace add_Tbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/oradata/orcl/add_tbs_01.dbf'


SQL> 
As an aside : Note how the extents and datafiles seemed to be accessible until I tried to ONLINE the datafiles.
Now, I do not have regular backups. Can I use the datafile copies ?  YES.

RMAN> restore datafile 14;

Starting restore at 03-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_DISK_1: restoring datafile 00014
input datafile copy RECID=5 STAMP=785025915 file name=/tmp/ADDTBS01.DBF
destination for restore of datafile 00014: /oradata/orcl/add_tbs_01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00014
output file name=/oradata/orcl/add_tbs_01.dbf RECID=0 STAMP=0
Finished restore at 03-JUN-12

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/tmp/add_tbs_second.dbf"

RMAN> 
I actually restored datafile 14 to the target location but only switched datafile 15 to the copy. This is clearly evidenced by :

SQL> select file_id, file_name  
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /tmp/add_tbs_second.dbf

SQL> 
I can now simply recover the tablespace.

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN> 
and query it now.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;

Tablespace altered.

SQL> alter tablespace add_tbs online;

Tablespace altered.

SQL> select /*+ FULL */ count(*) from hemant.add_table;

  COUNT(*)
----------
    703021

SQL>  
I can also restore datafile 15 by using the same "AS COPY" and "SWITCH" trick.

RMAN> backup as copy datafile 15 format '/oradata/orcl/add_tbs_02.dbf' ;

Starting backup at 03-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/tmp/add_tbs_second.dbf
output file name=/oradata/orcl/add_tbs_02.dbf tag=TAG20120603T230025 RECID=7 STAMP=785026826
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-12

Starting Control File Autobackup at 03-JUN-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_06_03/o1_mf_n_785026826_7wpz0bxo_.bkp comment=NONE
Finished Control File Autobackup at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs offline';

sql statement: alter tablespace add_tbs offline

RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/oradata/orcl/add_tbs_02.dbf"

RMAN> recover tablespace add_tbs;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace add_tbs online';

sql statement: alter tablespace add_tbs online

RMAN> 

SQL> select file_id, file_name
  2  from dba_data_files
  3  where tablespace_name = 'ADD_TBS';

   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /oradata/orcl/add_tbs_02.dbf

SQL> 
Today's question :  When is BACKUP AS COPY [and, optionally, SWITCH DATAFILE TO COPY] really useful ?  Which are the scenarios you would use this facility ?

.
.
.

OEM 12c : New Book

PACKT will be releasing a new book by Porus Homi Havewala : "Oracle Enterprise Manager 12c Cloud Control : Managing Data Centre Chaos".  It is currently available as a Pre-Order.
.
.
.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com