Thanks for the question, alan.

Asked: July 10, 2022 - 6:28 pm UTC

Last updated: October 26, 2022 - 3:22 am UTC

Version: 19.5

Viewed 10K+ times! This question is

You Asked

Hi all

from the documentation - issuing plus archivelog does the following

1) Run the ALTER SYSTEM ARCHIVE LOG CURRENT statement.

2) Run BACKUP ARCHIVELOG ALL.

3) Back up the rest of the files specified in the BACKUP command.

4) Run the ALTER SYSTEM ARCHIVE LOG CURRENT statement.

5) Back up any remaining archived logs generated during the backup.


The preceding steps guarantee that data file backups taken during the command are recoverable to a consistent state.

I am trying to understand if step 1 and 2 is necessary to ensure the backup are recoverable to consistent state ? if yes - why ?

if a database change is currently recorded in the ORL and the change data is not flush to disk yet - surely there will be a log switch during the backup of the database and the ORL will get archived and backup isn't it ?

in any case - step 4 will also do a log switch and archive the archivelogs

so why is step 1 and 2 necessary ?

Thank you

and Connor said...

It is to handle various catastrophic scenarios

For (1) and (2) - We're assuming that an archivelog backup will normally be much quicker than a full database backup. Lets assume we do NOT do 1 and 2. We're backing up the database and we lose the whole machine. Not only do we not have a backup, we don't have our current archivelogs either. We're now at a lost data scenario where we only have up our *previous* archivelog backup.
If we backup archivelogs first, then a disaster during database backup means we can still roll forward to almost the point of diaster.

For (4) and (5), if our backups are done off-site etc, then by grabbing all archivelogs *after* the database is backed up, we can now guarantee that this backup can be used in isolation to get our database to a working state. If we didn't do (4) and (5), then yes we could restore the database, but we will then need additional archive files from some other place (eg a subsequent backup).

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

removing plus archivelog

alan koh, July 11, 2022 - 5:19 pm UTC

hi conner

thank you for the reply and the rationale on 1 ) and 2)

i do hope to have your opinion on the below backup strategy without plus archivelog

1) i need to have a 30min RPO so i am backing up the archivelog every 30 minutes

backup archivelog all delete input

2) everday at 1am - i am also doing monday full backup and cumulative backup on the rest of the days - the backup is run with plus archivelog delete input;

backup database plus archivelog delete input

i am afraid that 1) and 2) might run concurrently causing some archivelog to be deleted before it is backup thus i have removed the 'plus archivelog delete input' from the backup database command

it is currently just

backup archivelog all delete input -- every 30 mins
backup incremental level 0 database -- every sunday
backup incremental level 1 cumulative database -- monday to saturday

3) i do not have a large FRA area - so after backing up the archivelog i need them to be deleted

=========================================================

the purpose of doing backup database plus archivelog is so that i am able to restore the database to a consistent state using this 1 backup

but if i am backing up archivelog every 1/2 hour with delete input - it means that some of the archivelogs would have already been deleted while the backup database is running - this will defeat the purpose of backup database plus archivelog

q1) do you think my current backup strategy is sound ? is it okay for me to remove the 'plus archivelog' from the backup database command ?

do i lose out anything other then losing the capbility to restore the database to a consistent state in 1 single backup

Thank you

July 13, 2022 - 2:50 am UTC

1) 30min RPO

I would grab another server in your network that already runs Oracle (eg a dev box) and use it to host a DataGuard copy of your production database. Now you have your archivelogs being automatically transferred to a second server (or even second site)

2) to avoid concurrent running, its easy to do a simple OS check via "ps" or use a file as a semaphore, eg

if [ -f backup_in_progress ] then
  echo Backup aborted, someone else running
  exit 1
else
  touch backup_in_progress
  -- run backup
  rm backup_in_progress
fi

To answer (3) - seriously look at a DataGuard copy. It blows my mind people dont do this for every database

alan koh, July 11, 2022 - 5:23 pm UTC

thank you Connor - apologies on the typo as i am not to edit further

alan koh, July 13, 2022 - 5:24 pm UTC

hi Connor

Thanks for the reply

Actually this backup i am doing is currently on the dataguard database server

The RPO of 30 minutes is really based on ransomware scenario whereby prod / standby server are held ransom

I am just stuck in the dilemma if i should still include "plus archivelog" in my backup database statement - as i am already doing

backup archivelog all delete input - every 30 minutes
backup incremental level 0 database - every monday
backup incremental level 1 cumulative database - tues to sunday

if i still include the plus archivelog in the backup database statement - it means i cannot run the backup archivelog all delete input while the database is being backup as the archivelog will get deleted

if the backup of the database took 6 hours to run - then essentially i will not meet the 30 minutes RPO as the archivelogs will not be backup during that period

so i am trying to make sense if it is still recommended to include the plus archivelog statement to the backup database command

hope to hear your thoughts

July 14, 2022 - 1:47 am UTC

I thnk you'll be fine - just be aware that the backup of the database you are taking will probably always need access to other backups (of the archive logs) in order to build a consistent version.

This really only has impact if you're doing different things with different backups (eg retention, off/on site) etc

rman catalog point in time recovery

alan koh, October 12, 2022 - 5:08 pm UTC

hi Connor

hope you are still around the corner

it came to my understanding that controfile is backup when

1) datafile #1 system is backup
2) autobackup is turn on
3) include current controlfile to backup database statement

i have always done PITR recovery using the latest controlfile autobackup as i assume it will always have the latest and all information of the backups and archivelogs required

Assuming database/controlfile/spfile is lost and all 1,2,3 is done during backup

q1) when using RMAN CATALOG to do PITR - how does oracle choose which controlfile to restore ? the closest controlfile backup after the PITR ?

q2) it seems to not be the case in Doc ID 1543996.1 - is it because the command issue is restore controlfile ? if i were to specifically restore the latest controlfile and assume that the required backup information for the PITR is still kept in the controlfile - i will no problem doing the recovery right ?

Thank you

October 26, 2022 - 3:22 am UTC

1) Once you do "set until" that tellls RMAN which controlfile(s) are candidates. It would pick one that contains all the backupsets and archives to achieve the point in time requested.

2) The example in the MOS note is about a totally self-contained backup, ie, the only controlfile you have access to is the one in the backup. In that instance, you would need to use the script recommended in the note to ensure the controlfile timing is correct.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer