Tag Archives: parameter

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

Find the Oracle parameter causing ORA-32004 and removing

Leave a comment

During the normally life cycle of a database, multiple upgrades will occur, resulting in parameters becoming obsolete. This article shows how to determine the parameter which is obsolete and removing it.

Typical error generated by an obsolete parameter:

[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl
-sysDBAUserName larry -sysDBAPassword larry
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
[oracle@mylinux2 bin]$

1. Logon to the database as the sys user.

[oracle@mylinux2 bin]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 01:32:07 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL to find the problem

select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

SQL> select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

NAME
——————————————————————————–
VALUE
——————————————————————————–
remote_os_authent
FALSE
SQL>

3. Reset this parameter

SQL> alter system reset remote_os_authent;
System altered.
SQL>

4. Stop database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

5. Start database.

SQL> startup
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL>

This completes removing of obsolete or deprecated Oracle parameters.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

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

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