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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016