I Introduction Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
Overview • This seminar introduces the new features of Oracle Database 11 g. • Previous experience with Oracle databases is required for a full understanding of many new features, particularly Oracle Database 10 g, releases 1 and 2. I-2 Copyright © 2007, Oracle. All rights reserved.
Oracle Database Innovation 30 years of sustained innovation… Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private Database Built-in Java VM Partitioning Support Built-in Messaging Object Relational Support Multimedia Support Data Warehousing Optimizations Parallel Operations Distributed SQL and Transaction Support Cluster and MPP Support Multiversion Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation I-3 … continuing with Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
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 I-4 Copyright © 2007, Oracle. All rights reserved.
Enterprise Grid Computing SMP dominance I-5 Oracle RAC clusters for availability Grids of low-cost hardware and storage Copyright © 2007, Oracle. All rights reserved. Managing change across the enterprise
Oracle Database 11 g: Focus Areas • • • I-6 Manageability Availability Performance Business intelligence and data warehousing Security Copyright © 2007, Oracle. All rights reserved.
Oracle Database 11 g: Focus Areas • Information management – – – Content management XML Oracle Text Spatial Multimedia and medical imaging • Application development – – I-7 PL/SQL. NET PHP SQL Developer Copyright © 2007, Oracle. All rights reserved.
Management Automation Autotuning Advisory I-8 Copyright © 2007, Oracle. All rights reserved. Replication Recovery Oracle RAC Schema Apps/SQL Memory Backup Storage Instrumentation
Oracle Database 11 g: New Features Overview Seminar Lesson Title 1 2 Using the Data Recovery Advisor and Flashback 3 RMAN and Data Guard Enhancements 4 Security: New Features 5 Intelligent Infrastructure 6 Data Warehousing Enhancements 7 I-9 Managing Storage Additional Performance Enhancements Copyright © 2007, Oracle. All rights reserved.
Oracle Database 11 g: Change Management Overview Seminar Lesson Title 1 2 Using Database Replay 3 Using SQL Performance Analyzer 4 Performing Online Changes 5 Using SQL Plan Management 6 Diagnosing Problems 7 I - 10 Setting Up the Test Environment Installing Patches Copyright © 2007, Oracle. All rights reserved.
Further Information For more information about topics that are not covered in this course, refer to the following: • Oracle Database 11 g: New Features e. Studies – http: //education. oracle. com/pls/web_prod-plqdad/db_pages. getpage? page_id=160 A comprehensive series of self-paced online courses covering all new features in detail • Oracle By Example series: Oracle Database 11 g – http: //www. oracle. com/technology/obe/11 gr 1_db/ind ex. htm • Oracle Open. World – http: //www. oracle. com/oracleworld/ I - 11 Copyright © 2007, Oracle. All rights reserved.
1 Managing Storage Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
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 Secure. Files • Use SQL and PL/SQL APIs to access Secure. Files • Use temporary tablespace enhancements 1 - 13 Copyright © 2007, Oracle. All rights reserved.
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 1 - 14 Copyright © 2007, Oracle. All rights reserved.
ASM Fast Mirror Resync: Overview ASM redundancy used 2 Disk access failure Secondary Primary 1 Oracle Database 11 g 4 Disk again accessible: Only need to resync modified ASM data extent 1 - 15 3 Failure time < DISK_REPAIR_TIME Copyright © 2007, Oracle. All rights reserved.
Setting Up ASM Fast Mirror Resync ALTER DISKGROUP dgroup. A SET ATTRIBUTE 'DISK_REPAIR_TIME'='3 H'; ALTER DISKGROUP dgroup. A OFFLINE DISKS IN FAILGROUP controller 2 DROP AFTER 5 H; ALTER DISKGROUP dgroup. A ONLINE DISKS IN FAILGROUP controller 2 POWER 2 WAIT; ALTER DISKGROUP dgroup. A DROP DISKS IN FAILGROUP controller 2 FORCE; • 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 1 - 16 Copyright © 2007, Oracle. All rights reserved.
ASM Preferred Mirror Read: Overview Site A Site B S P Site A Site B P Primary S Secondary 1 - 17 S P Copyright © 2007, Oracle. All rights reserved.
ASM Preferred Mirror Read: Setup ASM_PREFERRED_READ_FAILURE_GROUPS=DATA. SITEA On first instance ASM_PREFERRED_READ_FAILURE_GROUPS=DATA. SITEB On second instance Monitor SELECT preferred_read FROM v$asm_disk; SELECT * FROM v$asm_disk_iostat; 1 - 18 Copyright © 2007, Oracle. All rights reserved.
ASM Preferred Mirror Read: Best Practice Two sites/normal redundancy P Two sites/high redundancy S P S S P Only two failure groups: one for each instance Max four failure groups: two for each instance Three sites/high redundancy P P Primary S Only three failure groups: one for each instance S Secondary 1 - 19 S Copyright © 2007, Oracle. All rights reserved.
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). 1 - 20 Copyright © 2007, Oracle. All rights reserved.
SYSASM: Overview • Use the SYSASM privilege to manage ASM instances and avoid overlap between DBAs and storage administrators. 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; • SYSDBA to be deprecated: – Oracle Database 11 g, Release 1 behaves as in 10 g. – In future releases, SYSDBA is restricted in ASM instances. 1 - 21 Copyright © 2007, Oracle. All rights reserved.
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. DB instance ASM disk group ASM instance COMPATIBLE >= COMPATIBLE. RDBMS <= COMPATIBLE. ASM <= COMPATIBLE 1 - 22 Copyright © 2007, Oracle. All rights reserved.
ASM Disk Group Attributes Name au_size Property C Values Description 1|2|4|8|16|32|64 MB 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_repair_time AC 0 M to 232 D Length of time before removing a disk once offline template. tname. redundancy A UNPROTECT|MIRROR|HIG H Redundancy of specified template. tname. stripe A COARSE|FINE Striping attribute of specified template A: ALTER command C: CREATE command CREATE DISKGROUP DATA NORMAL REDUNDANCY DISK '/dev/raw 1', '/dev/raw 2' ATTRIBUTE 'compatible. asm'='11. 1'; 1 - 24 Copyright © 2007, Oracle. All rights reserved.
Simplified Diskgroup Commands COMMAND Effect ALTER DISKGROUP DATA CHECK; Checks all the metadata directories by default ALTER DISKGROUP data MOUNT RESTRICT; When a disk group is mounted in RESTRICTED mode, clients cannot access the files in a disk group. ALTER DISKGROUP data MOUNT FORCE; Mounts the disk group even if some disks belonging to the disk group are not accessible DROP DISKGROUP data FORCE INCLUDING CONTENTS; Enables users to drop a disk group that cannot be mounted; fails if the disk group is mounted anywhere 1 - 25 Copyright © 2007, Oracle. All rights reserved.
ASMCMD Extensions User-created directories Templates Disk group compatibility Disk group name Disk names and failure groups cp md_backup full $ asmcmd help md_restore lsdsk 1 - 26 Copyright © 2007, Oracle. All rights reserved. newdg nodg
ASMCMD Extension: Examples 1 2 3 4 1 - 28 ASMCMD> md_backup –b jfv_backup_file -g data Disk group to be backed up: DATA# Current alias directory path: jfv ASMCMD> Unintentional disk group drop 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> Restore disk group files using RMAN Copyright © 2007, Oracle. All rights reserved.
Secure. Files: Overview Deduplication Compression Data path optimizations Secure. Files PL/SQL APIs Encryption 1 - 29 Copyright © 2007, Oracle. All rights reserved.
Enabling Secure. Files Storage Secure. Files 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'; 1 - 31 Copyright © 2007, Oracle. All rights reserved.
Creating Secure. Files CREATE TABLE func_spec( id number, doc CLOB ENCRYPT USING 'AES 128' ) 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 LOB(doc) STORE AS SECUREFILE (ENCRYPT); CLOB) CREATE TABLE design_spec (id number, doc CLOB ENCRYPT) LOB(doc) STORE AS SECUREFILE; 1 - 32 Copyright © 2007, Oracle. All rights reserved.
Altering Secure. Files ALTER TABLE t 1 MODIFY LOB(a) ( KEEP_DUPLICATES ); Disable deduplication. Enable deduplication. ALTER TABLE t 1 MODIFY LOB(a) ( DEDUPLICATE LOB VALIDATE ); Enable partition ALTER TABLE t 1 deduplication. MODIFY PARTITION p 1 LOB(a) ( DEDUPLICATE LOB ); ALTER TABLE t 1 MODIFY LOB(a) ( NOCOMPRESS ); ALTER TABLE t 1 MODIFY LOB(a) (COMPRESS HIGH); Disable compression. Enable compression on Secure. Files within a single partition. ALTER TABLE t 1 MODIFY PARTITION p 1 LOB(a) ( COMPRESS HIGH ); Enable encryption using 3 DES 168. ALTER TABLE t 1 MODIFY ( a CLOB ENCRYPT USING '3 DES 168'); ALTER TABLE t 1 MODIFY PARTITION p 1 ( LOB(a) ( ENCRYPT ); Enable encryption on partition. ALTER TABLE t 1 MODIFY ( a CLOB ENCRYPT IDENTIFIED BY gh. Ytp); 1 - 33 Enable encryption and build the encryption key using a password. Copyright © 2007, Oracle. All rights reserved.
Accessing Secure. Files DBMS_LOB – GETOPTIONS – SETOPTIONS DBMS_SPACE_USAGE 1 - 34 Copyright © 2007, Oracle. All rights reserved. Secure. Files
Migrating to Secure. Files 1 - 35 Copyright © 2007, Oracle. All rights reserved.
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 600 m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1 m; ALTER TABLESPACE temp SHRINK SPACE [KEEP 200 m]; ALTER TABLESPACE temp SHRINK TEMPFILE 'tbs_temp. dbf'; 1 - 36 Copyright © 2007, Oracle. All rights reserved.
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 600 m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1 m; CREATE GLOBAL TEMPORARY TABLE temp_table (c varchar 2(10)) ON COMMIT DELETE ROWS TABLESPACE temp; 1 - 37 Copyright © 2007, Oracle. All rights reserved.
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 Secure. Files • Use temporary tablespace enhancements 1 - 38 Copyright © 2007, Oracle. All rights reserved.
2 High Availability Using the Data Recovery Advisor and Flashback Copyright © 2007, Oracle. All rights reserved.
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 2 - 40 Copyright © 2007, Oracle. All rights reserved.
Repairing Data Failures Oracle Database 11 g 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. • Flashback technology protects the life cycle of a row and assists in repairing logical problems. 2 - 41 Copyright © 2007, Oracle. All rights reserved.
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: 1. Assess data failures. – EM GUI – RMAN command line 2. List failures by severity. 3. Advise on repair. 4. Choose and execute repair. 5. Perform proactive checks. 2 - 42 Copyright © 2007, Oracle. All rights reserved.
Listing Data Failures 2 - 44 Copyright © 2007, Oracle. All rights reserved.
Advising on Repair (1) After manual repair (2) Automatic repair 1 2 a 2 b 2 - 45 Copyright © 2007, Oracle. All rights reserved.
Setting Corruption-Detection Parameters DB_ULTRA_SAFE OFF DATA_ONLY DATA_AND_INDEX DB_BLOCK_CHECKING OFF or FALSE MEDIUM FULL or TRUE DB_BLOCK_CHECKSUM TYPICAL FULL DB_LOST_WRITE_PROTECT TYPICAL 2 - 46 Copyright © 2007, Oracle. All rights reserved.
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 2 - 47 Copyright © 2007, Oracle. All rights reserved.
Flashback Data Archive Comparison Flashback Data Archive Flashback Database Main benefit Access to data at any point in time without changing the current data Physically moves entire database back in time Operation Online operation, tracking enabled, minimal resource usage Offline operation, requires preconfiguration and resources Granularity Table Database Access point-intime Any number per table One per database 2 - 48 Copyright © 2007, Oracle. All rights reserved.
Flashback Data Archive: Overview For long-retention requirements that exceed undo Original data in buffer cache Undo data DML operations FBDA Example: Three flashback data archives with retention of: 1 year Flashback data archives stored in tablespaces 2 - 49 2 years 5 years Copyright © 2007, Oracle. All rights reserved.
Creating a Flashback Data Archive: Example 1 1) Creating a flashback data archive: CREATE FLASHBACK ARCHIVE fla 1 TABLESPACE tbs 1 QUOTA 10 G RETENTION 5 YEAR; 2 Enabling history tracking for a specific table: 2) ALTER TABLE hr. employees FLASHBACK ARCHIVE fla 1; 3 Viewing the historical data: SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMP TO_TIMESTAMP ('2007 -01 -01 00: 00', 'YYYY-MM-DD HH 24: MI: SS'); 2 - 50 Copyright © 2007, Oracle. All rights reserved.
Configuring a Default Flashback Data Archive: Example Using a default flashback archive: 1 Create a default flashback data archive: CREATE FLASHBACK ARCHIVE DEFAULT fla 2 TABLESPACE tbs 1 QUOTA 10 G RETENTION 2 YEAR; 2 Enable history tracking for a table: ALTER TABLE stock_data FLASHBACK ARCHIVE; The name of the flashback data archive is not needed because the default is used. Disable history tracking: ALTER TABLE stock_data NO FLASHBACK ARCHIVE; 2 - 51 Copyright © 2007, Oracle. All rights reserved.
Using Flashback Data Archive: Examples Optionally, adding space: ALTER FLASHBACK ARCHIVE fla 1 ADD TABLESPACE tbs 3 QUOTA 10 G; Optionally, changing retention time: ALTER FLASHBACK ARCHIVE fla 1 MODIFY RETENTION 2 YEAR; Optionally, purging data: ALTER FLASHBACK ARCHIVE fla 1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' day); Optionally, dropping a flashback data archive: DROP FLASHBACK ARCHIVE fla 1; 2 - 52 Copyright © 2007, Oracle. All rights reserved.
Flashing Back a Transaction Oracle Database 11 g 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. 2 - 53 Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard: Sample 2 - 54 Copyright © 2007, Oracle. All rights reserved.
Flashback Transaction Wizard: Sample 2 - 55 Copyright © 2007, Oracle. All rights reserved.
Validating Dependencies The TRANSACTION_BACKOUT procedure checks dependencies such as: • Write-after-write (WAW) • Primary and unique constraints TX 1 TX 2 R 1 R 2 R 3 R 4 R 5 2 - 56 Copyright © 2007, Oracle. All rights reserved.
Dependency Report • The dependency report is generated in the following views: – DBA_FLASHBACK_TXN_STATE – DBA_FLASHBACK_TXN_REPORT • Review the dependency report, which shows all transactions backed out. • You then need to explicitly commit or roll back to make the changes permanent. SQL> SELECT * FROM DBA_FLASHBACK_TXN_STATE; COMPENSATING_XID --------0500150069050000 2 - 57 XID BACKOUT_MODE DEPENDENT_XID USER# ---------------03000000 A 9050000 4 0 05001 E 0063050000 4 03000000 A 9050000 0 Copyright © 2007, Oracle. All rights reserved.
Summary In this lesson, you should have learned how to: • Perform proactive failure checks by using the Data Recovery Advisor • Enable tracking of table data by using Flashback Data Archive • Back out data changes by using Flashback Transaction 2 - 58 Copyright © 2007, Oracle. All rights reserved.
3 High Availability RMAN and Data Guard Enhancements Copyright © 2007, Oracle. All rights reserved.
Objectives After completing this lesson, you should be able to: • Configure archive log deletion policies • Duplicate active databases using Oracle network (without backups) • Back up large files in multiple sections • Create archival backups for long-term storage • Query a physical standby database while redo is applied • Control the location of SQL Apply event information • Set the retention target for remote archived log files • Use the logical standby database flash recovery area • Create a snapshot standby database 3 - 60 Copyright © 2007, Oracle. All rights reserved.
RMAN Enhancements in Oracle Database 11 g • • Enhanced archive log deletion policies Database duplication made “network aware” Intrafile parallel backup and restore for very large files Archival backups for long-term storage Merging catalogs for enhanced recovery RMAN data recovery commands RMAN security enhancements 3 - 61 Copyright © 2007, Oracle. All rights reserved.
Duplicating a Database • Use with network (no backups required) – Includes a customized SPFILE • Use Enterprise Manager or the RMAN command line. TCP/IP Destination or AUXILIARY database Active source or TARGET database 3 - 63 Copyright © 2007, Oracle. All rights reserved.
Active Database Duplication: Selecting the Source 3 - 64 Copyright © 2007, Oracle. All rights reserved.
RMAN DUPLICATE Command DUPLICATE TARGET DATABASE TO aux FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/u 01', '/u 31' SET SGA_MAX_SIZE = 200 M SET SGA_TARGET = 125 M SET LOG_FILE_NAME_CONVERT = '/u 01', '/u 31' DB_FILE_NAME_CONVERT = '/u 01', '/u 31'; 3 - 65 Copyright © 2007, Oracle. All rights reserved.
Creating a Standby Database with the DUPLICATE Command DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE PARAMETER_VALUE_CONVERT '/u 01', '/u 31' SET "DB_UNIQUE_NAME"="FOO" SET SGA_MAX_SIZE = "200 M" SET SGA_TARGET = "125 M" SET LOG_FILE_NAME_CONVERT = '/u 01', '/u 31' DB_FILE_NAME_CONVERT = '/u 01', '/u 31'; 3 - 66 Copyright © 2007, Oracle. All rights reserved.
Parallel Backup and Restore for Very Large Files • Are created by RMAN with the SECTION SIZE value • Are processed independently (serially or in parallel) • Produce multipiece backup sets • Improve performance of the backup 3 - 67 Copyright © 2007, Oracle. All rights reserved.
Using RMAN Multisection Backups New option for BACKUP and VALIDATE DATAFILE commands: SECTION SIZE <integer> [M | K | G] Channel 1 Section 1 Channel 2 Section 2 Channel 3 Section 3 Channel 4 Section 4 One large data file 3 - 68 Copyright © 2007, Oracle. All rights reserved.
![Creating Archival Backups KEEP {FOREVER | UNTIL TIME [=] ' date_string '} NOKEEP [RESTORE](https://present5.com/presentation/cdb910261e76084f73bbd1ade99ae4fb/image-64.jpg "Creating Archival Backups KEEP {FOREVER | UNTIL TIME [=] ' date_string '} NOKEEP [RESTORE")
Creating Archival Backups KEEP {FOREVER | UNTIL TIME [=] ' date_string '} NOKEEP [RESTORE POINT rsname] 3 - 69 Copyright © 2007, Oracle. All rights reserved.
Archival Database Backup 1) Archiving a database backup: 1 CONNECT TARGET / CONNECT CATALOG rman/rman@catdb CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER RESTORE POINT END_OF_2006; 2 Changing the status of a database copy: 2) CHANGE COPY OF DATABASE CONTROLFILE NOKEEP; 3 - 70 Copyright © 2007, Oracle. All rights reserved.
IMPORT CATALOG RMAN Command Oracle Database 11 g extends the recovery catalog functionality by allowing the merge of recovery catalogs. • Importing metadata for all registered databases: IMPORT CATALOG cat 102/oracle@srcdb; • Importing metadata for two registered databases: IMPORT CATALOG cat 92/oracle@catdb DBID=1423241, 1423242; • Importing metadata from multiple catalogs: IMPORT CATALOG cat 102/rman@srcdb; IMPORT CATALOG cat 101/rman@srcdb; IMPORT CATALOG cat 92/rman@srcdb NO UNREGISTER; 3 - 71 Copyright © 2007, Oracle. All rights reserved.
RMAN Data Recovery Commands The Data Recovery Advisor command extensions for RMAN are listed in the following table: RMAN command Action LIST FAILURE Lists previously executed failure assessment CHANGE FAILURE Changes or closes one or more failures ADVISE FAILURE Displays the recommended repair option REPAIR FAILURE Repairs failure and closes 3 - 72 Copyright © 2007, Oracle. All rights reserved.
RMAN Security Enhancements • Configure transparent encrypted backups with: RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; or with: RMAN> SET ENCRYPTION ON; • Create and use the virtual private catalog: – For groups of databases and users – To consolidate repositories and maintain separate responsibilities 3 - 73 Copyright © 2007, Oracle. All rights reserved.
Improved Integration of RMAN and Data Guard • Set RMAN-persistent configurations for each database in the Data Guard configuration without connecting to the specific database as TARGET. • Restore a backup control file to a standby control file, and vice versa. – No need to back up a standby control file on the primary database to create a new standby database – Existing control file backup used to RESTORE AS STANDBY automatically • BACKUP, RESTORE, and RECOVER work transparently with any database in the configuration. • Create server parameter file (SPFILE) backups for each database in the configuration. 3 - 74 Copyright © 2007, Oracle. All rights reserved.
Real-Time Query and Physical Standby Databases Redo transport Primary database Redo stream Redo apply Physical standby database Queries SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 2 USING CURRENT LOGFILE DISCONNECT FROM SESSION; 3 - 75 Copyright © 2007, Oracle. All rights reserved.
Compressing Redo Data Enable compression of archived redo logs files during transmission to the standby database: • Set the COMPRESSION attribute on the LOG_ARCHIVE_DEST_n initialization parameter: SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='SERVICE=dest 1 2> COMPRESSION=ENABLE'; • Use the Oracle Data Guard Broker’s Redo. Compression property: DGMGRL> EDIT DATABASE <db_unique_name> SET PROPERTY Redo. Compression = {'ENABLE' | 'DISABLE'} 3 - 76 Copyright © 2007, Oracle. All rights reserved.
Dynamically Setting SQL Apply Parameters SQL Apply • • • 3 - 77 APPLY_SERVERS EVENT_LOG_DEST LOG_AUTO_DEL_RETENTION_TARGET LOG_AUTO_DELETE MAX_EVENTS_RECORDED MAX_SERVERS • • • MAX_SGA PREPARE_SERVERS RECORD_APPLIED_DDL RECORD_SKIP_ERRORS RECORD_UNSUPPORTED_OPERATIONS Copyright © 2007, Oracle. All rights reserved.
New Columns in DBA_LOGSTDBY_PARAMETERS Name Null? Type Description UNIT Yes VARCHAR 2(64) Unit (if any) SETTING VARCHAR 2(64) SYSTEM: The parameter value is not explicitly set by the user. The user can change it with the appropriate call to APPLY_SET. USER: The parameter value has been explicitly set by the user. DYNAMIC VARCHAR 2(64) YES: The parameter can be set dynamically without having to stop SQL Apply. NO: Setting of the parameter requires that SQL Apply be stopped. 3 - 78 Copyright © 2007, Oracle. All rights reserved.
Recording SQL Apply Event Information • EVENT_LOG_DEST: Determines where SQL Apply records the occurrence of an “interesting event” • Values: – DEST_ALL: All events are recorded in SYSTEM. LOGSTDBY$EVENTS and in the alert log. – DEST_EVENTS_TABLE: Default. All events that contain information about user data are recorded only in the SYSTEM. LOGSTDBY$EVENTS table. 3 - 79 Copyright © 2007, Oracle. All rights reserved.
Logical Standby Database Flash Recovery Area Specify the flash recovery area by using the LOG_ARCHIVE_DEST_n parameter: LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' 3 - 80 Copyright © 2007, Oracle. All rights reserved.
Initiating Fast-Start Failover from an Application Observer Application Primary database DBMS_DG. INITIATE_FS_FAILOVER 3 - 81 Copyright © 2007, Oracle. All rights reserved. Fast-start failover standby database
Setting Up a Test Environment by Using Snapshot Standby Databases Physical standby database Redo stream Open database Back out changes Redo stream Perform testing Snapshot standby database SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 3 - 82 Copyright © 2007, Oracle. All rights reserved.
Summary In this lesson, you should have learned how to: • Configure archive log deletion policies • Duplicate active databases using Oracle network (without backups) • Back up large files in multiple sections • Create archival backups for long-term storage • Query a physical standby database while redo is applied • Control the location of SQL Apply event information • Set the retention target for remote archived log files • Use the logical standby database flash recovery area • Create a snapshot standby database 3 - 83 Copyright © 2007, Oracle. All rights reserved.
4 Security: New Features Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
Objectives After completing this lesson, you should be able to: • Configure the password file to use case-sensitive passwords • Use Transparent Data Encryption support on a logical standby database • Use Transparent Data Encryption support for Streams • Create a tablespace with encryption for added security • Use Hardware Security Module (HSM) for storing external encrypted data • Use large object (LOB) encryption for Secure. File LOBs • Use Enterprise Manager to manage your database security options 4 - 85 Copyright © 2007, Oracle. All rights reserved.
Security Enhancements • Extended password support: – Is case sensitive, supports multibyte characters, and is hashed for comparison purposes • Automatic secure configuration from database installation: – Default password profile – Default auditing – Built-in password complexity checking • Default audit options to cover important security privileges 4 - 86 Copyright © 2007, Oracle. All rights reserved.
Secure Default Configuration • By default: – The default password profile is enabled. – Account is locked after 10 failed login attempts. • On upgrade: – Passwords are not case sensitive until changed. – Passwords become case sensitive by use of the ALTER USER command. • On creation: – Passwords are case sensitive. 4 - 87 Copyright © 2007, Oracle. All rights reserved.
Enabling the Built-in Password Complexity Checker Executing the utlpwdmg. sql script creates the password verification function: SQL> CONNECT / as SYSDBA SQL> @? /rdbms/admin/utlpwdmg. sql This alters the default profile: ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11 g; 4 - 88 Copyright © 2007, Oracle. All rights reserved.
Managing Default Audits In Oracle Database 11 g, default audit options cover important extended security privileges. Best practices: • Retain audit records by using: – Data Pump export command – SELECT into another table • Remove archived audit records after review and archive. 4 - 89 Copyright © 2007, Oracle. All rights reserved.
Privileges Audited By Default • CREATE EXTERNAL JOB • CREATE ANY JOB • GRANT ANY OBJECT PRIVILEGE • EXEMPT ACCESS POLICY • CREATE ANY LIBRARY • GRANT ANY PRIVILEGE • DROP PROFILE • ALTER PROFILE • DROP ANY PROCEDURE • ALTER ANY PROCEDURE • CREATE ANY PROCEDURE • ALTER DATABASE 4 - 90 • GRANT ANY ROLE • CREATE PUBLIC DATABASE LINK • DROP ANY TABLE • ALTER ANY TABLE • CREATE ANY TABLE • DROP USER • ALTER USER • CREATE SESSION • AUDIT SYSTEM • ALTER SYSTEM • SYSTEM AUDIT • ROLE Copyright © 2007, Oracle. All rights reserved.
Adjusting Security Settings 4 - 91 Copyright © 2007, Oracle. All rights reserved.
Setting Security Parameters SEC_CASE_SENSITIVE_LOGON Specifies non-case-sensitive passwords SEC_PROTOCOL_ERROR_FURTHER_ACTION SEC_PROTOCOL_ERROR_TRACE_ACTION Protects against denial-ofservice attacks SEC_MAX_FAILED_LOGIN_ATTEMPTS Protects against brute force attacks 4 - 92 Copyright © 2007, Oracle. All rights reserved.
Setting Database Administrator Authentication Sys/Ora$#CLe as SYSDBA Password file 4 - 93 Copyright © 2007, Oracle. All rights reserved.
Setting Up Directory Authentication for Administrative Users 1. Create the user in the directory. 2. Grant the SYSDBA or SYSOPER role to the user. 3. Set the LDAP_DIRECTORY_SYSAUTH parameter in the database. 4. Check whether the LDAP_DIRECTORY_ACCESS parameter is set to PASSWORD or SSL. 5. Test the connection: $sqlplus fred/t%3 e. EGQ@orcl AS SYSDBA 4 - 94 Copyright © 2007, Oracle. All rights reserved.
Transparent Data Encryption Support Several new features enhance the capabilities of Transparent Data Encryption (TDE) and build on the same infrastructure: • Support for Log. Miner – Support for logical standby • • • 4 - 95 Support for Streams Support for Asynchronous Change Data Capture Tablespace encryption Hardware-based master key protection Encryption for LOB columns Encryption for Data Pump data Copyright © 2007, Oracle. All rights reserved.
TDE and Logical Standby Logical standby database with TDE: • A wallet on the standby is a copy of the wallet on the primary. • The master key may be changed only on the primary. • Wallet open and close commands are not replicated. • The table key may be changed on the standby. • The table encryption algorithm may be changed on the standby. 4 - 96 Copyright © 2007, Oracle. All rights reserved.
TDE and Streams Oracle Streams now provides the ability to transparently: • Decrypt values protected by TDE for filtering and processing • Re-encrypt values so that they are never in clear text while on disk Capture 4 - 97 Staging Apply Copyright © 2007, Oracle. All rights reserved.
Using Tablespace Encryption To create an encrypted tablespace: 1. Create or open the encryption wallet: SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome 1"; 2. Create a tablespace with the encryption keywords: SQL> 2> 3> 4> CREATE TABLESPACE encrypt_ts DATAFILE '$ORACLE_HOME/dbs/encrypt. dat' SIZE 100 M ENCRYPTION USING '3 DES 168' DEFAULT STORAGE (ENCRYPT); This supports 3 DES 168, AES 128, AES 192, and AES 256. 4 - 98 Copyright © 2007, Oracle. All rights reserved.
Hardware Security Module Encrypt and decrypt operations are performed on the hardware security module. Hardware security module Encrypted data Client 4 - 99 Database server Copyright © 2007, Oracle. All rights reserved.
TDE and Kerberos Enhancements • Uses stronger encryption algorithms (no action required) • Provides interoperability between MS KDC and MIT KDC (no action required) • Allows longer principal name: CREATE USER KRBUSER IDENTIFIED EXTERNALLY AS 'Kerberos. User@SOMEORGANIZATION. COM'; 4 - 100 Copyright © 2007, Oracle. All rights reserved.
Encryption for LOB Columns CREATE TABLE test 1 (doc CLOB ENCRYPT USING 'AES 128') LOB(doc) STORE AS SECUREFILE (CACHE NOLOGGING ); • LOB encryption is allowed only for SECUREFILE LOBs. • All LOBs in the LOB column are encrypted. • LOBs can be encrypted on a per-column or per-partition basis: – Allows for the coexistence of SECUREFILE and BASICFILE LOBs 4 - 101 Copyright © 2007, Oracle. All rights reserved.
Enterprise Manager Security Management Manage security through EM. • Policy Manager replaced for: – Virtual Private Database – Application Context – Oracle Label Security • Added: – Enterprise User Security pages – TDE pages 4 - 102 Copyright © 2007, Oracle. All rights reserved.
Summary In this lesson, you should have learned how to: • Configure the password file to use case-sensitive passwords • Use TDE support on a logical standby database • Use TDE support for Streams • Create a tablespace with encryption for added security • Store external encrypted data by using the Hardware Security Module • Use LOB encryption for Secure. File LOBs on a per-column or per-partition basis • Use EM to manage your database security options 4 - 103 Copyright © 2007, Oracle. All rights reserved.
5 Intelligent Infrastructure Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
Objectives After completing this lesson, you should be able to: • Set up and modify Automatic SQL Tuning • Create Automatic Workload Repository (AWR) baselines for future time periods • Use additional supplied maintenance windows for specific maintenance tasks • Simplify memory configuration by setting upper limits to memory use • Use SPFILE enhancements to improve file accessibility • Perform clusterwide analysis of performance • Utilize Enterprise Manager interface for Resource Manager 5 - 105 Copyright © 2007, Oracle. All rights reserved.
Automatic SQL Tuning in Oracle Database 11 g Implement SQL profiles. Test SQL profiles. Workload Generate recommendations. Choose candidate SQL. One week AWR 5 - 106 SQL-tuning candidates DBA It’s automatic! View reports/ Control process Copyright © 2007, Oracle. All rights reserved.
Automatic SQL Tuning: Fine-Tune • Use DBMS_SQLTUNE: – SET_TUNING_TASK_PARAMETER – EXECUTE_TUNING_TASK – REPORT_AUTO_TUNING_TASK • Use DBMS_AUTO_TASK_ADMIN: – ENABLE – DISABLE 5 - 107 Copyright © 2007, Oracle. All rights reserved.
Automatic SQL Tuning: Dictionary Views DBA_ADVISOR_EXECUTIONS Gets data about each execution of the task DBA_ADVISOR_SQLSTATS Shows the test-execute statistics generated from the testing of the SQL Profiles DBA_ADVISOR_SQLPLANS Shows the plans encountered during test-execute 5 - 108 Copyright © 2007, Oracle. All rights reserved.
Automatic SQL Tuning Considerations • SQL not considered for Automatic SQL Tuning: – – – Ad hoc or rarely repeated SQL Parallel queries Still long-running queries after profiling Recursive SQL statements DMLs and DDLs • These categories can still be manually tuned using SQL Advisor. 5 - 109 Copyright © 2007, Oracle. All rights reserved.
Automatic Workload Repository Baselines Oracle Database 11 g further enhances the AWR baselines by: • Offering out-of-the-box moving window baseline from which you can specify adaptive thresholds • Scheduling the creation of a baseline using baseline templates • Renaming baselines • Setting expiration dates for baselines 5 - 110 Copyright © 2007, Oracle. All rights reserved.
Moving Window Baseline There is one moving window baseline: • SYSTEM_MOVING_WINDOW is a moving window baseline that corresponds to the last eight days of AWR data. • It is created out-of-the-box in Oracle Database 11 g. • By default, adaptive thresholds functionality computes statistics on this baseline. 5 - 111 Copyright © 2007, Oracle. All rights reserved.
Baseline Templates • Enable you to schedule the creation of baselines for future time periods of interest: – Single time period in the future – Repeating schedule • For example: – A known holiday weekend – Every Monday morning from 10 a. m. to 2 p. m. • When the end time for a baseline template changes from future to past, MMON detects the change and creates the baseline. 5 - 112 Copyright © 2007, Oracle. All rights reserved.
Generating Baseline for a Single Time Period Interesting time period …. . T 4 T 5 T 6 …. . Tx Ty Tz BEGIN DBMS_WORKLOAD_REPOSITORY. CREATE_BASELINE_TEMPLATE ( start_time => to_date('21 -JUN-2008', 'DD-MON-YYYY'), end_time => to_date('21 -SEP-2008', 'DD-MON-YYYY'), baseline_name => 'FALL 08', template_name => 'FALL 08', expiration => NULL ) ; END; 5 - 113 Copyright © 2007, Oracle. All rights reserved.
Using EM to Quickly Configure Adaptive Thresholds 5 - 114 Copyright © 2007, Oracle. All rights reserved.
Changes to Procedures and Views New DBMS_WORKLOAD_REPOSITORY Package Procedures CREATE_BASELINE_TEMPLATE RENAME_BASELINE MODIFY_BASELINE_WINDOW_SIZE Functions SELECT_BASELINE_METRIC New Views Modified DBA_HIST_BASELINE_DETAILS DBA_HIST_BASELINE_TEMPLATE 5 - 115 Copyright © 2007, Oracle. All rights reserved.
Automated Maintenance Tasks 10: 00 PM– 2: 00 AM Mon to Fri 5 - 116 6: 00 AM– 2: 00 AM Sat to Sun Copyright © 2007, Oracle. All rights reserved.
Default Maintenance Resource Manager Plan SQL> SELECT name FROM V$RSRC_PLAN 2 WHERE is_top_plan = 'TRUE'; NAME ----------------DEFAULT_MAINTENANCE_PLAN 5 - 117 Copyright © 2007, Oracle. All rights reserved.
Automated Maintenance Task Priorities … Run Job 1 with urgent priority Run Job 2 with urgent priority Run Job 3 with high priority Run Job 3 with medium priority Run Job 4 with medium priority … MMON urgent Stats Maintenance window high medium ABP Space Job 1 SQL DBA_AUTOTASK_TASK 5 - 118 Copyright © 2007, Oracle. All rights reserved. … Jobn
Automatic Memory Management: Overview PGA memory 10 g&11 g Untunable PGA Free Untunable PGA Memory target t PGA Untunable PGA t arge SQL areas SGA target SGA memory Free PGA target SQL areas Buffer cache 11 g et Buffer cache SGA targ Buffer cache Large pool Shared pool Java pool Streams pool Other SGA OLTP 5 - 119 BATCH Copyright © 2007, Oracle. All rights reserved. BATCH
Automatic Memory Management: Overview 11 g Memory max target 350 M 11 g Memory max target Memory target 250 M ALTER SYSTEM SET MEMORY_TARGET=300 M; 5 - 120 350 M Copyright © 2007, Oracle. All rights reserved. 300 M
Oracle Database 11 g Memory-Sizing Parameters MEMORY_MAX_TARGET SGA_MAX_SIZE MEMORY_TARGET SGA_TARGET SHARED_POOL_SIZE DB_CACHE_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE STREAMS_POOL_SIZE PGA_AGGREGATE_TARGET Others DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE DB_n. K_CACHE_SIZE 5 - 121 LOG_BUFFER RESULT_CACHE_SIZE Copyright © 2007, Oracle. All rights reserved.
ADDM Enhancements in Oracle Database 11 g • ADDM for Oracle Real Application Clusters (RAC) • Directives (finding suppression) • DBMS_ADDM package 5 - 122 Copyright © 2007, Oracle. All rights reserved.
Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10 g Application and SQL management Storage management System Resource management Backup and recovery management Database management Space management Intelligent infrastructure 5 - 123 Copyright © 2007, Oracle. All rights reserved.
Automatic Database Diagnostic Monitor for Oracle RAC Database ADDM Self-diagnostic engine Instance ADDM … AWR Inst 1 5 - 124 Copyright © 2007, Oracle. All rights reserved. Instn
ADDM for Oracle RAC • Identifies the most critical performance problems for the entire Oracle RAC cluster database • Runs automatically when taking AWR snapshots (the default) • Performs databasewide analysis of: – – – Global resources—for example, I/O and global locks High-load SQL, hot blocks Global cache interconnect traffic Network latency issues Skew in instance response times • Is used by DBAs to analyze cluster performance 5 - 125 Copyright © 2007, Oracle. All rights reserved.
ADDM for Oracle RAC Specified in the DBMS_ADVISOR. SET_DEFAULT_TASK_PARAMETER procedure: Value of INSTANCES ADDM Analysis Mode “ 0” or “UNUSED”(default) “UNUSED” (default) Database ADDM (all instances) “ 0” or “UNUSED” (default) Comma-separated list of instance numbers (1, 2, 5. . ) Partial analysis ADDM. Only instances specified in the INSTANCES parameter are analyzed. A positive integer (For example: “ 1”) Any value Instance ADDM. The instance specified in the INSTANCE parameter is analyzed. 5 - 126 Copyright © 2007, Oracle. All rights reserved.
EM Support for ADDM for Oracle RAC Finding History page: 5 - 127 Copyright © 2007, Oracle. All rights reserved.
DBMS_ADDM Package The DBMS_ADDM package eases ADDM management. It consists of the following procedures and functions: ANALYZE_DB Create an ADDM task for analyzing the database globally. ANALYZE_INST Create an ADDM task for analyzing a local instance. ANALYZE_PARTIAL Create an ADDM task for analyzing a subset of instances. DELETE Delete a created ADDM task (of any kind). GET_REPORT Get the default text report of an executed ADDM task. 5 - 128 Copyright © 2007, Oracle. All rights reserved.
Advisor-Named Findings and Directives • The DBA_ADVISOR_FINDING_NAMES view lists all possible findings. SQL> select finding_name from dba_advisor_finding_names; FINDING_NAME --------------------Top Segments by I/O Top SQL by "Cluster" Wait. . . Undersized Redo Log Buffer Undersized SGA Undersized Shared Pool Undersized Streams Pool • Advisor results are now classified and named, and exist in the DBA{USER}_ADVISOR_FINDINGS view. 5 - 129 Copyright © 2007, Oracle. All rights reserved.
Using the DBMS_ADDM Package • Create an ADDM directive, which filters “Undersized SGA” findings: SQL> 2 3 4 5 6 7 8 9 10 SQL> var tname varchar 2(60); BEGIN dbms_addm. insert_finding_directive (NULL, 'My undersized SGA directive', 'Undersized SGA', 2, 10); : tname : = 'my instance ADDM task'; dbms_addm. analyze_inst(: tname, 1, 2); END; / SELECT dbms_addm. get_report(: tname) from dual; • Possible findings found in DBA_ADVISOR_FINDING_NAMES 5 - 130 Copyright © 2007, Oracle. All rights reserved.
New ADDM Views • DBA{USER}_ADDM_TASKS: Displays every executed ADDM task. Is an extension of the corresponding Advisor views. • DBA{USER}_ADDM_INSTANCES: Displays instance-level information for ADDM tasks that are completed • DBA{USER}_ADDM_FINDINGS: Displays extensions of the corresponding Advisor views • DBA{USER}_ADDM_FDG_BREAKDOWN: Displays the contribution for each finding from the different instances for database and partial ADDM • DBA_ADDM_SYSTEM_DIRECTIVES: Displays the directives in the system that affect all tasks • DBA_ADDM_TASK_DIRECTIVES: Displays the directives in the system affecting a specific task. Use TASK_ID or TASK_NAME to limit to a specified task. 5 - 131 Copyright © 2007, Oracle. All rights reserved.
Resource Manager: New EM Interface 5 - 132 Copyright © 2007, Oracle. All rights reserved.
Resource Manager: New EM Interface 5 - 133 Copyright © 2007, Oracle. All rights reserved.
Easier Recovery from Loss of SPFILE The FROM MEMORY clause allows the creation of current systemwide parameter settings. CREATE PFILE [= 'pfile_name' ] FROM { { SPFILE [= 'spfile_name'] } | MEMORY } ; CREATE SPFILE [= 'spfile_name' ] FROM { { PFILE [= 'pfile_name' ] } | MEMORY } ; 5 - 134 Copyright © 2007, Oracle. All rights reserved.
Summary In • • • this lesson, you should have learned how to: Set up and modify Automatic SQL Tuning Create AWR baselines for future time periods Use additional supplied maintenance windows for specific maintenance tasks • Simplify memory configuration by setting MEMORY_TARGET initialization parameters • Improve file accessibility of the SPFILE file • Perform clusterwide analysis of performance using Oracle RAC-aware ADDM • Utilize Enterprise Manager interface for Resource Manager 5 - 135 Copyright © 2007, Oracle. All rights reserved.
6 Datawarehousing Enhancements Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
Objectives After completing this lesson, you should be able to: • Use SQL Access Advisor’s recommendations for partitioning options • Utilize partitioning enhancements to gain significantly faster data access: – – – 6 - 137 Interval partitioning System partitioning Composite partitioning enhancements Virtual column-based partitioning Reference partitioning Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor in Oracle Database 11 g Workload The DBMS_ADVISOR. QUICK_TUNE procedure is a shortcut for: • CREATE_TASK • UPDATE_TASK_ATTRIBUTES • DELETE_TASK Indexes 6 - 138 SQL Access Advisor Materialized views Copyright © 2007, Oracle. All rights reserved. Materialized Partitioned views log objects
Oracle Partitioning Core functionality Performance Manageability Oracle 8 Range partitioning Global range indexes Static partition pruning Basic maintenance operations: add, drop, exchange Oracle 8 i Hash and composite range-hash partitioning Partition-wise joins Dynamic pruning Merge operation Oracle 9 i List partitioning Oracle 9 i R 2 Composite range-list partitioning Oracle 10 g Global hash indexes Oracle 10 g R 2 1 M partitions per table Oracle Database 11 g More composite choices REF partitioning Virtual column partitioning 6 - 139 Global index maintenance Fast partition split Local index maintenance Multidimensional pruning Copyright © 2007, Oracle. All rights reserved. Fast drop table Interval partitioning Partition Advisor
Partitioning Enhancements • • • 6 - 140 Interval partitioning System partitioning Composite partitioning enhancements Virtual column-based partitioning Reference partitioning Copyright © 2007, Oracle. All rights reserved.
Interval Partitioning • Interval partitioning is an extension of range partitioning. • Partitions of a specified interval are created when inserted data exceeds all of the range partitions. • At least one range partition must be created. • Interval partitioning automates the creation of range partitions. 6 - 141 Copyright © 2007, Oracle. All rights reserved.
Interval Partitioning: Example CREATE TABLE sh. sales_interval PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'month')) (PARTITION P 0 values less than (TO_DATE('1 -1 -2002', 'dd-mm-yyyy')), PARTITION P 1 values less than (TO_DATE('1 -1 -2003', 'dd-mm-yyyy')), PARTITION P 2 values less than (TO_DATE('1 -7 -2003', 'dd-mm-yyyy')), PARTITION P 3 values less than (TO_DATE('1 -1 -2004', 'dd-mm-yyyy'))) AS SELECT * FROM SH. SALES WHERE TIME_ID < TO_DATE('1 -1 -2004', 'dd-mm-yyyy'); P 0 P 1 P 2 P 3 SYS_P 0 SYS_Pn … Interval components Range components Transition point 6 - 142 Copyright © 2007, Oracle. All rights reserved.
Moving the Transition Point • Creating an interval-partitioned table: CREATE TABLE sales_interval PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'month')) (PARTITION P 0 VALUES LESS THAN (TO_DATE('1 -12004', 'dd-mm-yyyy'))) AS SELECT * FROM SH. SALES WHERE 1 = 0; • Using the MERGE clause to move a transition point: ALTER TABLE sh. sales_interval MERGE PARTITIONS P 3, P 4 INTO PARTITION P 4; 6 - 143 Copyright © 2007, Oracle. All rights reserved.
System Partitioning System partitioning: • Enables application-controlled partitioning for selected tables • Provides the benefits of partitioning, but the partitioning and data placement are controlled by the application • Does not employ partitioning keys (as used in other partitioning methods) • Does not support partition pruning in the traditional sense 6 - 144 Copyright © 2007, Oracle. All rights reserved.
System Partitioning: Guidelines The following operations are supported for systempartitioned tables: • Partition maintenance operations and other data definition language (DDL) operations • Creation of local indexes • Creation of local bitmapped indexes • Creation of global indexes • All data manipulation language (DML) operations • INSERT AS SELECT with partition-extended syntax: INSERT INTO <table_name> PARTITION (<partition-name|number|bind var) AS <subqery> 6 - 145 Copyright © 2007, Oracle. All rights reserved.
System Partitioning: Example CREATE TABLE PARTITION BY ( PARTITION ); systab (c 1 integer, c 2 integer) SYSTEM p 1 p 2 p 3 p 4 TABLESPACE tbs_1, tbs_2, tbs_3, tbs_4 Using the system-partitioned table: INSERT INTO systab PARTITION (p 1) VALUES (4, 5); INSERT INTO systab PARTITION (1) VALUES (4, 5); ALTER TABLE systab MERGE PARTITIONS p 1, p 2 INTO PARTITION p 1; 6 - 146 Copyright © 2007, Oracle. All rights reserved.
Composite Partitioning Enhancements • Range top level: – Range-Range, List, Interval • List top level: SP 1 • Interval top level: – Interval-Range – Interval-List – Interval-Hash SP 1 … SP 1 SP 2 – List-List – List-Hash – List-Range SP 2 … SP 2 SP 3 … SP 3 SP 4 … SP 4 List, Range, Hash 6 - 147 Copyright © 2007, Oracle. All rights reserved.
Composite Range-Range Partitioning: Example CREATE TABLE sales (prod_id NUMBER(6) NOT NULL, cust_id NUMBER NOT NULL, time_id DATE NOT NULL, channel_id char(1) NOT NULL, promo_id NUMBER (6) NOT NULL, quantity_sold NUMBER(3) NOT NULL, amount_sold NUMBER(10, 2) NOT NULL) PARTITION BY RANGE (time_id) SUBPARTITION BY RANGE (cust_id) SUBPARTITION TEMPLATE ( SUBPARTITION sp 1 VALUES LESS THAN (50000), SUBPARTITION sp 2 VALUES LESS THAN (100000), SUBPARTITION sp 3 VALUES LESS THAN (150000), SUBPARTITION sp 4 VALUES LESS THAN (MAXVALUE) ) ( PARTITION VALUES LESS THAN (TO_DATE('1 -APR-1999', 'DD-MON-YYYY')), PARTITION VALUES LESS THAN (TO_DATE('1 -JUL-1999', 'DD-MON-YYYY')), PARTITION VALUES LESS THAN (TO_DATE('1 -OCT-1999', 'DD-MON-YYYY')), PARTITION VALUES LESS THAN (TO_DATE('1 -JAN-2000', 'DD-MON-YYYY')) ); 6 - 148 Copyright © 2007, Oracle. All rights reserved.
Virtual Column-Based Partitioning • Virtual column values are derived by the evaluation of a function or expression. • Virtual columns can be defined within a CREATE or ALTER table operation. CREATE TABLE employees (employee_id number(6) not null, … total_compensation as (salary *( 1+commission_pct)) • Virtual column values are not physically stored in the table but are evaluated on demand. • Virtual columns can be indexed and used in queries and DML and DDL statements like other column types. • Tables and indexes can be partitioned on a virtual column; even statistics can be gathered on them. 6 - 149 Copyright © 2007, Oracle. All rights reserved.
Virtual Column-Based Partitioning: Example CREATE TABLE employees (employee_id number(6) not null, first_name varchar 2(30), last_name varchar 2(40) not null, email varchar 2(25), phone_number varchar 2(20), hire_date not null, job_id varchar 2(10) not null, salary number(8, 2), commission_pct number(2, 2), manager_id number(6), department_id number(4), total_compensation as (salary *( 1+commission_pct)) ) PARTITION BY RANGE (total_compensation) ( PARTITION p 1 VALUES LESS THAN (50000), PARTITION p 2 VALUES LESS THAN (100000), PARTITION p 3 VALUES LESS THAN (150000), PARTITION p 4 VALUES LESS THAN (MAXVALUE) ); 6 - 150 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint. • The partitioning key is resolved through an existing parent-child relationship. • The partitioning key is enforced by active primary key and foreign key constraints. • Tables with a parent-child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. 6 - 151 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning: Example CREATE TABLE orders ( order_id NUMBER(12), order_date TIMESTAMP, order_mode VARCHAR 2(8), customer_id NUMBER(6), order_status NUMBER(2), order_total NUMBER(8, 2), sales_rep_id NUMBER(6), promotion_id NUMBER(6), CONSTRAINT orders_pk PRIMARY KEY(order_id) ) PARTITION BY RANGE(order_date) (PARTITION Q 1_2005 VALUES LESS THAN (TO_DATE('01 -APR-2005', 'DD-MON-YYYY')), PARTITION Q 2_2005 VALUES LESS THAN (TO_DATE('01 -JUL-2005', 'DD-MON-YYYY')), PARTITION Q 3_2005 VALUES LESS THAN (TO_DATE('01 -OCT-2005', 'DD-MON-YYYY')), PARTITION Q 4_2005 VALUES LESS THAN (TO_DATE('01 -JAN-2006', 'DD-MON-YYYY')) ); 6 - 152 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning: Example CREATE TABLE order_items ( order_id NUMBER(12) NOT NULL, line_item_id NUMBER(3) NOT NULL, product_id NUMBER(6) NOT NULL, unit_price NUMBER(8, 2), quantity NUMBER(8), CONSTRAINT order_items_fk FOREIGN KEY(order_id) REFERENCES orders(order_id) ) PARTITION BY REFERENCE(order_items_fk); 6 - 153 Copyright © 2007, Oracle. All rights reserved.
Bitmap Join Index for IOT A 6 - 154 IOT B Copyright © 2007, Oracle. All rights reserved.
Table Compression Table compression offers positive performance impact on queries accessing large amounts of data: • The data is compressed by eliminating duplicate values in a database block. • All database features and functions that work on regular blocks also work on compressed blocks. ALTER TABLE <table_name> COMPRESS | NOCOMPRESS (FOR {ALL | DIRECT_LOAD } OPERATIONS) 6 - 155 Copyright © 2007, Oracle. All rights reserved.
Summary In this lesson, you should have learned how to: • Implement partitioning on tables, indexes, and materialized views from SQL Access Advisor’s recommendations • Use partitioning enhancements to gain significantly faster data access: – – – 6 - 156 Interval partitioning System partitioning Composite partitioning enhancements Virtual column-based partitioning Reference partitioning Copyright © 2007, Oracle. All rights reserved.
7 Additional Performance Enhancements Oracle Database 11 g Copyright © 2007, Oracle. All rights reserved.
Objectives After completing this lesson, you should be able to: • Gain flexibility in automatic statistics generation at the object level: – Set up statistics preferences. – Set up incremental, multicolumn, and expression statistics. – Defer statistics publishing. • Use memory efficiently with Query Result Cache support • Discuss the increased cursor shareability in Oracle Database 11 g 7 - 158 Copyright © 2007, Oracle. All rights reserved.
Statistic Preferences: Overview Optimizer statistics gathering task Statement level Table level DBA_TAB_STAT_PREFS Schema level Database level Global level CASCADE ESTIMATE_PERCENT NO_INVALIDATE PUBLISH STALE_PERCENT DEGREE METHOD_OPT GRANULARITY INCREMENTAL set_global_prefs set_database_prefs set_schema_prefs set_table_prefs gather_*_stats DB MS _S se TA t| TS ge ex t| po rt | dele im te po rt DBA exec dbms_stats. set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '13'); 7 - 159 Copyright © 2007, Oracle. All rights reserved.
Partitioned Tables and Incremental Statistics: Overview … Q 1 1970 7 - 160 Q 2 1970 … Q 1 2007 Q 1 1970 Q 2 1970 Global statistics … Q 1 2007 Q 1 1970 Copyright © 2007, Oracle. All rights reserved. Q 2 1970 Q 1 2007
Partitioned Tables and Incremental Statistics in Oracle Database 11 g SYSAUX synopsis … Q 1 1970 Q 2 1970 … Q 1 2007 Q 1 1970 Q 2 1970 Q 1 2007 GRANULARITY=GLOBAL% & INCREMENTAL=TRUE 7 - 161 Copyright © 2007, Oracle. All rights reserved. Global statistics
Hash-Based Sampling for Column Statistics • Computing statistics for columns is the most expensive step in statistics gathering. • The row sampling technique gives inaccurate results with skewed data distribution. • A new approximate counting technique based on hash sampling is used when ESTIMATE_PERCENT is set to AUTO_SAMPLE_SIZE. – The old row sampling technique is used otherwise. 7 - 162 Copyright © 2007, Oracle. All rights reserved.
Multicolumn Statistics: Overview CARS MAKE MODEL 1 S(MAKE Λ MODEL)=S(MAKE)x. S(MODEL) select dbms_stats. create_extended_stats('jfv', 'cars', '(make, model)') from dual; 2 exec dbms_stats. gather_table_stats('jfv', 'cars', method_opt=>'for all columns size 1 for columns (make, model) size 3'); 3 DBA_STAT_EXTENSIONS CARS MAKE MODEL 4 S(MAKE 7 - 163 Λ MODEL)=S(MAKE, MODEL) Copyright © 2007, Oracle. All rights reserved.
Expression Statistics: Overview CREATE INDEX upperidx ON CARS(upper(MODEL)) CARS MODEL bl i ss CARS l til e po S MODEL R ec om S(upper( MODEL))=0. 01 m en DBA_STAT_EXTENSIONS CARS de d MODEL select dbms_stats. create_extended_stats('jfv', 'cars', '(upper(model))') from dual; exec dbms_stats. gather_table_stats('jfv', 'cars', method_opt=>'for all columns size 1 for columns (upper(model)) size 3'); 7 - 164 Copyright © 2007, Oracle. All rights reserved.
Deferred Statistics Publishing: Overview PROD OPTIMIZER_USE_PENDING_STATISTICS=TRUE OPTIMIZER_USE_PENDING_STATISTICS =FALSE Dictionary statistics Pending statistics PUBLISH=FALSE + GATHER_*_STATS DBA_TAB_PENDING_STATS IMPORT_TABLE_STATS expdp/impdp PUBLISH_PENDING_STATS EXPORT_PENDING_STATS TEST 7 - 165 Copyright © 2007, Oracle. All rights reserved.
Deferred Statistics Publishing: Example exec dbms_stats. set_table_prefs('SH', 'CUSTOMERS', 'PUBLISH', 'false'); 1 exec dbms_stats. gather_table_stats('SH', 'CUSTOMERS'); 2 alter session set optimizer_use_pending_statistics = true; 3 Execute your workload from the same session. 4 exec dbms_stats. publish_pending_stats('SH', 'CUSTOMERS'); 5 7 - 167 Copyright © 2007, Oracle. All rights reserved.
Query Result Cache • You cache the result of a query or query block for future reuse. • Cache is used across statements and sessions unless it is stale. • Benefits: – Scalability – Reduction of memory usage Query Result Cache SELECT … session 1 7 - 168 session 2 Copyright © 2007, Oracle. All rights reserved.
Setting Up the Query Result Cache Set at database level using the RESULT_CACHE_MODE initialization parameter. Values are as follows: • AUTO: The optimizer determines which results are to be stored in the cache based on repetitive executions. • MANUAL: Use the result_cache hint to specify results to be stored in the cache. • FORCE: All results are stored in the cache. 7 - 169 Copyright © 2007, Oracle. All rights reserved.
Using the RESULT_CACHE Hint SELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM employees GROUP BY department_id; -------------------------------| Id | Operation | Name |Rows -------------------------------| | | 0 | SELECT STATEMENT 1 | RESULT CACHE 2 | HASH GROUP BY | 3 | | 8 fpza 04 gtwsfr 6 n 595 au 15 yj 4 y | | | TABLE ACCESS FULL| EMPLOYEES 11 11 | 107 -------------------------------SELECT /*+ NO_RESULT_CACHE */ department_id, AVG(salary) FROM employees GROUP BY department_id; 7 - 170 Copyright © 2007, Oracle. All rights reserved.
Managing the Query Result Cache The following initialization parameters can be used to manage the Query Result Cache: • RESULT_CACHE_MAX_SIZE parameter: – It sets the memory allocated to the result cache. – The result cache is disabled if you set the value to 0. • RESULT_CACHE_MAX_RESULT: – It sets the maximum cache memory for a single result. – It defaults to 5%. • RESULT_CACHE_REMOTE_EXPIRATION: – It sets the expiry time for the Query Result Cache. – It defaults to 0. 7 - 171 Copyright © 2007, Oracle. All rights reserved.
Using the DBMS_RESULT_CACHE Package Use the DBMS_RESULT_CACHE package to: • Manage memory allocation for the query result cache • View the status of the cache: SELECT DBMS_RESULT_CACHE. STATUS FROM DUAL; • Retrieve statistics on the cache memory usage: EXECUTE DBMS_RESULT_CACHE. MEMORY_REPORT; • Remove all existing results and clear cache memory: EXECUTE DBMS_RESULT_CACHE. FLUSH; • Invalidate cached results depending on specified object: EXEC DBMS_RESULT_CACHE. INVALIDATE('JFV', 'MYTAB'); 7 - 172 Copyright © 2007, Oracle. All rights reserved.
Viewing Information About the Query Result Cache The following views provide information about the Query Result Cache: (G)V$RESULT_CACHE_STATISTICS Lists the various cache settings and memory usage statistics (G)V$RESULT_CACHE_MEMORY Lists all the memory blocks and the corresponding statistics (G)V$RESULT_CACHE_OBJECTS Lists all the objects (cached results and dependencies) along with their attributes (G)V$RESULT_CACHE_DEPENDENCY Lists the dependency details between the cached results and dependencies 7 - 173 Copyright © 2007, Oracle. All rights reserved.
Oracle Call Interface Client Query Cache • Extends server-side query caching to client-side memory • Ensures better performance by eliminating round trips to the server • Leverages client-side memory • Improves server scalability by saving server CPU resources • Automatically refreshes the result cache if the result set is changed on the server • Is particularly good for lookup tables 7 - 174 Copyright © 2007, Oracle. All rights reserved.
Setting the OCI Client Query Cache You can use client-side query caching by: • Setting initialization parameters – CLIENT_RESULT_CACHE_SIZE – CLIENT_RESULT_CACHE_LAG • Using the client configuration file – OCI_RESULT_CACHE_MAX_SIZE – OCI_RESULT_CACHE_MAX_RSET_ROWS Client result cache is then used depending on: – Tables result cache mode – RESULT CACHE hints in your SQL statements 7 - 175 Copyright © 2007, Oracle. All rights reserved.
PL/SQL Function Cache … Y: = HR. Calculate_Comp Cached results First query … … … Y: = HR. Calculate_Comp Subsequent queries 7 - 176 Copyright © 2007, Oracle. All rights reserved.
PL/SQL Function Cache: Example • Include the RESULT_CACHE option in the function declaration section of a package or function definition. • Optionally include the RELIES_ON clause to specify any tables or views on which the function results depend. CREATE OR REPLACE FUNCTION product. Name (prod_id NUMBER, lang_id VARCHAR 2) RETURN NVARCHAR 2 RESULT_CACHE RELIES_ON (product_descriptions) IS result VARCHAR 2(50); BEGIN SELECT translated_name INTO result FROM product_descriptions WHERE product_id = prod_id AND language_id = lang_id; RETURN result; END; 7 - 177 Copyright © 2007, Oracle. All rights reserved.
Automatic “Native” Compilation • More than 100% faster for pure PL/SQL or Java code • 10% to 30% faster for typical transactions with SQL – PL/SQL parameter: plsql_code_type — — — Just one value: NATIVE or INTERPRETED No need for C compiler No file system DLLs – Java parameter: java_jit_enabled — — 7 - 178 Just one value: TRUE or FALSE JIT “on-the-fly” compilation Transparent to user (asynchronous; in background) Code stored to avoid recompilations Copyright © 2007, Oracle. All rights reserved.
Adaptive Cursor Sharing: Overview • Adaptive Cursor Sharing allows for intelligent cursor sharing only for statements that use bind variables. • Adaptive Cursor Sharing is used to compromise between cursor sharing and optimization. • Adaptive Cursor Sharing benefits: – Automatically detects when different executions would benefit from different execution plans – Limits the number of generated child cursors to a minimum – Automated mechanism that cannot be turned off One plan is not always appropriate for all bind values. 7 - 179 Copyright © 2007, Oracle. All rights reserved.
Adaptive Cursor Sharing: Example SELECT ……FROM. . WHERE Job = : B 1 Ename IDno Job SMITH 6973 CLERK ALLEN 7499 CLERK WARD SCOTT 7521 CLERK 7788 CLERK CLARK 7782 CLERK Ename Empno Job KING 8739 7 - 180 VP Ename 1 IDno Job SMITH ALLEN WARD KING SCOTT CLARK 6973 7499 7521 8739 7788 7782 CLERK VP CLERK 2 Copyright © 2007, Oracle. All rights reserved.
Adaptive Cursor Sharing Views The following views provide information about Adaptive Cursor Sharing usage: V$SQL Two new columns show whether a cursor is bind-sensitive or bind-aware. V$SQL_CS_HISTOGRAM Shows the distribution of the execution count across the execution history histogram V$SQL_CS_SELECTIVITY Shows the selectivity cubes stored for every predicate that contains a bind variable and whose selectivity is used in the cursor sharing checks V$SQL_CS_STATISTICS Shows execution statistics of a cursor using different bind sets 7 - 181 Copyright © 2007, Oracle. All rights reserved.
Summary In this lesson, you should have learned how to: • Gain flexibility in automatic statistic generation at the object level: – Set up statistics preferences. – Set up incremental, multicolumn, and expression statistics. – Defer statistics publishing. • Use memory efficiently with Query Result Cache support • Discuss the benefits of increased cursor shareability using Adaptive Cursor Sharing 7 - 182 Copyright © 2007, Oracle. All rights reserved.
Скачать презентацию I Introduction Oracle Database 11 g Copyright
cdb910261e76084f73bbd1ade99ae4fb.ppt