Oracle RMAN 11g Backup and Recovery (134 page)

from v$flashback database log;

Using V$FLASHBACK_DATABASE_STAT

Oracle has built a monitoring view so that you can keep your eye on flashback logging activity.

V$FLASHBACK_DATABASE_STAT provides you with information on flashback data generated

396
Part III: Using RMAN Effectively

over the course of a period of time (typically, a one-hour window extending back from sysdate).

In addition to showing how much flashback logging occurred, this view posts the redo generated and the actual database data generated over the same period. The following
select
shows a sample output of this view:

select * from v$flashback database stat;

BEGIN TIM END TIME FLASHBACK DATA DB DATA REDO DATA

--------- --------- -------------- ---------- ----------

ESTIMATED FLASHBACK SIZE

------------------------

19-SEP-05 19-SEP-05 9003008 15294464 3986944

210247680

19-SEP-05 19-SEP-05 15884288 21225472 5766144

210247680

19-SEP-05 19-SEP-05 10248192 25772032 5162496

210075648

RMAN Workshop:
Perform Flashback Database

Workshop Notes

It’s time to give it a test-drive. We are going to introduce a fault that cannot be handled by any of the other, less-intrusive forms of flashback: the table truncate. Because the truncate does not produce any redo, we cannot do a Flashback Table operation. So, we are forced to do a flashback of the entire database.

Here, we are using the WOOD.WOODSCREW_INVENTORY table that we built earlier, during the “Explore Flashback Versions Query” RMAN Workshop. If you did not build the table yet, do so now.

Step 1.
First, get the current SCN from the database. Because we are simply testing, we can prepare for the test by getting the current SCN prior to putting the fault into the database: SQL> select current scn from v$database;

CURRENT SCN

----------------

885524

Step 2.
Introduce the fault:

SQL> truncate table wood.woodscrew inventory;

Table truncated.

Step 3.
Shut down the database, and then remount. The database must be mounted and not open for flashback.

SQL> connect / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

Chapter 15: Surviving User Errors: Flashback Technologies
397

Step 4.
Issue the
flashback
command:

SQL> flashback database to scn 885524;

Flashback complete.

Step 5.
Open the database in READ-ONLY mode to confirm that the table has been flashed back to the appropriate SCN:

SQL> alter database open read only;

Database altered.

SQL> connect wood/wood;

Connected.

SQL> select count(*) from woodscrew inventory;

COUNT(*)

----------

4

Step 6.
Open the database by using the
resetlogs
operation: SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database open resetlogs;

Flashback Data Archive (Total Recall)

New in Oracle 11
g,
Flashback Data Archive (sometimes referred to as Oracle Total Recall) is a set of functions that allow you to permanently archive all changes to a table so that you can go back to any point in time and look at the data as it was in the past. Unlike Flashback Query, which is dependent on the transitory nature of undo, Flashback Data Archive requires a specific type of tablespace to be built so that it can house the version information required to look back in time at a particular table.

Flashback Data Archive is useful for auditing and archival purposes on tables that have legal or regulatory sensitivities. You can configure the data archive to be of a set retention period that matches either the business or regulatory need, and then you assign the table to that archive.

Once this is complete, you can utilize a straightforward flashback query in SQL to look back in time at the table as of months or even years ago.

You should be mindful of two architectural restrictions based on how the archive data is generated and accessed. If you put a table in a data archive mode, you cannot:

■ Perform an
alter table
command with an UPGRADE clause

■ Perform a drop table

Either of these actions will result in an error:

ORA-55610: Invalid DDL statement on history-tracked table

398
Part III: Using RMAN Effectively

RMAN Workshop:
Create a Flashback Data Archive

Workshop Notes

In this workshop, we will create a Flashback Data Archive, and then add our WOODSCREW_

INVENTORY table to the archive. This archive will have a Quota of 500MB and a retention period of one year.

Step 1.
Create a tablespace to house the Flashback Data Archive:

SQL> create tablespace FBS datafile'/home/oracle/app/oracle/oradata/v112/fbs01.dbf' size 1024M;
Step 2.
Create the Data Archive in the FBS tablespace:

SQL> create flashback archive default FDA01 tablespace FBS quota 500M retention 1 year;
Step 3.
Add the matt.woodscrew_inventory table to the Flashback Data Archive FDA01. This is straightforward because we made FDA01 the default data archive for any table added without a specification.

SQL> alter table matt.woodscrew inventory flashback archive;

Summary

In this chapter, we introduced the new means of recovering from user-induced errors, known collectively in the Oracle Database as Flashback Technology. The new Flashback Technology allows you to recover from logical errors in a faster, less-intrusive way than running a full-bore media recovery. We discussed using Flashback Versions Query to determine the full scope of the logical corruption. We illustrated using the Flashback Table command to recover from a bad DML

statement, as well as from a table drop. We discussed the new Flashback Transaction option in 11
g
that utilizes the redo logs to undo an erroneous action at the transaction level instead of at the table level. We discussed the Flashback Database functionality, which allows for a point-in-time recovery of an entire database without requiring a full restore of the datafiles from backup. We ended the chapter by reviewing the new 11
g
functionality for creating a Flashback Data Archive, which allows a historical point of view on a table as far back as you have space to hold the records.

CHAPTER

16

Maintaining RMAN

400
Part III: Using RMAN Effectively

ntropy is a nasty result of the second law of thermodynamics. Basically,
entropy
describes the tendency of an ordered system to become disordered, the result of which is the requirement to maintain that system. RMAN is no different. When
E
using RMAN, you have to maintain a number of things to keep it running smoothly.

In this chapter, we talk all about maintaining RMAN. From the
crosscheck
command to retention policies to redundancy policies, everything you need to know about keeping RMAN

from falling apart is provided here. After we have talked about RMAN maintenance issues, we will discuss some issues specific to the recovery catalog, including an introduction to the recovery catalog schema itself.

RMAN Maintenance

You didn’t think you could just continue using RMAN without having to maintain it, did you? The truth is, RMAN is fairly maintenance free, but you need to be aware of a few maintenance-related things, which we address in this section. First, we are going to talk about the
crosscheck
command, followed by a discussion of retention policies. Next, we discuss the
change
command, and then, the
delete
command. Finally, we end this section with a discussion of cataloging your existing database backups in RMAN.

Cross-Checking RMAN Backups

You may encounter situations in which backup set pieces or copies are listed in the control file or recovery catalog but do not physically exist on the backup media (disk or tape). The physical files constituting the backup or copy might have been deleted, either by some process (for example, a separate retention policy for the tape management system that you are using or a damaged tape), or perhaps by the loss of a physical device that had backup set pieces on it.

In cases where the RMAN catalog and the physical backup destinations are out of synchronization, the
crosscheck
command is used to validate the contents of the RMAN

information in the control file or in the recovery catalog against the actual physical backup set pieces that are on the backup media.

When using the
crosscheck
command, we are interested in the status of each backup set or copy. Each backup set or copy has status codes that are listed in the STATUS column of the views: V$BACKUP_SET for backup set pieces and V$DATAFILE_COPY for copies if you are using a control file, and RC_BACKUP_SET for backup set pieces and RC_DATAFILE_COPY for copies if you want to look in the recovery catalog. There are several different backup status codes, but for now we are interested primarily in two:

■
A
AVAILABLE; RMAN assumes the item is available on the backup media.

■
X
EXPIRED; the backup set piece or the copy is stored in the RMAN catalog (meaning the control file or the recovery catalog), but is not physically on the backup media.

When the
crosscheck
command is executed, RMAN checks each backup set or copy listed in the catalog and determines if it is on the backup media. If it is not, that piece will be marked as EXPIRED and will not be a candidate for any restore operation. If the piece exists, then it will maintain its AVAILABLE status. If a backup piece or copy was previously marked EXPIRED and