Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Oracle Database 12c Backup and Recovery Survival Guide
Oracle Database 12c Backup and Recovery Survival Guide

Oracle Database 12c Backup and Recovery Survival Guide: A comprehensive guide for every DBA to learn recovery and backup solutions

eBook
Can$12.99 Can$66.99
Paperback
Can$83.99
Subscription
Free Trial

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Oracle Database 12c Backup and Recovery Survival Guide

Chapter 2. NOLOGGING Operations

You could be asking yourself why I want to talk about NOLOGGING operations in a backup and recovery book. The answer is simple: NOLOGGING operations will affect the recoverability of a database and due to this, it is very important for a DBA to understand this concept. It is the most visited topic on my blog www.oraclenz.org.

Many DBAs and developers use NOLOGGING operations when doing bulk inserts and massive deletion of data to reduce redo generation but many do not know that these statements will always generate redo, in other words, UPDATE or DELETE will always be logged. Later in this chapter, we will see some techniques that will reduce redo generation for massive updates and deletes.

In this chapter, we will cover the following topics:

  • LOGGING versus NOLOGGING

  • Disabling redo generation

  • NOLOGGING operations

  • How to reduce redo generation

  • Redo log wait events

  • Practice with some interesting scripts

NOLOGGING operations do not generate redo records in the redo log...

LOGGING versus NOLOGGING


Despite the importance of the redo entries, Oracle gives users the ability to limit redo generation on tables, partitions, tablespaces, and indexes by setting them in the NOLOGGING mode. NOLOGGING affects the recoverability of a database and before going into how to limit the redo generation, it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation. The following are some interesting points regarding this topic:

  • NOLOGGING is designed to handle bulk inserts of data which can be easily reproduced. (Remember that the UPDATE and DELETE operations will always be logged.)

  • Regardless of the LOGGING status, writing to the UNDO blocks will always cause generation of redo.

  • LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason, Oracle introduced the ALTER DATABASE FORCE LOGGING command to place the database in the FORCE LOGGING mode—meaning that the NOLOGGING attribute will not have any...

Disabling redo generation (NOLOGGING)


The NOLOGGING attribute tells Oracle that the operation being performed does not need to be recoverable in the event of a failure. In this case, the database will generate only a small set of metadata that is written to the redo log, and the operation will probably run faster. Oracle is relying on the user to recover the data manually in the event of any failure. In other words, the NOLOGGING option skips the generation of redo for the affected object, but will still log many things such as data dictionary changes caused by space management.

NOLOGGING operations

At the tablespace level, the LOGGING clause specifies the default LOGGING attribute for all tables, indexes, and partitions created in the tablespace and also for all objects subsequently created on it. When the tablespace LOGGING attribute is changed by the ALTER TABLESPACE statement, then all objects created after the ALTER statement will have the new LOGGING attribute; but be aware that all...

Reducing redo generation


This section shows you how to reduce redo generation using the LOGGING and NOLOGGING operations. The discussion is divided into two parts. In one part, you'll learn about things to do when LOGGING is in effect. In the other, you'll learn what to do when NOLOGGING is enabled.

Tips when LOGGING is in effect (not using NOLOGGING)

This section will cover some interesting tips to reduce redo generation without the need to use NOLOGGING.

Backups

As I mentioned earlier in the Redo generation and recoverability section in Chapter 1, Understanding the Basics of Backup and Recovery, user-managed backups can generate more redo. The best way to eliminate this problem is to use RMAN. RMAN does not need to write entire blocks to redo because it knows when a block is being copied. If you need to use the user-managed backup technique, then you can follow these steps to reduce redo generation:

  • Do not back up all the tablespaces at once (using the ALTER DATABASE BEGIN BACKUP command)....

Backups and NOLOGGING


If it is required that the data loaded using the NOLOGGING option needs to be recovered in the case of a disaster situation, your only solution is making a backup as soon as the NOLOGGING operation is made. Otherwise, the data cannot be recovered in the event of a media failure because it has not been logged.

Sometimes you will not be able to run a full backup of the database until later in the day. In this situation, you can make use of the following strategies:

  • Hot backup

  • In order to recover any additional data or modification to the table after the bulk insert using NOLOGGING is complete, you must at least perform a hot backup of the datafiles in which the objects are located. Remember that your database continues generating redo for the DML transactions over the objects, even if they are still using the NOLOGGING option, but you are strongly advised to place them in LOGGING after the NOLOGGING mode is no longer required, to avoid any possibility of unrecoverability...

Redo-related wait events


There are a number of wait events that happen during redo activities and most of them are I/O related. First, I will talk about the two most important wait events: 'log file parallel write' and 'log file sync'. Then I will mention some other important ones you should know about.

The 'log file parallel write' event

Oracle foreground processes wait for 'log file sync', whereas the LGWR process waits for 'log file parallel write'. Although we usually find 'log file sync' in the Top 5 Timed Events or the Wait Events section of the Statspack report, in order to understand it we will first look at 'log file parallel write'.

The LGWR background process waits for this event while it is copying redo records from the memory log buffer cache to the current redo group's member log files on disk. Asynchronous I/O will be used if available to make the write parallel, otherwise these writes will be done sequentially one member after the other. However, LGWR has to wait until the I...

Block corruption due to NOLOGGING


If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered, then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle 8i, an ORA-26040 is also signaled (ORA-26040: Data block was loaded using the NOLOGGING option) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation, you need to understand that:

  • Recovery cannot retrieve the NOLOGGING data

  • No data is salvageable from inside the block

If this is your situation, please note that:

  • The indexes with corrupt blocks can be dropped and re-created

  • The corrupt tables can be dropped and built from an alternative data source

  • The datafile(s) impacted by the NOLOGGING operations can be refreshed from the primary or the backup which was completed after the NOLOGGING...

Repairing NOLOGGING changes on physical and logical standby databases


After a NOLOGGING operation on the primary is detected, it is recommended to create a backup immediately if you want to recover from this operation in the future. However, there are additional steps required if you have an existing physical or logical standby database. Executing these steps is crucial if you want to preserve the data integrity of your standby databases.

For a physical standby database, Data Guard's Redo Apply process will process the invalidation redo and mark the corresponding data blocks as corrupted. Follow these steps to reinstate the relevant datafiles:

  1. Stop Redo Apply (RECOVER MANAGED STANDBY DATABASE CANCEL).

  2. Take the corresponding datafile(s) offline (ALTER DATABASE DATAFILE <datafile_name> OFFLINE DROP;).

  3. Start Redo Apply (RECOVER MANAGED STANDBY DATABASE DISCONNECT).

  4. Copy the appropriate backup of affected datafiles over from the primary database (for example, use RMAN to backup datafiles and...

Finding sessions that generate lots of redo


To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

  • Query v$sess_io: This view contains the column block_changes, which indicates how many blocks have been changed by the session. High values indicate a session generating lots of redo.

  • The query you can use is:

    SQL> SELECT s.sid, s.serial#, s.username, s.program,
    i.block_changes
     2   FROM v$session s, v$sess_io i
     3   WHERE s.sid = i.sid
     4   ORDER BY 5 desc, 1, 2, 3, 4;
    
  • Run the query multiple times and examine the delta between each occurrence of block_changes. Large deltas indicate high redo generation by the session.

  • Query v$transaction: This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the used_ublk and used_urec columns).

  • The query you can use is:

    SQL> SELECT...

Some other important facts


In this section we will talk about some other important facts about redo that are important for you to know.

Redo and undo for DML

When you issue an insert, update, or delete, Oracle actually makes the change to the data blocks that contain the affected data even though you have not issued a commit. To ensure database integrity, Oracle must write information necessary to reverse the change (undo) into the redo log file to handle any transaction failure or even a rollback. Recovery from media failure is ensured by writing the information necessary to replay all database changes (redo) to the database into the redo log file. So, undo and redo information needs to be written into the transaction log of the RDBMS as a logical consequence to protect the integrity of the data.

While the RDBMS logically would only need to write undo and redo into the transaction log, the undo portion must also be kept online (on disk and accessible to the RDBMS engine) to enable rollback...

Some useful scripts


Now is the time to see some important scripts that could help your life become a little bit easier when dealing with redo.

Redo generated since instance startup

The script redo_since_startup.sql will show statistics regarding redo since the instance was started.

The following is an example of the output of this script:

                      Redo Log Statistics

Statistic                                       Statistic
Name                                            Value
---------------------------------------------- ----------
redo KB read                                       209048
redo KB read (memory)                                   0
redo KB read (memory) for transport                     0
redo KB read for transport                              0
redo blocks checksummed by FG (exclusive)          205974
redo blocks checksummed by LGWR                         0
redo blocks read for recovery                        8532
redo blocks written                         ...

Summary


In this chapter, you learned about the LOGGING and NOLOGGING modes and saw how to use them. You also have seen different techniques for reducing redo generation when using the LOGGING and NOLOGGING modes. Hopefully we've cleared up some misconceptions about NOLOGGING operations as well. The NOLOGGING mode is one of my favorite topics in Oracle, and I hope you have enjoyed this chapter as much I have enjoyed writing it.

In the next chapter, we will talk about what is new in Oracle 12c for backup and recovery. We will see many new features and enhancements that will make our life more easy and our database recoverable.

Left arrow icon Right arrow icon

Key benefits

  • A practical reference to all Oracle backup and recovery options available, making it essential to any DBA in the world
  • A valuable guide for readers on the most frequent backup and recovery scenarios they can find in real life
  • Provides hands-on examples and a full hands-on lab to practise everything learned in this book

Description

The three main responsibilities of a successful DBA are to ensure the availability, recoverability, and performance of any database. To ensure the recoverability of any database, a DBA needs to have a strong backup and recovery skills set. Every DBA is always looking for a reference book that will help them to solve any possible backup and recovery situation that they can come across in their professional life. Oracle Database 12c Backup and Recovery Survival Guide has the unique advantage to be a reference to all Oracle backup and recovery options available, making it essential for any DBA in the world. If you are new to Oracle Database, this book will introduce you to the fantastic world of backup and recovery that is vital to your success. If you are an experienced DBA, this book will become a reference guide and will also help you to learn some possible new skills, or give you some new ideas you were never aware about. It will also help you to easily find the solution to some of the most well known problems you could find during your career as a DBA. This book contains useful screenshots, scripts, and examples that you will find more than useful. Most of the books currently available in the market concentrate only on the RMAN utility to backup and recovery. This book will be an exception to the rule and will become a must-have reference, allowing you to design a real and complete backup and recovery strategy. It covers the most important topics on Oracle database such as backup strategies, Nologging operations, new features in 12c, user managed backups and recoveries, RMAN (including reporting, catalog management, troubleshooting, and performance tuning), advanced data pump, Oracle Enterprise Manager 12c and SQL Developer. "Oracle Database 12c Backup and Recovery Survival Guide" contains everything a DBA needs to know to keep data safe and recoverable, using real-life scenarios.

Who is this book for?

This book is designed for Oracle DBAs and system administrators. The reader will have a basic working experience of administering Oracle databases. This book is designed for Oracle DBAs and system administrators.This book will become a reference guide and will also help you to learn some new skills, and give you some new ideas you never knew about, helping you to easily find the solution to some of the most well-known problems you could encounter as DBAs.

What you will learn

  • Understand all the options available for the backup and recovery of a database to be used in case of a disaster and how to survive it
  • Learn how to use RMAN and take advantage of its advanced functionalities
  • Use Data Pump for more than just backups, learning how powerful this utility is
  • Take advantage of Nologging operations in a safe and proper way
  • Perform backups and recovery properly in Multitenant and non-CDB environments
  • Take advantage of OEM12c and SQL Developer to fulfil your backup requirements and reduce risk in your organization
  • Understand the new features in 12c offered for backup and recovery and how to perform backup and recovery operations on it
  • Get familiar with the available range of Oracle products for backup and recovery, not only RMAN

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Sep 24, 2013
Length: 440 pages
Edition : 1st
Language : English
ISBN-13 : 9781782171201
Vendor :
Oracle
Category :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Sep 24, 2013
Length: 440 pages
Edition : 1st
Language : English
ISBN-13 : 9781782171201
Vendor :
Oracle
Category :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just Can$6 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just Can$6 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total Can$38.97 Can$195.97 Can$157.00 saved
Oracle Data Guard 11gR2 Administration : Beginner's Guide
Can$83.99
Oracle Database 12c Backup and Recovery Survival Guide
Can$83.99
Oracle Database 12c Security Cookbook
Can$77.99
Total Can$38.97Can$195.97 Can$157.00 saved Stars icon
Banner background image

Table of Contents

11 Chapters
Understanding the Basics of Backup and Recovery Chevron down icon Chevron up icon
NOLOGGING Operations Chevron down icon Chevron up icon
What is New in 12c Chevron down icon Chevron up icon
User-managed Backup and Recovery Chevron down icon Chevron up icon
Understanding RMAN and Simple Backups Chevron down icon Chevron up icon
Configuring and Recovering with RMAN Chevron down icon Chevron up icon
RMAN Reporting and Catalog Management Chevron down icon Chevron up icon
RMAN Troubleshooting and Tuning Chevron down icon Chevron up icon
Understanding Data Pump Chevron down icon Chevron up icon
Advanced Data Pump Chevron down icon Chevron up icon
OEM12c and SQL Developer Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.8
(10 Ratings)
5 star 90%
4 star 0%
3 star 10%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by




Earl D. Shaffer Feb 15, 2014
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Oracle Database 12c Backup and Recovery Survival GuidePACKT PublisherFrancisco Munoz AlvarezAman SharmaThis book should be part of every DBA's library. It covers the topics you would expect to be covered, but it does so in a conversational, yet professional, style. Especially as 12c becomes the 'next version to upgrade to' in 2014.After covering basic but important topics like archive/no-archive, restore vs recovery, effects of NOLOGGING, it covers RMAN vs Data Pump, cold vs hot backups, and provides scripts to help all this be clear.After covering RMAN in-depth, FRA, and V$ views, it cover real scenarios and shows how it all fits together in the real world.Buy the book today!
Amazon Verified review Amazon
Kai Yu Oct 03, 2013
Full star icon Full star icon Full star icon Full star icon Full star icon 5
It is great to see this new Oracle 12c database backup and recovery book. It has a comprehensive coverage of rman database backup and recovery techniques and experiences, and many different database backup and recovery scenarios in which DBAs can use a reference book for their daily database administration jobs, it also covers some latest 12c new features such as contrainer database (CDB) and pluggable database (PDBs) and how to backup and recovery the CDB and PDBs. I would say this is the great reference book for every DBA to have
Amazon Verified review Amazon
Tushar Kanti Nath Jan 26, 2014
Full star icon Full star icon Full star icon Full star icon Full star icon 5
"Oracle Database 12c Backup and Recovery Survival Guide" book is very well written. Each chapter explains the backup and recovery concepts followed by examples and notes from authors to make the book unique and interesting to read. No doubt the contents of books reflect authors vast experiences in backup and recovery. I enjoyed reading this book which includes basics of backup recovery, what is new in 12c,RMAN troubleshooting and Tuning etc. and stongly recommend all oracle professional to read this book to uplift the backup and recovery skill sets.
Amazon Verified review Amazon
jpo Nov 23, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
A dba can never have too much information. Especially about backup and recovery.
Amazon Verified review Amazon
Marcus Vinicius M Pedro Aug 01, 2014
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Oracle Database 12c Backup and Recovery Survival Guide is absolutely the most complete reference book to Oracle Database Administrators to learn how to maintain the databases recoverable.In this book, we have a comprehensive coverage of RMAN, the main tool for backup and recovery in Oracle Databases and the authors also covered different cenarios in which DBA's can live in the real life. It's definitely a book that can be used as a reference to use best practices in backup and recovery situations.It's also a great book because cover the new features for Oracle Database 12c, like CDB and PDB.If you wanna learn everything about backup and recover Oracle Database, you need to read this book!
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.