Fixing SPFILE parameter on ASM While database is closed

I accidently changed a parameter memory_max_target with a less value than required one. When i bounced database it gave me error that
memory_max_target should be atleast 6GB while i fixed it to 4GB. Both instances are down so it is not possible to fix parameter using alter system command. I created a dummy parameter file with minimum required parameters to start instance. Remember in my case asm is already running only database is down. So i created a text file d:\temp\rctest.txt with following parameters.

large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\oracle\admin\RCTEST\adump'
audit_trail='db'
db_create_file_dest='+DBDATA'
compatible='11.2.0.0.0'
control_files='+DATA/RCTEST/controlfile/control01.ctl'
#CONTROL_FILES='D:\app\oracle\fast_recovery_area\RCTEST\CONTROL01.CTL'
db_block_size=8192
db_domain=''
db_name='RCTEST'
db_recovery_file_dest='+DBFLASH'
db_recovery_file_dest_size=10G
diagnostic_dest='D:\app\oracle'
remote_login_passwordfile='exclusive'
RCTEST1.instance_number=1
RCTEST2.instance_number=2
cluster_database=true
cluster_database_instances=2
RCTEST1.thread=1
RCTEST2.thread=2


+++++++++++++++++++++++++++++++++++++++++++++++++++++
Start Instance in nomount state so we can access files on asm from sqlplus.

SQL> startup nomount pfile='d:\temp\RCTEST.txt';
ORACLE instance started.

Total System Global Area 1553379328 bytes
Fixed Size                  2281496 bytes
Variable Size            1224740840 bytes
Database Buffers          318767104 bytes
Redo Buffers                7589888 bytes


+++++++++++++++++++++++++++++++++++++++++++++++++++++
Access files on asm using asmcmd and to locate spfile or you can check it using below command

D:\app\11.2.0.4\grid\BIN>asmcmd
ASMCMD> ls
DATA/
FLASH/
OCRVOTEDISK/
ASMCMD> cd DATA
ASMCMD> ls
RCTEST/
TESTRAC/
ASMCMD> cd rctest
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilerctest.ora
ASMCMD> pwd
+DATA/rctest
ASMCMD>

So our spfile is located on +DATA diskgroup under path (+data/rctest/spfilerctest.ora)


Create pfile from spfile and fix parameter values

SQL> create pfile='D:\TEMP\INITRACTEST.TXT' FROM SPFILE='+data/rctest/spfilerctest.ora';

File created.

+++++++++++++++++++++++++++++++++++++++++++++++++++++
Contents of SPFILE

rctest2.__db_cache_size=4093640704
rctest1.__db_cache_size=3254779904
rctest2.__java_pool_size=33554432
rctest1.__java_pool_size=16777216
rctest1.__large_pool_size=50331648
rctest2.__large_pool_size=50331648
rctest2.__oracle_base='D:\app\oracle'#ORACLE_BASE set from environment
rctest1.__oracle_base='D:\app\oracle'#ORACLE_BASE set from environment
rctest1.__pga_aggregate_target=1728053248
rctest2.__pga_aggregate_target=1728053248
rctest1.__sga_target=5167382528
rctest2.__sga_target=5167382528
rctest2.__shared_io_pool_size=0
rctest1.__shared_io_pool_size=0
rctest2.__shared_pool_size=956301312
rctest1.__shared_pool_size=1795162112
rctest2.__streams_pool_size=0
rctest1.__streams_pool_size=16777216
*._b_tree_bitmap_plans=FALSE
*.audit_file_dest='D:\APP\ORACLE\ADMIN\RCTEST\ADUMP'
*.audit_trail='DB'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/rctest/controlfile/current.272.861389055','+FLASH/rctest/controlfile/current.261.861389055'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='rctest'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=52478083072
*.diagnostic_dest='D:\APP\ORACLE'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rctestXDB)'
*.event=''
rctest1.instance_number=1
rctest2.instance_number=2
*.memory_max_target=7368709120
*.memory_target=7294967296
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=TRUE
*.optimizer_mode='CHOOSE'
*.optimizer_use_invisible_indexes=TRUE
*.pga_aggregate_target=1716518912
*.processes=150
*.remote_listener='mohecrs-scan.mohe.gov.sa:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5167382528
rctest1.thread=1
rctest2.thread=2
*.undo_retention=18000
rctest1.undo_tablespace='UNDOTBS1'
rctest2.undo_tablespace='UNDOTBS2'


+++++++++++++++++++++++++++++++++++++++++++++++++
After fixing parameters start instance again with newly created pfile from spfile in nomount state. Now instead of replacing spfile with new file from pfile, just update parameter related to spfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

Add spfile parameter so we can change parameters in spfile directory from sqlplus.

SQL> ALTER SYSTEM SET SPFILE='+data/rctest/spfilerctest.ora';

System altered.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +data/rctest/spfilerctest.ora

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now change parameters in spfile from sqlplus (No need to recreate spfile from pfile)


SQL> alter system set memory_target=8G scope=spfile sid='*';

System altered.

SQL> alter system set memory_target=7G scope=spfile sid='*';

System altered.

SQL> alter system set memory_max_target=7G scope=spfile sid='*';

System altered.

Shutdown instance.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> EXIT

Startup database from cluster command

srvctl start database -d rctest