Why doesn't oracle full backup + archivelog NOT include the online redo logs?
我的总结就是如果备份了online log，恢复时到online log整个db就不再恢复了，而这个可以用alter system with logfile实现。如果还要最新的就是扯淡了，因为你永远备份不了最新的db。而且恢复的时候基于时间点的恢复，可以用后续的archive log任意恢复。而如果apple 了online log ，就不能继续恢复了？（应该也是可以的，继续基于SCN读取archived log中的内容。
Oracle recommends you do not copy a current online log because when you do so and restore that copy, the copy will appear at the end of the redo thread. However, additional redo may have been generated in the thread. Therefore, if you attempt to execute recovery supplying the redo log copy, recovery will erroneously detect the end of the redo thread and prematurely terminate, possibly corrupting the database.
------------------------------------------------
I can't get my head around understanding this Oracle design decision - not even after 10 years.
If I run a full/whole backup for database + archive log it will cause database to write the latest transaction to the online redo log file but it will not back up the online redo logs. why? - this seems bad design, no?
This means the latest commit after I start backup will not be saved because it's written to the online redo log file. (unless I do something smart like log switching)
This design raises more question then answers, and Oracle never answers these questions which is odd. Why they design it this way? Is there something in Oracle doc about the reason why they don't
I hope someone from @Oracle Support reads this, thank you
I feel it's a big deal, sorry if stupid question
-
Hello Ayman2,
Yes, Oracle does the 'alter system switch logfile' internally whenever you issue a backup archivelog all.
ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT will force a log switch, but they do it differently!
Both the SWITCH LOGFILE and ARCHIVELOG CURRENT write a quiesce checkpoint, a firm place whereby that last redo log is a part of the hot backup, but ARCHIVELOG CURRENT waits for the writing to complete. This can take several minutes for multi-gigabyte redo logs.
Conversely, the ALTER SYSTEM SWITCH LOGFILE command is swift and returns control to the caller in less than a second while ALTER SYSTEM ARCHIVELOG CURRENT pauses.
ALTER SYSTEM SWITCH LOGFILE is asynchronous; this command is fast to return to the invoking program because the writing of the redo log to the OS filesystem is done in the background. There is minimal risk in cases where the ARCH process cannot complete writing the redo log, such as cases where the OS archive log file directory is out of space. It is also risky because the calling script may move on to a subsequent step, assuming that the redo has been written. Some scripts will place a SLEEP 60 command in their backup script to allow time for the redo to complete writing, but this is not a best practice.
ALTER SYSTEM ARCHIVELOG CURRENT is synchronous; this is faster to return because this command waits until the online redo log has completed the writing of the redo log file to the filesystem. Furthermore, this command is safer because it waits for the OS to acknowledge that the redo log has been successfully written. Hence, ALTER SYSTEM ARCHIVELOG CURRENT is the best practice for production backup scripts with RMAN.
Another significant difference, If you are running RAC, the ALTER SYSTEM ARCHIVELOG CURRENT will switch the logs on all RAC nodes. In contrast, ALTER SYSTEM SWITCH LOGFILE will only switch the logfile on the instance where you issue the switch command. Hence, ALTER SYSTEM ARCHIVELOG CURRENT is a best practice for RAC systems.
I don't know if I could answer your question or not :D.
-
Hello Ayman2,
Oracle recommends you do not copy a current online log because when you do so and restore that copy, the copy will appear at the end of the redo thread. However, additional redo may have been generated in the thread. Therefore, if you attempt to execute recovery supplying the redo log copy, recovery will erroneously detect the end of the redo thread and prematurely terminate, possibly corrupting the database.
Cheers
Arsalan
FlagQuoteOff TopicLike
-
Ayman2 Posts: 341 Silver Badge
Feb 10, 2023 4:01AM edited Feb 10, 2023 4:05AM
hi @Arsalan Dehghanisariyarghan I'm asking about why oracle doesn't include the latest commit before the backup command is issued. Transactions after/during backup operation are not included and would be stored in the redo log but why doesn't the oracle backup include the last commit before the backup started?
very curious design choice. I feel I might be missing something but I feel I've not seen this addressed anywhere
FlagQuoteOff TopicLike
-
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,913 Tanzanite
Feb 10, 2023 4:24AM
Hi Ayman2,
I presume you are talking about online backups due to the reference to archive logs. Regarding your questions/concerns the main aspect is the "backup mode". In this situation the DB is up and running (with transactions going on). In essence while backup is going on the 'dbwr' background process could be writing to the underlying DB files. So there is likelihood of a block being fractured. This in turn leads to SCN being inconsistent. These needs to be addressed (by the backup tool) so that the backup is usable (if needed for restore/recovery). Of course with RMAN this is taken care of inherently.
This means the latest commit after I start backup will not be saved because it's written to the online redo log file
Once again if RMAN is being used this would be handled automatically. Care needs to be taken though in the backup commands/scripting being used. There are few options available - for example use the clause "backup ... archivelog all" (there few alternative clauses as well). In essence internally results in -
archive log current backups all archive logs (with optimization skips if any of the archive logs already backed up) Backups data files of the DB archive log current (this would take care of the above concern related to the latest commit) Any archive logs generated while the backup was going on gets backed up as well
Do make sure to have the control file backup later at the end so that the above details gets recorded. This would now result in a consistent backup.
Cheers -- Suresh
FlagQuoteOff Topic2Like
-
Ayman2 Posts: 341 Silver Badge
Feb 10, 2023 9:10AM edited Feb 10, 2023 9:11AM
hi @SureshMuddaveerappa
your answer is great. I always thought of backup as a save all button so I expect implicitly for Oracle to save the last commit to archive log before backup starts. Not sure why they are using their left hand to touch their right ear (so to speak). Seems like odd design to me. I've yet to hear a good reason for not automating that.
Maybe I need to study the topic more but it's been a decade hmm.. perhaps I should start review some things.
thanks for excellent advice.
It's odd that @Oracle Support or anyone else outside of Oracle doesn't have white paper link or something. I feel this is general gap in industry maybe
FlagQuoteOff Topic1Like
-
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,913 Tanzanite
Feb 10, 2023 11:04AM
Thanks Ayman2. There are a few docs available on support and elsewhere. I would start with Oracle docs. Check out -
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-backup-concepts.html#GUID-10871A83-DD0B-49FB-9601-A598D367ECD3 Doc ID 881395.1, Doc ID 1527806.1
Hope this helps -- Cheers -- Suresh
FlagQuoteOff TopicLike
-
Ayman2 Posts: 341 Silver Badge
Feb 10, 2023 2:01PM edited Feb 10, 2023 2:07PM
hi @SureshMuddaveerappa
Chapter 7, page 187 of "RMAN Recipes for Oracle Database 12c" says
And also Chapter 7, page 225 of "RMAN Recipes for Oracle Database 11g" says
When you issue the backup command with the "archivelog all" or "plus archivelog" clause, either of these
commands will back up the archived redo logs, and RMAN first directs the database to switch the current online redo log group. After this, all unarchived redo logs, including the one the database just switched out of, are archived.
This process guarantees that the backup contains all the redo information generated until the backup started.
To me this is confusing because "archivelog all" I thought, just backups the archived redo logs. It doesn't do internal redo log switch and online archival. Also in exactly same page they go on to say the opposite!
When you use the backup database plus archivelog command to back up archive logs as part of another
backup, RMAN will perform the following operations in the sequence listed here:
1. Run the alter system archive log current command. 2. Run the backup archivelog all command. 3. Back up the rest of the data files specified by the backup database command. 4. Run the alter system archive log current command. 5. Back up the new archive logs generated during the backup operation.
So why they separate point "1." and point "2."? Earlier, on the exact same page, they said "archivelog all" makes RMAN do an internal log switch and archival, so why do they need point "1." which says "alter system archive log current"?
So if oracle support doesn't know, and three authors in RMAN recipe don't know, then who knows!! :)
I feel RMAN has too many gaps in knowledge and my head is going to hurt hehe
FlagQuoteOff TopicLike
-
Feb 10, 2023 4:37PM edited Feb 10, 2023 4:44PM Answer ✓
Hello Ayman2,
Yes, Oracle does the 'alter system switch logfile' internally whenever you issue a backup archivelog all.
ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVELOG CURRENT will force a log switch, but they do it differently!
Both the SWITCH LOGFILE and ARCHIVELOG CURRENT write a quiesce checkpoint, a firm place whereby that last redo log is a part of the hot backup, but ARCHIVELOG CURRENT waits for the writing to complete. This can take several minutes for multi-gigabyte redo logs.
Conversely, the ALTER SYSTEM SWITCH LOGFILE command is swift and returns control to the caller in less than a second while ALTER SYSTEM ARCHIVELOG CURRENT pauses.
ALTER SYSTEM SWITCH LOGFILE is asynchronous; this command is fast to return to the invoking program because the writing of the redo log to the OS filesystem is done in the background. There is minimal risk in cases where the ARCH process cannot complete writing the redo log, such as cases where the OS archive log file directory is out of space. It is also risky because the calling script may move on to a subsequent step, assuming that the redo has been written. Some scripts will place a SLEEP 60 command in their backup script to allow time for the redo to complete writing, but this is not a best practice.
ALTER SYSTEM ARCHIVELOG CURRENT is synchronous; this is faster to return because this command waits until the online redo log has completed the writing of the redo log file to the filesystem. Furthermore, this command is safer because it waits for the OS to acknowledge that the redo log has been successfully written. Hence, ALTER SYSTEM ARCHIVELOG CURRENT is the best practice for production backup scripts with RMAN.
Another significant difference, If you are running RAC, the ALTER SYSTEM ARCHIVELOG CURRENT will switch the logs on all RAC nodes. In contrast, ALTER SYSTEM SWITCH LOGFILE will only switch the logfile on the instance where you issue the switch command. Hence, ALTER SYSTEM ARCHIVELOG CURRENT is a best practice for RAC systems.
I don't know if I could answer your question or not :D.
Cheers
Arsalan
FlagQuoteOff TopicLike
-
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,913 Tanzanite
Feb 10, 2023 9:46PM
Hello @Ayman2 ,
You are right, at times some of the docs can be a little confusing/tricky and/or ambiguous. Regarding the note of -
... To me this is confusing because "archivelog all" I thought ...
The key aspect here this is a composite command. That is internally it would translate to 2 sub commands of 'backup' + 'archivelog all'. This would in turn result in the mentioned behavior.
... will perform the following operations in the sequence listed ...
Here it gets into more finer details of the internal workings of online/hot backups with the composite command I mentioned above. For example say just before the start of the online backup there exists archive log sequence <n> through <n+x>.
With step 1 results in switching of redo now resulting in the new archive log sequence <n+x+1>. Later with step2 it would result in backing all of the archive logs <n> through <n+x+1> in the background. Note that the archive logs are always outside of DB dependent on your initialization parameter setting.
I have setup such backups (and have used for restore/recovery as well) for both clustered and non-clustered environments. These have been working well.
Cheers -- Suresh
FlagQuoteOff Topic1Like
-
Mark D Powell DBA Posts: 17,366 Rubellite
Feb 10, 2023 11:09PM
Ayman2, something to remember the online redo logs are only necessary for recovery when recovering to the last running time of the database instance such as when crash recovery is performed or you are restoring to the point of failure.
When the backup includes the archive logs you are generally attempting to create a complete backup set that contains everything necessary to restore the database to point in time at the end of the backup such as when creating a year-end archive. Otherwise, you are probably running your database backups and archive log backups on separate schedules such as level 0 weekly, level 1 daily, and archive log every N hours.
The official documentation is often written at a high level that describes in general what is being done. The detail of how the functionality is actually provided is not necessarily provided by the documentation. Other times the documentation still needs updating for changes Oracle has made and on occasion the documentation has actually reflected how something was supposed to work in a specific release, but the code changes did not take place. So read, think, test where possible, but most importantly understand what you need to do to use the feature successfully. If you set up your rman tasks per the guidelines, you will be able to restore.
HTH -- Mark D Powell --
FlagQuoteOff Topic1Like
-
Ayman2 Posts: 341 Silver Badge
Feb 12, 2023 6:45AM edited Feb 12, 2023 9:27AM
Wow @Arsalan Dehghanisariyarghan this is gold. Thank you Sir!
Are you sure it's a switch log file, and not " ALTER SYSTEM ARCHIVELOG CURRENT" that's invoked internally with "backup archivelog all"?
Can you see line 3 below, it says "current log archived". This make me think that "backup archivelog all" invokes "alter system archivelog current" internally, no? Sorry to be so pedantic but this is really racking my head
I would really appreciate your input, this is the log file for "backup archivelog all" There are three redo log groups per rac instance. Each group has two redolog files. There are two rac instances. So the RAC database has (3 groups x 2 instances) 6 redo log groups in total .
RMAN> 2> 3> 4> Starting backup at Feb 11 2023 15:30:03 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=799 instance=OT1QA901 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=86541 RECID=150806 STAMP=1128520902 input archived log thread=2 sequence=64272 RECID=150807 STAMP=1128522408 input archived log thread=1 sequence=86542 RECID=150808 STAMP=1128522622 channel ORA_DISK_1: starting piece 1 at Feb 11 2023 15:30:10 channel ORA_DISK_1: finished piece 1 at Feb 11 2023 15:30:25 piece handle=/oracle_data/rman_backup/OT1QA90/backup_DB_OT1QA90_S_52831_P_1_T_1128526209.bak tag=FULL_DB_COPY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: deleting archived log(s) archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_1_86541__8g3rlmh9_.arc RECID=150806 STAMP=1128520902 archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_2_64272__65wg66y9_.arc RECID=150807 STAMP=1128522408 archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_1_86542__8hq0rv63_.arc RECID=150808 STAMP=1128522622 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=2 sequence=64273 RECID=150811 STAMP=1128526207 input archived log thread=1 sequence=86543 RECID=150809 STAMP=1128525413 input archived log thread=1 sequence=86544 RECID=150810 STAMP=1128526204 channel ORA_DISK_1: starting piece 1 at Feb 11 2023 15:30:25 channel ORA_DISK_1: finished piece 1 at Feb 11 2023 15:30:32 piece handle=/oracle_data/rman_backup/OT1QA90/backup_DB_OT1QA90_S_52832_P_1_T_1128526225.bak tag=FULL_DB_COPY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: deleting archived log(s) archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_2_64273__8m1vnlsk_.arc RECID=150811 STAMP=1128526207 archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_1_86543__8lb6ot0r_.arc RECID=150809 STAMP=1128525413 archived log file name=/oracle_data/fra/OT1QA90/archivelog/2023_02_11/o1_mf_1_86544__8m1t5lbs_.arc RECID=150810 STAMP=1128526204 Finished backup at Feb 11 2023 15:30:32Starting Control File and SPFILE Autobackup at Feb 11 2023 15:30:32 piece handle=/oracle_data/rman_backup/OT1QA90/backup_CF_c-3960287428-20230211-06.ctl comment=NONE Finished Control File and SPFILE Autobackup at Feb 11 2023 15:30:34 ...
Thank you so much
Thank you @SureshMuddaveerappa and @Mark D Powell for your extensive and useful responses
FlagQuoteOff TopicLike
-
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,913 Tanzanite
Feb 12, 2023 1:33PM
Hello @Ayman2 ,
On the RMAN run log entries -
current log archived (of line 3)
This internally is due to forcing a log switch and thereby results in archiving the current log group. This is what I had mentioned earlier in my first response - "In essence internally results in - archive log current ..." and the above line 3 validates the same.
Since you are running a 2 node RAC the same is also reflected in the archive logs from the respective threads of thread 1 and thread 2.
... this is the log file for "backup archivelog all" ...
Is this part of 2 separate "backup" commands? For example as in -
backup ... database; backup archivelog all ...;
The alternative being -
backup ... database plus archivelog ...;
Based on above the internal sequence of operations would differ. However RMAN would handle the archive log that is accessible for the RAC instance/node. The number of redo log groups in the RAC instance has no impact on the archive log backups. Note that archive logs per se is "outside" of the DB files.
Cheers -- Suresh
FlagQuoteOff Topic1Like
-
Ayman2 Posts: 341 Silver Badge
Feb 25, 2023 9:15AM
hi,
Thank you @Arsalan Dehghanisariyarghan for gold reply that I will refer back to that explains the nuts and bolts of the insides as I was really scratching my head about the RMAN recipe books (I think this book is over rated) and also the oracle documentation which seemed everywhere (in my opinion only)
Thank you @SureshMuddaveerappa you are right about answering my question indeed, thank you. And thank you for the follow up answer and also for your patients Sir!! <3
Thank you @Mark D Powell for your insights and expertise
Best Regards
FlagQuoteOff TopicLike
-
Mr. C Posts: 1,279 Silver Trophy
Mar 24, 2023 9:03PM edited Mar 24, 2023 9:19PM
Why doesn't oracle full backup + archivelog NOT include the online redo logs?
Ayman2
Feb 10, 2023
hi,
I can't get my head around understanding this Oracle design decision - not even after 10 years.
If I run a full/whole backup for database + archive log it will cause database to write the latest transaction to the online redo log file but it will not back up the online redo logs. why? - this seems bad design, no?
This means the latest commit after I start backup will not be saved because it's written to the online redo log file. (unless I do something smart like log switching)
This design raises more question then answers, and Oracle never answers these questions which is odd. Why they design it this way? Is there something in Oracle doc about the reason why they don't
I hope someone from @Oracle Support reads this, thank you
I feel it's a big deal, sorry if stupid question
thanks
View Post
There is a valid point in there, but the design as chosen by Oracle implies the fact that there is some delay in time the moment you start paying attention to the time of backup, but looking into exact minutes of differences. This specific issue does NOT exist with classic cold backup, you know, the backup without RMAN, shutting down your database, taking a full backup. If you want a lot of assurance about a specific time of backup, consider that option.
But the online, RMAN type of backup just has this setup: the data which is in the current Redos, is not candidate for backup, until a Log Switch occurs. This may be manual or automatic, and here is the trick: it is the DBA's responsibility to have the switches as frequent as needed. You know that you can't take a full backup of a database in 20 seconds, so you must acknowledge that your backup takes some time. And, by the time your backup has completed, it actually is already obsolete, if you count minutes. So, have a check on that: how long does a full backup take, but also: how long are your deltas in between switches (on average). If the switches occur way too few, that is where your problem is.
It's a bit like: what if your database crashes, and a developer complains he has lost a lot of data, Then it turns out he was doing only 1 commit, until the end of the transaction. His process may be quick, correct, perfect in all ways .. until the database crashes mid session. Not that there is lots of chance of that occuring, but just saying that technically that is what may happen.
Accuracy of data for backups requires recent Log Switches, and recent Commits. The first is the responsibility of the DBA, the latter is a developer responsibility.
The RMAN tool itself has so many options, it's hard to blame it non working, in not all options are being used. For example, what if you take an Archivelog backup, directly after every full backup (whatever level) ?
FlagQuoteOff TopicLike
-
Mar 24, 2023 10:08PM
Ayman2, you are welcome. Keep in mind when using a third-party book as reference when the first edition was published and what edition was current at the time. The book could be accurate for the edition it was written against, but enhancements Oracle provided with new releases may not be reflected in the book even if the book is marked as updated. I seen complaints by more than one author about the publisher limiting the amount of change that could go into the update. It is always wise to consult the official documentation for the version in question and compare the information before implementing a feature.
HTH -- Mark D Powell --
FlagQuoteOff Topic1Like
-
Ayman2 Posts: 341 Silver Badge
Mar 25, 2023 12:50AM
Thanks Mark
In the past the authors used to maintain an errata but I think it is not available anymore.