Introduction

1. Introduction Oracle Database 11g

2. Overview • This seminar introduces the new features of Oracle Database 11g. • Previous experience with Oracle databases is required for a full understanding of many new features, particularly Oracle Database 10g, releases 1 and 2.

3. Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private DatabaseBuilt-in Java VM Partitioning Support Built-in Messaging Object Relational Support Multimedia SupportData Warehousing Optimizations Parallel Operations Distributed SQL and Transaction Support Cluster and MPP Support Multiversion Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation Oracle Database Innovation 30 years of sustained innovation… … continuing with Oracle Database 11g

4. Customer Testimonials “Oracle customers are highly satisfied with its Real Application Clusters and Automatic Storage Management when pursuing scale-out strategies.” Mark Beyer, Gartner, December 2006 “By consolidating with Oracle Grid Computing on Intel/Linux, we are witnessing about a 50% reduction in costs with increased performance.” Tim Getsay, Assistant Vice-Chancellor Management Information Systems Vanderbilt University

5. Enterprise Grid Computing SMPdominance Oracle RACclustersforavailability Managing change across the enterprise Grids oflow-costhardware andstorage

6. Oracle Database 11g: Focus Areas • Manageability • Availability • Performance • Business intelligence and data warehousing • Security

7. Oracle Database 11g: Focus Areas • Information management • Content management • XML • Oracle Text • Spatial • Multimedia and medical imaging • Application development • PL/SQL • .NET • PHP • SQL Developer

8. Management Automation Autotuning Advisory Instrumentation Oracle RAC Storage Backup Memory Schema Recovery Replication Apps/SQL

9. Oracle Database 11g: New Features Overview Seminar

10. Oracle Database 11g: Change Management Overview Seminar

11. Further Information • For more information about topics that are not covered in this course, refer to the following: • Oracle Database 11g: New Features eStudies • http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=160A comprehensive series of self-paced online courses covering all new features in detail • Oracle By Example series: Oracle Database 11g • http://www.oracle.com/technology/obe/11gr1_db/index.htm • Oracle OpenWorld • http://www.oracle.com/oracleworld/

12. Managing Storage Oracle Database 11g

13. Objectives • After completing this lesson, you should be able to: • Use ASM Fast Mirror Resync to improve disk failure recovery times • Set up ASM Fast Mirror Resync • Configure ASM preferred mirror failure groups • Use the SYSASM privilege to manage ASM disks • Use the compatibility modes for disk groups • Use ASMCMD command extensions to back up and restore disk groups • Discuss LOB improvements using SecureFiles • Use SQL and PL/SQL APIs to access SecureFiles • Use temporary tablespace enhancements

14. Automatic Storage Management (ASM) Enhancements • Availability: • ASM Fast Mirror Resync • ASM preferred mirror failure groups • Scalability: • Increased limits • Security: • New SYSASM privilege • Manageability: • Automatic extent size adjustments • ASM disk group attributes • New manageability options • Additional ASMCMD extensions

15. 1 2 4 3 ASM Fast Mirror Resync: Overview ASM redundancy used Disk access failure Primary Secondary Oracle Database 11g Disk again accessible: Only need to resync modified ASM data extent Failure time < DISK_REPAIR_TIME

16. Setting Up ASM Fast Mirror Resync ALTER DISKGROUP dgroupA SET ATTRIBUTE 'DISK_REPAIR_TIME'='3H'; • V$ASM_ATTRIBUTE: Views current resync attributes • V$ASM_DISK, V$ASM_DISK_IOSTAT: Shows repair time left • V$ASM_OPERATION: Shows disk resync operation ALTER DISKGROUP dgroupA OFFLINE DISKS IN FAILGROUP controller2 DROP AFTER 5H; ALTER DISKGROUP dgroupA ONLINE DISKS IN FAILGROUP controller2 POWER 2 WAIT; ALTER DISKGROUP dgroupA DROP DISKS IN FAILGROUP controller2 FORCE;

17. P S ASM Preferred Mirror Read: Overview Site A Site B S P Site A Site B Primary S P Secondary

18. ASM Preferred Mirror Read: Setup Setup On first instance ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEA On second instance ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEB Monitor SELECT preferred_read FROM v$asm_disk; SELECT * FROM v$asm_disk_iostat;

19. S P ASM Preferred Mirror Read: Best Practice Two sites/normal redundancy Two sites/high redundancy P S P S P S S S Only two failure groups: one for each instance Max four failure groups: two for each instance Three sites/high redundancy P S S Primary Only three failure groups: one for each instance Secondary

20. ASM Scalability and Performance Enhancements • The ASM data extent size grows automatically according to the file size. • ASM supports variable sizes to: • Raise the maximum possible file size • Reduce memory utilization in the shared pool • No administration is needed apart from manual rebalance (in case of fragmentation).

21. SYSASM: Overview • Use the SYSASM privilege to manage ASM instances and avoid overlap between DBAs and storage administrators. • SYSDBA to be deprecated: • Oracle Database 11g, Release 1 behaves as in 10g. • In future releases, SYSDBA is restricted in ASM instances. SQL> CONNECT / AS SYSASM SQL> CREATE USER ossysasmusername IDENTIFIED by passwd; SQL> GRANT SYSASM TO ossysasmusername; SQL> CONNECT ossysasmusername / passwd AS SYSASM; SQL> DROP USER ossysasmusername;

22. ASM Disk Group Compatibility • Compatibility of each disk group is separately controllable: • RDBMS compatibility controls minimum client level. • ASM compatibility controls ASM metadata on disk structure. • Useful with heterogeneous environments. • Setting disk group compatibility is irreversible. ASM instance DB instance ASM diskgroup COMPATIBLE.RDBMS >= COMPATIBLE <= <= COMPATIBLE COMPATIBLE.ASM

23. ASM Disk Group Attributes • Name • Property • Values • Description • au_size • C • 1|2|4|8|16|32|64MB • Size of allocation units in the disk group • compatible.rdbms • AC • Valid database version • Format of messages exchanged between DB and ASM • compatible.asm • AC • Valid ASM instance version • Format of ASM metadata structures on disk • disk_repair_time • AC • 0 M to 232 D • Length of time before removing a disk once offline • template.tname.redundancy • A • UNPROTECT|MIRROR|HIGH • Redundancy of specified template • template.tname.stripe • A • COARSE|FINE • Striping attribute of specified template A: ALTERcommand C: CREATEcommand CREATE DISKGROUP DATA NORMAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2' ATTRIBUTE 'compatible.asm'='11.1';

24. Simplified Diskgroup Commands

25. md_restore md_backup lsdsk cp ASMCMD Extensions User-created directoriesTemplatesDisk group compatibilityDisk group nameDisk names and failure groups full $ asmcmd help nodg newdg

26. ASMCMD Extension: Examples ASMCMD> md_backup –b jfv_backup_file -g data Disk group to be backed up: DATA# Current alias directory path: jfv ASMCMD> 1 Unintentional disk group drop 2 ASMCMD> md_restore -b jfv_backup_file -t full -g data Disk group to be restored: DATA# ASMCMDAMBR-09358, Option -t newdg specified without any override options. Current Diskgroup being restored: DATA Diskgroup DATA created! User Alias directory +DATA/jfv created! ASMCMD> 3 Restore disk group files using RMAN 4

27. Deduplication Compression Data path optimizations PL/SQL APIs Encryption SecureFiles: Overview SecureFiles

28. Enabling SecureFiles Storage • SecureFiles storage can be enabled by using: • DB_SECUREFILE initialization parameter with the following valid values: • ALWAYS |PERMITTED | NEVER | IGNORE • ALTER SESSION | SYSTEM command: SQL>ALTER SYSTEM SET db_securefile = 'ALWAYS';

29. Creating SecureFiles CREATE TABLE func_spec( id number, doc CLOB ENCRYPT USING 'AES128' ) LOB(doc) STORE AS SECUREFILE (DEDUPLICATE LOB CACHE NOLOGGING); CREATE TABLE test_spec ( id number, doc CLOB) LOB(doc) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING); CREATE TABLE design_spec (id number, doc CLOB) LOB(doc) STORE AS SECUREFILE (ENCRYPT); CREATE TABLE design_spec (id number, doc CLOB ENCRYPT) LOB(doc) STORE AS SECUREFILE;

30. Altering SecureFiles ALTER TABLE t1 MODIFY LOB(a) ( KEEP_DUPLICATES ); ALTER TABLE t1 MODIFY LOB(a) ( DEDUPLICATE LOB VALIDATE ); ALTER TABLE t1 MODIFY PARTITION p1 LOB(a) ( DEDUPLICATE LOB ); Disable deduplication. Enable deduplication. Enable partition deduplication. ALTER TABLE t1 MODIFY LOB(a) ( NOCOMPRESS ); ALTER TABLE t1 MODIFY LOB(a) (COMPRESS HIGH); ALTER TABLE t1 MODIFY PARTITION p1 LOB(a) ( COMPRESS HIGH ); Disable compression. Enable compression. Enable compression on SecureFiles within a single partition. ALTER TABLE t1 MODIFY ( a CLOB ENCRYPT USING '3DES168'); ALTER TABLE t1 MODIFY PARTITION p1 ( LOB(a) ( ENCRYPT ); ALTER TABLE t1 MODIFY ( a CLOB ENCRYPT IDENTIFIED BY ghYtp); Enable encryption using 3DES168. Enable encryption on partition. Enable encryption and build the encryption key using a password.

31. SecureFiles Accessing SecureFiles • DBMS_LOB • GETOPTIONS • SETOPTIONS DBMS_SPACE.SPACE_USAGE

32. SecureFiles Migrating to SecureFiles

33. Temporary Tablespace Shrink • Sort segment extents are managed in memory when physically allocated. • Can be an issue after big sorts are done • To release physical space from your disks, shrink temporary tablespaces: • Locally managed temporary tablespaces • Online operation CREATE TEMPORARY TABLESPACE temp TEMPFILE 'tbs_temp.dbf' SIZE 600m REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m; ALTER TABLESPACE temp SHRINK SPACE [KEEP 200m]; ALTER TABLESPACE temp SHRINK TEMPFILE'tbs_temp.dbf';

34. Tablespace Option for Creating Temporary Tables • Specify the temporary tablespace to use for your global temporary tables. • Decide the proper temporary extent size. CREATE TEMPORARY TABLESPACE temp TEMPFILE 'tbs_temp.dbf' SIZE 600m REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m; CREATE GLOBAL TEMPORARY TABLE temp_table (c varchar2(10)) ON COMMIT DELETE ROWS TABLESPACE temp;

35. Summary • In this lesson, you should have learned how to: • Use ASM Fast Mirror Resync to improve disk failure recovery times • Set up ASM Fast Mirror Resync using SQL • Configure preferred mirror groups using the ASM_PREFERRED_READ_FAILURE_GROUPS parameter • Use the SYSASM privilege to manage ASM disks • Use the compatibility modes for disk groups • Use ASMCMD command extensions to back up and restore disk groups • Discuss LOB improvements using SecureFiles • Use temporary tablespace enhancements

36. High Availability Using the Data Recovery Advisor and Flashback

37. Objectives • After completing this lesson, you should be able to: • Perform proactive failure checks • Query the Data Recovery Advisor views • Enable tracking of table data by using Flashback Data Archive • Back out data changes by using Flashback Transaction

38. Repairing Data Failures • Oracle Database 11g offers the following advancements in the repair of data failures: • Data Recovery Advisor analyzes failures based on symptoms and determines repair strategies. • Data Guard provides failover to a standby database, so that your operations are not affected by down time. • Flashbacktechnology protects the life cycle of a row and assists in repairing logical problems.

39. 1. Assess data failures. 5. Perform proactive checks. Data Recovery Advisor • Offers fast detection, analysis, and repair of failures • Minimizes down time and run-time failures • Alleviates disruptions for users • Can be implemented using: • EM GUI • RMAN command line 2. List failures by severity. 3. Advise on repair. 4. Choose and execute repair.

40. Listing Data Failures

41. Advising on Repair • (1) After manual repair • (2) Automatic repair 1 2a 2b

42. Setting Corruption-Detection Parameters

43. Flashback Data Archive: Overview • Transparently tracks historical changes to all Oracle data in a highly secure and efficient manner • Secure • No possibility of modifying 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

44. Flashback Data Archive Comparison

45. FBDA Flashback Data Archive: Overview For long-retention requirements that exceed undo Undo data Original data in buffer cache DML operations Example: Three flashback data archives with retention of: 1 year 2 years Flashback data archives stored in tablespaces 5 years

46. Creating a Flashback Data Archive: Example 1 • 1) Creating a flashback data archive: 2) Enabling history tracking for a specific table: Viewing the historical data: CREATE FLASHBACK ARCHIVE fla1 TABLESPACE tbs1 QUOTA 10G RETENTION 5 YEAR; 2 ALTER TABLE hr.employees FLASHBACK ARCHIVE fla1; 3 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');

47. Configuring a Default Flashback Data Archive: Example • Using a default flashback archive: Create a default flashback data archive: Enable history tracking for a table: The name of the flashback data archive is not needed because the default is used. • Disable history tracking: 1 CREATE FLASHBACK ARCHIVE DEFAULT fla2 TABLESPACE tbs1 QUOTA 10G RETENTION 2 YEAR; 2 ALTER TABLE stock_data FLASHBACK ARCHIVE; ALTER TABLE stock_data NO FLASHBACK ARCHIVE;

48. Using Flashback Data Archive: Examples Optionally, adding space: ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 10G; • Optionally, changing retention time: ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; • Optionally, purging data: ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day); • Optionally, dropping a flashback data archive: DROP FLASHBACK ARCHIVE fla1;

49. Flashing Back a Transaction • Oracle Database 11g allows you to flash back a transaction using Enterprise Manager (EM) or the command line. • EM calls the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure with the NOCASCADE option. • Supplemental logging must be enabled. • You must have the SELECT, FLASHBACK, and data manipulation language (DML) privileges on all affected tables.

50. Flashback Transaction Wizard: Sample