Transcript No Slide Title

How to Thrive as a DBA in an Oracle10g World
Speaker: George Trujillo, Trubix, Inc
Think Training,
Think Trubix
www.trubix.com
1-1
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Objectives
• Key Oracle10g features
• Address future Oracle database directions
• Discuss important skills sets for Oracle10g
1-2
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Trubix Inc.
• Incorporated in 1994.
– Largest content provider of Oracle related courses in
world. Over 110 related courses.
• Instructors are hand-selected from top consultants
in North America.
• Specializing in advanced Oracle, Linux and Java
solutions.
• George Trujillo - 17 years Oracle consulting
experience.
1-3
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Oracle10g Environments are More Complex
• Most Oracle9i DBAs are not prepared to support
Oracle10g environments:
–
–
–
–
–
–
–
1-4
Web services
Java
XML
Application servers
LDAP and Internet environments
RAC, Data Guard, Advanced Queuing, Security
ASM
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Oracle10g Key Areas
• Greatly simplified administration in day to day
tasks.
• Enhancements to Oracle9i features
• New tools and features
• Enhancements to performance tuning
• New backup and recovery features
• New storage management features
1-5
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Improved Administration Features
• Simplified initialization parameters
–
–
–
–
–
Basic and advanced parameters
Tracking database features usage
Improved tracing
New and improved advisories
Enhancements to utilities
• Server and threshold alerts
• The advanced parameters are available for more
advanced databases.
1-6
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
The OEM Central Console
• The Oracle 10g OEM Central Console is a Java based web
browser that supports the management of the Oracle
ecosystem.
• This Central Console can be used to manage all the Oracle
10g databases and Oracle 10g application servers.
• The Central Console can be used to manage, monitor and
tune the entire Oracle infrastructure.
1-7
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
The OEM Central Console
1-8
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
EM2GO
• EM2GO supports Oracle Database 10g
management with wireless PDAs.
– Supports SSL.
– Uses the Pocket PC Internet Browser
– Communicates between the Console and the
Management Service.
1-9
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Big Tablespaces
• Big tablespaces support extremely large
tablespaces (4GB blocks) for VLDB
environments.
– They are designed for environments that
contain Automatic Storage Management
(ASM), logical volume managers or RAID.
– An 8K block size big tablespace can support a
32 terabyte datafile. A 32K block big tablespace
can support a 128 terabyte datafile.
1-10
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Creating a Big Tablespace
• Bigfile tablespaces make sense in the context of an
enterprise disk volume management context. Thus,
the DBA must use a proper RAID configuration with
striping and mirroring for optimal performance.
• Example first portion of bigfile tablespace create
statement:
SQL > CREATE BIGFILE TABLESPACE big_data
DATAFILE '/u09/oradata/dev10g/bigdata01.dbf' SIZE 100G….
1-11
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Moving Tablespaces Cross Platform
• At version 10g the DBA now has the ability to
port transportable tablespaces across OS
platforms. This provides the DBA with great
flexibility when working with an integrated
environment.
• This view displays which OS environments
support this new feature.
SQL > COLUMN PLATFORM_NAME FORMAT A30
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
1-12
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
The SQL Access Advisor
• The SQL Access Advisor simplifies tuning SQL
through the EM 10g browser based console.
– The manual methods traditionally used by DBA’s are
largely eliminated with this tool which allows a DBA to
tune SQL running or drawn from the library cache or
SQL from a tuning set created by the DBA.
• The SQL Access Advisor recommends to the DBA
indexes, materialized views etc to actually
decrease what is known as the analyzed SQL
workload according to the SQL Access Advisor.
1-13
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
SQL Access Advisor
1-14
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
SQL Tuning Advisor
• The SQL Tuning Advisor tool is designed for
optimizing SQL. The SQL Tuning Advisor:
• Monitors inefficient SQL statements.
• Evaluates resources (CPU, I/O, temporary space)
consumed by these SQL statements.
• Allows a DBA to tune SQL within the browser
pages of the Oracle 10g database OEM console
rather than via the command line only with
utilities such as tkprof or SQL_TRACE.
1-15
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Workload Repository
• The Automatic Workload Repository
(AWR) collects data similar to
STATSPACK for self-tuning features.
• Self-tuning features that use AWR:
–
–
–
–
1-16
SQL Tuning Advisor
Automatic Database Diagnostic Monitor
Undo advisor
Segment Advisor
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Database Diagnostic Monitor
• The Automatic Database Diagnostic
Monitor (ADDM) monitors over 50 events.
• The ADDM facilitates automatic
configuration with:
– Automatic Storage Management (ASM)
– Automatic Memory Management (AMM)
1-17
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
ADDM Looks For
•
•
•
•
•
•
•
•
1-18
Memory structure utilization
CPU bottlenecks
Utilization (checkpoints, archiving, log files, …)
Concurrency
Top end SQL
I/O issues
High end PL/SQL
Hot segments
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Memory Management
• Automatic Memory Management supports
the automatic tuning of the key memory
areas of the SGA.
1-19
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Maintenance Tasks (AMT)
• The Automatic Maintenance Tasks allow
DBAs to automate regular tasks with the
Oracle Scheduler.
1-20
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Tuning with Advisory Central
1-21
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Scheduling Through EM
1-22
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Data Pump
• Data pump is a new utility for exporting
(expdp) and importing (impdp) data.
– The new binaries are expdp and impdp.
– The utilities can be accessed using the 10g EM
console web browser pages or the traditional
CLI or command line interface.
1-23
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Advantages of Data Pump
•
•
•
•
Supports restarting data pump jobs.
Sorts exporting/importing over the network.
Estimates space required for export.
Supports filters with INCLUDE and
EXCLUDE.
• Supports monitoring of current jobs.
• Allows remapping of tablespaces.
1-24
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Loading and Unloading Data
• An external table can load data into a data file as
part of creating an external table with a AS
SELECT statement.
CREATE TABLE my_ext_tab
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
LOCATION ('my_d.dmp')
)
AS SELECT * FROM my_tab;
1-25
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Automatic Storage Management
• Automatic Storage Management (ASM) allows
disks to be managed as a logical volume within a
disk group.
• I/O is balanced to all disks within the disk group.
• An ASM instance manages the disk group.
• ASM contains two background processes (ARB0,
ARB1).
• The database instance uses an ASMB process that
communicates with the ASM. RBAL does a
global open on the ASM disks.
1-26
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Advantages of ASM
• Improved performance by balanced I/O across
disks in a disk group.
• Simplifies data file and disks with disk groups.
• Can eliminate the need for a volume manager.
• Supports mirroring
• Disks can be added and removed while the
database is running.
• Eliminates manual disk tuning.
1-27
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Flashback technology
•
•
•
•
•
•
•
Flashback database
Flashback table
Flashback version query
Flashback drop
Flashback transaction query
Flashback row history
Flashback transaction history
1-28
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Flashback Table
• Flashback table restores a table or tables to
a point in time without using backups.
• Indexes, triggers and constraints are
automatically maintained.
1-29
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Recycle Bin
• The recycle bin is a repository for dropped
tables and the associated objects.
Dependent objects include:
–
–
–
–
1-30
Indexes
Triggers
LOBS and LOB index segments
Nested tables
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Database Infrastructure Administrator
• DBAs are needing to support new environments
–
–
–
–
1-31
Web services
Application servers
Java and XML
Multi-tiered architectures
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Oracle Enterprise Manager
•
•
•
•
•
OEM is for wimps
Central console management
Support for advanced features
Support for application server
Tuning and monitoring
1-32
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Oracle10g Skills
• Upcoming areas that Oracle DBAs with the
necessary skills are going to be in demand:
–
–
–
–
–
–
1-33
64-bit architecture and Linux
Application servers
Multi-tiered architectures (Web services, XML, Java)
Advanced configurations (RAC, GRID, Data Guard, AQ)
Oracle10g storage management and ASM
Oracle10g Administration
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Visit our web site at:
www.trubix.com
1-34
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com
Thank You for Attending!
George J. Trujillo, Jr.
[email protected]
Think Training,
Think Trubix
www.trubix.com
1-35
Copyright  Trubix™ Inc.,2003 - 2004 All rights reserved.
www.trubix.com