Transcript Oracle Database 11g New Features

Oracle Database 11g New
Features
New Features for the DBA
Robert G. Freeman
Collaborate 2008
Robert G. Freeman
About Me…
 Oracle Press Author

Oracle Database 11g New Features
 Oracle Database 10g RMAN Backup and Recovery
 Portable DBA: Oracle
 Other OP Titles

Principal Engineer
The Church of Jesus Christ of Latter-Day Saints
Husband, father, pilot, black belt, general
malcontent.
Oracle Database 11g
Topics we will cover

Installing and Upgrading to 11g.
 Administration and Change Assurance
 Backup and Recovery Features
 Partitioning Features
Strategic Strategies
Oracle Database 11g
INSTALLING AND
UPGRADING TO 11g
Oracle Database 11g
Installing 11g
 APEX
installed automatically now.
 The install has changed a bit
 ORACLE_BASE must be defined.
 ORACLE_BASE stored in Inventory.
Oracle Database 11g
Installing 11g
Pre-requisites check
Make
sure all OS patches (e.g. RPM’s) are
installed.
Sometimes need to skip checks (for
example, running RHEL5 Linux).
Recommend you install a small database
during the install or afterwards to test that all
went well.
Oracle Database 11g
Installing 11g
I
would recommend you not upgrade when
first installing the DB software
Oracle offers the option to upgrade
databases during software install
Test the install base before you start any
upgrades
Database creation
Networking test
Oracle Database 11g
Upgrade to 11g – Upgrade Methods

Supported Upgrade Methods
 Database Upgrade Assistant
 Manual Upgrades
 Export/Import
 SQL*Plus Copy
Oracle Database 11g
Upgrade to 11g – Upgrade Paths
DBUA/Manual
Upgrade Supported Upgrade
Paths
Upgrade
to supported upgrade version
Any Oracle version < 9.2.0.4
Upgrade directly to 11g
Oracle 9.2.0.4 (or higher)
Oracle 10.1.0.2 (or higher)
Oracle 10.2.0.1(or higher) (10.2.0.3 for CW)
Exp/Imp method or SQL*Plus copy method
generally supported across versions.
Oracle Database 11g
Upgrade to 11g - DBUA
Using the DBUA
Run from the 11g Oracle Home
Personally recommend you make your own
backup before you upgrade a database.
Executes pre-upgrade checks and postupgrade checks.
Oracle Database 11g
Upgrade to 11g - DBUA
DBUA prompts you for
Database to upgrade (from /etc/oratab or an
Oracle service)
Location of DIAGNOSTIC_DEST
Option to move datafiles (including to/from
ASM)
Backup of your database
Ability to manage passwords for any new
database users created.
Oracle Database 11g
Upgrade to 11g – Select Database
Oracle Database 11g
Note Logging
Upgrade to 11g - Logging
Oracle Database 11g
Upgrade to 11g – Log Files
Log Name
Purpose
UpgradeResults.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 11g
Upgrade to 11g – DBUA Upgrade
Summary
Oracle Database 11g
Upgrade to 11g – Manual Upgrades
Manual
Upgrades supported
Can be a bit tedious and requires a number of
manual steps.
Recommend you use a checklist!
More overall control over the upgrade process.
Most steps can be re-executed if they fail once
you have corrected the cause of the failure.
Oracle Database 11g
Upgrade to 11g – Using Exp/Imp
Useful if you are migrating from a database
version that does not support the DBUA or
manual upgrade method.
In some ways perhaps the safest way to
upgrade.
Allows you to configure the new 11g database
before you move data over to it.
In many cases, may be the slowest way to
upgrade your database.
Oracle Database 11g
Upgrade to 11g – Using Exp/Imp/DataPump
Allows
you to move to different platforms
easily.
Can also be used to rollback from an
unsuccessful upgrade.
Oracle Database 11g
Upgrade to 11g
Test
test test
You never know what won’t work in your
environment.
You can take advantage of change
assurance.
Change assurance might not catch
everything (e.g. 9i to 10.2 ODBC issue)
Oracle Database 11g
Administration of
Oracle Database 11g
Oracle Database 11g
Administration of Oracle Database 11g
ADR
AWR
Support Workbench
Automatic Memory
Database
Management
Replay
SQL performance Analyzer
Virtual Columns
Table Compression
Oracle Database 11g
Automatic Diagnostic
Repository (ADR)
Oracle Database 11g
Automatic Diagnostic Repository (ADR)
New management structure in 11g called the
Automatic Diagnostic Repository (ADR)
Centralized and standardized repository for
the Oracle 11g Fault Diagnosability
infrastructure.
Replaces several existing directories
User_dump_dest
Background_dump_dest
Core_dump_dest
Oracle Database 11g
Automatic Diagnostic Repository (ADR)
Files stored in the ADR:
Alert log
Trace files
Incident packages
Default location
$ORACLE_BASE/diag/{product}/{database}/{instance}
Example:
/u01/app/oracle/diag/rdbms/orcl/orcl
Oracle Database 11g
DIAGNOSTIC_DEST
Oracle Database 11g
ADR
Alert log now XML based (though a text
copy is still available).
New view V$DIAG_INFO
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:\ORACLE\PRODUCT
1 ADR Home
C:\ORACLE\PRODUCT\diag\rdbms\rob11gr4\rob11gr4
Oracle Database 11g
ADR
New
tool: ADRCI
ADR Command Line Interpreter
Used to manage and report from ADR.
Oracle Database 11g
ADR
ADRCI
Example
C:\oracle\product\11gBetaR4\db_01\NETWORK\ADMIN>adrci
adrci>>show alert -tail
ADR Home = C:\oracle\product\diag\rdbms\rob11gr4\rob11gr4:
************************************************************200
7-06-02 00:42:47.398000 -06:00
Logminer Bld: Lockdown Complete. DB_TXN_SCN is
UnwindToSCN (LockdownSCN) 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 11g
ADR
ADRCI
Homes
Systems with multiple databases will have
individual ADR homes.
Set the ADR_HOME when using ADRCI to
make sure you are pointed to the correct
location.
Oracle Database 11g
ADRCI
Homes
Example:
ADR
adrci> show homes
ADR Homes:
diag/rdbms/probe2/PROBE2
diag/rdbms/probe1/PROBE1
diag/asm/+asm/+ASM11
diag/tnslsnr/prolin2/listener
adrci> set homepath
diag/rdbms/probe2/PROBE2
adrci> show homes
ADR Homes:
diag/rdbms/probe2/PROBE2
Oracle Database 11g
Automatic Workload Repository (AWR)
Oracle Database 11g
AWR
AWR
Features
Default data retention
Changes from 7 days to 8 days.
Baselines
Adaptive Metric Thresholds
Oracle Database 11g
AWR
AWR
Baselines
Moving
window baselines A moving baseline
typically based on the entirety of the statistical data
contained in AWR
Single baseline Allows you to define a baseline
to be captured for a single specified period of time
in the future
Repeating baseline Allows you to define a
baseline to be captured for a repeating period of
time in the future
Oracle Database 11g
AWR
Adaptive
Metric Thresholds
Reporting thresholds developed using
AWR metrics.
Allows for floating monitoring thresholds.
As your system workload changes, the
alerting thresholds will evolve to reflect the
current state of the database.
Oracle Database 11g
Support Workbench
Oracle Database 11g
Support Workbench
Provides
the ability to investigate errors and
report them to Oracle.
Identify errors via Health Checkers and
reported errors (e.g. ORA-600).
Repair some kinds of errors
Open SR’s
Package files to send to Oracle
Oracle Database 11g
Support Workbench
Health Checkers
Diagnostic programs that are automatically
run by Oracle. Can also run manually.
6 General categories of checkers
Database
Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Undo Segment Integrity Check
Transaction Integrity Check
Dictionary Integrity Check
Oracle Database 11g
Support Workbench
What
might a Health Checker Find?
Missing Datafile
Logically corrupted data file
Data dictionary corruption
Redo log corruption
Undo segment corruption
Errors discovered will surface in the support
workbench and in data dictionary views.
Oracle Database 11g
Support Workbench
Create
an SR with the Support Workbench
Use an existing problem
Discovered by a checker
Reported by Oracle (ORA-600)
Create your own problem (User-reported
problem).
Automatic SR creation
Oracle Database 11g
Support Workbench
Package
files related to the problem
Trace files
Core dumps
Alert log
Automatic package creation
Custom package creation
Oracle Database 11g
Automatic Memory
Management
Oracle Database 11g
Automatic Memory Management
Two
parameters now to manage SGA and PGA
Memory_target - Dynamic
Memory_max_size – Not dynamic
Oracle Database 11g
Automatic Memory Management
Memory_target= sga_max_size+
max(pga_aggregate_target,
results of (select value from v$pgastat where
name='maximum PGA allocated';) )
 Oracle will adjust underlying memory areas as
required.
Oracle Database 11g
Automatic Memory Management
Replaces these parameters
Sga_max_size
Sga_size (will be configured with a default
value)
Shared_pool_size
Db_cache_size
Java_pool_size
Large_pool_size
Streams_pool_size
Pga_aggregate_target
Oracle Database 11g
Automatic Memory Management
Still
configure
Log_buffer
Keep, Recycle and non-default block size
buffers
Oracle Database 11g
Automatic Memory Management
Memory Advisor View
V$MEMORY_TARGET_ADVICE (Dependent n
AWR)
SQL> select * from v$memory_target_advice
order by memory_size_factor;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME 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 11g
Database
Replay
Oracle Database 11g
Database Replay
Part of 11g Real Application Testing (RAT)
Provides the ability to replay database
workload in real-time.
Test system changes more reliably
OS Changes
New indexes
Application SQL changes
Converting to RAC (or from RAC)
Oracle Database 11g
Database Replay
Four
basic steps to replay
Capture workload
Process captured workload
Replay workload
Review results
Oracle Database 11g
Database Replay – Capture Workload
Prepare
for capture
Your database is in ARCHIVELOG mode.
Backup database (or set a restore point)
Create directory for workload capture related
files.
Determine if you need to cycle the database
before capture begins.
Oracle Database 11g
Database Replay – Capture Workload
Execute
capture
OEM – Leads you through the process
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 11g
Database Replay – Capture Workload
Status
of Workload Capture
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 11g
Database Replay –Capture Workload
Stop
Capture
OEM – Provides a button to stop workload
capture.
Manual – dbms_workload_capture.finish_capture
BEGIN
Exec dbms_workload_capture.finish_capture;
END;
/
Oracle Database 11g
Database Replay –Process Captured
Workload
Stop Capture
– Provides workflow guiding you through
the process.
Manual
dbms_workload_replay.process_capture
OEM
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE
(capture_dir => 'MY_CAPTURE');
END;
/
Oracle Database 11g
Database Replay –Process Captured
Workload
Processing
the workload creates additional files
associated with the capture process.
Prepares the capture for movement (if desired)
Move workload to replay database directory
ftp, sftp, etc…
Oracle Database 11g
Database Replay –Replay Captured Workload
Prepare
the replay database
Create/Restore/Flashback database if required.
Remap connections if required.
External references such as database links,
external tables, and the like might be different.
You can create a connection mapping to resolve
these differences.
Make any changes you wish to make (new index)
Oracle Database 11g
Database Replay –Replay Captured Workload
Replay the workload
OEM provides a workflow to follow.
Manually via PL/SQL packages and replay
client(s)
Initialize replay data with
dbms_workload_replay.initialize_replay
Prepare for the replay
Start workload client (wrc)
Start replay
Oracle Database 11g
Database Replay –Replay Captured Workload
Initialize
Replay Example:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY
replay_name => 'Pre_Upgrade_Cap_070107_01',
replay_dir => 'MY_REPLAY_DIR');
END;
/
Initialized
replay shows here:
select id, name from dba_workload_replays;
ID NAME
---------- ---------------------------------3 REPLAY-rob11gr4-20070712204202
Oracle Database 11g
Database Replay –Replay Captured Workload
Prepare Replay
Determine parameters of the replay
Think time - manage the correct think time
between database calls during Database Replay.
Connection time - manage the timeframe
between the start of the replay and when each
session connection is made.
Synchronization - disable SCN-based
synchronization of the replay.
Oracle Database 11g
Database Replay –Replay Captured Workload
Example
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (
synchronization=>FALSE);
END;
/
Oracle Database 11g
Database Replay –Start Replay
Starting
Replay
Part of OEM Database Replay Workflow
Start replay OS clients
Start replay on database
Oracle Database 11g
Database Replay –Start Workload Client(s)
Workload Clients
Multithreaded programs
Each thread submits a workload from a captured
session. Database replay will wait for these to start.
Must start manually for both OEM and manual
replay operations.
Started from command line (wrc).
Example:
wrc mode=replay userid=replay_sys
password=Robert
Oracle Database 11g
Database Replay –Start Database Replay
Start
Database Replay (manual)
Use dbms_workload_replay.start_replay
Will start replay.
Will exit after replay has started.
Use dbms_workload_replay.cancel_replay to stop.
Oracle Database 11g
Database Replay – Replay Monitoring Views
Data
Dictionary Views to Monitor Replay
DBA_WORKLOAD_REPLAYS
DBA_WORKLOAD_REPLAY_DIVERGENCE
V$WORKLOAD_REPLAY_THREAD
Oracle Database 11g
Database Replay – Report on Replay Results
Workload Replay Report
Dbms_workload_replay.get_replay_info
Dbms_workload_replay.report
Various options for level of detail, output type,
etc…
Oracle Database 11g
Database Replay – Report on Replay Results
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 11g
SQL Performance Analyzer
Oracle Database 11g
SQL Performance Analyzer
Provides
the ability to simulate the impacts of a
given change on a system.
New database parameter setting
New index
Remove an index
Changed statistics
OS Upgrades
Oracle Database 11g
SQL Performance Analyzer

Basic Workflow
 Capture the SQL workload in the form of a
SQL Tuning Set.
 If you are using a test system, set up the test
system and move the SQL tuning set to the test
system.
 Measure the SQL workload performance
before the change.
Oracle Database 11g
SQL Performance Analyzer

Basic Workflow
 Make the change.
 Measure the SQL workload performance after
the change.
 Compare the performance results.
Oracle Database 11g
SQL Performance Analyzer

OEM Supports three types of workflows
 Optimizer Upgrade
 Parameter Change
 Guided Workflow
Oracle Database 11g
SQL Performance Analyzer

How is this different than Database Replay?
 You can pick the SQL you wish to test.
 SQL Statements are executed sequentially, not
concurrently.
 Links into the SQL Tuning Optimizer are
provided in OEM with SPA.
Oracle Database 11g
Virtual
Columns
Oracle Database 11g
Virtual Columns
Oracle
Database 11g support for derived
values for table columns.
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 11g
Virtual Columns
You
cannot write to a virtual column.
There is no support for index-organized,
external, object, cluster, or temporary tables.
There is no support for Oracle-supplied
datatypes, user-defined types, LOBs, or LONG
RAWs.
Oracle Database 11g
Virtual Columns
Example:
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) );
Creates
a virtual column called curr_retirement.
Derived value from salary, years_of_service
columns.
Oracle Database 11g
Table
Compression
Oracle Database 11g
Table Compression
Compression in 11g no longer subject to direct
mode restrictions.
All SQL operations now will result in
compressed data.
Potentially significant reduction in space
utilization.
Can potentially improve performance
(dependent on CPU) due to much reduced
row per block counts.
Oracle Database 11g
Table Compression
Compression in 11g no longer subject to direct
mode restrictions.
You can compress an entire table, or
specific partitions.
Unfortunately , compression is a seperatly
licensed product.
Oracle Database 11g
Example
Table Compression
CREATE TABLE compress_demo (
tab_id
NUMBER(6), tab_rec_time date,
tab_store varchar2(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 11g
Backup
and Recovery In Oracle Database 11g
Oracle Database 11g
11g Backup and Recovery
RMAN
Data
Recovery Advisor
Flashback Database
Snapshot Stand-by Database
Data Pump
Oracle Database 11g
RMAN
Oracle Database 11g
RMAN
Interfile
Backup Parallelism
Now the backup of a given data file can be
done in parallel.
Faster backup compression
Active database duplication
Oracle Database 11g
Data
Recovery Advisor
Oracle Database 11g
Data Recovery Advisor
Data
Recovery Advisor
Processes detected data loss/corruption issues
Recommends solutions to correct
Manual
Automatic
Will execute automatic solution on demand.
Oracle Database 11g
Data Recovery Advisor
Example
(Command line)
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 11g
Data Recovery Advisor
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:
'/oracle01/oradata/orcl/users01.dbf' is missing
Impact: Some objects in tablespace USERS might be unavailable
Oracle Database 11g
Data Recovery Advisor
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 /oracle01/oradata/orcl/users01.dbf was unintentionally
renamed or moved, restore it
Oracle Database 11g
Data Recovery Advisor
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/orcl/hm/reco_2909488425.hm
Oracle Database 11g
Data Recovery Advisor
Fix the problem – Manual or RMAN
Manual - Run the repair script.
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 11g
Data Recovery Advisor
the problem – Manual or RMAN
RMAN – Use the repair failure command
Fix
RMAN> repair failure;
-- Rman executes script
Oracle Database 11g
Flashback
Database Features
Oracle Database 11g
Flashback Features
Flashback Transaction Backout
Backout
committed transactions and dependent
transactions, online!
Must be generating supplemental logging data.
OEM or Manual method.
Oracle Database 11g
Flashback Features
Flashback Data Archives
Track changes that occur on a table over the
lifetime of the table.
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 5g retention 1 year;
Oracle Database 11g
Flashback Features
Assign to a table
Create table test_arch (id number)
tablespace users flashback archive;
Alter table other_test flashback archive;
Turn
off – All history is lost…
Alter table other_test
no flashback archive;
Oracle Database 11g
Flashback Features
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 11g
Flashback Features
Querying – Just use the as of clause in the select
command:
Select id from test
as of timestamp
(systimestamp - interval '6' month);
the Table – The table can also be
flashed back to a point in time further back:
Flashback
Flashback table test to timestamp
(systimestamp – interval '6' month);
Oracle Database 11g
Flashback Features
space fills up – Transactions fail.
Monitor space usage
DBA_FLASHBACK_ARCHIVE_TS – Space
allocated to each flashback archive.
Size of objects in the archive –
DBA_FLASHBACK_ARCHIVE_TABLES and
DBA_EXTENTS.
When
Oracle Database 11g
Snapshot
Standby Database
Oracle Database 11g
Snapshot Standby Database
Open
the standby for use
Issue DML, DDL
When done, put the database back into standby
mode.
Flashback to the point it was opened.
All changed rolled back.
Redo from primary DB applied.
Oracle Database 11g
Data
Pump
Oracle Database 11g
11g Data Pump
Data
Pump
Export utility deprecated
Compression of dump files
Encryption
Data remapping
Table renaming
Overwrite dump files
Oracle Database 11g
11g Data Pump – Exp utility deprecated
The
exp utility is deprecated.
This means no bug fixes.
The imp utility is not deprecated.
Oracle Database 11g
11g Data Pump – Compression of Dump
Files
The
compression parameter now available with
expdp.
Provides for compression of Data Pump dump
files..
Oracle Database 11g
11g Data Pump – Compression of Dump
Files
Compression Options:
ALL
Enables compression for the entire
operation.
Metadata_only The default setting. Causes
only the metadata to be compressed.
Data_only Only the data being written to the
dump file set will be compressed.
None No compression will take place.
Oracle Database 11g
11g Data Pump – Compression of Dump
Files
Example
expdp Robert/robert
DIRECTORY= data_pump_dir
DUMPFILE=hr_comp.dmp
COMPRESSION=ALL
Oracle Database 11g
11g Data Pump – Encryption
The
encryption parameter supports
encryption/decryption of dumpfile data
You can encrypt
metadata
data
both
Oracle Database 11g
11g Data Pump – Encryption
Available
encryption algorithms
AES128
AES192
AES256
Define the encryption algorithm using the
encryption_algorithm parameter.
Oracle Database 11g
11g Data Pump – Encryption
The encryption_mode parameter supports either
Password or wallet keyed encryption
Available encryption modes
Dual – Both password and encryption wallet
used.
Password – Only password used to authenticate
(encryption_password parameter)
Transparent – Only encryption wallet used.
Oracle Database 11g
11g Data Pump – Encryption
Example
expdp Robert/robert DIRECTORY=data_pump_dir
DUMPFILE=hr_comp.dmp
encryption=all
encryption_password=Robert
encryption_algorithm=AES128
encryption_mode=PASSWORD
Oracle Database 11g
11g Data Pump – Data Obfuscation
Data
Obfuscation
During export or import operation
Modify data via PL/SQL routine
Remap_data parameter
Lists schema.table_name.column_name:
package_name for remap operation
Separate remap_data parameter for each
table/column pair.
Oracle Database 11g
11g Data Pump – Data Obfuscation
Example PL/SQL Code – Shifts each letter.
Create or replace package my_package
as
function my_function (p_in_data varchar2)
return varchar2;
end;
/
Create or replace package body my_package
as
function my_function (p_in_data varchar2)
return varchar2
As
v_return
varchar2(30);
begin
v_return:=translate(p_in_data, 'abcdefghijklmnopqrstuvwxyz',
'bcdefghijklmnopqrstuvwxyza');
return v_return;
end;
end;
/
Oracle Database 11g
11g Data Pump – Data Obfuscation
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 11g
11g Data Pump – Data Obfuscation
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 11g
11g Data Pump – Table Rename
Rename tables during an import
Not available when importing
Use the Remap_table parameter
Example
impdp Robert/robert DIRECTORY=data_pump_dir
DUMPFILE=remap.dmp tables=ROBERT.NAMES
remap_table=ROBERT.NAMES:COPY_NAMES
Oracle Database 11g
11g Data Pump – Overwrite Dump Files
You
can now have Data Pump overwrite old dump
files.
Use the reuse_dumpfile parameter.
Example
expdp Robert/robert DIRECTORY=data_pump_dir
DUMPFILE=remap.dmp tables=ROBERT.NAMES
reuse_dumpfiles=Y
Oracle Database 11g
Partitioning
In Oracle Database 11g
Oracle Database 11g
11g Partitioning
New and enhanced partitioning in 11g
Interval partitioning
Extended composite partitioning
Reference partitioning
System partitioning
Partition with Virtual Columns
Oracle Database 11g
Interval
Partitioning
Oracle Database 11g
11g Partitioning – Interval Partitioning
Interval
Partitioning
Automatic partition creation!
Use the new interval keyword to define the
interval for new partitions.
Partition created when new data is added. Only
the relevant partition is created.
Example
interval numtoyminterval(1,'MONTH'))
Oracle Database 11g
11g Partitioning – Interval Partitioning
Interval
Partitioning
Interval Functions
Numtodsinterval – Convert a number into
an interval day to second literal.
Numtoyminterval – Convert a number into
an interval year to month literal.
Oracle Database 11g
11g Partitioning – Interval Partitioning
Interval Partitioning
The usual list of “cant’s” (which probably
means that’s exactly what you want to do).
The interval-partitioned table can only have
one partitioning key column, and it must be of
type NUMBER or DATE.
Index-organized tables are not supported.
You cannot create a domain index on an
interval-partitioned table.
Oracle Database 11g
11g Partitioning – Interval Partitioning
Interval
Partitioning
More “cant’s”
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.
Can not use maxvalue, and the partitioning
key column cannot specify NULL values.
Oracle Database 11g
11g Partitioning – Interval Partitioning
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 11g
11g Partitioning – Interval Partitioning
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_P41
 Added Partition
Oracle Database 11g
11g Partitioning – Interval Partitioning
You
can fix that though…
alter table daily_sales
rename partition sys_p41 to p_Jan_2007;
Migrate
partitioned tables to interval partitioning
alter table employee_compensation
set interval (numtoyminterval(1,'MONTH'));
Oracle Database 11g
11g Partitioning – Interval Partitioning
New syntax for addressing partition by values is a
helpful:
select * from daily_sales partition
for (to_date('31-dec-2007','dd-mon-yyyy'));
Recommend that
developers migrate to this syntax
and away from any direct mention of partition
names.
Oracle Database 11g
Extended Composite Partitioning
Oracle Database 11g
11g Partitioning – Extended Composite
A whole
host of new composite partition options
are available in 11g
Composite range-range partitioning
Composite list-range partitioning
Composite list-hash partitioning
Composite list-list partitioning
Oracle Database 11g
Reference
Partitioning
Oracle Database 11g
11g Partitioning – Reference
Reference
partitioning allows you to partition a
child table based on the partition key of a given
parent table.
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 11g
11g Partitioning – Reference
Example: Parent table creation
create table customer_orders
( customer_id number, order_id number not null,
order_date date not null, order_mode varchar2(8),
order_status varchar2(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 11g
11g Partitioning – Reference
Note: No order_date
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 11g
11g Partitioning – Reference
Does not support
Interval partitioning
Index-organized tables
External tables
A domain index storage table
The reference primary key or unique constraint
cannot point to a virtual column
Oracle Database 11g
System
Partitioning
Oracle Database 11g
11g Partitioning – System
Provides
the ability to create a single table that has
many physical partitions.
No partition key is defined.
You define a specific number of partitions
You must define which partition the data goes
in when doing an insert of data.
Delete and update SQL does not require any
special syntax.
Oracle Database 11g
11g Partitioning – System
Example
CREATE TABLE TestTable
(col1 integer, col2 integer)
PARTITION BY SYSTEM(
PARTITION s1 TABLESPACE tbs_s1,
PARTITION s2 TABLESPACE tbs_s2,
PARTITION s3 TABLESPACE tbs_s3,
PARTITION s4 TABLESPACE tbs_s4);
Oracle Database 11g
11g Partitioning – System
Example Insert
INSERT INTO TestTable
PARTITION (s1) VALUES (4,5);
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 (s1)
set col2 = 6 where col1 = 4;
Oracle Database 11g
Partitioning With Virtual
Columns
Oracle Database 11g
11g Partitioning – Partition with Virtual Columns
You
can use a virtual column as the partition key
for a table.
Oracle Database 11g
11g Partitioning – Partition with Virtual Columns
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 11g
Oracle
Database 11g Strategic Strategies
Oracle Database 11g
Oracle Database 11g – Strategic Discussions
Is 11g ready for prime time?
First release woes…
At first glance, 11g seems to be a smaller
release. Don’t be fooled. There are some
significant features for both the developer and
the DBA.
Example of the impacts of a new feature –
SQL Plan Management
Oracle Database 11g
Oracle Database 11g – Strategic Discussions
My recommendations
Wait for the first patch set and begin testing.
Use RAT to determine the impacts of 11g on
your workload.
RAT will also be handy to have when looking
for 11g induced errors (ala ORA-0600’s).
Move to 11g after successful regression testing.
Be careful of the impacts of new features on
how you do things.
Oracle Database 11g
Oracle Database 11g – Strategic Discussions
I’m running 9i. Should I move to 10g or 11g?
If you need the features of 10g now, I’d move
to 10g.
If you can wait, I’d prefer to move to 11g after
the first patch set and full regression testing.
Your support will last longer.
One move, rather than two. Less risk.
More features, better performance.
Watch out for licensing!
Oracle Database 11g
Oracle Database 11g – Strategic Discussions
Reported
bugs in 11g
Some RAC Bugs - Node one on clustered
database CPU high. Memory leaks.
Some basic code bugs- Archive logs created in
the wrong place.
LOB corruption in Intermedia
Several documentation bugs.
Lots of 10gR2 bugs fixed in 11g.
Oracle Database 11g
And
so….
Oracle Database 11g
We
have discussed a great many features
present in 11g.
There are 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!).
SQL Plan Management
Transparent tablespace encryption
Flashback Data Archive
Private statistics
Oracle Database 11g
More
11g Features we did not cover, we
simply did not have time.
Recovery of old statistics
DDL Lock Timeout
alter session
set ddl_lock_timeout = 10;
Result
Cache
PL/SQL New Features
And more…