New Features in Database Creation - Oracle 11g

You can create a new Oracle data base either with manual commands at the SQL prompt or with the help of the Data base Configuration Assistant (DBCA).Oracle Data base 11g contains some changes in both methods of creating new data bases.Some changes such as new initialization parameters are,of course, common to both techniques,so first we’ll cover the new initialization parameters in Oracle Data base 11g.

New Initialization Parameters

When you create a new Oracle Data base 11g version data base,you’ll want to know about some important changes regarding the Oracle initialization parameters.
There are both new initialization para meters and some depre cated para meters.A few significant new initia lization parameters in Oracle 11g affect the implementation of certain key new Oracle 11g features.You don & rs quo;t necessarily have to set any of these new para meters when you& rsquo; re up grading to Oracle Data base 11g—or even when creating a new Oracle 11g data base,for that matter.

You can now create a traditional text initialization parameter file or a server parameter file from the current values of the initialization para meters in memory.Chapter 3 shows you how to do this.Another new Oracle Data base 11g feature is that the initia lization parameter settings are recorded in the alert log in a way supposed to make it easy for you to copy and paste those settings if you want to create a new parameter file.When you start up the instance, Oracle writes all initialization para meter values to the alert log in valid syntax, so you can copy and paste this into a new initialization parameter file if you want.

Oracle’s wonderful compatibility feature means that your 9i or 10g database will work under the Oracle 11g software with nary a change.The lowest possible setting you can use for the compatible initialization parameter is 10.0.0 before you up grade to the Oracle Data base 11g release.The default value is 11.1.0,and the maximum value is 11.1.0.n.n. When you set the compatible para meter to the minimum value of 10.0.0,the newly up graded data base can avail of only a small subset of the new features of Oracle Data base 11g.However, some of these new initialization parameters control several of the most important innovations of Oracle 11g, topics that we discuss later this book.Scan through the following sections to see which initialization parameters you must use, if you want to adopt key new Oracle 11g features.All these parameters will be explained in more detail in the relevant chapters later in this book.The following sections are a quick summary of the most important initialization parameter changes in Oracle 11g

Memory-Related Parameters

One of the biggest changes in Oracle Database 11g is the new automatic memory management feature under which both the major components of Oracle’s memory allocation—the shared global area (SGA)and the program global area (PGA)—will be automatically expanded and shrunk,based on the needs of the instance.All you need to do is set the values for two memoryrelated para meters, memory_target and memory_max_target:

  • The memory_target parameter sets the system-wide usable memory and lets Oracle tune both the SGA and PGA,changing the values of the SGA and the PGA automatically based on the demands of the running Oracle instance.You can dynamically change the value for this parameter using the alter system command.
  • The memory_max_target parameter sets the maximum value of memory Ora cle can use.
    That is,the value you set for the memory_ max_target parameter is the maximum value up to which you can adjust the memory_target parameter ’s value.

You can enable automatic memory management by setting the value of the memory_target parameter and the memory_max_target para meter in the initialization parameter file when creating a new database.You can also add them later to the initialization para meter file after data base creation, but you have to bounce the data base for automatic memory management to take effect.Here’s an example showing how to specify the new memory-related parameters if you started your data base with an initialization parameter file:

  • memory_max_target = 500MB
  • memory_target = 350MB
  • sga_target = 0
  • pga_aggregate_target = 0

This set of initialization parameters ensures that the server allocates 350MB of memory to Oracle right away.Oracle will allocate this memory among the SGA and the PGA.You can dynamically change the value of the memory_target parameter up to the maximum of 500MB set by the memory_max_target para meter.Note that you must set both the sga_target and parameters to 0 if you don& rsquo;t want to set any minimum values for the sizes of the SGA and the PGA.For test ing purposes on both Linux and Windows servers, you can use as little as 120MB as the value for the memory_target para meter.We discuss the aut omatic memory management feature in detail in Chapter 3.

PL/SQL Native Compilation Parameter

In Oracle Data base 11g,it’s easier than ever to enable PL/SQL native compi lation,which offers greater performance benefits.In Oracle Data base 10g,you had to use the initialization parameter you’ve also set the diagnostic_dest parameter.

If your Oracle base location is/u01/app/oracle,your data base name is orcl, and the instance name is also orcl,then by default the ADR home directory will take the following form:

/u01/app/oracle/diag/rdbms/orcl/orcl plsql_native_library_dir to set a directory, as well as specify the plsql_native_library_sbdir_count parameter to enable native compilation of PL/SQL code.In addition,you also had to use the spnc_commands file in the plsql directory under the Oracle home.In Oracle Data base 11g you use just one initialization parameter,plsql_code_type,to turn on native compilation.You don’t need a C compiler,and you don’t have to manage any file system DLLs either.You don’t need to create any directories or use the spnc_commands file.You can also adopt native compilation for Java.We explain native compilation in more detail in Chapter 4.

PL/SQL Native Compilation Parameter

The diagnostic_dest Parameter

The initialization parameter diag nostic_dest is new in Oracle 11g.this para meter points to the location of the new auto matic diagnostic repository The diagnostic_dest para meter replaces the user_dump_dest,back ground_dump_ dest,and core_dump_dest initia lization para meters in past releases.The data base ignores any values you set for these para meters if

If your Oracle base location is /u01/app/oracle, your data base name is orcl,and the instance name is also orcl,then by default the ADR home directory will take the following form:


The diagnostic_dest Parameter

In the orcl directory, you’ll find various subdirectories such as alert,incident,and trace.The trace directory is where the user_ dump_dest and core_ dump_dest files used to go in earlier releases.

You can specify that the ADR be located in a nondefault location by setting the diagnostic_dest initialization parameter. The basic directory for the ADR, known as the ADR home, will have the following directory structure:


If you set the diagnostic_dest parameter to/u05/app/oracle,the data base name is orcl,and the instance name is orcl1, then the following would be your ADR home directory:


New Result Cache–Related Parameters

You’re familiar with the caching of queries in the shared pool component of the SGA.In Oracle Data base 11g, Oracle has gone quite a bit further and caches the actual results of queries in memory.The caching is done in a new component of the SGA called the result cache.result caching dramatically improves performance for frequently run queries when there are few changes in data.

For a table or view to be considered for result caching, you must alter the table using the result_cache_mode clause.You must set the new initialization parameter result_cache_mode to the appropriate value if you want the data base to consider all queries or to only those quer that involve tables and views for which you have set the result_cache option.You can also cache PL/SQL function results in addition to SQL query results.

In addi- tion to the result_ cache_mode_option,there are several result cache–related initialization para meters,such as the result_ cache_max_result, result_cache_max_size,and result_ cache_remote_expiration parameters.You can also cache query results on the client side.When you use client-side query caching,you can specify the new parameter sclient_result_cache_ size and client_result_cache_lag.

New Result Cache–Related Parameters

Parameter to Control DDL Lock Timeout

One of the important new features of the Oracle 11g data base is the new DDL locking duration control feature.The ddl_lock_time out parameter lets you specify the length of time a DDL statement will wait for a DML lock.this feature comes in handy when you want to perform online reorganization, where a DML lock by a user may prevent a DDL operation from succeeding.You can practi cally specify that a DDL statement wait forever by setting the para meter to the maximum allowed value, which is 1,000,000 seconds

SecureFiles-Related Parameter

The new Oracle Secure Files feature is a major re vamping of the impleme ntation of Large Objects (LOBs).By using the new initia lization parameter db_securefile,you can specify whether to treat a LOB file as a SecureFiles file.

SecureFiles-Related Parameter

The db_ultra_safe Parameter

The new parameter db_ultra_safe sets default values for para meters,such as the db_block_checking para meter,that control prote ction levels.
To be precise,you can control three corruption checking parameters—db_block_ checking db_ block_checksum,and db_lost_write_protect — by specifying values for the db_ultra_safe para meter.

The db_ultra_safe_parameter can take three values—off,data only, and data and index.By default,the db_ultra_safe parameter is set to off,meaning that any values you set for any of the three parameters won’t be over ridden.If you set the db_ultra_ safe parameter value to data only, the following will be true:

  • db_block_checking will be set to medium.
  • db_lost_write_protect will be set to typical.
  • db_block_checksum will be set to full.

If you set the db_ultra_safe parameter value to data and index,the following will be true:

  • db_block_checking will be set to full.
  • db_lost_write_protect will be set to typical.
  • db_block_checksum will be set to full.

Security-Related Parameters

There are two important security-related initialization parameters that are new in Oracle Database 11g.The first para meter,sec_case_sensitive_logon,lets you enable and disable pass word case-sensitivity in the data base.By default, pass word case- sensitivity is enabled in Oracle Database 11g.

The other new security-related initialization para meter is the para meter sec_max_failed_login_attempts,which specifies the maximum number of times a client can make a connection attempt to a server.The default value of this parameter is 10.

Optimizer-Related Parameters

There are several important new optimizer-related initia lization parameters that are intended to support powerful new features such as SQL Plan Management, private statistics,and invisible indexes.We discuss all these features in subsequent chapters and merely introduce the relevant new initialization parameters in this section.

relevant new initialization parameters in this section.
Oracle Data base 11g replaces the old plan stability feature with the new SQL Plan Management feature.A change in the execution plan of an important SQL statement can potentially degrade perfor mance.To avoid this performance degradation,the database selects optimal SQL plan baselines and prevents the optimizer from changing the execution plan of a statement until the new plan is found to be definitely superior to the existing SQL baseline plan (lower cost).You can enable automatic SQL plan capture so the database can capture and maintain SQL plan history using infor mation from the optimizer.

By default,automatic plan capture is disabled,and you can enable it by setting the optimizer_capture_sql_plan_baselines para meter to true.Chapter 4 contains a detailed discussion of the SQL Plan Management feature.

Use the new initialization parameter optimizer_use_sql_base lines to enable the use of SQL plan baselines that are stored in what’s called the SQL management base.If you enable SQL plan base lines,the cost optimizer will search in the SQL management base for a SQL plan base line for the SQL statement being currently compiled.If there is a SQL plan outline available,the cost optimizer will select the baseline plan with the least cost.

A third new optimizer-related parameter,optimizer_private_ statistics,all- ows you to specify the use of private statistics during the compilation of SQL stat ements.Please refer to Chapter 4 for details about the major optimizer-related new features.

Finally,the new parameter optimizer_use_invisible_indexes lets you enable and disable the use of invisible indexes.

DBCA Enhancements

The DBCA provides an alternative to the manual creation of a new Oracle data base.In Oracle Data base 11g, you should be aware of a couple of changes when you’re creating a new data base with the help of the DBCA.These changes pertain to security settings and the choice of memory allocation for the new data base

We summarize the changes in the DBCA by listing all the steps required to create a new data base with the DBCA.Most of the steps are identical to the steps you followed in the Oracle Data base 10g release,but there are two new steps and a couple of modified steps. Let’s review the data base creation steps when you use the DBCA to create a new Oracle 11g data base:

  1. On the DBCA Operations page, select the Create a Database option.
  2. On the Data base Templates page,select one of the following database types:Data Ware house,General Purpose,or Transaction Processing.
  3. On the Data base Identification page, select the database name and the system identifier (SID).
  4. On the Management Options page,select Database Control or Grid Control.
  5. On the Database Credential page, specify passwords for accounts such as sys and system.
  6. On the Security Settings page,choose the security settings for the new database (this is new in Oracle Database 11g).DBCA provides secure data base configuration features by default.You can turn off security configuration in the new data base if you want.Here are the important features related to secure data base configuration:
    • Audit settings
    • Password profiles
    • Revoking grants to the public role
  7. You’ll find more about secure data base configuration in Chapter 6.Figure shows the new Security Settings page.

    DBCA’s new Security Settings page

    DBCA’s new Security Settings page

  8. On the Network Configuration page, select the listener(s) for which you plan to register the new data base.(This is new in Oracle Data base 11g.) Figure shows the new Network Configuration page.
  9. DBCA’s new Network Configuration page

    DBCA’s new Network Configuration page

  10. On the Storage options page, select the type of storage mechanism.
  11. On the Data base File Locations page,specify the Oracle soft ware home and the directory for the data base files,or select the Oracle-Managed Files (OMF) option for data base files.
  12. On the Recovery Configuration page,specify the archivelog/ noarchivelog choice and the flash recovery area location.
  13. On the Data base Content page, specify the sample schemas and custom scripts to be run after data base creation.
  14. On the Initialization Parameters page, alter the default settings for various initialization parameters,such as memory, character sets, and so on.This screen lets you select among the three types of memory allocation—automatic memory management, auto matic shared memory management,or manual shared memory manage ment.(This option has been modified in Oracle Database 11g.) Figure
  15. The DBCA’s New Initialization Parameters page

    The DBCA’s New Initialization Parameters page

  16. On the Data base Storage page, make changes in the storage structure of the data base.
  17. On the Database Creation Options page,choose from three options:Create Data base,Save As a Data base Template,or Generate Data base Creation Scripts.

In step 6, you can choose to use the new enhanced default security settings,or you can disable the security controls if you want.The default security controls are part of the new Secure Configuration option, which configures data base auditing and password policy and expiration settings.hus,the default config uration includes the Secure Configuration option,but you can check the Disable Security Settings box to disable the enhanced security controls.The new database is then installed with the default security options for Oracle Database 10g Release 2.You can configure the Secure Configuration option later by invoking the DBCA and

Of course, as with all new database releases, several of the older Oracle-supplied packages have been updated.Please refer to the Oracle P/L SQL Packages manual for full details on all Oracle packages that have been updated in Oracle Database 11g.

In step 12, you get a chance to modify the default initia lization parameter settings.The only real change in this section pertains to the allocation of Oracle memory. As in Oracle Data base 10g,you can choose between Typical,which requires little or no configuration on your part, and Custom,which requires more configuration.If you choose the Typical method of memory allo cation,Oracle automatically tunes memory for the instance using automatic memory management.

The amount of memory Oracle allocates to the instance will be a percentage of the overall physical memory on the server.If you choose the Custom method, you must specify the memory you want to allocate to the Oracle instance,and you must also choose from one of the following three types of memory allocation:

  • Automatic memory management (new in Oracle Database 11g)
  • Automatic shared memory management
  • Manual shared memory management

You select automatic management by first selecting the Typical option on the Initialization Para meters page and then selecting the Use Automatic Memory Management option.Automatic memory manage ment,as explained earlier in this chapter,is new to Oracle Data base 11g,and you can enable it through the new unitization para meters memory_target and memory_max_target.

New Oracle Background Processes

Some new Oracle background processes in Oracle Database 11g can help with some of the new features introduced as part of this release. Here are the important new back ground processes in Oracle Data base 11g:

  • FBDA:The flash back data archive process archives data from all tables that are enabled for flashback archive.The process stores all pre-change images of the table rows in the flash back archive following a DML operation such as an update or a deletion.Flash back archiving is an important Oracle Database 11g new feature.
  • SMCO: The space management coordinator process is in charge of coordinating the work of space management–related tasks such as space reclamation, for example.
  • RCBG: The result cache background process supports the new result cache feature.You’ll get a chance to review the key new back ground processes in more detail when we discuss the new features that use these back ground processes.

You’ll get a chance to review the key new back ground processes in more detail when we discuss the new features that use these back ground processes.

New Oracle Background Processes

New Oracle-Supplied PL/SQL Packages

Several new Oracle-supplied PL/SQL packages provide support for various new features.We describe the most important packages briefly here; you’ll find explanations regarding the use of each of these packages:

  • DBMS_CONNECTION_POOL: Supports the new database resident connection pooling feature.
  • DBMS_SQLPA: Supports the new SQL Performance Analyzer feature.
  • DBMS_ADDM: Facilitates the management of the Automatic Data base Diagnostic Monitor.
  • DBMS_SPM: Supports the new SQL plan management feature .
  • DBMS_AUTO_TASK_ADMIN: Supports the automated maintenance task .
  • DBMS_COMPARISION: Lets you compare the data base objects in two different data bases.
  • DBMS_SQLDIAG: Lets you manually invoke the new SQL Test Case Builder .
  • DBMS_HM:Supports the new database health management feature.The package helps you run Health Monitor checks and retrieve the resulting reports.
  • DBMS_RESULT_CACHE: Supports the new result cache feature .
  • DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY: Support the new Data base Replay feature.

Of course,as with all new database releases, several of the older Oracle-supplied packages have been up dated.Please refer to the Oracle P/L SQL Pac kages manual for full details on all Oracle packages that have been updated in Oracle Database 11g.

New Oracle-Supplied PL/SQL Packages

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics