Oracle Database 11 g New Features for the DBA Robert G. Freeman Collaborate 2008
Robert G. Freeman About Me… l Oracle Press Author Oracle Database 11 g New Features l Oracle Database 10 g RMAN Backup and Recovery l Portable DBA: Oracle l Other OP Titles l Principal Engineer The Church of Jesus Christ of Latter-Day Saints l. Husband, father, pilot, black belt, general malcontent. l
Oracle Database 11 g Topics we will cover Installing and Upgrading to 11 g. l Administration and Change Assurance l Backup and Recovery Features l Partitioning Features l. Strategic Strategies l
Oracle Database 11 g l. INSTALLING AND UPGRADING TO 11 g
Oracle Database 11 g Installing 11 g APEX installed automatically now. l The install has changed a bit l ORACLE_BASE must be defined. l ORACLE_BASE stored in Inventory. l
Oracle Database 11 g Installing 11 g l. Pre-requisites check l. Make sure all OS patches (e. g. RPM’s) are installed. l. Sometimes need to skip checks (for example, running RHEL 5 Linux). l. Recommend you install a small database during the install or afterwards to test that all went well.
Oracle Database 11 g Installing 11 g l. I would recommend you not upgrade when first installing the DB software l. Oracle offers the option to upgrade databases during software install l. Test the install base before you start any upgrades l. Database creation l. Networking test
Oracle Database 11 g Upgrade to 11 g – Upgrade Methods l Supported Upgrade Methods l Database Upgrade Assistant l Manual Upgrades l Export/Import l SQL*Plus Copy
Oracle Database 11 g Upgrade to 11 g – Upgrade Paths l. DBUA/Manual Upgrade Supported Upgrade Paths l. Upgrade to supported upgrade version l. Any Oracle version < 9. 2. 0. 4 l. Upgrade directly to 11 g l. Oracle 9. 2. 0. 4 (or higher) l. Oracle 10. 1. 0. 2 (or higher) l. Oracle 10. 2. 0. 1(or higher) (10. 2. 0. 3 for CW) l. Exp/Imp method or SQL*Plus copy method generally supported across versions.
Oracle Database 11 g Upgrade to 11 g - DBUA l. Using the DBUA l. Run from the 11 g Oracle Home l. Personally recommend you make your own backup before you upgrade a database. l. Executes pre-upgrade checks and postupgrade checks.
Oracle Database 11 g Upgrade to 11 g - DBUA l. DBUA prompts you for l. Database to upgrade (from /etc/oratab or an Oracle service) l. Location of DIAGNOSTIC_DEST l. Option to move datafiles (including to/from ASM) l. Backup of your database l. Ability to manage passwords for any new database users created.
Oracle Database 11 g Upgrade to 11 g – Select Database
Oracle Database 11 g Note Logging Upgrade to 11 g - Logging
Oracle Database 11 g Upgrade to 11 g – Log Files Log Name Purpose Upgrade. Results. html This is a summary of what the DBUA intends to upgrade. This HTML file is displayed by the DBUA before the upgrade begins. Trace. log Provides detailed tracing information on the entire upgrade process. Any errors reported by the DBUA will be recorded in this log. Oracle_Server. log *Tail this file during upgrade. This file provides details of the execution of the entire migration project. If an error occurs or the upgrade fails, you can find more details in this file. Post_Upgrade. log Log file for details on post upgrade operations. You can look in this file to determine if the upgrade was successful or not.
Oracle Database 11 g Upgrade to 11 g – DBUA Upgrade Summary
Oracle Database 11 g Upgrade to 11 g – Manual Upgrades l. Manual Upgrades supported l. Can be a bit tedious and requires a number of manual steps. l. Recommend you use a checklist! l. More overall control over the upgrade process. l. Most steps can be re-executed if they fail once you have corrected the cause of the failure.
Oracle Database 11 g Upgrade to 11 g – Using Exp/Imp l. Useful if you are migrating from a database version that does not support the DBUA or manual upgrade method. l. In some ways perhaps the safest way to upgrade. l. Allows you to configure the new 11 g database before you move data over to it. l. In many cases, may be the slowest way to upgrade your database.
Oracle Database 11 g Upgrade to 11 g – Using Exp/Imp/Data. Pump l. Allows you to move to different platforms easily. l. Can also be used to rollback from an unsuccessful upgrade.
Oracle Database 11 g Upgrade to 11 g l. Test test l. You never know what won’t work in your environment. l. You can take advantage of change assurance. l. Change assurance might not catch everything (e. g. 9 i to 10. 2 ODBC issue)
Oracle Database 11 g l. Administration of Oracle Database 11 g
Oracle Database 11 g Administration of Oracle Database 11 g l. ADR l. AWR l. Support Workbench l. Automatic Memory Management l. Database Replay l. SQL performance Analyzer l. Virtual Columns l. Table Compression
Oracle Database 11 g l. Automatic Diagnostic Repository (ADR)
Oracle Database 11 g Automatic Diagnostic Repository (ADR) l. New management structure in 11 g called the Automatic Diagnostic Repository (ADR) l. Centralized and standardized repository for the Oracle 11 g Fault Diagnosability infrastructure. l. Replaces several existing directories l. User_dump_dest l. Background_dump_dest l. Core_dump_dest
Oracle Database 11 g Automatic Diagnostic Repository (ADR) l. Files stored in the ADR: l. Alert log l. Trace files l. Incident packages l. Default location $ORACLE_BASE/diag/{product}/{database}/{instance} l. Example: /u 01/app/oracle/diag/rdbms/orcl
Oracle Database 11 g DIAGNOSTIC_DEST
Oracle Database 11 g ADR l. Alert log now XML based (though a text copy is still available). l. New view V$DIAG_INFO l. Used to determine information about the ADR: SQL> select * from v$diag_info; INST_ID NAME VALUE ------------------1 Diag Enabled TRUE 1 ADR Base C: ORACLEPRODUCT 1 ADR Home C: ORACLEPRODUCTdiagrdbmsrob 11 gr 4
Oracle Database 11 g ADR l. New tool: ADRCI l. ADR Command Line Interpreter l. Used to manage and report from ADR.
Oracle Database 11 g ADR l. ADRCI Example C: oracleproduct11 g. Beta. R 4db_01NETWORKADMIN>adrci>>show alert -tail ADR Home = C: oracleproductdiagrdbmsrob 11 gr 4: ******************************200 7 -06 -02 00: 42: 47. 398000 -06: 00 Logminer Bld: Lockdown Complete. DB_TXN_SCN is Unwind. To. SCN (Lockdown. SCN) is 1832443 2007 -06 -02 00: 42: 48. 929000 -06: 00 db_recovery_file_dest_size of 2048 MB is 83. 44% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2007 -06 -02 00: 43: 45. 586000 -06: 00
Oracle Database 11 g ADR l. ADRCI Homes l. Systems with multiple databases will have individual ADR homes. l. Set the ADR_HOME when using ADRCI to make sure you are pointed to the correct location.
Oracle Database 11 g l. ADRCI Homes l. Example: ADR adrci> show homes ADR Homes: diag/rdbms/probe 2/PROBE 2 diag/rdbms/probe 1/PROBE 1 diag/asm/+ASM 11 diag/tnslsnr/prolin 2/listener adrci> set homepath diag/rdbms/probe 2/PROBE 2 adrci> show homes ADR Homes: diag/rdbms/probe 2/PROBE 2
Oracle Database 11 g l. Automatic Workload Repository (AWR)
Oracle Database 11 g AWR l. AWR Features l. Default data retention l. Changes from 7 days to 8 days. l. Baselines l. Adaptive Metric Thresholds
Oracle Database 11 g AWR l. AWR Baselines l. Moving window baselines A moving baseline typically based on the entirety of the statistical data contained in AWR l. Single baseline Allows you to define a baseline to be captured for a single specified period of time in the future l. Repeating baseline Allows you to define a baseline to be captured for a repeating period of time in the future
Oracle Database 11 g AWR l. Adaptive Metric Thresholds l. Reporting thresholds developed using AWR metrics. l. Allows for floating monitoring thresholds. l. As your system workload changes, the alerting thresholds will evolve to reflect the current state of the database.
Oracle Database 11 g l. Support Workbench
Oracle Database 11 g Support Workbench l. Provides the ability to investigate errors and report them to Oracle. l. Identify errors via Health Checkers and reported errors (e. g. ORA-600). l. Repair some kinds of errors l. Open SR’s l. Package files to send to Oracle
Oracle Database 11 g Support Workbench l. Health Checkers l. Diagnostic programs that are automatically run by Oracle. Can also run manually. l 6 General categories of checkers l. Database Structure Integrity Check l. Data Block Integrity Check l. Redo Integrity Check l. Undo Segment Integrity Check l. Transaction Integrity Check l. Dictionary Integrity Check
Oracle Database 11 g Support Workbench l. What might a Health Checker Find? l. Missing Datafile l. Logically corrupted data file l. Data dictionary corruption l. Redo log corruption l. Undo segment corruption l. Errors discovered will surface in the support workbench and in data dictionary views.
Oracle Database 11 g Support Workbench l. Create an SR with the Support Workbench l. Use an existing problem l. Discovered by a checker l. Reported by Oracle (ORA-600) l. Create your own problem (User-reported problem). l. Automatic SR creation
Oracle Database 11 g Support Workbench l. Package files related to the problem l. Trace files l. Core dumps l. Alert log l. Automatic package creation l. Custom package creation
Oracle Database 11 g l. Automatic Memory Management
Oracle Database 11 g Automatic Memory Management l. Two parameters now to manage SGA and PGA l. Memory_target - Dynamic l. Memory_max_size – Not dynamic
Oracle Database 11 g Automatic Memory Management l. Memory_target= sga_max_size+ max(pga_aggregate_target, results of (select value from v$pgastat where name='maximum PGA allocated'; ) ) l Oracle will adjust underlying memory areas as required.
Oracle Database 11 g Automatic Memory Management l. Replaces these parameters l. Sga_max_size l. Sga_size (will be configured with a default value) l. Shared_pool_size l. Db_cache_size l. Java_pool_size l. Large_pool_size l. Streams_pool_size l. Pga_aggregate_target
Oracle Database 11 g Automatic Memory Management l. Still configure l. Log_buffer l. Keep, Recycle and non-default block size buffers
Oracle Database 11 g Automatic Memory Management l. Memory Advisor View V$MEMORY_TARGET_ADVICE (Dependent n AWR) SQL> select * from v$memory_target_advice order by memory_size_factor; MEMORY_SIZE_FACTOR ESTD_DB_TIME_FACTOR ------------------176. 5 126 1. 1443 264. 75 110 1 352 1 110 1 440 1. 25 110 1 528 1. 5 109. 99 616 1. 75 109. 99 704 2 109. 99
Oracle Database 11 g l. Database Replay
Oracle Database 11 g Database Replay l. Part of 11 g Real Application Testing (RAT) l. Provides the ability to replay database workload in real-time. l. Test system changes more reliably l. OS Changes l. New indexes l. Application SQL changes l. Converting to RAC (or from RAC)
Oracle Database 11 g Database Replay l. Four basic steps to replay l. Capture workload l. Process captured workload l. Replay workload l. Review results
Oracle Database 11 g Database Replay – Capture Workload l. Prepare for capture l. Your database is in ARCHIVELOG mode. l. Backup database (or set a restore point) l. Create directory for workload capture related files. l. Determine if you need to cycle the database before capture begins.
Oracle Database 11 g Database Replay – Capture Workload l. Execute capture l. OEM – Leads you through the process l. Manual – dbms_workload_capture. start_capture BEGIN DBMS_WORKLOAD_CAPTURE. START_CAPTURE (name => 'Pre_Upgrade_Capture_092507_01', dir => 'Workload_Capture', duration => 1200); END; /
Oracle Database 11 g Database Replay – Capture Workload l. Status of Workload Capture l. Use the dba_workload_captures view select id, ID -----12 name, status from dba_workload_captures; NAME STATUS ---------------Pre_Upgrade_Capture_092507_01 IN PROGRESS
Oracle Database 11 g Database Replay –Capture Workload l. Stop Capture l. OEM – Provides a button to stop workload capture. l. Manual – dbms_workload_capture. finish_capture BEGIN Exec dbms_workload_capture. finish_capture; END; /
Oracle Database 11 g Database Replay –Process Captured Workload l. Stop Capture l. OEM – Provides workflow guiding you through the process. l. Manual ldbms_workload_replay. process_capture BEGIN DBMS_WORKLOAD_REPLAY. PROCESS_CAPTURE (capture_dir => 'MY_CAPTURE'); END; /
Oracle Database 11 g Database Replay –Process Captured Workload l. Processing the workload creates additional files associated with the capture process. l. Prepares the capture for movement (if desired) l. Move workload to replay database directory lftp, sftp, etc…
Oracle Database 11 g Database Replay –Replay Captured Workload l. Prepare the replay database l. Create/Restore/Flashback database if required. l. Remap connections if required. l. External references such as database links, external tables, and the like might be different. l. You can create a connection mapping to resolve these differences. l. Make any changes you wish to make (new index)
Oracle Database 11 g Database Replay –Replay Captured Workload l. Replay the workload l. OEM provides a workflow to follow. l. Manually via PL/SQL packages and replay client(s) l. Initialize replay data with dbms_workload_replay. initialize_replay l. Prepare for the replay l. Start workload client (wrc) l. Start replay
Oracle Database 11 g Database Replay –Replay Captured Workload l. Initialize Replay Example: BEGIN DBMS_WORKLOAD_REPLAY. INITIALIZE_REPLAY replay_name => 'Pre_Upgrade_Cap_070107_01', replay_dir => 'MY_REPLAY_DIR'); END; / l. Initialized replay shows here: select id, name from dba_workload_replays; ID NAME ----------------------3 REPLAY-rob 11 gr 4 -20070712204202
Oracle Database 11 g Database Replay –Replay Captured Workload l. Prepare Replay l. Determine parameters of the replay l. Think time - manage the correct think time between database calls during Database Replay. l. Connection time - manage the timeframe between the start of the replay and when each session connection is made. l. Synchronization - disable SCN-based synchronization of the replay.
Oracle Database 11 g Database Replay –Replay Captured Workload l. Example BEGIN DBMS_WORKLOAD_REPLAY. PREPARE_REPLAY ( synchronization=>FALSE); END; /
Oracle Database 11 g Database Replay –Start Replay l. Starting Replay l. Part of OEM Database Replay Workflow l. Start replay OS clients l. Start replay on database
Oracle Database 11 g Database Replay –Start Workload Client(s) l. Workload Clients l. Multithreaded programs l. Each thread submits a workload from a captured session. Database replay will wait for these to start. l. Must start manually for both OEM and manual replay operations. l. Started from command line (wrc). l. Example: wrc mode=replay userid=replay_sys password=Robert
Oracle Database 11 g Database Replay –Start Database Replay l. Start Database Replay (manual) l. Use dbms_workload_replay. start_replay l. Will start replay. l. Will exit after replay has started. l. Use dbms_workload_replay. cancel_replay to stop.
Oracle Database 11 g Database Replay – Replay Monitoring Views l. Data Dictionary Views to Monitor Replay l. DBA_WORKLOAD_REPLAYS l. DBA_WORKLOAD_REPLAY_DIVERGENCE l. V$WORKLOAD_REPLAY_THREAD
Oracle Database 11 g Database Replay – Report on Replay Results l. Workload Replay Report l. Dbms_workload_replay. get_replay_info l. Dbms_workload_replay. report l. Various options for level of detail, output type, etc…
Oracle Database 11 g Database Replay – Report on Replay Results l. Example DECLARE rep_rpt CLOB; BEGIN rep_id : = DBMS_WORKLOAD_REPLAY. GET_REPLAY_INFO( dir => 'Workload_Capture'); rep_rpt : = DBMS_WORKLOAD_REPLAY. REPORT( replay_id => rep_id, format => 'TEXT'); dbms_output. put_line(rep_rpt); END; /
Oracle Database 11 g l. SQL Performance Analyzer
Oracle Database 11 g SQL Performance Analyzer l. Provides the ability to simulate the impacts of a given change on a system. l. New database parameter setting l. New index l. Remove an index l. Changed statistics l. OS Upgrades
Oracle Database 11 g SQL Performance Analyzer l Basic Workflow l Capture the SQL workload in the form of a SQL Tuning Set. l If you are using a test system, set up the test system and move the SQL tuning set to the test system. l Measure the SQL workload performance before the change.
Oracle Database 11 g SQL Performance Analyzer l Basic Workflow l Make the change. l Measure the SQL workload performance after the change. l Compare the performance results.
Oracle Database 11 g SQL Performance Analyzer l OEM Supports three types of workflows l Optimizer Upgrade l Parameter Change l Guided Workflow
Oracle Database 11 g SQL Performance Analyzer l How is this different than Database Replay? l You can pick the SQL you wish to test. l SQL Statements are executed sequentially, not concurrently. l Links into the SQL Tuning Optimizer are provided in OEM with SPA.
Oracle Database 11 g l. Virtual Columns
Oracle Database 11 g Virtual Columns l. Oracle Database 11 g support for derived values for table columns. l. Derived values are calculated by defining a set of expressions or functions that are associated with the virtual column when the table is created or a column is added.
Oracle Database 11 g Virtual Columns l. You cannot write to a virtual column. l. There is no support for index-organized, external, object, cluster, or temporary tables. l. There is no support for Oracle-supplied datatypes, user-defined types, LOBs, or LONG RAWs.
Oracle Database 11 g l. Example: Virtual Columns Create table employee ( emp_id number primary key, salary number (8, 2) not null, years_of_service number not null, curr_retirement as (salary*. 0005 * years_of_service) ); l. Creates a virtual column called curr_retirement. l. Derived value from salary, years_of_service columns.
Oracle Database 11 g l. Table Compression
Oracle Database 11 g Table Compression l. Compression in 11 g no longer subject to direct mode restrictions. l. All SQL operations now will result in compressed data. l. Potentially significant reduction in space utilization. l. Can potentially improve performance (dependent on CPU) due to much reduced row per block counts.
Oracle Database 11 g Table Compression l. Compression in 11 g no longer subject to direct mode restrictions. l. You can compress an entire table, or specific partitions. l. Unfortunately , compression is a seperatly licensed product.
Oracle Database 11 g l. Example Table Compression CREATE TABLE compress_demo ( tab_id NUMBER(6), tab_rec_time date, tab_store varchar 2(300) ) PARTITION BY RANGE (tab_rec_time) (PARTITION long_ago VALUES LESS THAN (TO_DATE('01 -JAN-2007', 'DD-MON-YYYY')) COMPRESS, PARTITION not_so_long_ago VALUES LESS THAN (TO_DATE('01 -APR-2007', 'DD-MON-YYYY')), PARTITION close_but_not_yet VALUES LESS THAN (TO_DATE('01 -JUN-2007', 'DD-MON-YYYY')), PARTITION now_or_future VALUES LESS THAN (MAXVALUE));
Oracle Database 11 g l. Backup and Recovery In Oracle Database 11 g
Oracle Database 11 g Backup and Recovery l. RMAN l. Data Recovery Advisor l. Flashback Database l. Snapshot Stand-by Database l. Data Pump
Oracle Database 11 g l. RMAN
Oracle Database 11 g RMAN l. Interfile Backup Parallelism l. Now the backup of a given data file can be done in parallel. l. Faster backup compression l. Active database duplication
Oracle Database 11 g l. Data Recovery Advisor
Oracle Database 11 g Data Recovery Advisor l. Processes detected data loss/corruption issues l. Recommends solutions to correct l. Manual l. Automatic l. Will execute automatic solution on demand.
Oracle Database 11 g Data Recovery Advisor l. Example (Command line) l. List detected failures: RMAN> list failure; List of Database Failures ============= Failure ID Priority Status Time Detected Summary ------------ ------242 HIGH OPEN 19 -SEP-07 One or more nonsystem datafiles are missing
Oracle Database 11 g Data Recovery Advisor l. List details of detected failures: RMAN> list failure detail; List of Database Failures ============= Failure ID Priority Status Time Detected Summary ------------ ------242 HIGH OPEN 19 -SEP-07 One or more nonsystem datafiles are missing Impact: See impact for individual child failures List of child failures for parent failure ID 242 Failure ID Priority Status Time Detected Summary ------------ ------470 HIGH OPEN 19 -SEP-07 Datafile 4: '/oracle 01/oradata/orcl/users 01. dbf' is missing Impact: Some objects in tablespace USERS might be unavailable
Oracle Database 11 g Data Recovery Advisor l. Corrective Advice…? RMAN> advise failure; List of Database Failures ============= Failure ID Priority Status Time Detected Summary ------------ ------242 HIGH OPEN 19 -SEP-07 One or more nonsystem datafiles are missing Mandatory Manual Actions ============ no manual actions available Optional Manual Actions ============ 1. If file /oracle 01/oradata/orcl/users 01. dbf was unintentionally renamed or moved, restore it
Oracle Database 11 g Data Recovery Advisor l. Corrective Advice…? Automated Repair Options ============ Option Repair Description ------------1 Restore and recover datafile 4 Strategy: The repair includes complete media recovery with no data loss Repair script: /oracle/app/oracle/diag/rdbms/orcl/hm/reco_2909488425. hm
Oracle Database 11 g Data Recovery Advisor l. Fix the problem – Manual or RMAN l. Manual - Run the repair script. l. Contents in our case were: # restore and recover datafile sql 'alter database datafile 4 offline'; restore datafile 4; recover datafile 4; sql 'alter database datafile 4 online';
Oracle Database 11 g Data Recovery Advisor l. Fix the problem – Manual or RMAN l. RMAN – Use the repair failure command RMAN> repair failure; -- Rman executes script
Oracle Database 11 g l. Flashback Database Features
Oracle Database 11 g Flashback Features l. Flashback Transaction Backout l. Backout committed transactions and dependent transactions, online! l. Must be generating supplemental logging data. l. OEM or Manual method.
Oracle Database 11 g Flashback Features l. Flashback Data Archives l. Track changes that occur on a table over the lifetime of the table. l. Must create a flashback archive and assign to an existing tablespace or tablespace group: Create flashback archive default archive_one_year tablespace retention_archives Quota 5 g retention 1 year;
Oracle Database 11 g Flashback Features l. Assign to a table Create table test_arch (id number) tablespace users flashback archive; Alter table other_test flashback archive; l. Turn off – All history is lost… Alter table other_test no flashback archive;
Oracle Database 11 g Flashback Features l. Many Table DDL commands are Disabled while a tablespace is in flashback archive mode. SQL> drop table test_arch; drop table test_arch * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table
Oracle Database 11 g Flashback Features l. Querying – Just use the as of clause in the select command: Select id from test as of timestamp (systimestamp - interval '6' month); l. Flashback the Table – The table can also be flashed back to a point in time further back: Flashback table test to timestamp (systimestamp – interval '6' month);
Oracle Database 11 g Flashback Features l. When space fills up – Transactions fail. l. Monitor space usage l. DBA_FLASHBACK_ARCHIVE_TS – Space allocated to each flashback archive. l. Size of objects in the archive – DBA_FLASHBACK_ARCHIVE_TABLES and DBA_EXTENTS.
Oracle Database 11 g l. Snapshot Standby Database
Oracle Database 11 g Snapshot Standby Database l. Open the standby for use l. Issue DML, DDL l. When done, put the database back into standby mode. l. Flashback to the point it was opened. l. All changed rolled back. l. Redo from primary DB applied.
Oracle Database 11 g l. Data Pump
Oracle Database 11 g Data Pump l. Export utility deprecated l. Compression of dump files l. Encryption l. Data remapping l. Table renaming l. Overwrite dump files
Oracle Database 11 g Data Pump – Exp utility deprecated l. The exp utility is deprecated. l. This means no bug fixes. l. The imp utility is not deprecated.
Oracle Database 11 g Data Pump – Compression of Dump Files l. The compression parameter now available with expdp. l. Provides for compression of Data Pump dump files. .
Oracle Database 11 g Data Pump – Compression of Dump Files l. Compression Options: l. ALL Enables compression for the entire operation. l. Metadata_only The default setting. Causes only the metadata to be compressed. l. Data_only Only the data being written to the dump file set will be compressed. l. None No compression will take place.
Oracle Database 11 g Data Pump – Compression of Dump Files l. Example expdp Robert/robert DIRECTORY= data_pump_dir DUMPFILE=hr_comp. dmp COMPRESSION=ALL
Oracle Database 11 g Data Pump – Encryption l. The encryption parameter supports encryption/decryption of dumpfile data l. You can encrypt lmetadata lboth
Oracle Database 11 g Data Pump – Encryption l. Available encryption algorithms l. AES 128 l. AES 192 l. AES 256 l. Define the encryption algorithm using the encryption_algorithm parameter.
Oracle Database 11 g Data Pump – Encryption l. The encryption_mode parameter supports either Password or wallet keyed encryption l. Available encryption modes l. Dual – Both password and encryption wallet used. l. Password – Only password used to authenticate (encryption_password parameter) l. Transparent – Only encryption wallet used.
Oracle Database 11 g Data Pump – Encryption l. Example expdp Robert/robert DIRECTORY=data_pump_dir DUMPFILE=hr_comp. dmp encryption=all encryption_password=Robert encryption_algorithm=AES 128 encryption_mode=PASSWORD
Oracle Database 11 g Data Pump – Data Obfuscation l. During export or import operation l. Modify data via PL/SQL routine l. Remap_data parameter l. Lists schema. table_name. column_name: package_name for remap operation l. Separate remap_data parameter for each table/column pair.
Oracle Database 11 g Data Pump – Data Obfuscation l. Example PL/SQL Code – Shifts each letter. Create or replace package my_package as function my_function (p_in_data varchar 2) return varchar 2; end; / Create or replace package body my_package as function my_function (p_in_data varchar 2) return varchar 2 As v_return varchar 2(30); begin v_return: =translate(p_in_data, 'abcdefghijklmnopqrstuvwxyz', 'bcdefghijklmnopqrstuvwxyza'); return v_return; end; /
Oracle Database 11 g Data Pump – Data Obfuscation l. Remap during export expdp Robert/robert DIRECTORY=data_pump_dir DUMPFILE=remap. dmp tables=Robert. names remap_data=Robert. names. user_name: Robert. my_package. my_function
Oracle Database 11 g Data Pump – Data Obfuscation l. Remap during import (with 2 columns) impdp Robert/robert DIRECTORY=data_pump_dir DUMPFILE=remap. dmp tables=ROBERT. NAMES remap_data=Robert. copy_names. user_name: Robert. my_package. my_function remap_data=Robert. copy_names. user_commment: Robert. my_package. my_function
Oracle Database 11 g Data Pump – Table Rename l. Rename tables during an import l. Not available when importing l. Use the Remap_table parameter l. Example impdp Robert/robert DIRECTORY=data_pump_dir DUMPFILE=remap. dmp tables=ROBERT. NAMES remap_table=ROBERT. NAMES: COPY_NAMES
Oracle Database 11 g Data Pump – Overwrite Dump Files l. You can now have Data Pump overwrite old dump files. l. Use the reuse_dumpfile parameter. l. Example expdp Robert/robert DIRECTORY=data_pump_dir DUMPFILE=remap. dmp tables=ROBERT. NAMES reuse_dumpfiles=Y
Oracle Database 11 g l. Partitioning In Oracle Database 11 g
Oracle Database 11 g Partitioning l. New and enhanced partitioning in 11 g l. Interval partitioning l. Extended composite partitioning l. Reference partitioning l. System partitioning l. Partition with Virtual Columns
Oracle Database 11 g l. Interval Partitioning
Oracle Database 11 g Partitioning – Interval Partitioning l. Automatic partition creation! l. Use the new interval keyword to define the interval for new partitions. l. Partition created when new data is added. Only the relevant partition is created. l. Example interval numtoyminterval(1, 'MONTH'))
Oracle Database 11 g Partitioning – Interval Partitioning l. Interval Functions l. Numtodsinterval – Convert a number into an interval day to second literal. l. Numtoyminterval – Convert a number into an interval year to month literal.
Oracle Database 11 g Partitioning – Interval Partitioning l. The usual list of “cant’s” (which probably means that’s exactly what you want to do). l. The interval-partitioned table can only have one partitioning key column, and it must be of type NUMBER or DATE. l. Index-organized tables are not supported. l. You cannot create a domain index on an interval-partitioned table.
Oracle Database 11 g Partitioning – Interval Partitioning l. More “cant’s” l. Interval partitioning does not support subpartitions. Thus, you can create an interval partition on the main partition of a composite partitioned table, but the subpartition cannot be interval-partitioned. l. Can not use maxvalue, and the partitioning key column cannot specify NULL values.
Oracle Database 11 g Partitioning – Interval Partitioning l. Example create table statement create table daily_sales ( product_id number not null , customer_id number not null , sale_dt date not null , quantity_sold number(3) not null) partition by range (sale_dt) interval (numtoyminterval(1, 'MONTH')) ( partition p_before_1_jan_2007 values less than (to_date('01 -01 -2007', 'dd-mm-yyyy')));
Oracle Database 11 g Partitioning – Interval Partitioning l. New partition names are ugly… select partition_name from user_tab_partitions where table_name = 'DAILY_SALES' order by partition_position; PARTITION_NAME --------------P_BEFORE_1_JAN_2007 SYS_P 41 Added Partition
Oracle Database 11 g Partitioning – Interval Partitioning l. You can fix that though… alter table daily_sales rename partition sys_p 41 to p_Jan_2007; l. Migrate partitioned tables to interval partitioning alter table employee_compensation set interval (numtoyminterval(1, 'MONTH'));
Oracle Database 11 g Partitioning – Interval Partitioning l. New syntax for addressing partition by values is a helpful: select * from daily_sales partition for (to_date('31 -dec-2007', 'dd-mon-yyyy')); l. Recommend that developers migrate to this syntax and away from any direct mention of partition names.
Oracle Database 11 g l. Extended Composite Partitioning
Oracle Database 11 g Partitioning – Extended Composite l. A whole host of new composite partition options are available in 11 g l. Composite range-range partitioning l. Composite list-hash partitioning l. Composite list-list partitioning
Oracle Database 11 g l. Reference Partitioning
Oracle Database 11 g Partitioning – Reference l. Reference partitioning allows you to partition a child table based on the partition key of a given parent table. l. The net effect is that the child table is partitioned on the same key as the parent table, even if the child table does not have that key column in it!
Oracle Database 11 g Partitioning – Reference l. Example: Parent table creation create table customer_orders ( customer_id number, order_id number not null, order_date not null, order_mode varchar 2(8), order_status varchar 2(1)) partition by range (order_date) ( partition p_before_jan_2007 values less than(to_date('01 -JAN-2007', 'dd-MON-yyyy')) , partition p_2007_jan values less than(to_date('01 -FEB-2007', 'dd-MON-yyyy'))) parallel; alter table customer_orders add constraint customer_orders_pk primary key (order_id);
Oracle Database 11 g Partitioning – Reference Note: No order_date l. Example: Child table creation column create table customer_order_items BUT the FK Will be used to align ( order_id number not null The partitions! , product_id number not null Essentially the two tables will , quantity number not null be equipartitioned. , sales_amount number not null , constraint customer_order_items_orders_fk foreign key (order_id) references customer_orders(order_id) ) partition by reference (customer_order_items_orders_fk) parallel; Note partition by reference statement
Oracle Database 11 g Partitioning – Reference l. Does not support l. Interval partitioning l. Index-organized tables l. External tables l. A domain index storage table l. The reference primary key or unique constraint cannot point to a virtual column
Oracle Database 11 g l. System Partitioning
Oracle Database 11 g Partitioning – System l. Provides the ability to create a single table that has many physical partitions. l. No partition key is defined. l. You define a specific number of partitions l. You must define which partition the data goes in when doing an insert of data. l. Delete and update SQL does not require any special syntax.
Oracle Database 11 g Partitioning – System l. Example CREATE TABLE Test. Table (col 1 integer, col 2 integer) PARTITION BY SYSTEM( PARTITION s 1 TABLESPACE tbs_s 1, PARTITION s 2 TABLESPACE tbs_s 2, PARTITION s 3 TABLESPACE tbs_s 3, PARTITION s 4 TABLESPACE tbs_s 4);
Oracle Database 11 g Partitioning – System l. Example Insert INSERT INTO Test. Table PARTITION (s 1) VALUES (4, 5); l. If you can, it’s also a good idea to put a partition specification in other statements to reduce partition searching. For example: update testable partition (s 1) set col 2 = 6 where col 1 = 4;
Oracle Database 11 g l. Partitioning With Virtual Columns
Oracle Database 11 g Partitioning – Partition with Virtual Columns l. You can use a virtual column as the partition key for a table.
Oracle Database 11 g Partitioning – Partition with Virtual Columns l. Example Create table part_employee ( emp_id number primary key, sal number not null, years_of_service number not null, curr_retirement as sal*. 0005*years_of_service)) partition by range (curr_retirement) ( partition not_much values less than (100) , partition just_enough values less than (500) , partition oh_no_we_are_in_trouble values less than (maxvalue));
Oracle Database 11 g l. Oracle Database 11 g Strategic Strategies
Oracle Database 11 g – Strategic Discussions l. Is 11 g ready for prime time? l. First release woes… l. At first glance, 11 g seems to be a smaller release. Don’t be fooled. There are some significant features for both the developer and the DBA. l. Example of the impacts of a new feature – SQL Plan Management
Oracle Database 11 g – Strategic Discussions l. My recommendations l. Wait for the first patch set and begin testing. l. Use RAT to determine the impacts of 11 g on your workload. l. RAT will also be handy to have when looking for 11 g induced errors (ala ORA-0600’s). l. Move to 11 g after successful regression testing. l. Be careful of the impacts of new features on how you do things.
Oracle Database 11 g – Strategic Discussions l. I’m running 9 i. Should I move to 10 g or 11 g? l. If you need the features of 10 g now, I’d move to 10 g. l. If you can wait, I’d prefer to move to 11 g after the first patch set and full regression testing. l. Your support will last longer. l. One move, rather than two. Less risk. l. More features, better performance. l. Watch out for licensing!
Oracle Database 11 g – Strategic Discussions l. Reported bugs in 11 g l. Some RAC Bugs - Node on clustered database CPU high. Memory leaks. l. Some basic code bugs- Archive logs created in the wrong place. l. LOB corruption in Intermedia l. Several documentation bugs. l. Lots of 10 g. R 2 bugs fixed in 11 g.
Oracle Database 11 g l. And so….
Oracle Database 11 g l. We have discussed a great many features present in 11 g. l. There an equally large number of features we did not cover, we simply did not have time (or I had to much time left over and we will talk about them!). l. SQL Plan Management l. Transparent tablespace encryption l. Flashback Data Archive l. Private statistics
Oracle Database 11 g l. More 11 g Features we did not cover, we simply did not have time. l. Recovery of old statistics l. DDL Lock Timeout alter session set ddl_lock_timeout = 10; l. Result Cache l. PL/SQL New Features l. And more…
Скачать презентацию Oracle Database 11 g New Features for the
537d25079cc722502b704f0ed1428fd3.ppt