Tag Archives: initialization

Oracle

Oracle SPFILE vs INIT FILE:

Leave a comment

Oracle 9i and later version of the RDBMS have provided us with an increasing ability to dynamically control initialization parameters through the use of a SPFILE instead of an INIT file. By definition the differences between the two files are:

INIT File – OS text file that defines the initialization parameters for the database. You may be able to dynamically change initialization parameters in a database instance but the changes will not survive a restart of the database. The text init file must be updated manually for any changes to take affect upon restart.

SPFILE – OS binary file that defines the initialization parameters for the database. Dynamic changes to a database instance can be stored in the SPFILE to take affect upon restart and immediately. Non-Dynamic changes can be recorded to the SPFILE, to take affect at restart without having to enter OS layer.

This article will cover determining what you are using (an SPFILE or INIT file) and how oracle determines which to use.

Determination of what you are using:

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 21 19:31:43 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Use the following command to determine if you are using an spfile: show parameter {initalization_parameter}

In below example, you are using an SPFILE because the VALUE file is not null and defines an SPFILE:

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string
/opt/app/oracle/10.2.0/dbs/spfileorcl.ora

In this next example you are using an init file because the VALUE of SPFILE is null.

NAME TYPE VALUE
———————————— ———– ——————————
spfile string

This completes the determining the use of SPFILE or INIT file.

How Oracle handles SPFILE and INIT files during startup. (10g and higher)

1. First, Oracle looks for a ‘pfile={file and full path}’ clause in your start command and if it is present uses that file regardless of the existence of any other SPFILE or INIT file.

2. Second, Oracle looks for an spfile{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

3. Finally, Oracle looks for an init{sid}.ora file in the directory
$ORACLE_HOME/dbs and if found it will use it.

4. If none of these exist, the Oracle instance will not start.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Changing init parameters with an SPFILE:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to change initialization parameter from within an Oracle database. This procedure is the same on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 27 09:49:04 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. We use the alter system command to change initialization parameters from within the Oracle database while an SPFILE is in use. The following is an example of the alter system command.

alter system set {initialization_parameter} = {new_value} scope = {scope}

4. The key here is the scope clause which can have three possible values:

MEMORY – No changes are made to the SPFILE at all and the change is only mode in the current instance. To use this option, the initialization parameter must be dynamic and modifiable.

BOTH – Changes are made both in the current instance and in the SPFILE for future restart of the database. To use this option, the initialization parameter must be dynamic and modifiable.

SPFILE – Changes are not made in the current instance and the SPFILE is updated with new initialization parameter setting; however it will only take affect on restart of the database.

5. Examples of changing initialization parameter with alter system command:

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump’ scope=both;

System altered.

SQL> alter system set user_dump_dest = ‘/opt/oracle/udump ‘ scope=memory;

System altered.

SQL> alter system set audit_file_dest=’/opt/oracle/udump ‘ scope=spfile;

System altered.

This completes adjusting initialization parameters in an SPFILE.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Determining if an initializing parameter is dynamic or not:

Leave a comment

Since Oracle 9i, Oracle has introduced the use of SPFILE over INIT files for control of the RDBMS initialization parameters. The introduction of SPFILE was to allow for the dynamic changing of initialization parameters setting while the database is up and eliminating the need to open an OS layer file to make changes to the ORACLE initialization parameters. This article will cover: how to determine if an initialization parameter is dynamic. This procedure is the same on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 18 20:49:04 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following SQL statement: select NAME, ISSYS_MODIFIABLE from V$PARAMETER;

SQL> set pagesize 500
SQL> column name format a30
SQL> select NAME, ISSYS_MODIFIABLE from V$PARAMETER;
NAME ISSYS_MODIFIABLE
——————— ——————-
..
..
..
user_dump_dest IMMEDIATE
max_dump_file_size IMMEDIATE
audit_file_dest DEFERRED
LINUX_sched_noage FALSE
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
session_max_open_files FALSE
..
..
..
SQL>

4. Note: In the example above we have the NAME of the initialization parameter and three values for ISSYS_MODIFIABLE column, we can use this to determine if the initialization parameter is modifiable or not.

IMMEDIATE – The parameter is dynamic and can take affect immediately no matter if you are using a SPFILE or INIT file. When using an SPFILE you can use the clause SCOPE in the following ways: BOTH = change will take affect immediately and on restart of database; MEMORY = change will only take affect in current instance; and SPFILE = change will only take affect on restart of database. When using an INIT file you can use the clause SCOPE in the following ways: MEMEORY = change will only take affect in current instance.

DEFERRED – The parameter is not dynamic and will not take affect immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

FALSE – The parameter is not dynamic and cannot be changed immediately. When using an SPFILE you can use the clause SCOPE in the following ways: SPFILE = change will only take affect on restart of database. When using an INIT file you have to manually update INIT file and restart database.

This completes determining if an initialization parameter is dynamic.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com