ORA-32018: Parameter Cannot be modified in memory on another instance

ORA-32018: parameter cannot be modified in memory on another instance
 

Cause: Parameter adjustment can take a very long time
 

Action: Modify the parameter individually on each instance using the SID clause of the alter system command

Problem Description
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.

SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.

Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,

SQL> show parameter instance

NAME                                            TYPE      VALUE
------------------------------------       -------------- ------------------------------
active_instance_count              integer
cluster_database_instances      integer          3
instance_groups                      string
instance_name                        string            3
instance_number                      integer         3
instance_type                          string         
open_links_per_instance           integer         4
parallel_instance_group             string
parallel_server_instances          integer         3

From the parameter value we see we are in 3 nodes RAC database and our instance name is ORA3. So set the parameter in our current instance by specifying a SID value.

SQL> alter system set streams_pool_size=100M sid='ORA3';

System altered.

Similarly set it on rest of the instances ORA2 and ORA1 for example.


SQL> alter system set streams_pool_size=100M sid='ORA2';

System altered.


SQL> alter system set streams_pool_size=100M sid='ORA1';

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.

Read More

Rules To Specify Values In An Initialization Parameter

•Parameter within initialization file can be specified in any order.

•If a line starts with pound sign(#) the rest words of the line is ignored. # indicates it is comment.

•If OS is case sensitive then value of filename within initialization parameter is case sensitive.

•To enter several parameters in a line use spaces between the parameters. Like,
db_name=dbase sga_target=300M sga_max_size=300M

•The parameter that take multiple values enter multiple values enclosed in parentheses and separated by commas. Like,
control_files=('/oradata1/arju/control01.ctl','/oradata1/arju/control02.ctl')

Alternatively you can enter multiple values without parentheses and commas. Like,
control_files='/oradata1/arju/control01.ctl' '/oradata1/arju/control02.ctl'

•You can use the IFILE initialization parameter to embed the contents of another initialization parameter file into the current initialization parameter file.

•If you specify a parameter value in multiple location then the last executed value override the prior one since the parameter is executed in sequential order. For example, if my pfile contains value in the following order
sga_target=300M
open_cursor=100
sga_target=400M
then sga_target=400M is in effect.

•Enclose in quotation marks any parameter value that contains a special character.
For example you can specify DB_DOMAIN in any of following ways,

DB_DOMAIN = 'ARJU.ORACLE#.COM'
DB_DOMAIN = ARJU.ORACLE\#.COM

Related Topic:-

• Initialization Parameter File (PFILE)

Read More

Oracle Initialization Parameters File Types

There are different types of initialization parameters in oracle, such as,

1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters

1)Derived Parameters:

As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.

For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.

2)Operating System-Dependent Parameters 

The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.

3)Variable Parameters 

The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.

Read More

SGA_TARGET Initialization Parameter

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

• Buffer cache (DB_CACHE_SIZE)
• Shared pool (SHARED_POOL_SIZE)
• Large pool (LARGE_POOL_SIZE)
• Java pool (JAVA_POOL_SIZE)
• Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

• Log buffer
• Other buffer caches, such as KEEP, RECYCLE, and other block sizes
• Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

Property                         Description

Parameter type                Big integer

Syntax  SGA_TARGET     integer [K | M | G]

Default value                     0 (SGA autotuning is disabled

Read More

SGA_MAX_SIZE Initialization Parameter

Oracle Database can set limits on how much virtual memory the database uses for the SGA. It can start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter. If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Parameter                         Description

DB_CACHE_SIZE              The size of the cache of standard blocks.

LOG_BUFFER                    The number of bytes allocated for the redo log buffer.

SHARED_POOL_SIZE      The size in bytes of the area devoted to shared SQL and PL/SQL statements.

LARGE_POOL_SIZE         The size of the large pool; the default is 0.

JAVA_POOL_SIZE            The size of the Java pool.

Related Post :-

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_TARGET initialization Parameter  

Read More

Setting Initial Database ArchiveLog Mode

You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode. If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files

Changing the Database Archiving Mode

To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

1) Shut down the database instance

 SHUTDOWN

 An open database must first be closed and any associated instances shut down before you can switch   the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

2) Back up the database.

Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode

3) Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files

4) Start a new instance and mount, but do not open, the database.

STARTUP MOUNT

To enable or disable archiving, the database must be mounted but not open.

5) Change the database archiving mode. Then open the database for normal operations.

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

6) Shut down the database.

SHUTDOWN IMMEDIATE

7) Back up the database.

Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.

Read More

ArchiveLog Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

•  A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure. 
• If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
• You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. 

If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode

Related Post :- Setting the Initial Database Archiving Mode

Read More

NoArchiveLog Mode and ArchiveLog Mode

You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode.

If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files.

This section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and contains these topics:

• Running a Database in NOARCHIVELOG Mode
• Running a Database in ARCHIVELOG Mode

Read More

Archived Redo Logs Files

What Is the Archived Redo Log?

Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.

When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind.

You can use archived redo logs to:

Recover a database

Update a standby database

Get information about the history of a database using the LogMiner utility


Related Post: NOARCHIVELOG and ARCHIVELOG Mode

Read More