I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
18 June, 2012
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
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 :
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>
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>
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>
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>
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>
.
.
.
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.
.
.
.
.
.
.
01 June, 2012
Subscribe to:
Posts (Atom)