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
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:
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:
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.
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:/u01/app/oracle/diag/rdbms/orcl/orcl
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:<diagnostic_dest>/diag/rdbms/<dbname>/<instname>
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:/u05/app/oracle/diag/rdbms/orcl/orcl1
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.
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
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.
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:
If you set the db_ultra_safe parameter value to data and index,the following will be true:
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.
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.
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:
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 Network Configuration page
The DBCA’s New Initialization Parameters page
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:
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:
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-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:
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.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.