07 Adjusted MEMORY_TARGET for OCM11G Database

References

Overview

I reduced the memory allocated to the BANKSTOWN VM image from 2GB to 1GB after removing ASM. I created a database called ocm11g before doing the reduction, and got an error on startup.

I managed to fix this by using a temporary initialization parameter file (PFILE) instead of using the server parameter file (SPFILE) (see Initialization Parameter Files and Server Parameter Files ).

This procedure is based on " Recovering a Lost or Damaged Server Parameter File ":

Create a text initialization parameter file (PFILE) from the parameter value listings in the alert log.

When an instance starts up, the initialization parameters used for startup are written to the alert log. You can copy and paste this section from the text version of the alert log (without XML tags) into a new PFILE.

See "Viewing the Alert Log" for more information.

Create the SPFILE from the PFILE.

See "Creating a Server Parameter File" for instructions.

Analysis

Error Message

Get the following message on startup for ocm11g database instance:

2012-02-01 09:52:02.939000 +11:00
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 851443712 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 529989632 and used is 0 bytes.
memory_target needs larger /dev/shm

The instance cannot start even in NOMOUNT mode.

Reconstract the Database Server Parameter File

I used the ADRCI Command-Line Utility to extract the startup parameters from the database server alert log.

Starting ADRCI

From Using ADRCI in Interactive Mode , I issued the following commands:

export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
adrci

The first three (3) lines were unnecessary because I had already incorporated them into the .bashrc file for the oracle user.

Set the ADRCI Home Path

Using the procedure in Setting the ADRCI Homepath Before Using ADRCI Commands , I ran the following commands:

[oracle@bankstown ~]$ adrci

ADRCI: Release 11.1.0.7.0 - Production on Thu Feb 9 23:01:57 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u00/app/oracle"
adrci> show homes
ADR Homes:
diag/tnslsnr/bankstown/listener
diag/rdbms/ocm11g/ocm11g
diag/rdbms/unknown/ocm11g
diag/clients/user_oracle/host_183182459_11
adrci> set home diag/rdbms/ocm11g/ocm11g

Find the Startup Parameters

I used the procedure in Viewing the Alert Log to search for the startup parameters:

adrci> show alert

Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /u00/app/oracle/product/11.1.0/db_1/dbs/spfileocm11g.ora
System parameters with non-default values:
processes                = 150
nls_language             = "ENGLISH"
nls_territory            = "AUSTRALIA"
memory_target            = 812M
control_files            = "/u01/app/oracle/oradata/ocm11g/OCM11G/controlfile/o1_mf_7lhgdzry_.ctl"
control_files            = "/u02/app/oracle/oradata/ocm11g/OCM11G/controlfile/o1_mf_7lhgf4q7_.ctl"
db_block_size            = 8192
compatible               = "11.1.0.0.0"
log_archive_format       = "%t_%s_%r.dbf"
db_create_file_dest      = "/u01/app/oracle/oradata/ocm11g"
db_create_online_log_dest_1= "/u01/app/oracle/oradata/ocm11g"
db_create_online_log_dest_2= "/u02/app/oracle/oradata/ocm11g"
db_recovery_file_dest    = "/u02/app/oracle/oradata/ocm11g/flash_recovery_area"
db_recovery_file_dest_size= 4G
undo_tablespace          = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = "yaocm.id.au"
dispatchers              = "(PROTOCOL=TCP) (SERVICE=ocm11gXDB)"
audit_file_dest          = "/u00/app/oracle/admin/ocm11g/adump"
audit_trail              = "DB"
db_name                  = "ocm11g"
open_cursors             = 300
star_transformation_enabled= "TRUE"
diagnostic_dest          = "/u00/app/oracle"

The line highlighted in red needs to be changed.

The name of the server parameter file is highlighted in green .

Create PFILE

About Initialization Parameters and Initialization Parameter Files says that Text Initialization Parameter File Format allows:

For parameters that accept multiple values, to enable you to easily copy and paste name/value pairs from the alert log , you can repeat a parameter on multiple lines, where each line contains a different value.

Emphasis Mine

The non-default parameters are copied from the alert log into /tmp/initocm11g.ora , and the MEMORY_TARGET parameter is changed to the following:

  memory_target            = 452M

Start the Instance in NOMOUNT Mode

" Starting Up an Instance " says that:

You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT clause:
STARTUP NOMOUNT 

I only need the NOMOUNT to create the SPFILE.

In order to read the text initialization parameter file (PFILE), I use the STARTUP command as follows to start the instance in NOMOUNT mode:

sqlplus /NOLOG
STARTUP NOMOUNT PFILE=/tmp/initocm11g.ora

Create SPFILE from PFILE

Once the database instance has been started in NOMOUNT mode, I can use the CREATE SPFILE as follows to create a valid version:

CREATE SPFILE='/u00/app/oracle/product/11.1.0/db_1/dbs/spfileocm11g.ora' FROM PFILE='/tmp/initocm11g.ora';

The name of the SPFILE was gotten from the alert log above, and the name of the PFILE was gotten from the STARTUP command used to start the instance.

Start the Instance Using the SPFILE

Now that I have recreated the SPFILE with valid parameters, I can use the SHUTDOWN command followed by the STARTUP command to start the instance as follows:

SHUTDOWN IMMEDIATE
STARTUP

This will ensure that the default SPFILE is correct.