Transcript Corporate PPT Template - Universidad Nacional De Colombia

Carlos Eduardo Trivino
Oracle Certified Associate
Database Administrator
Where we come from
Object
Relational
Internet
Relational
Internet
Platform
Introducing
Oracle Database 10g:
A Revolution in Database
Technology
Who is #1 in databases?
Oracle: #1 Database Vendor in 2004
Sybase
2,9%
NCR
1,4%
Others
15,8%
Oracle
33,0%
Microsoft
15,3%
IBM
31,6%
Source: IDC, July 2004
(Includes License
& Maintenance)
Oracle Database 10g Goals
 Half the Cost
 Highest Quality of Service
–
–
Highest Availability, Reliability, Security
Highest Performance, Scalability
 Easiest to Manage
Problem: Islands of
Computation




Limited scalability, no resource sharing
Must be configured for peak loads
Single point of failure
Slow to adjust to business needs
Sales
Files
HR
Procurement
Service
Supply
Chain
Financials
Grid Computing Eliminates
Islands of Computation
Coordinated use of many small servers
acting as one large computer.
IT Problem
Separate servers
High h/w & s/w costs
Configured for peak
Single point of failure
Hard to change
Difficult to manage
Grid Solution
Shared servers
Low cost components
Capacity on demand
Fault tolerant
Flexible
Unified management
Introducing Oracle 10g
Complete, integrated grid infrastructure
Oracle Grid Computing
Storage Grid
Database Grid
Application Server
Grid
Grid
Control




Low cost
Highest quality of service
Rapidly adjusts to meet business needs
Unified management
Grid Computing Defined
Coordinated use of
many small servers acting
as one large computer.
Example: In December
 Order Entry maxes out processing orders
 Financials is idling below capacity
Order Entry
Financials
Example: In January
 Order Entry drops off from season high
 Financials maxes out on year end close
Order Entry
Financials
Grid Fault Tolerance
Database
Failover
App. Server
Detection
Total
Recovery Time
Oracle9i
10 - 60 secs
minutes
minutes
Oracle 10g
< 8 secs
< 4 secs
< 12 secs
Re-establish database connections faster
with Oracle 10g infrastructure.
10g Performance Metrics




PLSQL up to 2x
Table Scans up to 2x
SQL Profile Tuning huge
Floating point math up to
10x
 Bulk LOB Update up to
3x
 Import up to 15x
 Export up to 35%
 Incremental backup up 10x
 Parallel media recovery
2-4x
 Backup compression 17x
 Infiniband interconnect
½ latency, 2x block transfer
 TPC-C
–
–
Best absolute and best on 4way Intel
+28% on same hardware
Business Challenge –
How to Scale DBA’s to the
Grid ?
Installation
6%
Creation &
Configuration 12 %
Others
15%
Software
Maintenance 6%
Data
Loading 6%
Ongoing System
Management 55%
Source: IOUG 2001 DBA Survey
10g initialization parameters
 “Basic” parameters:
–
–
–
–
–
–
–
–
–
–
–
–
–
CLUSTER_DATABASE
COMPATIBLE
CONTROL_FILES
DB_BLOCK_SIZE
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_DOMAIN
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_NUMBER
JOB_QUEUE_PROCESSES
–
–
–
–
–
–
–
–
–
–
–
–
–
–
–
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
NLS_LANGUAGE, NLS_TERRITORY
OPEN_CURSORS
PGA_AGGREGATE_TARGET
PROCESSES
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
ROLLBACK_SEGMENTS
SESSIONS
SGA_TARGET
SHARED_SERVERS
STAR_TRANSFORMATION_ENABLED
UNDO_MANAGEMENT
UNDO_TABLESPACE
Flashback Versions Query
 view the contents of a table as it existed at some
point in the recent past(UNDO_RETENTION).
 The feature allows the DBA to see the value of a
column as of a specific time, as long as the
before-image copy of the block is available in the
undo segment
Rename Tablespace
 Useful in Transportable Tablespace scenarios
 ALTER TABLESPACE user RENAME to u1;
–
–
–
Can’t rename SYSTEM or SYSAUX
Tablespace and all datafiles must be online
Can also rename READ ONLY tablespaces
PROD
ARCHIVE
Tablespaces and Groups
 Huge tablespaces could be separated for
maintenance (backup&recovery) reasons
 Users still have single point for storing data
– CREATE TABLESPACE TS DATAFILE ‘xx’ SIZE
1M
TABLESPACE GROUP G1;
– ALTER TABLESPACE NEWTS TABLESPACE
GROUP G1;
– ALTER TABLESPACE NEWTS TABLESPACE
GROUP ‘’;
– CREATE TABLE T(…) TABLESPACE G1;
Bigfile Tablespaces
 Support for sizes up to 8 Exabytes!
–
–
–
8 000 000 Terabytes
Max 65535 files in database
SYSTEM & SYSAUX can’t be bigfile tablespaces
8 000 000 000 000 000 000 bytes

CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf‘
SIZE 50G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO;
Crossplatform Transportable TS
RMAN> CONVERT TABLESPACE sales_1,sales_2
2> TO PLATFORM ’Microsoft Windows NT’
3> FORMAT ’/temp/%U’;
...
Transporting Tablespaces Between Databases
input datafile fno=00004
name=/u01/oracle/oradata/salesdb/sales_101.dbf
converted datafile=/temp/data_D-10_I-3295731590_TSEXAMPLE_FNO-4_06ek24vl
channel ORA_DISK_1: datafile conversion complete,
elapsed time: 00:00:45
Crossplatform Transportable DB
Now you can transport an entire database across platforms that
share the same endianess (byte ordering) in the release 2.
Data Pump
 A server-managed data transportation tool
 Direct load/extract capabilities
 Very high performance/efficient with large data
Data
sets
Pump
Client
Data
 Replacement for exp/imp
Pump
–
Client
old exp/imp remain supported
Data
Warehouse
Data Pump
Data Pump
PROD
Data Pump





Commands expdp/impdp
Can use files or direct network transfer
Dynamic configuration, resumable operations
Client can detach and reconnect
Can be parallelized using PARALLEL
–
Even loads to/from external text files
 Monitored through DBA_DATAPUMP_JOBS
expdp hr/hr dumpfile=chap7:job_tabs.dmp nologfile=y
content=metadata_only tables=jobs,job_history
Database Encryption(new feature release 2)
•You can encrypt any or all the columns in a table.
•Since the data is stored encrypted, all downstream
components, such as backup and archived logs,
also have the encrypted format
How Transparent Data Encryption
works
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/orawall)))
alter system set encryption key
authenticated by "remnant";
alter system set encryption wallet open
authenticated by "remnant";
 create table accounts (
acc_no number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
SSN varchar2(9) ENCRYPT USING
'AES128') ;
 alter table accounts modify (ssn encrypt
using 'AES128');
Online Reorg of a Single
Partition

partition by range (trans_date) (
partition y03q1 values less than (to_date('04/01/2003','mm/dd/yyyy')),
partition y03q2 values less than (to_date('07/01/2003','mm/dd/yyyy')),
partition y03q3 values less than (to_date('10/01/2003','mm/dd/yyyy'))
)

Begin
dbms_redefinition.start_redef_table(
uname
=> 'ARUP',
orig_table => 'TRANS',
int_table => 'TRANS_TEMP',
col_mapping => NULL,
options_flag => dbms_redefinition.cons_use_rowid,
part_name => 'Y03Q2');
end;
Unlimited DBMS Output
 ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 2
 package dbms_output used to be 1 million
bytes(release 1)
 package dbms_output used to unlimited
bytes(release 2)
WRAP Package
 PL/SQL program units often contain very
sensitive and confidential information about
company procedures and trade secrets
begin
dbms_ddl.create_wrapped
('create or replace procedure p2 as begin
null; end;')
end;
Autotrace
 Autotrace is now using the DBMS_XPLAN
package to display the explain plans. This
gives us a much more detailed explain plan.
set autotrace traceonly explain;
Conditional Compilation
 Conditional compilation is the ability to make
the compiler effectively ignore code (or not)
create or replace procedure p as
begin
$IF $$debug_code $THEN
dbms_output.put_line( 'Our debug code' );
dbms_output.put_line( 'Would go here' );
$END
dbms_output.put_line( 'And our real code here' );
end;
alter procedure P compile 2 plsql_ccflags = 'debug_code:true' reuse settings;
Automatic Storage
Management (ASM)
 Eliminates need for
conventional file system and
volume manager
 Capacity on demand
–
Add/drop disks online
 Automatic I/O load balancing
–
–
Automatic Storage
Management
Stripes data across disks to
balance load
Best I/O throughput
 Automatic mirroring
 Easy
ASM – How it Works
 No volumes: just a pool of
storage
 Partitions total disk space
into uniform sized
megabyte units
Automatic Storage
Management
ASM – How it Works
 No volumes: just a pool of
storage
 Partitions total disk space
into uniform sized
megabyte units
 Efficient, online add/remove
of disk with automatic
rebalancing
Automatic Storage
Management
ASM – How it Works
 No volumes: just a pool of
storage
 Partitions total disk space
into uniform sized
megabyte units
 Efficient, online add/remove
of disk with automatic load
balancing
Automatic Storage
Management
Automatic Storage
Management Summary
 Low Cost
–
–
–
–




Eliminates need for volume manager and file systems
Works well with inexpensive, modular storage
Better storage utilization
Easy – up to 50% less DBA/Sys Admin work
Fault tolerant
Raw disk performance
Capacity on demand
Automatic I/O load balancing
Storage Grid - Highest Data
Protection for Lowest
Data Guard
Cost
Redo
Shipping
Flash Backup
Flashback
ASM Mirroring
Storage Failure
Protection
Flashback Logs
Human Error
Protection
On Disk
Backups
Corruption
Protection
Site Failure
Protection
Database Server Grid –
Real Application Clusters
 Runs Real Applications
 Proven
–
–
Over 500 production sites
Certified with leading apps
 Runs on standard,
low cost servers
 Fault tolerant
 Capacity on demand
 New integrated clusterware
makes it easy everywhere
ERP
CRM
DW
Database Server Grid –
Workload Management
 Push-button, online add/drop server to cluster
 Automatic Load Balancing
–
–
–
–
Based on “service” concept
Automatic routing of service connection requests to
appropriate server with lowest load
On server failure, automatic re-allocation of surviving
servers to services
Dynamic load balancing when service levels not met
(coming soon)
Oracle RAC 10g - Only
Solution for Enterprise Grids
 Mission Critical Quality of Service on
Industry Standard, Low Cost Servers
 Integrated clusterware makes RAC easy
for everyone
 Fault tolerant, scales all applications
 Capacity on demand
 Automatic load balancing
Oracle 10g Grid Management
One-half to one-tenth the cost
 Self-Managing
Database
 OEM Grid Control
–
–
Grid-wide
operations
Scale from one to
hundreds of
systems with little
incremental cost
ERP
EMAIL
DW
Oracle Database 10g
Self-managing Database

Built-in Intelligent Infrastructure
–
–

Automatic Database Diagnostic Monitor
–

Automatic performance diagnostic engine in the
database
Automatic Tuning Optimizer
–


Code instrumentation
Data warehouse to analyze performance of customer
workloads
SQL Profile to tune packaged applications
Automatic PGA and SGA Memory Management
Lots more …
Oracle 10g OEM Grid Control
 Manages the Oracle
Grid environment
 Scales to thousands
of servers
 Application Service
Level Management
 Configuration
Management
–
–
–
Provisioning
Cloning
Patch Management
ERP
EMAIL
DW
Business Intelligence on the Grid
 Lower Cost
–
–
Leverage 10g hardware, software, labor savings
Manage millions of terabytes
 Intelligence when you need it
–
–
Cross-platform transportable tablespaces
Asynchronous change data capture
 More value from your data
–
–
Mature OLAP, Data Mining
Improved SQL Analytics – SQL Model Clause
HTML DB –
Rapid Web Development Tool
 Builds database-driven Web applications
 For non-programmers
 App developers and app users just need a
web browser
 Addresses MS Access proliferation problem
–
Supports hosted development and deployment
environment
Easy Transition to the Grid
 Oracle 10g preserves
investment
–
–
–
Runs all existing apps
on clusters
Moves data to new
platforms as fast as
FTP
Low risk - Start small
and add capacity on
demand
ERP
EMAIL
DW
Oracle 10g Grid Computing
 Half the Cost
–
–
–
Low cost, modular
storage and servers
Complete,
integrated software
infrastructure
Half the labor cost
 Highest Quality of
Service
 Rapidly Adjust to
Business Needs
–
–
Capacity on
demand
Load balancing
 Easy to Manage
ERP
EMAIL
DW
Key Technology Innovations
 Automatic Storage
Management
 Flashback
Database/Table
 Fast Disk-based Backup
and Recovery
 RAC Workload
Management
 Rolling Upgrade
 Cross-platform
Transportable
Tablespaces
 Exabytes of data
 SQL Model Clause
 Self-managing
Database
 Grid Control
 HTML DB
 Integrated software
stack
Next Steps….
 Tuesday, 2pm, Larry Ellison, Keynote on 10g
 Wednesday, 9:45 Chuck Rozwat, Keynote on
10g
 Wednesday, 2:30 Ken Jacobs, Keynote on
Ease of Management
Key Technical Presentations











Self-managing DB, Monday, 1 pm, Rm 103
HTML DB, Monday, 1 pm, Rm 104
OEM Grid Control, Monday, 2:30 pm, Rm 103
RAC, Monday, 2:30 pm, Rm 104
SQL Model Clause, Monday, 4 pm, Rm 130
High Availability, Tuesday, 11 am, Rm 304
XML DB, Tuesday, 11 am, Rm 130
Grid, Tuesday, 3:30 pm, Rm 102
ASM, Wednesday, 11 am, Rm 103
Streams, Wednesday, 1 pm, Rm 103
OLAP, Thursday, 1 pm, Rm 131
Reminder –
please complete the OracleWorld
online session survey
Thank you.