11g 新特性之flashback ppt课件

19 Copyright 2009, Oracle. All rights reserved. Flashback Technology, LogMiner, and Data Pump Enhancements Copyright 2009, Oracle. All rights reserved.19 - 2 Objectives After completing this lesson, you should be able to: Use Flashback Data Archive to create, protect, and use history data Prepare your database Create, change, and drop a flashback data archive View flashback data archive metadata Use Flashback Transaction Backout Set up Flashback Transaction prerequisites Query transactions with and without dependencies Choose backout options and flash back transactions Use Enterprise Manager LogMiner Use Data Pump legacy mode Copyright 2009, Oracle. All rights reserved.19 - 3 Flashback Data Archive: Overview Transparently tracks historical changes to all Oracle data in a highly secure and efficient manner: “Secure” No possibility to modify historical data Retained according to your specifications Automatically purged based on your retention policy “Efficient” Special kernel optimizations to minimize performance overhead of capturing historical data Stored in compressed form in tablespaces to minimize storage requirements Completely transparent to applications Easy to set up Copyright 2009, Oracle. All rights reserved.19 - 4 Flashback Data Archive: Overview Original data in Undo data DML operations buffer cache Flashback data archives stored in tablespaces 1 year 2 years 5 years Example: Three flashback data archives with retention of: For retention requirements exceeding undo FBDA Copyright 2009, Oracle. All rights reserved.19 - 5 History or archive tables: - Compressed storage - With automatic digital shredding Flashback Data Archive: Architecture Buffer cache DML changes used by FBDA Old values Undo FBDA Flashback data archives 1 2 3 Copyright 2009, Oracle. All rights reserved.19 - 6 Preparing Your Database Create one or more tablespaces for data archives and grant QUOTA on the tablespaces. Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to create and maintain flashback archives to the archive administrator. Grant the FLASHBACK ARCHIVE object privilege (to enable history tracking for specific tables in the given flashback archives) to the archive user. Grant FLASHBACK and SELECT privileges to query specific objects as necessary. Copyright 2009, Oracle. All rights reserved.19 - 7 Copyright 2009, Oracle. All rights reserved.19 - 8 Configuring undo: Create an undo tablespace (default: automatically extensible tablespace) Enable Automatic Undo Management (11g default) Understand automatic tuning of undo: Fixed-size tablespace: Automatic tuning for best retention Automatically extensible undo tablespace: Automatic tuning for longest-running query Recommendation for Flashback: Fixed-size undo tablespace Preparing Your Database Copyright 2009, Oracle. All rights reserved.19 - 9 Copyright 2009, Oracle. All rights reserved.19 - 10 Flashback Data Archive: Workflow Create the flashback data archive. Optionally, specify the default flashback data archive. Enable the flashback data archive. View flashback data archive data. Copyright 2009, Oracle. All rights reserved.19 - 11 Using Flashback Data Archive 1. Create the flashback data archive: 2. Enable history tracking for a table in the archive: 3. View the historical data: CREATE FLASHBACK ARCHIVE fla1 TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR; ALTER TABLE inventory FLASHBACK ARCHIVE fla1; SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMP TO_TIMESTAMP (2007-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS); Copyright 2009, Oracle. All rights reserved.19 - 12 Configuring a Default Flashback Data Archive Using a default flashback archive: 1. Create a default flashback data archive: 2. Enable history tracking for a table: Note: The name of the flashback data archive is not needed because the default one is used. 3. Disable history tracking: CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1 QUOTA 10G RETENTION 2 YEAR; ALTER TABLE stock_data FLASHBACK ARCHIVE; ALTER TABLE stock_data NO FLASHBACK ARCHIVE; Copyright 2009, Oracle. All rights reserved.19 - 13 Filling the Flashback Data Archive Space What happens when your flashback data archive gets full? 90% space usage Raising of errors: ORA-55623 “Flashback Archive “%s“ is blocking and tracking on all tables is suspended“ ORA-55617 “Flashback Archive “%s“ runs out of space and tracking on “%s“ is suspended“ Generating alert log entry Suspending tracking Copyright 2009, Oracle. All rights reserved.19 - 14 Maintaining Flashback Data Archives Adding space: Changing retention time: Purging data: Dropping a flashback data archive: ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G; ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP(SYSTIMESTAMP - INTERVAL 1 day); ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; DROP FLASHBACK ARCHIVE fla1; Copyright 2009, Oracle. All rights reserved.19 - 15 Flashback Data Archive: Examples To enforce digital shredding: To access historical data: To recover data: CREATE FLASHBACK ARCHIVE tax7_archive TABLESPACE tbs1 RETENTION 7 YEAR; SELECT symbol, stock_price FROM stock_data AS OF TIMESTAMP TO_TIMESTAMP (2006-12-31 23:59:00, YYYY-MM-DD HH24:MI:SS) INSERT INTO employees SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP(2007-06-12 11:30:00,YYYY-MM-DD HH24:MI:SS) WHERE name = JOE; Copyright 2009, Oracle. All rights reserved.19 - 16 Flashback Data Archive: DDL Restrictions Using any of the following DDL statements on a table enabled for Flashback Data Archive causes an ORA- 55610 error: ALTER TABLE statement that does any of the following: Performs partition or subpartition operations Converts a LONG column to a LOB column Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause DROP TABLE statement TRUNCATE TABLE statement Copyright 2009, Oracle. All rights reserved.19 - 17 Flashback Data Archive: Supporting Transparent Schema Evolution Additional DDL supported: DROP, RENAME, and MODIFY column DROP and TRUNCATE partition RENAME and TRUNCATE table Flashback queries work across DDL changes: Output is presented accordingly. All other DDL not automatically supported Drop Column Add Column Add Column time Flashback Version Query 11.2 Copyright 2009, Oracle. All rights reserved.19 - 18 Flashback Data Archive: Supporting Full Schema Evolution DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA and DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA Disables Flashback Data Archive on specified tables, allowing more complex DDL (upgrades, split tables) Enforces schema integrity after association Base table and history table schemas must be the same. Requires the FLASHBACK ARCHIVE ADMINISTER privilege ALTER SCHEMADISASSOCIATE ALTER SCHEMAASSOCIATE 11.2 1 2 4 3 History table History table Base table Copyright 2009, Oracle. All rights reserved.19 - 19 Viewing Flashback Data Archives Viewing the results: View Name Description *_FLASHBACK_ARCHIVE Displays information about flashback data archives *_FLASHBACK_ARCHIVE_TS Displays tablespaces of flashback data archives *_FLASHBACK_ARCHIVE_TABLES Displays information about tables that are enabled for flashback archiving Copyright 2009, Oracle. All rights reserved.19 - 20 Guidelines and Usage Tips COMMIT or ROLLBACK before querying past data Use of current session settings Obtain SCN with the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function. Compute a past time with: (SYSTIMESTAMP - INTERVAL 10 MINUTE) Use System Change Number (SCN) where precision is needed (time stamps have a three-second granularity). Copyright 2009, Oracle. All rights reserved.19 - 21 Quiz Which of the following are characteristics of a flashback data archive? A flashback data archive consists of one or more tablespaces or parts thereof. A flashback data archive consists of only one tablespace. You can have only one flashback data archive in each database. You can have multiple flashback data archives in each database. Copyright 2009, Oracle. All rights reserved.19 - 22 Flashback Transaction Backout Logical recovery option to roll back a specific transaction and all its dependent transactions Using redo logs and supplemental logging Creating and executing compensating transactions You finalize changes with commit or roll back. Faster and easier than laborious manual approach Dependent transactions include: Write-after-write (WAW) and primary key constraints, but not foreign key constraints Copyright 2009, Oracle. All rights reserved.19 - 23 Prerequisites and database must be in ARCHIVELOG mode 11.2 update Copyright 2009, Oracle. All rights reserved.19 - 24 Flashing Back a Transaction You can flash back a transaction by using Enterprise Manager or the command line. Enterprise Manager uses the Flashback Transaction Wizard, which calls the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure with the NOCASCADE option. If the PL/SQL call finishes successfully, it means that the transaction does not have any dependencies and a single transaction is backed out successfully. Copyright 2009, Oracle. All rights reserved.19 - 25 Using the Flashback Transaction Wizard Copyright 2009, Oracle. All rights reserved.19 - 26 Using the DBMS_FLASHBACK.TRANSACTION_BACKOUT Procedure CREATE TYPE XID_ARRAY AS VARRAY(100) OF RAW(8); CREATE OR REPLACE PROCEDURE TRANSACTION_BACKOUT( numberOfXIDs NUMBER, - number of transactions passed as input xids XID_ARRAY, - the list of transaction ids options NUMBER default NOCASCADE, - back out dependent txn timeHint TIMESTAMP default MINTIME - time hint on the txn start ); Copyright 2009, Oracle. All rights reserved.19 - 27 Viewing a Dependency Report After choosing your backout option, the dependency report is visible in the DBA_FLASHBACK_TXN_STATE and DBA_FLASHBACK_TXN_REPORT views. Review the dependency report that shows all transactions which were backed out. Commit the changes to make them permanent. Roll back to discard the changes. Copyright 2009, Oracle. All rights reserved.19 - 28 Viewing Flashback Transaction Metadata View Name Description *_FLASHBACK_TXN_REPORT Displays related XML information *_FLASHBACK_TXN_STATE Displays the transaction identifiers for backed- out transactions SQL SELECT * FROM DBA_FLASHBACK_TXN_STATE; COMPENSATING_XID XID BACKOUT_MODE DEPENDENT_XID USER# - - - - - 0500150069050000 03000000A9050000 4 0 0500150069050000 05001E0063050000 4 03000000A9050000 0 Copyright 2009, Oracle. All rights reserved.19 - 29 Quiz Flashback Transaction Backout enables you to roll back a specific transaction and dependent transactions while the database remains online. True False Copyright 2009, Oracle. All rights reserved.19 - 30 Using LogMiner Powerful audit tool for Oracle databases Direct access to redo logs User interfaces: SQL command line Graphical user interface (GUI) Integrated with Enterprise Manager Copyright 2009, Oracle. All rights reserved.19 - 31 Review: Data Pump Export and Import expdp client Dump file set Database Data Pump job Source Master table Server process Dump file set Server process Target Data Pump job impdp client Database Master table Database link “Network mode” Copyright 2009, Oracle. All rights reserved.19 - 32 Migration with Data Pump Legacy Mode Assistance in transitioning from imp and exp utilities to impdp and expdp utilities Data Pump in legacy mode: 1. Encounters unique imp or exp parameter and enters legacy mode 2. Attempts to map the old syntax to the new syntax 3. Displays new syntax 4. Exits legacy mode Best practice tip: Oracle strongly recommends that you view the new syntax and make script changes as time permits. 11.2 Copyright 2009, Oracle. All rights reserved.19 - 33 Data Pump Legacy Mode The Data Pump export and import utilities: Read and write files only in Data Pump format Accept exp and imp utility commands in legacy mode Legacy mode parameters: Can be identical to the new syntax: FILESIZE=integerB | K | M | G Can be similar: QUERY= query_clause Are ignored, when the command is superceded by Data Pump defaults. BUFFER=integer COMPRESS=y|n DIRECT=y|n Cause an error when old and new syntax is mixed. 11.2 Copyright 2009, Oracle. All rights reserved.19 - 34 Data Pump Legacy Mode Legacy mode parameters (continued): Are mapped to Data Pump parameters, if possible: consistent=y|n - FLASHBACK_TIME GRANTS=n - EXCLUDE=CONSTRAINTS INDEXES=n - EXCLUDE=INDEX LOG=filename - LOGFILE=filename FILE=filename - dumpfile=directory-object:filename Can be similar, but not identical: FEEDBACK=integer - STATUS Cause an error when incompatible with new Data Pump: VOLSIZE=integer