Transcript Flashback Database

Flashback
Copyright © 2006, Oracle. All rights reserved.
Objectives
Recycle bin
Flashback DB
Config. FB DB
Monitor FB DB
Guar. Res. Pt.
After completing this lesson, you should be able to:
• Query the recycle bin
• Configure Flashback Database
• Perform Flashback Database to a point in time
• Monitor flashback log statistics
• Enable and disable the Flashback Database
feature
• Use the Enterprise Manager Recovery Wizard to
perform Flashback Database
• Use guaranteed restore points with Flashback
Database
6-2
Copyright © 2006, Oracle. All rights reserved.
Flashback Technology: Review
Object
Level
Uses
Affects
Data
Database
Truncate table;
Undesired multitable
changes made
Database
Flashback
logs
TRUE
Table
Drop table
Drop
Recycle bin
TRUE
Update with the wrong
WHERE clause
Table
Undo data
TRUE
Compare current data
with data from the past
Query
Undo data
FALSE
Compare versions of a
row
Version
Undo data
FALSE
Investigate several
historical states of data
Transaction
Undo data
FALSE
Tx
6-3
Scenario Examples
Flashback
Technology
Copyright © 2006, Oracle. All rights reserved.
Flashback Drop and the Recycle Bin
RECYCLEBIN=ON
DROP TABLE employees;
FLASHBACK TABLE
employees
TO BEFORE DROP;
Mistake was
made.
6-4
Copyright © 2006, Oracle. All rights reserved.
Recycle Bin
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
USER_OBJECTS
EMPLOYEES
EMPLOYEES_PK
Recycle
bin
4
DBA_FREE_SPACE
BIN$zbjrBdpw==$0
EMPLOYEES
3
EMPLOYEES_PK
1
6-5
BIN$zbjra9wy==$0
Objects are:
– Renamed
– Not moved
2
DROP TABLE employees;
Copyright © 2006, Oracle. All rights reserved.
Restoring Tables from the Recycle Bin
•
•
Restore dropped tables and dependent objects.
If multiple recycle bin entries have the same
original name:
– Use unique, system-generated names to restore a
particular version
– When using original names, the restored table is
last in, first out (LIFO)
•
Rename the original name if that name is currently
used.
FLASHBACK TABLE <table_name>
TO BEFORE DROP [RENAME TO <new_name>];
6-7
Copyright © 2006, Oracle. All rights reserved.
Recycle Bin: Automatic
Space Reclamation
Recycle bin
2
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
BIN$zbjrBdpw==$0
BIN$zbjra9wy==$0
DBA_FREE_SPACE - RECYCLEBIN
1
Autoextend 3
6-8
Copyright © 2006, Oracle. All rights reserved.
Recycle Bin: Manual Space Reclamation
PURGE {TABLE <table_name>|INDEX <index_name>}
PURGE TABLESPACE <ts_name> [USER <user_name>]
PURGE [USER_|DBA_]RECYCLEBIN
6-10
Copyright © 2006, Oracle. All rights reserved.
Bypassing the Recycle Bin
DROP TABLE <table_name> [PURGE] ;
DROP TABLESPACE <ts_name>
[INCLUDING CONTENTS] ;
DROP USER <user_name> [CASCADE] ;
6-11
Copyright © 2006, Oracle. All rights reserved.
Querying the Recycle Bin
SELECT owner, original_name, object_name,
type, ts_name, droptime, related, space
FROM dba_recyclebin
WHERE can_undrop = 'YES';
SELECT original_name, object_name,
type, ts_name, droptime, related, space
FROM user_recyclebin
WHERE can_undrop = 'YES';
SQL> SHOW RECYCLEBIN
6-12
Copyright © 2006, Oracle. All rights reserved.
Querying Data from
Dropped Tables
USER_TABLES
DROPPED
TABLE_NAME
NO
SALES
YES
BIN$zbjrBdpw==$0
EMPLOYEES
YES
BIN$zbjra9wy==$0
EMPLOYEES_PK
NO
SALES_PK
DROPPED
INDEX_NAME
USER_INDEXES
SELECT ...
FROM "BIN$zbjrBdpw==$0" [AS OF ...]
WHERE ...
6-13
Copyright © 2006, Oracle. All rights reserved.
Recycle
bin
Flashback Database: Review
Recycle bin
> Flashback DB
Config. FB DB
Monitor FB DB
Guar. Res. Pt.
The Flashback Database operation:
• Works like a rewind button for the database
• Can be used in cases of logical data corruptions
made by users
Users
generate
errors.
6-14
The
database is
corrupted.
You “press the
rewind button.”
Copyright © 2006, Oracle. All rights reserved.
The
database is
“rewound.”
Flashback Database Architecture
SGA
Not every
change!
Flashback
buffer
Buffer cache
Redo log
buffer
LGWR
Every
change
Log block
before
images
periodically.
Flashback
logs
Redo
logs
RVWR
1
Back out changes
to database using
before images.
Do forward
media recovery.
…
6-15
2
…
Copyright © 2006, Oracle. All rights reserved.
Configuring Flashback
Database
1. Configure the
flash recovery area.
2. Set the retention
target.
Recycle bin
Flashback DB
> Config. FB DB
Monitor FB DB
Guar. Res. Pt.
3. Enable Flashback
Database.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER SYSTEM SET
2 DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
6-16
Copyright © 2006, Oracle. All rights reserved.
Configuring Flashback Database Using EM
Make sure that the database is in ARCHIVELOG mode.
6-17
Copyright © 2006, Oracle. All rights reserved.
Configuring Flashback Database Using EM
Enable flashback logging and specify the flash
recovery area.
6-18
Copyright © 2006, Oracle. All rights reserved.
Flashback Database: Examples
RMAN> FLASHBACK DATABASE TO TIME =
2> "TO_DATE('2004-05-27 16:00:00',
3> 'YYYY-MM-DD HH24:MI:SS')";
RMAN> FLASHBACK DATABASE TO SCN=23565;
RMAN> FLASHBACK DATABASE
2> TO SEQUENCE=223 THREAD=1;
SQL> FLASHBACK DATABASE
2 TO TIMESTAMP(SYSDATE-1/24);
SQL> FLASHBACK DATABASE TO SCN 53943;
SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
6-19
Copyright © 2006, Oracle. All rights reserved.
Performing Flashback Database Using EM
Select object and
operation type.
6-20
Copyright © 2006, Oracle. All rights reserved.
Performing Flashback Database Using EM
Launching the Recovery Wizard:
6-21
Copyright © 2006, Oracle. All rights reserved.
Performing Flashback Database Using EM
6-22
Copyright © 2006, Oracle. All rights reserved.
Excluding Tablespaces from
Flashback Database
ALTER TABLESPACE <ts_name> FLASHBACK {ON|OFF}
SQL> SELECT name, flashback_on
2 FROM
v$tablespace;
•
•
6-23
Take the tablespace offline before you perform the
Flashback Database recovery.
Drop the tablespace or recover the offline files
with traditional point-in-time recovery.
Copyright © 2006, Oracle. All rights reserved.
Flashback Database Considerations
•
When the Flashback Database operation
completes, open the database:
– In read-only mode to verify that the correct target
time or SCN was used
– With a RESETLOGS operation to allow for DML
•
•
The opposite of “flash back” is “recover.”
You cannot use Flashback Database in the
following situations:
– The control file has been restored or re-created.
– A tablespace has been dropped.
– A data file has been shrunk.
•
6-24
Use the TO BEFORE RESETLOGS to flash back to
before the last RESETLOGS operation.
Copyright © 2006, Oracle. All rights reserved.
Monitoring Flashback
Database
Recycle bin
Flashback DB
Config. FB DB
> Monitor FB DB
Guar. Res. Pt.
To monitor the ability to meet your retention target:
• View the flash recovery area disk quota:
SQL> SELECT estimated_flashback_size,
2
flashback_size
3 FROM
V$FLASHBACK_DATABASE_LOG;
•
Determine the current flashback window:
SQL> SELECT oldest_flashback_scn,
2 oldest_flashback_time
3 FROM
V$FLASHBACK_DATABASE_LOG;
•
Monitor logging in the Flashback Database logs:
SQL> SELECT *
2 FROM
V$FLASHBACK_DATABASE_STAT;
6-26
Copyright © 2006, Oracle. All rights reserved.
Monitoring Flashback Database with EM
6-28
Copyright © 2006, Oracle. All rights reserved.
Guaranteed Restore Points
Recycle bin
Flashback DB
Config. FB DB
Monitor FB DB
> Guar. Res. Pt.
A guaranteed restore point ensures that you can
perform a FLASHBACK DATABASE command to that SCN
at any time.
SQL> CREATE RESTORE POINT before_load
2 GUARANTEE FLASHBACK DATABASE;
6-29
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Query the recycle bin
• Configure Flashback Database
• Perform Flashback Database to a point in time
using Enterprise Manager or RMAN
• Monitor flashback log statistics
• Enable and disable the Flashback Database
feature
• Use guaranteed restore points with Flashback
Database
6-30
Copyright © 2006, Oracle. All rights reserved.
Practice Overview: Performing
Flashback Database
This practice covers the following topics:
• Performing Flashback Database to undo unwanted
transactions
• Monitoring the Flashback Database retention
• Determine the size of the flashback logs
6-31
Copyright © 2006, Oracle. All rights reserved.