Top Reasons To Move to Oracle 9i

1. Session id: Top Reasons To Move to Oracle 9i An Oracle DBA’s Perspective Steve Lemme VP Product Management Computer Associates Author “Implementing and Managing Oracle Databases” Steve.lemme@ca.com 2002

2. Objectives • 500 new features, lets cut to the chase • 9i features a DBA should checkout • How those features add business value • Why those drive a 9i upgrade • The Business Return On Upgrade (ROU)

3. With All That's NewWhat Do You Do? • Wait? • Install them all? • Space? • Try them all? • Time? • Upgrade to 9i and ignore them? • Get the benefit - learn, test, plan • Upgrade!

4. #10 Oracle 9i RAC • What Stands Out? • Provides High Availability • Enables application failover • Enables better scalability • Multiple instances can share a single physical DB • Instances with common data & control files, yet individual log files and rollback segments • Global cache management synchronization technology (Cache Fusion) • Load balancing

5. #10 9i RAC (ROU) • Why Does This Help? • Provides rapid and automatic recovery from failures (node failure, instance crash, …) • Provides increased scalability • Masks failures to end users • Reduces or eliminates the need for custom application failover coding/scripting • Better usage of subsystem resources • Assists with database consolidation • Automation of complex cache management with Cache Fusion

6. #10 Summary • Primary Consideration • Hardware • More physical items to manage • Bottom Line • Need HA • Running OPS • Consider 9i RAC

7. #9 9i Oracle Managed Files • What Stands Out? • Automatically creates and deletes files for tablespaces, temp files, redo logs, control files • Create DB objects without specifying underlying operating system files • If a group is dropped, corresponding Oracle-managed files are deleted at operating system level • Default file system directory can be changed dynamically • OMF data file can be added to an existing tablespace • OMF and non-OMF files can coexist • Logical volume manager RAID support, large extensible file system support

8. #9 9i OMF (ROU) • Why Does This Help? • Eliminates tracking of what DB files match up to what system files • Reduces time and risks associated with data file mix-ups errors • Like accidental system file deletion • Time to figure how to create and where • Reduces space wasted by obsolete files • Standardized naming of database files • Eliminates put operating system–specific file names in SQL scripts, increasing portability

9. #9 Summary • Primary Considerations • Stripped file systems • Extensible logical volumes • File system that provides large, extensible files • Still need to manage subsystem space • Bottom Line • Have Junior administrators • DBAs in separate group from Sys Admins • Lots of databases files • Consider 9i OMF

10. #8 9i External Tables • What Stands Out? • External data as a virtual table • Queried and joined directly and in parallel • Doesn’t requiring it to be loaded in the database • Reading defined in SQL*Loader control file format • Access rights are controlled through SELECT TABLE and read/write directory privileges • SQL, PL/SQL and JAVA operations are permitted on the table • Create Views and Synonyms • SELECT, JOIN, SORT • Parallel Queryable

11. #8 9i External Tables (ROU) • Why Does This Help? • Single copy of the data is kept • Data files can stay where stored by applications • Saves storage • Reduces mix ups of which file to load is where • Eliminates SQL*Load time • File natively read by Oracle • No longer necessary to stage the data inside the database for comparison or transformation • Don’t have to wait for completion of long load • Reduces resources consumption • Helps reduce data scheduling complexity

12. #8 Summary • Primary Considerations • Read only • Cannot index • Bottom Line • When complete external source needs to be joined with existing DB objects and transformed • Have external large data sets queried infrequently • Consider 9i External Tables

13. #7 9i Automated Undo • What Stands Out? • Automated rollback undo and retention or manually • Transactions can span multiple undo segments • Defined by UNDO_MANAGEMENT parameter: • AUTO: The instance manages undo data automatically • MANUAL: DBA manages rollback segments manually • Undo data managed by single tablespace defined by • UNDO_TABLESPACE - Defaults to SYS_UNDO • UNDO_RETENTION - Seconds to try and keep “historic” information • Minimum of one UNDO tablespace • Default is SYSTEM tablespace and unlimited space • You can have multiple, but recommended that you have only one • UNDO tablespace must be locally managed • UNDO_SUPPRESS_ERRORS • Set to TRUE, application coded RBS-related commands succeed • Enables Query Flashback

14. #7 9i Automated Undo (ROU) • Why Does This Help? • Reduces manually rollback segment management • Saves time determining the correct number • Saves time managing them individually • Reduces risk of common mix-ups creating, dropping, or altering rollback segments • Enhances application performance • Reduces risk of stopped applications waiting for rollback • Reduces rollback contention of resources • UNDO_SUPPRESS_ERRORS eliminates need to wait until applications recode • Automatic UNDO speeds crash recovery

15. #7 Summary • Primary Considerations • Be aware of database corruption bug pre-9.0.1.1.0 patch set • Can’t use both methods in same instance; for migration it’s possible: • Create undo tablespaces in a database that is using rollback segments • Drop rollback segments in a database that is using undo tablespaces • UNDO_MANAGEMENT - AUTO • Space - need sufficiently large UNDO tablespace • Each Tx assigned its own undo segment until space runs out, then they share • Space quota is by consumer group, not by user • One user may prevent all other users from executing DML • Bottom Line • Considering hardware, storage upgrades to deal with rollback issues • Current recovery time not meeting service levels • Spending lots of time on Rollbacks? 9i Automated Undo

16. #6 9i Temporary Tablespace • What Stands Out? • Temporary is for non-permanent database objects • Objects persist for duration of the session • User’s temporary tablespace either SYSTEM or a tablespace type TEMPORARY • Used/enforced during “create user” • Can specify a db-wide default temporary tablespace • Cannot be specified as SYSTEM • Will be locally managed • Created with ALTER DATABASE or the CREATE DATABASE command • Can have multiple TEMPORARY tablespaces

17. #6 9i Temporary Tablespace (ROU) • Why Does This Help? • Don’t have to: • Remember to create user with temporary tablespace • Find user having SYS tablespace as their temp tablespace • Space management (extent allocation and deallocation) is locally managed reducing complexity • Helps optimize sort performance • Processes performing sorts reuse existing sort extents of the sort segment, rather than allocating a segment (and potentially many extents) for each sort • Sort segment created for each instance is reused (only dropped if tablespace is dropped) • Temporary tablespaces can be striped for better I/O

18. #6 Summary • Primary Considerations • Temporary tablespaces cannot contain permanent objects such as tables • If tablespace was not created with a standard block size, you cannot change it from permanent to temporary • You cannot take a temporary tablespace offline • Bottom Line • Lots of moving of data between OLTP database and data warehouses • Eliminates the annoying SYSTEM tablespace usage for temporary space • Consider temporary tablespace

19. #5 9i Multiple Block Size • What Stands Out? • Non-standard block size • Up to five non-standard block sizes • Power-of-two value between 2KB and 32KB • Can add buffers for new block size dynamically • ALTER SYSTEM SET DB_nK_CACHE_SIZE • Index Organized Table overflow, out-of-line large object (LOB) segments can be stored in a tablespace with a block size different than that of the base table • Standard block size • Defined at creation using DB_BLOCK_SIZE • Can’t change without recreating DB • For SYSTEM and TEMPORARY tablespaces • The default for the other tablespaces

20. #5 9i Multiple Block Size (ROU) • Why Does This Help? • Enables transporting of tablespaces between databases with different block sizes • Enable better performance of I/O • Small blocks enable faster active rows in buffer cache • Large blocks on indexes reduce B*-tree depth • More flexibility to match and optimize objects in tablespaces of appropriate block size • Add buffers dynamically for new block size doesn’t require restarting database • Useful in hybrid databases where DSS transactions benefit from large block sizes, while OLTP operations suited to smaller block sizes

21. #5 Summary • Primary Considerations • Partitions of partitioned objects must reside in tablespaces of the same block size • Temporary tablespaces, permanent tablespaces used as default temporary tablespaces, must be of standard block size • Bottom Line • Considering hardware, storage upgrades for better database performance • Lots of time spent moving objects to get better I/O • Moving lots of data between OLTP database and data warehouses – 9i Multiple Block Sizes

22. #4 9i Resource Manager • What Stands Out? • Automatically switches a session's group based on resource plan directive • Estimated execution time of an operation, proactively • Switches long running operation from high priority to a low priority • Automatic policy-based detection of long and short running SQL • Limit resource use by user group, time of day • Control active sessions and session execution time • Once limit is exceeded, subsequent sessions will be queued • “Time-out” to prevent a session from waiting in the queue indefinitely • SWITCH_GROUP, SWITCH_TIME, SWITCH_ESTIMATE • Define period operation can run before switched to another group • Plan directive UNDO_POOL • Limits the amount of undo space that can be used • When exceeded, prevents DML (INSERT, UPDATE, DELETE) • SELECT statements are still allowed

23. #4 9i Resource Manager (ROU) • Why Does This Help? • Provides fine grained control of resources consumed • Automatically manages resource consumption • Eliminates issues with runaway or blocking sessions • Provides predictable performance in mixed workload environments • Enables meeting service levels by blocking execution of long running queries during the peak usage period • Business-critical processes assigned more CPU • Dynamically allocates runtime memory based on current available memory and each query’s requirements • Degree of parallelism is dynamically chosen based on available resources and each query’s requirements

24. #4 Summary • Primary Considerations • Takes some time to setup • Need to have configuration management to know who has set up what • Bottom Line • Considering hardware, storage upgrades to deal with performance issues • Need to control problem apps/users • Need to meet service level objectives • Consider 9i Resource Manager

25. #3 9i Data Guard • What Stands Out? • The new Standby Database, ready for prime time • No change to the application needed • Can switchover to a standby without resetlogs • Real-time copy of data to protect againstdata loss • Propagate changes made to primary database, not every I/O • Failover without other hardware or software mirroring • Failover to a local or remote site • Local and remote standbys • Flexible data protection modes • Application of changes from the primary can be delayed • Can be used to offload backups • Customers can optimize logical standby for queries by adding new indexes, views etc

26. #3 9i Data Guard (ROU) • Why Does This Help? • Protect against user errors, data corruption, disasters • Leverage standby for backups, maintenance, and reporting • Reduces downtime for planned outages such as operating system or hardware upgrades • Delay enables detection of user errors and prevents their propagation to standby • Protection from data corruption • File system corruptions on the primary do not propagate to standby because the received redo is validated before it is applied to the standby

27. #3 Summary • Primary Considerations • Extra hardware & software • Network links • Bottom Line • Offload backups and reporting • Protect against data loss including human error, corruption and natural disasters • Consider 9i Data Guard

28. #2 9i Flashback Query • What Stands Out? • Go back to: • Version of the database at a specified clock time • Or a specified system change number (SCN) • Enabled during session, terminates when session ends • To perform over a 24-hour period, undo_retention set to 24 hours • FAST_START_MTTR_TARGET • # of seconds database takes to perform crash recovery of a single instance • Dynamic parameter • Query speed depends on amount of data and # of changes to make • Need to set up • UNDO_RETENTION parameter • Default 30 secs, or ‘if not yet committed’ • Sufficiently large undo tablespace • UNDO_MANAGEMENT must be auto • Need execute privilege on DBMS_FLASHBACK to use the feature

29. #2 9i Oracle Flashback (ROU) • Why Does This Help? • Makes recovery easier and faster • Faster recovery gets business up sooner • Enables fixing ‘human error’ Undo OOPS • Accidentally delete rows from a table; enables to then recover the deleted rows • Estimated time provides indication of expected time of recovery • View an old image of data, without disrupting database services • Flashback is faster and easier to set-up and supports more data types than Log Miner

30. #2 Summary • Primary Considerations • Watch balance between performance and recoverability • Retention is reliant upon amount of space available • Cannot perform FB query over a database link • Does not apply to PL/SQL packages, procedures, or functions • Can’t be used while a transaction is active • In Flashback mode, user cannot perform DML or DDL operations • Bottom Line • Require application self-service error correction capabilities • Need to quickly recover accidentally deleted data • Consider 9i Flashback

31. #1 9i Fine-Grained Audit • What Stands Out? • Create explicit auditing conditions against data • Capture the SQL statement, not the data • 250 configurable audit options • Set by statement, by system privilege, by object or by user • Extensible auditing through database triggers • Attach audit policy to table or view with WHERE condition on SELECT statements • Whenever a column is selected in any part of a DML statement • AUDIT COLUMN reduces false audit conditions • Selective data encryption via PL/SQL or Java (JCA/JCE) • Can invoke a procedure as part of the audit • Attach audit policy to table or view (DBMS_FGA) • Specify audit condition using SQL triggering audit condition (predicate) • Event handler can alert administrator to triggering condition

32. #1 9i Fine-Grained Audit (ROU) • Why Does This Help? • Enable application driven security policies, instead on embedding code in the applications • Enables extensible, more granular audit records • Ensuring users have the minimum set of privileges required to perform their job functions • Enables holding users accountable for their actions • Helps identify misuse • Reduces the # of records written to audit logs • Enables intrusion detection through event handler • Increase security as development groups do not have to share specific application security info

33. #1 Summary • Primary Considerations • One audit column only • Does not automatically capture records • Additional administration • Bottom Line • Audit records are essential in proving the violation of access rights • Need data intrusion detection • Need security of data • Consider 9i Fine Grained Auditing

34. Business Is Ever Changing Status quo Deploy new Hire additional staff Enforce architectural guidelines Leverage technology to manage complexity Less functionality, costs Risk, education, costs Increased costs Better infrastructure management, costs Time, resources, risk reduction in costs When facing the new challenges, we must either: Action Result

35. Balancing the RequirementsWith Available Resources Availability Manageability Integration Scalability Flexibility Extensibility Budget Performance People Technology It requires people or technology

36. In Conclusion • Today's DB servers are complex and powerful engines that drive Internet-enabled applications • New Oracle 9i features can help reduce existing work • The DBA role has expanded to include functions such as App Server Administration, RAC etc. • DBA’s still need to learn, and manage them • These “TOP TEN” features are ten valid business reasons why you should consider a 9i upgrade

37. CA Manages eBusiness Today • “The Software That Manages eBusiness” • World's Leading Business Software Company • 17,000+ Employees Worldwide • Full Support, Training, and Services • 100+ Partners Worldwide including Oracle • One of Oracle largest Global Partners with over 30 Management Solutions for Oracle • Local offices • Questions?