Anticipating tomorrows technologies     Oracle Database 10g New Features! This chapter first explains how to upgrade to Oracle Database 10 g and then describes the various Oracle Database 10 g features that are designed to make managing the server easier. This preview is based on the current beta at the time of writing. The final version of the book will cover many more exciting new manageability features from the final release of the software. The following are the specific topics that are covered: Upgrading to Oracle Database 10 g Using new statistics-collection features Flushing the database buffer cache Using the Database Resource Manager new features Firing up the new job scheduler (known as The Scheduler) Learning all about user-configurable default tablespaces Using tablespace groups and multiple default temporary tablespaces Renaming tablespaces Dropping databases Taking advantage of new LOB storage limitations Using the SYSAUX tablespace Using Automated Storage Management (ASM) Upgrading to Oracle Database 10g Oracle Database 10 gprovides a fairly easy upgrade path for users of older Oracle versions. The following versions can directly be upgraded to Oracle Database 10 g: Oracle Database 8.0.6 Oracle Database 8.1.7 Oracle Database 9.0.1 Oracle Database 9.2 If your database version is not in the preceding list, then you must first upgrade to one of these versions, after which you can upgrade to Oracle Database 10 g. Upgrading the Database After you are at a supported upgrade level, you can upgrade to Oracle Database 10 g by using any one of the following four upgrade options: Use the Oracle Database Upgrade Assistant (DBUA). Perform a manual upgrade. Use exp/imp to copy the data in your database to a new 10 g database. Use the SQL*Plus copy command or the create table as select command to copy the data from your current database to your new 10 g database. NOTE Always back up your database before you start your upgrade! The DBUA The DBUA is a GUI that is designed for upgrading your Oracle database to Oracle Database 10 g. You will have the option of starting the DBUA from the Oracle Universal Installer (OUI) when installing Oracle Database 10 g. DBUA guides you through the upgrade of your Oracle database. You can also start the DBUA at any time in stand-alone fashion (from the command line, just enter dbua) to upgrade your database. From Windows, you can also start the DBUA from the Start menu (either from the Oracle folder or use start | run and enter dbua). One nice feature of the DBUA is that it will offer to back up your database for you. This feature does have some limited functionality, because backups to removable media are not supported. CAUTION Oracle Database 10g only supports a direct downgrade back to Oracle release 9.2.0.3 or later. You can use imp/exp (Oracle’s import/export utilities), however, to move the migrated database data to other versions of Oracle. I strongly advise that you test this method of downgrading on a non-production server first, if you plan to use it. Performing Manual Upgrades Manual upgrades allow you to use a series of scripts and utilities to upgrade your database. The summary steps of manual upgrades include: Develop a test plan to run after your upgrade. Back up your database. Run the Upgrade Information Tool (UIT), which is a SQL script, utlu10li.sql, located in the directory $ORACLE_HOME/rdbms/admin. This script analyzes your database before you upgrade it and alerts you to any problems that might endanger the successful upgrade of your database. Upgrade the database. Follow the Oracle upgrade instructions for your specific version and operating system. This step includes the creation of the new SYSAUX tablespace, which is new in Oracle Database 10 g. (This tablespace is described in detail later, in the section “The SYSAUX Tablespace.”) Check the component registry (DBA_REGISTRY) to make sure your upgrade was successful. Back up your new Oracle Database 10 gdatabase. Run your test plan and validate your upgrade. The Compatible Parameter Once you have upgraded to Oracle Database 10 g, the compatible parameter can be set no lower than 9.2.0. Thus, if you are upgrading from 8.0.6, you need to set compatible to 9.2.0 before you can open your database under Oracle Database 10 g. The Oracle Upgrade manual (Oracle10 gUpgrade Guide) provides detailed instructions on setting the compatible parameter. Once you are satisfied that the database can operate under Oracle Database 10 g, you can set the compatible parameter to 10.0. Note that, once you set the compatible parameter to 10.0, you cannot set it back. This is different than in previous versions of Oracle. Also note that the command alter database reset compatibility is now obsolete. There are a number of other possible upgrade issues that you will need to deal with depending on the database features that you are using. I strongly suggest that you carefully review the Oracle Upgrade documentation, and that you test your Oracle Database 10 g upgrades several times before doing one for real in production. One final upgrade thought. I suggest that you do not use any of the new Oracle Database 10 g features in a production environment until you have tested the feature thoroughly. While Oracle does its best to regression test new features, there are always a few kinks to be worked out in the beginning. If you find a new feature irresistible (and after you read this book, I hope you do!), then by all means try it out. Test it over and over to make sure it works the way it’s intended, and that it doesn’t have some nasty impacts, like causing performance problems or causing your database to crash. Also, check Oracle MetaLink, and even open an Oracle iTAR, before you use a new feature that will be a prominent part of your design. Statistics Collection Oracle Database 10 g offers some new features to help you collect database statistics. These new features include collection of data dictionary statistics, new behaviors associated with the dbms_stats package, and new features related to monitoring tables in the database. Collecting Data Dictionary Statistics The Rule Based Optimizer (RBO) is desupported with Oracle Database 10 g. It’s still there in Oracle Database 10 g, but Oracle is moving away from it quickly and you will find no bug fixes associated with it in future versions of the database. With desupport of the RBO, it becomes even more important to address the question of collection of database statistics. Oracle Database 10 g includes new statistics-gathering features. This includes the ability to collect data dictionary statistics, which is now recommended as a best practice by Oracle. Also, Oracle Database 10 gincludes new features that enhance the generation of object level statistics within the database. Let’s look at these next. Data Dictionary Statistics Collection Oracle Database 10 g is a big departure from previous releases of Oracle insofar as Oracle recommends that you analyze the data dictionary. You can collect these statistics by using either the dbms_stats.gather_schema_stats or dbms_stats.gather_ database_stats Oracle-supplied procedures, as shown here: Exec dbms_stats.gather_schema_stats(’SYS’) The gather_schema_stats and gather_database_stats procedures are not new in Oracle Database 10 g, but using them to collect data dictionary statistics is new, as are some new parameters that are available with these procedures. Oracle Database 10 g also offers two new procedure in the dbms_stats Oracle-supplied package. First, the dbms_stats.gather_dictionary_stats procedure facilitates analysis of the data dictionary. Second the dbms_stats.delete_dictionary_ stats procedure allows you to remove data dictionary stats. Here is an example of the use of the dbms_stats.gather_dictionary_stats procedure: exec dbms_stats.gather_dictionary_stats; This example gathers statistics from the SYS and SYSTEM schemas as well as any other schemas that are related to RDBMS components (e.g., OUTLN or DBSNMP). From a security perspective, any user with SYSDBA privileges can analyze the data dictionary. However, non-SYSDBA user accounts must be granted the analyze any dictionary system privilege to be able to analyze the data dictionary. Gathering Fixed Table Statistics A new parameter in Oracle Database 10 g to the dbms_stats.gather_database_stats and dbms_stats.gather_ database_stats supplied procedures is gather_fixed. This parameter is set to false by default, which disallows statistics collection for fixed data dictionary tables (e.g., x$ tables). Oracle suggests that you analyze fixed tables only once during a typical system workload. You should do this as soon as possible after your upgrade to Oracle Database 10 g, but again it should be under a normal workload. Here is an example of the use of the gather_fixed argument within the bms_stats.gather_schema_stats procedure: Exec dbms_stats.gather_schema_stats(’SYS’,gather_fixed=>TRUE) Yet another new procedure, dbms_stats.gather_fixed_objects_stats, has been provided in Oracle Database 10 gto collect object statistics on fixed objects. It also has a brother, delete_fixed_objects_stats, which will remove the object statistics. Second cousins and new Oracle Database 10 gprovided procedures include dbms_stats.export_ fixed_objects_stats and dbms_stats.import_fixed_ objects_stats. These allow you to export and import statistics to user-defined statistics tables, just as you could with normal table statistics previously. This allows your data dictionary fixed statistics to be exported out of and imported into other databases as required. One other note: the dbms_stats Oracle-supplied package also supports analyzing specific data dictionary tables. Database Resource Manager New Features The Database Resource Manager in Oracle Database 10 g offers a few new features that you need to be aware of: The ability to revert to the original consumer group at the end of an operation that caused a change of consumer groups The ability to set idle timeout values for consumer groups The ability to create mappings for the automatic assignment of sessions to specific consumer groups Each of these topics is discussed, in turn, in more detail in the following sections. Reverting Back to the Original Consumer Group Prior to Oracle Database 10 g, if a SQL call caused a session to be put into a different consumer group (for example, because a long-running query exceeded a SWITCH_TIME directive value in the consumer group), then that session would remain assigned to the new resource group until it was ended. Oracle Database 10 gallows you to use the new SWITCH_BACK_AT_CALL_END directive to indicate that the session should be reverted back to the original consumer group once the call that caused it to switch consumer groups (or the top call) is complete. This is very useful for n-tier applications that create a pool of sessions in the database for clients to share. Previously, after the consumer group had been changed, all subsequent connections would be penalized based on the settings of the consumer group resource plan. The new SWITCH_BACK_AT_CALL_END directive allows the session to be reset, thus eliminating the impact to future sessions. Here is an example of the use of this new feature: EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'main_plan', GROUP_OR_SUBPLAN => 'goonline', COMMENT => 'Online sessions', CPU_P1 => 80, SWITCH_GROUP => 'ad-hoc', SWITCH_TIME => 3,SWITCH_ESTIMATE => TRUE, SWITCH_BACK_AT_CALL_END=>TRUE); In this case, I have created a plan directive that is a part of an overall plan called MAIN_PLAN. This particular plan directive is designed to limit the impact of online ad-hoc users (or perhaps applications that are throwing out a great deal of dynamic SQL that’s hard to tune) if they issue queries that take a long time (in this example, 3 seconds). This directive causes a switch to a consumer group called ad-hoc, which would likely further limit CPU and might also provide for an overall run-time limit on executions in this particular plan/resource group. Since I have included the SWITCH_BACK_AT_ CALL_END directive in this plan directive, the consumer group will revert back to the original plan after the completion of the long-running operation. Scheduler Changes Oracle Database 10 g offers a brand new job-scheduling facility, known as The Scheduler. The Scheduler is controlled via the new Oracle Database 10 gsupplied package dbms_scheduler. This package replaces the dbms_job package that has been around for some time. Overview of The Scheduler The new scheduler in Oracle Database 10 g offers much added functionality over the dbms_job package. The Scheduler enables you to execute a variety of stored code (such as PL/SQL), a native binary executable, and shell scripts. The object that is being run by The Scheduler is known as the program.The program is more than just the name; it includes related metadata about the program, such as the arguments to be passed to it and the type of program that is being run. Different users can use a program at different times, eliminating the need to have to redefine the program every time you wish to schedule a job. Programs can be stored in program libraries,which allows for easy reuse of program code by other users. Each program, when scheduled, is assigned to a job.A job can also just contain an anonymous PL/SQL block instead of a program. The job is a combination of the program (or anonymous PL/SQL block) and the schedule associated with the program, which defines when the job is to run. Also associated with the job is other metadata related to the job, such as the job class and the window or window group. The job classis a category of jobs that share various characteristics, such as resource consumer group assignments and assignments to a common, specific, service name. The job class is related to the job window. The job window, or window group, essentially allows the job to take advantage of specific resource plans. For example, if the schedule for a job is for it to run every hour, the job window will allow it to run under one resource group in the morning and a different resource group in the evening. That way, you can control the resources the job can consume at different times throughout the day. Oracle provides two different interfaces into The Scheduler. The first is the dbms_scheduler package and the second is through the Oracle Enterprise Manager (OEM). User-Configurable Default Tablespaces Oracle offers user-configurable default tablespaces in Oracle Database 10 g. Once you configure a default user tablespace, all new users will be assigned to that tablespace rather than the SYSTEM tablespace. At the time this was written, this feature was not available to test, but I thought you would like to know it’s coming. Tablespace Groups and Multiple Default Temporary Tablespaces Oracle Database 10 g now allows you to define tablespace groups, which are logical groupings of tablespaces. This further allows you to assign temporary tablespaces to those groups, and then assign this tablespace group as the default temporary tablespace for the database. In essence, tablespace groups allow you to combine temporary tablespaces into one tablespace pool that is available for use to the database. Assigning Temporary Tablespaces to Tablespace Groups You can assign a temporary tablespace to a tablespace group in one of two ways. First, you can assign it to a tablespace group when you create the tablespace via the create tablespace command. Second, you can add a tablespace to a tablespace group via the alter tablespace command. An example of each of these operations is listed next (note that OMF is configured in this example): Create temporary tablespace temp_tbs_01 tablespace group tbs_group_01; alter tablespace temp_tbs_01 tablespace group tbs_group_02; There is no limit to the number of tablespaces that can be assigned to a tablespace group. The tablespace group shares the same namespace as normal tablespaces, so tablespace names and tablespace group names are mutually exclusive. You can also remove a tablespace from a group by using the alter tablespace command and using empty quotes as an argument to the tablespace group parameter, as shown in this example: Alter tablespace temp3 tablespace group ’’; Defining a Tablespace Group as the Default Temporary Tablespace After you have created the tablespace group and assigned a set of tablespaces to that group, you can assign that group of temporary tablespaces (or that tablespace group) as the default temporary tablespace for the system, or as a temporary tablespace group for specific users. You can do this in the create database statement when you create the database, or you can use the alter database statement to modify the temporary tablespace settings. Using either statement, you simply define the tablespace group as the default tablespace, as shown in this example: Alter database default temporary tablespace tbs_group_01; This has the effect of assigning multiple tablespaces as the default temporary tablespace. Once you have assigned a tablespace group as the default temporary tablespace group, you cannot drop any tablespace in that group. So, now you can define more than a single tablespace as the database default temporary tablespace; as a result, larger SQL operations can use more than one tablespace for sort operations, thereby reducing the risk of running out of space. This also provides more tablespace space, and potentially better I/O distribution for sort operations and parallel slave operations that use temporary tablespaces. If a tablespace group is defined as the default temporary tablespace, then no tablespaces in that group can be dropped until that assignment has been changed. You can assign a user to a tablespace group that might not be the default tablespace group either in the create user or alter user statements, as shown in these examples that assign the TBS_GROUP_01 tablespace to the user NO_PS: Create user no_ps identified by gonesville default tablespace dflt_ts temporary tablespace tbs_group_01; alter user no_ps temporary tablespace tbs_group_02; Dropping Databases The drop database command can be used to drop your database. Oracle will drop the database, deleting all control files and all datafiles listed in the control file. If you are using a SPFILE, then Oracle will remove it as well. Only a user with SYSDBA privileges can issue the statement and the database must be mounted (not open) in exclusive and restricted mode. Here is an example of the use of the drop database command: Drop database; Automated Storage Management Oracle Database 10 g introduces Automated Storage Management (ASM), a service that provides management of disk drives. ASM can be used on a variety of configurations, including Oracle9 iRAC installations. ASM is an alternative to the use of raw or cooked file systems. ASM offers a number of features, including: Simplified daily administration The performance of raw disk I/O for all ASM files Compatibility with any type of disk configuration, be it JBOD or complex SAN Use of a specific file-naming convention to name files, enforcing an enterprise-wide file-naming convention Prevention of the accidental deletion of files, since there is no file system interface and ASM is solely responsible for file management Load balancing of data across all ASM managed disk drives, which helps improve performance by removing disk hot spots Dynamic load balancing of disks as usage patterns change and when additional disks are added or removed Ability to mirror data on different disks to provide fault tolerance Support of vendor-supplied storage offerings and features Enhanced scalability over other disk-management techniques ASM can work in concert with existing databases that use raw or cooked file systems. You can choose to leave existing file systems in place or move the database datafiles to ASM disks. Additionally, new database datafiles can be placed in either ASM disks or on the preexisting file systems. Databases can conceivably contain a mixture of file types, including raw, cooked, OMF, and ASM (though the management of such a system would be more complex). The details of implementing and managing ASM are significant and would consume more than a few chapters. Review the Oracle Database 10 g documentation for more details on this new Oracle feature. McGraw-Hill/Osborne Sample             What is Cold Fusion ?     A computer will not make a good manager out of a bad manager. It makes a good manager better faster and a bad manager worse faster. . Edward Esber, CEO of Ashton-Tate               © 1999- Digital Mesh Softech India (P) Limited, Kochi. Home | Client Login | Sitemap | Our Land | Privacy | Terms of use