How to Thrive as a DBA in an Oracle10g World Speaker: George Trujillo, Trubix, Inc

1. How to Thrive as a DBA in an Oracle10g World Speaker: George Trujillo, Trubix, Inc Think Training, Think Trubix www.trubix.com

2. Objectives • Key Oracle10g features • Address future Oracle database directions • Discuss important skills sets for Oracle10g

3. 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.

4. Oracle10g Environments are More Complex • Most Oracle9i DBAs are not prepared to support Oracle10g environments: • Web services • Java • XML • Application servers • LDAP and Internet environments • RAC, Data Guard, Advanced Queuing, Security • ASM

5. 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

6. 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.

7. 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.

8. The OEM Central Console

9. 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.

10. 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.

11. 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….

12. 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;

13. 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.

14. SQL Access Advisor

15. 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.

16. Automatic Workload Repository • The Automatic Workload Repository (AWR) collects data similar to STATSPACK for self-tuning features. • Self-tuning features that use AWR: • SQL Tuning Advisor • Automatic Database Diagnostic Monitor • Undo advisor • Segment Advisor

17. 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)

18. ADDM Looks For • Memory structure utilization • CPU bottlenecks • Utilization (checkpoints, archiving, log files, …) • Concurrency • Top end SQL • I/O issues • High end PL/SQL • Hot segments

19. Automatic Memory Management • Automatic Memory Management supports the automatic tuning of the key memory areas of the SGA.

20. Automatic Maintenance Tasks (AMT) • The Automatic Maintenance Tasks allow DBAs to automate regular tasks with the Oracle Scheduler.

21. Automatic Tuning with Advisory Central

22. Scheduling Through EM

23. 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.

24. 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.

25. 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;

26. 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.

27. 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.

28. Flashback technology • Flashback database • Flashback table • Flashback version query • Flashback drop • Flashback transaction query • Flashback row history • Flashback transaction history

29. Flashback Table • Flashback table restores a table or tables to a point in time without using backups. • Indexes, triggers and constraints are automatically maintained.

30. Recycle Bin • The recycle bin is a repository for dropped tables and the associated objects. Dependent objects include: • Indexes • Triggers • LOBS and LOB index segments • Nested tables

31. Database Infrastructure Administrator • DBAs are needing to support new environments • Web services • Application servers • Java and XML • Multi-tiered architectures

32. Oracle Enterprise Manager • OEM is for wimps • Central console management • Support for advanced features • Support for application server • Tuning and monitoring

33. Oracle10g Skills • Upcoming areas that Oracle DBAs with the necessary skills are going to be in demand: • 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

34. Visit our web site at:www.trubix.com

35. Thank You for Attending! George J. Trujillo, Jr. georget@trubix.com Think Training, Think Trubix www.trubix.com