1 / 88

NoCOUG 2005 Winter Conference Oracle 10g Backup and Recovery New Features

NoCOUG 2005 Winter Conference Oracle 10g Backup and Recovery New Features. Daniel T. Liu Senior Technical Consultant First American Real Estate Solutions. Date: Tuesday, February 8, 2005 @ 2:15 PM - 3:15 PM Venue: Room 103, Oracle Conference Center, Redwood Shores, CA. Agenda.

jana
Download Presentation

NoCOUG 2005 Winter Conference Oracle 10g Backup and Recovery New Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NoCOUG 2005 Winter Conference Oracle 10g Backup and Recovery New Features Daniel T. Liu Senior Technical Consultant First American Real Estate Solutions Date: Tuesday, February 8, 2005 @ 2:15 PM - 3:15 PM Venue: Room 103, Oracle Conference Center, Redwood Shores, CA

  2. Agenda • Introduction • Oracle Database 10g • Challenges for Backup and Recovery • Types of Database Errors • Backup and Recovery Solutions NoCOUG 2005, Daniel T. Liu

  3. Agenda • Extended Flashback Features • Enhanced RMAN Features • Summary • Q & A NoCOUG 2005, Daniel T. Liu

  4. Introduction to Oracle 10g • Grid Computing Concept • Utilizing computing resource as utility • Software level • Hardware level • Grid Computing Challenges • Standardization • Security • Reliability NoCOUG 2005, Daniel T. Liu

  5. Introduction to Oracle 10g • Oracle Database 10g New Features • Real Application Clusters • Oracle Streams • Cross-platform transportable tablespaces • Automatic Storage Management (ASM) • Self-Managing Database • New Backup and Recovery Features NoCOUG 2005, Daniel T. Liu

  6. Challenges for Backup and Recovery • Database size is getting bigger and bigger • Database Backup Time • A DLT tape writes 6 MB/Second, or 21 GB/hour • A 2 TB database with one tape driver will take 97 hours to backup • Database Recovery Time • Is the backup good? • Trouble shooting time NoCOUG 2005, Daniel T. Liu

  7. Types of Database Errors • Corruptions • Logical corruptions • Physical corruptions • Human Errors • Accidentally drop, or truncate a table • Accidentally delete, update rows in a table • Accidentally delete a data file or drop a tablespace • Disasters • War, terrorism • Earthquake, flood, fire or hurricane • No power for a long period of time • Server crash, malfunction of hardware NoCOUG 2005, Daniel T. Liu

  8. Backup and Recovery Solutions • Logical Methods • Export and Import • Data Pump • Flashback Functions • Physical Methods • O/S Backup and Restore • RMAN Backup and Restore • High Availability Methods • Data Guard • RAC • Oracle Streams NoCOUG 2005, Daniel T. Liu

  9. Extended Flashback Functions • Flashback Database • Flashback Drop • Flashback Table • Flashback Versions Query • Flashback Transaction Query NoCOUG 2005, Daniel T. Liu

  10. Flashback Database • Traditional recovery method uses backups and redo log files; Flashback Database is implemented using a new type of log file called Flashback Database logs. • The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. • The data block images are used to quickly back out changes to the database during Flashback Database. NoCOUG 2005, Daniel T. Liu

  11. Flashback Database • Flashback Database reduces the time required to recover the database to a point in time. • The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database. • Flashback Database is faster than traditional point-in-time recovery. NoCOUG 2005, Daniel T. Liu

  12. Flashback Database NoCOUG 2005, Daniel T. Liu

  13. Flashback Database • The list below shows all the background processes for ‘grid’ instance. $ ps -ef | grep grid oracle 25124 1 0 16:32:05 ? 0:00 ora_s000_grid oracle 25116 1 0 16:32:04 ? 0:00 ora_reco_grid oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid oracle 25112 1 0 16:32:04 ? 0:00 ora_ckpt_grid oracle 25110 1 0 16:32:04 ? 0:00 ora_lgwr_grid oracle 25108 1 0 16:32:04 ? 0:00 ora_dbw0_grid oracle 25114 1 0 16:32:04 ? 0:00 ora_smon_grid oracle 25118 1 0 16:32:04 ? 0:00 ora_cjq0_grid oracle 25120 1 0 16:32:04 ? 0:00 ora_rbal_grid oracle 25122 1 0 16:32:04 ? 0:00 ora_d000_grid oracle 25106 1 0 16:32:04 ? 0:00 ora_pmon_grid NoCOUG 2005, Daniel T. Liu

  14. Enabling Flashback Database • Make sure the database is in archive mode. • Configure the recovery area by setting the two parameters: • DB_RECOVERY_FILE_DEST • DB_RECOVERY_FILE_DEST_SIZE • Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature: SQL> STARTUP MOUNT EXCLUSIVE; SQL> ALTER DATABASE FLASHBACK ON; • Set the Flashback Database retention target: • DB_FLASHBACK_RETENTION_TARGET NoCOUG 2005, Daniel T. Liu

  15. Disabling Flashback Database • Disabling Flashback Database SQL> ALTER DATABASE FLASHBACK OFF; • Determine if Flashback Database is enabled SQL> select flashback_on 2 from v$database; FLASHBACK_ON ------------ YES NoCOUG 2005, Daniel T. Liu

  16. Monitoring Flashback Database • Monitoring Flashback Database SQL> select begin_time, flashback_data, 2 db_data, redo_data, ESTIMATED_FLASHBACK_SIZE 3 from v$flashback_database_stat; BEGIN_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE -------------------- -------------- ---------- ---------- ------------------------ Feb 22 2004 01:05:14 147456 2719744 92160 0 Feb 22 2004 00:05:09 3891200 5857280 2537984 252788736 Feb 21 2004 23:05:04 7979008 13615104 3385344 254877696 Feb 21 2004 22:05:00 14893056 19857408 17463296 255737856 Feb 21 2004 21:04:55 4210688 6422528 2598912 254361600 Feb 21 2004 20:04:51 4333568 8962048 2775552 256475136 Feb 21 2004 19:04:46 4431872 7028736 2804736 258588672 Feb 21 2004 18:04:41 4202496 8511488 2635264 260726784 Feb 21 2004 17:04:37 4030464 6938624 2546688 263012352 Feb 21 2004 16:04:32 4005888 7479296 2512384 265420800 Feb 21 2004 15:04:27 3874816 6864896 2471936 267927552 Feb 21 2004 14:04:23 4153344 7028736 2578944 270532608 Feb 21 2004 13:04:18 3825664 7675904 2497536 273113088 13 rows selected. NoCOUG 2005, Daniel T. Liu

  17. Flashback Database Retention • Monitor the Flashback Database retention target SQL> select * 2 from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------------------- ---------------- -------------- ------------------------ 2.2029E+12 Oct 06 2003 09:44:42 1440 48316416 • The default value for flashback retention time is 1440 minutes. NoCOUG 2005, Daniel T. Liu

  18. Flashback Database • Example 1: Flashback a Database using RMAN RMAN> FLASHBACK DATABASE 2> TO TIME = TO_DATE 3> (‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’); • Example 2: Flashback a database using SQL command SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24); SQL> FLASHBACK DATABASE TO SCN 76239; SQL> ALTER DATABASE RESETLOGS; NoCOUG 2005, Daniel T. Liu

  19. Flashback Drop • Prior to Oracle 10g, a DROP command permanently removed objects from the database. SQL> DROP TABLE CUSTOMERS; • In Oracle 10g, a DROP command places the object in the recycle bin. • The extents allocated to the segment are not reallocated until you purge the object. • You can restore the object from the recycle bin at any time. NoCOUG 2005, Daniel T. Liu

  20. Recycle Bin • A recycle bin contains all the dropped database objects until, • You permanently drop them with the PURGE command. • Recover the dropped objects with the FLASHBACK TABLE command. • There is no room in the tablespace for new rows or updates to existing rows. • The tablespace needs to be extended. NoCOUG 2005, Daniel T. Liu

  21. Recycle Bin • You can view the dropped objects in the recycle bin from two dictionary views: • USER_RECYCLEBIN: list all dropped user objects • DBA_RECYCLEBIN: list all dropped system-wide objects NoCOUG 2005, Daniel T. Liu

  22. Example 1: Dropping an Object SQL> create table test (col_a varchar(4)); Table created. SQL> select object_name, original_name, 2 type, createtime, droptime 3 from user_recyclebin; no rows selected SQL> drop table test; Table dropped. NoCOUG 2005, Daniel T. Liu

  23. Example 1: Dropping an Object SQL> select object_name, original_name, 2 type, createtime, droptime 3 from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41  NoCOUG 2005, Daniel T. Liu

  24. Example 1: Dropping an Object SQL> create table test (col_b varchar(4)); Table created. SQL> select object_name, original_name, 2 type, createtime, droptime 3 from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41  SQL> drop table test; Table dropped. NoCOUG 2005, Daniel T. Liu

  25. Example 1: Dropping an Object SQL> select object_name, original_name, 2 type, createtime, droptime 3 from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:07:33 2004-02-21:19:08:17 BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41 NoCOUG 2005, Daniel T. Liu

  26. Example 1: Dropping an Object SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TABLE 2004-02-21:19:08:17 TEST BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TABLE 2004-02-21:19:04:41 NoCOUG 2005, Daniel T. Liu

  27. Example 2: Restoring a Dropped Object • This example will restore a dropped table test.  SQL> flashback table “BIN$0+ktoVChEmXgNAAADiUEHQ==$0” to before drop; Flashback complete. NoCOUG 2005, Daniel T. Liu

  28. Example 3: Dropping a Table Permanently • This statement removes the table permanently: SQL> drop table test purge;   Table dropped. • This statement removes the table in the recycle bin: SQL> purge table "BIN$0+ktoVChEmXgNAAADiUEHQ==$0"; Table purged. NoCOUG 2005, Daniel T. Liu

  29. Example 4: Dropping a Tablespace • You can only issue this command when the tablespace users is empty. Object in the recycle bin of tablespace users will be purged: SQL> drop tablespace users; • When you issue this command, objects in the tablespace users are dropped. They are not placed in the recycle bin. Any objects in the recycle bin belonging to the tablespace users are purged. SQL> drop tablespace users including contents; NoCOUG 2005, Daniel T. Liu

  30. Example 5: Purging the Recycle Bin • This statement purges the user recycle bin: SQL> purge recyclebin;   Recyclebin purged. • This statement removes all objects from the recycle bin: SQL> purge dba_recyclebin;   DBA Recyclebin purged. • This statement purges all objects from tablespace users in the recycle bin: SQL> purge tablespace users; Tablespace purged. NoCOUG 2005, Daniel T. Liu

  31. Flashback Table • Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. • When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as, indexes, constraints, and triggers will be restored. NoCOUG 2005, Daniel T. Liu

  32. Flashback Table • Flashback Table operations are not valid for the following object types: • Tables that are part of a cluster • Materialized views • Advanced Queuing tables • Static data dictionary tables • System tables • Partitions of a table • Remote tables (via database link) NoCOUG 2005, Daniel T. Liu

  33. UNDO_RETENTION Parameter • Data used to recover a table is stored in the undo tablespace. You can use the parameter UNDO_RETENTION to set the amount of time you want undo information retained in the database. • The default value for UNDO_RETENTION is 900 seconds (15 minutes). NoCOUG 2005, Daniel T. Liu

  34. Guaranteed Retention • When an active transaction uses all the undo tablespace, the system will start reusing undo space that would have been retained unless you have specified RETENTION GUARANTEE for the tablespace. • To create an undo tablespace with the RETENTION GUARANTEE option, issue the following command: SQL> CREATE UNDO TABLEAPCE undo_tbs DATAFIEL ‘/u02/oradata/grid/undo_tbs01.dbf’ SIZE 1 G RETENTION GUARANTEE; NoCOUG 2005, Daniel T. Liu

  35. Flashback Table Privileges • You must have the FLASHBACK TABLE or FLASHBACK ANY TABLE system privilege to use the Flashback Table feature. NoCOUG 2005, Daniel T. Liu

  36. Example 1: Flashback Table using SCN • Table row movement must be enabled to flashback a table: SQL> ALTER TABLE billing ENABLE ROW MOVEMENT; • This statement brings a table ‘billing’ back to a certain SCN number; SQL> FLASHBACK TABLE billing TO SCN 76230; NoCOUG 2005, Daniel T. Liu

  37. Example 2: Flashback Table using TIMESTAMP • This statement brings a table ‘billing’ back to a certain timestamp: SQL> FLASHBACK TABLE billing TO TIMESTAMP TO_TIMESTAMP(‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’); NoCOUG 2005, Daniel T. Liu

  38. Flashback Versions Query • Flashback Query was first introduced in Oracle9i, to provide a way for you to view historical data. • In Oracle 10g, this feature has been extended. • You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. NoCOUG 2005, Daniel T. Liu

  39. Flashback Versions Query • You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row. • The Flashback Versions Query feature retrieves all committed occurrences of the row. • The row history data is stored in the undo tablespace. NoCOUG 2005, Daniel T. Liu

  40. Flashback Versions Query • The undo_retention initialization parameter specifies how long the database will keep the amount of committed undo information. • If a new transaction needs to use undo space and there is not enough free space left, any undo information older than the specified undo retention period will be overwritten. • You can set the undo tablespace option to RETENTION GUARANTEE to retain all row histories. NoCOUG 2005, Daniel T. Liu

  41. Example 1 • To verify the retention value for the tablespace, you can issue the following statement: SQL> select tablespace_name, retention 2 From dba_tablespaces;  TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY UNDOTBS1 NOGUARANTEE SYSAUX NOT APPLY TEMP NOT APPLY NoCOUG 2005, Daniel T. Liu

  42. Example 2 SQL> create table emp (name varchar2(10), salary number(8,2)); Table created. SQL> insert into emp values ('DANIEL',2000); 1 row created. SQL> commit; Commit complete. NoCOUG 2005, Daniel T. Liu

  43. Example 2 SQL> update emp set salary = 3000 where name = 'DANIEL'; 1 row updated. SQL> commit; Commit complete. NoCOUG 2005, Daniel T. Liu

  44. Example 2 SQL> select * from emp; NAME SALARY ---------- ---------- DANIEL 3000 SQL> select * from emp versions between scn minvalue and maxvalue; NAME SALARY ---------- ---------- DANIEL 3000 DANIEL 2000 NoCOUG 2005, Daniel T. Liu

  45. Flashback Transaction Query • It provides a way for you to view changes made to the database at the transaction level. • It allows you to diagnose problems in your database and perform analysis and audit transactions. • You can use this feature in conjunction with the Flash Versions Query feature to roll back the changes made by a transaction. NoCOUG 2005, Daniel T. Liu

  46. Flashback Transaction Query • You can retrieve the transaction history from flashback_transaction_query view: Name Null? Type ------------------------------------ -------- -------------- XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000) NoCOUG 2005, Daniel T. Liu

  47. Example SQL> select versions_xid, name, salary 2 from emp 3 versions between scn minvalue and maxvalue; VERSIONS_XID NAME SALARY ---------------- ---------- ---------- 0003000E00000FE2 DANIEL 3000 DANIEL 2000 SQL> select * 2 from flashback_transaction_query 3where xid = '0003000E00000FE2'; NoCOUG 2005, Daniel T. Liu

  48. Example SQL> select xid, start_scn, start_timestamp, 2 table_name, undo_sql 3 from flashback_transaction_query 4 where xid = '0009001F000000B2‘; XID START_SCN START_TIMESTAMP TABLE_NAME UNDO_SQL ---------------- ---------- -------------------- ---------- ------------------------------------------------------------------------------- 0009001F000000B2 714980 Feb 21 2004 23:30:31 EMP update "ORACLE"."EMP" set "SALARY" = ‘2000' where ROWID = 'AAAMWJAAEAAAAFsAAA'; NoCOUG 2005, Daniel T. Liu

  49. Summary - Flashback • Flashback Database • Flashback Drop • Flashback Table • Flashback Versions Query • Flashback Transaction Query NoCOUG 2005, Daniel T. Liu

  50. Enhanced RMAN Features • Automated File Creation During Recovery • Simplified Recovery Through Resetlogs • Change-Aware Incremental Backups • Automated Disk-Based Backup and Recovery • RMAN Database Dropping and Deregistration NoCOUG 2005, Daniel T. Liu

More Related