19 | 08 | 2017
Latest Articles
Popular Articles

RMAN

RMAN Archival backups with restore points

User Rating:  / 1
PoorBest 

RMAN Archival backups with restore points

 

Reworked on September 26 2013

 

RMAN backupsets have a retention period configured with the persistent setting "configure retention policy" syntax. Once the backup becomes eligible for deletion it is purged with the "delete obsolete" command. It can happen though that the business or DBA would like to keep a backup for a longer time. See also Keep Backupsets

No problem at all since backups can be taken with the attribute "keep until time 'sysdate+365'" or "keep forever" 

However with previous releases the dba needed to add the attribute NOLOGS or LOGS

For cold backupsThe NOLOGS attribute was perfect, but the NOLOGS couldn' t be used for hot backups since you need at least 1 archivelog backed up to recover this backup to a consistent state). If you specified the LOGS attribute for hot backups none of the archived logs backups created after the archival backup were deleted. This was not what you were looking for.

A really useful 11G new feature are the archival backups optionally with restore points. The restore point can be used as a shortcut for restore. ( sounds logic ) . The biggest gain is that the archival backup is self contained, it contains all the files, database file backups AND archivelogs backups to recover this backup to a consistent state AND only the archivelog backups to recover this backup are flagged as to be kept.

 


RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> backup as compressed backupset check logical database format '/u02/fast_recovery_area/SILVER/ARCHIVAL_%d_%U' keep until time 'sysdate+180' restore point 'BEFORE_UPGRADE';
5> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=25 device type=DISK

allocated channel: ch2
channel ch2: SID=23 device type=DISK

Starting backup at 26-SEP-13
current log archived

backup will be obsolete on date 25-MAR-14
archived logs required to recover from this backup will be backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/SILVER/users01.dbf
input datafile file number=00002 name=/u01/oradata/SILVER/sysaux01.dbf
input datafile file number=00009 name=/u01/oradata/SILVER/PM101.DBF
input datafile file number=00010 name=/u01/oradata/SILVER/PM201.DBF
input datafile file number=00011 name=/u01/oradata/SILVER/PM301.DBF
channel ch1: starting piece 1 at 26-SEP-13
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/oradata/SILVER/undotbs01.dbf
input datafile file number=00001 name=/u01/oradata/SILVER/system01.dbf
input datafile file number=00007 name=/u01/oradata/SILVER/ts_nologging_01.dbf
input datafile file number=00005 name=/u01/oradata/SILVER/ts_data_01.dbf
input datafile file number=00006 name=/u01/oradata/SILVER/TS_UNDO_01.DBF
input datafile file number=00008 name=/u01/oradata/SILVER/ts_archive_01.dbf
channel ch2: starting piece 1 at 26-SEP-13
channel ch2: finished piece 1 at 26-SEP-13
piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d9okqfgu_1_1 tag=TAG20130926T104236 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:51
channel ch1: finished piece 1 at 26-SEP-13
piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d8okqfgu_1_1 tag=TAG20130926T104236 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:32

backup will be obsolete on date 25-MAR-14
archived logs required to recover from this backup will be backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 26-SEP-13
channel ch1: finished piece 1 at 26-SEP-13
piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_daokqfjr_1_1 tag=TAG20130926T104236 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01

current log archived
backup will be obsolete on date 25-MAR-14
archived logs required to recover from this backup will be backed up
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=213 RECID=1893 STAMP=827145854
channel ch1: starting piece 1 at 26-SEP-13
channel ch1: finished piece 1 at 26-SEP-13
piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dbokqfjv_1_1 tag=TAG20130926T104236 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 25-MAR-14
archived logs required to recover from this backup will be backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 26-SEP-13
channel ch1: finished piece 1 at 26-SEP-13
piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dcokqfk0_1_1 tag=TAG20130926T104236 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-SEP-13
released channel: ch1
released channel: ch2

 

RMAN> list backup tag='TAG20130926T104236';

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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
387 Full 180.11M DISK 00:00:49 26-SEP-13

BP Key: 387 Status: AVAILABLE Compressed: YES Tag: TAG20130926T104236
Piece Name: /u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d9okqfgu_1_1
Keep: BACKUP_LOGS Until: 25-MAR-14

List of Datafiles in backup set 387
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 41435352 26-SEP-13 /u01/oradata/SILVER/system01.dbf
3 Full 41435352 26-SEP-13 /u01/oradata/SILVER/undotbs01.dbf
5 Full 41435352 26-SEP-13 /u01/oradata/SILVER/ts_data_01.dbf
6 Full 41435352 26-SEP-13 /u01/oradata/SILVER/TS_UNDO_01.DBF
7 Full 41435352 26-SEP-13 /u01/oradata/SILVER/ts_nologging_01.dbf
8 Full 41435352 26-SEP-13 /u01/oradata/SILVER/ts_archive_01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
388 Full 544.72M DISK 00:01:28 26-SEP-13
BP Key: 388 Status: AVAILABLE Compressed: YES Tag: TAG20130926T104236
Piece Name: /u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d8okqfgu_1_1
Keep: BACKUP_LOGS Until: 25-MAR-14

List of Datafiles in backup set 388
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 41435351 26-SEP-13 /u01/oradata/SILVER/sysaux01.dbf
4 Full 41435351 26-SEP-13 /u01/oradata/SILVER/users01.dbf
9 Full 41435351 26-SEP-13 /u01/oradata/SILVER/PM101.DBF
10 Full 41435351 26-SEP-13 /u01/oradata/SILVER/PM201.DBF
11 Full 41435351 26-SEP-13 /u01/oradata/SILVER/PM301.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
389 Full 80.00K DISK 00:00:01 26-SEP-13
BP Key: 389 Status: AVAILABLE Compressed: YES Tag: TAG20130926T104236
Piece Name: /u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_daokqfjr_1_1
Keep: BACKUP_LOGS Until: 25-MAR-14

SPFILE Included: Modification time: 26-SEP-13
SPFILE db_unique_name: SILVER

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
390 84.50K DISK 00:00:00 26-SEP-13
BP Key: 390 Status: AVAILABLE Compressed: YES Tag: TAG20130926T104236
Piece Name: /u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dbokqfjv_1_1
Keep: BACKUP_LOGS Until: 25-MAR-14

List of Archived Logs in backup set 390
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 213 41435308 26-SEP-13 41435405 26-SEP-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
391 Full 1.17M DISK 00:00:04 26-SEP-13

BP Key: 391 Status: AVAILABLE Compressed: YES Tag: TAG20130926T104236
Piece Name: /u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dcokqfk0_1_1
Keep: BACKUP_LOGS Until: 25-MAR-14

Control File Included: Ckp SCN: 41435410 Ckp time: 26-SEP-13

 

 

SQL> select scn,guarantee_flashback_database,name from v$restore_point;

SCN              GUA NAME
---------------- --- ------------------------------
41435395         NO  BEFORE_UPGRADE