-> Can be used in normal backup and recovery scenario. This is the code I use for the archival backup: You may have noticed thata sql script ( set_restorepoint.sql) is called by the backup code.. here it is: ============== RESTORE ============== The restore of an archival backup is usually done with the rman duplicate database command, having a connection to the original target database and the catalog, where the duplicatecommand creates a new database ( on the same or different host ) using the archival backup. The following command removes the KEEP attributes of all archived redo log backups (sample output included): Description of the illustration keepoption.gif. Change). 11) run at least basic sanity check (i.e. With version 11.1 rman introduced archival backups ( see https://docs.oracle.com/cd/B28359_01/backup.111/b28270/wnbradv.htm#BRADV021 and

Using a high DOP while creating the backup will almost certainly guarantee that we may use (at least) the same DOP when restoring (to be more precise: ; however running any backup job with a high DOP may not be feasible. Hyper-V vs Vagrant/VirtualBox, Docker, WebLogic Image on Oracle Container Cloud Service, Using bindings to connect Azure Functions to Azure Queue Storage, How to deploy InfluxDB in Azure using a VM service with dedicated storage, Using Azure Artifacts for your own NPM modules. backup and log should contain as much information as possible to restore the database and its configuration. This is obviusly redundat, but I choosed to tolerate this overhead to keep my backup scripts as simple as possible. a long post rman script will look like this: sql alter session set NLS_DATE_FORMAT=dd/mm/yyyy hh24:mi:ss ; Here you can find all the scripts. Normal Restore should use short-term backups which usually is active on Tape Library. RMAN backupsets have a retention period configured with the persistent setting "configure retention policy" syntax. Specifies the time until which the backup or copy must be kept. At last current control file is backed up. Years after you make the archival backup, you could restore and recover it to query the data as it appeared at the time of the backup. All futures backups of archivelog will be kept until. Again, redo log is switched, archived and backed up. ( same OS, same Oracle version ), Grant with grant option as a workaround for ORA-01720, Create manually a sql profile and set your own hints, Flashback database using guaranteed restore point, Datapump feature : Attach to and Manage a running Datapump job, Data Guard : Adjust RMAN' s archivelog deletion policy. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. I feel safer with no automatic optimization in place, as backup optimization could exclude some datafiles from backups. RMAN catalogue is needed both for backup and restore of a database from an archival backup. We can leave this the old setup until the last backup take with KEEP LOGS retention beobsolete. -> their purpose is to produce a snapshot of the database that can be restored on another system for testing . RMAN> change backup tag archival keep until time sysdate+2; Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. The restore point can be used as a shortcut for restore.

'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)'; 3> BACKUP CHECK LOGICAL I manage some environments and I see that some DBAfail tounderstand this difference and end up making incorrect configurations forarchival backup and classic backups.

-> Keep until time can take date expression or specific date. Note: You cannot use KEEP to override the retention policy for files stored in the flash recovery area. KEEP UNTIL TIME 'SYSDATE + 400'

RESTORE POINT BKP_MONTHLY_20160218_1556; channel tape1: SID=241 device type=SBT_TAPE, channel tape1: Data Protection for Oracle: version 5.5.2.0, backup will be obsolete on date Mar 24 2017 15:57:15, archived logs required to recover from this backup will be backed up, channel tape1: starting compressed full datafile backup set, channel tape1: specifying datafile(s) in backup set, input datafile file number=00001 name=+TSM/testdb/datafile/system.501.901826545, input datafile file number=00006 name=+TSM/testdb/datafile/users.487.901826545, input datafile file number=00002 name=+TSM/testdb/datafile/sysaux.542.901826547, input datafile file number=00003 name=+TSM/testdb/datafile/undotbs1.442.901826547, input datafile file number=00004 name=+TSM/testdb/datafile/users.314.901826547, input datafile file number=00005 name=+TSM/testdb/datafile/users.510.901826547, channel tape1: starting piece 1 at Feb 18 2016 15:57:15, channel tape1: finished piece 1 at Feb 18 2016 15:58:40, piece handle=RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1 tag=MONTHLY_FULL_20160218 If you specify a KEEP TIME such as 01-JAN-07, then the backup becomes obsolete one second after midnight on this date.

The general name for a backup created with BACKUP KEEP is an archival backup.; However, as you can read from the MOS notes listed before, this backup cannot be used as a source to run a DUPLICATE command. If available, RMAN uses these backups for disaster recovery restore operations, but their purpose is to produce a snapshot of the database that can be restored on another system for testing or historical usage. Many environments inherit old backup configurations of old DBAs, then is very common to see set up long-term backup environments using backup database keep logs;. Using the FRA to store archivelog will not conflict with these backups; recovery time would be shortened because rman will use archivelog in the FRA and with no need to restore them. tag=MONTHLY_FULL_20160218, channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:45, channel ORA_SBT_TAPE_1: starting archived log restore to default destination, channel ORA_SBT_TAPE_1: restoring archived log, channel ORA_SBT_TAPE_1: reading from backup piece RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1, channel ORA_SBT_TAPE_1: piece handle=RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1 Im using rdbms 11.2.0.4. The database switches the redo logs so it can archive the current online redo log. Although it is not very well documented when you figure out how to configure the keystore and credential store it is quite simple to use. And the other is a partition or directory with enough space to store the backup. Oracle Workflow, a brief introduction Part 1 Setting up an environment, Bloom Filters, Hierarchical Profiling, Synopses & One pass distinct sampling, jumping the GAPP feast for DBAs (considered harmful..), 2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december), Delete play-resources from Azure using runbooks, Deploy A Docker Container To AWS With Azure Pipelines, Azure Pipelines: Using and set up a webapp (part 1), Using a Managed Identity with Azure Automation Accounts, IoT is silo integration using Azure Reference Architecture, Docker, WebLogic Image on Microsoft Azure Container Service, Minikube on Windows. This is the main topic, the reason which made me write down this note. SQL> select SEQUENCE# ,ARCHIVED,STATUS,FIRST_CHANGE# from v$log; RMAN> backup database tag archival format /home/oracle/backup/%U keep until time sysdate+3 restore point qwe; 2. This backup is not part of the disaster recovery strategy. If the database is open during the backup, then RMAN automatically generates and backs up the archived redo logs needed to make the database backup consistent (see Example 2-26). To avoid these issue we will not use rmans configured degree of parallelism and we will explicitly allocate a low number of channel in archival backup scripts. -> you can anytime change any backup piece to have a keep attribute. Restore archivelog all would try to restore all archivelog hence it will always raise errors for any deleted archivelog with no backup.Since we can always find some old archivelog metadata in out catalog, such a check would fail consistently; validating archivelog in the last 36 hours running restore archivelog from time sysdate 36 / 24 validate seems to be reasonable. What about rman optimization and controlfile autobackup? This example makes a database backup with tag Q107 and specifies that it should never be considered obsolete (partial sample output included). 2> SET UNTIL RESTORE POINT BKP_MONTHLY_20160218_1556; channel ORA_SBT_TAPE_1: SID=225 device type=SBT_TAPE, channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0, channel ORA_DISK_1: SID=233 device type=DISK, channel ORA_SBT_TAPE_1: starting datafile backup set restore, channel ORA_SBT_TAPE_1: restoring control file, channel ORA_SBT_TAPE_1: reading from backup piece RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1, channel ORA_SBT_TAPE_1: piece handle=RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1

Our starting point is RMANs documentation and some additional MOS note: This post will go through point 1-10; Ill discuss remaing topics in a part 2. Note: A recovery catalog is only required for KEEP FOREVER. He is specialized in Oracle RAC, Oracle Virtualization, and has a a strong interest in APEX development and PL/SQL. FORMAT 'RMAN_MONTHLY_FULL_%d_%s_%p_%T_%c' This will force rman to keep *ALL* subsequents archivelog backup *as long as the backup will not be deleted*.

This will add some overhead (some archivelogs will appear twice in the archival backup) but this will allow us to run the DUPLICATE command. However, a massive parallel archival backup may slow down concurrent operations. First of all you need re-configure your scripts to long-term backups by using restore point from now. tag=MONTHLY_FULL_20160218, channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01, archived log file name=+TSM/testdb/archivelog/2016_02_18/thread_1_seq_91.267.904150729 thread=1 sequence=91, channel default: deleting archived log(s), archived log file name=+TSM/testdb/archivelog/2016_02_18/thread_1_seq_91.267.904150729 RECID=834 STAMP=904150729, media recovery complete, elapsed time: 00:00:02, new incarnation of database registered in recovery catalog, How to migrate old fashion to new fashion to make long-term backup, Click to share on Facebook (Opens in new window), Click to share on Twitter (Opens in new window), Click to share on LinkedIn (Opens in new window), Click to email a link to a friend (Opens in new window), Click to share on Reddit (Opens in new window), Click to share on Tumblr (Opens in new window), Click to share on Pinterest (Opens in new window), Starting with Oracle In-Memory (aka IM) newFeature, https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV89551. Allrightsreserved. When backup starts, it switched redo log and archives it. If you specify KEEP when backing up to the recovery area, then RMAN issues an error. Keep in mind that such a check is only useful if we inspect its logs rather than its exit code. After change these backups to UNAVAILABLE all Backups of Archivelogs will beorphan and will be obsolete. Yes, There is aworkaround to avoid RMANstill keeping all logs forever, if you are using 11.1 or above. This was not what you were looking for. RMAN> CHANGE BACKUP TAG MONTHLY_FULL_20160218 UNAVAILABLE; backup piece handle=RMAN_MONTHLY_FULL_TESTDB_328_1_20160218_1 RECID=311 STAMP=904147035, backup piece handle=RMAN_MONTHLY_FULL_TESTDB_329_1_20160218_1 RECID=312 STAMP=904147121, backup piece handle=RMAN_MONTHLY_FULL_TESTDB_330_1_20160218_1 RECID=313 STAMP=904147126, backup piece handle=RMAN_MONTHLY_FULL_TESTDB_331_1_20160218_1 RECID=314 STAMP=904147129, connected to target database (not started). 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. Here four backup sets have been generated. Changeto unavailableBackup 1,2,3,4 and archivelogs to restore these database. 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. ->The SCN is captured just after the datafile backups complete. 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_1allocated channel: ch1channel ch1: SID=25 device type=DISK, allocated channel: ch2channel ch2: SID=23 device type=DISK, Starting backup at 26-SEP-13current log archived, backup will be obsolete on date 25-MAR-14archived logs required to recover from this backup will be backed upchannel ch1: starting compressed full datafile backup setchannel ch1: specifying datafile(s) in backup setinput datafile file number=00004 name=/u01/oradata/SILVER/users01.dbfinput datafile file number=00002 name=/u01/oradata/SILVER/sysaux01.dbfinput datafile file number=00009 name=/u01/oradata/SILVER/PM101.DBFinput datafile file number=00010 name=/u01/oradata/SILVER/PM201.DBFinput datafile file number=00011 name=/u01/oradata/SILVER/PM301.DBFchannel ch1: starting piece 1 at 26-SEP-13channel ch2: starting compressed full datafile backup setchannel ch2: specifying datafile(s) in backup setinput datafile file number=00003 name=/u01/oradata/SILVER/undotbs01.dbfinput datafile file number=00001 name=/u01/oradata/SILVER/system01.dbfinput datafile file number=00007 name=/u01/oradata/SILVER/ts_nologging_01.dbfinput datafile file number=00005 name=/u01/oradata/SILVER/ts_data_01.dbfinput datafile file number=00006 name=/u01/oradata/SILVER/TS_UNDO_01.DBFinput datafile file number=00008 name=/u01/oradata/SILVER/ts_archive_01.dbfchannel ch2: starting piece 1 at 26-SEP-13channel ch2: finished piece 1 at 26-SEP-13piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d9okqfgu_1_1 tag=TAG20130926T104236 comment=NONEchannel ch2: backup set complete, elapsed time: 00:00:51channel ch1: finished piece 1 at 26-SEP-13piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_d8okqfgu_1_1 tag=TAG20130926T104236 comment=NONEchannel ch1: backup set complete, elapsed time: 00:01:32, backup will be obsolete on date 25-MAR-14archived logs required to recover from this backup will be backed upchannel ch1: starting compressed full datafile backup setchannel ch1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ch1: starting piece 1 at 26-SEP-13channel ch1: finished piece 1 at 26-SEP-13piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_daokqfjr_1_1 tag=TAG20130926T104236 comment=NONEchannel ch1: backup set complete, elapsed time: 00:00:01current log archivedbackup will be obsolete on date 25-MAR-14archived logs required to recover from this backup will be backed upchannel ch1: starting compressed archived log backup setchannel ch1: specifying archived log(s) in backup setinput archived log thread=1 sequence=213 RECID=1893 STAMP=827145854channel ch1: starting piece 1 at 26-SEP-13channel ch1: finished piece 1 at 26-SEP-13piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dbokqfjv_1_1 tag=TAG20130926T104236 comment=NONEchannel ch1: backup set complete, elapsed time: 00:00:01, backup will be obsolete on date 25-MAR-14archived logs required to recover from this backup will be backed upchannel ch1: starting compressed full datafile backup setchannel ch1: specifying datafile(s) in backup setincluding current control file in backup setchannel ch1: starting piece 1 at 26-SEP-13channel ch1: finished piece 1 at 26-SEP-13piece handle=/u02/fast_recovery_area/SILVER/ARCHIVAL_SILVER_dcokqfk0_1_1 tag=TAG20130926T104236 comment=NONEchannel ch1: backup set complete, elapsed time: 00:00:02Finished backup at 26-SEP-13released channel: ch1released channel: ch2. All rights reserved. I decided to investigate the 11g possibilities of setting a restorepoint within an archival backup, and using this restorepoint to flashback the database back in time.

I have created an archival backup on the EXAMPLE database on BANKSTOWN: Sign in|Recent Site Activity|Report Abuse|Print Page|Powered By Google Sites, Section 1 - Grid Infrastructure: Clusterware and ASM, 11G OCM Data and Data Warehouse Management, 11G OCM Database, RMAN, EM and Network Configuration, General Database and Network Administration, and Backup Strategy, Section 1: New Features of Oracle Database 12c, Oracle VM Implementation and Administration Guide, OCP Oracle Database 11g New Features for Administrators Exam Guide (exam 1Z0-050).

Will try explain infew lines the purpose of Long-Term backups.

We can achieve this storing the archivelog in the FRA and backing them with BACKUP ARCHIVELO ALL NOT BACKED UP with no additional DELETE option.Archivelog will be backed up, say, hourly and they will be only deleted when the database will think it is time to free up some space in the FRA, provided that they have already been backed up.I will manually check that any rman full backup wont last longer than 1 day and that any FRA is large enough to store 2-3 days of archivelog to get this setup working.This way the archival backup shoul always find all needed archivelog still present in the FRA.

I also choosed not to use controlfile autobackup to avoid a few bugs on older, unpatches systems. RMAN provides a convenient way to create a backup and exempt it from your backup retention policy. Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. It can happen though that the business or DBA would like to keep a backup for a longer time. (i.e you dont will be able to recovery that backup).

AGAIN, if you are using 10.2 or later version you CANT do this below. This is the code I use for the restore of an archival backup: The setup of these two files( dbarchivalbackup.cmd, dbarchivalrestore.cmd ), and the 4 sql files called, saves quite some time doing cold backups and cold restores. Here is a demo for you. Note down the lines in bold blue colors carefully. Please note that rman will not only retain the archivelog needed to recover the backup: it will retaing all subsequent archivelog (or their backup). To overcome this limit I added to any archival backup an additional archivelog backup. We do care about RPO, and we will schedule archivelog backup consequently.Since RMAN jobs can be run in parallel, we can schedule rman ordinary backups and rman archival backup without having to worry about overlapping schedules, as our archival backup will not block or prevent other rman jobs. For this reason, the FORMAT string must allow for the creation of multiple backup pieces. restore datafile 1,2 validate or restore tablespace SYSTEM,SYSAUX validate. On this version there is no difference between a classic backup or archival backups.

Change only Backup 1 and Archivelogs to Restore this Backup to unavailable. You might choose to run these checks with a low DOP in order not to overload the backup/storage system. Change). How to use weak ref cursors and bulk collects into a table of objects to clean up data, JHeadstart Querying a Master for its Details for example: search Departments with at least one Clerk, Fun with ADF BC Bind Parameters Part Three: Support for generic bind-parameters in ADF Business Components. The restore point is a label for the SCN to which this archival backup can be restored and recovered, enabling the database to be opened. All archivelogs that does not have status UNAVAILABLE until 01/02/2016 12:00 will be obsolete and will deleted on next delete obsolete. If you specified the LOGS attribute for hot backups none of the archived logs backups created after the archival backup were deleted. -> Changing any backup piece to have a keep attribute will allow backup piece to exempt itself from retention policy.

All Archivelogs Backups will be obsolete and RMAN will automaticaly deleted it. The archived redo logs necessary to make the datafiles consistent are included in the backup set. 2> ALLOCATE CHANNEL tape1 It seens to me a bug, because Oracle just ignore retention policy of archivelog log, if Backup with Keep clause is UNAVAILABLE. Change), You are commenting using your Twitter account. FULL DATABASE You can ask: I dont want to keep all logs and want to keep only my backups which is not obsolete? Recovery Manager: Release 11.2.0.4.0 - Production on Thu Feb 18 15:56:59 2016.

Change), You are commenting using your Twitter account. So I ended up with backing up the spfile as pfile, as an add-on to the archival backup check out the backup code I provided before -, and using this pfile to restore the original spfile, as part of the rman restore/recovery. Please note that both ordinary rman backups and archival rman backups must backup the same archivelog independently: we must ensure that the archivelog backup will not delete any archivelog needed by the archival backup.