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
€8.99 €39.99
Paperback
€48.99
Subscription
Free Trial
Renews at €18.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

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 : 9781782171218
Vendor :
Oracle
Category :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Product Details

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

Packt Subscriptions

See our plans and pricing
Modal Close icon
€18.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
€189.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 €5 each
Feature tick icon Exclusive print discounts
€264.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 €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total €26.97 €116.97 €90.00 saved
Oracle Data Guard 11gR2 Administration : Beginner's Guide
€48.99
Oracle Database 12c Backup and Recovery Survival Guide
€48.99
Oracle Database 12c Security Cookbook
€45.99
Total €26.97€116.97 €90.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

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.