{"id":33,"date":"2010-04-03T02:36:49","date_gmt":"2010-04-03T07:36:49","guid":{"rendered":"http:\/\/www.mcneelytech.com\/blog1\/?p=33"},"modified":"2024-06-18T13:11:25","modified_gmt":"2024-06-18T18:11:25","slug":"common-oracle-mistakes-small-businesses-make-mistake-2","status":"publish","type":"post","link":"http:\/\/www.mcneelytech.com\/blog1\/?p=33","title":{"rendered":"Common Oracle mistakes small businesses make \u2013 mistake #2"},"content":{"rendered":"

Mistake #2: Choosing (or defaulting to) an inappropriate archivelog setting<\/strong><\/p>\n

Let\u2019s say you back up your database nightly.  If your database were obliterated in a hardware failure at 3:58 pm, would it be important to recover the changes made to the database since last night?  If it’s a training or test database, or a production database that doesn’t change much, perhaps just the restoring database backup from last night would be fine.  Otherwise, you probably would be interested in recovering the work that’s been done since last night.  (And having users insistently and repeatedly ask you when they\u2019ll get their data back would only serve to increase your interest in completing the recovery.)<\/p>\n

Remember the online redo logs from Mistake #1?  As we discussed, they are vital to database recoveries.  Let’s talk more about how they work.<\/p>\n

Databases have more than one redo log.  Let’s say that our database has three redo logs (logs 1, 2, and 3).  Let’s also say it takes about 30 minutes for a redo log to fill.  When log 1 fills with transactional data, it closes, and log 2 begins writing.  When log 2 fills, log 3 starts writing.  When log 3 fills, log 1 gets overwritten.<\/p>\n

Losing the information in log 1 is fine, as long as you don’t need it later.  You can get copies of each redo log before it is overwritten.  These are called archived redo logs, and they are only generated if the database is running in archivelog mode.<\/p>\n

Now, back to our 3:58 pm database obliteration scenario.  Let\u2019s say your backup was made at 3 am last night; you are running in archivelog mode; and you back up your archive logs every hour, on the hour.<\/p>\n

You would restore your backup from 3 am, then “apply” the archived redo logs made since the 3 am backup.  Applying redo logs replays the transactions stored in the redo logs, “applying” them to your restored database.  This is how you get your database changes back since this morning.  You’d be able to replay enough transactions to get your database to the state it was in at 3 pm when you made your hourly archivelog backup.  (I’m oversimplifying here to more easily illustrate the concept.)<\/p>\n

If you weren’t running in archivelog mode, you’d restore your backup from last night at 3 am, then you’d be done, because it would not be possible to replay the transactions made since 3 am.  You would lose all work done since 3 am this morning.<\/p>\n

If you run in archivelog mode, there are backup types that you can run that otherwise would not be available to you.<\/p>\n

So, rollforward recoveries and more available backup types are the good points about running in archivelog mode.<\/p>\n

There are some bad points, too:<\/p>\n

\u2022    Archivelog mode can make your database run more slowly, although in many cases it’s undetectably so.<\/p>\n

\u2022    You\u2019ll have to manage the archivelogs \u2013 if the file system\/drive they are on fills up, the database will hang.  You\u2019ll need some mechanism to delete them in a timely (not to early, not too late) fashion.<\/p>\n

But what if you don’t care about getting your transactions back since 3 am?  And what if you don’t make a backup type that is capable of replaying transactions and applying them to the backup?  In either case, running in archivelog mode will not help you.  Don’t bother with it.<\/p>\n

Do you know what your archive logging strategy is?  Is that strategy serving your business well?<\/p>\n","protected":false},"excerpt":{"rendered":"

Mistake #2: Choosing (or defaulting to) an inappropriate archivelog setting<\/p>\n

Let\u2019s say you back up your database nightly.  If your database were obliterated in a hardware failure at 3:58 pm, would it be important to recover the changes made to the database since last night?  If it’s a training or test database, or a production […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-33","post","type-post","status-publish","format-standard","hentry","category-oracle","odd"],"_links":{"self":[{"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/posts\/33","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=33"}],"version-history":[{"count":4,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions"}],"predecessor-version":[{"id":279,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=\/wp\/v2\/posts\/33\/revisions\/279"}],"wp:attachment":[{"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=33"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=33"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mcneelytech.com\/blog1\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=33"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}