Transcript Corporate PPT Template - Universidad Nacional De Colombia
Carlos Eduardo Trivino
Oracle Certified Associate Database Administrator
Where we come from
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% (Includes License & Maintenance)
Source: IDC, July 2004
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 Procurement Supply Chain HR Financials Files Service
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 Application Server Grid Storage Grid Database 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 Oracle9
i
Oracle 10g Database Failover 10 - 60 secs < 8 secs App. Server Detection minutes < 4 secs Total Recovery Time minutes < 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 4 way Intel +28% on same hardware
Business Challenge Grid ?
– How to Scale DBA’s to the Installation 6% Others 15% Creation & Configuration 12 % 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 50
G
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_TS EXAMPLE_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 sets Replacement for exp/imp – old exp/imp remain supported
Data Pump Client Data Pump Client Data Warehouse
Data Pump
PROD
Data Pump
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
– –
Stripes data across disks to balance load Best I/O throughput Automatic mirroring Easy Automatic Storage Management
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 Cost Data Guard Redo Shipping Flash Backup Site Failure Protection Flashback ASM Mirroring Flashback Logs Human Error Protection On Disk Backups Corruption Protection Storage 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
– –
Code instrumentation Data warehouse to analyze performance of customer workloads Automatic Database Diagnostic Monitor
–
Automatic performance diagnostic engine in the database Automatic Tuning Optimizer
–
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.