Oracle® Database Administrator's Guide 11g Release 1 (11.1)
Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Reference 11g Release 1 (11.1)
Oracle® Database SQL Language Reference 11g Release 1 (11.1)
Oracle® Database Utilities 11g Release 1 (11.1)
SQL*Plus® User's Guide and Reference Release 11.1
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":
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.