A Discussion of Obsolete parameters in 10G or "Hey - Where Did LOG_ARCHIVE_START Go?"

During startup, Oracle must read either an initialization parameter file or a server parameter file. These files contain a list of configuration parameters that are used to name things, set limits or size constructs within the database instance.  Database admnistrators are able to adjust or "tweak" selected parameters to improve the performance of the database system.  Determining which parameters to adjust depends upon the performance characteristics of the database being tuned. Each new release contains new parameters and makes other parameters deprecated or obsolete.   If we want our database create statement to run successfully the first time, we'll need to find out what the changes are.  

Ace DBA Jim Dojonovic, Giant Eagle's resident 10G researcher, has already done the bulk of the work for us. Jim did what every good DBA should do before creating a database using a new release; he read the Database Upgrade Guide!  The Upgrade Guide is required reading for those that plan to use a new release or perform Oracle database upgrades.  It provides hints, tips and tricks - the "rules of the road" that help you achieve a trouble-free upgrade.   You are able to access the entire 10G documentation set by visiting Technet's Oracle10G Documentation Portal.   Then choose the link titled "Upgrade Guide" to access the 10G upgrade documentation.  If you don't have an account on Technet, the web page will prompt you to create one.   If you are serious about learning you trade, go ahead and create the account  The benefits are numerous and the price can't be beat (it's free!).

After reviewing the 10G Upgrade Guide, Jim then turned his attention to the new features section of the Oracle10G Reference Guide.  The Reference Guide has a section that contains a listing of all of the parameters that can be specified in the database's parameter file. 

Database upgrades are one of the more stressful activities DBAs are required to perform.   There's nothing like an upgrade "gone bad" to bring unwanted attention upon you.  How do you reduce the chances of that event occurring?  You prepare for the migration process by reading the Database Upgrade Guide, reviewing upgrade problems on Oracle's Metalink support website and searching the World Wide Web looking for any tidbits of information you can find.  

Oracle10G Deprecated Parameters
DBA Dojonovic found that there were several parameters that were deprecated in Oracle10G. Deprecated parameters are often made obsolete in a future release but they continue to be supported to provide backward compatibility.   If a deprecated parameter is specified in the parameter file, Oracle displays a warning message during instance startup.  In addition, Oracle displays a listing of all deprecated parameters found in the database's parameter file in the alert log.

Here's an example of the error message that is returned when a 10G database is started with one or more obsolete and/or deprecated parameters specified:

SQL>STARTUP
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Here's an excerpt from the alert log from one of our 10G databases showing the warning message:

Deprecated system parameters with specified values:
  log_archive_start       
  max_enabled_roles       
  parallel_automatic_tuning
End of deprecated system parameter listing

The following initialization parameters were deprecated in release 10.1:

• BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
• BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
• GLOBAL_CONTEXT_POOL_SIZE
• LOCK_NAME_SPACE  (superseded by sp-name parameter)
• LOG_ARCHIVE_START (ARCH process is automatically started)
• MAX_ENABLED_ROLES  (min = max =148 in 10G)
• PARALLEL_AUTOMATIC_TUNING  (set automatically)
• PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)   

You can issue the following SQL statement to get a list of all deprecated initialization parameters:

SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';

Oracle10G Obsolete Parameters
We also found that Oracle made numerous parameters obsolete in 10G.  Remember that a 10G database will successfully start even if one or more obsolete parameters are specified.  Like their deprecated counterparts, Oracle will return a warning during startup and record a message in the database's alert log.  But the obsolete parameter will not have any affect on the database environment.  Oracle will often decide to make a particular parameter obsolete and then "hide" it by placing an underscore "_" in front of it.   These parameters, often called underscored or hidden parameters, are not documented by Oracle but can still be specified under the supervision of Oracle support.   An example of this would be OPTIMIZER_MAX_PERMUATATIONS, which is now hidden in Oracle10G.

The following initialization parameters were made obsolete in release 10.1:

• DBLINK_ENCRYPT_LOGIN 
• HASH_JOIN_ENABLED 
• LOG_PARALLELISM  
• MAX_ROLLBACK_SEGMENTS 
• MTS_CIRCUITS 
• MTS_DISPATCHERS 
• MTS_LISTENER_ADDRESS 
• MTS_MAX_DISPATCHERS 
• MTS_MAX_SERVERS 
• MTS_MULTIPLE_LISTENERS 
• MTS_SERVERS 
• MTS_SERVICE 
• MTS_SESSIONS 
• OPTIMIZER_MAX_PERMUTATIONS
• ORACLE_TRACE_COLLECTION_NAME
• ORACLE_TRACE_COLLECTION_PATH
• ORACLE_TRACE_COLLECTION_SIZE 
• ORACLE_TRACE_ENABLE 
• ORACLE_TRACE_FACILITY_NAME
• ORACLE_TRACE_FACILITY_PATH 
• PARTITION_VIEW_ENABLED 
• PLSQL_NATIVE_C_COMPILER 
• PLSQL_NATIVE_LINKER 
• PLSQL_NATIVE_MAKE_FILE_NAME
• PLSQL_NATIVE_MAKE_UTILITY 
• ROW_LOCKING
• SERIALIZABLE 
• TRANSACTION_AUDITING 
• UNDO_SUPPRESS_ERRORS

The major change in this batch of obsolete parameters is that you no longer need to preconfigure initialization parameters for Oracle's Multi-Threaded or Shared Server. 

DBAs can query the V$OBSOLETE_PARAMETER dynamic performance view to display all obsolete and hidden parameters.   The view contains the NAME column (which is self-explanatory) and the ISSPECIFIED column that contains the value "TRUE" if the parameter has been set in the database's parameter file. 
 
You can issue the SQL statement below to create a listing of all of the obsolete parameters:

SQL> select name, isspecified from v$obsolete_parameter;

New Parameters
Instead of providing you with a laundry list of all of the new parameters contained in 10G, I'll discuss each one in depth when I discuss the feature they pertain to.  Hey, that's the great thing about blogs - you can always write another one!  Rest assured that the DBAs at Giant Eagle will be thoroughly investigating the majority of new features contained in Oracle's latest release.

Next Up
Jim will be creating our first data warehouse test database to finally begin the testing phase of our 10G evaluation process.