How to change Oracle hidden parameters

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
 

Oracle hidden parameters start with "_". They can not be viewed from the output of show parameter,
or by querying v$parameter unless and until they are set explicitly in init.ora.

If you want to view all the hidden parameters and their default values, the following query
could be of help,

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/';

To see the listing of all hidden parameters query,

select * from SYS.X$KSPPI where substr(KSPPINM,1,1) = '_';

It is never recommended to modify Oracle hidden parameters without the assistance of Oracle Support. Changing these parameters may lead to high performance degradation and other problems in the database.

Methods to change Oracle hidden parameters:

1) If you use pfile, you can entry of the hidden parameter into initSID.ora, and restart the instance.

"_shared_pool_reserved_pct"=10

2) If you want to set for the current session, use ALTER SESSION SET ...

3) If you want to set it permanently and you are using spfile, use ALTER SYSTEM SET ...... SCOPE=BOTH

Some hidden parameters cannot be changed in MEMORY, such as _shared_pool_reserved_pct. See below:

SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /fs/u01/oracle/product/1 0.2.0.2ee/dbs/spfilemydb.ora SQL> alter system set "_shared_pool_reserved_pct"=10 scope=both; alter system set "_shared_pool_reserved_pct"=10 scope=both * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=memory; alter system set "_shared_pool_reserved_pct"=10 scope=memory * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile; System altered.

If SCOPE=SPFILE, the instance needs to be restarted.

Database upgrading methods

There are four different upgrade methods to upgrade Oracle database to the new Oracle database release:

1) Database Upgrade Assistant (DBUA)
2) Manual Upgrade
3) Export/Import
4) Data Copying

1) DBUA (Database Upgrade Assistant)

The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database 10g release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations. This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is automatically by the upgrade assistant.

2) Manual upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release. A manual upgrade gives you finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easy to diagnose the error. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
When manually upgrading a database, perform the following pre-upgrade steps:

• Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is SQL script that ships with the new Oracle Database 10g release, and must be run in the environment of the database being upgraded.
  The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database 10g release.
• Prepare the new Oracle Home.
• Perform a backup of the database.

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

3) Export/Import

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database 10g database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database.

Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.

4) Data Copying

You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.

Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables